Direction: Consider the following relational schemas :
Suppliers (sid : integer, sname : string, city : string, street : string)
Parts (pid : integer, pname : string, color : string)
Catalog (sid : integer, pid : integer, cost : real)
-
Assume that, in the suppliers relation above, each supplier and each street within a city has a unique name, and (sname, city) forms a candidate key. No other functional dependencies are implied other than those implied by primary and candidate keys. Which one of the following is true about the above schema?
-
- The schema is in BCNF
- The schema is in 3NF but not in BCNF
- The schema is in 2NF but not in 3NF
- The schema is not in 2NF
- The schema is in BCNF
Correct Option: A
As given that each supplier and each street within a city has a unique name, it means (same ,city) and (street ,city) combination is unique. So these will be candidate keys. There is no other FD other than CK determining attributes. In all FD, LHS will be SK.
Example:
Hence 2 candidate Keys are possible
(1) (Sname, city) ⇒ (QR)
(2) (Street, city) ⇒ (SR)
⇒ The relation suppcier is in BCNF So the relation should be in BCNF.
