★ ER question: draw in this order

1. Nouns → entity rectangles; stable descriptors → attributes.
2. Underline each identifier. Split address only if the prompt exposes its parts.
3. Verbs → relationship diamonds. Put dates/quantity/price on the relationship they describe.
4. Add both cardinalities last; test with one sentence from each side.

M:N with history: GUEST — BOOKING(start_date,end_date) — ROOM. Repeat booking means the relational key must distinguish dates.

★ ER → relations

Entity → table + PK. 1:N → FK on N side. 1:1 → FK on mandatory/total side + UNIQUE. M:N → bridge with both FKs; add relationship attributes. Multivalued → own table. Recursive → self-FK or bridge.

HOTEL(hotel_id, …)
ROOM(room_id, hotel_id FK, type_id FK, …)
BOOKING(room_id FK, guest_id FK, start_date, end_date)

★ Key by closure

Start with attributes never on any FD right-hand side. Repeatedly add RHS when LHS ⊆ closure. If closure = all attributes, it is a superkey. Remove each attribute in turn; if none can be removed, candidate key.

R(A,B,C,D,E), A→C, B→C, E→D: RHS={C,D}; start ABE; (ABE)⁺=ABCDE ⇒ key ABE.

★ Normal forms

1NF one value/cell. 2NF 1NF + no non-prime attribute depends on a proper subset of a candidate key. 3NF for every non-trivial X→A: X superkey or A prime. BCNF every determinant X is a superkey.

Hotel proof: IDs→own facts; (room,guest,start)→end. Scalar cells; no partial/transitive FD; every determinant is a key ⇒ BCNF. Never infer ZIP→City from a sample.

DDL frame

CREATE TABLE child (
  id integer PRIMARY KEY,
  name varchar(255) NOT NULL,
  parent_id integer NOT NULL REFERENCES parent(id),
  amount numeric(10,2) CHECK (amount BETWEEN 0 AND 10000),
  start_date date NOT NULL,
  end_date date NOT NULL,
  CHECK (end_date > start_date)
);

PostgreSQL: connect to database d → CREATE SCHEMA s → CREATE TABLE s.table(...). A schema is a namespace inside one database.

Easy FK: column → type → NOT NULL → REFERENCES table(key). Use varchar(50) for compact codes/address parts and varchar(255) for names/free text; these are conventions, not presets.

Paper checks

• Every table has a PK.
• Every FK points to a declared PK/UNIQUE key.
• ZIP/phone/card are text, not arithmetic numbers.
• Bridge PK contains repeat discriminator when repeats are allowed.
• NOT NULL for mandatory participation; CHECK for domain rules.

★ RA operators

σ rows · π columns · ρ rename · ∪ / ∩ / − sets · × pairs · ⋈_θ matched pairs. Extended: ⟕ left join · γ group/aggregate · τ sort.

RA→SQL: σ=WHERE; π=SELECT DISTINCT; ⋈=JOIN; ⟕=LEFT JOIN; γ=GROUP BY; σ after γ=HAVING; τ=ORDER BY. State “extended RA” for COUNT/SUM/order.

★ SQL logical order

FROM/JOIN → WHERE → GROUP BY → HAVING → SELECT → DISTINCT → ORDER BY → LIMIT

WHERE filters rows before grouping. HAVING filters groups after aggregation. Every selected non-aggregate belongs in GROUP BY.

Aggregate frame

SELECT h.name, COUNT(b.room_id) AS visits
FROM hotel h
LEFT JOIN room r ON r.hotel_id=h.id
LEFT JOIN booking b ON b.room_id=r.id
GROUP BY h.id, h.name
ORDER BY h.name;

LEFT JOIN preserves zero-booking hotels. COUNT(non-null right key), not COUNT(*).

Anti-join / “never”

SELECT COUNT(*)
FROM customer c
WHERE NOT EXISTS (
  SELECT 1 FROM orders o
  JOIN order_item oi USING(order_id)
  JOIN item i USING(item_id)
  WHERE o.customer_id=c.customer_id
    AND i.name LIKE 'A%'
);

Prefer NOT EXISTS; NOT IN becomes UNKNOWN if the subquery contains NULL.

★ Query translation

“each/per” → GROUP BY. “only once” → DISTINCT. “total >” → SUM + HAVING. “all, even none” → LEFT JOIN. “ever” → EXISTS/join + DISTINCT. “never” → NOT EXISTS. “between” → BETWEEN (inclusive).

ACID + WAL

A all-or-nothing · C constraints preserved · I concurrent result equivalent to allowed serial behavior · D committed data survives failure.

WAL first: log record reaches durable storage before the dirty page. Checkpoint flushes dirty pages and records a recovery start point; too frequent ⇒ more I/O/full-page WAL.

Final 60-second audit

• Aliases and join predicates present?
• Scheduled vs actual time?
• Date boundary inclusive/exclusive?
• DISTINCT requested?
• Zero rows must survive?
• GROUP BY and HAVING in correct place?
• Units, relation schema, and final result shown?

◇ Low-return memory

View = stored query. Procedure = CALL, may manage transaction. Function returns value/table and is used in expressions. Trigger runs automatically on an event. Learn after ER, normalization, RA and SELECT.