15,510 views

Consider the following $\text{ER}$ diagram

The minimum number of tables needed to represent $M$, $N$, $P$, $R1$, $R2$ is

1. $2$
2. $3$
3. $4$
4. $5$

why we can't make one table for p and n as they ask for minimum no of table.

new table with filed {p1,p2,n1,n2} can serve the same purpose, they are not empose any condition like normalization
from directed edge we can find for R1 that

it can be one-to-one or it can be many to one

in both the cases we dont need any new table for R ,just add primary key p1 into TOTAL side which acts as a foreign key referring to p table
Which normal form should our relations satisfy?

FYI  ->For this kind of questions  for cross verification could be done via small example.

Link for $2^{nd}$ part  -> https://gateoverflow.in/87025/gate2008-83

Hi @prayas ji, Based on obtained tables and their respective keys it is in BCNF.

This Question needs improvement, Directed edge does not imply anything here without dot on the other end. More over they should have given min. max. cardinality to identify relationship status.
3NF
If you want to check your knowledge of ER diagram then do this question
Is R1 one to many or many to one relation?

First strong entity types are made to tables. So, we get two tables $M$ and $P$.

I assume $R1$ is $1:1$ or $1:n$ as that would minimize the number of tables as asked in question.

Now participation of $M$ in $R1$ is total (indicated by double arrow) meaning every entity of $M$ participate in $R1$. Since $R1$ is not having an attribute, we can simple add the primary key of $P$ to the table $M$ and add a foreign key reference to $M$. This handles $R1$ and we don't need an extra table. So, $M$ becomes ${M1, M2, M3, P1}$.

N here is a weak entity weakly related to $P$. So, we form a new table $N$, and includes the primary key of $P (P1)$ as foreign key reference. Now $(P1, N1)$ becomes the primary key of $N$.

Thus we get $3$ tables.

$M: {M1, M2, M3, P1}$ - $M1$ primary key, $P1$ references $P$

$P: {P1, P2} - P1$ primary key

$N: {P1, N1, N2} - (P1, N1)$ primary key, $P1$ references $P$.

by

@arjun sir, Why cant we store [M R1 P] in 1 table, assuming 1:1 cardinality and total participation is also given?

Thanks!

Really 1:1 rel'n is the one who hs gone unnoticed. Is it that good that we can combine into 1 only (simply)

???
@Arjun
If it's 1:1 relation from M to P, then M, R1 and P can be combined together, as it's total participation from the side of M.

So the answer of this question depends on whether R1 is 1:1 or M:1
@manu00x, even if it's 1:1 then if we combine those 2 entities then it will be in 1NF and no more in 3NF so can't combine.
@Rajesh where did they mention about the relations to be in 3 NF?
" if we combine those 2 entities then it will be in 1NF and no more in 3NF so can't combine. " will check thanx. No, no thanx 3nf problem is a different scenario i.e. transitivity. Then as a matter of fact even if u keep 2 tables with one table having p.k of the other to show the rel'n & all of the other(total participation side) then still in that case (wid 2 tables remember) transitivity is shown as it's all game of the p.k.s.
3nf doesn't help or reduce the problem here it's infact a separate issue also doing a table division 'duty'.
What if we were given that both R1 and R2 were having many to many relationships.

then we will have one more table for R1 , but will we have one more table for R2 also ? since it is a weak relation ..

what will be the answer then ? (min. number of tables )

4 or 5?
Which normal form should our relations satisfy?

In 1nf we can further minimise the tables
@Xylene as M1 is a key then M1 P1 cannot be primary key it will be a super key
@Manu Thakur, sir please confirm this, If we assume R1 is 1:1 relation and combine all M, R1, P into one relation then this relation will be in 2nf as there is no partial dependency. But yes it is not 3nf as there is a transitive dependency.

P1 should be the key of combined relation. P1 --> M1 and M1 --> M2 transitive dependency.
@Hemant Parihar, you are correct.
but @Arjun sir, if R1 is in 1:M relation then relaiton must be given to the Many side which means to the table P na? please tell me where I am getting wrong.
I think R1 should be N:1 relation.

@Arjun Sir, What does the arrow indicate?

If arrow is present on both left and right side then it means one-one relationship.

@Arjun

I think we require only one table for M-R1-P with Primary key equal to P1 only. Coz if you make P1,M1 Primary Key, we can have Null values (consider a case where no. of elements in P is greater than no. of elements in M and mapping in such a way that atleast one elemnet of P is not mapped to any element of M).

Correct me, if i am wrong.

@ayushsomani

No, we do require 2 tables for M-R1-P .

According to you, If 1 table and Primary key equal to P1......

Now, the problem that arises is as relation R1 is Many:One for a single value of P1 there might be cases that it can map to multiple values of M1, which is not allowed (multiple values in a single cell).

@Arjun Sir, If we consider R1 as 1:N relation, then How can P1 act as a foreign key in M? Because cardinality of M would be N which results in a violation of atomicity.

Please correct me if I’m wrong!
It is better to include foreign key(primary key of P here; i.e P1) in the total participating entity as all the tuples will contain some value. If FK is added to P, then many fields will be empty.
It's not a best one

\large \begin{align*} \text{Table}_1 &= M1, M2, M3, P1\\ \text{Table}_2 &= P1, P2\\ \text{Table}_3 &= P1, N1, N2 \end{align*}

so we have 3 tables here
this imply that

Q.82 = option B
Q.83 = option A

for 82 ---> 3 table will be there ----

for 83 ---> ans is a--> cause the R1 (M1,P1) and M(M1,M2,M3) ----both have the same primary key ,so we can merge them , so new table will be (m1,m2,m3,p1)

by

for 82

the double rectangale means its a weak entiry and can be combined with the relation R2

so isn't needs 4 tables to represent them ? i.e. M,P,R1 ,(R2,N)
@bikram sir

weak entity requires extra table

ur suggestion?

yes, weak entity requires extra table .

• Weak Entity Set Cannot exists alone
• To build a table/schema for weak entity set we construct a table with one column for each attribute in the weak entity set
Which normal form should our relations satisfy?
@bikram sir

why M and P is not merged here?? If it is 1:1 cardinality and total participation then we can merge both M and P.

# GATE | GATE CS 2008

M, P are strong entities hence they must be represented by separate tables.

Many-to-one and one-to-many relationship sets that are total on the many-side can be represented by adding an extra attribute to the “many” side, containing the primary key of the “one” side. ( This way no extra table will be needed for Relationship sets )

M table is modified to include primary key of P side(i.e. P1). N is weak entity, and is modified to include primary key of P (i.e, P1).

Therefore there would be minimum of 3 tables with schema given below :

M ( M1, M2, M3, P1)
P ( P1, P2 )
N ( P1, N1, N2 )

Note: This modification of a table in the case of one-many or many-one to include relationship set at the many side works well, but only in the case when the relationship set doesn’t have its own attributes. If the relationship set has its own attribute then we need to make a separate table for the relationship set also.

Relationship  between M & P is N:1 so solve Accordingly .

The Side which Has "Arrow" has Cardinality = 1 always.(Remember That).

### 1 comment

It would be better if you post it with proper Source. There is no such mention in any of the standard textbooks.