The Gateway to Computer Science Excellence
+11 votes
1.7k views

How many minimum relations required for given ER diagram ?

in Databases by Active (1.8k points) | 1.7k views
+1

I think the answer is 5. Check this 

https://gateoverflow.in/177106/er-diagram

0
i am getting 7 table five table for five entity and two table for  for R1 and R2  because it's M:N relationship.can anyone check my mistakes???
0

@srestha maam Please clarify. I cannot find a specific answer. 

First  E1(ABC) 

E4(A K L F) A is the foreign key from E1 for the relation R4,F is the foreign key from E3 for the relation R5

E2(DEF)  F is foreign key from E3 for 1:1 relation between E3 and E2(R3)

E'(AD   A is foreign key from E1 and D is foreign key from E2 we had to create E' for the many to many relation R1.

E3(FG) 

E4(FHIJ) here F is foreign key from E3 for 1:M relation between E3 and E5(R6)

E''(D1,D2) D1 ,D2 are foreign key from E2. for the recursive relation R2.

So in total we need 7 tables.

 

0

@Doraemon

why an entity will create a table?

Entity+Relationship creates the table.

right??

0

@srestha maam

"why an entity will create a table?"
What made u say so?Please explain

We need tables for entities to exist then only we can represent the  relationships between entities.

0
because u created table for $E_{1}$

right?
0

@srestha maam

1>I created the table for E1 because we have the relation R4 which is a 1:M relation from E1 to E4 ,
And made A the foreign key in E4 which is referencing to A( which is the primary key in E1).

2> we could  have joined tables E1 and E4 making attribute A in E1 the key. then the number of tables would basically reduce to 6 but we will have a lot of null values.

3>I think we have to have E1.
And E1  is also participating in the many to many relationship with  E2 so we definitely need to have E1.

 

 

 

0

@srestha please see this.

Could we have done it like this:

Table for E4

K L
k1 l1
k2 l2

k3

l1
 


Table for E1

A B C
a1 b1 c2
a2 b1 c1
a3 b2 c2
a4 b4 c4

Table for E3

E G
f1 g1
f2 g2

We could combine E1, E4,E3 ->represent the relations R5 and R4

Let us name the table E'

A B C K L F G
a1 b1 c2 k1 l1 f1 g1
a2 b1 c1 null null null null
a3 b2 c2 k2 l2 f2 g2
a4 b4 c4 k3 l1 null null

Table for E5:

H I J A(foreign key to E'(A)for the relation R6 )
h1 i1 j1 a1
h2 i2 j1 a1
h3 i2 j2 a3

Similarly, we will have a table for E2 which could have a foreign key A to E'(A) for the relation R3 

We need to have the table E''(D1, D2) D1, D2 are foreign key from E2. for the recursive relation R2.

E'''(AD)   A is foreign key from E'(A,B,C,K,L,F,G) and D is foreign key from E2 we had to create E' for the many to many relation R1.

 

So in total wee will have E'''(AD), E''(D1, D2),E2,E5,E'(A,B,C,K,L,F,G)

So total 5  tables .

0

@Doraemon

E2 has a many to many relation. Does it need separate table?

0

@srestha  maam

 I think it will have because we need a different table for many to many(E"'(AD))  and D will be a foriegn key from E2 .

otherwise how will we represent the relation R1?what is your opinion about it?

0
Any reference?

because , we know m:n participation generally takes $2$ tables
+1
m:n  always takes 3 tables unless its recursive relation.
0
no, it can merge in 2 tables. But question is, what about here, self referential table?
0
In m:n relation we can't combine the relationship table to either of the tables so we create a 3rd table for describing the relationship between 2 tables.

self- referential table case comes under recursive relations.

it will need 2 tables.
0

@srestha.                                                     How 2 tables can represent m:n?

We need 3 tables atleast.

Self referential can take 2minimum

Can u give an example in which 2tables are required?

https://www.geeksforgeeks.org/minimization-er-diagram/

Check this

+1

here tables will be

Table 1

A B
   
   

 Table 2

A A B
     
     

 here A is foreign key referencing A in table 1.

0

@Satbir

how many minimmum  tables are u getting for this ER Diagram?

+1
E1 and E4 are having 1:1 and one side total particpation so E1 and E4 can be merged to 1 table.

E3 and E5 are having 1:M and both side total particpation so E1 and E4 can be merged to 1 table.

E3 and E2 are having 1:1 and one side total particpation so E1 and E4 can be merged to 1 table.i.e.

E3,E5,E2 can be merged into 1 table.

To represent R1 we need one table which have 2 foreign keys one that points to E1 and other that points to E2 and connects them.

To represent R5 we can add a foreig key to (E1 E4) table which then connects E4 with E3.

R2 is a self referential relation so we need one more table which will have attributes of E2 and a foreign key that connects it to attribute of E2 which is present in E3,E5,E2 tabl.

hence we need 4 tables only.
0

13 Answers

+4 votes

hope  this helps...

by Active (2.5k points)
0
why we are combining $E2-R3-E3$ ?

Since E3 is not totally participating combining them will produce some redundancies
+2 votes

Total 8 tables are required

Let (EntityName,NumberOf Table)

(E1,1),(E2,3),(E3,1),(E4,1),(E5,2) 

E2 has 3 tables because in suppose we have 

Recursive Relationship -types of relationships in er diagram

So 3 tables required for E2 and E5 is weak entity so 2 tables required

weak entity type(entity and its types)

total = 1+3+1+1+2 = 8 tables 

Reference :-http://www.edugrabs.com/entity-and-its-types/

by Boss (11.7k points)
0
In case of E5 can't SSN be added to the table of CHILD instead of adding a seperate table for the relation between the two?
+2

check this

+1

@Nancy Pareta 

$R_4$ can be with either $E_1$ or $E_4$  but why you combined 3 of them as a  $Single$   table ?

0
In 1:1 if one side participation is total then we can merge them into 1
0
okay . got it
0
But where is it given that R4 is 1:1?
0
check arrows.

if u didn't get then i ll suggest to go through basics once of ER
+2 votes
answer must be 5 tables
by Active (1.2k points)
+2 votes
Ans is 5

{R1} {E1R4E2} {E5R6} {R2} {E3R3R5E2}
by Active (2.5k points)
+1 vote
5 for each entity set and 3 different tables for the relationships R1, R2 and R5 since they are many-to-many relationships.Thus, total 8.
by (133 points)
+1
R5 is a 1 to 1 relationship  given in diagram
0

E1(A,B,C)

R1(A,D)

E2(D,E)

R2(D,D')

E4R5(K,L,F)

E3(F,G)

E5(H,I,J,F)

R3(D,F)

NOTE: We can not merge $E_{1}R_{4}E_{4}$ Bcz in $R_{1}(A,D))$ $A$ have to refers to "PK" of $E1$ and if we merge  $E_{1}R_{4}E_{4}$ then ${A,K}$  will be PK of this table not just $A$

0
Reena... What is the problem in merging E1R4E4.. I mean in relation we get A nd K where K is on total participation side with no null values if we merge the table K alone can be the primary key of table..
0
@reena _kandari

If we merge E1R4E4 ,,K will be in new primary key,

But anyway in R1(A,D) A refers to PK which is A,,but in our new merge table K will be key,

So foreign key A is refering to A in E1 which is not primary key anymore now .

Is this possible ??
0 votes
by Active (1.8k points)
0 votes
7 tables
by Junior (849 points)
0
Even I am getting 7 tables

Can someone verify?
0 votes

R4 Relationship is not given, therefore, if we want to have minimum no. of tables, we can consider R4 as either 1:1 or 1:N Relationship. 

  • Entities - we have 5 Entities (E1, E2, E3, E4 and E5 (weak entity)). For each Entity, we have a Relation (or, table). Therefore, 5 Tables are required.
  • Attributes - Since, we don't have any Multi-valued Attribute, we don't require any Relation (or, table).
  • Relationship - Except R1, R2 (Both are many to many Relationship. R2 is recursive relationship) and R5 (a Identifying Relationship), all the other Relations are either 1:1 or 1:N, therefore, we don't require Relation (or, table) for these Relationships. We require a Relation (or, table) for many to many Relationship. Hence, two Relations (or, table) as we have two many to many Relationship (i.e. R1 and R2).

Total = 5 + 0 + 2 = 7

by Active (1.3k points)
edited by
Quick search syntax
tags tag:apple
author user:martin
title title:apple
content content:apple
exclude -tag:apple
force match +apple
views views:100
score score:10
answers answers:2
is accepted isaccepted:true
is closed isclosed:true
50,737 questions
57,384 answers
198,542 comments
105,343 users