Relation R has eight attributes ABCDEFGH. Fields of R contain only atomic…
2013
Relation R has eight attributes ABCDEFGH. Fields of R contain only atomic values.
F = {CH → G, A → BC, B → CFH, E → A, F → EG} is a set of functional dependencies (FDs) so that F⁺ is exactly the set of FDs that hold for R.
The relation R is:
- A.
in 1NF, but not in 2NF
- B.
in 2NF, but not in 3NF
- C.
in 3NF, but not in BCNF
- D.
in BCNF
Attempted by 19 students.
Show answer & explanation
Correct answer: A
Concept
Normal forms are defined relative to the candidate keys of a relation. An attribute is prime if it belongs to some candidate key, otherwise non-prime. 2NF forbids a partial dependency: no non-prime attribute may depend on a proper subset of any candidate key. So the test is: find every candidate key, label prime vs non-prime attributes, then check whether any non-prime attribute is determined by part of a key.
Finding the candidate keys
An attribute that never appears on the right-hand side of any FD cannot be produced by any closure, so it can never be derived from other attributes and must therefore appear in every candidate key. Here D appears on no right-hand side (and on no left-hand side either), so every candidate key must contain D.
Compute attribute closures (each step adds attributes forced by an FD whose left side is already included):
(AD)⁺: A → BC adds B, C; B → CFH adds F, H; F → EG adds E, G. With D this gives all eight attributes → AD is a key.
(BD)⁺: B → CFH → then F → EG → then E → A; with D this closes to all eight → BD is a key.
(ED)⁺: E → A → then A → BC, B → CFH, F → EG; with D closes to all eight → ED is a key.
(FD)⁺: F → EG → then E → A → A → BC, B → CFH; with D closes to all eight → FD is a key.
Candidate keys: AD, BD, ED, FD. Prime attributes (appear in some key): A, B, D, E, F. Non-prime attributes: C, G, H.
Applying the 2NF test
Take the FD A → BC. A is a proper subset of the candidate key AD, and C is a non-prime attribute. So a non-prime attribute (C) depends on part of a key (A). This is exactly a partial dependency, which 2NF prohibits.
Because atomic values are given, R is in 1NF; but the partial dependency breaks 2NF, so R is in 1NF and not in 2NF.
Cross-check
If a relation fails 2NF it cannot satisfy any stronger form, so 3NF and BCNF are automatically ruled out. A second witness of the partial dependency is B → CFH: B is part of key BD and C, F, H reach non-prime C as well, confirming the violation independently.