The 12 Rules That Shaped Relational Databases

Whenever we use an application, whether it's a banking app, an online shopping platform, or a college management system, a lot of information is stored and managed behind the scenes.

But have you ever wondered how databases organize this information? And what actually makes a database relational?

To understand this, let's go back to 1970, when computer scientist Edgar F. Codd introduced the relational model of databases.

His idea transformed how we organize, store, and manage information.

Later, Codd proposed a set of rules to describe what a true Relational Database Management System (RDBMS) should support.

These principles are commonly known as Codd's 12 Rules.

Let's understand them through a simple, real-world example.

Imagine a college management system that stores:

• Student details
• Course information
• Attendance records
• Examination marks

How should such a database organize and manage all this information?

Let's explore.

First, What Is a Relational Database?

A relational database organizes information into tables.

Each table contains rows and columns.

For example, imagine a Students table:

Here:

• Each row represents one student.
• Each column represents a particular type of information.
• Student_ID helps uniquely identify each student.

We can also create separate tables for courses, attendance, and examination marks.

These tables can be connected through relationships between their data.

This is the basic idea behind a relational database.

But Edgar F. Codd wanted to go beyond simply storing information in tables. He wanted to establish principles that relational database systems should follow.

Understanding Codd's 12 Rules

Before we begin, here's an interesting detail:

Codd's rules are numbered from Rule 0 to Rule 12.

So, although they are commonly called the 12 Rules, there are actually 13 numbered rules when Rule 0 is included.

Let's understand each one.

Rule 0 – The Foundation Rule

A database system must manage its data entirely through its relational capabilities.

In simple words: If a system claims to be a relational database, it should use the relational model to manage and access its data.

Real-world example:

Imagine our college management system uses relational tables to store student information.

The system should manage that information through its relational database capabilities rather than depending on an entirely different mechanism for its core database operations.

Rule 1 – The Information Rule

All information in a relational database should be represented logically and consistently as values in tables.

In simple words: Information should be represented in one consistent relational form.

Real-world example:

Student names, registration numbers, departments, and marks are stored as values in their respective table columns.

The database should represent its information in this consistent tabular form.

Rule 2 – Guaranteed Access Rule

Every individual data value should be accessible using a combination of the table name, primary key value, and column name.

In simple words: We should have a reliable way to find any particular piece of information.

Real-world example:

Suppose we want to find the department of the student whose Student_ID is 101.

We can identify the information using:

• Table: Students
• Primary key: 101
• Column: Department

This gives us a clear and reliable way to access the required value.

Rule 3 – Systematic Treatment of NULL Values

A relational database should handle missing or unknown information consistently.

In databases, NULL represents the absence of a known or applicable value.

It is not the same as zero or an empty string.

Real-world example:

Imagine a student has not yet received their examination result.

Instead of entering zero marks, the database can store NULL because the result is not yet available.

This helps distinguish missing information from an actual result of zero.

Rule 4 – Dynamic Online Catalog Based on the Relational Model

A relational database should maintain information about its own structure, such as tables, columns, and constraints.

This information is called metadata.

It should also be represented in relational form and accessible using the same relational language used to manage ordinary data.

Real-world example:

Suppose a developer wants to know which tables exist in the college database.

The database should provide a way to retrieve information about its tables and columns.

In many SQL databases, system catalogs or information schema views provide this information.

Rule 5 – Comprehensive Data Sublanguage Rule

A relational database should support a comprehensive language that can perform different database operations.

This includes:

• Creating tables
• Retrieving information
• Inserting records
• Updating records
• Deleting records
• Defining constraints
• Managing permissions
• Managing transactions

It should also support transaction operations such as BEGIN, COMMIT, and ROLLBACK.

Real-world example:

Using SQL, a developer can create a Students table, insert a new student, retrieve student details, update a department, and delete a record when permitted.

SQL is a common example of a language used to perform these operations.

Rule 6 – View Updating Rule

A database should support updating views that are theoretically capable of being updated.

First, what is a view?

A view is a virtual table created using a query on one or more database tables.

Real-world example:

Imagine the college management system has a view that displays only student names and departments.

Instead of showing every column from the Students table, the view displays only the information required by a particular user.

Codd's rule says that views which are theoretically updatable should support updates through the view.

However, not every view can be updated. For example, views involving certain joins or calculations may not be directly updatable.

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

A relational database should support inserting, updating, and deleting multiple records using a single operation.

In simple words: Database operations should work with sets of records, not only one record at a time.

Real-world example:

Imagine the college wants to update the department details of 50 students.

Instead of updating each student individually, a database should support updating multiple matching records through a single statement.

This makes database operations more efficient and easier to manage.

Rule 8 – Physical Data Independence

Changes in how data is physically stored should not require changes to the applications using the database.

In simple words: The application should not need to know exactly how the database stores its data internally.

Real-world example:

Imagine the college database moves its data to a different storage device or changes its internal storage organization.

The student management application should continue working without requiring changes simply because the physical storage method changed.

Rule 9 – Logical Data Independence

Changes to the logical structure of a database should not unnecessarily affect existing applications.

In simple words: Developers should be able to make certain changes to the database structure without breaking applications that depend on it.

Real-world example:

Suppose the college adds a new column called Email_Address to the Students table.

An existing application that only uses Student_ID and Student_Name should continue working without needing changes, provided its existing interface and dependencies remain compatible.

Rule 10 – Integrity Independence

Database rules and constraints should be defined and stored within the database itself rather than existing only inside individual applications.

In simple words: The database should help protect the correctness of its data.

Real-world example:

Imagine every student must have a unique registration number.

Instead of relying only on the application to check duplicate registration numbers, the database can enforce a UNIQUE constraint.

The constraint is defined in the database, so other applications accessing the same database must also follow it.

This helps maintain data integrity.

Rule 11 – Distribution Independence

Users and applications should not need to know where the database physically stores its data.

In simple words: The location of data should be hidden from the user as much as possible.

Real-world example:

Imagine a college has multiple campuses.

Student information might be stored across different database servers or locations.

The application should ideally allow authorized users to access the required information without needing to understand where every record is physically stored.

Rule 12 – Non-Subversion Rule

If a relational database provides a lower-level method to access its data, that method should not allow users to bypass the relational rules and integrity constraints.

In simple words: Nobody should be able to use an alternative access method to break the rules that protect the database.

Real-world example:

Imagine our college database enforces a rule that every attendance record must refer to an existing student.

A lower-level access method should not be able to bypass this rule and insert attendance records for students who do not exist.

The database must protect its integrity regardless of the access method.

Do Modern Databases Follow All 12 Rules?

An important thing to understand is that Codd's rules describe an ideal standard for a fully relational database management system.

They are not a checklist that every database product automatically satisfies.

Even widely used database systems do not necessarily meet every rule completely.

Some rules, particularly view updating, logical data independence, and distribution independence, are challenging to implement fully.

However, these rules are still valuable because they help us understand the principles behind relational database systems and evaluate how closely a database follows the relational model.

How Do These Rules Help Us Understand Relational Databases?

Let's look at the bigger picture.

Codd's rules describe important principles that help relational database systems organize, access, protect, and manage information.

Here are some of the major areas they cover:

Each rule addresses a different aspect of relational database management.

Together, they provide a theoretical benchmark for understanding relational database systems.

What I Learned

Before exploring these rules, I understood relational databases mainly as collections of tables connected through relationships.

But Codd's rules helped me understand that a relational database is about much more than simply storing information in rows and columns.

It also involves:

• How information is accessed.
• How missing values are handled.
• How data integrity is maintained.
• How applications interact with databases.
• How databases handle changes in their structure and storage.

One interesting takeaway is that database design is not just about deciding where to store information. It is also about making sure that information remains accessible, reliable, and consistent.

That's why I find learning database concepts through real-world examples useful. They help turn technical principles into situations we can understand.

If you're learning SQL or database development, try exploring the principles behind the databases you use every day.

Understanding why these principles exist can make you a better developer.

Technology becomes easier to understand when we look beyond what it does and explore why it works that way.

Let's keep making technology simple!

References

1. Edgar F. Codd – Is Your DBMS Really Relational? (1985).
2. Edgar F. Codd – Does Your DBMS Run By the Rules? (1985).