2Database design & normalization

• 2.1Conceptual design and E-R diagrams

  Covers the fundamentals of the E-R diagram, which models a business domain as entities, relationships, and attributes; cardinality, which expresses the multiplicity of a relationship; and resolving many-to-many relationships, which cannot be represented directly in a relational database—framed as the practical judgment of deriving a relational schema from an E-R diagram.

• 2.2Functional dependency and normalization theory

  Covers functional dependency, which expresses a dependency between attributes; partial functional dependency, where an attribute depends on only part of a composite key; transitive functional dependency, where an attribute depends indirectly via a non-key attribute; and first, second, and third normal form, which progressively eliminate these dependencies to prevent update anomalies—framed as the practical design judgment of removing update anomalies.

• 2.3Boyce-Codd normal form and higher normalization

  Covers Boyce-Codd normal form (BCNF), which eliminates anomalies that survive even third normal form (every determinant of every functional dependency is a candidate key); fourth normal form, which eliminates multivalued dependencies; fifth normal form, which eliminates join dependencies; and the trade-off between higher normalization and reversibility of decomposition (lossless decomposition)—framed as a practical decomposition judgment.

• 2.4Data constraints and integrity

  Covers the primary key and candidate key that uniquely identify an entity, the foreign key and referential constraint that reference another table's primary key, and the check constraint (CHECK) that restricts values with an arbitrary condition—framed as design judgments for maintaining integrity.

• 2.5From logical design to physical design

  Covers physical design, which translates a normalized logical design into an actual storage scheme; denormalization, which intentionally introduces redundancy for performance; and the design judgment of balancing the trade-off between normalization and denormalization, and between JOIN cost and redundancy.