Consider this portion of the model of a bank account we created in an exercise last week:
Convert this ER diagram into a set of relational schema.
State clearly any assumptions you make in your design.
Last time we took advantage of the close correspondence between ER models and the relations in the relational data model to enumerate two simple rules for converting an ER model into a set of relations:
Applying the first rule to the customer model above, we create relations named Customer, Phone, and Address:
Customer(ssNo, name)
Phone(areaCode, number)
Address(street, city, state)
with such as these:
ssNo name
----------- -----
123-45-6789 Eugene W
321-45-6789 John Smith
areaCode number
-------- --------
319 273-5919
517 452-9159
319 273-2618
street city state
-------- ----------- -----
339 WRT Cedar Falls IA
1 Elm St Plainfield IN
305 WRT Cedar Falls IA
The second rule tells us to create relations named PhoneOf and AddressOf:
PhoneOf(ssNo, areaCode, number)
AddressOf(ssNo, street, city, state)
with instances such as these:
ssNo areaCode number
----------- -------- --------
123-45-6789 319 273-5919
321-45-6789 517 452-9159
123-45-6789 319 273-2618
ssNo street city state
----------- -------- ----------- -----
321-45-6789 339 WRT Cedar Falls IA
321-45-6789 1 Elm St Plainfield IN
123-45-6789 305 WRT Cedar Falls IA
That's it!
In cases such as this, we can apply a tweak that produces a smaller set of schemas. PhoneOf is an n:1 relationship from Phone to Customer, which means that there will be exactly one customer associated with each phone. This allows us to fold the PhoneOf relation into the relation for Phone, resulting in this schema:
Phone(areaCode, number, owner)
where the domain of the "owner" attribute is the set of customer
keys. The combined relation would look like this:
areaCode number owner
-------- -------- -----------
319 273-5919 123-45-6789
517 452-9159 321-45-6789
319 273-2618 123-45-6789
The same situation holds for AddressOf, which we can fold into the Address relation. After combining these two relations, we are left with only three relations:
Customer(ssNo, name)
Phone(areaCode, number, owner)
Address(street, city, state, owner)
That's a parsimonious logical model.
We are wrapping up our initial study of the Entity Relationship model and the relational data model. In class this week, we learned about weak entities in the ER model, and then considered how we can translate an ER model into a set of relational schema. In this session, we learn about a special case we haven't considered before, unary relationships, and tie up couple of loose ends regarding weak entities:
When modeling a sports league, we might want to show that two teams play each other in the regular season:
When modeling the film industry, we might want to show that one movie is the sequel of another:
But we don't want to show the same entity twice in a model. That is redundant! Instead, we allow one entity to appear twice in the same relationship:
Each line between the entity and relationship may represent a different role played by the entity. One movie is the original, and the other movie sequel. In such cases, we label the lines with the names of the roles. An original movie can have many sequels, but a sequel can have only one previous film, so the relationship is n:1. The Plays relation, on the other hand, is simpler: both teams have the same role in the relationship, and any team can play any other. This relationship is m:n.
Unary relationships aren't a special case so much as a natural idea found in many domains that we haven't seen yet in our models or ER diagrams. Use a unary relationship when the problem calls for one.
Last time, we saw two kinds of weak entity. The first occurs naturally in the domain, such as when one entity is in a "part of" relationship with another entity. We can recognize such weak entities by the fact that their attributes are insufficient for uniquely identifying tuples on their own.
The second we create in order to make our model faithful to other parts of the domain, such as the EnrollmentRecord entry in an university information system. EnrollmentRecord helped us to express a relationship between students and courses that had data attributes of its own.
Another common source of weak entities is when we create an artificial entity in order to convert a multi-way relationship into a set of binary relationships. Some styles of conceptual modeling allow relationships among more than two entities, but other such as UML do not. Multi-way relationships can be more difficult to understand, so even when we can use them, we don't always want to.
One way to eliminate a multi-way relationship is to create a new entity that represents the relationship and then "connect" this entity to the original entities via binary relationships.
Consider the case of a studio's contract to produce a movie. The contract is a relationship among the movie, the producing studio, the headline actor, and the actor's studio:
We can eliminate this compound relationship by making Contract an entity that relates to the movie, actor, and two studios:
This model eliminates the need to label the studio relationship arcs, as those labels are now full-fledged relationships themselves.
Now that we have made contract an entity, contracts are able to have attributes of their own, such as the date they are signed and the amount paid to the producing studio. Unfortunately, these attributes are not enough to uniquely identify a contract. This makes Contract a weak entity:
We assemble the key for Contract from attributes passed through all four supporting relationships. The keys of the actor, movie, and two studios will make up the key for the contract.
Connecting entities such as Contract are often weak. Some entities created in this way do not have any attributes of their own and so will depend on their supporting entities for key attributes. Others, like Contract, do have natural attributes, but they are too weak to identify tuples uniquely.
The rules for converting entities and relationships into relational schema, which we used again for our opening exercise, do not work well for handling weak entities. Consider the studio crew example from last time:
First of all, the key for the Crew entity consists of both the local key, number, and the key for the supporting entity Studio, name. So we'll have to change Rule 1 for converting the entity into a relational schema.
Second, notice what the key for the Crew does: it represents the relationship between a crew and a studio right in the relation for Crew. We don't have to create a relation for the WorksFor relationship at all!
This is a special case of something we saw last time. The relation for an n:1 relationship can often be eliminated by combining it with relation of the source entity (the one on the "n" side of the relationship). The same dynamic is at play with weak entities, for a slightly different reason: creating the key for the weak entity has already done the work of combining the relations!
So, we have a new set of rules for converting a weak entity in an ER model into a set of relations:
For our studio crew example, we will end up with two relations:
Studio(name, address)
Crew(studio, number, crewChief)
The attribute "studio" will be the name of the studio that employs
the crew.
The Studio relation comes from our usual set of rules, and the Crew relation domes from our rules for weak entities. The weak entity rules tell us not to create a relation for the WorksFor relationship.
Even with the special rules for weak entities, the process of converting ER models into relational models is not too complex. Together, these two sets of rules works for many kinds of ER models, including all the models we'll build this semester.
A charity once required me to include a title with my name while I was making a donation. I was bored with "Mr." and "Dr.", so I gave myself a promotion.
Of course, they sold their mailing list to other charities. Now I receive mail for the Vice Admiral with some regularity.
When I first thought of this in the context of our course, I was thinking that this was a danger of having a poorly-defined domain for a relation's attribute. But that's not right. "Vice admiral" is a perfectly valid title -- for someone (*). Just not me.
(*) statista.com tells me that there are 37 vice admirals in the US Navy this year.
I suppose the real danger is requiring users to provide a value they don't care about. This is a user interaction problem!
Or maybe the problem is people like me, having a little fun.
When you finish working through this session, email me to let me know you are done. If you have any questions at that point, please ask at least one in your email message. Feel free to ask as many as you'd like!