Dark Mode

12,939 views

53 votes

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

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

0

0

51 votes

Best answer

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$

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

0

0

27 votes

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.

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.

0

18 votes

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)**