Why did the database administrator leave his wife?
She had one-to-many relationships.
—unknown author

One-to-many relationships

In the next chapter, we introduce SQL queries that span multiple tables using JOINS. To practice, you will need a schema that consists of multiple tables. This chapter introduces you to a small schema with a one-to-many relationship between two tables. In one of the following lessons on modeling the theory behind this practical chapter is fully detailed. Here you will only find a brief introduction.

Two tables

In the previous chapters, the various components of a SELECT statement were worked out. As a basis, we used a table that was the translation of this conceptual model:

Separate tables

This schema has one entity type with a number of attributes. One of those attributes is the lecturer code of the coordinator of the OPO. After all, each OPO has a unique coordinator. It makes sense to add a second entity type ‘Lecturer’ to the schema. After all, we have a lot of information about lecturers. This entity type contains information about all lecturers, not just the coordinators of an OPO. A naive attempt is the following figure:

If you translate this to PostgreSQL you get two separate tables. However, this structure is a source of problems. We mention two possible errors:

• Someone adds a row (= a new course) to the table ‘Course’ with a coordinator that is not in the table ‘Lecturer’.
• Someone deletes a row in the ‘Lecturer’ table. The lecturer from this row however, is also coordinator of an OPO.

Both operations will cause the database to contain errors. You can then no longer look up info from some courses about the coordinator of the OPO. This is not a good solution. You want your RDBMS (‘Relational DataBase Management System’) to protect you against operations that introduce errors into the schema.

Linked tables

The problem, of course, is that the two tables cannot exist separately. There is a relationship between the two. The conceptual model in the figure below makes this relationship visible:

From right to left you read the relationship as ‘is coordinator of’, from left to right it could then become something in the style of ‘has as coordinator’. Each course has at least one and at most one (i.e. just one) coordinator. Not every lecturer is coordinator of an course, but a lecturer could possibly be coordinator of several courses. The cardinality on lecturer's side is thus (0, N). This is an example of a one-to-many relationship.

Note that the attribute ‘Coordinator’ from an earlier figure is omitted. Indeed, it has been replaced by a relationship with a new entity type ‘Lecturer’ because we feel the need to do more than just keep track of a lecturer's number.

Translation to relational model

Now how do you translate this one-to-many relationship into a relational model? After all, PostgreSQL is an example of a relational database. If we translate the conceptual model from the previous section to a logical model, that logical model will have to be a relational model.

You can study all about logical models in detail in the chapter ‘Logical data model’. The sole purpose of this section is to briefly introduce you to this topic so that we can move on to the next part in our exploration of the SQL language (JOIN). The logical model looks like this :

For now, it's enough that you can read this diagram, and it's not that difficult:

• Each entity type becomes a table, represented by a rectangle with a title: ‘Course’ and ‘Lecturer’.
• Each attribute of an entity type becomes a column in the table.
• Each table has a primary key (PK), represented here in yellow. That primary key has a unique value for each row in the table.
• Each course unit is required to have one coordinator. There is therefore a relationship between the two tables. In the table ‘Course’ a ‘Coordinator’ column has been added. This column is a so-called ‘Foreign Key’ (FK, indicated in green) because each value of this column must occur as a PK of the ‘Lecturer’ table.
• The connection between the two tables makes it clear that the contents of the column corresponds to the value of the ‘Lecturer_id' column. The symbol (two dashes) in the small blue circle indicates that each course unit is required to have one lecturer as coordinator.
• Conversely, the symbol in the larger orange circle shows that each lecturer can be coordinator of zero (bullet) or one or more courses (crow's foot).

This logical model is finally converted to a physical model, where we specify further information that depends on the specific properties of the chosen RDBMS (in our case PostgreSQL). For example, an example would be the data types of the columns.

The physical model was drawn with DBSchema. We will cover this in detail later. This physical model corresponds to the logical model, in a slightly different notation (notice the blue departing arrow indicating that this field is an FK referring to the yellow-brown arriving arrow at lecturer_id). Also, each field has been given a data type (char, date, ...):

Adding this one-to-many relationship in the database is done as follows:

1. First create the table ‘Lecturer’.
2. Search for the primary key of the ‘Lecturer’ table. This primary key is the ‘lecturer_id’ field. The data type of this primary key is a char(8), because a lecturer code always begins with a ‘u’ followed by seven digits. Note that we are actually using the designations ‘field’ and ‘column’ interchangeably.
3. In the table ‘Course’ a column should now be created with exactly the same data type as the data type of the primary key in the lecturer table, so also a char(8). We call this ‘transferring the primary key’ and refer to it in the course table as an ‘foreign key’.
4. Now link these two columns using a constraint (constraint).
5. Then first enter the data in table ‘Lecturer’.
6. After that you can fill the table ‘Course’ with rows.

Realization in your own schema

From here, you need to roll up your sleeves and effectively create the two tables . After all, we want you to soon be able to add and remove information in your own tables and that won't be possible if we let everyone work in the same table. So start pgAdmin and execute the following steps.

If you follow the roadmap below (based on the steps in previous section) you get two linked tables filled with data. We'll make some small exercises on this schema. In the next chapter on JOIN we will use this schema as a basis.

Response of the RDBMS to errors

Now make the following small exercises. Sometimes the purpose of an exercise is to let you make an error so that you learn to properly interpret error messages from our RDBMS.

ERROR: duplicate key value violates unique constraint "pk_lecturer_lecturer_id"
DETAIL: Key (lecturer_id)="(u0012047)" already exists.
SQL state: 23505

INSERT INTO rxxxxxxx.lecturer( lecturer_id, last_name, first_name, email, street, house_number,
  municipality, birthdate, start_date, end_date, sex ) VALUES ( 'u0099999', 'De Ridder',
  'Anouk', 'anouk.deridder@ucll.be', 'Kerkstraat', '23', '3053 Haasrode',
  '1994-07-05', '2022-10-15', null, 'V');

INSERT 0 1

Query returned successfully in 85 msec.

DELETE
FROM rxxxxxxx.lecturer
WHERE lecturer_id = 'u0099999';

DELETE
FROM rxxxxxxx.lecturer
WHERE last_name = 'De Ridder' AND first_name = 'Anouk'; -- dangerous!

DELETE 1

Query returned successfully in 53 msec

DELETE
FROM rxxxxxxx.lecturer
WHERE first_name = 'Bram' AND last_name = 'De Smet' AND municipality LIKE '%Leuven';

ERROR: update or delete on table "lecturer" violates foreign key constraint
  "fk_course_lecturer" on table "course"
DETAIL: Key (lecturer_id)=(u0012047) is still referenced from table "course".
SQL state: 23503

INSERT INTO rxxxxxxx.course( code, credits, name, start_date,
  end_date, language, semester, coordinator ) VALUES ( 'MBI39A', 3, 'Trees and graphs',
  '2017-09-15', '2022-09-14', 'nl', 2, 'u0099998');

ERROR: insert or update on table "course" violates
  foreign key constraint "fk_course_lecturer"
DETAIL: Key (coordinator)=(u0099998) is not present in table "lecturer".
SQL state: 23503