CSCI 5333.3 DBMS
Homework #3
Due date: 2/29 (Wed)
(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:
SNum | SName |
SCity |
Status |
S1 |
ABC |
Dallas |
10 |
S2 |
DEF |
Houston |
20 |
S3 |
Go go |
Houston |
12 |
S4 |
P&G |
Dallas |
2 |
S5 |
Yap |
Phoenix |
5 |
S6 |
Yue |
Dallas |
1 |
Part:
PNum |
PName |
Color |
Weight |
P1 |
Drum |
Green |
10 |
P2 |
Hammer |
Green |
20 |
P3 |
Minipod |
Red |
4 |
P4 |
Micropod |
Red |
4 |
P5 |
Blue Spur |
Blue |
3 |
P6 |
Musical Box |
Blue |
13 |
P7 |
Bear |
Blue |
9 |
P8 |
Panda |
White |
10 |
Supply:
SNum |
PNum |
Quantity |
S1 |
P1 |
10 |
S1 |
P2 |
3 |
S2 |
P1 |
11 |
S2 |
P2 |
1 |
S2 |
P4 |
6 |
S3 |
P4 |
1 |
S3 |
P5 |
2 |
S3 |
P6 |
12 |
S3 |
P7 |
5 |
S4 |
P2 |
1 |
S4 |
P5 |
10 |
S4 |
P7 |
4 |
S4 |
P8 |
10 |
S5 |
P1 |
11 |
S5 |
P3 |
5 |
S5 |
P4 |
10 |
S5 |
P5 |
14 |
Provide the relational algebra expressions in the standard notations for the following query.
(a) List all citiy names with a supplier supplying a green part.
(b) List the supplier numbers of all suppliers that more than three counts of any green part.
(c) List the part number of parts supplied by every supplier in Houston.
(d) List the names of parts with a supplier with status greater than 4.
(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.