Converting ER to Relational Model
Strong entity sets with atomic attributes
If I have a strong entity set in the ER diagramm, then I'm going to take it and translate it into in the schema diagram, something that looks exactly the same.
Relationship sets with atomic attributes
The primary key of that relationship set, Saw is composed of the primary keys of the participating entity sets. So Saw has a Person's ID identifying which person saw the movie, and then the movie was identified by the title and the year, so that will also be part of the Saw. Primary keys altogether the Saw has are coming from the primary keys of participating entity sets.
In addition to that, Saw has other attributes the relationships had, numStars.
We'll have a foreign key constraint from Saw.ID to Person.ID indicated by arrow.
We'll put curly bracket to say that it's title and year together has to be in Movie.
To sum up, relationship set is represented by a relation schema whose primary key comprised of the primary keys of the participating entity sets; additional attributes for attributes of the relationship set. Foreign key constraints from schema representing relationship to schemas representing entity sets.
Non-atomic attributes
I'm just going to have the first name and the last name and I don't need to have a separate column for name.
Date of Birth and the ID are both atomic and they'll just appear as usual before.
The age of the person from the ER diagram was a derived attribute, which means that it doesn't need to be stored explicitly and it just disappears when I go over to the relational model. However, when you're doing that conversion, you might want to make a a note somewhere that age is something that you might care about and how you're going to compute the age.
In ER model, phone is a multi-valued attributes. A person could have many phone numbers. We'll have a separate relation schema for multi-valued attributes. We have a foreign key constraint saying that the ID has to be a legit ID that appears as the ID of some person.
To sum up, composite attributes in ER model become lowest level attributes of the composite. Multi-valued attributes in ER model appear as a new table. Derived attributes are not represented in Relation schema.
Weak Entity Sets
Student IDs were not unique globally, but that were unique within a particular university that they attend. In other words, students at different universities may have same sID, but no two students at same university ahve same sID.
In relational model, university is just a strong entity set with primary key, Uname. For Student, we'll have the discriminator sID along with the primary key of the associated strong entity set - Uname and state altogether forming the primary key of a Student. Lastly, I'll have a foreign key constraint referencing the strong entities at university.
Notice that there's no dotted line which means that there's no such thing as a discriminator in the relational model or schema diagram. By explicitly putting the primary key of the associated strong entity set into the weak entity set we can get rid of the possible duplication of discriminators.
Special case: many to one, total relationship
Before optimization
For every row of person, I would have exactly one row of favorite because each person has one favorite movie.
After optimization
We'll put the information from favorite into person and we'll make a foreign key constraint from person to movie.
Since every person is associated with exactly one favorite movie, I don't need to have a separate table for favorite. Instead I can put the information about a person's favorite movie as part of the information about that Person. It only works when I've got at most one favorite movie for a person. We're only going to do it when they have exactly one favorite movie.
If we have people who don't have a favorite movie will end up having null values for that extra information.
Last updated