• 8 hours
  • Medium

Free online content available in this course.

course.header.alt.is_video

course.header.alt.is_certifying

Got it!

Last updated on 5/5/22

Transform the Associations From Your UML Class Diagram

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:

Association between production and ProductionCompany
Association between production and ProductionCompany

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.

Adding prod_company as a foreign key
Adding prod_company as a foreign key 
Diagram showing a foreign key
Diagram showing a foreign 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.

Adding the third additional table prod_dir_assoc
Adding the third additional table prod_dir_assoc

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

Diagram showing the tables with their primary and foreign keys
Diagram showing the tables with their primary and foreign keys

One-to-One Association

One-to-one association between cinema and address
One-to-one association between cinema and address

But isn't this the same as for the one-to-many 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.

Ever considered an OpenClassrooms diploma?
  • Up to 100% of your training program funded
  • Flexible start date
  • Career-focused projects
  • Individual mentoring
Find the training program and funding option that suits you best
Example of certificate of achievement
Example of certificate of achievement