Back to course home page

FORDHAM UNIVERSITY, Fordham College Lincoln Center
CSEU 3500 -- Data Base Systems
Dept. of Computer & Info. Sciences, Spring, 2004

Homework for Chapter 3
Due date: Wednesday, March 3

3.1

[10 pts.] Design a relational database for a university registrar's office. The office maintains data about each class, including the instructor, the number of students enrolled, and the time and place of the class meetings. For each student-class pair, a grade is recorded. [Base your relational design on the E-R diagram shown below. If you combine or drop any tables, explain why.]

E-R diagram for Exercise 3.1.

3.3

[10 pts.] Design a relational database corresponding to the diagram of Figure 3.38. [This diagram could be an alternative answer to Exercise 2.2. If you combine or drop any tables, explain why.]

Figure 3.38 E-R diagram.

3.5 (modified)

[28 pts.] Consider the relational database of Figure 3.39, where the primary keys are underlined. For each of the following queries, give an expression in the relational algebra to express each of the following queries:

employee (person-name, street, city)

works (person-name, company-name, salary)

company (company-name, city)

manages (person-name, manager-name)

Figure 3.39 Relational database for Exercises 3.5, 3.8 and 3.10.

(a)

Find the names of all companies located in Peoria.

(b)

Find the names and salaries of all employees who work for First Bank.

(c)

Find the names, streets, and cities of residence of all employees who work for either Small Bank or First Bank.

(d)

Find the city where Smith's company is located.

(e)

Find the street and city where Smith's manager lives.

(f)

Find the average salary of all employees at First Bank.

(g)

Find the company with the most employees.

3.9

[8 pts.] Using the bank example [whose schema is shown in Figure 3.9], write relational-algebra queries to find the accounts held by [two or more] customers in the following ways:

(a)

using an aggregate function.

(b)

without using any aggregate functions.

(Note that the wording in the text says ``more than two'' customers, but to simplify the problem, I have changed it to ``two or more.'')