The Gateway to Computer Science Excellence
First time here? Checkout the FAQ!
x
+17 votes
1.4k views

Consider the following 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$?

  1. ${M1, M2, M3, P1}$
  2. ${M1, P1, N1, N2}$
  3. ${M1, P1, N1}$
  4. ${M1, P1}$
asked in Databases by Veteran (386k points)
edited by | 1.4k views
0

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

+1

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.

1 Answer

+18 votes
Best answer

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

answered by Veteran (386k points)
edited by
+1
how R1 is 1-1 assumed it will be M-1 na? b/c arrow is given.
+1
R1 will be (1,n). As double parallel line represent Participation Constraint.
0

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!

0

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 

Answer:

Related questions

Quick search syntax
tags tag:apple
author user:martin
title title:apple
content content:apple
exclude -tag:apple
force match +apple
views views:100
score score:10
answers answers:2
is accepted isaccepted:true
is closed isclosed:true
48,411 questions
52,746 answers
183,341 comments
68,213 users