Model a Database With UML

Transform Your Compositions

Composition is a specific type of one-to-many association. As you've seen before, you have to add a foreign key in the table at the “many” end that references the table’s primary key at the “one” end.

But as you learned, the difference between a composition and a simple one-to-many association is that the component part can't exist without its composite. So, you identify a part based on its composite, meaning that the primary key of the part must include the composite.

Let’s consider a production and its production company. As you saw, a production is defined by its title and the production company that produced it (because two productions might have the same title).

Now that a foreign key  prod_company  is added to  production  that references  production_company  , you can identify each production using its title and company.

You've now identified the final primary key for your relational model, i.e., the  production  table’s primary key  (title, production_company)  .

Now that you have the primary key for  production  , you need to update the  assc_prod_dir  table with this new key. Here’s the result: 

Transform Your Association Classes

As you've seen, the purpose of an association class is to define the characteristics between two classes. For example, the  filming  association class describes the link between a production and a location using a start and end date for the filming session:

Up to now, the  shooting  table only had two attributes (start date and end date). On its own, this date doesn't mean anything. However, you need to be able to link it to the relevant production and location, so as you've probably guessed, you need to use foreign keys!

Association Classes for a Many-to-Many Relationship

The relationship between  production   and  location  is many-to-many.

You already know how to translate this relationship, which is to add a new table containing the two foreign keys referencing the two tables on each side of the association. Here is what it looks like:

Additionally, you need to add the attributes that describe this association to the new table.

Yes, it is. Well done!

Here’s the final result:

Primary Key

You saw in the last chapter that the two dates alone are not enough to guarantee the uniqueness constraint (two productions can be filmed during the same period). But it’s now possible with the added foreign keys.

You also saw that the default primary key of a table within a many-to-many association consists of two foreign keys that reference the two tables on each side of the association.

You know that the set of attributes (production_title, location, zip code) is part of the primary key of  shooting   . However, you’d need to add other attributes to the key in some cases. Remember that a single production can be filmed several times in the same location in the scenario.

So, here’s a possible primary key:    (production_title, location, zip_code, start_date, end_date). You can't have two rows in the  shooting  relating to the same film, in the same location, starting on the same day. If this is the case, it must mean that the two rows relate to a single filming session.

Association Classes for a One-to-Many Relationship

Association Classes for a One-to-One Relationship

Let’s Recap!

• Translate a composition the same as a one-to-many association but add the foreign key to the composite table (to the primary key of the part table).

• A many-to-many association class for an association means adding a new table. This table contains all of the attributes of the association class and two foreign keys referencing the two tables on each side of the association.

We’ve completed the second step in translating your UML diagram into a relational model. Well done! You've transformed your compositions and association classes. In the next chapter, let’s see how to transform your inheritance relationships.