GATE IT 2004 | Question: 75

Question

A relation $\text{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, $\text{Empdtl}$ is a relation in

• A. $\textsf{1NF}$ only
• B. $\textsf{2NF}$ and hence also in $\textsf{1NF}$
• C. $\textsf{3NF}$ and hence also in $\textsf{2NF}$ and $\textsf{1NF}$
• D. $\textsf{BCNF}$ and hence also in $\textsf{3NF}$, $\textsf{2NF}$ and $\textsf{1NF}$

Comments

As ‘none of the above’ option is not there so we have to assume that empcode contains no null values otherwise there won’t be any key in the relation.

---

For any pincode, there is only one city and state:-

this doesnt mean  pincode-->city,state

it means  city,state-->pincode

This can be observed by example

---

state,city,street → pincode is a transitive dependency as well right ?

Answer 1

It is in $\textsf{2NF}$ but not $\textsf{3NF}$. For $\textsf{2NF}$ all non prime attribute should be fully functionally dependent on key. Here key is empcode and contains only one attribute hence no partial dependency. But there is transitive dependency in this (pincode -> city, state). So it is not in $\textsf{3NF}$.

Answer: $B$

Comments

Before reading this. Read this post which tells the difference between candidate key and primary key:

https://dba.stackexchange.com/questions/171133/difference-between-candidate-key-primary-key

 

---

Can’t  emp_name+street+pincode be a candidate key for given FD’s.

If so then given relation is not in 2NF.

---

How is (pincode -> city, state) a transitive dependency ?

Answer 2

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

LHS of every FD is not super key and RHS is not prime attribute
From the FDs we can see that there are transitive dependencies, hence the table is not in 3NF.

Comments

Please explain through FDs what does ur last line convey i.e.

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

---

@Shubhanshu pincode -> city, state (non_key->non_key) causes Transitive dependency.

Answer 3

let assume
empcode(key) : 1
name              : 2
street              : 3 
city                  : 4 
state                : 5 
pincode          : 6

"For any pincode, there is only one city and state" : 6->45
"for any given street, city and state, there is just one pincode" : 345->6

total FDs
6->45
345->6
1->23456 ( becoz of 1 ia key so all others are functionally dependent on it)

CK is 1 
6->45 (non_key->non_key) hence 2NF
345->6 (non_key->non_key) hence 2NF
1->23456 (super key ->any) hence BCNF

therofore , relation is in 2NF and also in 1NF ( option B is correct)

Comments

Thats nice way to explain. Thanks !

Answer 4

answer should be B. Here CANDIDATE KEY is empcode (not primary key mind it). So pincode -> city, state is non-prime to non-prime, hence not in 3NF. And we can't find any partial dependencies. So it is in 2NF.