Second Normal Form

Question

 

Comments

Here (customerid,storeid) is a key. As they have not mentioned any FD's explicitly we can assume that FD

storeid -> storelocation exists. (It's quite logical too to assume that this FD exists) 

As storeid is a part of a key and storelocation is a nonprime attribute, this FD leads to the violation of 2NF.

Now if we are decomposing our table as (cid,sid,amount) and (sid,slocation) both the tables will be in BCNF.

It would also be a lossless and dependency preserving decomposition. So I think option C is correct!

---

@Utkarsh Joshi

why not b?

---

can anyone  explain me properly !

I have doubt in this question

---

himgta what doubt you have in option B?? can you explain??

---

note that, this is a non-standard question.. We have to make some assumptions to solve it..

by checking all the options and with some common sense, we get the FD

1) storeid ---> storelocation

2) customerid,storeid ----> billamount.

 

@himgta

note that all foreign keys are not acts as primary keys, but a relation should have a primary key, due to this reason, option c is preferred than option b

---

@Shaik Masthan in option B they are saying storeid of store refers to storeid of reciept correct?

---

yes, receipt is parent table and store is child table

---

but storeid is not a primary key in parent table! how can we do such a reference?

---

according to me option B should be the first one which we should eliminate! As its not proper reference!

---

i already commented that, it is not a standard question...

but note that any unique key can be work as referencable.

---

@joshi
Why option C is eliminate?

Note that, the storeid in the store table is primary key... It is referencing to the receipt table means receipt is child table and store is a parent table from option C

---

ans. will be c), and after that decomposition, it'll be in 2nf, 3nf and bcnf also.

---

here, storelocation is partially dependent on storeid,

and in 2NF we remove all the partial dependencies

so, we'll make a new table as STORE, where storeid, storelocation will be the attributesand storeid will be primary key, and in the RECEIPT table customerid, storeid, billamount will be the attributes, and customerid will be the primary key & storeid will be foreign key

---

mam, storeid is a foreign key in customer table but option b conveying that storeid is foreign key in store table.

isn't it?

---

Shaik Masthan TYPO* I meant option B should be the first to eliminate!! 

---

edited!

---

@ Shaik Masthan yes, option b) conveying to make the storeid as foreign key in the STORE table -- so, b) will be eliminated

---

storeid -> storelocation is partially dependent. then, if we remove storelocation from receipt table and make a store table with storeid and storelocation where storeid will be the primary key and in receipt table storeid will be referencing to the store table. then it'll be in 2nf.

and after that, no transitive dependencies are there. that's why it is already in 3nf, and bcnf also.

so, ans. will be c)

---

option A), B) & D) will be eliminated

ans will be option C)

---

@ Sukanya Das yes, after making storeid as a primary key in the STORE table, & if the customerid is the primary key & storeid is the foreign key in the RECEIPT table, then the relation will be in 2NF, 3NF and as well as BCNF

---

@ Subarna Das it is corrected now. ans. will be c)