Consider relation SUPPLY (S#, Sname, P#, Qty) with supplier names unique such that:
F: fd1: S#  Sname; fd2: Sname  S#; {S#, P#}  Qty
S# represents a supplier number, Sname represents a supplier name, P# represents a part number, and Qty represents the quantity of a specific part supplied by a specific supplier.

a. Is there a 3NF violation in SUPPLY? If yes, explain. If no, is there a BCNF violation in SUPPLY? Explain.
b. Decompose SUPPLY if necessary so that the resulting relational schema is in BCNF. Is your design attribute-preserving, dependency-preserving, and a lossless-join decomposition? Explain.

Transcribed Image Text:

SUPPLY S# S1 Qty 300 S1 SMITH P2300 400 200 100 100 S2 CLARK P300 S2 CLARK P2400 200 200 300 400 sname P# SMITH P1 S1 S1 SMITH P3 SMITH P4 S1 SMITH PS S1 SMITH P6 MORRIS P2 S4 MCNARY P2 S4 MCNARY P4 S4 MCNARY P5 S3