Midterm 1 review

Overview

Midterm 1 is next week.

In this lab, the TAs will be guiding you through some practice problems related to the exam.

These problems are NOT comprehensive, so make sure to do a thorough review of all of the relevant material.

In-lab practice problems

ER diagrams and the relational model

1. Imagine that you are designing a database for a club that organizes hikes and other outdoor outings. The following is a partial ER diagram for this database:

   

   1. How could we modify this diagram to capture the following constraints?

      • Every outing is attended by at least one member.

      • Every outing goes to exactly one location.

   2. Given the constraints specified in part (a):

      • What type of relationship set is Attends: many-to-one, one-to-one, or many-to-many? (Note: If it is many-to-one, don’t forget to include a direction.)

      • What type of relationship set is GoesTo?

   3. Turn the ER diagram into the schema for a relational database. Use as few tables as possible. Underline the primary keys and specify any foreign keys.

SQL

In the coursepack, there are a number of SQL practice problems on pages 121-127.

Relational algebra

How would you solve the following problems using relational algebra instead of SQL?

• problem 4 on page 122 of the coursepack

• problem 5 on page 123 (assuming student names are unique)

• problem 7 on page 124, but instead of producing a count for each student, produce tuples of the form (student id, student name, majoring department). If a student has multiple majors, they should have multiple tuples. If a student has no majors, they should have a single tuple in which the majoring department is NULL.

Record formats

Imagine that we are working with a Course table that has the following schema:

Course(name VARCHAR(10) PRIMARY KEY, start_time CHAR(8), end_time CHAR(8), room_id CHAR(4))

Consider the following tuple from that table:

('CS 460', '13:25:00', '14:15:00', '0003')

1. If we use the fixed-length record format discussed in lecture, what would the record look like for this tuple? What would its length be? Assume that we’re using one-byte characters.

2. Now assume that we’re using the third type of variable-length record discussed in lecture, in which each record begins with a header of field offsets. What will the record look like for the above tuple, and what will its length be? In addition to one-byte characters, you should assume that we use two-byte integers for integer metadata like offsets.

There are also extra-practice problems on record formats in Lab 4.

B-trees and B+trees

There are extra-practice problems on these topics in Lab 4.

More practice

The midterm 1 info page includes additional practice problems that you can do on your own.

Last updated on February 27, 2025.