Data Modeling and Normalization

Introduction

A database is not merely a collection of tables. It is a formal representation of a business domain: its objects, events, rules, relationships, and constraints. Good data modeling translates business requirements into a structure that is accurate, maintainable, efficient, and capable of supporting reliable SQL queries.

Data modeling is particularly important in full-stack development because application defects frequently originate in poor data design. Ambiguous relationships, duplicated facts, uncontrolled null values, and inconsistent identifiers can produce incorrect reports, difficult updates, security weaknesses, and costly migrations. Normalization provides a principled method for reducing such problems, while selective denormalization can improve performance when its costs are understood and controlled.

This chapter explains how to translate requirements and entity–relationship (ER) models into relational schemas, analyze functional dependencies, apply normalization through Third Normal Form (3NF) and Boyce–Codd Normal Form (BCNF), and justify denormalization in production systems.

Learning Objectives

By the end of this chapter, you should be able to:

• identify entities, attributes, relationships, and business constraints;
• translate an ER model into relational tables and keys;
• distinguish candidate, primary, alternate, and foreign keys;
• express and analyze functional dependencies;
• identify insertion, update, and deletion anomalies;
• decompose relations into 1NF, 2NF, 3NF, and BCNF;
• evaluate lossless join and dependency preservation;
• design a normalized schema using SQL constraints; and
• justify selective denormalization using measurable requirements.

1. From Business Requirements to Data Models

Business requirements should be expressed as facts and rules before tables are designed. Consider the following requirements for an online learning platform:

1. A student may enroll in many courses.
2. A course may have many students.
3. Each course is taught by one or more instructors.
4. An enrollment records the enrollment date and final grade.
5. A course has a unique course code.
6. An instructor has a unique institutional email address.

The nouns suggest entities such as Student, Course, and Instructor. The verbs suggest relationships such as enrolls and teaches. The stated uniqueness rules and cardinalities become constraints.

A useful requirements analysis asks:

• What objects must be stored?
• What events or transactions must be recorded?
• Which facts describe an object, and which describe a relationship?
• Which values identify a row uniquely?
• How many instances may participate in a relationship?
• Is participation optional or mandatory?
• What historical facts must remain unchanged after later updates?

Entities, attributes, and relationships

An entity is a distinguishable object or concept, such as Customer or Product. An attribute describes an entity, such as customer name or product price. A relationship describes an association, such as a customer placing an order.

Cardinality specifies how many instances may participate:

• one-to-one (1:1): one instance relates to at most one instance of another entity;
• one-to-many (1:M): one instance relates to many instances;
• many-to-many (M:N): many instances relate to many instances.

Optionality specifies whether participation is required. For example, an employee may be required to belong to a department, while a department may temporarily have no employees.

An M:N relationship must become an associative relation in the relational model. For Student and Course, Enrollment is such a relation because the relationship itself has attributes, including enrollment date and grade.

2. Relational Schemas and Keys

A relation is represented as a table whose rows are tuples and whose columns are attributes. A relational schema describes the table structure, for example:

STUDENT(StudentID, FullName, Email)

A key is an attribute or combination of attributes that identifies rows.

• A superkey uniquely identifies a tuple, possibly with unnecessary attributes.
• A candidate key is a minimal superkey.
• A primary key is the candidate key selected as the main identifier.
• An alternate key is a candidate key not selected as primary.
• A foreign key references a candidate key in another relation.
• A composite key contains more than one attribute.

For example, both StudentID and Email may uniquely identify a student. StudentID can be the primary key and Email an alternate key enforced with UNIQUE.

A relational design for the learning platform could be:

CREATE TABLE Student (
    student_id  INTEGER PRIMARY KEY,
    full_name    VARCHAR(120) NOT NULL,
    email        VARCHAR(254) NOT NULL UNIQUE
);

CREATE TABLE Course (
    course_id    INTEGER PRIMARY KEY,
    course_code  VARCHAR(20) NOT NULL UNIQUE,
    title        VARCHAR(200) NOT NULL
);

CREATE TABLE Enrollment (
    student_id       INTEGER NOT NULL,
    course_id        INTEGER NOT NULL,
    enrollment_date  DATE NOT NULL,
    final_grade      DECIMAL(5,2),
    PRIMARY KEY (student_id, course_id),
    FOREIGN KEY (student_id) REFERENCES Student(student_id),
    FOREIGN KEY (course_id) REFERENCES Course(course_id)
);

The composite primary key prevents the same student from being enrolled in the same course more than once. If retakes are allowed, the business rule changes and the key might become (student_id, course_id, attempt_no).

Mapping common ER constructs

A multivalued attribute such as a student’s phone numbers should not be stored as Phone1, Phone2, and Phone3. Instead, use StudentPhone(student_id, phone_number), possibly with a type column and a suitable key.

3. Functional Dependencies

Normalization is based on functional dependencies (FDs). An FD X → Y means that whenever two rows agree on all attributes in X, they must also agree on all attributes in Y. X is the determinant, and Y is functionally dependent on X.

For example, in a course relation:

CourseCode → CourseTitle, CreditHours

This means a course code determines one course title and one credit value. It does not mean that every title determines one course code; the reverse dependency would need business justification.

An FD is a semantic rule about valid data, not merely a pattern observed in a current table. If two rows happen to have the same city and department today, that does not establish City → Department.

Keys and functional dependencies

A candidate key K for relation R is a minimal attribute set that functionally determines every attribute of R. In notation:

and no proper subset of K has that property.

Suppose:

Enrollment(StudentID, CourseID, StudentName, CourseTitle, Grade)

with dependencies:

• StudentID → StudentName
• CourseID → CourseTitle
• (StudentID, CourseID) → Grade

The likely candidate key is (StudentID, CourseID). StudentID alone does not determine a course, and CourseID alone does not determine a student.

Attribute closure

The closure of X, written X⁺, is the set of attributes functionally determined by X under a given FD set. It helps identify keys and test decomposition properties.

To compute X⁺:

1. Start with X⁺ = X.
2. If an FD Y → Z has Y contained in X⁺, add Z to X⁺.
3. Repeat until no new attributes can be added.

For the relation above, let X = {StudentID, CourseID}. Starting with these two attributes, the dependencies add StudentName, CourseTitle, and Grade. Therefore X⁺ contains all attributes, so X is a superkey. Since neither StudentID nor CourseID alone determines all attributes, the key is minimal and is a candidate key.

4. Why Normalize?

Poorly designed tables often store several independent facts together. This causes anomalies.

Consider:

ENROLLMENT_RECORD(StudentID, StudentName, CourseID, CourseTitle, InstructorID, InstructorName, Grade)

A student’s name is repeated for every course. A course title is repeated for every enrolled student.

Update anomaly

If an instructor’s name is stored in hundreds of rows, changing the name requires multiple updates. If one row is missed, the database contains conflicting values.

Insertion anomaly

If course information can be inserted only when a student enrolls, a new course with no enrollment cannot be represented.

Deletion anomaly

Deleting the last enrollment in a course may accidentally remove the only stored record of that course.

Normalization decomposes a relation into smaller relations so that each fact is stored in an appropriate place. The objective is not to create the maximum possible number of tables. It is to reduce redundancy while preserving meaning and constraints.

5. Normal Forms

First Normal Form (1NF)

A relation is in 1NF when each attribute contains atomic values according to the chosen domain, and repeating groups are not stored in columns or lists. A cell containing Java, Python, SQL violates a conventional 1NF design because it stores multiple values in one attribute.

Atomicity is context-dependent. A full postal address may be treated as one value if the application never searches its components, but it should be decomposed if street, city, and postal code must be validated or queried separately.

Second Normal Form (2NF)

A relation is in 2NF if it is in 1NF and every non-prime attribute is fully functionally dependent on every candidate key. A non-prime attribute is one that is not part of any candidate key.

2NF matters primarily when a candidate key is composite. A partial dependency occurs when a non-key attribute depends on only part of a composite key.

In the enrollment relation, StudentName depends only on StudentID, and CourseTitle depends only on CourseID. Therefore the relation is not in 2NF. Decompose it into Student, Course, and Enrollment.

Third Normal Form (3NF)

A relation is in 3NF if, for every nontrivial FD X → A, at least one of the following holds:

1. X is a superkey; or
2. A is a prime attribute.

A nontrivial FD is one where A is not already contained in X. 3NF eliminates problematic transitive dependencies in which a non-key attribute determines another non-key attribute.

For example:

EMPLOYEE(EmployeeID, DepartmentID, DepartmentName)

with:

• EmployeeID → DepartmentID
• DepartmentID → DepartmentName

DepartmentName is transitively dependent on EmployeeID through DepartmentID. Decompose into:

• Employee(EmployeeID, DepartmentID)
• Department(DepartmentID, DepartmentName)

Boyce–Codd Normal Form (BCNF)

A relation is in BCNF if, for every nontrivial FD X → Y, X is a superkey. BCNF is stricter than 3NF. A 3NF relation can contain an FD whose determinant is not a superkey if the dependent attribute is prime; BCNF disallows that exception.

BCNF is desirable when its decomposition does not make important dependencies difficult to enforce. However, 3NF is often selected because a dependency-preserving 3NF decomposition can be more practical.

6. A Worked Decomposition

Assume the relation:

REGISTRATION(StudentID, StudentName, CourseID, CourseTitle, InstructorID, InstructorName, Grade)

Business rules are:

• StudentID → StudentName
• CourseID → CourseTitle, InstructorID
• InstructorID → InstructorName
• (StudentID, CourseID) → Grade

The candidate key is (StudentID, CourseID).

Step 1: 1NF. Assume every column contains one atomic value.

Step 2: 2NF. StudentName depends only on StudentID; CourseTitle and InstructorID depend only on CourseID. Separate them:

• Student(StudentID, StudentName)
• Course0(CourseID, CourseTitle, InstructorID, InstructorName)
• Registration(StudentID, CourseID, Grade)

Step 3: 3NF. In Course0, InstructorName depends on InstructorID, not directly on CourseID. Separate instructors:

• Course(CourseID, CourseTitle, InstructorID)
• Instructor(InstructorID, InstructorName)

The final schema is:

Student(StudentID, StudentName)

Instructor(InstructorID, InstructorName)

Course(CourseID, CourseTitle, InstructorID)

Registration(StudentID, CourseID, Grade)

This design stores each student, instructor, course, and registration fact once. Foreign keys connect the relations and constraints enforce valid references.

7. Lossless Join and Dependency Preservation

Decomposition must preserve information. A decomposition is lossless, or non-additive, if joining the decomposed relations reconstructs exactly the valid original relation, without generating spurious rows.

For a binary decomposition of R into R₁ and R₂, a common sufficient condition is that the common attributes functionally determine all attributes of R₁ or all attributes of R₂. If the common attributes are a key of one component, the join is generally lossless under the dependency assumptions.

Dependency preservation means that constraints can be enforced by checking the decomposed relations individually, without repeatedly joining them. A decomposition may be lossless but not dependency-preserving. This matters operationally because cross-table constraints can be expensive or difficult to enforce with ordinary declarative SQL constraints.

The standard synthesis approach for 3NF commonly seeks a lossless, dependency-preserving decomposition. BCNF decomposition can sometimes sacrifice dependency preservation, so designers must evaluate the practical consequences.

8. Implementing a Normalized Design in SQL

Normalization is a logical design activity; SQL implementation adds physical and integrity decisions.

CREATE TABLE Department (
    department_id   INTEGER PRIMARY KEY,
    department_name VARCHAR(100) NOT NULL UNIQUE
);

CREATE TABLE Employee (
    employee_id     INTEGER PRIMARY KEY,
    department_id   INTEGER NOT NULL,
    employee_name   VARCHAR(120) NOT NULL,
    FOREIGN KEY (department_id)
        REFERENCES Department(department_id)
);

Primary keys establish identity. Foreign keys protect referential integrity. NOT NULL expresses mandatory participation, while UNIQUE expresses alternate keys. CHECK constraints express domain rules, such as:

CHECK (salary >= 0)

A foreign key does not automatically indicate whether deletion should cascade. ON DELETE CASCADE, RESTRICT, and SET NULL have different business consequences and must be selected deliberately. Cascading deletion may be appropriate for dependent line items, but dangerous for historical transactions.

Indexes should support common predicates, joins, and ordering requirements. A foreign key is logically required for integrity, but whether it needs an index depends on the database engine and workload. Normalization and indexing solve different problems: normalization controls redundancy and integrity; indexes improve access paths.

9. Illustrative Case: An Online Retail Platform

Background

An online retailer stores customers, products, orders, payments, and shipments. The original application uses one wide table containing customer details, order details, product details, and payment status.

Problem

The table contains repeated customer addresses and product descriptions. Updating a product price changes historical orders incorrectly if the order stores only a reference to the current product price. Deleting a canceled order can remove the only record of a customer’s address. Reports also show inconsistent totals because different application services calculate them independently.

Analysis

The design team identifies these facts:

• a customer can place many orders;
• an order belongs to one customer;
• an order contains one or more order lines;
• a product can occur on many order lines;
• an order line records the price agreed at the time of sale;
• a payment belongs to an order but may be attempted more than once.

The resulting schema includes:

• Customer(customer_id, name, email);
• Order(order_id, customer_id, ordered_at, status);
• Product(product_id, sku, current_price);
• OrderLine(order_id, product_id, quantity, sale_price); and
• Payment(payment_id, order_id, attempted_at, amount, status).

OrderLine.sale_price is not an accidental duplicate of Product.current_price. It represents a different business fact: the historical price agreed for that order line. This illustrates a crucial modeling principle: two similarly named attributes may legitimately coexist when they have different meanings, time dimensions, or determinants.

Approach and outcome

The team separates stable entities from transactional relationships, adds foreign keys and uniqueness constraints, and defines order totals as derived from order lines. If a materialized total is later stored for performance, a reconciliation process validates it against the authoritative lines.

The outcome is improved consistency, clearer auditability, and simpler transactional updates. The design also supports multiple payment attempts without overwriting history.

Learning points

• Model events, not only current states.
• Preserve historical facts explicitly.
• Distinguish current product price from transaction price.
• Treat repeated data as a signal to examine dependencies, not as automatic evidence that every duplicate is wrong.

10. Selective Denormalization

Denormalization intentionally introduces controlled redundancy or precomputed data to improve performance, simplify read-intensive access, or support reporting. It is not a replacement for sound logical modeling.

Common forms include:

• storing a cached order total;
• maintaining a summary table by day and product;
• duplicating a frequently displayed name in a search index;
• using a materialized view;
• embedding data in a document store for an aggregate read pattern.

Denormalization may be justified when:

1. a measured query or reporting workload is too slow;
2. joins or aggregations are demonstrably costly;
3. the redundant value has a clear source of truth;
4. update mechanisms are reliable; and
5. acceptable temporary inconsistency is explicitly defined.

A useful decision process is:

1. establish a normalized baseline;
2. measure query latency, throughput, storage, and lock contention;
3. identify the specific bottleneck;
4. consider indexes, query rewriting, partitioning, caching, or materialized views first;
5. document the invariant between source and copy;
6. implement synchronization and reconciliation; and
7. monitor correctness and performance after deployment.

A cached order_total creates an invariant such as:

order_total = Σ(quantity × sale_price)

where the sum is over all lines belonging to the order. This is a business invariant, not a universally appropriate physical design. If it is stored, updates to lines must update the total atomically, or a background process must detect and repair discrepancies.

Denormalization risks include stale values, inconsistent writes, increased storage, complicated transactions, and unclear ownership of truth. It should therefore be justified by evidence rather than intuition.

11. Contemporary and Professional Considerations

Modern applications frequently combine normalized transactional databases with search engines, caches, warehouses, and event streams. Each representation should have a defined purpose and consistency model. Change data capture can propagate normalized database changes to analytical systems, but downstream consumers must handle ordering, duplication, and schema evolution.

AI-assisted schema generation can suggest entities, keys, and SQL, but generated designs must be reviewed against actual business rules. AI may infer dependencies from sample data that are not true constraints, overlook privacy requirements, or create unsafe cascade actions. Human domain expertise and testing remain essential.

Data governance is also part of modeling. Designers should classify personal data, minimize collection, enforce least-privilege access, define retention periods, and record audit requirements. A normalized schema does not automatically provide privacy: excessive joins, broad views, or poorly protected foreign keys can still expose sensitive relationships.

Challenges and Limitations

Normalization depends on accurate dependencies. If requirements are incomplete, a formally elegant schema may still model the wrong domain. Excessive decomposition can increase query complexity, while insufficient decomposition causes redundancy and anomalies. Normal forms also do not address every concern: indexing, partitioning, workload management, authorization, temporal history, and data quality require additional techniques.

Real organizations may have legacy systems, conflicting identifiers, denormalized reporting extracts, and changing business rules. Schema evolution should therefore be managed through versioned migrations, backward-compatible deployment strategies, data profiling, and automated integrity tests.

Best Practices

• Begin with business facts and invariants, not table names.
• Record determinant assumptions explicitly.
• Identify candidate keys before selecting primary keys.
• Use associative tables for M:N relationships.
• Enforce important rules with database constraints where possible.
• Preserve historical values when their meaning is time-dependent.
• Test decompositions for lossless joins and dependency preservation.
• Use realistic data and workload tests, not only toy examples.
• Normalize first; denormalize only for a measured reason.
• Document every redundant attribute, its source of truth, and synchronization method.
• Review privacy, retention, and authorization implications during design.

Chapter Summary

Data modeling translates requirements into entities, attributes, relationships, keys, and constraints. ER constructs map systematically to relational schemas: entities become relations, 1:M relationships use foreign keys, and M:N relationships require associative relations. Functional dependencies describe business rules about which attributes determine others and provide the foundation for identifying candidate keys and normalization opportunities.

1NF removes repeating groups, 2NF removes partial dependencies on composite keys, 3NF removes problematic transitive dependencies, and BCNF requires every determinant of a nontrivial dependency to be a superkey. Decompositions should be evaluated for losslessness and dependency preservation. SQL keys, foreign keys, uniqueness, nullability, and checks turn logical rules into enforceable database behavior.

Normalization reduces update, insertion, and deletion anomalies, but it does not guarantee optimal performance for every workload. Selective denormalization can be appropriate when supported by measurements, explicit invariants, reliable synchronization, and monitoring. Professional database design balances correctness, performance, maintainability, governance, and the historical meaning of data.

Key Terms

Functional dependency: A rule stating that one attribute set determines another.

Determinant: The left-hand attribute set in a functional dependency.

Candidate key: A minimal attribute set that uniquely identifies a tuple.

Primary key: The selected candidate key used as the principal row identifier.

Alternate key: A candidate key not selected as the primary key.

Foreign key: An attribute set referencing a candidate key in another relation.

Composite key: A key consisting of multiple attributes.

Entity: A distinguishable object or concept represented in a data model.

Attribute: A property describing an entity or relationship.

Cardinality: The permitted number of relationship participants.

Associative relation: A table representing an M:N relationship.

1NF: Normal form requiring atomic values and no repeating groups.

2NF: 1NF plus absence of partial dependencies on a composite candidate key.

3NF: A normal form eliminating specified transitive dependencies.

BCNF: A normal form requiring every determinant of a nontrivial FD to be a superkey.

Lossless decomposition: A decomposition whose join reconstructs the original valid relation without spurious tuples.

Dependency preservation: The ability to enforce dependencies using the decomposed relations without joins.

Update anomaly: Inconsistency risk caused by repeated facts requiring multiple updates.

Denormalization: Deliberate, controlled introduction of redundancy for a justified purpose.

Review Questions

Short Answer Questions

1. What is the difference between a candidate key and a primary key?
2. Why must an M:N relationship usually be represented by an associative relation?
3. Define the functional dependency X → Y.
4. What type of anomaly occurs when deleting one fact unintentionally removes another fact?
5. State the main requirement of BCNF.

Long Answer Questions

1. Explain how business requirements are translated into entities, attributes, relationships, keys, and constraints.
2. Compare 1NF, 2NF, 3NF, and BCNF, including the types of dependency each addresses.
3. Explain lossless join and dependency preservation and why both matter.
4. Describe how foreign keys, NOT NULL, UNIQUE, and CHECK constraints implement data-model rules.
5. Discuss the advantages and risks of selective denormalization.

Analytical / Application Questions

1. Given OrderLine(OrderID, ProductID, ProductName, Quantity, SalePrice), identify likely functional dependencies and propose a normalized decomposition. Explain why SalePrice may remain in OrderLine.
2. A team proposes storing customer names in every order row to avoid a join. Design an evaluation plan to decide whether this denormalization is justified.
3. A university allows students to retake courses. Explain how this changes the key of an enrollment relation and what additional attributes or constraints may be required.

Multiple Choice Questions with Answers

Question 1

Which relation is normally used to represent a many-to-many relationship between Student and Course?

A. A single column in Student containing course IDs

B. An associative relation containing student and course foreign keys

C. A duplicate copy of Student for each course

D. A view with no underlying tables

Correct Answer: B. An associative relation containing student and course foreign keys

Question 2

Which statement best describes a functional dependency X → Y?

A. Every value of Y must be unique

B. X and Y must be stored in separate tables

C. Equal X values require equal Y values

D. Y must be a primary key

Correct Answer: C. Equal X values require equal Y values

Question 3

In Enrollment(StudentID, CourseID, StudentName, CourseTitle, Grade), which dependency is partial?

A. StudentID → StudentName

B. (StudentID, CourseID) → Grade

C. StudentID → CourseID

D. Grade → StudentName

Correct Answer: A. StudentID → StudentName

Question 4

Which normal form specifically removes partial dependencies on a composite candidate key?

A. 1NF

B. 2NF

C. 3NF

D. BCNF

Correct Answer: B. 2NF

Question 5

A relation is in BCNF when every determinant of a nontrivial functional dependency is what?

A. A foreign key

B. A non-prime attribute

C. A superkey

D. A nullable attribute

Correct Answer: C. A superkey

Question 6

Which constraint most directly prevents two students from having the same institutional email address?

A. CHECK

B. UNIQUE

C. DEFAULT

D. INDEX only

Correct Answer: B. UNIQUE

Question 7

Why should an order line usually store sale_price even when Product stores current_price?

A. The two values represent different historical business facts

B. Product IDs cannot be foreign keys

C. Sale prices are always primary keys

D. Normalization requires duplicate prices

Correct Answer: A. The two values represent different historical business facts

Question 8

Which situation is an update anomaly?

A. A new course can be inserted without students

B. Changing a repeated department name requires multiple row updates

C. A foreign key rejects an invalid reference

D. A composite key identifies an enrollment

Correct Answer: B. Changing a repeated department name requires multiple row updates

Question 9

What is the main purpose of testing a decomposition for losslessness?

A. To guarantee every query uses an index

B. To ensure joining decomposed relations does not create spurious rows

C. To remove all foreign keys

D. To ensure every relation has one column

Correct Answer: B. To ensure joining decomposed relations does not create spurious rows

Question 10

Which is the strongest general justification for denormalization?

A. A developer prefers fewer tables

B. Sample data contains repeated values

C. Measured workload evidence shows a controlled redundancy is needed

D. Foreign keys are inconvenient to write

Correct Answer: C. Measured workload evidence shows a controlled redundancy is needed