Final Exam — Pattern & Cheat Sheet

CSE 303 · Database Management · Final Term · Total marks 40 · 9:00 – 11:00 AM · answer ALL questions · partial grading is available. CLO1 = 18 marks, CLO2 = 22 marks.

Instructions on the paper: 1. Answer ALL questions. 2. Mobile phones or any electronic gadget is strictly prohibited. 3. Partial grading is available — so never leave a box empty; a correct SELECT … FROM … WHERE skeleton already earns marks.

How the 40 marks are built

Part	Topic	Marks	CLO	Split	What is asked
Part 1	SQL — Basics	8	CLO1	Q1 (a) 3 · (b) 3 · (c) 2	Aggregate + GROUP BY, multi-table join with COUNT(DISTINCT), count per group.
Part 2	SQL — Views, Integrity Constraints, Authorization	10	CLO1	Q2 3+3 · Q3 1+3	Create a view and query it; define referential integrity + CREATE TABLE with cascading actions.
Part 3	SQL — Function and Trigger	9	CLO2	Q4 5 · Q5 4	Write an AFTER UPDATE trigger writing to two log tables; write a scalar stored function.
Part 4	Storage and File Structure	13	CLO2	Q6 3 · Q7 3+1.5 · Q8 1+2.5+2	RAID-level name mapping, MTTDL calculation + conclusion, RAID 4 parity and recovery.
Total		40	18 (CLO1) + 22 (CLO2)

Strategy

Bank Part 4 first — 13 marks of pure recall + arithmetic

the RAID mapping table and the MTTDL formula are the cheapest marks on the paper

Then Part 3 — the trigger and function are template answers

memorise the DELIMITER … BEGIN … END skeleton

Then Part 2 — CREATE VIEW + CREATE TABLE with foreign keys

Leave Part 1 queries for last — they need the most thinking per mark

17 of the 40 marks (Parts 3 + Q6/Q7) are pattern answers you can write from memory.

One-page cheat sheet

SQL skeletons

memorise these

-- count per group
SELECT col, COUNT(*)          FROM t GROUP BY col;
SELECT col, COUNT(DISTINCT x) FROM t GROUP BY col;

-- view + use the view
CREATE VIEW v AS SELECT a, COUNT(*) AS c FROM t GROUP BY a;
SELECT * FROM v WHERE c = (SELECT MAX(c) FROM v);

-- referential integrity + cascading
FOREIGN KEY (child_col) REFERENCES parent(parent_key)
    ON DELETE CASCADE    -- or SET NULL / SET DEFAULT / RESTRICT
    ON UPDATE CASCADE

-- trigger
DELIMITER $$
CREATE TRIGGER name AFTER UPDATE ON t FOR EACH ROW
BEGIN
    IF NEW.c <> OLD.c THEN INSERT INTO log VALUES (...); END IF;
END$$
DELIMITER ;

-- function
DELIMITER $$
CREATE FUNCTION f(p INT) RETURNS DECIMAL(10,2)
DETERMINISTIC READS SQL DATA
BEGIN
    DECLARE v DECIMAL(10,2);
    SELECT col INTO v FROM t WHERE id = p;
    RETURN v;
END$$
DELIMITER ;

RAID level names — worth 3 marks every time

Level	Elaborated name
RAID 0	Block striping; non-redundant
RAID 1	Mirrored disks with block striping
RAID 2	Memory-Style Error-Correcting-Codes (ECC) with bit striping
RAID 3	Bit-Interleaved Parity
RAID 4	Block-Interleaved Parity
RAID 5	Block-Interleaved Distributed Parity
RAID 6	P + Q Redundancy scheme

The two formulas

\lambda = \frac{1}{\mathit{MTTF}} \qquad \mathit{MTTDL} = \frac{\mathit{MTTF}^2}{2 \times \mathit{MTTR}} \qquad \text{years} = \frac{\text{hours}}{24 \times 365}

P = D1 ⊕ D2 ⊕ … ⊕ Dn · Dk = P ⊕ (every surviving disk)
XOR column rule: even number of 1s → 0, odd → 1.

Mnemonics. NEW/OLD: “New comes IN, Old goes OUT” — INSERT has only NEW, DELETE only OLD, UPDATE both. WHERE vs HAVING: rows before, groups after. Cascade: “the child follows the parent”.

Every question worked out: Spring 2026 — Solved →