Quick Revision
High-yield summary and quick revision notes for Database Management System.
1. ACID Properties and Recovery Summary
| Property | Definition | Failure Scenario from Practice | Resolution Mechanism |
|---|---|---|---|
| Atomicity | All 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. |
| Consistency | Database 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. |
| Isolation | Execution 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. |
| Durability | Once 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 Construct | Relational Mapping Rule | Concrete Example from Course Slides |
|---|---|---|
| Strong Entity | Map to separate table. Attributes become columns; key becomes Primary Key (PK). | Customer Customer(customer_id [PK], name, phone) |
| Weak Entity | Map to own table. PK = Parent PK + Partial Key (discriminator). Parent PK is Foreign Key. | Attendance(session_id [FK], member_id [FK]) |
| Composite Attribute | Decompose into primitive atomic fields. | Name first_name, last_name |
| Multi-valued Attribute | Create separate table with parent PK + attribute value as composite PK. | Customer_Addresses(customer_id [FK], address) |
| Relationship | Place PK of one entity as Foreign Key in the other table with a UNIQUE constraint. | Department managed by Employee dept(..., manager_emp_id [FK, UNIQUE]) |
| Relationship | Place PK of the "1" side as a Foreign Key in the "N" side table. | Customer to Order Orders(order_id [PK], customer_id [FK], ...) |
| Relationship | Create separate bridge/junction table. PK = composite of both entity PKs. | Order to MenuItem Order_Items(order_id [FK], item_id [FK], qty) |
3. SQL Joins Comparison
Loading diagram...
INNER JOIN: Returns rows where join condition evaluates toTRUEin both tables.LEFT OUTER JOIN: Returns all rows from left table; non-matching right columns are filled withNULL. 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 withNULL.FULL OUTER JOIN: Combines left and right outer joins; unmatched rows on either side appear withNULL.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...
-
First Normal Form (1NF):
- All column values must be atomic (indivisible).
- No repeating groups or arrays.
- Primary key identified.
-
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!
-
Third Normal Form (3NF):
- Must be in 2NF.
- No Transitive Dependencies: For every non-trivial functional dependency :
- is a Superkey, OR
- is a Prime Attribute (part of some candidate key).
-
Boyce-Codd Normal Form (BCNF):
- Must be in 3NF.
- For every non-trivial functional dependency , MUST be a Superkey.
5. SQL Subquery Operators Quick Reference
| Operator | Usage Pattern | Evaluation Condition |
|---|---|---|
IN | WHERE id IN (SELECT id FROM ...) | Evaluates to TRUE if value equals any item in result list. |
NOT IN | WHERE 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! |
EXISTS | WHERE EXISTS (SELECT 1 FROM ... WHERE ...) | Evaluates to TRUE if subquery returns at least 1 row (stops searching immediately). Safest for subqueries with NULLs. |
NOT EXISTS | WHERE 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 / > SOME | WHERE salary > ANY (SELECT salary ...) | Evaluates to TRUE if value is greater than the minimum value of the subquery result. |
> ALL | WHERE 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:TRUEif the lastFETCHreturned a row.%NOTFOUND:TRUEif the lastFETCHfailed to find a row.%ROWCOUNT: Total number of rows fetched so far.%ISOPEN:TRUEif 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 |
|---|---|---|
INSERT | NULL | Contains newly inserted values. |
UPDATE | Contains values prior to update. | Contains proposed post-update values. |
DELETE | Contains 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;
/