Knowledge Hub

The Full Ladder:

UNF → 1NF → 2NF → 3NF → BCNF → 4NF → 5NF → 6NF → DKNF

We now move into a detailed exploration of normalization, not just as a concept, but as a structured progression. Normalization is often introduced briefly in textbooks and then quickly reduced to 3NF in practice. However, to truly understand relational modelling and the rationale behind specialized modelling techniques, it is important to examine the full ladder.

The normalization journey follows a clear sequence:

UNF → 1NF → 2NF → 3NF → BCNF → 4NF → 5NF → 6NF → DKNF

Each stage builds upon the previous one. At every step, structural weaknesses are removed and data integrity is strengthened. The objectives remain consistent throughout:

• Eliminate redundancy
• Prevent update, insert, and delete anomalies
• Increase structural integrity
• Prepare the model for specialized architectural use

This progression is not merely theoretical. It represents the structural discipline that underpins clean relational design.

Unnormalized Form (UNF) represents the raw and unstructured state of data before any normalization rules are applied. It is the natural starting point of most data collections, whether they originate from spreadsheets, CSV extracts, operational exports, or loosely designed legacy systems. In UNF, structure exists, but discipline does not. Data is captured for convenience rather than integrity.

In this form, tables may contain repeating groups, multi-valued attributes, and mixed entities within a single row. For example, in the loan approval case, a single row may contain customer information, loan details, approval status, and multiple repayment dates, repayment amounts, and repayment statuses all embedded within the same record. Columns such as RepayDates, RepayAmounts, and RepayStatuses may store several values in one cell, separated by commas or line breaks. This violates relational principles because each column should hold a single, atomic value.

First Normal Form (1NF) introduces the first formal rule of relational structure: every attribute must contain atomic, indivisible values. This means that each cell in a table must hold exactly one value, not a list, not a set, and not a repeating group. In addition, each row must represent a single instance of an entity, and every record must be uniquely identifiable.

In the Unnormalized Form, we saw repayment dates, amounts, and statuses stored as multi-valued attributes inside a single row. This violates relational principles because a column should not contain multiple values. First Normal Form eliminates this issue by removing repeating groups and restructuring the data so that each field contains only one value.

Applying 1NF to the loan approval example means separating multi-valued repayment information into individual rows. Instead of storing multiple repayment dates in a single column such as “Jan-31, Feb-28,” we create separate rows for each repayment instance. Each repayment date, repayment amount, and repayment status becomes a distinct record. As a result, the table grows vertically rather than horizontally, and repeating groups are eliminated.

Second Normal Form (2NF) builds on First Normal Form by eliminating partial dependencies. A table is in 2NF if it is already in 1NF and every non-key attribute depends on the entire primary key, not just part of it. This rule becomes relevant only when a table has a composite primary key, that is, a primary key made up of more than one column.

To understand this clearly, consider the loan approval example after applying 1NF. Suppose we have a table representing repayments with a composite primary key such as (LoanID, RepayDate). This combination uniquely identifies each repayment record. However, if we include attributes like LoanAmount or CustomerName in that same table, those attributes depend only on LoanID, not on the full composite key (LoanID, RepayDate). They are not specific to an individual repayment date. This creates a partial dependency.

A partial dependency occurs when an attribute depends on only part of a composite key rather than the whole key. In relational design, this is undesirable because it introduces redundancy and update anomalies. For example, if LoanAmount is stored alongside each repayment record, the same loan amount would repeat for every repayment row. If the loan amount needed correction, multiple rows would require updates.

Third Normal Form (3NF) removes transitive dependencies. A table is in 3NF if it is already in Second Normal Form and every non-key attribute depends only on the primary key, not on another non-key attribute. In other words, there must be no indirect dependency chains inside the table.

A transitive dependency occurs when a non-key attribute depends on another non-key attribute rather than directly on the primary key. This creates hidden redundancy and potential inconsistency. The dependency pattern can be expressed as A → B → C, where A is the primary key, B is a non-key attribute, and C depends on B instead of directly on A.

Returning to the loan approval example, suppose the Loan table contains the following attributes: LoanID (primary key), CustID, CustomerName, and CustomerAddress. While LoanID determines CustID, the attributes CustomerName and CustomerAddress actually depend on CustID, not directly on LoanID. This means there is a transitive dependency: LoanID → CustID → CustomerName. The customer details are not properties of the loan itself; they are properties of the customer.

Boyce–Codd Normal Form (BCNF) is a stricter refinement of Third Normal Form. While 3NF eliminates transitive dependencies, it still allows certain edge cases where a non-key attribute (or a non-superkey determinant) can functionally determine other attributes. BCNF addresses these edge cases by strengthening the dependency rule.

Formally, a table is in BCNF if, for every functional dependency X → Y, the determinant X is a superkey of the relation. In simpler terms, whenever one attribute (or group of attributes) determines another, that determining attribute must uniquely identify rows in the table. If it does not, the design violates BCNF.

To understand the difference from 3NF, consider a scenario in the loan approval domain. Suppose a table stores LoanID, ProductType, and InterestRate. Assume that business rules state that each ProductType has exactly one InterestRate. This creates the functional dependency:

Fourth Normal Form (4NF) addresses multi-valued dependencies. A table is in 4NF if it is already in Boyce–Codd Normal Form and does not contain two or more independent multi-valued facts about the same key. In simpler terms, a single row should not attempt to store multiple independent lists related to the same primary key.

A multi-valued dependency occurs when one key determines multiple independent sets of values that are unrelated to each other. When these independent lists are stored in the same table, they can unintentionally create false combinations, often described as a Cartesian explosion. This happens because relational tables combine values across columns, even when those values are logically independent.

Consider an extension of the loan domain. Suppose we attempt to store, in one table, the following information about each LoanID:

• A list of CollateralTypes (e.g., House, Car)
• A list of Guarantors (e.g., PersonA, PersonB)

(Projection–Join Normal Form)

Fifth Normal Form (5NF), also known as Projection–Join Normal Form (PJNF), eliminates redundancy that appears only when a table is decomposed into smaller projections and then re-joined. Unlike earlier normal forms, which focus on functional or multi-valued dependencies, 5NF addresses join dependencies, situations where a table can be reconstructed from multiple smaller tables, yet still contains hidden redundancy.

A table is in 5NF if it is already in Fourth Normal Form and every join dependency in the table is implied by the candidate keys alone. If a table can be decomposed into smaller tables in such a way that the original data can be perfectly reconstructed by joining them, without introducing spurious rows, then the decomposition should be performed.

To understand this more intuitively, consider an expanded loan example. Suppose we create a table that records relationships between Loan, Guarantor, and CollateralType together in one structure:

Sixth Normal Form (6NF) represents the most fine-grained level of relational decomposition used in practical modelling. A table is in 6NF when it is already in Fifth Normal Form and has been decomposed so that each relation captures a single fact about a key, typically with explicit time validity. In this form, every table represents one attribute, or a tightly bound set of attributes, over time.

The guiding idea of 6NF can be summarized simply: one attribute, one timeline, one table.

Earlier normalization stages focused on eliminating redundancy caused by partial dependencies, transitive dependencies, multi-valued dependencies, and join dependencies. Sixth Normal Form goes further by decomposing data to its smallest meaningful components, particularly when time becomes an important dimension.

To understand 6NF in the loan approval context, consider a Loan table that includes attributes such as Amount, InterestRate, ProductType, and Status. In earlier normal forms, these attributes might coexist in a single table keyed by LoanID. However, if each of these attributes can change independently over time, storing them together may create temporal anomalies.

Domain–Key Normal Form (DKNF) represents the theoretical “ultimate” stage of normalization. A relation is in DKNF if every constraint on the table can be expressed solely as either a domain constraint or a key constraint. If any rule governing the data cannot be enforced purely through domain definitions or key definitions, the relation is not in DKNF.

A domain constraint defines what values are valid for a column. For example, a DecisionStatus column may allow only APPROVED, REJECTED, or PENDING. An InterestRate column may allow only non-negative numeric values. These constraints govern permissible values.

A key constraint defines uniqueness. It specifies which combination of attributes uniquely identifies a row. For example, LoanID may uniquely identify a loan. A composite key such as (LoanID, EffectiveStart) may uniquely identify a time-bound fact.

The rule for achieving DKNF is conceptually simple but practically demanding. Starting from Sixth Normal Form, all remaining constraints, including business rules, dependencies, conditional requirements, and cross-attribute logic, must be expressible through domain or key rules alone. No non-trivial constraints should remain outside these two categories.