CMPT-354: Database Systems and Structures

Assignment 1

Total scores: 60	Summer 1998
Due Thursday, May 28	Instructor: Osmar R. Zaïane

(12 points) Construct an E-R diagram for the simplified university registrar's office discussed in class.
Also, define the tables (schemes) corresponding to your E-R diagram, and indicate the primary keys of each table.
- Make sure your design includes at least the following: Students, Courses, Instructors, Assignments, Departments, Textbooks, classrooms.
- TA's are instructors.
- For each student-assignment pair a grade is recorded.
- For each student-course pair a grade is recorded.
- Each Course is assigned only one classroom.
There could be many ``correct'' answers for this question, depending on the assumptions made. State your assumptions clearly. For full points, your design and assumptions should match and the E-R diagram should be clear.
(24 points) Consider the following relational database:

person(pname, street, city)
works-for(pname, cname, salary)
company(cname, city)
manages(pname, mname)

Answer each of the following queries in:
- the relational algebra
- the tuple relational calculus
- the domain relational calculus
1. Find the street and city of all employees who work for the SuperStore, live in Coquitlam, and earn more than $35,000.
2. Find the names and the companies they work for, for all people who have a higher salary than their manager.
3. Assume that companies may be located in several cities. Find all companies located in every city in which the SuperStore is located.
4. Find the names of managers who manage more than 5 employees living in Richmond.
(12 points) Let R = (A, B, C) and S = (D, E, F). Express each following expression in two other equivalent forms (relational algebra, tuple relational calculus, or domain relational calculus).
(12 points) Consider the relational database defined by the schema in your solution of question 1. Give a relational-algebra expression for each of the following queries:
1. Find the course with the most students.
2. Find the smallest classroom (least number of seats).
3. Find the average grade for CMPT-354
4. Find the instructor teaching the course with the highest average grade

Last update: May 19th, 1998. Page created and maintained by Osmar R. Zaļane