Databases

Q1 (14)

(a) (7 marks)

SELECT c.city, SUM(o.amount) AS revenue
FROM customers c
JOIN orders o ON o.cust_id = c.cust_id
WHERE o.status = 'shipped'
GROUP BY c.city
HAVING SUM(o.amount) > 10000
ORDER BY revenue DESC
LIMIT 5;

Marks: correct JOIN (2), WHERE status filter (1), GROUP BY city (1), HAVING aggregate condition (1), ORDER BY DESC (1), LIMIT 5 (1).

(b) (4 marks) The move is not valid. WHERE filters individual rows before grouping; HAVING filters groups after aggregation. SUM(amount) is a group aggregate that does not exist at the WHERE stage, so WHERE SUM(amount)>10000 is illegal. (Evaluation order: FROM/JOIN → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT.) — 2 for verdict, 2 for evaluation-order reasoning.

(c) (3 marks) HAVING status='shipped' references a non-aggregated column not in the GROUP BY list (and evaluated post-grouping), which is a semantic error / non-deterministic. Also it fails to restrict which rows are summed, so the SUM includes non-shipped orders — giving wrong totals. (1 for it being invalid at group level, 2 for the wrong-total consequence.)

Q2 (12)

SELECT
  cust_id, order_id, order_date, amount,
  SUM(amount) OVER (PARTITION BY cust_id ORDER BY order_date
                    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total,
  LAG(amount) OVER (PARTITION BY cust_id ORDER BY order_date) AS prev_amount,
  DENSE_RANK() OVER (PARTITION BY cust_id ORDER BY amount DESC) AS amount_rank
FROM orders;

Marks: (a) running SUM with PARTITION+ORDER (4), (b) LAG with correct partition/order, NULL default is natural (4), (c) DENSE_RANK ordered DESC so largest = 1 (4). Deduct 1 each for missing PARTITION BY cust_id.

Q3 (10)

WITH RECURSIVE bounds AS (
  SELECT DATE_TRUNC('month', MIN(order_date)) AS first_m,
         DATE_TRUNC('month', MAX(order_date)) AS last_m
  FROM orders WHERE cust_id = 42
),
months AS (
  SELECT first_m AS month_start, last_m FROM bounds
  UNION ALL
  SELECT month_start + INTERVAL '1 month', last_m
  FROM months
  WHERE month_start + INTERVAL '1 month' <= last_m
)
SELECT month_start FROM months ORDER BY month_start;

Marks: anchor computing first/last month via DATE_TRUNC (3), recursive UNION ALL adding one month (3), termination condition <= last_m (2), correct final projection/order (2). Accept dialect variants; must have anchor + recursive step + terminating predicate.

Q4 (12)

(a) (5 marks) A lost update occurs when two transactions read the same value and both write back based on their stale read; one write silently overwrites the other's committed change.

Interleaving:

T1 read balance = 100
T2 read balance = 100
T1 write balance = 100 - 50 = 50, COMMIT
T2 write balance = 100 + 30 = 130, COMMIT   ← overwrites T1
Final = 130 (wrong).  Correct = 100 - 50 + 30 = 80.

2 for definition, 2 for interleaving, 1 for stating 130 wrong vs 80 correct.

(b) (3 marks) REPEATABLE READ is the lowest standard level that prevents lost update in a locking implementation (each transaction holds its read lock to end, so the second reader/writer blocks or is prevented). READ COMMITTED does not prevent it. (2 for level, 1 for justification.) (Accept discussion that lost update is not in the classic ANSI phenomena table but is prevented once repeatable read / long read locks are held.)

(c) (4 marks)

• Pessimistic: acquires an exclusive/write lock on the row on read (SELECT FOR UPDATE); the second transaction blocks until the first commits, then reads the updated value → no lost update, at cost of blocking. (2)
• Optimistic: both proceed without locking; at commit each checks a version/timestamp. The second commit detects the row version changed since its read and aborts/retries → no lost update, at cost of a rollback under contention. (2)

Q5 (12)

(a) (5 marks) Highest satisfied: 1NF only (atomic; but check 2NF). Actually check partial dependencies: key = {student_id, course_id}. student_id → student_name and course_id → course_name, instructor are partial dependencies on parts of the key → violates 2NF. So the relation is in 1NF but not 2NF. Marks: 2 for correct NF (1NF), 3 for identifying a partial dependency (e.g. course_id → course_name) as the 2NF violator.

(b) (5 marks) BCNF decomposition:

• students(student_id PK, student_name)
• courses(course_id PK, course_name, instructor)
• instructors(instructor PK, instructor_office)
• enrollments(student_id, course_id) — PK = {student_id, course_id}, FKs to students & courses.

Each table: every non-trivial FD's left side is a key → BCNF. Marks: 1 per correct table with key (cap 5). Note instructor → instructor_office (a transitive dep, 3NF/BCNF violation) is resolved by splitting instructors out.

(c) (2 marks) Example: to change an instructor's office you must update every enrollment row for every course they teach (update anomaly / inconsistency risk). After decomposition it's one row in instructors. (Accept insertion anomaly: can't record a course with no enrolled student in the original.)

[
  {"claim":"Lost update wrong final balance is 130 (T2 overwrites), correct is 80","code":"start=100; correct=start-50+30; lost=start+30; result = (correct==80 and lost==130)"},
  {"claim":"Q1 shipped-revenue threshold logic: a city with shipped totals 10000 is excluded (strict >), 10001 included","code":"th=10000; result = (not (10000>th)) and (10001>th)"},
  {"claim":"Recursive month sequence from Jan to Apr inclusive yields 4 rows","code":"first=1; last=4; rows=last-first+1; result = (rows==4)"},
  {"claim":"BCNF decomposition of enrollments yields 4 tables","code":"tables=['students','courses','instructors','enrollments']; result = (len(tables)==4)"}
]