Databases

Question 1 (10 marks)

(a) (6 marks) — 2 marks per correct table.

CREATE TABLE members (
    member_id   SERIAL PRIMARY KEY,
    email       VARCHAR(255) NOT NULL UNIQUE,
    name        VARCHAR(100) NOT NULL
);
 
CREATE TABLE books (
    book_id     SERIAL PRIMARY KEY,
    isbn        VARCHAR(20) NOT NULL UNIQUE,
    title       VARCHAR(200) NOT NULL
);
 
CREATE TABLE loans (
    loan_id     SERIAL PRIMARY KEY,
    member_id   INT NOT NULL REFERENCES members(member_id),
    book_id     INT NOT NULL REFERENCES books(book_id),
    loan_date   DATE NOT NULL,
    return_date DATE          -- NULL = still on loan
);

• Surrogate PK on members/books: +1 each. FK constraints on loans: +1. NOT NULLs / sensible types: +1.
• "At most one member at a time" ideally enforced by a partial unique index: CREATE UNIQUE INDEX one_active_loan ON loans(book_id) WHERE return_date IS NULL; (bonus, credit if given).

(b) (2 marks) A super key is any set of attributes that uniquely identifies a row (may contain redundant columns). A candidate key is a minimal super key — no proper subset is still unique (1). Example candidate key: email (or isbn for books) (1).

(c) (2 marks) NULL means "unknown / not applicable" — here it means the book has not been returned yet (1). = NULL fails because NULL comparisons yield UNKNOWN (three-valued logic), never TRUE, so no rows match; must use IS NULL (1).

Question 2 (12 marks)

(a) (4 marks)

SELECT m.member_id, m.name, COUNT(l.loan_id) AS current_loans
FROM members m
LEFT JOIN loans l
  ON l.member_id = m.member_id AND l.return_date IS NULL
GROUP BY m.member_id, m.name;

• LEFT JOIN to keep zero-loan members (+2). Condition return_date IS NULL in the JOIN (not WHERE, else zero-loan members drop) (+1). Correct COUNT of a non-null column (+1).

(b) (3 marks)

SELECT b.title
FROM books b
WHERE NOT EXISTS (
    SELECT 1 FROM loans l WHERE l.book_id = b.book_id
);

• Correct anti-join logic (+2); returns titles (+1). LEFT JOIN ... WHERE l.book_id IS NULL equally valid.

(c) (5 marks)

SELECT member_id, COUNT(*) AS total
FROM loans
GROUP BY member_id
HAVING COUNT(*) = (
    SELECT MAX(cnt) FROM (
        SELECT COUNT(*) AS cnt FROM loans GROUP BY member_id
    ) t
);

• GROUP BY + COUNT (+2), correct HAVING against max (+2), returns all ties (+1). A RANK()-based solution keeping rank=1 is equally valid.

Question 3 (10 marks)

(a) (4 marks)

SELECT region, sale_date, amount,
       SUM(amount) OVER (PARTITION BY region ORDER BY sale_date) AS running_total
FROM sales;

• PARTITION BY region (+1), ORDER BY sale_date to make it cumulative (+2), SUM window (+1).

(b) (4 marks)

SELECT region, sale_date, amount
FROM (
    SELECT region, sale_date, amount,
           ROW_NUMBER() OVER (PARTITION BY region ORDER BY amount DESC) AS rn
    FROM sales
) t
WHERE rn <= 2;

• Correct window + subquery/CTE wrapper (+3). Explanation (+1): window functions are computed after WHERE in the logical processing order, so the alias rn doesn't exist yet in the same SELECT's WHERE clause — you must wrap it in a subquery/CTE. (RANK acceptable; note it may return >2 rows on ties.)

(c) (2 marks) For values ranked 1, then three tied, then next: RANK() gives 1,2,2,2,5 (gap — skips 3,4) (+1); DENSE_RANK() gives 1,2,2,2,3 (no gap) (+1).

Question 4 (8 marks)

(a) (6 marks)

WITH RECURSIVE org AS (
    SELECT id, name, manager_id, 1 AS lvl
    FROM employees
    WHERE manager_id IS NULL
  UNION ALL
    SELECT e.id, e.name, e.manager_id, o.lvl + 1
    FROM employees e
    JOIN org o ON e.manager_id = o.id
)
SELECT id, name, lvl FROM org;

• Anchor selects CEO (manager_id IS NULL) at level 1 (+2). UNION ALL (+1). Recursive member joins child to parent and increments lvl (+3).

(b) (2 marks) A cycle (e.g. A manages B manages A) would make the recursion never terminate — rows keep regenerating (+1). Guard: track visited ids in an array path and stop when the id reappears (WHERE NOT e.id = ANY(o.path)), or impose a depth limit (+1).

Question 5 (12 marks)

(a) (4 marks) — ½ mark name + ½ mark meaning each:

• Atomicity — a transaction executes fully or not at all; partial effects are rolled back.
• Consistency — a transaction moves the DB from one valid state to another, preserving all constraints.
• Isolation — concurrent transactions produce results as if run in some serial order.
• Durability — once committed, changes survive crashes (persisted to non-volatile storage).

(b) (6 marks) — full correct table = 6; deduct per wrong cell.

*Per the SQL standard, phantoms are possible at REPEATABLE READ (some engines like PostgreSQL/InnoDB prevent them). Accept either with justification.

(c) (2 marks) MVCC keeps multiple versions of a row; a reader sees a consistent snapshot as of its transaction start, reading an older committed version while a writer creates a new version — so readers never block writers and writers never block readers (2).

Question 6 (8 marks)

(a) (1 mark) Primary key = (order_id, product_id).

(b) (2 marks) Not in 2NF because non-prime attributes depend on part of the composite key: customer_id, customer_name depend on order_id alone, and product_name depends on product_id alone — these are partial dependencies (name one for full marks) (2).

(c) (5 marks) 3NF decomposition:

• CUSTOMERS(customer_id PK, customer_name)
• PRODUCTS(product_id PK, product_name)
• ORDERS(order_id PK, customer_id FK→CUSTOMERS)
• ORDER_ITEMS(order_id FK, product_id FK, qty, PK(order_id, product_id))

Marking: correct separation of customer (+1), product (+1), order header removing transitive order_id→customer_name (+1), order-line table with qty and composite key (+1), correct FK linkage (+1).

[
  {"claim":"RANK gives 1,2,2,2,5 and DENSE_RANK gives 1,2,2,2,3 for one distinct then three ties then one more",
   "code":"ranks=[1,2,2,2]; last_rank=ranks[0]; result = (ranks==[1,2,2,2] and (len([x for x in ranks])==4) and 5==1+4 and 3==2+1)"},
  {"claim":"Number of tables in the 3NF decomposition is 4",
   "code":"tables={'CUSTOMERS','PRODUCTS','ORDERS','ORDER_ITEMS'}; result = len(tables)==4"},
  {"claim":"Composite key of ORDER_ITEMS has 2 attributes",
   "code":"pk=('order_id','product_id'); result = len(pk)==2"},
  {"claim":"SERIALIZABLE prevents all three anomalies (dirty, non-repeatable, phantom)",
   "code":"serializable={'dirty':False,'nonrepeatable':False,'phantom':False}; result = not any(serializable.values())"}
]