In this chapter, you will learn how to model the associations you identified in the UML diagram. For example, linking a row in one table (e.g., production ) to a row in another table (e.g., ProductionCompany ).
By drawing your UML diagram, you have defined the associations between classes. For example, only one production company produces a production.
That’s a good place to start. But now, you need to be more precise by knowing which film is produced by which company. In other words, you must link each row of the production table with the rows in the ProductionCompany table.
Discover How Foreign Keys Come Into Play
The relational model allows you to do this using a foreign key.
A foreign key is an attribute (or set of attributes) in one table that refers back to the primary key of another table to model the link between the rows in them.
Here’s what it would look like if you were to model the link between two tables, book , and person :
The FK notation shows the attributes that make up a foreign key. You show a foreign key using a dotted line between two tables on a diagram as follows:
The foreign key consists of only one attribute ( author_id ) because the person primary key has only one attribute ( id ). But if the primary key has two attributes, as is the case in your location table, you would need a foreign key with two attributes, which you could call location and zip code :
![Tabular representation of the location and shooting tables showing the primary and foreign keys as [PK] and [FK], respectively](https://user.oc-static.com/upload/2022/04/14/16498882949084_image31.png)
Minimum Cardinality and Null Values
What Is a Null Value?
The relational model makes a practical value available, expressed as null . This value is the equivalent of an empty cell in a spreadsheet where information is lacking or doesn't exist.
For example, if the person table holds attributes for date_of_birth and date_of_death , you could enforce a rule that the date_of_birth attribute must never be null if you want to record the date of birth for every person. This is known as the NOT NULL constraint. However, the date of death will be null for individuals still alive.
Understand How Null Values Are Used in Foreign Keys Based on Minimum Cardinality
When you convert UML associations into foreign keys, the NOT NULL constraint is helpful when the minimum cardinality is 1 (not 0).
Let’s take the example of the composition between
Film(inherited fromproduction) andProductionCompany. This relationship is one-to-many because the multiplicity is 1 at theProductionCompanyend, meaning that a production has one and only one production company.
This association converts into a foreign key in the production table consisting of just one column: production_company , which refers to the primary key name in the ProductionCompany table:
If the minimum cardinality was 0, you could have a production with no production company, showing a null value in the prod_company column:
However, since the minimum cardinality is 1, you must populate production_company for all rows and cannot have any null values.
Let’s Recap!
A foreign key is a set of columns in one table that refers back to the primary key of another table to model the link between the rows in these two tables.
A foreign key can consist of one or more columns.
An absence of value is modeled using a null value in a table.
You can use null values in a foreign key to specify that a row in table A is not linked to any row in table B.
So there you have it! In the following chapter, you will learn to use primary and foreign key concepts to translate all the different association types you defined in your UML diagram.
