in Databases edited by
12,939 views
53 votes
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

  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}$
in Databases edited by
12.9k views

4 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.
0
0
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
0
0
state,city,street → pincode is a transitive dependency as well right ?
0
0

6 Answers

51 votes
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$
edited by

4 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

 

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

If so then given relation is not in 2NF.
0
0
How is (pincode -> city, state) a transitive dependency ?
0
0
27 votes
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.
by

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

0
0

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

1
1
18 votes
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)

1 comment

Thats nice way to explain. Thanks !
0
0
1 vote
1 vote
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.
Answer:

Related questions