Model a Database With UML

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  : 

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 from  production  ) and  ProductionCompany  . This relationship is one-to-many because the multiplicity is 1 at the  ProductionCompany  end, 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.