Classes to Tables

Strong entity types

A strong entity is one that doesn’t depend on the existence of another entity. For each strong entity create a new table. This relation will contain all simple attributes of the entity. For composite attributes such as name only include its simple attributes such as first name and last name.

Weak entity types

Also create a table from every weak entity. But the primary key will be composite. Consisting on the primary key of the strong entity and the weak entity.

Binary relationships

one-to-many (1:*)

The one to many is easy. The entity on the many side will have a foreign key. This will be the primary key of the one side. For example: if a cashier registers many sales. But a sale is registered by one cashier. The primary key of the cashier; let’s say cashier_id, will be a foreign key in the sales table. Because if it where otherwise, you will need a list of keys in the cashier table consisting of all the sales that this cashier made.

One-to-one (1:1)

The one to one relationship is a little bit more complicated. It will have three cases.

(a) mandatory participation on both sides of 1:1 relationship;

(b) mandatory participation on one side of 1:1 relationship;

mandatory participation on both sides

In this case both entities are merged into one table. We choose one of the primary keys as primary key and left the other one as an alternate key.

mandatory participation on one side

This case is a little bit like the one to many case. The entity that has mandatory participation will have a copy of the primary key of the entity that has optional participation. Why? This is because mainly we don’t want null values in our tables. So the mandatory entity we know will exist and thus its primary key. So if the optional exists it will always have the foreign key of the mandatory entity. If it were otherwise the primary key of the optional would be referenced in the mandatory entity. And the optional entity may not exists. So there’s a risk that there’s a null value on the mandatory entity.

optional participation on both sides

In this case one can choose. However, if we can identify a way that would lower the risk of null values. For example; let’s say that a company has cars that the staff can use if they choose to. A car may or may not have a staff. And a staff may or may not use a car of the company. However, the mayority of cars have a staff. And only a privileged minority uses a car. So in the cars table if we reference the staff id that uses that car there will be a few null values. If we would do otherwise, we would reference the car id in the staff table. Since a lot of staff don’t have a car associated with them then there will be a lot of null values in the staff table.

Many-to-many (:)

This is different from any other binary relationship. Becuase we create a completely new table. The primary key of this new table will be a composite key that will include the keys of both entities. Likewise, there will be a foreign key for each entity’s primary key.

References

I retrieved this information from the book of my relational database course pages 530-536

Database Systems A Practical Approach to Design, Implementation, and Management. SIXTH EDITION

Thomas Connolly adn Carloyn Begg