Database Design and Normalization

19.1 Modeling Process

Requirements → conceptual (ERD) → logical (normalized relations) → physical (types, indexes, partitioning, tablespaces).

Tool: SQL Developer Data Modeler (forward and reverse engineering).

19.2 ER Concepts

Entities, attributes, relationships, cardinality (1:1, 1:N, M:N with junction tables), optionality, supertypes and subtypes, weak entities.

19.3 Normal Forms

Atomic values, no repeating groups No partial dependency on a composite key No transitive dependency Every determinant is a candidate key No multi-valued or join dependencies

19.4 Denormalization and Physical Design

Materialized views, summary tables, virtual columns, redundant FKs for performance, IOTs, clusters, partitioning.

19.5 Data Warehouse Design

Star and snowflake schemas, fact and dimension tables, SCD types (1, 2, 3, 6), conformed dimensions, bitmap indexes and star transformation, dimensions and hierarchies for query rewrite, analytic views.

19.6 Anti-Patterns

EAV overuse, comma-separated lists in columns, missing FKs, generic "one true lookup table", storing dates as strings, using NUMBER without precision for everything, surrogate-only designs without natural unique constraints.