ERModel
Question 1 
Which one of the following is used to represent the supporting manyone relationships of a weak entity set in an entityrelationship diagram?
Ovals that contain underlined identifiers
 
Rectangles with double/bold border  
Diamonds with double/bold border
 
Ovals with double/bold border

Question 2 
Given the basic ER and relational models, which of the following is INCORRECT?
An attribute of an entity can have more than one value  
An attribute of an entity can be composite  
In a row of a relational table, an attribute can have more than one value  
In a row of a relational table, an attribute can have exactly one value or a NULL 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 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 multivalued 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
2  
3  
5  
4 
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 4 
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 CRoll_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
Both (i) and (ii) will fail  
(i) will fail but (ii) will succeed  
(i) will succeed but (ii) will fail  
Both (i) and (ii) will succeed 
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 5 
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
Person  
Hotel Room  
Lodging  
None of these. 
Question 6 
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 → AWhich of the following functional dependencies is NOT implied by the above set?
CD → AC  
BD → CD  
BC → CD  
AC → BC 
Option (B):
BD → CD
BD^{+} = BD
i.e., BD cannot derive CD and hence is not implied.
Question 7 
What is the optimized version of the relation algebra expression π_{A1}(π_{A2}(σ_{F1}(σ_{F2}(r)))), where A1, A2 are sets of attributes in with A1 ⊂ A2 and F1, F2 are Boolean expressions based on the attributes in r?
π_{A1} (σ_{(F1∧F2)} (r))  
π_{A1} (σ_{(F1∨F2)} (r))  
π_{A2} (σ_{(F1∧F2)} (r))  
π_{A2} (σ_{(F1∨F2)} (r)) 
Two Selects with Boolean expression can be combined into one select with AND of two Boolean expressions.
Question 8 
A prime attribute of a relation scheme is an attribute that appears
in all candidate keys of R.  
in some candidate key of R.  
in a foreign key of R.  
only in the primary key of R . 
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 9 
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
some dependent.  
all dependents.  
some of his/her dependents.  
all of his/her dependents. 
Question 10 
Consider an EntityRelationship (ER) model in which entity sets E_{1} and E_{2} are connected by an m:n relationship R_{12}, E_{1} and E_{3} are connected by a 1:n (1 on the side of E_{1} and n on the side of E_{3}) relationship R_{13}.
E_{1} has two singlevalued attributes a_{11} and a_{12} of which a_{11} is the key attribute. E_{2} has two singlevalued attributes a_{21} and a_{22} is the key attribute. E_{3} has two singlevalued attributes a_{31} and a_{32} of which a_{31} 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 ___________.
4  
6  
7  
8 
Question 11 
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?
Relationship R is onetomany and the participation of A in R is total.  
Relationship R is onetomany and the participation of A in R is partial.  
Relationship R is manytoone and the participation of A in R is total.  
Relationship R is onetomany and the participation of A in R is partial. 
The relational table for R be merged that of A, if the relationship R is Manytoone and the participation of A in R is total.
Question 12 
In an EntityRelationship (ER) model, suppose R is a manytoone 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?
Every entity in E1 is associated with exactly one entity in E2.  
Some entity in E1 is associated with more than one entity in E2.  
Every entity in E2 is associated with exactly one entity in E1.  
Every entity in E2 is associated with at most one entity in E1.

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 13 
Consider the following ER diagram
The minimum number of tables needed to represent M, N, P, R1, R2 is
2  
3  
4  
5 
➝ 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 14 
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?
{M1, M2, M3, P1}  
{M1, P1, N1, N2}  
{M1, P1, N1}  
{M1, P1} 
For M = {M1, M2, M3, P1}
P = {P1, P2}
N = {N1, N2, P1}
Question 15 
Let E_{1} and E_{2} be two entities in an E/R diagram with simple singlevalued attributes. R_{1} and R_{2} are two relationships between E_{1} and E_{2}, where R_{1} is onetomany and R_{2} is manytomany. R_{1} and R_{2} do not have any attributes of their own. What is the minimum number of tables required to represent this situation in the relational model?
2  
3  
4  
5 
R_{1} is one to many.
R_{2} is many to many.
→ E_{1} and E_{2} have separate table because they need to store multiple values.
→ R_{2} also have separate table by considering Primary keys E_{1} and E_{2} as foreign keys.
→ R_{1} is converted to many side table i.e., E_{2} as Primary key and E_{1} as Foreign key.
So, totally we need 3 tables to store the value.
Question 16 
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) Π_{sname}(σ_{courseno=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) {〈S_{N}〉∃S_{R}∃R_{P}(〈S_{R},S_{N}〉 ∈ Students ∧ 〈S_{R},107,R_{P}〉 ∈ Registration ∧ R_{P}>90)}
I, II, III and IV  
I, II and III only  
I, II and IV only  
II, III and IV only 