Mixed practice set 3

Third mixed set. Same running data as Set 1.

12 questions · 20 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) — (min,max)

WORKS_IN(EMPLOYEE, DEPT) shows EMPLOYEE (1, 1) and DEPT (0, N). Which is true?

1. (A) Every employee works in exactly one department; a department may have none.
2. (B) Every department has at least one employee.
3. (C) The relationship is M:N.
4. (D) An employee may work in many departments.

Q2 · NAT · 2 mark(s) — ER mapping

EMPLOYEE has a recursive 1:N relationship SUPERVISES and a 1:1 relationship (both total) with PARKING_SPOT. How many tables at minimum?

Numerical answer: ________

Q3 · NAT · 2 mark(s) — Outer join + NULL

SELECT COUNT(*) FROM STUDENT S LEFT JOIN ENROLL E ON S.roll = E.roll WHERE E.grade IS NULL OR E.grade <> 'A';

Numerical answer: ________

Q4 · NAT · 1 mark(s) — INTERSECT

How many rows: SELECT roll FROM ENROLL INTERSECT SELECT roll FROM STUDENT WHERE dept = 'CS';

Numerical answer: ________

Q5 · MCQ · 2 mark(s) — Aggregates

SELECT dept, AVG(cgpa) FROM STUDENT GROUP BY dept HAVING COUNT(*) > COUNT(cgpa); returns:

1. (A) no rows
2. (B) (CS, 8.75)
3. (C) (CS, 5.83)
4. (D) (CS, 8.75), (EE, 7.8)

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

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

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

Q7 · NAT · 2 mark(s) — 3NF synthesis

How many relations does 3NF synthesis produce for R(A, B, C, D, E, F) with F = {A → B, C → D, E → F, AC → E}?

Numerical answer: ________

Q8 · NAT · 1 mark(s) — Spanned blocks

r = 80,000 records, R = 300 bytes, B = 4096, spanned. Number of blocks?

Numerical answer: ________

Q9 · NAT · 2 mark(s) — B+ capacity

A B+ tree has internal order p = 50 and leaf order p_leaf = 40. Maximum records with 3 levels?

Numerical answer: ________

Q10 · MCQ · 2 mark(s) — Serializability

S = R3(X) R2(X) W3(X) R1(X) W1(X). Equivalent serial order?

1. (A) T2, T3, T1
2. (B) T3, T2, T1
3. (C) T1, T2, T3
4. (D) not CS

Q11 · MCQ · 1 mark(s) — Thomas rule

TS(T) = 8, RTS(X) = 5, WTS(X) = 12. T issues write(X). With the Thomas write rule:

1. (A) T waits
2. (B) the write is ignored
3. (C) the write executes
4. (D) T aborts

Q12 · MSQ · 2 mark(s) — Recovery

Immediate update. Log: ⟨T1 start⟩ ⟨T1, A, 1, 2⟩ ⟨T1 commit⟩ ⟨checkpoint⟩ ⟨T2 start⟩ ⟨T2, B, 3, 4⟩ ⟨T3 start⟩ ⟨T3, A, 2, 7⟩ ⟨T3 commit⟩ — crash. Which actions are needed?

1. (A) Redo T3
2. (B) Undo T2
3. (C) Undo T3
4. (D) Redo T1