Final end-to-end test (30 questions, 90 min)

Thirty questions covering all phases (45 marks). Set a 90-minute timer and work on paper. For MSQ, record every correct option; for NAT, record a number. When you finish, open the final answer key below to check your work.

Running data used by some questions:

STUDENT

COURSE

ENROLL

30 questions · 45 marks · 90 minutes. Negative marking on MCQ only: −1/3 mark for a wrong 1-mark MCQ, −2/3 for a wrong 2-mark MCQ. No negative marking for MSQ or NAT; MSQ has no partial credit.

Q1 · MCQ · 1 mark(s) — ER notation

In an ER diagram, a dashed ellipse represents:

1. (A) a key attribute
2. (B) a weak entity
3. (C) a multivalued attribute
4. (D) a derived attribute

Q2 · NAT · 2 mark(s) — ER to tables

E1 (with 2 multivalued attributes) —1:N— E2 —M:N— E3, and E3 —1:1, both total— E4. Minimum number of tables?

Your answer:

Q3 · NAT · 1 mark(s) — Superkeys

R(A, B, C, D, E) has candidate keys {A, B} and {C}. How many superkeys?

Your answer:

Q4 · NAT · 2 mark(s) — Cascading delete

T(id, parent), parent → T.id ON DELETE CASCADE, holds (1, NULL), (2, 1), (3, 1), (4, 2), (5, 4), (6, 3), (7, 6), (8, NULL), (9, 8). After deleting id 3, how many tuples remain?

Your answer:

Q5 · MSQ · 1 mark(s) — Integrity

ENROLL.roll references STUDENT.roll (no referential actions declared). Which operations can violate referential integrity?

1. (A) Inserting into STUDENT
2. (B) Inserting into ENROLL
3. (C) Deleting from STUDENT
4. (D) Deleting from ENROLL

Q6 · NAT · 1 mark(s) — Projection

How many tuples are in πdept(σcgpa > 7(STUDENT))?

Your answer:

Q7 · MCQ · 2 mark(s) — Division

πroll, cid(ENROLL) ÷ πcid(σdept='CS'(COURSE)) is:

1. (A) {1, 2, 3}
2. (B) {3}
3. (C) {1}
4. (D) {1, 3}

Q8 · NAT · 2 mark(s) — Natural join

How many tuples are in STUDENT ⋈ COURSE (natural join)?

Your answer:

Q9 · MCQ · 1 mark(s) — TRC safety

Which tuple relational calculus expression is unsafe?

1. (A) { t | ¬(t ∈ R) }
2. (B) { t | t ∈ R ∧ t.A > 5 }
3. (C) { t | t ∈ R ∨ t ∈ S }
4. (D) { t | t ∈ R ∧ ¬(t ∈ S) }

Q10 · NAT · 2 mark(s) — NULL logic

SELECT COUNT(*) FROM STUDENT WHERE NOT (cgpa > 8 AND dept = 'CS');

Your answer:

Q11 · NAT · 2 mark(s) — GROUP BY

How many rows: SELECT dept FROM STUDENT GROUP BY dept HAVING AVG(cgpa) > 7;

Your answer:

Q12 · MCQ · 2 mark(s) — Correlated subquery

SELECT S1.name FROM STUDENT S1 WHERE (SELECT COUNT(*) FROM STUDENT S2 WHERE S2.cgpa > S1.cgpa) < 2; returns:

1. (A) Asha, Chirag
2. (B) Asha
3. (C) Asha, Chirag, Esha
4. (D) Asha, Bhavin, Chirag

Q13 · NAT · 1 mark(s) — Outer join

SELECT COUNT(*) FROM COURSE C LEFT JOIN ENROLL E ON C.cid = E.cid AND E.grade = 'B';

Your answer:

Q14 · NAT · 1 mark(s) — Bag operations

R holds the values 1, 1, 1, 2, 3 and S holds 1, 2, 2. How many rows are in R EXCEPT ALL S?

Your answer:

Q15 · NAT · 2 mark(s) — Candidate keys

R(A, B, C, D, E, F), F = {AB → C, C → D, D → A, E → F}. How many candidate keys?

Your answer:

Q16 · MCQ · 2 mark(s) — Normal forms

R(A, B, C, D, E), F = {A → BC, C → D, BD → E}. Highest normal form?

1. (A) 1NF
2. (B) 2NF
3. (C) 3NF
4. (D) BCNF

Q17 · NAT · 1 mark(s) — Minimal cover

How many FDs are in a minimal cover of {A → BC, B → C, AB → D}?

Your answer:

Q18 · MCQ · 2 mark(s) — Decomposition

R(A, B, C, D), F = {A → B, B → C, C → D}, decomposed into {AB, BC, CD}. It is:

1. (A) lossless and dependency preserving
2. (B) lossless, not preserving
3. (C) lossy, preserving
4. (D) lossy, not preserving

Q19 · MSQ · 1 mark(s) — BCNF

Which statements are true?

1. (A) Every BCNF relation is in 3NF.
2. (B) Every relation with two attributes is in BCNF.
3. (C) 3NF allows an FD X → A with X not a superkey if A is prime.
4. (D) A lossless BCNF decomposition always preserves dependencies.

Q20 · NAT · 1 mark(s) — Blocking factor

B = 4096 bytes, R = 250 bytes, unspanned. Blocking factor?

Your answer:

Q21 · NAT · 2 mark(s) — Multilevel index

r = 250,000 records, a secondary index on a key field, K = 10, P = 6, B = 1024. Block accesses to fetch one record through the multilevel index?

Your answer:

Q22 · NAT · 2 mark(s) — B+ tree order

B = 2048, K = 14, Pr = 10, P = 6. Order of a B+ tree leaf node (number of key–record-pointer pairs)?

Your answer:

Q23 · NAT · 1 mark(s) — B-tree insertion

A B-tree whose nodes hold at most 2 keys starts empty; insert 1, 2, 3, 4, 5, 6, 7, 8 in order. How many node splits occur?

Your answer:

Q24 · NAT · 1 mark(s) — B-tree bounds

B-tree of order 7 (max children), height 3. Minimum number of keys?

Your answer:

Q25 · MCQ · 2 mark(s) — Conflict serializability

S = R1(X) R2(Z) R1(Z) R3(X) R3(Y) W1(X) W3(Y) R2(Y) W2(Z) W2(Y). Which is true?

1. (A) S is conflict equivalent to T2, T3, T1
2. (B) S is conflict equivalent to T3, T1, T2
3. (C) S is not conflict serializable
4. (D) S is conflict equivalent to T1, T2, T3

Q26 · MCQ · 1 mark(s) — Recoverability

W1(A) R2(A) C1 W2(A) C2 is:

1. (A) strict
2. (B) not recoverable
3. (C) recoverable but not cascadeless
4. (D) cascadeless but not strict

Q27 · MCQ · 2 mark(s) — View serializability

S = R1(A) W2(A) R3(A) W1(A) W3(A). Which is true?

1. (A) S is conflict serializable
2. (B) S is view serializable but not conflict serializable
3. (C) S is neither
4. (D) S is serial

Q28 · MCQ · 1 mark(s) — 2PL

Which statement about basic two-phase locking is false?

1. (A) It guarantees conflict serializability.
2. (B) The serial order follows the lock points.
3. (C) It prevents deadlock.
4. (D) It may allow cascading rollbacks.

Q29 · MCQ · 1 mark(s) — Deadlock prevention

Wound-wait. T1 (TS = 10) holds item Q; T2 (TS = 5) requests Q. What happens?

1. (A) T2 waits
2. (B) T1 is rolled back
3. (C) T2 is rolled back
4. (D) Both wait

Q30 · NAT · 2 mark(s) — Timestamp ordering

TS(T1) = 1, TS(T2) = 2, TS(T3) = 3. Under basic timestamp ordering, schedule R2(A) W1(A) R3(B) W2(B) W3(A). How many transactions are aborted?

Your answer:

Show the key

🎓 That is the whole course.