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
- [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.
- [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.]
- 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.
- Find the names of all companies located in Peoria.
- Find the names and salaries of all employees who work for
- Find the names, streets, and cities of residence of all
employees who work for either Small Bank or First Bank.
- Find the city where Smith's company is located.
- Find the street and city where Smith's manager lives.
- Find the average salary of all employees at First Bank.
- Find the company with the most employees.
- [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:
- using an aggregate function.
- 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