Keys and Relationships¶

Keys¶

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

Record Id¶

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

Relations¶

Tables stored in the database often have to be connected in some way. For example, in the case of a system implemented for the needs of the university, each student should be somehow associated with a particular course of study. In practice, this solution is implemented using a foreign key:

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

• in the Course table it is a column id
• in the Student table it is the column 'index number'.

The Course table also contains a foreign key (on the Student id column) indicating the value of the primary key of the Student table. Thanks to this structure, we can always determine which student belongs to a particular course, e.g.:

Three types of relationships can be distinguished in the theory of relational database design:

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

One-to-one¶

For this type of relationship, one record from the X table corresponds exactly to one record from theY table. In the example below, there is a situation where one record from the Person table corresponds to one record from the Personal_id table.

One to many¶

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

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

Many to many¶

The relation n: m occurs when one row from the table X can correspond to many records from the table Y and vice versa, one record from the table Y can correspond to many records from the table X. The following is an incorrect implementation of this relationship :

This solution, if certain conditions are met, may work properly, but if we wanted to include more than three authors of the book, the above example would not achieve the intended functionality.

In practice, many-to-many relationships are realized through an additional table, the so-called auxiliary table.

In the example above, a new BooksAuthors table was created (its name is completely arbitrary), which is responsible for linking the Authors table and the Books table.

In this case, the BookId column indicates the primary key of the Books table, while the AuthorId column indicates the AuthorId column, which is the primary key of this table. Thanks to this structure, it is possible to reproduce a case in which one author has written many books and the book could have been written by many authors.

ATTENTION: A one-to-many relationship can also be created using a linking table, but in practice, we rarely use this option.

ATTENTION: The connecting table, in a many-to-many relationship, is mandatory and almost always consists of two columns.