Previous Year Questions: ER Model | Database Management System (DBMS) - Computer Science Engineering (CSE) PDF Download

Q1: In the context of owner and weak entity sets in the ER (Entity-Relationship) data model, which one of the following statements is TRUE?  (2024 SET 2)
(a) The weak entity set MUST have total participation in the identifying relationship
(b) The owner entity set MUST have total participation in the identifying relationship
(c) Both weak and owner entity sets MUST have total participation in the identifying relationship
(d) Neither weak entity set nor owner entity set MUST have total participation in the identifying relationship
Ans: (a)
Sol: The weak entity set must participate totally in the identify weak relationship.
Usually, there exist one to many relationship between strong entity and weak entity thus total participation from weak entity set

Q2: Let S be the specification: "Instructors teach courses. Students register for courses. Courses are allocated classrooms. Instructors guide students." Which one of the following ER diagrams CORRECTLY represents S?  (2024 SET 1)

(a) (i)
(b) (ii)
(c) (iii)
(d) (iv)
Ans: (d)
Sol: Option D is correct
There's a relationship b/w :

• Instructors (Teaches) courses
• Students (Registration) courses
• Courses (Allocation) classrooms
• Instructors (Guides) students

Link them and you'll get a figure like (iv)
Also obviously : Student, Instructor, Courses and Classroom are all entity

Q3: Which one of the following is used to represent the supporting many-one relationships of a weak entity set in an entity-relationship diagram?  (2020)
(a) Diamonds with double/bold border
(b) Rectangles with double/bold border
(c) Ovals with double/bold border
(d) Ovals that contain underlined identifiers
Ans: (a)
Sol:
Weak entity set is represented by Rectangles with double/bold border.

Q4: In an Entity-Relationship (ER) model, suppose R is a many-to-one relationship from entity set E1 to entity set E2. Assume that E1 and E2 participate totally in R and that the cardinality of E1 is greater than the cardinality of E2.  (2018)
Which one of the following is true about R?
(a) Every entity in E1 is associated with exactly one entity in E2.
(b) Some entity in E1 is associated with more than one entity in E2.
(c) Every entity in E2 is associated with exactly one entity in E1.
(d) Every entity in E2 is associated with at most one entity in E1.
Ans: (a)
Sol: Since it is a many to one relationship from E1 to E2, therefore:

1. No entity in E1 can be related to more than one entity in E2. ( hence B is incorrect)
2. An entity in E2 can be related to more than one entity in E1.(hence C and D are incorrect).

Option (A) is correct: Every entity in E1 is associated with exactly one entity in E2.

Q5: An ER model of a database consists of entity types A and B. These are connected by a relationship R which does not have its own attribute. Under which one of the following conditions, can the relational table for R be merged with that of A?  (2017 SET 2)
(a) Relationship R is one-to-many and the participation of A in R is total
(b) Relationship R is one-to-many and the participation of A in R is partial
(c) Relationship R is many-to one and the participation of A in R is total
(d) Relationship R is many-to one and the participation of A in R is partial
Ans: (c)
Sol: The relation table for R should always be merged with the entity that has total participation and relationship should be many to one.
Answer is C.

Q6: Consider an Entity-Relationship (ER) model in which entity sets E1 and E2 are connected by an m : n relationship R12. E1 and E3 are connected by a 1 : n (1 on the side of E1 and n on the side of E3) relationship R13.
E1 has two single-valued attributes a11 and a12 of which a11 is the key attribute. E2 has two single-valued attributes a21 and a22 of which a21 is the key attribute. E3 has two single-valued attributes a31 and a32 of which a31 is the key attribute. The relationships do not have any attributes.
If a relational model is derived from the above ER model, then the minimum number of relations that would be generated if all the relations are in 3NF is _______.  (2015 SET 1)
(a) 2
(b) 3
(c) 4
(d) 5
Ans: (c)
Sol: Answer is 4. The relations are as shown:
⟨a₁₁, a₁₂⟩ for  E₁ 
⟨a₂₁, a₂₂⟩ E₂
⟨a₃₁, a₃₂,a ₁₁ ⟩ for E₃ and E₁ -  E₃ relationship
⟨a₁₁, a₂₁⟩ form m : n relationship E₁- E₂
We cannot combine any relation here as it will give rise to partial functional dependency and thus violate
3 NF. 

Q7: Given the basic ER and relational models, which of the following is INCORRECT?  (2012)
(a) An attribute of an entity can have more than one value
(b) An attribute of an entity can be composite
(c) In a row of a relational table, an attribute can have more than one value
(d) In a row of a relational table, an attribute can have exactly one value or a NULL value
Ans: (c)
Sol: According to Relational Model every relation should be in 1NF and according to 1 NF, there should not be a multivalued attribute in a relation. That means an attribute can have only one value or NULL value in a row. So statement of option C is incorrect

Q8: Consider the following ER diagram  (2008) 

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}
Ans: (a)
Sol: 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 R 1 is total (indicated by double arrow) meaning every entity of M participate in R 1 . Since R 1 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 R 1 and we don't need an  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 ( P 1 ) as foreign key reference. Now ( P 1 , N 1 ) becomes the primary key of N .
Thus we get 3 tables.
M : M 1 , M 2 , M 3 , P 1 - M 1 primary key, P 1 references P
P : P 1 , P 2 - P 1 primary key
N : P 1 , N 1 , N 2 - ( P 1 , N 1 ) primary key, P 1 references P .
So, answers is A. 

 Q9: Consider the entities 'hotel room', and 'person' with a many to many relationship 'lodging' as shown below:  (2005) 

If we wish to store information about the rent payment to be made by person (s) occupying different hotel rooms, then this information should appear as an attribute of
(a) Person
(b) Hotel Room
(c) Lodging
(d) None of these
Ans: (c) 
Sol: Since it is many to many, rent cannot be an attribute of room or person entities alone. If depending on number of persons sharing a room the rent for each person for the room will be different. Otherwise rent can be attribute of room. hence i go for attribute of Lodging.
Correct Answer: C

Q10: Let E1 and E2 be two entities in an E/R diagram with simple single-valued attributes. R1 and R2 are two relationships between E1 and E2, where R1 is one-to-many and R2 is many-to-many. R1 and R2 do not have any attributes of their own. What is the minimum number of tables required to represent this situation in the relational model?  (2005)
(a) 2
(b) 3
(c) 4
(d) 5
Ans: (b)
Sol: We need a separate table for many-to-many relation.
one-to-many relation doesn't need a separate table and can be handled using a foreign key.
So, answer is B - 3 tables.