Second mixed set. Same running data as Set 1.
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 · MCQ · 1 mark(s) — Weak entity
A weak entity type's key is formed from:
- (A) all its attributes
- (B) the identifying relationship's attributes
- (C) its partial key alone
- (D) its owner's key plus its partial key
Q2 · NAT · 2 mark(s) — Superkeys
R(A, B, C, D, E, F) has candidate keys {A} and {B, C}. How many superkeys?
Numerical answer: ________
Q3 · NAT · 2 mark(s) — Self-join
SELECT COUNT(*) FROM STUDENT S1, STUDENT S2 WHERE S1.dept = S2.dept AND S1.roll < S2.roll;
Numerical answer: ________
Q4 · MCQ · 2 mark(s) — GROUP BY
SELECT dept FROM STUDENT S JOIN ENROLL E ON S.roll = E.roll GROUP BY dept HAVING COUNT(*) > 1; returns:
- (A) no rows
- (B) CS, EE, ME
- (C) CS
- (D) CS, EE
Q5 · NAT · 1 mark(s) — Outer join
R(A, B) = {(1, a), (2, b), (3, c)}, S(B, C) = {(a, x), (b, y), (b, z), (d, w)}. How many tuples are in R ⟕ S?
Numerical answer: ________
Q6 · NAT · 2 mark(s) — Minimal cover
How many FDs are in a minimal cover of {AB → C, A → B, B → C, C → D}?
Numerical answer: ________
Q7 · NAT · 2 mark(s) — Primary index
A file of 60,000 records with 30 records per block is sorted on its key. A single-level primary index has bfrᵢ = 40. Block accesses to fetch a record?
Numerical answer: ________
Q8 · NAT · 1 mark(s) — B-tree
In a B-tree of order 9 (max children), what is the minimum number of children of a non-root internal node?
Numerical answer: ________
Q9 · MCQ · 2 mark(s) — View serializability
S = R2(A) W1(A) W2(A) W3(A). Which is true?
- (A) CS, equivalent to T2, T1, T3
- (B) VS but not CS, equivalent to T2, T1, T3
- (C) neither CS nor VS
- (D) VS, equivalent to T1, T2, T3
Q10 · MCQ · 1 mark(s) — Locking
Which protocol guarantees no cascading rollbacks?
- (A) Basic 2PL
- (B) Basic timestamp ordering
- (C) Wait-die
- (D) Strict 2PL
Q11 · NAT · 2 mark(s) — Wait-die
Wait-die. Requests (each alone), as (requester TS, holder TS): (4, 8), (8, 4), (2, 6), (6, 2). How many requesters die?
Numerical answer: ________
Q12 · MCQ · 1 mark(s) — Anomaly
R1(X) R2(X) W1(X) W2(X) is an example of:
- (A) phantom
- (B) unrepeatable read
- (C) dirty read
- (D) lost update
✓Answer Vault
Reveal answers when you’re ready
Q1 Answer: (D) · 1 mark(s)
Owner key + partial key.
- (A) is wrong: Not the definition.
- (B) is wrong: No.
- (C) is wrong: Not unique across owners.
Re-read: Lesson 1.2.3 — Weak entity sets, identifying relationship, partial key
Q2 Answer: 40 · 2 mark(s)
Contain A: 32. Contain BC: 16. Contain ABC: 8. 32 + 16 − 8.
Re-read: Lesson 2.2.1 — Superkey, candidate key, primary key, alternate key; counting superkeys
Q3 Answer: 3 · 2 mark(s)
CS pairs (1,3), (1,5), (3,5); EE and ME have one student each.
Re-read: Lesson 3.3.3 — SELECT–FROM–WHERE, DISTINCT, aliases, LIKE, BETWEEN, ORDER BY; bags vs sets
Q4 Answer: (C) · 2 mark(s)
CS has 5 enrolment rows (Asha 3, Chirag 2); EE and ME have 1 each.
- (A) is wrong: CS qualifies.
- (B) is wrong: ME has 1.
- (D) is wrong: EE has 1.
Re-read: Lesson 3.3.5 — Aggregates, GROUP BY, HAVING; COUNT(*) vs COUNT(col); NULL handling
Q5 Answer: 4 · 1 mark(s)
Matches: (1, a, x), (2, b, y), (2, b, z); plus (3, c, NULL).
Re-read: Lesson 3.1.4 — Outer joins: left, right, full
Q6 Answer: 3 · 2 mark(s)
In AB → C, A is extraneous (B⁺ = BCD already contains C), so it becomes B → C, a duplicate. Nothing else is redundant: {A → B, B → C, C → D}.
Re-read: Lesson 4.2.5 — Minimal (canonical) cover
Q7 Answer: 7 · 2 mark(s)
2,000 data blocks → 50 index blocks → ⌈log₂ 50⌉ + 1 = 6 + 1.
Re-read: Lesson 5.3.2 — Primary, clustering, secondary indexes; counting index blocks and block accesses
Q8 Answer: 5 · 1 mark(s)
⌈9/2⌉.
Re-read: Lesson 5.5.1 — B-tree structure and order (order conventions and the GATE trap)
Q9 Answer: (B) · 2 mark(s)
R2(A) before W1(A): T2 → T1; W1(A) before W2(A): T1 → T2: cycle, not CS. T2 reads initial A, T3 writes last: T2, T1, T3 is view equivalent (blind writes W1, W3).
- (A) is wrong: There is a cycle.
- (C) is wrong: It is VS.
- (D) is wrong: T2 must read the initial A.
Re-read: Lesson 6.4.3 — View serializability; blind writes; relation to conflict serializability
Q10 Answer: (D) · 1 mark(s)
X locks held until commit.
- (A) is wrong: Can cascade.
- (B) is wrong: Not recoverable automatically.
- (C) is wrong: A deadlock scheme.
Re-read: Lesson 6.6.3 — Strict, rigorous and conservative 2PL
Q11 Answer: 2 · 2 mark(s)
Younger requesters die: (8, 4) and (6, 2).
Re-read: Lesson 6.6.4 — Deadlocks: wait-for graph, wait-die, wound-wait, starvation
Q12 Answer: (D) · 1 mark(s)
T1's write is overwritten.
- (A) is wrong: No.
- (B) is wrong: Not a re-read.
- (C) is wrong: No uncommitted read.
Re-read: Lesson 6.2.1 — Concurrency problems: lost update, dirty read, unrepeatable read, phantom