The Design Process, ER and EER - Database Cheat Sheet

Read an EER diagram, then build one hierarchy three ways in SQLite: one table full of NULLs, a table per subclass, and concrete tables whose ids clash. Plus the four design phases, (min, max) on a recursive link, and weak entities.

On the sheet:

- Reading an EER diagram: disjoint, overlapping and union, with a symbol key

- Four phases, in order: requirements to physical

- Recursive link, read with (min, max): a supervisor counted at both ends

- EER words: IS-A, inheritance, specialization and generalization

- Map 1: one table: a discriminator column, and the NULLs it leaves

- Map 2: a table per subclass: a shared key, a JOIN and ON DELETE CASCADE

- Map 3: concrete classes: tables stacked with UNION ALL, and ids that clash

- Choosing a map: the measured price of each

- Weak entity: owner key plus partial key

Two A4 pages, printed front and back, sized to print once and keep beside you while you work. Written in short sentences and plain English, for readers whose first language is not English and clearer for everyone else because of it.

It pairs with the Database Design Process, Entity-Relationship Modeling, Enhanced ER (EER) Modeling and Model to Tables: Relational Mapping books, so you can learn each idea in full and then revise it here in two pages.

Every query on this sheet was run against a live database before the page was printed, so the result you read is the result you get. Instant PDF download. By M.R. Sterling.