The DataBase Course

Exercise No. 3

SQL and Relational Calculus - Practical

Due on December 7nd by midnight

The Example Database Schema: Suppliers supply certain parts for certain projects. The information about who supplies what to which projects is found in the shipments table. The tables are:

Suppliers(s#,sname,status,city)
Parts(p#,pname,color,weight,city)
Projects(j#,jname,city)
Shipments(s#,p#,j#)

The s#,p#,j# attributes are key attributes. The tables are available to query using Oracle.

Express the following queries in tuple relational calculus and SQL:

1. Get all pairs of part numbers that are supplied by the same supplier. Include both pairs (a,b) and (b,a) in your answer.
2. Which part numbers are heavier than p1?
3. Which projects (give project numbers) are made entirely out of red parts?
4. For which colors are all the parts of that color supplied by s5?
5. What are the names of the projects which are located in a city where no supplier is located?
6. Get supplier numbers for suppliers who supply the same part to all projects.
7. What are the average, minimum and maximum values for supplier status?
8. What is the average number of parts per project?

The queries should be clearly documented! For questions 7 and 8 only an SQL query is required.

You should submit:

• The first six queries in calculus, on paper.
• Printouts of the eight SQL queries and outputs.
• Submit electronically (run ~db/bin/submit to submit ex1.tar) the following files:
  README - containing only your login on the first line, and your full name and relevant exercise documentation afterwards.
  queries.sql - containing the queries.
  outputs.txt - containing the outputs.