Lab 1: ER diagrams and the relational model
Task 0: Preliminaries
-
If you have any questions about this lab or anything else regarding the course, you can post them on Piazza, our course’s Q&A site. Use the link in the left-hand navigation bar to access Piazza.
Please take advantage of Piazza’s search feature. Before posting a question, you should use this feature to see if someone else has already asked a similar question!
-
The labs consist of practice exercises that are designed to reinforce the key concepts from lecture and to prepare you for the problem sets.
-
Feel free to ask questions at any point during the lab.
-
We may not finish all of the lab exercises during the actual lab session. Every Thursday, we will add the solutions for the current week’s lab to the Labs page of the course website.
-
We don’t grade the lab exercises. However, starting with next week’s lab, your attendance at lab will count towards your participation grade. If you attend 85% of the lab sessions over the course of the semester, you will get full credit for lab participation. See the syllabus for more details.
-
To get full credit for participation in lab, you must work productively throughout the lab session. You will not be penalized if you cannot finish all of the lab exercises.
-
Feel free to work on the lab exercises with your classmates, and don’t hesitate to ask a staff member for help!
Task 1: Constructing an ER diagram
In this example, we’ll consider a database of movie information. Suppose we want to allow users to get information about:
- movies
- actors
- directors
- Academy Awards
For the purposes of this exercise, we’ll assume that each movie has a single director, and we will only concern ourselves with the following awards: Best Picture, Best Actor, Best Actress, Best Supporting Actor, and Best Supporting Actress.
Let’s construct an ER diagram for this domain. While there are many possible answers for this problem, you should try to include at least one example of each of the following:
- a relationship set with one or more of its own attributes
- a cardinality constraint
- a participation constraint
- a ternary (three-way) relationship set
In addition to constructing the ER diagram, specify the primary-key attributes of all entity sets by underlining the attributes on the diagram.
Here are some points worth noting about this diagram:
-
The diagram uses a single entity set, Person, for both actors and directors. The advantage to using a single set for both is that some people may be both actors and directors. If we used two separate entity sets, we would end up storing information about some people twice—which wastes space and makes it possible for inconsistencies to arise in the database.
-
The Directed and Acted In relationship sets are binary relationship sets—they each store associations between two entities. Awarded is a ternary relationship set, because it involves associations between three entities: Oscar entities, Person entities, and Movie entities, such as the fact that Tom Hanks won the Best Actor award in 1994 for his performance in “Philadelphia”.
-
The Awarded relationship is many-to-one, because a given Oscar statue is awarded to one person. (We are assuming that the only Oscars we care about are the four major acting awards, all of which are won by one person, and the Best Picture award, which for our purposes is won/received by the director.) We show this many-to-one relationship by using an arrow from Awarded to Person. Strictly speaking, this indicates that a given Oscar-movie combination is associated with at most one person; while this is more than we really need to say, it’s not incorrect.
-
We also have an arrow from Directed to Person, because we’re assuming that there is a single director for each movie, and thus Directed is a function that maps a given movie to its director.
-
A given entity set can participate either totally or partially in a given relationship set. In this case, Oscar has total participation in Awarded, because each entity in Oscar has a corresponding relationship in Awarded. Similarly, Movie participates totally in both Acted In and Directed if we assume that we always store the director and at least one actor for each movie in the database. We indicate total participation by using either a thick line (as shown here) or a double line. Thin/single lines indicate partial participation. For example, Person participates only partially in Acted In, because only some people in the database are actors.
-
The diagram shows an attribute for the Acted In relationship set. This salary attribute is associated with the relationship set because an actor earns a separate salary for each movie in which he or she acts.
-
The attributes that constitute the primary keys for the Oscar, Person, and Movie entity sets are underlined. Note that we need multiple attributes for each primary key (e.g., name and year for Movie). If we were to implement this database, we might well associate a unique ID with each entity in a given entity set, and use the IDs as the primary keys instead.
Task 2: Interpreting and transforming an ER diagram
Now let’s switch to a different domain: airline information—more specifically, information about flights, aircraft, and employees.
The following is an ER diagram for this domain:
Here are some facts about the diagram:
- The Employees entity set includes both pilots and other kinds of employees.
- The Is Certified relationship set specifies the types of aircraft that a pilot is certified to fly.
- The Uses relationship set specifies the airplane used by a particular flight.
- The Has Type relationship set specifies a particular airplane’s aircraft type.
Answer the following questions:
-
Why is there an arrow from Has Type to Aircraft Type?
Because a given aircraft has at most one aircraft type. Thus, Has Type is a many-to-one relationship set from Airplane to Aircraft Type.
-
Why isn’t there an arrow from Is Certified to Aircraft Type?
Because a given employee may be certified to fly more than one type of aircraft. Thus, Is Certified is a many-to-many relationship set.
-
Why is there a thick line from Airplane to Aircraft Type?
Because every aircraft has at least one aircraft type. Thus, Airplane has total participation in Aircraft Type.
-
Why isn’t there a thick line from Employee to Is Certified?
Because not every employee is certified to fly. Thus, Employee has partial participation in Is Certified.
-
In transforming this diagram to a relational schema, would we need a separate relation to represent the Has Type relationship set? Why or why not?
No. Because Has Type is many-to-one from Airplane to Aircraft Type, we can combine the separate relations that would ordinarily be formed for Airplane and Has Type into a single relation:
Airplane(num, type_id)
Now, transform this diagram to a relational schema. Specify the name and attribute names of each relation in the schema, underlining the primary-key attributes of each relation.
Notes:
-
As discussed above, we did not give Has Type its own relation. Similarly, because Uses is many-to-one, we are able to combine its relation with the relation for Flight.
-
We would be unlikely to give Has Type its own relation, since the entity set that we combined it with (Airplane) has only one attribute of its own. However, we could have chosen to give Uses its own relation. If we did, you would end up with the following schema for it:
Uses(flight_num, airplane_num)In addition, we would remove airplane_num from the relation for Flight.
Note that the primary key of Uses only includes the primary key from the constrained entity set (Flight). This allows us to ensure that a given flight appears at most once in Uses.
Finally, specify any foreign keys that are present in your relations along with the associated referential-integrity constraints.
-
airplane_num in Flight, which references Airplane.num; each value of airplane_num must match a value of the num attribute from Airplane.
-
type in Airplane, which references AircraftType.id; each value of type match a value of the id attribute from AircraftType.
-
employee_id in IsCertified, which references Employee.id; each value of employee_id must match a value of the id attribute from Employee.
-
aircraft_type_id in IsCertified, which references AircraftType.id; each value of aircraft_type_id must match a value of the id attribute from AircraftType.
If we gave Uses its own relation, both of its attributes would be foreign keys:
-
flight_num, which references Flight.flno; each value of flight_num must match a value of the flno attribute from Flight.
-
airplane_num, which references Airplane.num; each value of airplane_num must match a value of the num attribute from Airplane.
Last updated on September 15, 2024.