A relation Empdtl is defined with attributes empcode (unique), name, street,…
2004
A relation Empdtl is defined with attributes empcode (unique), name, street, city, state and pincode. For any pincode, there is only one city and state. Also, for any given street, city and state, there is just one pincode. In normalization terms, Empdtl is a relation in
- A.
1NF only
- B.
2NF and hence also in 1NF
- C.
3NF and hence also in 2NF and 1NF
- D.
BCNF and hence also in 3NF, 2NF an 1NF
Attempted by 151 students.
Show answer & explanation
Correct answer: B
Answer: The relation is in 2NF (and therefore 1NF), but not in 3NF or BCNF.
Reasoning:
Primary key and functional dependencies: primary key is empcode. From the problem statement we have the functional dependencies:
empcode -> name, street, city, state, pincode
pincode -> city, state
(street, city, state) -> pincode
2NF check: Because the primary key is a single attribute (empcode), there are no partial dependencies on part of a composite key. Therefore the relation satisfies 2NF.
3NF check: There is a transitive dependency: empcode -> pincode and pincode -> city,state, so city and state are transitively dependent on the key. This violates 3NF (non-key attribute determining another non-key attribute).
BCNF check: BCNF requires every determinant to be a candidate key. Here pincode determines city and state but pincode is not a key of Empdtl, so BCNF is not satisfied.
How to achieve 3NF: Decompose to remove the transitive dependency, for example:
Emp(empcode, name, street, pincode)
Location(pincode, city, state)