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

If we do not consider empcode attribute as a primary key(as not given in the Q.) then,​

 

we have to find the candidate keys form the given FDs and the FDs are==>

 

P -> C 

 

P -> Sta

 

St,C,Sta -> P 

 

 So candidate keys will be ==> (E,N,St,P) and (E,N,St,C,Sta).

 

So all attributes will be prime attributes.

 

1. Checking for BCNF ==> LHS of the FDs are not superkeys so it will not be in BCNF.

 

2. Checking for 3NF ==> as all attributes are prime attributes so the relation Empdtl will be in 3NF.

 

 

 

Then ​​​​The ans will be (c). But if it is mandatory to assume the unique attribute as primary key , then the ans will be (b).

Answer 2

pincode -> city, state
street,city,state -> pincode
 

The relation has no partial dependency. So, it is in 2NF

Now, check for 3NF

For being transitive dependency, there should be a nonprime->nonprime dependency will be there.

But here no such dependency.

Moreover if a relation is in 3NF, then either RHS each relation will be in prime attribute, or LHS of each relation will be super key.

Here , these condition satisfies. So, relation is in 3NF Ans D)

Comments

what is primary key(Unique and not Null)

if we don't consider NULL in relation

empcode is unique as given so it can uniquely determine every attribute of the relation so it is the candidate key of the relation

given functional dependencies are 

1)  pincode -> city, state   (non prime ------> non prime) not in 3nf
2)  street,city,state -> pincode  (non prime ------>non prime) not in 3nf 
as we can see no partial dependencies in both FD's so we can conclude that relation is in 2nf

a relation is not in 2nf if partial dependencies exists but not there so 2nf

a relation is in 3nf if either right side of fd is super key or left side of fd is prime attribute ,we can see in above both fd does'nt satisfies this properties so not in 3nf

---

thank u

---

(Y) .....

---

also contain 1 more FD that is empcode-----> name,street,city,state,pincode

pincode -----> city,state

street,city,state ------> pincode

here CK is empcode and no partial dependency

b/c simple CK does not contain partial dependency