12,939 views

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

1. $\textsf{1NF}$ only
2. $\textsf{2NF}$ and hence also in $\textsf{1NF}$
3. $\textsf{3NF}$ and hence also in $\textsf{2NF}$ and $\textsf{1NF}$
4. $\textsf{BCNF}$ and hence also in $\textsf{3NF}$, $\textsf{2NF}$ and $\textsf{1NF}$

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 ?

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$

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 ?
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.
by

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."

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

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)

1 comment

Thats nice way to explain. Thanks !
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.