Consider the following relation: Faculty(facName, dept, office, rank,…
2017
Consider the following relation: Faculty(facName, dept, office, rank, dateHired).
facName | dept | office | rank | dateHired |
|---|---|---|---|---|
Ravi | Art | A101 | Professor | 1975 |
Murali | Math | M201 | Assistant | 2000 |
Narayanan | Art | A101 | Associate | 1992 |
Lakshmi | Math | M201 | Professor | 1982 |
Mohan | CSC | C101 | Professor | 1980 |
Sreeni | Math | M203 | Associate | 1990 |
Tanuja | CSC | C101 | Instructor | 2001 |
Ganesh | CSC | C105 | Associate | 1995 |
Assume that no two faculty members within a single department have the same name, that each faculty member has only one office, and that each office belongs to exactly one department (so office determines dept). 3NF refers to Third Normal Form and BCNF refers to Boyce-Codd Normal Form.
Then Faculty is:
Answer: B. In 3NF, not in BCNF — 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…
- A.
Not in 3NF, in BCNF
- B.
In 3NF, not in BCNF
- C.
In 3NF, in BCNF
- D.
Not in 3NF, not in BCNF
Attempted by 454 students.
Show answer & explanation
Correct answer: B
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.
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.
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.
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.
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.
Common pitfall. It can look like office alone determines everything (office → facName, rank, dateHired) once office → dept is established, but the data does not license that: office C101 is shared by two different faculty rows — (Mohan, Professor, 1980) and (Tanuja, Instructor, 2001) — same office, yet different facName, rank and dateHired. So office fixes only dept, never the rest; it is the sole non-superkey determinant, among the relation's stated dependencies, that separates 3NF (which tolerates it here) from BCNF (which does not).
Cross-check. BCNF ⊂ 3NF always, so a relation can be in 3NF yet fail BCNF, but never the reverse. Here the single offending dependency among those stated, 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.
A video solution is available for this question — log in and enroll to watch it.