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.
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.
The issue with the above answer is introducing many NULL entries.
Now the question is whether NULL entries are allowed or not while converting the ER diagram into tables?
This screenshot is from FUNDAMENTALS OF DATABASE SYSTEMS by Ramez Elmasri and Shamkant B. N avathe.
Last Para suggesting that, we can have NULL.
in last para, it is saying that, a new table approach can be used in case of 1:N relation types to avoid excessive nulls in the foreign keys. Can is used in the statement. That means, we can still go for excessive NULLS depends upon our requirement.
However, I found some contradiction too in the same book.
This is contradicting. Here, they're saying that M:N relation must create a separate relationship relation. If NULLs are allowed, we always need not to be create a separate table for M:N relation ( i.e., atleast one side total participation case).
This screenshot is from Silberschatz−Korth−Sudarshan : Database System Concepts, Fourth Edition book, which specifically mentioned about Total participation only. Nothing about NULL values.
https://gateoverflow.in/218954/what-an-interesting-dbms-question
