CSc 8710 Fall 2005
Homework 2 (Due: September 21, 2005 - Wednesday)

Consider the following relational database schema: 

  suppliers(SNO,sname,city)
  parts(PNO,pname,color,city)
  projects(JNO,jname,city)
  SPJ(SNO,PNO,JNO,qty)
Here SNO, PNO, and JNO in SPJ are foerign keys referring to corresponding attributes in the other three relations.
Write queries in Domain Relational Calculus (DRC), Datalog, Relational Algebra, and SQL for the following:
  1. Get all pairs of part numbers such that some supplier supplies both the indicated parts.
  2. Get all supplier-number/part-number/project-number triples such that the indicated supplier, part, and project are all located in the same city.
  3. Get all supplier-number/part-number/project-number triples such that the indicated supplier, part, and project are not all located in the same city.
  4. Get the supplier numbers of suppliers who supply the same part to all projects.
  5. Get the project numbers for projects supplied with at least all parts available from S1.
  6. Get supplier-number/part-number pairs such that the indicated supplier does not supply the indicated part.
  7. Get all pairs of supplier numbers, Sx and Sy say, such that Sx and Sy supply exactly the same set of parts each.
The Datalog, Relational Algebra, and SQL queries must be verified using Prolog, RA Interpreter, and Oracle SQL respectively.

Here is sample data etc. to get you started on this assignment:

  1. Datalog/Prolog Sample Data
  2. Relational Algebra Sample Database Directory
  3. SQL Script and Sample Data
  4. RA.jar for use on your PC
Here is sample data which I used to grade your HW2:
  1. Datalog/Prolog Sample Data
  2. Relational Algebra Sample Database Directory
  3. SQL Script and Sample Data