Database Design: From ER Modeling to Normalization

Good database design not only satisfies user requirements but also has a profound impact on applications; however, designing a database is a complex process that benefits from a systematic methodology.

The design process typically includes three stages: (1) fully describing future users' data needs, (2) selecting a data model and converting requirements into a conceptual schema, and (3) transforming the abstract model into an implementation through logical and physical design.

The article focuses on constructing a data model (the ER model) and converting it into a relational schema, while briefly mentioning requirements analysis and physical design.

Conceptual Model (ER) – The Entity‑Relationship model is the most widely used conceptual model. It defines three basic concepts: entity sets, relationship sets, and attributes. Entities represent distinguishable objects; attributes describe properties, with some serving as keys. Relationship sets capture associations between entities and may have their own attributes.

Entity sets can be strong or weak. Weak entity sets depend on another entity for identification and are depicted with a dashed underline on their key attributes. Relationship sets are shown as diamonds (double diamonds for weak‑entity participation) and their attributes are placed in rectangles connected by dashed lines.

Attributes are classified as simple vs. composite, single‑valued vs. multi‑valued, and derived. The article explains how each type is handled when mapping to relational tables.

Constraints – Cardinality (one‑to‑one, one‑to‑many, many‑to‑one, many‑to‑many) and participation constraints (total vs. partial) describe how many instances of one entity may be associated with another.

Mapping to Relational Schemas – Strong entities with simple attributes map directly to tables with matching attributes and primary keys. Complex attributes lead to additional tables for their component simple attributes; multi‑valued attributes generate separate tables linked by the entity’s primary key; derived attributes are omitted.

Weak entities are mapped similarly, but their primary key includes the key of the owning strong entity. Relationship sets become tables containing the attributes of the relationship plus the primary keys of all participating entities; the choice of primary key depends on the cardinality.

Normalization – The article illustrates why a relational schema derived from an ER model may still be poorly designed and introduces the normal forms to improve it.

First Normal Form (1NF) : each column holds atomic values and each row has a primary key.

Second Normal Form (2NF) : satisfies 1NF and all non‑key attributes are fully functionally dependent on the whole primary key.

Third Normal Form (3NF) : satisfies 2NF and non‑key attributes have no transitive dependencies on the primary key.

Boyce‑Codd Normal Form (BCNF) : satisfies 3NF and every determinant is a candidate key, eliminating remaining partial and transitive dependencies among keys.

Examples using a library borrowing table demonstrate how each normal form removes redundancy and improves data integrity, and how higher normal forms may still require further decomposition (e.g., BCNF) to avoid update anomalies.

Conclusion – High‑quality database design requires a solid ER model, careful mapping to relational schemas, and appropriate normalization. Understanding the domain model and the specific DBMS features (e.g., storage engines, indexing) is essential for building robust, maintainable databases.