# Structured Query Language/Relational Databases

← Introduction to SQL | Data Query Language →
Before learning SQL, relational databases have several concepts that are important to learn first. Databases store the data of an information system. We regroup data by groups of comparable data (all the employees, all the projects, all the offices...). For each group of comparable data, we create a table. This table is specially designed to suit this type of data (its attributes). For instance, a table named employee which stores all the employees would be designed like this:
employee $^{thetable}$ :
| id_employee $^{theprimarykey}$ | an integer |
| firstname $^{acolumn}$ | a string of characters $^{acolumntype}$ |
| lastname | a string of characters |
| phone | 10 numbers |
| mail | a string of characters |
And the company employees would be stored like this:
employee :
| id_employee | firstname | lastname | phone | mail |
| 1 $^{acolumnvalue}$ | Big | BOSS | 936854270 | big.boss@company.com |
| 2 | John | DOE | 936854271 | john.doe@company.com |
| 3 | Linus | TORVALDS | 936854272 | linus.torvalds@company.com |
| 4 | Jimmy | WALES | 936854273 | jimmy.wales@company.com |
| 5 | Larry | PAGE | 936854274 | larry.page@company.com |
The data stored in a table is called entities. As a table is usually represented as an array, the data attributes (first name, last name...) are called columns and the records (the employees) are called rows. id_employee is a database specific technical identifier called a primary key. It is used to link the entities from a table to another. To do so, it must be unique for each row. A primary key is usually underlined. Any unique attribute (for instance, the mail) or group of attributes (for instance, the first name and last name) can be the table primary key but it is recommended to use an additional technical id (id_employee) for primary key.
Let's create a second table called project which stores the company projects:
| employee id_employeean integer firstnamea string of characters lastnamea string of characters phone10 numbers maila string of characters | project id_projectan integer namea string of characters created_ona date ended_ona date # manageran integer |
And the company projects would be stored like this:
| employee id_employeefirstnamelastnamephonemail 1BigBOSS936854270big.boss@company.com 2JohnDOE936854271john.doe@company.com 3LinusTORVALDS936854272linus.torvalds@company.com 4JimmyWALES936854273jimmy.wales@company.com 5LarryPAGE936854274larry.page@company.com | project id_projectnamecreated_onended_on# manager 1Google1998-09-08NULL5 2Linux1991-01-01NULL3 3Wikipedia2001-01-01NULL4 |
id_project is the primary key of the project table and manager is a foreign key. A foreign key is a technical id which is equal to one of the primary keys stored in another table (here, the employee table). Doing this, the Google project is linked to the employee Larry PAGE. This link is called a relationship. A foreign key is usually preceded by a sharp. Note that several projects can point to a common manager so an employee can be the manager of several projects.
Now, we want to create, not a single link, but multiple links. So we create a junction table. A junction table is a table that isn't used to store data but links the entities of other tables. Let's create a table called members which links employees to project:
| employee id_employeean integer firstnamea string of characters lastnamea string of characters phone10 numbers maila string of characters | members # id_employeean integer # id_projectan integer | project id_projectan integer namea string of characters created_ona date ended_ona date # manageran integer |
And the employees and the projects can be linked like this:
| employee id_employeefirstnamelastnamephonemail 1BigBOSS936854270big.boss@company.com 2JohnDOE936854271john.doe@company.com 3LinusTORVALDS936854272linus.torvalds@company.com 4JimmyWALES936854273jimmy.wales@company.com 5LarryPAGE936854274larry.page@company.com 6MaxTHE GOOGLER936854275max.the-googler@company.com 7JennyTHE WIKIPEDIAN936854276jenny.the-wikipedian@company.com | project id_projectnamecreated_onended_on# manager 1Google1998-09-08NULL5 2Linux1991-01-01NULL3 3Wikipedia2001-01-01NULL4 |
| members # id_employee# id_project 32 21 43 51 23 61 73 ||
An employee can be associated to several projects (John DOE with Google and Wikipedia) and a project can be associated to several employees (Wikipedia with Jimmy, John and Jenny), which is impossible with just a foreign key. A junction table hasn't its own primary key. Its primary key is the couple of foreign keys, as this couple is unique. A junction table can link more than two entity tables by containing more columns.

## Relationships

So let's list the different types of relationships:
* One to one,
* One to many (for instance, the manager of a project),
* Many to many (for instance, the members of the projects).
For each type of relationships, there is a way to link the entities :
* One to many relationship: create a foreign key from an entity table to the other,
* Many to many relationship: create a junction table,
* One to one relationship: just merge the two tables.
Now you know how to design a database schema and to put the data of your information system into it.