Phase 3 Checkpoint (15 questions)

Running data: STUDENT with 5 rows (Esha's cgpa is NULL, she has no enrolments), COURSE C1–C4, ENROLL with 7 rows. Relational algebra and calculus questions use set semantics; SQL questions use SQL semantics.

15 questions · 25 marks · 45 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 · 1 mark(s) — Projection

How many tuples are in πdept, credits(COURSE)?

Numerical answer: ________

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

How many tuples are in ENROLL ⋈ COURSE ⋈ STUDENT (natural joins, 5-row STUDENT)?

Numerical answer: ________

Q3 · MCQ · 2 mark(s) — Division

πroll, cid(ENROLL) ÷ πcid(σdept='CS' ∨ dept='EE'(COURSE)) returns:

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

Q4 · NAT · 2 mark(s) — Outer join

R(A, B) = {(1, a), (2, a), (3, b)}, S(B, C) = {(a, x), (c, y)}. How many tuples are in R ⟗ S?

Numerical answer: ________

Q5 · MCQ · 1 mark(s) — Equivalence

Which rewrite is not valid in general?

1. (A) πA(R ∩ S) = πA(R) ∩ πA(S)
2. (B) σc(R ∩ S) = σc(R) ∩ σc(S)
3. (C) σc1(σc2(R)) = σc2(σc1(R))
4. (D) πA(R ∪ S) = πA(R) ∪ πA(S)

Q6 · MCQ · 2 mark(s) — TRC

{ s.roll | s ∈ STUDENT ∧ ∃c ∈ COURSE (c.dept = 'ME' ⇒ ∃e ∈ ENROLL (e.roll = s.roll ∧ e.cid = c.cid)) } on the 5-row STUDENT returns:

1. (A) ∅
2. (B) {1, 2, 3, 4}
3. (C) {4}
4. (D) all five rolls

Q7 · MCQ · 1 mark(s) — Safety

Which TRC expression is unsafe?

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

Q8 · NAT · 1 mark(s) — DISTINCT

How many rows: SELECT DISTINCT cid FROM ENROLL WHERE roll IN (1, 3);

Numerical answer: ________

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

How many rows: SELECT * FROM STUDENT WHERE NOT (cgpa >= 8 OR dept = 'EE');

Numerical answer: ________

Q10 · NAT · 2 mark(s) — Aggregates

SELECT SUM(credits) FROM COURSE C JOIN ENROLL E ON C.cid = E.cid WHERE E.roll = 1;

Numerical answer: ________

Q11 · MCQ · 2 mark(s) — Outer join count

SELECT S.name, COUNT(E.cid) FROM STUDENT S LEFT JOIN ENROLL E ON S.roll = E.roll AND E.grade = 'A' GROUP BY S.name; gives Bhavin:

1. (A) 0
2. (B) 1
3. (C) NULL
4. (D) no row

Q12 · NAT · 2 mark(s) — Set ops

R = {5, 5, 5, 6}, S = {5, 6, 6}. How many rows in R EXCEPT ALL S plus rows in R INTERSECT ALL S?

Numerical answer: ________

Q13 · NAT · 2 mark(s) — Subquery

How many rows: SELECT name FROM STUDENT WHERE cgpa < ALL (SELECT cgpa FROM STUDENT WHERE dept = 'CS' AND cgpa IS NOT NULL);

Numerical answer: ________

Q14 · MCQ · 2 mark(s) — Correlated

SELECT C.title FROM COURSE C WHERE NOT EXISTS (SELECT * FROM STUDENT S WHERE S.dept = C.dept AND NOT EXISTS (SELECT * FROM ENROLL E WHERE E.roll = S.roll AND E.cid = C.cid)); returns:

1. (A) Circuits, Thermo
2. (B) no rows
3. (C) DBMS, Networks, Circuits, Thermo
4. (D) Circuits only

Q15 · MCQ · 1 mark(s) — Views

Which statement about views is false?

1. (A) A normal view stores its rows on disk.
2. (B) A view can hide columns from users.
3. (C) A view with GROUP BY is generally not updatable.
4. (D) Views support logical data independence.