WHAT AN INTERESTING DBMS QUESTION!!

Question

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.

Comments

@srestha yes, participation makes difference

 

@ why case 3 is invalid,

let that relation is R(A,B) and A is Foreign Key from Many Side and B is Foreign Key from One side... Make A as a Primary Key of R... Then what is the problem?

---

https://gateoverflow.in/229580/madeeasy-test-series-number-of-tables-required

---

My answers:

1 2 2 1 3 3 1 1 2 1

Answer 1

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

Comments

Shaik Masthan

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

Doesn't it require 3 tables ???? 

---

NO.

---

Is it possible in RDB ???

Suppose in a table AB is the pk & B is the FK which points to some other table's B which is Pk of that table???

Answer 2

This might help.Source : GeekForGeeks.     

Comments

thanks a lot

---

Satisfactory answer

---

Is this valid irrespective of any normal form and even when attributes are multivalued

---

I think not bcz if we normalise then we might have to decompose(and we will have more tables) based on FD to reduce redundancy. Here there might be redundant data(but min amount of tables).