The relation (Roll_number, Name, Date_of_Birth, Age) is: A. in second normal form but not in third normal form B. in third normal form but not in BCNF C. in BCNF D. in none of the above

GATE 2004 · Databases · Database Normalization · medium

Answer: The relation is in 2NF but NOT in 3NF, because Date_of_Birth -> Age is a transitive dependency. Answer: A.

1. Identify the candidate key and check 2NF: The only candidate key is Roll_number (a single attribute). Since the key is not composite, there can be no partial functional dependencies. Name, Date_of_Birth, and Age all fully depend on Roll_number. Hence the relation is in 2NF.
2. Check for transitive dependencies (3NF test): Date_of_Birth -> Age holds because Age can be computed from Date_of_Birth. Both Date_of_Birth and Age are non-prime attributes (not part of any candidate key). This gives the transitive chain: Roll_number -> Date_of_Birth -> Age. A non-key attribute (Age) depends on another non-key attribute (Date_of_Birth), violating 3NF.