Databases

Q1. (3 marks — 1 each) (a) A tuple/row is a single record of the relation, one set of related attribute values. (b) An attribute/column is a named field with a defined data type/domain across all rows. (c) NULL represents a missing/unknown/inapplicable value — it is not zero or empty string, and comparisons with it yield UNKNOWN. Why: tests the three foundational relational vocabulary items.

Q2. (4 marks)

• Super key: any set of attributes that uniquely identifies a row (may contain extra attributes). (1)
• Candidate key: a minimal super key — no attribute can be removed while keeping uniqueness. (1)
• Primary key is chosen from the candidate keys. (1)
• Natural key comes from real-world data (e.g. email); surrogate key is a system-generated artificial value (e.g. auto-increment ID) with no business meaning. (1)

Q3. (4 marks — 1 each)

• Atomicity — a transaction executes fully or not at all (all-or-nothing).
• Consistency — a transaction moves the DB from one valid state to another, preserving constraints.
• Isolation — concurrent transactions appear to execute serially; intermediate states are hidden.
• Durability — once committed, changes survive crashes/power loss.

Q4. (4 marks — 2 each) (a)

SELECT dept, COUNT(*) AS n
FROM Employee
GROUP BY dept
HAVING COUNT(*) > 5;

Why: filtering on an aggregate requires HAVING, not WHERE. (2; 1 if WHERE misused)

(b)

SELECT name, salary
FROM Employee
WHERE salary = (SELECT MAX(salary) FROM Employee);

or ORDER BY salary DESC LIMIT 1. (2)

Q5. (5 marks)

• INNER JOIN returns only rows with a matching key in both tables. (1)
• LEFT OUTER JOIN returns all rows of the left table; unmatched right columns become NULL. (1)
• (a) INNER → matches {2,3} → 2 rows (1)
• (b) LEFT → all of A {1,2,3}; 1 gets NULLs → 3 rows (1)
• (c) CROSS → 3 × 3 = 9 rows (1)

Q6. (4 marks — 1 for each correct pairing, 1 for naming all three)

• Dirty read → prevented from READ COMMITTED upward.
• Non-repeatable read → prevented from REPEATABLE READ upward.
• Phantom read → prevented only at SERIALIZABLE. (Naming the three anomalies correctly = 1; three correct levels = 3.)

Q7. (5 marks) (a) 2NF: in 1NF and no partial dependency — every non-prime attribute depends on the whole candidate key, not part of it. (1.5) (b) 3NF: in 2NF and no transitive dependency — non-prime attributes depend only on candidate keys, not on other non-prime attributes. (1.5) (c) BCNF: for every functional dependency X→Y, X is a super key. (1) (d) Any of: insertion / update / deletion anomaly. (1)

Q8. (4 marks)

• CAP theorem: a distributed system cannot simultaneously guarantee all three of Consistency, Availability, and Partition tolerance. (1)
• During a partition (P is unavoidable in distributed systems) the system must choose between C and A. (1)
• CP system: rejects/blocks requests to keep data consistent (sacrifices availability). (1)
• AP system: keeps responding but may return stale data (sacrifices strong consistency). (1)

Q9. (4 marks — 2 each) (a) age = 30 (equality) → hash index is optimal (O(1) point lookup); B-tree also works. (2) (b) BETWEEN 20 AND 40 (range) → B-tree index, because it stores keys in sorted order and supports range scans; a hash index cannot serve ranges. (2)

Q10. (3 marks)

Why: RANK gives ties the same rank and skips the next value(s) (so after two 1's it jumps to 3). (1 for the tie=1, 1 for skip to 3, 1 for 4)

[
  {"claim":"INNER JOIN of {1,2,3} and {2,3,4} yields 2 rows",
   "code":"A={1,2,3}; B={2,3,4}; result=(len(A & B)==2)"},
  {"claim":"CROSS JOIN of 3 and 3 rows yields 9 rows",
   "code":"result=(3*3==9)"},
  {"claim":"LEFT JOIN keeps all 3 left rows",
   "code":"A={1,2,3}; result=(len(A)==3)"},
  {"claim":"RANK over scores [90,90,80,70] desc gives [1,1,3,4]",
   "code":"scores=[90,90,80,70]; sd=sorted(scores,reverse=True); ranks=[sd.index(s)+1 for s in scores]; result=(ranks==[1,1,3,4])"}
]