Consider the following relational schema: Suppliers(sid:integer, sname:string,…
2009
Consider the following relational schema:
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?
- A.
The schema is in BCNF
- B.
The schema is in 3NF but not in BCNF
- C.
The schema is in 2NF but not in 3NF
- D.
The schema is not in 2NF
Attempted by 157 students.
Show answer & explanation
Correct answer: A
Answer: The schema is in BCNF.
Reasoning: List the relevant functional dependencies implied by the given keys and check the BCNF condition (every nontrivial FD must have a superkey on the left).
Suppliers: keys are sid and (sname, city). So sid -> sname, city, street and (sname, city) -> sid, street. Each determinant (sid and (sname, city)) is a key, so Suppliers satisfies BCNF.
Parts: pid is the key, so pid -> pname, color and the determinant is a key. Parts satisfies BCNF.
Catalog: the composite (sid, pid) is the key and (sid, pid) -> cost. The determinant is a key, so Catalog satisfies BCNF.
Because every nontrivial functional dependency in each relation has a superkey on the left, all relations are in BCNF and therefore the whole schema is in BCNF.
A video solution is available for this question — log in and enroll to watch it.