Question

The relation EMPDT1 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 EMPDT1 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 and 1NF

The question was asked during an interview for a job.

My question is based upon Materialized Views topic in division Query Processing Techniques of Database Management

Answer 1

Right choice is (b) 2NF and hence also in 1NF

The best explanation: Empcode is unique, therefore it is the primary key. Since the primary key consists of a single attribute there will be no partial dependency, hence the relation is in 2NF.

From the question we get the FDs as below:

 pincode -> city, state

 street,city,state -> pincode

From the FDs we can see that there are transitive dependencies, hence the table is not in 3NF.