Twelve questions from all phases, mixed on purpose so you must first recognise which idea each needs. Running data: STUDENT has 5 rows (Esha's cgpa is NULL and she has no enrolments), COURSE has C1–C4, ENROLL has 7 rows, as in Lesson 3.3.4.
12 questions · 19 marks · 35 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 · NAT · 2 mark(s) — ER mapping
E1 —R1— E2 is 1:N and E2 —R2— E3 is M:N. E1 has one multivalued attribute. All participations are partial. Minimum number of tables?
Numerical answer: ________
Q2 · NAT · 2 mark(s) — Candidate keys
R(A, B, C, D, E), F = {AB → C, C → D, D → A}. How many candidate keys?
Numerical answer: ________
Q3 · NAT · 1 mark(s) — NULL
SELECT COUNT(*) FROM STUDENT WHERE cgpa <> 8.4;
Numerical answer: ________
Q4 · NAT · 1 mark(s) — Relational algebra
How many tuples are in πroll(ENROLL) − πroll(σgrade='A'(ENROLL))?
Numerical answer: ________
Q5 · MCQ · 2 mark(s) — Normal forms
R(A, B, C, D), F = {A → B, B → A, A → C, C → D}. Highest normal form?
- (A) 1NF
- (B) 2NF
- (C) 3NF
- (D) BCNF
Q6 · NAT · 2 mark(s) — B+ tree order
B = 4096, K = 12, P = 8. Order p of a B+ tree internal node?
Numerical answer: ________
Q7 · MCQ · 2 mark(s) — Serializability
S = R1(A) R2(A) R3(B) W1(A) R2(C) R2(B) W2(B) W1(C). Which serial schedule is S conflict equivalent to?
- (A) T1, T2, T3
- (B) T3, T2, T1
- (C) T2, T3, T1
- (D) none
Q8 · MCQ · 1 mark(s) — Recoverability
W1(X) R2(X) C1 C2 is:
- (A) not recoverable
- (B) strict
- (C) recoverable but not cascadeless
- (D) cascadeless but not strict
Q9 · MCQ · 2 mark(s) — Decomposition
R(A, B, C, D, E), F = {A → B, C → D}, decomposed into {AB, CD, ACE}. It is:
- (A) lossless, not preserving
- (B) lossless and dependency preserving
- (C) lossy, preserving
- (D) lossy, not preserving
Q10 · NAT · 1 mark(s) — B-tree capacity
B-tree of order 4 (max children), height 3. Maximum number of keys?
Numerical answer: ________
Q11 · NAT · 2 mark(s) — Schedules
T1, T2, T3 have 3, 3 and 1 operations. How many schedules?
Numerical answer: ________
Q12 · MCQ · 1 mark(s) — TRC
On the 5-row STUDENT, { s.name | s ∈ STUDENT ∧ ¬∃e ∈ ENROLL (e.roll = s.roll) } returns:
- (A) ∅
- (B) {Esha}
- (C) all five names
- (D) {Diya, Esha}
✓Answer Vault
Reveal answers when you’re ready
Q1 Answer: 5 · 2 mark(s)
E1, E2, E3 (3) + R2 (1) + multivalued attribute (1). R1 is a foreign key in E2.
Re-read: Lesson 2.4.2 — Mapping 1:1, 1:N, M:N with participation; minimum number of tables
Q2 Answer: 3 · 2 mark(s)
B and E are never on the right: BE⁺ = BE. ABE, BCE, BDE each reach everything.
Re-read: Lesson 4.2.3 — Attribute closure; all candidate keys; prime vs non-prime; counting superkeys
Q3 Answer: 3 · 1 mark(s)
Asha, Bhavin, Diya. Chirag is FALSE; Esha is UNKNOWN.
Re-read: Lesson 3.3.4 — NULL in SQL: three-valued logic, IS NULL, comparisons
Q4 Answer: 2 · 1 mark(s)
{1, 2, 3, 4} − {1, 3} = {2, 4}.
Re-read: Lesson 3.1.2 — Union, intersection, difference; union compatibility
Q5 Answer: (B) · 2 mark(s)
Keys A and B (single attributes, so no partial dependency). C → D: C is not a superkey and D is non-prime: transitive.
- (A) is wrong: No partial dependency exists.
- (C) is wrong: C → D violates 3NF.
- (D) is wrong: Not 3NF.
Re-read: Lesson 4.4.5 — Drill: highest normal form of a given relation
Q6 Answer: 205 · 2 mark(s)
8p + 12(p − 1) ≤ 4096 → 20p ≤ 4108 → 205.
Re-read: Lesson 5.6.2 — B+ tree order from block size, key size and pointer size
Q7 Answer: (B) · 2 mark(s)
R2(A) before W1(A): T2 → T1. R3(B) before W2(B): T3 → T2. R2(C) before W1(C): T2 → T1. Order T3, T2, T1.
- (A) is wrong: T2 must precede T1.
- (C) is wrong: T3 must precede T2.
- (D) is wrong: Acyclic.
Re-read: Lesson 6.4.2 — Conflict serializability: conflicting ops, precedence graph, equivalent serial orders
Q8 Answer: (C) · 1 mark(s)
T2 reads uncommitted X (not cascadeless) but commits after T1 (recoverable).
- (A) is wrong: Commit order is fine.
- (B) is wrong: No.
- (D) is wrong: The read is dirty.
Re-read: Lesson 6.5.1 — Recoverable, cascadeless and strict schedules; how they nest
Q9 Answer: (B) · 2 mark(s)
Chase: the ACE row gains B (A → B) and D (C → D), becoming all a's. Both FDs lie inside pieces.
- (A) is wrong: Both FDs are inside pieces.
- (C) is wrong: It is lossless.
- (D) is wrong: Both hold.
Re-read: Lesson 4.3.1 — Lossless-join decomposition: binary test and chase (table) method
Q10 Answer: 63 · 1 mark(s)
4³ − 1.
Re-read: Lesson 5.5.3 — B-tree deletion basics; min/max keys; height bounds
Q11 Answer: 140 · 2 mark(s)
7!/(3!3!1!).
Re-read: Lesson 6.3.1 — Schedules: serial vs concurrent; counting possible schedules
Q12 Answer: (B) · 1 mark(s)
Only Esha has no enrolment.
- (A) is wrong: Esha qualifies.
- (C) is wrong: Four students have enrolments.
- (D) is wrong: Diya took C4.
Re-read: Lesson 3.2.2 — Exists and for-all queries; translating between TRC and RA