Codd's 12 Rules and Their Relationship to Normalization

Codd's rules describe what a relational database management system should support. Third normal form describes the dependencies within a relation. They concern related ideas, but answer different questions. A table can satisfy 3NF without demonstrating that its DBMS meets every relational criterion. Conversely, a capable relational DBMS can store tables whose design contains unnecessary repetition.

This distinction matters when evaluating a database or explaining a design decision. Splitting a table can remove a normalization problem, but cannot add a missing database language feature. Installing a different DBMS cannot automatically discover your business rules or correct a poorly chosen set of tables. Good results require both sound design and appropriate system capabilities.

Why There Are Thirteen Numbered Rules

The familiar name “Codd's 12 rules” covers Rules 1 through 12, accompanied by a foundational Rule 0. Codd used these criteria to examine claims that database products were fully relational. His 1985 article, Does Your DBMS Run by the Rules?, provides the historical context. The explanations below summarize the criteria and apply them to classroom examples; they are not a product certification checklist.

Read “relational” as more than “stores data in tables.” The model also concerns how users address information, express operations, define constraints, and remain insulated from implementation details. A graphical grid alone says little about those capabilities. Likewise, SQL support is useful evidence to investigate, not proof that every requirement is satisfied.

A Running Example: Employee and Department

Assume an employee belongs to exactly one department, many employees can share a department, and each department identifier determines its current name. EmployeeID is the only candidate key of the combined relation. These business rules explain the dependency chain introduced earlier:

EmployeeID → DepartmentID → DepartmentName

Separating the facts produces the following schema notation:

Employee(EmployeeID PK, EmployeeName, DepartmentID FK)
Department(DepartmentID PK, DepartmentName)

DepartmentName is stored once per department, and Employee retains a reference to it. Under the stated dependencies, the relations satisfy 3NF. We can now ask separate questions: Can the DBMS enforce the reference? Can applications query a view? Can an administrator add an index without rewriting those applications? Those questions concern system behavior.

Rule 0: The Foundation Rule

A fully relational system must manage the database through its relational capabilities.

Consider an application that can display employee rows through SQL but must use proprietary record pointers to change department assignments. The tabular display does not establish that the underlying management interface is relational. For students, the useful question is whether ordinary database work remains expressible through relational mechanisms, rather than requiring navigation through physical record structures. This criterion applies to the system as a whole, not to the number of tables in a particular schema.

Rule 1: The Information Rule

Database information is represented logically as values in tables.

Our employee's department is represented by DepartmentID, not by an undocumented position in a file. The implementation may use pages, trees, compression, or other structures internally. Those physical choices are separate from the logical representation presented to users. Normalization helps decide which relation should contain DepartmentName, but an unnormalized relation can also present its information in tabular form. Therefore, the information rule and 3NF must not be treated as equivalent tests.

Rule 2: Guaranteed Access

A scalar value is logically addressable using its table, primary-key value, and column.

For example, DepartmentName for DepartmentID 10 can be requested without knowing the disk block containing that row. A query may use a key predicate even when the optimizer chooses an index or a scan internally. This is a logical access guarantee, not a promise of constant execution time. It also does not prohibit alternate keys, views, or multiple useful queries that reach the same fact. “One logical identity” should not be confused with “only one permitted access path.”

Rule 3: Systematic Treatment of Null Values

Missing or inapplicable information requires consistent treatment distinct from ordinary values.

An unknown employee telephone number should not be confused with a real number containing zeros. Similarly, an empty string and an unknown value express different concepts, even though individual SQL products may handle them differently. Decide what missing information means in the application before choosing nullable columns. A nullable field does not automatically make a design incorrect, and replacing every missing value with a fabricated placeholder can conceal the uncertainty that users need to understand.

Normalization does not choose a missing-value policy for you. For the running example, the business rule requires a department assignment, so Employee.DepartmentID should be non-null. Another application might explicitly permit unassigned employees. That would be a different participation rule requiring different constraints and query expectations.

Rule 4: A Relationally Accessible Catalog

Authorized users should query database descriptions through the relational language.

A catalog describes tables, columns, constraints, and other database objects. Imagine writing a report listing every foreign key that references Department. Queryable metadata allows administration tools and developers to inspect those definitions systematically, instead of relying on a separate handwritten document. Access still depends on permissions. A well-normalized Employee table does not create this catalog facility; the DBMS supplies it. Designers then use the facility to confirm that their intended constraints were actually declared.

Rule 5: A Comprehensive Data Sublanguage

At least one defined language must cover data, views, constraints, authorization, and transaction boundaries.

This reaches beyond retrieving rows. A database application also needs to define structures, change information, control access, and group changes into transactions. Suppose assigning a new department requires several related updates. The application needs a way to commit the complete operation or roll it back, rather than leave a partially changed database. Normalization may influence the statements required, but it does not supply transaction control. Evaluate each capability directly rather than assuming that a successful SELECT demonstrates the complete language requirement.

Rule 6: View Updating

The system should support updates through every theoretically updatable view.

This does not mean every imaginable query result has an unambiguous update meaning. If a view shows the number of employees per department, changing a displayed count from five to six does not identify which employee should be created. A simple projection of one table raises different questions from an aggregate report or a join.

Product support must be checked separately. For example, PostgreSQL's CREATE VIEW documentation specifies conditions for automatically updatable views. Normalizing base tables does not automatically make a joined view writable. Before promising an update interface, identify the affected base rows, verify the supported operations, and test the behavior of omitted columns and required values.

Rule 7: High-Level Insert, Update, and Delete

Relational modification must support sets of rows, not only individual records.

A department reassignment might affect all employees meeting a condition. Expressing that condition in one update lets the DBMS process the qualifying set. This does not mean every task must use a single statement or that procedural code is forbidden. It means a relational system must provide more than a record-at-a-time interface. The designer still needs to define the intended set precisely, consider constraints, and choose an appropriate transaction boundary for the operation.

Rule 8: Physical Data Independence

Changes in storage or access methods should not require application changes.

For example, an administrator may add an index on Employee.DepartmentID to help a frequent join. Applications should continue referring to the same logical tables and columns. The execution plan and response time may change; physical independence does not mean performance remains identical. It means the application is insulated from the physical organization. Whether the underlying tables satisfy 3NF is a separate issue: an index can improve access to either a normalized or a poorly structured relation.

Rule 9: Logical Data Independence

Information-preserving logical changes should be insulated from applications where the relational interface permits.

Suppose an older program expects EmployeeID, EmployeeName, and DepartmentName in one result. After decomposition, a compatibility view could present those columns by joining Employee and Department. This can preserve a read interface while the base schema changes. It is a design technique to evaluate, not a guarantee that every existing query and update remains valid.

Renaming exposed columns, changing meanings, or removing information can still break applications. Write behavior may also require separate work. Dependency analysis and interface testing remain necessary even when the new design reduces redundancy and better represents the business rules.

Rule 10: Integrity Independence

Database integrity rules should be definable relationally and recorded in the catalog.

The employee application may check that a department exists before accepting a form, but other clients can write to the same database. A declared foreign key provides a shared enforcement point. A primary key prevents duplicate identifiers, and a non-null constraint implements required participation. These declarations complement normalization; they are not created automatically by drawing an ER diagram.

Not every business policy fits a simple constraint. Some require more involved database logic or application workflows. The practical lesson is to distinguish which rules the database enforces from which ones clients merely promise to follow, and to document any remaining enforcement responsibilities explicitly.

Rule 11: Distribution Independence

Applications should be insulated from changes in the distribution of database information.

Moving some department information to another location should not force users to navigate physical sites merely to express the same logical request. This criterion concerns location transparency, not a promise that distributed operations have no latency or failure modes. Transactions, availability, and operational costs still need evaluation. Dividing a relation during normalization is also not the same operation as distributing storage across servers. One changes the logical design; the other concerns where the system manages its information.

Rule 12: Nonsubversion

Any lower-level interface must not bypass relational integrity constraints.

Consider a bulk-loading path that inserts Employee rows without checking DepartmentID. If it silently accepts references that ordinary relational updates reject, applications no longer share a dependable integrity boundary. When evaluating import tools or maintenance procedures, identify whether constraints are enforced, temporarily suspended, or validated afterward. An administrative mechanism that changes enforcement requires deliberate control; it should not be mistaken for evidence that normalization protects data by itself. The rule evaluates consistency across access mechanisms.

Where Normalization Fits

Third normal form evaluates functional dependencies, considering all candidate keys. For every nontrivial dependency X → A, X must be a superkey or A must be prime. That test determines whether the relation has a particular structural property. It does not test catalog access, transaction syntax, distributed execution, or view-update support.

In our example, decomposition gives DepartmentName an appropriate home. Integrity constraints maintain the references, a view can expose a convenient report, and an index may improve access. Each decision has a separate purpose. Keeping those purposes distinct makes troubleshooting easier: a duplicate department name in employee rows is a design concern, while an unsupported view update is a DBMS capability or interface concern.

A Practical Review You Can Perform

Begin with a small, representative business operation rather than a vendor's general description. Choose creating an employee, assigning a department, renaming that department, and retrieving a report. Write down the expected results and prohibited states. This gives the design discussion an observable target and prevents broad labels such as “fully normalized” from substituting for verification.

1. Check the design: List candidate keys and dependencies. Verify the decomposition preserves the intended information and stated dependencies.
2. Inspect declarations: Confirm keys, required values, and references in the database catalog. A diagram and a deployed schema can disagree.
3. Test invalid operations: Try a duplicate identifier and a missing-parent reference in an isolated test database. Record what rejects each operation.
4. Test interfaces: Check reports, views, imports, and application writes. Identify which constraints and permissions apply to each path.
5. Measure changes: Evaluate execution plans and workload behavior separately from normalization. Document compatibility work when the logical schema changes.

This procedure does not certify complete adherence to Codd's criteria. It produces useful evidence about your application's actual requirements. A database can be suitable for a project even when some theoretical capabilities are restricted, provided those limitations are understood and accommodated. Avoid unsupported product rankings or blanket claims that any SQL implementation satisfies every rule.

Example: Preserving a Report Interface

Imagine a payroll report that reads EmployeeID, EmployeeName, and DepartmentName. After separating department facts, give the report a view exposing those same columns. Check that the join returns exactly one row per employee under the mandatory department rule. Compare the old and new results, including employees whose names are identical and departments with many employees.

Next, ask whether the report only reads data. If it also changes department names, preserving its column list does not establish compatible write behavior. Decide which base table should receive the change and whether the DBMS supports that operation through the view. This small example connects normalization, integrity, logical independence, and view semantics while keeping their responsibilities distinct. A successful read test answers only the reading question.

The Normalization Mnemonic: Useful but Incomplete

The familiar phrase about depending on the key, the whole key, and nothing but the key is a teaching aid. It is not a substitute for the formal definitions, and this lesson does not attribute its authorship to Codd. In particular, the phrase can hide the need to consider alternate candidate keys and the prime-attribute exception in 3NF.

Use it to remember the questions: Are facts identified properly? Do any attributes depend on only part of a candidate key? Are descriptive facts being repeated because another determinant belongs in its own relation? Then apply the actual definitions. First normal form is not established simply by adding an ID column, and 3NF does not mean every value appears only once anywhere in the database.

Check Your Understanding

A team splits Employee and Department into 3NF relations but leaves foreign keys undeclared. Its import program inserts an employee assigned to a missing department. Has normalization failed?

Summary and Next Step

Codd's criteria describe relational system capabilities; normalization organizes facts according to dependencies. Use both perspectives without treating either as proof of the other. The next lesson examines normalization beyond third normal form, returning to the structural questions that remain after 3NF.

DeNormalization Theory - Exercise