Copyright © T. H. Merrett

COMP 612               Principles of Database Programming                Week 8

Assignment 7

Use QT-selectors or other relational algebra operators to answer queries 2, 11,
21, 33, 41, 58 of Exercise 4.1-10 (p.249 of Relational Information Systems):

2. Find the items sold by no departments on the second floor.

11. List the departments having an average salary greater than 25000.

21. List the departments that do not sell all the items of type A:
  a) departments that sell some but not all of the items of type A;
  b) all departments except those that sell all items of type A (includes all
     the departments that sell no item at all);
  c) departments that sell something excluding those that sell all items of
     type A.

33. List the companies which supply all the items of type A to one same
    department on the second floor which sells all the items of type B.
    [Logical order of quantifiers: (ALL item of type A) (SOME department)
    (ALL item of type B)]

41. List each item supplied to at least two departments by each company that
    supplies it.

58. List each employee and the difference of his salary with the aveerage
    salary of the department where he works.


Some formulations may contain no QT-selectors.

You may wish to alter the constant values in some questions to test your
formulations against the data.

Use relix to test your formulations: you can use 
       input "/course/cs612/qtsel/createQTseldb.a";
to create the database.

Use the supply data for both supply and sales.

ALL RELIX ASSIGNMENTS ARE TO BE SUBMITTED IN THE FORM OF AN UNEDITED TRACE
OF YOUR DEBUGGED FINAL RUN.


Syntax for QT-selectors:

   <project-list> quant <quantifier list> where <condition> in <relational-expr>

   <quantifier-list>::= (<domain-expr with # or .>) <domain-expr> !
                     (<domain-expr with # or .>) <domain-expr>,<quantifier-list>

   "where <condition>" is optional.

Shared marks for shared work: assignments should be your own work; marks
for joint assignments will be divided by the number of collaborators.
But please feel free to work in groups to learn or for extra work that
is not for marks. HINT: treat your own work for marks as top secret; treat
others' work, if you come across it, as personal and private to them.