Skip to Content
PrepPath logoPrepPath

Quick Revision

High-yield summary and quick revision notes for Database Management System.

1. ACID Properties and Recovery Summary

PropertyDefinitionFailure Scenario from PracticeResolution Mechanism
AtomicityAll operations in a transaction succeed, or all are rolled back ("All-or-Nothing").Fund transfer debit succeeds on Account A, but crash occurs before crediting Account B.Write-Ahead Logging (WAL): Undo log reverts uncommitted changes during crash recovery.
ConsistencyDatabase moves from one valid state to another, preserving all explicit and implicit integrity constraints.Order created but inventory stock count remains unchanged; loan fee deducted without loan record.Transaction rollbacks, database constraints (CHECK, FOREIGN KEY), and atomic state transitions.
IsolationExecution of one transaction is unaffected by concurrently executing transactions.Two students simultaneously book the last seat in a course; both see it available and both confirm.Concurrency Control: Strict Two-Phase Locking (2PL), row-level locks (SELECT ... FOR UPDATE), or serializable isolation.
DurabilityOnce committed, data changes survive any subsequent server crash, reboot, or power outage.Cab booking confirmed to customer, but booking record disappears after server restart.Redo logging: log buffers flushed to non-volatile disk before sending commit acknowledgement.

2. E-R to Relational Mapping Rules

Conceptual ConstructRelational Mapping RuleConcrete Example from Course Slides
Strong EntityMap to separate table. Attributes become columns; key becomes Primary Key (PK).Customer →\rightarrow Customer(customer_id [PK], name, phone)
Weak EntityMap to own table. PK = Parent PK + Partial Key (discriminator). Parent PK is Foreign Key.Attendance(session_id [FK], member_id [FK])
Composite AttributeDecompose into primitive atomic fields.Name →\rightarrow first_name, last_name
Multi-valued AttributeCreate separate table with parent PK + attribute value as composite PK.Customer_Addresses(customer_id [FK], address)
1:11:1 RelationshipPlace PK of one entity as Foreign Key in the other table with a UNIQUE constraint.Department managed by Employee →\rightarrow dept(..., manager_emp_id [FK, UNIQUE])
1:N1:N RelationshipPlace PK of the "1" side as a Foreign Key in the "N" side table.Customer (1)(1) to Order (N)(N) →\rightarrow Orders(order_id [PK], customer_id [FK], ...)
M:NM:N RelationshipCreate separate bridge/junction table. PK = composite of both entity PKs.Order (M)(M) to MenuItem (N)(N) →\rightarrow Order_Items(order_id [FK], item_id [FK], qty)

3. SQL Joins Comparison

Loading diagram...
  • INNER JOIN: Returns rows where join condition evaluates to TRUE in both tables.
  • LEFT OUTER JOIN: Returns all rows from left table; non-matching right columns are filled with NULL. Used to find orphaned records (WHERE right.key IS NULL).
  • RIGHT OUTER JOIN: Returns all rows from right table; non-matching left columns are filled with NULL.
  • FULL OUTER JOIN: Combines left and right outer joins; unmatched rows on either side appear with NULL.
  • NATURAL JOIN: Automatically matches all identically named columns across tables and eliminates duplicate columns.

4. Normalization Rules and Decision Checklist

The Normalization Ladder

Loading diagram...
  1. First Normal Form (1NF):

    • All column values must be atomic (indivisible).
    • No repeating groups or arrays.
    • Primary key identified.
  2. Second Normal Form (2NF):

    • Must be in 1NF.
    • No Partial Dependencies: No non-prime attribute may depend on a proper subset of any candidate key.
    • Shortcut: If every candidate key consists of a single attribute, the table is automatically in 2NF!
  3. Third Normal Form (3NF):

    • Must be in 2NF.
    • No Transitive Dependencies: For every non-trivial functional dependency X→YX \rightarrow Y:
      • XX is a Superkey, OR
      • YY is a Prime Attribute (part of some candidate key).
  4. Boyce-Codd Normal Form (BCNF):

    • Must be in 3NF.
    • For every non-trivial functional dependency X→YX \rightarrow Y, XX MUST be a Superkey.

5. SQL Subquery Operators Quick Reference

OperatorUsage PatternEvaluation Condition
INWHERE id IN (SELECT id FROM ...)Evaluates to TRUE if value equals any item in result list.
NOT INWHERE id NOT IN (SELECT id FROM ...)Evaluates to TRUE if value does not match any item. Caution: If subquery returns even one NULL, NOT IN returns empty set!
EXISTSWHERE EXISTS (SELECT 1 FROM ... WHERE ...)Evaluates to TRUE if subquery returns at least 1 row (stops searching immediately). Safest for subqueries with NULLs.
NOT EXISTSWHERE NOT EXISTS (SELECT 1 FROM ... WHERE ...)Evaluates to TRUE if subquery returns 0 rows. Used for relational division (e.g. "employees who work on all projects").
> ANY / > SOMEWHERE salary > ANY (SELECT salary ...)Evaluates to TRUE if value is greater than the minimum value of the subquery result.
> ALLWHERE salary > ALL (SELECT salary ...)Evaluates to TRUE if value is greater than the maximum value of the subquery result.

6. PL/SQL Syntax Cheat Sheet

Basic Anonymous Block

DECLARE
    v_name  Students.name%TYPE;
    v_marks Students.marks%TYPE;
BEGIN
    SELECT name, marks INTO v_name, v_marks
    FROM Students
    WHERE student_id = 5;

    DBMS_OUTPUT.PUT_LINE('Name: ' || v_name || ', Marks: ' || v_marks);
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        DBMS_OUTPUT.PUT_LINE('Student not found.');
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);
END;
/

Explicit Cursor Lifecycle and Attributes

DECLARE
    CURSOR c_students IS
        SELECT student_id, marks FROM Students WHERE marks > 70;
    v_id    Students.student_id%TYPE;
    v_marks Students.marks%TYPE;
BEGIN
    OPEN c_students;
    LOOP
        FETCH c_students INTO v_id, v_marks;
        EXIT WHEN c_students%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE('ID: ' || v_id || ', Marks: ' || v_marks);
    END LOOP;
    CLOSE c_students;
END;
/
  • Cursor Attributes:
    • %FOUND: TRUE if the last FETCH returned a row.
    • %NOTFOUND: TRUE if the last FETCH failed to find a row.
    • %ROWCOUNT: Total number of rows fetched so far.
    • %ISOPEN: TRUE if cursor is currently open.

Cursor FOR Loop Pattern (Clean & Auto-Closing)

BEGIN
    FOR r IN (SELECT name, marks FROM Students WHERE department = 'CSE') LOOP
        DBMS_OUTPUT.PUT_LINE(r.name || ' scored ' || r.marks);
    END LOOP;
END;
/

Stored Procedure & Function

-- Function returning value
CREATE OR REPLACE FUNCTION Get_Marks (p_id IN NUMBER) RETURN NUMBER AS
    v_marks NUMBER;
BEGIN
    SELECT marks INTO v_marks FROM Students WHERE student_id = p_id;
    RETURN v_marks;
END;
/

-- Procedure updating data
CREATE OR REPLACE PROCEDURE Add_Bonus (p_id IN NUMBER, p_bonus IN NUMBER) AS
BEGIN
    UPDATE Students SET marks = marks + p_bonus WHERE student_id = p_id;
    COMMIT;
END;
/

Database Triggers (:OLD vs :NEW)

Operation:OLD Pseudorecord:NEW Pseudorecord
INSERTNULLContains newly inserted values.
UPDATEContains values prior to update.Contains proposed post-update values.
DELETEContains values of row being deleted.NULL
CREATE OR REPLACE TRIGGER Trg_Audit_Marks
AFTER UPDATE OF marks ON Students
FOR EACH ROW
WHEN (OLD.marks <> NEW.marks)
BEGIN
    INSERT INTO Student_Marks_Audit (student_id, old_marks, new_marks)
    VALUES (:OLD.student_id, :OLD.marks, :NEW.marks);
END;
/

On this page