Self doubt

Question

The number of tables required in SELF-REFERENTIAL relation when different mappings(1: M, M:1, 1:1, M: N) are used with different participation (Partial Participation at both sides, Total Participation at both sides and partial participation at one side).

(Tabular Format answer will be appreciated).

Comments

I think for 1:1 it is one table and for M:N 2 tables.

not so sure.

Answer 1

Whenever they ask about minimum number of tables then why can’t me merge all into one ? And have answer = 1 always.

Well, there are some situations when we go for more tables, lets see one by one.

1:1 Cardinality

Now suppose this is relationship I am having in which a₃ and c₁ are not participating

If you try to merge all into single table then it will look like this-

Here you can notice one thing, Both the columns have null somewhere (bcoz its partial participation)

And that’s why you can not merge into one table, bcoz if we do so, We can not even assign a primary key to table.

(Note: Primary key can not have null in it.)

Now what if atleast one side has total participation in 1:1 ?

In this case atleast one column will not be having any Null entries and we can make that column (that is either primary key of E1 or E2) as primary key of whole table and we can merge all into one.

Now you can (you should) check for other combinations. I am writing directly –

1.Only Partial participations-

• One-One:2 Tables, Merge Relation to any of the side.
• Many-One or One-Many:2 Tables, Merge Relation to Many side.
• Many-Many:3 tables (separate table for relation)

2. Any one of the  side has full participation-

• Full participation on Many Side: 2 Tables
• Full Participation on one side: 1 Table

3.Both Side full participation-

• Always one table irrespective of Cardinality.

Comments

iit madras: https://www.youtube.com/watch?v=WIkw87aLAMc&t=2738s&ab_channel=nptelhrd

Database Systems(4^th edition) by Elmasri and Navathe:- 

In NPTEL as well as Navathe, it is said that for M:N relation we make 2 tables. We dont see if there is total participation on both sides or not. We can merge two tables only when there is 1:1 relation and total participation on both sides, not in any other case. 

Also, see this:-

It says we can also include the foreign key on the side of partial participation as well. It is just that there will be a lot of null values.

---

@ shreya

 

if one side total participation then this entity primary key  whole table primary key

because if it is in total participation then its value never null

---

@Sachin Mittal 1 sir ,
Why in these questions table no are different ? These are from the gate overflow test series 
https://gateoverflow.in/347495/gate-overflow-test-series-databases-test-1-question-14
https://gateoverflow.in/347484/gate-overflow-test-series-databases-test-1-question-20
https://gateoverflow.in/347464/gate-overflow-test-series-databases-test-1-question-30

Answer 2

PARTICIPATION	1:M	M:1	1:1	M:N
Partial Participation on both side	2(min) 3(max)	2(min) 3(max)	2(min) 3(max)	3
Total participation on both side	2(min) 3(max)	2(min) 3(max)	2(min) 3(max)	1
Partial participation on one side	2(min) 3(max)	2(min) 3(max)	2(min) 3(max)	3

Min is what people usually used to avoid adding unnecessary tables, Max also we can provide by taking the primary key of the table of each side and create one new table.

For 1:1 in total participation at both side is 1 coz, whenever there is total participation with one to one cardinality, then the no. Of entities at both side will be equal. Therefore we can combine both the tables in one table which will save space

Comments

I guess we need to answer about self referential relations.i.e we have to form a relationship between entities of same table.

 For e.g. Take a table employees which contains data of all employees of a company.

Now lets have a relationship named manages.

Manager manages employees.

Since manager is also an employee, so his data will be in that same employees table.

Consider a manager can manage several people or none. This falls under 1:m. Partial participation on both sides. As manager may not be managed by anyone. Or there may be a new employee without a manager.

To store this data only 1 table is required. Just add one more column named "managed by" in original table. This will take care of relationship.

---

Is this table is for self Referncial relation??

Answer 3

For 1:1 relationship:

1 table is required in each case.i.e total participation, partial participation.etc.

 

For N:M relationship:

2 table is required in each case.

 

For 1:M relationship:

1 table is sufficient.