Model a Database With UML

Here's the good news. Now that you understand primary and foreign keys, you have everything you need to translate your UML diagram into a relational model.

As you’ll see, this translation process is automatic. You just need to apply certain rules, and it's done.

One-to-Many Association

Take your UML diagram and look at the association between  production  and  ProductionCompany  .

This association is one-to-many. Of course, it’s also a composition. But composition is a specific type of one-to-many association. For now, forget about the composition part and concentrate on the one-to-many aspect:

Because a production company produces many productions, you need to add a foreign key to  production  that refers back to the  production_company  primary key.

Since the  production – production_company  association is a composition, it follows the above rule. There is an additional feature, but you’ll learn more about that later.

Many-to-Many Association

Let’s look at the  production – director  association, a many-to-many association.

If you try to put a foreign key in  production   that refers back to the primary key in  director  , you’ll see that one production can only have one director. You don’t want this. If you put a foreign key in  director  instead, you will then only have one production for each director.

For a better illustration of this problem, watch this excellent Openclassrooms video. 

We need to find another solution.

Here, you can see that a production can have several directors, and a director can direct several productions.

One-to-One Association

Yes, but with an added detail. According to the diagram above, a movie theater can only have one address, but two movie theaters could have the same address. You don’t want this.

If you want to specify that one cinema can only occupy one address, you need to add a uniqueness constraint to the  address  attribute. So, the  address  column couldn’t have the same value twice. This means that  address  cannot hold the same reference to a given address twice. In other words, one address can only be linked to one cinema.

Let’s Recap!

• A one-to-many association means adding a foreign key to the table at the “many” end of the link. This foreign key refers back to the table’s primary key at the “one” end.

• A many-to-many association means adding a new table. The primary key of this new table will consist of the two foreign keys of the tables linked together.

• Also, use a foreign key for a one-to-one association between table  A  and table  B  . The foreign key is held in  A  and references the primary key in  B  .

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