Higher normal form decomposition

Question

$X$ is an entity set. $E$ and $F$ are multivalued attributes.

1. How many minimum tables which satisfy 1NF ?
2. How many minimum tables which satisfy 2NF ?
3. How many minimum tables which satisfy 3NF ?
4. How many minimum tables which satisfy BCNF ?
5. How many minimum tables which satisfy 4NF ?

Comments

• $1NF$ 
• CK  = {AEF,BEF} and $1$ table required
• But there is partial dependency in $A \rightarrow CD$ Or, $B \rightarrow CD$
• Creating new table $T1$ for $ABCD$ will remove this partial dependency.
• Then how to handle the MVDs ? 

---

@Debashish.

We would need seperate table for set FD and set MVD.

1 table for set FD.

1 table each for each MVD.

I am not getting from where are you getting partial dependencies.

---

WHat is MVD ?

Answer 1

lets start from scratch..
first deal with the multivalued attributes E and F
we need a separate table for them with either A or B ...means ABCD and AEF or BEF
now we are in 1NF with 2 tables

move towards 2NF...now from hereon we need FDs to further divide the tables..but since they are not mentioned we cant do anything and these 2 tables are by default in 2NF and BCNF too,,,

what if FDs were given?
then we need to check..but then also ABCD is in 2NF since A and B are 2 single attributes...there cant be any partial dependency...for 3NF there shouldnt be any FD like C->D or D->C else decompose...

for BCNF u shouldnt have any dependency like C->A or C->B or D->A or D->B...rest fine

so for ur question since no FDs were given finally u'll have 2 tables for 1NF,2NF,3NF,BCNF....else even if FDs were given it was in 2NF atleast rest u need to check for..

for 4NF...just split E and F also in 2 separate tables...

Comments

+1 for answer

Few doubts:

1. To my knowledge, we can select one of $CK$ = {$AEF$,$BEF$} as $PK$ and form a table and that table will be sufficient for $1NF$. (Because we accommodated multivalued attrib's in that table although table size increated)
2.  Assuming my $1$st point is true, then for $2NF$ we need to the separation of $EF$ to avoid $PD$. Here we create $T1$ table with $ABCD$ and one more table $T2$. (btw $T1$ is fine and in $BCNF$)
3. This 2nd table .  you are saying $E, F$ with either A or B.  This is what I was asking Vijay then.
4. Accommodating E and F with A or B, both ways we can handle the second table. Is that correct ?

---

when we make a table for multivalued attributes like AEF or BEF..whatever u put as attribute there they all are the CK combined..means AEF is the candidate key...so we dont have to deal with that table any further...just for 4NF separate them...

---

I got your point, for 4NF separation after $AEF$ or $BEF$. Then $AE$ and $AF$ will be created for $4NF$ assuming table as $AEF$. 

My doubt was very basic.. simply asking why not consider $A$ and $B$ both along with $E$,$F$ while separation..why we are considering either $A$ or $B$. Is this because of fact that the $E$,$F$ multivalued properties can be expressed even if we have at least one super-key from other table ( not necessarily all superkeys from other table ) ??

note : above statements may be invalid. please correct me if I am assuming something wrong.

---

yes u r right..any candidate key with the multivalued attributes will work..so either take A or take B...

---

Thanks !

---

So, in this QS: assuming FD's $2$ tables for $BCNF$ and $3$ tables for $4NF$ ?

---

assuming no FDs u mean...yes 2 for BCNF and 3 for 4NF...

Answer 2

R1={ABCD}

R2={AEF} OR R2={BFF}

Comments

here u are giving only one entity and all other are attributes.

So, here FD between attributes.right?

Is here A,B separately primary key?

or, AB combined primary key?

---

I donot think more A,B two separate primary key can be accepted by single entity.

Though according to ur question , I could say

as, AB primary key ,closure of AB could accept all other attributes

So, in 1NF (ABCDEF) - could accept in 1 table.

In case of 2NF , 3NF , 4NF there are 3 tables.

One table for X and other 2 tables for E and F

In case of BCNF , each attribute should be a super key.

So, In that case table could be more.

But in general there could be only 3 tables

---

is mvd in syllabus?