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. We will add the solutions for the current week’s lab to the Labs page of the course website on either Thursday evening or Friday morning.

• 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.

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:

1. Why is there an arrow from Has Type to Aircraft Type?

2. Why isn’t there an arrow from Is Certified to Aircraft Type?

3. Why is there a thick line from Airplane to Aircraft Type?

4. Why isn’t there a thick line from Employee to Is Certified?

5. 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?

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.

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

Last updated on September 15, 2024.

