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

How transitive dependency hold true here???????????

---

Even I think it should be B . I couldn't find any partial dependency . Were you able to find any?

---

non-prime $\rightarrow$ non-prime is not 3NF but it is in 2NF.

---

how empcode is primary key by just the information empcode(unique).

UNIQUE key may contains NULL, moreover it can contains multiple NULL, as NULL is unknown or like NULL contains some garbage value which is not equals to any other NULL.

---

Candidate keys are used in FD.

Candidate keys can be NULL. no need to see for primary key.

see the comments of arjun sir in the best answer.

---

Why isn't  (Name, Pincode, Street) and (Name, City, Street, State) is also candidate keys?

---

How can u get empcode.

Ck is such a key,by which you can get all attributes in table(i.e using CK you can uniqely identify a tuple)

---

I am confused here even after reading all comment how can we say unique key as Primary key as we know that unique key can have one null value . So can anyone just explain this point if null is in unique key than how we can conclude this as primary key ?

---

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.