Consider the following relation: Faculty(facName, dept, office, rank,…

Concept. A relation is in 3NF if, for every non-trivial functional dependency X → A, at least one holds: X is a superkey, OR A is a prime attribute (part of some candidate key). A relation is in BCNF, a strictly stronger form, only if the left-hand side X of every non-trivial dependency is a superkey — the prime-attribute escape clause of 3NF is removed.

Application to this relation.

1. Derive the functional dependencies. Names are unique within a department, so (facName, dept) determines a faculty row: (facName, dept) → office, rank, dateHired. From the data each office sits in exactly one department (A101→Art, M201→Math, C101→CSC, …), giving office → dept.

2. Find the candidate keys. (facName, dept) determines every attribute, so it is a candidate key. Because office → dept, the set (facName, office) also determines dept and hence all attributes, so it is a candidate key too. Prime attributes are therefore facName, dept and office.

3. Test BCNF. Take office → dept. The determinant office is not a superkey, because one office is shared by several faculty (C101 is used by both Mohan and Tanuja), so office cannot fix facName, rank or dateHired. A non-superkey determinant violates BCNF.

4. Test 3NF. For the same office → dept, apply the prime-attribute escape: the right-hand side dept is a prime attribute (it is part of the candidate key (facName, dept)). 3NF permits this dependency, and no other dependency breaks the rule, so 3NF holds.

Cross-check. BCNF ⊂ 3NF always, so a relation can be in 3NF yet fail BCNF, but never the reverse. Here the single offending dependency office → dept fails BCNF (non-superkey determinant) but passes 3NF (prime right-hand side) — exactly the gap between the two forms.

Result: the relation is in 3NF but not in BCNF.