DBMS Cheat Sheet

-- relation: a table
-- tuple: one row; attribute: one column
-- domain: the legal values of a column
Students(id, name, dept)

-- schema: the structure (stable)
-- instance: the rows right now
-- degree: number of columns
-- cardinality: number of rows

-- entity: primary key is never NULL
-- referential: a foreign key matches
--   a parent key, or is NULL
-- domain: age is an integer >= 0

-- super key: identifies a row (extras ok)
-- candidate: a minimal super key
-- primary: the candidate you pick
-- alternate: every other candidate

CREATE TABLE enroll (
  student_id INT REFERENCES students(id),
  course_id  INT,
  PRIMARY KEY (student_id, course_id)
);

-- surrogate: an id the db generates
--   no business meaning
-- natural: email, isbn — meaningful,
--   but the real world can change it

-- entity: a thing (Student, Course)
-- attribute: a fact about it
-- relationship: how entities connect
-- key attribute is underlined

-- 1:1  a person and a passport
-- 1:N  a dept and its employees
-- M:N  students and courses
-- M:N becomes its own table

-- no key of its own; needs the owner
-- Section depends on Course
-- partial key (room) plus Course id
-- double rectangle, double diamond

-- one wide table of students and courses
-- insert: no course without a student
-- update: dept name copied on many rows
-- delete: last student removes the course

-- 1NF  atomic cells, no repeating groups
-- 2NF  no partial dependency
--      on part of a composite key
-- 3NF  no transitive: non-key → non-key

-- student_id → name, dept
-- Armstrong: reflexivity,
--   augmentation, transitivity
-- closure of X: all that X determines
-- candidate key: closure is every column

-- BCNF: every determinant is a superkey
-- 4NF: no multi-valued dependency
-- 5NF: no hidden join dependency
-- denormalize only for a measured join

SELECT s.name, e.grade
FROM students s
JOIN enroll e
  ON e.student_id = s.id;

-- inner: only rows that match
-- left:  every left row; NULL if none
-- right: every right row
-- full:  both sides, NULL where missing

-- σ grade > 80 (Enroll)   select rows
-- π name (Student)         project cols
-- Student ⋈ Enroll         join
-- π ∪ π    same columns    union

CREATE INDEX enroll_student_idx
  ON enroll (student_id);
-- lookups and joins speed up
-- every insert and update pays

-- B+ tree: keys in order, leaves linked
--   equality and range (BETWEEN, ORDER)
-- hash: equality only, not a range
-- clustered: rows stored in key order

-- covering: the index holds every
--   column this query needs
-- skip: tiny tables, columns you
--   rewrite constantly, a flag alone

BEGIN;
UPDATE accounts SET bal = bal - 100
  WHERE id = 1;
UPDATE accounts SET bal = bal + 100
  WHERE id = 2;
COMMIT;     -- or ROLLBACK

-- atomicity    all of it, or none
-- consistency  constraints hold after
-- isolation    other work stays hidden
-- durability   commit survives a crash

-- READ UNCOMMITTED  dirty reads
-- READ COMMITTED    no dirty reads
-- REPEATABLE READ   row stays put
-- SERIALIZABLE      as if one by one
-- engines differ on phantoms

-- lost update: two writes, one vanishes
-- dirty read: saw a value that rolled back
-- non-repeatable: row changed mid-txn
-- phantom: a new row appears in a range

-- shared (S): many readers
-- exclusive (X): one writer
-- 2PL: grow (acquire), then shrink
-- no new lock after the first release

-- MVCC: a reader sees a snapshot
-- writers add a new version
-- deadlock: each waits on the other
-- detect it, abort one, let it retry

-- page (often 8 KB) is the I/O unit
-- buffer pool: those pages in memory
-- heap: rows appended, no order
-- sequential file: rows ordered by a key

EXPLAIN ANALYZE
SELECT * FROM enroll
WHERE student_id = 42;
-- seq scan reads the heap
-- index scan walks the B+ tree first

-- parse → plan → execute
-- cost ≈ pages read, not wall clock
-- a stale statistic picks a bad plan
-- selective predicates want an index

-- log the change before the data page
-- checkpoint: a shorter recovery start
-- redo what committed
-- undo what did not

-- partition: pick consistency or
--   availability, not both
-- CP waits or fails; AP answers, repairs
-- 2PC: prepare, then commit
-- a dead coordinator can stall everyone

CREATE VIEW dean_list AS
SELECT student_id, grade FROM enroll
WHERE grade >= 90;
-- a view is a stored query
GRANT SELECT ON dean_list TO advisor;