Lesson 12

Database Normalization: 1NF and 2NF Conclusion

Normalization organizes relations according to their keys and dependencies so that independently maintained facts do not have to be repeated unnecessarily. ^[1] This module developed that idea through first normal form, functional dependencies, and second normal form. It also showed why reaching 2NF does not complete every database design.

The practical outcome is a method you can apply to a new schema or a legacy table: establish what a row means, identify the governing business rules, determine the candidate keys, and separate facts whose dependencies require different relations. Then verify that the decomposition preserves the information and relationships you started with.

This conclusion brings the lessons together rather than treating each normal form as an isolated definition. Use the linked reviews to revisit a difficult step, work through the combined example, and complete the quiz before continuing to Third Normal Form.

1. Normalize Facts, Not Just the Appearance of a Table

The introduction to normal forms and definition of normalization established that table width and table count do not determine design quality. A narrow table can mix unrelated facts; a wide table can be appropriate when its attributes depend on the same key. Each decomposition needs a reason grounded in the meaning of the data.

Consider an order line that repeats the customer's current telephone number. The order-product association is a distinct fact, but the telephone number describes the customer. If the same customer places many orders, the combined design requires maintaining many copies of one current contact detail. Repeating CustomerID in those orders is useful; repeating the customer's independently changing description is the concern.

The objectives of normalization are illustrated by three anomalies. An insertion anomaly prevents recording a customer before an order exists. An update anomaly requires changing the same phone number in many rows. A deletion anomaly loses the only stored customer details when the customer's last order is removed. These problems arise when facts with different lifecycles share one required row.

Separating customers from orders allows each to be maintained appropriately. Foreign keys preserve their connections, and joins combine their information for reports. Normalization therefore does not mean losing the ability to retrieve a complete business view. It changes where facts are maintained, while preserving how they relate.

2. First Normal Form Makes Repeated Items Explicit

First Normal Form provides the conventional relational representation used in this course: one value from the intended domain at each attribute position, without repeating groups. A date can be one value even though it has year, month, and day components. Atomicity is relative to the model, not physical indivisibility.

The book examples distinguish interchangeable author slots from attributes with different roles. Author1 and Author2 impose an arbitrary limit on a list of contributors. A third author requires another column, and searching for an author requires checking several columns. StartDate and EndDate, by contrast, express different roles; having the same data type does not make them a repeating group.

The practical correction is to represent each contributor association as a row. The course uses numbered author slots as a repeating-group design; formal interpretations of scalar columns should not distract from its central problem, which is the fixed limit on a relationship that can have many occurrences.

In achieving 1NF, the minimal two-table example uses BOOK and AUTHOR, with BookID as a foreign key in AUTHOR. In that particular design, AuthorID identifies a book-specific entry. It does not identify a person across the catalog. If the same person contributes to two books, that design records two separate entries.

For a catalog that maintains authors independently, use Book, Author, and BookAuthor. The association table connects books and people without assuming names are unique. If contributor order matters, preserve it in an AuthorOrder attribute and retrieve results with ORDER BY. Decomposition must retain meaningful ordering and associations, not merely produce smaller tables.

3. A Flat Table Can Still Be Poorly Structured

The limitations of 1NF explain why single-valued cells do not establish that a schema is fully normalized. A flat order-line relation can still repeat order dates, customer details, and product descriptions. Checking its format tells you little about which identifiers determine those attributes.

Do not conclude that every 1NF relation necessarily has anomalies. A relation in 3NF is also in 1NF. The point is that the 1NF test alone does not establish whether higher normal-form conditions hold. Further analysis may reveal defects, or confirm that no decomposition is needed under the stated dependencies.

Meaning also determines whether apparent duplication is intentional. A product's current catalog price is different from the price agreed on a past order. Replacing every historical sale price with the current catalog price would destroy information. Likewise, an address captured for a shipment is not necessarily the customer's current address. Name and document these different facts explicitly.

4. Functional Dependencies Express Business Rules

A functional dependency, written X → Y, means that rows agreeing on X must also agree on Y in every valid state of a relation. X and Y may contain one or several attributes. For a customer model allowing one current phone number, CustomerID → Phone expresses that rule.

The arrow does not imply a formula, an immutable value, or a one-to-one relationship. A customer's phone can change between valid states. Several customers may share one phone number, so the reverse dependency does not follow. A reverse arrow requires a separate rule, just as a name should not be assumed unique because it happens to be unique in a small sample.

A determinant is the attribute set on the left of an arrow. It is not necessarily a candidate key. CourseID can determine CourseTitle while appearing in many enrollment rows. A superkey determines every attribute of a relation; a candidate key is a minimal superkey. One candidate key is selected as the primary key, and the others remain relevant to normalization.

The lesson on identifying dependencies begins with requirements: what does a row represent, which period does it cover, and which exceptions are permitted? A course offering may need CourseNr, Semester, and Year together. Parallel sections may require another identifier. Co-teaching changes the assumption that an offering determines one professor.

Use sample data to look for contradictions. Two legitimate rows with the same proposed determinant and different dependent values disprove the proposed dependency. No contradictions in the present data do not prove that the rule holds for all permitted future data. Interviews, documented requirements, and explicit constraints provide the necessary context.

Logical inference helps once the rules are established. If an offering determines ProfID and ProfID determines ProfName, then the offering determines ProfName. Attribute closure applies dependencies repeatedly to determine everything implied by a proposed key. Reaching all attributes establishes a superkey; checking that no proper subset does so establishes minimality.

5. Second Normal Form Removes Partial Dependencies

Second Normal Form requires 1NF and full functional dependency of every non-prime attribute on every candidate key. A prime attribute belongs to at least one candidate key; a non-prime attribute belongs to none. Full dependency means that no proper subset of the key still determines the attribute.

For a student-course relation keyed by (StudentID, CourseID), the student's name depends on StudentID alone and the course title depends on CourseID alone. Those are partial dependencies. A final grade depends on the full pair under the rule that each student has one enrollment and one final grade per course within the modeled period.

The complete 2NF decomposition therefore keeps student details in Student, course details in Course, and the student-course association in StudentCourse. StudentName must be retained in Student; removing it without a destination would lose data. If Grade exists, it remains with the enrollment.

Checking only a selected single-column primary key can miss a partial dependency involving an alternate composite candidate key. Adding a surrogate identifier does not erase the business dependencies. Similarly, deciding that a column “belongs to the entity” or is a foreign key is not a formal 2NF test. Determine which attributes actually suffice to identify its value.

6. Bring the Steps Together: Orders and Products

Assume one customer per order, one occurrence of each product per order, and one current name per customer and product. Quantity and AgreedUnitPrice describe a particular order-product association. These assumptions make the pair (OrderID, ProductID) the candidate key for an order line.

OrderLineDraft(
    OrderID, ProductID, OrderDate,
    CustomerID, CustomerName, ProductName,
    Quantity, AgreedUnitPrice
)

The draft uses individual rows instead of numbered product slots, so its representation meets the course's 1NF convention. However, it combines facts with several determinants:

OrderID → OrderDate, CustomerID
CustomerID → CustomerName
ProductID → ProductName
OrderID, ProductID → Quantity, AgreedUnitPrice

OrderDate and CustomerID depend on only OrderID, while ProductName depends on only ProductID. CustomerName is also determined by OrderID through CustomerID. The draft therefore has partial dependencies relative to the pair key, including one obtained by inference rather than a directly listed arrow.

Orders2NF(OrderID PK, OrderDate, CustomerID, CustomerName)
Product(ProductID PK, ProductName)
OrderLine(OrderID PK/FK, ProductID PK/FK,
          Quantity, AgreedUnitPrice)

Under the stated dependencies, this design reaches 2NF. Orders2NF deliberately retains CustomerName to expose the next issue. The order-line pair remains the composite primary key, and its two components reference the corresponding order and product. The agreed historical price stays with the purchase.

If the same product can occupy several lines of one order, change the row identifier, perhaps to (OrderID, LineNumber). Do not enforce the pair-key example on an application with different rules. Reassess the dependencies after changing the meaning of a row.

7. Verify the Decomposition, Not Just Its Appearance

A lossless decomposition reconstructs the original relation from its projections without missing associations or inventing combinations. In the order example, each line joins to one order through OrderID and one product through ProductID. Those identifiers determine the corresponding parent descriptions.

Verification should account for every original attribute and relationship. Compare reconstructed values, not just total row counts. Test orders with several products, products on several orders, and parents without children. An inner join is suitable for reconstructing existing order lines, but a report listing every catalog product may need a left join to retain unsold products.

Also consider dependency preservation: can the original rules still be checked without recombining all tables? Primary and unique keys enforce identifiers, foreign keys enforce valid references, and required-value or check constraints implement additional rules. No single constraint type expresses every business requirement.

Before migration, investigate conflicting descriptions for one identifier. DISTINCT removes identical rows, not contradictions. Load referenced parent records before their children, validate constraints, and retain a rollback copy. A decomposition diagram is a design proposal until the migrated data and reconstructed results have been checked.

8. Why 2NF Is Not the Finish Line

The limitations of 2NF concern dependencies that remain after partial dependencies have been removed. In Orders2NF, an order determines its customer and the customer determines a current name. CustomerID does not identify an order, because one customer can place many orders. CustomerName can therefore repeat across orders even though this relation satisfies 2NF under the example's rules.

The next decomposition places the current customer name in Customer and retains CustomerID in Orders as a foreign key. This removes the need to rename the same customer in several orders. It also allows a customer to exist independently of orders.

The CD example makes another distinction important. A source extract repeating CDNo for different distributors cannot use CDNo alone as its key. First separate distributor assignments from CD details. Only then use the CD relation to demonstrate the residual dependency CatNo → CatName. A category name repeated across CDs can still produce insertion, update, and deletion anomalies.

The common 3NF problem is a non-superkey determinant controlling a non-prime attribute. More precisely, 3NF requires every nontrivial dependency X → A to have either a superkey determinant X or a prime dependent attribute A. Not every dependency inferred through transitivity violates 3NF. Module 4 develops this distinction and the further decompositions it supports.

9. Combine Normalization with Modeling and Testing

Entity-relationship modeling identifies the objects and associations the application needs. Normalization checks how attributes depend on identifiers inside the resulting relations. These approaches complement each other, but neither guarantees the correct schema without accurate requirements, and they need not produce identical designs under different assumptions.

Use both iteratively. Review cardinality, optional participation, historical requirements, and data lifecycles with users. A foreign key may ensure that an enrollment references an existing student without requiring every student to have an enrollment. That distinction belongs in the model and must remain clear in the implementation.

Evaluate performance separately from normal-form compliance. Indexes, query design, and workload measurements influence response time. Deliberate denormalization needs a documented benefit and a method for maintaining redundant facts consistently. Normalization is not a promise that every query becomes faster, nor is a larger number of tables proof that the design is better.

Module Review Checklist

Explain which independently maintained facts a combined table repeats.
Distinguish repeating groups from attributes with different roles.
State the meaning and time scope of a row before selecting its keys.
Identify candidate keys and dependencies from rules, not sample uniqueness.
Locate non-prime attributes determined by proper subsets of candidate keys.
Preserve all attributes and associations during decomposition.
Verify reconstruction and constraints with representative data.
Recognize remaining dependencies that require analysis beyond 2NF.

Formal 2NF Reminder

A relation must first satisfy 1NF. For every candidate key K and non-prime attribute A, no proper subset of K may determine A. Check dependencies implied by the rules as well as those explicitly listed. The terminology “every candidate key” is essential when several possible identifiers exist.

Glossary

Candidate key: A minimal attribute set that determines the entire row. Minimal means no attribute can be removed while retaining that property.
Composite key: A key containing more than one attribute. “Concatenated key” is an older term; joining the values into one string is not required.
Determinant: The attribute set on the left of a functional dependency. It may determine only part of a row and need not be a key.
Prime attribute: An attribute belonging to at least one candidate key. Non-prime attributes belong to none.
Full dependency: A dependency whose determinant has no proper subset that still determines the dependent attributes.
Partial dependency: For the 2NF test, a dependency of a non-prime attribute on a proper subset of a candidate key.
Transitive inference: Deriving X → Z from X → Y and Y → Z. Additional key conditions determine whether the result exposes a normal-form violation.
Lossless decomposition: A decomposition whose projections can be joined to reconstruct the original relation without spurious tuples.
Dependency preservation: The ability to enforce the original functional dependencies through the dependencies on the decomposed relations.
Normalization: Organizing relations according to their dependencies to reduce redundant facts while preserving information and relationships.

Normalization Quiz and Next Module

Take the Normalization Quiz to check your understanding. For each answer, identify the applicable rule instead of relying only on a slogan or the visual shape of a table.

Continue to Third Normal Form to examine the dependencies and anomalies that 2NF can leave unresolved.

[1]Normalization: Organizing relations according to their keys and dependencies to reduce redundant facts while preserving information and relationships.