Database Terms - and their Meanings

Before learning the language you will have to learn some words and their meanings. Basic stuff, but you cannot continue without them. Feel free to skip any word if you already know the meaning.

Database

A database is a collection of data that is organized in a specific way. The data is stored in a way that makes it easy to access using queries. For example, a database called 'Simpsons'.

Table

A database can have multiple tables in it. All the data is stored in the tables. A table has a set of fields in it. These fields makes the design or the structure of the database.
The database 'Simpsons' has two tables - called 'Character' and 'Quote'

Field

Each table has a set of fields.

Our example table 'Character' has four fields - id, name, iq and sex.

Data Type

Each field can have only one kind of data. That is called the Data Type of that field. This can be a string, an integer, a date, etc.

When creating a table, you have to specify the kind of data that can be stored in that field. One field can be used to store a number, another for a string. Since every table has a number of fields, different types of data can be stored in a table.

The data type in our example table 'Character' are...

• id - integer
• name - string
• iq - integer
• sex - string(actually it is enum - 'm' or 'f' - more on this later)

Row

All the terms till now dealt with the structure - row refers to the data. It can also called a record. The collection of a set of values for all fields in one table is together called a row. Yeah, even I cannot understand that definition. But an example will make things clearer - the first row of the Character table has these values...

All this together is called a row.

Primary Key

Primary key is a field that will uniquely identify each row in the table. The value of this field will be unique through the table. In our example table, 'id' is the primary key. This will be 1 for the first row, 2 for the second and so on.

Relation

There will be many cases when the data in one table can be related to the data in another table. This connection between two tables is called a relation.

Foreign Key

When there is a relation between 2 tables, these tables will be connected by inserting the primary key of one table into the corresponding row of the other table. The field used in such a way to connect the 2 tables is called the foreign key.

For example, the 'Quote' table has 3 fields id, character_id, and quote. The character_id field will have the id of the character who spoke the quote - as shown here...

Character Table

Quote Table

The Christmas joke was made by the character with id 3 - that is Bart. And the 'wedding' quote has the character_id 1 that means it is spoken by Homer.

Sorry if you find the definitions here a bit too much. Trust me - you are much better off here than if you are learning SQL from a database book. They are going to lay definitions on you till your eyes bleed. I have seen brave men die from too much exposure to definitions.