CS101b - Assignment 1

The Relational Model and Relational Algebra

Please keep track of the total time you spend on this assignment, and make a note of that on what you turn in. This way we can tune the assignments for future classes.

Suggested Reading

This week's assignment draws almost entirely from Chapter 2. However, Chapter 1 has some good introductory coverage, so you will definitely benefit from reviewing at least these parts:

• Chapter 1: Intro, sections 1.1-1.3
• Most book examples are based on a banking enterprise. Read section 1.6.2 to get an idea of what the bank is like, so that you understand why the schemas are the way they are.
• If you want to learn more about the history of relational database systems, read section 1.13

This week's assignment covers all of Chapter 2, so you should read the entire chapter.

Book Exercises

Do these problems from the "Exercises" section of Chapter 2. Each of these problems requires you to write a number of queries or update operations in the relational algebra. You can write the solutions by hand, or do them electronically (for example in MS Word or LaTeX). The only requirement is that it must be neat and understandable.

(For tips on how to write relational algebra expressions in various programs, see this page.)

Turn in a hard copy of your assignment in class on the due date.

Hints:

• Like math problems, most of these problems have reasonably simple solutions, but it might take a while to find the "trick." If your solution to a problem gets unwieldy or convoluted, you might be on the wrong track.
• If you see how to solve a part of the problem, write that part of the query and note what it does. It will help you figure out the solution, and you will be more likely to get partial credit if it's wrong.
• Familiarize yourself with the natural join operation - it can make many queries quite straightforward to write.
• If you find you are reusing a particular sub-expression, consider using a temporary relation to represent that result.
• Unlike SQL, predicates for the select operation can't use relational algebra operations.

Problems:

Each part of a problem is worth three points. For example, problem 2.5 has five parts, for a total of 15 points. There are 16 parts in all, for a total of 48 points.

• Problem 2.5

• Problem 2.6

• Problem 2.7

  Hints for part b:

  • Use temporary relations and multiple steps to simplify the solution.
  • Consider how you might use the rename operation (or generalized projection) to easily compute the set of managers from the works relation.

• Problem 2.8

  • Accounts are stored in the account and depositor relations; pages 40-42.
  • Part b is pretty grungy. Implementing such queries without aggregate functions usually is.

• Problem 2.9

  • Note that the min and max aggregate functions only compute a single value; they can't be directly used to select a particular tuple. Thus, to find tuples with the min or max value, you need to compare all tuples to a tuple containing that value - a Cartesian product.
  • You might want to use temporary relations on these problems to simplify things.