Section 1

Constructing an ER diagram
Interpreting and transforming an ER diagram

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

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 descriptive attributes
a key 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.

The following is one possible solution:

Oscar ER diagram solution

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 a descriptive 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.

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:

Airline ER diagram

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 on a particular date.
The Has Type relationship set specifies a particular airplane’s aircraft type.

Answer the following questions:

Could we replace the Date entity set with an attribute of the relationship set? Why or why not?

No, because a given flight may use the same airplane on different dates. If we used an attribute associated with the Uses relationship set, we would be limited to one date value for each combination of flight and airplane entities.
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.

We will underline the primary-key attributes below.

Flight(flno, origin, dest, dist, departs, arrives)

Airplane(num, type)

AircraftType(id, name, range)

Employee(id, name, salary)

Uses(flno, date, airplane_num)

IsCertified(employee_id, aircraft_type_id)

Finally, specify any foreign keys that are present in your relations along with the associated referential-integrity constraints.

Here are the foreign keys and their associated constraints:

type in Airplane; each value of that attribute must match a value of the id attribute from the AircraftType relation.
flno in Uses; each value of that attribute must match a value of the flno attribute from the Flight relation.
airplane_num in Uses; each value of that attribute must match a value of the num attribute from the Airplane relation.
employee_id in IsCertified; each value of that attribute must match a value of the id attribute from the Employee relation.
aircraft_type_id in IsCertified; each value of that attribute must match a value of the id attribute from the AircraftType relation.

Last updated on September 26, 2024.