CSCI 5931A.1
Advanced Database Development
Summer 1999
Mid-Term Examination
Name: _____________________________ Grade: ______________________
Attempt all questions.
Time: one hour and 45 mintues
Open: lecture notes only.
Return both question and answer sheets.
(1) Consider the following UML class diagram (10 points, 5 each):
(a) Convert the class diagram to the relational model. For each relation, specify:
(2) Consider the simple Supplies database with an instance shown below. (20 points, 5 each)
Suppliers
SNUM SNAME
SCITY
STATUS
---- ------------------------- -------------------- ---------
S1 Supplier #1
Houston
5
S2 Supplier #2
Houston
4
S4 Supplier #4
Houston
3
S5 Supplier #5
Houston
9
S6 Supplier #6
Houston
10
S7 Supplier #7
Dallas
7
S8 Supplier #8
Dallas
4
S9 Supplier #9
New York
2
S10 Supplier #10
New York
9
S11 Supplier #11
New York
3
S12 Supplier #12
Houston
6
S13 Supplier #13
Houston
2
Parts
PNUM PNAME
COLOR
WEIGHT PCITY
---- ------------------------- --------------- --------- --------------------
P1 Part #1
RED
10.5 Houston
P2 Part #2
RED
9.5 Houston
P3 Part #3
GREEN
4.5 Houston
P4 Part #4
GREEN
11.5 Dallas
P5 Part #5
BLUE
1.5 Dallas
P6 Part #6
BLUE
2.5 New York
P7 Part #7
RED
3.5 New York
Supplies
SNUM PNUM QUANTITY
---- ---- ---------
S1 P1
10
S1 P2
50
S1 P3
40
S1 P4
20
S1 P5
9
S1 P6
15
S1 P7
18
S2 P1
100
S2 P3
200
S2 P4
105
S4 P2
25
S4 P4
33
S4 P5
3
S4 P6
1
S4 P7
150
S5 P3
180
S5 P5
200
S6 P1
210
S6 P2
220
S6 P3
107
S6 P5
108
(a) Write a SQL statement to return all supplier numbers that does not supply any part supplied by 'S5'. In the above example, it should include all supplier nums not supplying 'P3' or 'P5'.
(b) Write a stored function GetPartCount(In_PNum) that returns the total inventory count of In_PNum. For example, GetPartCount('P1') for the above instances returns 320.
(c) Write a stored function Superset_Supplier(In_SNum_Left, In_SNum_Right) that returns true if and only if In_SNum_Left supplies every part that In_SNum_Right supplies. For example, Superset_Supplier('S1', 'S2') returns true and Superset_Supplier('S4', 'S5') returns false.
(d) Write a trigger to maintain log change in supplier status when a supplier status is updated. Assume that the table SupplierStatusLog has been created:
drop sequence SupplierStatusLogSeq;
create sequence SupplierStatusLogSeq
start with 100
increment by 1;
drop table SupplierStatusLog;
create table SupplierStatusLog(
LogId
NUMBER(8) Primary key,
SNum
CHAR(4),
NewStatus NUMBER(2),
DeltaStatus NUMBER(2),
LogDate
DATE
);
drop table Mono;
create table Mono (
MonoId NUMBER primary key
);
insert into Mono values (1);
An example session:
SQL> select * from SupplierStatusLog;
no rows selected
SQL> update Suppliers
2 set Status = 10
3 where SNum = 'S1';
1 row updated.
SQL>
SQL> update Suppliers
2 set Status = 4
3 where SNum = 'S1';
1 row updated.
SQL>
SQL> select * from SupplierStatusLog;
LOGID SNUM NEWSTATUS DELTASTATUS
LOGDATE
--------- ---- --------- ----------- ---------
100 S1
10 5 21-JUN-99
101 S1
4 -6 21-JUN-99
SQL>