DBMS Notes & ER Diagrams

Entity — real-world object (rectangle)

Attribute — property (ellipse)

Relationship — association (diamond)

Cardinality: 1:1, 1:N, M:N

Participation: total (double line) / partial

Keys

• Super key — uniquely identifies
• Candidate key — minimal super key
• Primary key — chosen candidate key
• Foreign key — references primary key of another table

Core operators: σ (select), π (project), ⋈ (join), ∪, −, ×

SQL essentials

SELECT col FROM table
WHERE condition
GROUP BY col HAVING cond
ORDER BY col
JOIN ... ON ...

Joins: INNER, LEFT/RIGHT/FULL OUTER, CROSS

Subqueries: correlated vs non-correlated

Aggregates: COUNT, SUM, AVG, MIN, MAX

Why: remove redundancy, avoid update/insert/delete anomalies

• 1NF — atomic values, no repeating groups
• 2NF — 1NF + no partial dependency on composite key
• 3NF — 2NF + no transitive dependency
• BCNF — for every FD X→Y, X is super key

Functional Dependency: X → Y means Y is determined by X

Exam tip: Given FDs, find candidate keys then check highest normal form.

ACID

• Atomicity — all or nothing
• Consistency — valid state → valid state
• Isolation — concurrent txns don't interfere
• Durability — committed data persists

Problems: Dirty read, Non-repeatable read, Phantom read

Schedules: serial, serializable, conflict serializable

Locking: shared/exclusive, 2PL, deadlock

Timestamps & MVCC used in modern DBs

Purpose: speed up lookups

• Primary / Clustering index
• Secondary index
• Dense vs Sparse
• B+ Tree — most common (range queries efficient)
• Hash index — equality only

Trade-off: faster reads, slower writes, extra storage