new table with filed {p1,p2,n1,n2} can serve the same purpose, they are not empose any condition like normalization

Consider the following $\text{ER}$ diagram

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

- $2$
- $3$
- $4$
- $5$

### 9 Comments

new table with filed {p1,p2,n1,n2} can serve the same purpose, they are not empose any condition like normalization

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

## 5 Answers

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

So, answers is **B.**

### 21 Comments

P1 should be the key of combined relation. P1 --> M1 and M1 --> M2 transitive dependency.

I think we require * only one table for M-R1-P* with

*. Coz if*

**Primary key equal to P1 only**

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

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).**

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)

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