Lab 1: ER diagrams, the relational model and relational algebra

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: Interpret and transform an ER diagram

Let’s consider the problem of designing a database of airline information—more specifically, information about flights, aircraft and employees.

The following is an ER diagram for this problem 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 (or, more precisely, from Airplane through 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 Has 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.

Task 2: Understand and write relational-algebra queries

In lecture, we’ve been working with a university database. Here is one possible schema for this database:

You can also see sample contents of the relations/tables here.

1. Let’s say that we want to use relational algebra to find the names of all students in the database who are enrolled in CS 460, along with the credit status for which they are enrolled.

   Why doesn’t the following query work?
   π _{name, credit_status} (σ _{course_name = ‘CS 460’} (Student x Enrolled))

2. How could we fix the query so that we get only the names of students who are actually enrolled in CS 460?

3. Would the following relational-algebra query allow us to solve the same problem?
   π _{name, credit_status} (σ _{course_name = ‘CS 460’} (Student ⨝ Enrolled))

4. Assume that a given student can have 0 or more majors. If we want to find the id numbers of all students who are not majoring in computer science, why wouldn’t the following query work?
   π _id (σ _{id = student_id AND dept_name != ‘comp sci’} (Student x MajorsIn))

5. How could we use relational algebra to obtain the id numbers of all students who are not majoring in computer science?

Extra Practice: Construct 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.

Last updated on September 12, 2025.

