CSCI 5333.4 DBMS
Homework #3

Due date: 10/5

(1) Relational Algebra

This question is based on the Supply database we discussed in the class (with the relations Supplier, Part and Supply).

A drastically simplified database:

Supplier(SNum,  SName,  SCity,  Status)
Part(PNum, PName,  Color,  Weight)
Supply(SNum,  PNum, Quantity)

An example of relation instance:
Supplier:

Part:

Supplies:

Provide the relational algebra expressions in the standard notations for the following query.

(a) List all (City, Color) pairs such that ('CityA', 'ColorP') means that there is a supplier in CityA that supplies a part of ColorP.

(b) List the supplier names that supply both parts 'P1' and 'P2'.

(c) List the names of all suppliers that supplies every red part.

(d) List the names of all suppliers that supplies some parts but do not supplies part 'P1'.

(2) Redo (1) in relational algebra using the format of a relational algebra interpreter.

First, you need to install the relational algebra (aql.jar) interpreter found in: http://tinman.cs.gsu.edu/~raj/elna-lab-2010/ch2/.Make sure you read the manual: http://tinman.cs.gsu.edu/~raj/elna-lab-2010/ch2/ch2.pdf, especially section 2.2 on the relational algebra interpreter.

To turn in the answer for this question (and this question only), send an email to the TA with the subject heading: HW#3: <<Student_Last_Name>>, <<Student_First_Name>>. It should attach a file names HW3_RA_Query_<<Student_Last_Name>>_<<Student_First_Name>>.txt. The file should include the relational algebra expressions ready for copy and paste into the interpreter for the TA to test run. For example.

(1)

PROJECT [PNUM] (PARTS);

(2)

...

(PROJECT [SNUM] (SUPPLIER))
MINUS
(PROJECT [SNUM] (SUPPLY));

Conform to the naming convention strictly or you may risk not being test run successfully by the TA.

(3) Redo (1) in TORC.

(4) Redo (1) in DORC.