course content

Course Content

Relational Database and Normalization

One-to-One and Many-to-ManyOne-to-One and Many-to-Many

One-to-One.

A One-to-One relationship is a type of relationship where each record in one table has a corresponding record in another table. It's a combination of two relations:

  • The first table has a foreign key that references the primary key of the second table.
  • The second table has a foreign key that references the primary key of the first table.

The two tables have a one-to-one relationship, which means that each record in one table is associated with exactly one record in the other table.

Note

The One-to-One relationship is based on the foreign key. The foreign key should have a parameter “unique=True”.

There are situations when one record can exist only in the presence of another, but this other can be independent. This relationship is called One mandatory to one.

Let's look at the following diagram:

One more represention:

One mandatory to one: The Passport should have a Person it belongs to. The Person can exist without a passport (e.g. children).

Many-to-Many

The Many-to-Many relationship is also a combination of two relationships, but more obvious.

This relationship requires creating a new table. A new table should have three columns: id (id for each relation), first_table_id, and second_table_id. The first_table_id and second_table_id columns are foreign keys to the related tables.

There are driver_id and car_id (foreign keys). In the table “driver-car”, we can see the five relations with a unique id:

  • (id 1 and 2) The driver John (driver_id=1) can ride the cars Subaru (car_id=1) and BMW (car_id=3).
  • (id 3, 4, and 5) The driver Emma (driver_id=2) can ride the cars Subaru (car_id=1), Mercedes (car_id=2), and BMW (car_id=3).

Many drivers can ride many cars: John and Emma can ride Subaru and BMW.

Database diagram:

Additional tables are usually hidden on diagrams. From above you can see the absence of the driver-car table.

1. Choose a One-to-One relationship
2. Choose a Many-to-Many relationship

question-icon

Choose a One-to-One relationship

Select the correct answer

question-icon

Choose a Many-to-Many relationship

Select the correct answer

Section 2.

Chapter 4