Understanding How to Identify Number of Tables for ER Diagram

Question

Doubts -

1) When a ER diagram is given and no cardinalities are provided. How to determine relationship type 1:1 or 1:N or M:N ?

    Generally in such ER diagrams arrows are given. Referring to this article where arrow is a Bachman notation   http://stackoverflow.com/questions/4755497/entity-relationship-diagram.

Is the following interpretation correct : The side on which an arrow is present represents N side relation

2) Can there be two different relations among two enties?

    An example I can think of is Emp worksFor Dept ( M:N relation Many employees can work for many Dept )

   Emp manages Dept ( 1:N One emp manages more than 1 department. But at any time given a department can be managed by only one emp)

3) When there is total participation (double line) on both sides of the Entities. In order to minimize tables should they be combined into single table?

4) Also how to understand ternary relation? Is there a relationship between E3 and E2 via R1 in below example ?

Question 1 -

Question 2 -

Comments

@Bikram sir,for question 2 how many tables wil required?

either 1 or 2?

---

@ reena_kandari

For question #2 ,  minimum 3 tables are required .

1 table for E2

1 table for E3

1 table for R E4

1:1 with only single side total participation we always take 1 table . so total 3 tables in minimum. 

---

@Bikram Veteran

we cannot always merge R and E4 , the cardinality constraints dont violate the fact that , there may be tuples in R4 not participating in the relation R. When you combine E4 ( or any other table) such non-participating tuples are also included and make the merged table undefined.

Thus , in my view there must be minimum 4 tables for the ques 2 . (each for each entity and cardinalities handled by foreign keys in any one of the relations for each pair of relations).

Answer 1

To understand your

Doubt # 1)  When a ER diagram is given and no cardinalities are provided. How to determine relationship type 1:1 or 1:N or M:N ?

When a diagram is given , we need to check that diagram , see below image from korth :

it clearly says the arrow side means 1 cardinality .  which you wrote reversely in your question . It would be  " The side on which an arrow is present represents 1 side relation" .

For question 1, minimum table require is 3 . ( for Many to one we need 2 tables, for 1:1 with total participation at one side we need 1 table so in total 3 tables require )

----------------

And for 2 nd question , there is a mistake in diagram , see below picture :

There is no arrow with double line to represent total participation . Only double line is require like that = 

But in question between E1 and R , there is a double line with arrow , which is not correct. there should be no arrow to represent total participation. 

And for question 2 minimum table require is 3 ( 1:1 with only single side total participation we always take 1 table) .

-----------

For your doubt #3 : When there is total participation (double line) on both sides of the Entities. In order to minimize tables should they be combined into single table ?

yes, we can minimize them into a single table .

Comments

Sir, please correct me on this.

If we have E1, E2 as entities, and R as relation which is 1:1

a) If there is total participation on both sides, we make one table, R(P1, P2), with the primary keys P1, and P2 of the entities E1 and E2.

b)What if there is total participation on one side only? How many tables do we get? According to the link http://www.techtud.com/sites/default/files/public/lec7.pdf , if there is total participation on E1, we take E1 as the base and use the primary key of E1 in table E2. And here we get two tables, E1 and E2.

c) What if there is partial participation on both sides? Here also we get two table, E1, and E2?

Answer 2

for ur 3rd question

ans: if er diagram is 1:1 relationship with total participation then we can combine both relations to minimise the table

consider the exmaple: customer and license here the participation of both the entities is full and every customer should have only one license and every license should be owned by only one customer hence it is one to one relationship and participation is also total on both sides

hence here we dont need 2 tables one for customer and other for license we can maintain it in just 1 table by combining both the tables.

Comments

• If both the entities having total participation in 1:1 relationship then we only require 1 table named of relation this is the case in which we can say that only one table is required.

http://www.techtud.com/sites/default/files/public/lec7.pdf page 18 with highlighted part that supports this statement.

• If there is total participation on any one side between two entities  in 1:1 relationship then we need at least 1 table and we can move the P.K onto the total participation side. 
• Reference for this click this ppt 
• http://www.geeksforgeeks.org/minimization-er-diagram/ ( there should be no arrow in total participation, notation use in this link is not correct for 1:1 total participation , only " = " should be there.)

• 1:1  with both sides partial participation require 2 tables .