Test by Bikram | Databases | Test 1 | Question: 1

Question

 

What is the minimum number of tables required to represent this E-R diagram in the relational model? Considering that  for both R1 and R2 cardinality is $1:1$

• A. $2$
• B. $3$
• C. $4$
• D. $5$

Answer 1

Question is bit confusing. Number of tables depends upon  given cardinality, but it is not specified here that weather generated schema have to be normalized or not.

Here  that relationship R2 is one to one with partial relationship on both sides, (identification: single line, each 1-to-1 and many-to-many is basically partial relationship. bcz that specify one member can be connected with how much number of other side, it never says that it must use every member) (P.S.: with arrow, without arrow for 1-to-1 and many-to-many changes with different sources.)

and relationship R1 is one to one relationship with total participation on both sides (identification: double line)

Now CASE 1: tables do not need to be normalized

In that case obviously A_R1_B needs 1 table as total participation. Now as null can be there in tables, we can make 1 table for C and then connect R2 on either side.

A_R1_B_R2 (C comes here as foreign key), C

or

A_R1_B, R2_C (B comes as foreign key)

So total 2 tables.

(thanks to @reena_kandari, @shubhanshu for clearing idea)

Now coming to doubt that if total participation requires 1 table and partial participation requires 2 tables then why not total being 3? try to understand logic in following image. here complete total participation behaves as one whole entity in R2 relationship and C as another side of R2 relationship. And so as per default rule R2 can merege on either side.

CASE 2: tables has to be normalized

Now i have doubt here that weather after normalization, nulls are allowed in tables or not? I referred many references and asked many fellows but i didn't get any firm side.

So if normalized tables allows null values then 2 tables same as above.

But if normalized tables do not allow null values then we can't merge R2 on either side bcz due to partial participation it will generate null values for sure.

So in that case, we need A_R1_B 1 table, C 1 table and R2 1 table (having B and C as foreign key).

So in total 3 tables required.

So answer can be 2 or 3 depending on question details.

Comments

R1 is one to one relationship with total participation 

---

How to interpret the arrows?

---

Single arrow R2 is one to one relationship

Double arrow R1  is one to one relationship with total participation 

---

@Bikram sir What is the correct interpretation of double arrow  because there is a ACE-test series question in which it is interpreated as total participation with one to one relationship .. So now its a big confusion here.

---

We represent Total Participation using Double line it is standard notation.

One to one relationship is used using single line with arrow , it is standard notation.

https://en.wikipedia.org/wiki/One-to-one_(data_model)

Many to many relationship is used using without arrow with a single line.

https://en.wikipedia.org/wiki/Many-to-many_(data_model)

---

In that ACE question they give total participation with one to one relationship .

---

from korth book

---

Now the total table required must be 2 one for A-R1-B and one for another one?

---

Three Tables are required for A,B and C only  after that R1 requires one table  as it is one to one and also R2 is one to one so no table the answer should be 4

why are we not considering entities here?

https://gateoverflow.in/3717/gate2004-it-73

Here also we are taking tables for Entities?

Please explain

@Bikram sir

---

@Abhinav93  

That was a different case, see that question carefully https://gateoverflow.in/3717/gate2004-it-73

more condition also there in that Gate question.

In this question 2 is correct answer.

---

@ Bikram Sir here we do not care about normal form that is why the answer is 2?

For example if hey would have said "What is the minimum number of tables required to represent this E-R diagram in the relational model that satisfy 3nf" then the answr should have been 4?

---

yes .

here we care only cardinality not any normal forms.

And your assumption is right i think .

---

@Abhinav93

 "What is the minimum number of tables required to represent this E-R diagram in the relational model that satisfy 3nf" 

For above query, ans should be 3, but how 4???

---

Strong entitities always have a table no matter what. So why is this not followed here?

---

@abhishek

How you can say A, B and C are strong entities ? where you read Strong entities always have a table , need to know the source .

---

I can say A,B and C are strong entities since they are represented in the question as a rectangle box with no border and regarding strong entities always have table, I haven.t read it in any book, but saw it in various simple problems in books.

---

@abhishek

There is a snap from korth , in comment i posted that snap, you can check it .

Based on cardinality we define how many tables needed . That korth-snap also supports that .

---

@ Abhisek Das  

"Strong entities always have a table no matter what " ===> this is wrong statement .

Example: if I have E-R diagram for a relationship(<person > having <license> ) for an entity <person> and entity <license> , here both will have full participation and one person can not have more than one license number and a single license number can be assigned to only one person.

So, while preparing the table, we will need only a single table, each row containing attribute of both <person> and <license>.

so your assumption is not correct. Depends on cardinality we make tables.

---

@Bikram sir,Here 2 tables will required?

as cardinality of A-R-B is not given so for minimum case we can take it 1:1 .

so one table for A-R-B and one for C.

---

@reena

For that ACE question , you posted snap, your answer was correct, it is 4 tables.

Their answer given 2 which is wrong.

--------

In this question, R1 have total participation on both sides so it needs 1 table.

R2 have partial participation on both sides so it needs 2 tables.

Here both r1 and r2 we assume in 1:1 cardinality . so total 3 tables require.

---

@Bikram Sir, I think R1 has many to many mapping since it doesn't have arrows on its either sides as like R2.

And if it is so then A-R1-B all will be in different tables. So for R1 number of tables should be 3

And B-R2-C which is 1:1 it needs 2 tables.

So all the relations are as follow:-

A, R1, BR2, C

OR

A, R1, B, R2C

SO, the answer should be 4.

ryt?

---

@Shubhanshu

R1 have both sides total participation , that require 1 table. No arrow represent total participation also like ' = ' this .

R2 have both sides partial participation , require 2 tables.

so in total 1+2 = 3 tables.

---

@Bikram Sir, please clarify what is the mapping of R1 and R2.

because in @Sheshang answer, he is considering both with arrow and without arrow as one to one mapping it is confusing.

---

yes, both R1 and R2 is in 1:1 .

see this image 

Double line means total participation , unless mention we consider it 1:1 only .

Single line means partial participation, unless mention it is also 1:1 only.

Now depends upon entity and their relationship those cardinality changes. Here only A ,B ,C is given unlike any real world entity so here cardinality is 1:1

---

Ok, so that diagram given in the question must be corrected because it should contain arrow on both sides of relation R1 as like R2.

then it will be ok.

---

@shbhanshu

In any book you will never found double line with arrow represent total participation with 1:1 relationship. Not even in any Gate paper this kind of notation is used.

Double line like this '=' only represents total participation. This notation is used in books and gate papers itself.

and ' -->' represents 1:1 cardinality .

symbol like this  a single line     

used as partial participation .

In this question from  this notation     it means R2 in partial participation . And in a relationship with both sides partial participation, we need 2 tables  that's why for R2 we need 2 tables.

---

@Bikram sir, suppose entity A=Aadhar card

B=Person and C=car,

R1=every person must have a aadhar number,and every aadhar is related to only one person(1:1 with total participation)

R2=A person may have maximum 1 car and a car may not be related to any person(1:awith partial participation)

So,we can make a table for A-R1-B, no entries will be null and every person will participte in this relation.---->1 table

for C we can make a table in which all car will participate and respective owner person, and primary key of person(for eg person_id) will be used as a foreign key in this table. like

Car1_id, person_id1

Car2_id, person_id2

Car3_id, null......like this---table 2

no referential integrity violate because every person_id will be present in relation A-R1-B.

I think 2 tables are enough here.

---

Is the relation R2 having 1:1 partial participation?

---

yes @Shubhanshu R2 is 1:1 with partial participation.

---

Yes then it will have 2 tables as follow:-

Aadhar_R1_Person and R2_car

OR

Aadhar_R1_Person_R2 and car

because R2 can merge to either side.

---

anyway in short, in case of (without normalization), 2 tables are enough. in case of (normalization)... if null allowed then 2 tables re enough. if no null allowed then 3 tables needed

---

@ reena_kandari

You said,

Relation R2 = A person may have maximum 1 car and a car may not be related to any person(1:1 with partial participation)

so in B_R2_C table, where  we first make a table in which all car will participate and respective owner person,

then what about remaining cars ?? as you said a car may not be related to any person 

so as no entry in table is Null ( which is also followed in table A_R1_B , no null entry) 

hence  we need 2 tables in B_R2_C is not it ?

and total 1+2 = 3 tables require .

---

@reena and @ Shubhanshu 

In your example it is possible to have 2 tables when it is mention without normalization , because of Null entry in table.

 In case of normalization if null is allowed then 2 tables are enough. If no null allowed then 3 tables needed for B_R2_C .

And nothing is mention in your example about Null .

also it is partial participation for R2 so all cars not related to a person, some cars are left, for them we can make a separate table .

are you agree with me ?

---

@Bikram sir, for your first doubt,

in second table all car entries will be present.

And when asked "minimum number of tables" without any normalization,then the minimum number is same as tables in 1NF.In this case we just have to take care of only two things.

1.all entries of an entity set must be present in any table.(no loss of information)

2.Second and most important is we must have a PK for each and every table.

---

@reena

yes, i agree with you.

You consider Null values ( to represent all entity ) and without any Normalization case .

Here in this question, nothing is mention about normalization so we can assume it is not normalized .

If it is normalized and Null is allowed then also 2 tables require .

And with No Normalization case , 2 tables are required .

So, finally after these long discussions i go with 2 tables as an answer for this question .

---

If B=R=C was also there, i.e Total participation in all sets, then can we merge all tables into just one table?

---

https://gateoverflow.in/86626/test-by-bikram-2017-database-systems-1?show=97413#c97413 https://gateoverflow.in/86626/test-by-bikram-2017-database-systems-1?show=97407#c97407

---

https://gateoverflow.in/173573/relationship-total-participation-entity-single-table-formed

---

If null is indeed there in table than why we don't just merge all table and use a single big table. And because we are allowing null values so we will be able to store any combination of data.

Will there be any data loss? Or any data which can not be inserted or so?