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

for the 1st doubt, the side where arrow is present is considered one side in question as far as i have seen.

point 2, i guess we can as there may be need where we want to have many relationships between 2 entities and those relationships have nothing in common in their meaning except they share primarry keys of entities on which they relate.

3 point , i also have doubt about this.

4 point as told above arrow side is considered as 1, so for E2 and E3, we can write like for one instance of E2 there exist a unique instance of E3 ( like functional dependency).

---

i guess!  the side where arrow is present is considered one side

---

@Pikachu and @Uddipto Could you please verify this substitution for Question 1 -

R1 ( Project managedBy Emp ) : Multiple projects are managed by one  Emp ( N : 1 )

                                                     Foreign key on the side of Project

R1 ( Dept managedBy Emp ) : Only One dept is managed by one  Emp  ( 1 : 1 )

                                                     Foreign key can be on any side

R1 ( Project managedBy Dept ) : Multiple projects are managed by one  Dept ( N : 1 )

                                                     Foreign key can be on Project side

R2 (  Emp has Dependents ) : Emp has many dependents ( 1 : N )

                                                Total participation from Dependents. Foreign key on Dependents side.

Min Tables : 4

---

For Question 1- (E1 R1), E2, E3, (R2, E4). Minimum tables = 4.

For Question 2- It is the case of one-to-one relation, with total participation at one end. So minimum relationship required will be 1. One candidate key and three alternate keys will be there. Minimum tables = 1.

---

@random Could you please explain how? The diagram does not consist of double line ( indication of total participation ) Then how could we combine into single table?

---

@yashgupta1992 In Question 2, there is a double line between E1 and R. That says all the entities of E1 are participating in the relation R. Morever, there is a one-to-one relation between E1 and E2, E3, E4. So we can use the key of relation E1 as the Candidate key in the merged relation and keys of E2, E3 and E4 as Alternate keys.

Note: In one-to-one mapping, usually R can combine to either of the entity set, but when one entity set has total participation, it can be combined into a single table

---

@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 .