Keys and Relations¶

Keys¶

A table in a relational database system should enable the unique identification of specific records. It may happen many times that there is a lot of identical information in the database that should be distinguished. The following situation should not be acceptable:

Record Id¶

To uniquely identify records, an additional column is often introduced to store the unique record identifier (often simply called id). It can be a real (or so-called natural) identifier, such as a PESEL number. However, many times a abstract identifier is implemented in databases that has nothing to do with the actual data structure that the database is to store. This column is called the primary key. These keys are necessary for the correct binding of the data in the tables.

Relations¶

Tables stored in a database must often be related to each other in some way. For example, in the case of a system implemented for the needs of a university, each student should be related in some way to a specific field of study. In practice, this is done with a foreign key:

In the example above, both tables have a primary key:

• in the Direction table it is the id column
• in the Student table it is the index number column.

There is also a foreign key in the Direction table (in theStudent id column) that indicates in the value of the primary key of the Student table. Thanks to such a structure, we can always determine which student belongs to a specific field of study, e.g .:

In relational database design theory, three types of relationships can be distinguished:

• one to one (1: 1)
• one to many (1: n)
• many-to-many (n: m).

One-to-one¶

In this type of relationship, one record in the X table corresponds to exactly one record in the Y table. In the example below we have a situation where one record from the table Person corresponds to one record from the tablePesel.

One to many¶

In the case of the 1:n relation, one record from the X table may correspond to one or more records from the Y table, but one record from the Y table may correspond to only one record from table X.

In the above example, we are dealing with a situation in which one customer can place many different orders, while one order can be assigned only to one customer.

Many to many¶

A n: m relationship occurs when one row in theX table can correspond to many records in the Y table, and vice versa, one record in theY table can correspond to many records in the X table. The incorrect realization of this relation is the following example:

This solution may work properly under several conditions, but if we wanted to include more than three authors of the book, the above example would not implement the target functionality.

In practice, many-to-many relationships are achieved using a additional table, the so-called auxiliary table.

In the above example, a new table BooksAuthors was brought to life (its name is completely arbitrary), which is responsible for linking theAuthors table and the Books table into a relation.

In this case, the BookId column points to the primary key of the Books table, and the AutorId column points to theAutorId column, which is the primary key of this table. Thanks to this structure, it is possible to seamlessly recreate a case where one author wrote many books and a book could have been written by multiple authors.

NOTE: A one-to-many relationship can also be created using a connecting table, but in practice we rarely use this possibility.

NOTE: The many-to-many linking table is mandatory and almost always consists of two columns.