• 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 Your Compositions and Association Classes

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: 

The assc_prod_dir table updated with this new key
The assc_prod_dir table updated with this new key

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:

The Shooting association class
The Shooting association class

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:

Diagram of a many-to-many association
Diagram of a many-to-many association

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

But isn’t this new table our shooting table?

Yes, it is. Well done!

Here’s the final result:

Translation of an association class into the new “shooting” table
Translation of an association class into the new “shooting” table 
Translation of an association class into a new table
Translation of an association class into a new table

Primary Key

We still haven’t identified the primary key for  shooting  !

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 class for a one-to-many association
Association class for a one-to-many association

Translation of a one-to-many association class
Translation of a one-to-many association class

Association Classes for a One-to-One Relationship

Association class for a one-to-one association
Association class for a one-to-one association
Caption: Translation of the one-to-one association
Translation of the one-to-one association

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. 

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