CMPT-354: Database Systems and Structures

Assignment 2

Total scores: 40Summer 1998
Due Thursday, June 18Instructor: Osmar R. Zaïane
  1. (16 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:

    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.

  2. (12 marks) Give expressions in QBE, Quel, and Datalog equivalent to each of the following two queries:
    1. , where R=(A,B,C) and S=(D,E,F), and r(R) and s(S).
    2. , where R=(A,B) and S=(A,C), and r(R) and s(S) .

  3. (6 marks) Consider the relational database of question 1. Using SQL, define a view consisting of mname (manager name) and the average salary of all employees who work for that manager. Explain with an example why the database system should not allow updates to be expressed in terms of this view.

  4. (6 marks) Give an SQL schema definition for the relational database of question 1. Choose an appropriate domain for each attribute and an appropriate primary key for each relation schema.

Last update: June 9th, 1998. Page created and maintained by Osmar R. Zaïane