Structured Query Language/Relational Databases

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 the table
id_employee the primary key	an integer
firstname a column	a string of characters a column type
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 a column value	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_employee an integer firstname a string of characters lastname a string of characters phone 10 numbers mail a string of characters

project  id_project         an integer  name a string of characters created_on a date ended_on a date # manager an integer

And the company projects would be stored like this:

employee id_employee firstname lastname phone mail 1 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

project
id_project	name	created_on	ended_on	# manager
1	Google	1998-09-08	NULL	5
2	Linux	1991-01-01	NULL	3
3	Wikipedia	2001-01-01	NULL	4

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 same 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_employee an integer firstname a string of characters     lastname a string of characters phone 10 numbers mail a string of characters

members # id_employee an integer    # id_project an integer

project id_project an integer name a string of characters created_on a date ended_on a date # manager an integer

And the employees and the projects can be linked like this:

employee id_employee firstname lastname phone mail 1 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 6 Max THE GOOGLER 936854275 max.the-googler@company.com 7 Jenny THE WIKIPEDIAN 936854276 jenny.the-wikipedian@company.com

members
# id_employee	# id_project
3	2
2	1
4	3
5	1
2	3
6	1
7	3

project
id_project	name	created_on	ended_on	# manager
1	Google	1998-09-08	NULL	5
2	Linux	1991-01-01	NULL	3
3	Wikipedia	2001-01-01	NULL	4

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 (Important)

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.