The relational model wasn't designed to show inheritance relationships. It’s possible to model them, but it requires you to do some thinking.
There are three different ways to translate an inheritance relationship:
By reference.
By a parent class.
By child classes.
Each of these three methods has pros and cons, which we’ll go through now.
The most common method is to transform inheritance by reference because this suits most scenarios.
Transform Your Inheritance by Reference
When using this method, you need to create a table for the parent class and a one for the child class. Foreign keys in the child class tables will allow you to refer back to the parent class table.
The primary key of each child table must be the same primary key as the parent table. Additionally, each primary key in the child tables is also a foreign key referencing the parent table.
What does this mean?
In our case, the primary key of production is ( title , production_company ). Therefore, you need to add these two attributes to the child tables (tv_film, feature_film , web_series , tv_series , and series ), and they will also be both the foreign key to production and the primary key to each child table:
Also, consider that season is part of the series primary key, but we don’t want to make this course too long, so we won’t deal with that here.
Transform Your Inheritance by Parent Class
There's just one table that corresponds to the parent class when using this method.
This requires bringing the data from the child classes into a single table and incorporating the attributes of the child classes into the parent class. You also add an additional attribute (the discriminator attribute), indicating which child class each row corresponds to.
Looking back at your UML, you’ll see that all the production child classes have only one attribute: season . So, you’ll add this to the production table. Note that this attribute will have a NULL value when the production isn’t a series.
So, you add a discriminator attribute that tells you if a production is a TV film, a feature film, etc. Call this attribute filming_type :
Transform Your Inheritance by Child Class
When using this method, the parent class doesn't generate a table. Instead, each child class is translated into a table, and each of these tables inherits the attributes of the parent class.
What’s more, the primary key of each child table must match the primary key of the parent class. So in the scenario, each child table will have (title, production_company) as a primary key:
Alternatives
The relational model wasn’t designed to manage inheritance, so the object-relational model was created. This model builds upon the relational model and adds ideas borrowed from the object-oriented approach (as modeled by the UML diagram), such as inheritance.
Now it's Your Turn!
Now that you have the rules for translating from UML to the relational model, it is your turn to can translate your UML diagram into a relational model!
You can then compare your relational model with the answer below:
Let’s Recap!
An inheritance relationship can be translated in three different ways:
By reference.
By parent class.
By child classes.
Well done! You now know how to translate any UML diagram into a relational model. But actually, does your model minimize redundancy? That’s what we’re going to explore in the final part.
