GATE CSE 2008 | Question: 83

Question

Consider the following $\text{ER}$ diagram

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

 Which of the following is a correct attribute set for one of the tables for the minimum number of tables needed to represent $M$, $N$, $P$, $R1$, $R2$?

• A. ${M1, M2, M3, P1}$
• B. ${M1, P1, N1, N2}$
• C. ${M1, P1, N1}$
• D. ${M1, P1}$

Comments

Is this a standard ER notaion convention? 

 Directed line is used to indicate one occurrence and undirected line is used to indicate many occurrences in a relation.

reference : http://ecomputernotes.com/fundamental/what-is-a-database/er-notation

---

An excellent read:

https://www.tutorialcup.com/dbms/er-model-into-tables.htm

Covers almost everything.

SUMMARY

The basic rules for converting the ER diagrams into tables are:

• Convert all the Entities in the diagram to tables: All the entities represented in the rectangular box in the ER diagram become independent tables in the database.
• All single-valued attributes of an entity is converted to a column of the table: All the attributes, whose value at any instance of time is unique, are considered as columns of that table. 
• The key attribute in the ER diagram becomes the Primary key of the table.
• Declare the foreign key column, if applicable: attribute COURSE_ID in the STUDENT entity is from COURSE entity. Hence add COURSE_ID in the STUDENT table and assign it a foreign key constraint. COURSE_ID and SUBJECT_ID in LECTURER table form the foreign key column. Hence by declaring the foreign key constraints, the mapping between the tables are established.
• Any multi-valued attributes are converted into the new table: A hobby in the Student table is a multivalued attribute. Any student can have any number of hobbies. So we cannot represent multiple values in a single column of STUDENT table. We need to store it separately, so that we can store any number of hobbies, adding/ removing/deleting hobbies should not create any redundancy or anomalies in the system. Hence we create a separate table STUD_HOBBY with STUDENT_ID and HOBBY as its columns. We create a composite key using both the columns.
• Any composite attributes are merged into the same table as different columns: Address is a composite attribute. It has Door#, Street, City, State, and Pin. These attributes are merged into STUDENT table as individual columns.
• One can ignore derived attribute since it can be calculated at any time: In the STUDENT table, Age can be derived at any point in time by calculating the difference between DateOfBirth and the current date. Hence we need not create a column for this attribute. It reduces the duplicity in the database.

some of the special cases

Converting Weak Entity:

A weak entity is also represented as a table. All the attributes of the weak entity form the column of the table. But the key attribute represented in the diagram cannot form the primary key of this table. We have to add a foreign key column, which would be the primary key column of its strong entity. This foreign key column along with its key attribute column forms the primary key of the table.

Representing 1:1 relationship

We have LECTURER teaches SUBJECT relation. It is a 1:1 relation. i.e.; one lecturer teaches only one subject. We can represent this case in two ways

1. Create a table for both LECTURER and SUBJECT. Add the primary key of LECTURER in the SUBJECT table as a foreign key. It implies the lecturer name for that particular subject.

2. Create a table for both LECTURER and SUBJECT. Add the primary key of SUBJECT in LECTURER table as a foreign key. It implies the subject taught by the lecturer.

In both the case, the meaning is same. The foreign key column can be added in either of the tables, depending on the developer’s choice. 

Representing 1:N relationship

Consider SUBJECT and LECTURER relation, where each Lecturer teaches multiple subjects. This is a 1: N relation. In this case, the primary key of LECTURER table is added to the SUBJECT table. i.e.; the primary key at 1 cardinality entity is added as foreign key to N cardinality entity

Representing M:N relationship

Consider the example, multiple students enrolled for multiple courses, which is M:N relation. In this case, we create STUDENT and COURSE tables for the entities. Create one more table for the relation ‘Enrolment’ and name it as STUD_COURSE. Add the primary keys of COURSE and STUDENT into it, which forms the composite primary key of the new table.

Both the participating entities are converted into tables, and a new table is created for the relation between them. Primary keys of entity tables are added into the new table to form the composite primary key. We can add any additional columns if present as an attribute of the relation in ER diagram.

---

If you could address, “total” and “partial” participation constraint, in all these, that would be great !

Answer 1

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

I assume $\text{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 $\text{{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 $A$. 

Comments

According to the above comment by @Tuhin Dutta and also I have learnt the same from coaching:

"Representing 1 : N relationship

Consider SUBJECT and LECTURER relation, where each Lecturer teaches multiple subjects. This is a 1: N relation. In this case, the primary key of LECTURER table is added to the SUBJECT table. i.e.; the primary key at 1 cardinality entity is added as a foreign key to N cardinality entity"

Here, cardinality between M and P is "1 : N". So, M's Primary key must be a foreign key in P. 

Also, between P and N cardinality is "N : 1". hence, N's Primary key must be added to P as foreign key.

so, the tables will be: 

M1, M2, M3

P1, P2, M1, N1

N1, N2

Is my conceptual understanding wrong? Bcoz in M and P, @Arjun sir have referenced P's Primary key as foreign key in M.

@Arjun sir, Please Help!

As, the tables mentioned by me are not in options, I can easily go for Option A. But I want to understand the concept.

Also here https://gateoverflow.in/8309/gate2015-1-41   -- @Arjun sir you have followed same convention that I have mentioned above i.e relation between E1 and E3 is 1 : N and hence E1's primary key is tranfered to E3 as foreign key!

---

In case of  1 to N relationships    [the properties if anyof the relation] along with the primary key of the N side  are  added to the 1 side  

 

in the er diagram , the side where 1 is written actually has  n participation and where N is written  has  1 participation 

---

 correct attribute set for 'one of the tables' for the minimum number of tables.

If one can read question carefully word by word, answer is directly given in one option only and i.e A without any confusion or dilemma in  the options.

We know the primary key of partial side is shifted towards total side and became 'foreign key' on total side. Hence, Option A can easily be caught.  

Answer 2

 

ref: https://www.cs.uct.ac.za/mit_notes/database/htmls/chp06.html