edited by
11,064 views
31 votes
31 votes
Let $E1$ and $E2$ be two entities and $R$ is a relation between $E1$ and $E2$, then what is the minimum no of tables required to represent $E1, E2$ and $R$ if -

1. $E1$ and $E2$ have $1:m$ cardinality($E1$ on $1$ side, $E2$ on $m$ side); $E1$ has total participation and $E2$ has partial participation.

2. $E1$ and $E2$ have $1:m$ cardinality($E1$ on $1$ side, $E2$ on $m$ side); $E2$ has total participation and $E1$ has partial participation.

3. $E1$ and $E2$ have $1:m$ cardinality($E1$ on $1$ side, $E2$ on $m$ side); both $E1$ and $E2$ have partial participation.

4. $E1$ and $E2$ have $1:m$ cardinality($E1$ on $1$ side, $E2$ on $m$ side); both $E1$ and $E2$ have total participation.

5.$ E1$ and $E2$ have $m:n$ cardinality; $E1$ has total participation and $E2$ has partial participation.

6. $E1$ and $E2$ have $m:n$ cardinality; both $E1$ and $E2$ have partial participation.

7. $E1$ and $E2$ have $m:n$ cardinality; both $E1$ and $E2$ have total participation.

8. $E1$ and $E2$ have $1:1$ cardinality; $E1$ has total participation and $E2$ has partial participation.

9. $E1$ and $E2$ have $1:1$ cardinality; both $E1$ and $E2$ have partial participation.

10. $E1$ and $E2$ have $1:1$ cardinality; both $E1$ and $E2$ have total participation.

Assume that there is no multi-valued attribute is present in any of the $10$ cases.
edited by

2 Answers

Best answer
40 votes
40 votes

I’m assuming minimum requirement is 1NF.

 

1) if relationship is many to many and both entities are partially participation

        you can't merge ===> require 3 tables

2) if relationship is many to many and  either of the entities are partially participation but not both side

        you can't merge ===> require 2 tables

3) if relationship is many to many and both entities are total participation

        you can merge all in one table and key of the relation is pk(E1)+pk(E2) but data is redundant and so many partial functional dependencies you get but not transitive dependencies

Why we do normalization ? 

   By normalization tables get increased then what you achieved by merging the tables


1) if relationship is many to one and both entities are partially participation

        you can't merge in one table  ===> 2 tables required

2) if relationship is many to one and many side entity is only partially participation

        you can merge in one table  ===> redundancy and transitive dependencies get but not partial functional dependencies.  because of pk(new table)=pk(E1)

3) if relationship is many to one and one side entity is only partially participation

        you can't merge in one table ===> 2 tables required

4) if relationship is many to one and both entities are totally participation

you can merge in one table  ===> redundancy and transitive dependencies get but not partial functional dependencies.  because of pk(new table)=pk(E1)


1) if relationship is one to one and both entities are partially participation

        you can't merge in one table ===> require 2 tables

2) if relationship is one to one and  either of the entities are partially participation

        you can merge in one table ===> but pk of resultant table should be pk of partial participation otherwise you require 2 tables.

3) if relationship is one to one and both entities are total participation

        you can merge all in one table and pk(new table)=either pk(E1) or pk(E2) is sufficient.




let E1 have m entities and E2 have n entities then no.of tuples in the relation is

MANY-TO-MANY

i)  both are partial participation ===> min=3, max= mn-(m+n)+1 
ii) only one (E2 side ) is partial participation ===> min=m+1, max= mn-m 
iii)   both are total participation ===> min=max(m,n)+1, max= mn

MANY-TO-ONE

 i)  both are partial participation ===> min=2, max= m-1 
ii) only one side is partial participation ===> min=max= m
iii) only many side is partial participation (m>(n+1))===> min=n+1, max= m-1 
iv) both are total participation (m>n)===> min=max= m


ONE-TO-ONE

 i)  both are partial participation ===> min=1, max= min(m-1,n-1) 
ii) only E2 side is partial participation (therefore m<n) and merge as one table ===> min=max=m 
iii)   both are total participation (therefore m=n)  ===> min=max= m

edited by
16 votes
16 votes

This might help.Source : GeekForGeeks.     

Related questions

2 votes
2 votes
3 answers
1
Balaji Jegan asked May 3, 2018
979 views
How many minimum number of tables are required for this ER Diagram?
0 votes
0 votes
1 answer
3
0 votes
0 votes
1 answer
4