ER-Model

Question 1

Which one of the following is used to represent the supporting many-one relationships of a weak entity set in an entity-relationship diagram?

A
Ovals that contain underlined identifiers
B
Rectangles with double/bold border
C
Diamonds with double/bold border
D
Ovals with double/bold border
Question 1 Explanation: 
An entity set that does not have sufficient attributes to form a primary key is termed as a weak entity and an entity set that has a primary key is termed as strong entity set. For a weak entity set to be meaningful, it must be associated with another entity set, called identifying or owner entity set. The relationship associating the weak entity set with the identifying entity set is called the identifying relationship and it is represented by double diamond. The identifying relationship is many-to-one from the weak entity set to the identifying entity set and the participation of weak entity set in the relationship is total.
Question 2

Given the basic ER and relational models, which of the following is INCORRECT?

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
Question 2 Explanation: 
Option (A): In ER-model, multivalued attribute of an entity can have more than one value.
Option (B): In ER model, the attribute which can be further broken down into some other attributes is called composite attribute.
Option (C): In Relational model, the intersection of one row and column should contain only one value. So, option (C) is INCORRECT.
Option (D): In Relational model, the intersection of one row and column should contain either exactly one value or NULL.
Question 3

Consider two tables in a relational database with columns and rows as follows:

Table: Student
ROLL_NO	  NAME	DEPT_ID
   1	  ABC	   1
   2	  DEF	   1
   3	  GHI	   2
   4	  JKL	   3
Table: Department
DEPT_ID	DEPT_NAME
   1	   A
   2	   B
   3	   C 
Roll_no is the primary key of the Student table, Dept_id is the primary key of the Department table and Student.Dept_id is a foreign key from Department.Dept_id.
What will happen if we try to execute the following two SQL statements?
(i)  update Student set Dept_id = Null 
     where Roll_on = 1
(ii) update Department set Dept_id = Null 
     where Dept_id = 1 

A
Both (i) and (ii) will fail
B
(i) will fail but (ii) will succeed
C
(i) will succeed but (ii) will fail
D
Both (i) and (ii) will succeed
Question 3 Explanation: 
Here in (i), when we update in Student table, Dept_id = Null, then it will not cause any problem to referenced table.
But in (ii) if we set in Department table, Dept_id = Null, then it will produce inconsistency because in Student table we will still have the tuples containing the Dept_id = 1.
Question 4

Consider the following entity relationship diagram (ERD), where two entities E1 and E2 have a relation R of cardinality 1 : m.

The attributes of E1 are A11, A12 and A13 where A11 is the key attribute. The attributes of E2 are A21, A22 and A23 where A21 is the key attribute and A23 is a multi-valued attribute. Relation R does not have any attribute. A relational database containing minimum number of tables with each table satisfying the requirements of the third normal form (3NF) is designed from the above ERD. The number of tables in the database is

A
2
B
3
C
5
D
4
Question 4 Explanation: 
One table for E1, two tables for E2 (A21, A22 and A21, A23) because we need to make a separate table for multivalued attribute to satisfy minimum 1NF condition that requires atomic attributes.
Then, we get
T1: {A11, A12, A13} - key is A11
T2: {A21, A22, A11} - key is A21
T3: {A21, A23} - key is {A21, A23}
Question 5

In a schema with attributes A, B, C, D and E following set of functional dependencies are given

A → B 
A → C 
CD → E 
B → D 
E → A 
Which of the following functional dependencies is NOT implied by the above set?

A
CD → AC
B
BD → CD
C
BC → CD
D
AC → BC
Question 5 Explanation: 
Apply membership test for all the functional dependencies.
Option (B):
BD → CD
BD+ = BD
i.e., BD cannot derive CD and hence is not implied.
Question 6

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

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.
Question 6 Explanation: 
The information should appear as an attribute of lodging, because this is the only common attribute that relating to the hotel room and person.
Question 7
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?
A
(i)
B
(ii)
C
(iii)
D
(iv)
Question 8

Consider the relational schema given below, where eId of the relation dependent is a foreign key referring to empId of the relation employee. Assume that every employee has at least one associated dependent in the dependent relation.

employee (empId, empName, empAge)
dependent(depId, eId, depName, depAge)

Consider the following relational algebra query:

empId(employee)-∏empId(employee(empId = eID)∧(empAge ≤ depAge)dependent)

The above query evaluates to the set of empIds of employees whose age is greater than that of

A
some dependent.
B
all dependents.
C
some of his/her dependents.
D
all of his/her dependents.
Question 8 Explanation: 
The inner query selects the employees whose age is less than or equal to at least one of his dependents. So, subtracting from the set of employees, gives employees whose age is greater than all of his dependents.
Question 9

A prime attribute of a relation scheme is an attribute that appears

A
in all candidate keys of R.
B
in some candidate key of R.
C
in a foreign key of R.
D
only in the primary key of R .
Question 9 Explanation: 
A prime attribute of a relation scheme R is an attribute that appears in some candidate keys of R. Need not to appear in all the candidate keys.
Ex: AB, BC, CD are candidate keys of R(ABCD). In the FDs set one attribute may not be part of all the FDs.
Question 10

What is the optimized version of the relation algebra expression πA1A2F1F2(r)))), where A1, A2 are sets of attributes in  with A1 ⊂ A2 and F1, F2 are Boolean expressions based on the attributes in r?

A
πA1(F1∧F2) (r))
B
πA1(F1∨F2) (r))
C
πA2(F1∧F2) (r))
D
πA2(F1∨F2) (r))
Question 10 Explanation: 
 Since A1 ⊂ A2 will get only attribute A1 as it is in the outside. So we can remove project A2.
 Two Selects with Boolean expression can be combined into one select with AND of two Boolean expressions.
Question 11

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

A
4
B
6
C
7
D
8
Question 11 Explanation: 

Question 12

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?

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 one-to-many and the participation of A in R is partial.
Question 12 Explanation: 

The relational table for R be merged that of A, if the relationship R is Many-to-one and the participation of A in R is total.
Question 13

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.

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.
Question 13 Explanation: 

The M : 1 relationship holds between two entities E1 and E2, in which each tuple from E2 is in relation with many tuples of E1. One tuple from E1 is in relation with only one tuple of E2. It is given that participation from both the sides is total and the cardinality of E1 is greater than E2.

Therefore, every entity E1 is associated with exactly one entity in E2.
Question 14

Consider the following ER diagram

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

A
2
B
3
C
4
D
5
Question 14 Explanation: 
➝ M, P are entities so they require individual tables.
➝ Here N is a Weak entity, but it need to modify the primary key of P such as P1
M = {M1, M2, M3, P1}
P = {P1, P2}
N = {N1, N2, P1}
➝ Relationship set has its own attribute, then no need to create a separate table.
➝ Finally we require 3 minimum tables to represent M, P, N, R1, R2.
Question 15

Consider the following ER diagram

Which of the following is a correct attribute set for one of the tables for the correct answer to the above question?

A
{M1, M2, M3, P1}
B
{M1, P1, N1, N2}
C
{M1, P1, N1}
D
{M1, P1}
Question 15 Explanation: 
Possible set of attributes is
For M = {M1, M2, M3, P1}
P = {P1, P2}
N = {N1, N2, P1}
Question 16

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?

A
2
B
3
C
4
D
5
Question 16 Explanation: 
R1 and R2 two relationships between E1 and E2.
R1 is one to many.
R2 is many to many.
→ E1 and E2 have separate table because they need to store multiple values.
→ R2 also have separate table by considering Primary keys E1 and E2 as foreign keys.
→ R1 is converted to many side table i.e., E2 as Primary key and E1 as Foreign key.
So, totally we need 3 tables to store the value.
Question 17

Consider the following relation schema

    Students(rollno: integer, sname: string)
    Courses(courseno: integer, cname: string)
    Registration(rollno: integer, courseno: integer, percent: real)

Which of the following queries are equivalent to this query in English?

"Find the distinct names of all students who score more than 90% in the course numbered 107" 
(I) SELECT DISTINCT S.sname
    FROM Students as S,Registration as R
    WHERE R.rollno = S.roll AND R.courseno = 107 AND R.percent > 90
(II) Πsnamecourseno=107∧percent>90(Registration ⋈ Students))
(III) {T|∃S ∈ Students, ∃R ∈ Registration (S.rollno = R.rollno ∧ R.courseno = 107 ∧ R.percent > 90 ∧ T.sname = S.sname)}
(IV) {〈SN〉|∃SR∃RP(〈SR,SN〉 ∈ Students ∧ 〈SR,107,RP〉 ∈ Registration ∧ RP>90)}
A
I, II, III and IV
B
I, II and III only
C
I, II and IV only
D
II, III and IV only
Question 17 Explanation: 
Four queries given in SQL, RA, TRC and DRC and all four statements will retrieve the required information.
There are 17 questions to complete.

Access quiz wise question and answers by becoming as a solutions adda PRO SUBSCRIBER with Ad-Free content

Register Now

If you have registered and made your payment please contact solutionsadda.in@gmail.com to get access