Paper 2 CS · UNIT 04 · MOCK TEST

Mock 2 – Relational Model, Algebra & Calculus (Part 1)

Unit 4: Database Management Systems · 27 questions · about 32 minutes. Press End Mock any time to see your score and the correct answers.

All 11 topic-wise mocks in this unit

0 / 27 answered 00:00

Q1. A safe expression in relational calculus is one that is guaranteed to yield

AFinite number of tuples BFinite number of attributes CFinite number of null values DFinite number of indices

Q2. Which of the following are true?
A. A database may have only one primary key
B. Every key is a candidate key
C. There can be multiple candidate keys
D. There can be more than one super keys
E. Every candidate key is a primary key.
Choose the correct answer from the options given below:

AA, C, D only BA, D, E only CB, C, D only DB, D, E only

Q3. Consider the following three relations: Employee(eid, eName), Comp(cid, cName), Own(eid, cid). Which of the following relational algebra expressions returns the set of eids who own all brands?

Aπ_eid(π_{eid, cid}(Own) ÷ π_cid(Comp)) Bπ_eid(π_eid(Own) × π_cid(Comp)) Cπ_eid(π_{eid, cid}(Own) × π_cid(Comp)) Dπ_eid(π_eid(Own) × (π_{cid, cName}(Own) ÷ π_cid(Comp)))

Q4. Consider R(A, B, C, D, E) be a relation with the following dependencies: C → F, E → A, EC → D, A → B. Which of the following is a key for R?

ACD BEC CAE DAC

Q5. An alternate key in a database table is also called

APrimary key BCandidate key CSuper key DForeign key

Q6. Consider a relation schema R = (U, V, W, X, Y, Z), on which the following functional dependencies hold: {U → V, VW → X, Y → W, X → U}. The candidate keys of R are:

AUY, VY BUY, VY, XY CUYZ, VYZ, VWZ DUYZ, VYZ, XYZ

Q7. How does a relational database ensure data integrity?

ABy encrypting all data stored BBy enforcing rules defined in the schema CBy compressing data for efficient storage DBy allowing unrestricted access to all users

Q8. Match List I with List II

List I	List II
(a) BCNF iff	(I) every JD is implied by the candidate keys
(b) 5NF iff	(II) all underlying domains contain scalar values only
(c) 1NF iff	(III) every MVD is implied by the candidate keys
(d) 4NF iff	(IV) every FD is implied by the candidate keys

A(a)-(III), (b)-(II), (c)-(I), (d)-(IV) B(a)-(IV), (b)-(I), (c)-(II), (d)-(III) C(a)-(II), (b)-(III), (c)-(IV), (d)-(I) D(a)-(IV), (b)-(I), (c)-(III), (d)-(II)

Directions (Q96–100): Consider the relations X(S, Si, C) and Y(S, P, D):

X
S	Si	C
J	1	M
B	2	N
R	3	H
T	4	G

Y
S	P	D
J	S1	CA
B	P1	AB
R	D1	DC
A	H1	MD

Q9. Find the number of tuples obtained by applying the operation X ⟕_{X.S = Y.S} Y (left outer join).

A1 B3 C4 D6

Directions (Q96–100): Consider the relations X(S, Si, C) and Y(S, P, D):

X
S	Si	C
J	1	M
B	2	N
R	3	H
T	4	G

Y
S	P	D
J	S1	CA
B	P1	AB
R	D1	DC
A	H1	MD

Q10. The result of the operation X ⟖_{X.S = Y.S} Y (right outer join) will be:

S  Si    C     P   D
J  1     M     S1  CA
B  2     N     P1  AB
R  3     H     D1  DC
T  4     G     NULL NULL

S  Si    C     P   D
J  1     M     S1  CA
B  2     N     P1  AB
R  3     H     D1  DC
A  NULL  NULL  H1  MD

S  Si    C     P   D
J  1     M     S1  CA
B  2     N     P1  AB
R  3     H     D1  DC
T  4     G     NULL MD

DNone of these

Correct answer: B.

S  Si    C     P   D
J  1     M     S1  CA
B  2     N     P1  AB
R  3     H     D1  DC
A  NULL  NULL  H1  MD

A right outer join keeps every Y tuple: J, B, R match, and A (only in Y) appears with NULL for Si and C.

Open this question →

Directions (Q96–100): Consider the relations X(S, Si, C) and Y(S, P, D):

X
S	Si	C
J	1	M
B	2	N
R	3	H
T	4	G

Y
S	P	D
J	S1	CA
B	P1	AB
R	D1	DC
A	H1	MD

Q11. Which join is used to get all the tuples of relations X and Y, with NULL values for the corresponding missing values?

ALeft outer join BRight outer join CNatural join DFull outer join

Directions (Q96–100): Consider the relations X(S, Si, C) and Y(S, P, D):

X
S	Si	C
J	1	M
B	2	N
R	3	H
T	4	G

Y
S	P	D
J	S1	CA
B	P1	AB
R	D1	DC
A	H1	MD

Q12. The number of tuples obtained by applying a right outer join on relations X and Y is:

A16 B5 C3 D4

Directions (Q96–100): Consider the relations X(S, Si, C) and Y(S, P, D):

X
S	Si	C
J	1	M
B	2	N
R	3	H
T	4	G

Y
S	P	D
J	S1	CA
B	P1	AB
R	D1	DC
A	H1	MD

Q13. The number of tuples obtained by applying the Cartesian product over X and Y is:

A16 B12 C04 D32

Directions (Q96–100): A company is implementing a new database system to manage its customer information. The database contains tables for customers, orders, products and sales representatives. Each customer can place multiple orders and each order can contain multiple products. Each order is associated with a specific sales representative who manages the account. The database is designed to improve efficiency in tracking sales and customer interactions.

Q14. Which of the following tables is most likely to have a foreign key referencing another table?

ACustomers BOrders CProducts DSales representatives

Q15. A ______ operation combines the rows of two relations and outputs a new relation that has both input relations' rows in it.

Adifference Bunion Ccartesian product Dproject

Q16. Consider the relation R(P, Q, R, S, T, U, V, W, X, Y) and FDs: {P, Q, S} → T, R → Y, {P, Q} → V, {R, X} → X, Q → U, V → {W, X}. Which of the following is a candidate key?

A{P, Q, R, S} B{R, Q, X} C{V, X} DNone of the above

Q17. Statement I: Any retrieval request that can be specified in basic relational algebra can also be specified in relational calculus.
Statement II: Any retrieval request that can be specified in relational calculus can also be specified in basic relational algebra.

ABoth Statement I and Statement II are true. BBoth Statement I and Statement II are false. CStatement I is true but Statement II is false. DStatement I is false but Statement II is true.

Directions (Q96–100): Use the following schema of an academic institution's relational database:

student (rollno, name, degree, year, sex, deptno, advisor)
department (deptid, name, hod, phone)
professor (empid, name, sex, startyear, deptno, phone)
course (courseid, cname, credits, deptno)
enrollment (rollno, courseid, sem, year, grade)
teaching (empid, courseid, sem, year, classroom)
prerequisite (precourseid, courseid)

deptno in student, professor and course refers to department.deptid; advisor (student) and hod (department) refer to professor.empid; rollno in enrollment refers to student; courseid in enrollment and teaching refers to course; empid in teaching refers to professor; precourseid and courseid in prerequisite refer to course.

Q18. Which of the following queries would retrieve the roll numbers and names of students who have enrolled in ALL the prerequisite courses of course number 324?

SELECT s.rollno, s.name FROM student s, enrollment e
WHERE e.rollno = s.rollno AND e.courseid = ANY
  (SELECT precourseid FROM prerequisite p WHERE p.courseid = '324')

SELECT s.rollno, s.name FROM student s, enrollment e
WHERE e.rollno = s.rollno AND e.courseid = ALL
  (SELECT precourseid FROM prerequisite p WHERE p.courseid = '324')

SELECT s.rollno, s.name FROM student s
WHERE NOT EXISTS (SELECT * FROM prerequisite p
  WHERE p.courseid = '324' AND NOT EXISTS
    (SELECT * FROM enrollment e
     WHERE e.courseid = p.precourseid AND e.rollno = s.rollno))

SELECT s.rollno, s.name FROM student s
WHERE EXISTS (SELECT * FROM prerequisite p
  WHERE p.courseid = '324' AND EXISTS
    (SELECT * FROM enrollment e
     WHERE e.courseid = p.precourseid AND e.rollno = s.rollno))

Correct answer: C.

SELECT s.rollno, s.name FROM student s
WHERE NOT EXISTS (SELECT * FROM prerequisite p
  WHERE p.courseid = '324' AND NOT EXISTS
    (SELECT * FROM enrollment e
     WHERE e.courseid = p.precourseid AND e.rollno = s.rollno))

'Enrolled in all prerequisites' is relational division, expressed with a double NOT EXISTS: there is no prerequisite of 324 that the student has not enrolled in. A single enrollment row can equal ALL the prerequisites only if there is just one, and the ANY/EXISTS versions find students with at least one. (The printed paper garbles option C's brackets, and some published keys print B, but B is not a correct division query.)

Open this question →

Q19. Identify the incorrect statement.

AA candidate key is a minimal set of one or more attributes that, taken collectively, allows us to uniquely identify any entity in the entity set. BA candidate key for which no proper subset is also a candidate key is called a super key. CA super key is a set of one or more attributes that, taken collectively, allows us to uniquely identify any entity in the entity set. DA super key for which no proper subset is also a super key is called a candidate key.

Q20. Consider the two relations below (primary keys: EMP.eid and DEPT.did). Identify all possible foreign key(s) based only on the two relations.
EMP (eid, ename, did)
DEPT (did, dname)

Aeid Bdid Ceid, did Deid, did, ename

Directions (Q31–35): Consider the relational schema of Sailors S, Reserves R and Boats B.

Table 1: Sailors

Sid	Sname	Rating	Age
22	Dustin	7	45.0
29	Brutus	1	33.0
31	Lubber	8	55.5
32	Andy	8	25.5
58	Rusty	10	35.5
64	Horatio	7	35.5
71	Zorba	10	16.0
74	Horatio	9	35.0
85	Art	3	25.5
95	Bob	3	63.5

Table 2: Reserves

Sid	Bid	Day
22	101	10/10/98
22	102	10/10/98
22	103	10/8/98
22	104	10/7/98
31	102	11/10/98
31	103	11/6/98
31	104	11/12/98
64	101	9/5/98
64	102	9/8/98
74	103	9/8/98

Table 3: Boats

Bid	Bname	Color
101	Interlake	blue
102	Interlake	red
103	Clipper	green
104	Marine	red

Q21. Which relational algebra query computes the sids of sailors with age over 20 who have not reserved a red boat?

Aπ_sid(σ_age>20 Sailors) − π_sid((σ_color='red' Boats) ⋈ Reserves ⋈ Sailors) Bπ_sid(σ_{color='red' ∧ age=20}(Boats ⋈ Sailors ⋈ Reserves)) Cπ_sid(σ_age<20 Sailors) − π_sid((σ_color='red' Boats) ⋈ Reserves ⋈ Sailors) Dπ_sid(σ_age>20 Sailors) ∧ π_sid((σ_color='red' Boats) ⋈ Reserves ⋈ Sailors)

Directions (Q31–35): Consider the relational schema of Sailors S, Reserves R and Boats B.

Table 1: Sailors

Sid	Sname	Rating	Age
22	Dustin	7	45.0
29	Brutus	1	33.0
31	Lubber	8	55.5
32	Andy	8	25.5
58	Rusty	10	35.5
64	Horatio	7	35.5
71	Zorba	10	16.0
74	Horatio	9	35.0
85	Art	3	25.5
95	Bob	3	63.5

Table 2: Reserves

Sid	Bid	Day
22	101	10/10/98
22	102	10/10/98
22	103	10/8/98
22	104	10/7/98
31	102	11/10/98
31	103	11/6/98
31	104	11/12/98
64	101	9/5/98
64	102	9/8/98
74	103	9/8/98

Table 3: Boats

Bid	Bname	Color
101	Interlake	blue
102	Interlake	red
103	Clipper	green
104	Marine	red

Q22. Which query/queries compute the names of sailors who have reserved a red boat?
Q1: π_sname((σ_color='red' Boats) ⋈ Reserves ⋈ Sailors)
Q2: π_sname(π_sid((π_bid σ_color='red' Boats) ⋈ Reserves) ⋈ Sailors)
Q3: π_sname((σ_color='red' Reserves) ⋈ Boats ⋈ Sailors)

ABoth Q1 and Q2 BBoth Q2 and Q3 COnly Q1 DOnly Q2

Directions (Q31–35): Consider the relational schema of Sailors S, Reserves R and Boats B.

Table 1: Sailors

Sid	Sname	Rating	Age
22	Dustin	7	45.0
29	Brutus	1	33.0
31	Lubber	8	55.5
32	Andy	8	25.5
58	Rusty	10	35.5
64	Horatio	7	35.5
71	Zorba	10	16.0
74	Horatio	9	35.0
85	Art	3	25.5
95	Bob	3	63.5

Table 2: Reserves

Sid	Bid	Day
22	101	10/10/98
22	102	10/10/98
22	103	10/8/98
22	104	10/7/98
31	102	11/10/98
31	103	11/6/98
31	104	11/12/98
64	101	9/5/98
64	102	9/8/98
74	103	9/8/98

Table 3: Boats

Bid	Bname	Color
101	Interlake	blue
102	Interlake	red
103	Clipper	green
104	Marine	red

Q23. Which query/queries compute the names of sailors who have reserved boat 103?
Q1: π_sname((σ_bid=103 Boats) ⋈ Sailors)
Q2: π_sname(σ_bid=103(Reserves ⋈ Sailors))
Q3: π_sname((σ_bid=103 Reserves) ⋈ Sailors)

ABoth Q1 and Q3 BBoth Q2 and Q3 COnly Q3 DOnly Q2

Directions (Q31–35): Consider the relational schema of Sailors S, Reserves R and Boats B.

Table 1: Sailors

Sid	Sname	Rating	Age
22	Dustin	7	45.0
29	Brutus	1	33.0
31	Lubber	8	55.5
32	Andy	8	25.5
58	Rusty	10	35.5
64	Horatio	7	35.5
71	Zorba	10	16.0
74	Horatio	9	35.0
85	Art	3	25.5
95	Bob	3	63.5

Table 2: Reserves

Sid	Bid	Day
22	101	10/10/98
22	102	10/10/98
22	103	10/8/98
22	104	10/7/98
31	102	11/10/98
31	103	11/6/98
31	104	11/12/98
64	101	9/5/98
64	102	9/8/98
74	103	9/8/98

Table 3: Boats

Bid	Bname	Color
101	Interlake	blue
102	Interlake	red
103	Clipper	green
104	Marine	red

Q24. Which query computes the names of sailors who have reserved all boats?

Aρ(Tempsids, (π_bid Reserves) ÷ (π_bid Boats)); π_sname(Tempsids ⋈ Sailors) Bρ(Tempsids, (π_{sid, bid} Reserves) ÷ (π_bid Boats)); π_sname(Tempsids ⋈ Sailors) Cρ(Tempsids, (π_sid Sailors) ÷ (π_bid Boats)); π_sname(Tempsids ⋈ Sailors) Dρ(Tempsids, (π_sid Reserves) ÷ (π_bid Boats)); π_sname(Tempsids ⋈ Boats)

Directions (Q31–35): Consider the relational schema of Sailors S, Reserves R and Boats B.

Table 1: Sailors

Sid	Sname	Rating	Age
22	Dustin	7	45.0
29	Brutus	1	33.0
31	Lubber	8	55.5
32	Andy	8	25.5
58	Rusty	10	35.5
64	Horatio	7	35.5
71	Zorba	10	16.0
74	Horatio	9	35.0
85	Art	3	25.5
95	Bob	3	63.5

Table 2: Reserves

Sid	Bid	Day
22	101	10/10/98
22	102	10/10/98
22	103	10/8/98
22	104	10/7/98
31	102	11/10/98
31	103	11/6/98
31	104	11/12/98
64	101	9/5/98
64	102	9/8/98
74	103	9/8/98

Table 3: Boats

Bid	Bname	Color
101	Interlake	blue
102	Interlake	red
103	Clipper	green
104	Marine	red

Q25. Which query computes the names of sailors who have reserved a red and a green boat?

Aρ(Tempred, π_sid((σ_color='red' Boats) ⋈ Reserves)); ρ(Tempgreen, π_sid((σ_{color='green'} Boats) ⋈ Reserves)); π_sname((Tempred ∩ Tempgreen) ⋈ Sailors) Bρ(Tempboats2, (σ_color='red' Boats) ∩ (σ_{color='green'} Boats)); π_sname(Tempboats2 ⋈ Reserves ⋈ Sailors) Cπ_sname(((σ_color='red' Boats) ∩ (σ_{color='green'} Boats)) ⋈ Reserves ⋈ Sailors) Dρ(Tempboats2, σ_{color='red' ∧ color='green'} Boats); π_sname(Tempboats2 ⋈ Reserves ⋈ Sailors)

Q26.

Consider the following three relations Employee(eid, eName), Comp(cid, cName), Own(eid, cid). Which of the following relational algebra expressions returns the set of eids who own all brands?

Aπ_eid( π_eid,cid(Own) ÷ π_cid(Comp) ) Bπ_eid( π_eid(Own) × π_cid(Comp) ) Cπ_eid( π_eid,cid(Own) × π_cid(Comp) ) Dπ_eid( π_eid(Own) × (π_cid,Name(Own) ÷ π_cid(Comp)) )

Q27.

Consider R(A, B, C, D, E) be relation with following dependencies: C->F, E->A, EC->D, A->B. Which of the following is a key for R ?

ACD BEC CAE DAC

Next: Mock 3 – Relational Model, Algebra & Calculus (Part 2) →