Codd's 12 Rules — The Relational Model | OneNoughtOne

0/131

Database Management SystemsData Models

The Relational Model

LevelBeginner

Duration75 mins

TopicData Models

3 / 5

Codd's 12 Rules

The Relational Manifesto

By the mid-1980s, the term "relational database" had become a marketing buzzword. Vendors slapped the label on products that were only superficially relational—databases that might use tables for display but lacked the mathematical foundations that gave the relational model its power. E.F. Codd, the inventor of the relational model, watched this corruption of his work with growing concern. In 1985, he published a remarkable article in Computerworld that would become one of the most influential pieces in database history: "Is Your DBMS Really Relational?" In this article and its sequel "Does Your DBMS Run By the Rules?", Codd articulated twelve rules (actually thirteen, numbered 0-12) that a database management system must satisfy to be called fully relational. These rules weren't arbitrary requirements—they were precise translations of the relational model's mathematical foundations into practical implementation criteria. More than requirements, Codd's rules became a benchmark for evaluating database systems and a roadmap for database development. Let's examine each rule in detail.

What You Will Learn

By the end of this page, you will understand each of Codd's 12 rules (plus Rule 0), the theoretical rationale behind each rule, how real database systems comply (or fail to comply) with these rules, and why these rules remain relevant benchmarks today.

Rule 0: The Foundation Rule

Rule 0 Statement

For any system that is advertised as, or claimed to be, a relational database management system, that system must be able to manage databases entirely through its relational capabilities.

Interpretation Rule 0 is the meta-rule that establishes the context for all other rules. It states that a system claiming to be relational must use relational features—not non-relational workarounds—to manage data. This might seem obvious, but consider systems that: Require pointer-based navigation for certain operations Need non-SQL commands to perform some administrative tasks Store metadata in non-relational structures Require external tools for backup/recovery If any essential database operation requires stepping outside the relational paradigm, the system isn't fully relational. Why This Matters Rule 0 ensures that the benefits of the relational model—data independence, declarative querying, optimization—apply to ALL database operations, not just simple ones. A system that's relational "except for" certain cases forces users back into the complexity the relational model was designed to eliminate.

Rule 0 Compliance Example

Evaluating a system against Rule 0

Input

System A:
  - Data queries: SQL ✓
  - Data modification: SQL ✓
  - Schema changes: SQL ✓
  - Backup/recovery: SQL commands ✓
  - Permission management: SQL (GRANT/REVOKE) ✓
  - Metadata access: SQL (information_schema) ✓

Output

System A Verdict: COMPLIANT with Rule 0
All database management uses relational capabilities.

System B:
  - Data queries: SQL ✓
  - Bulk loading: Proprietary binary format ✗
  - Statistics: Must run external utility ✗
  
System B Verdict: PARTIAL compliance only

Rule 1: The Information Rule

Rule 1 Statement

All information in a relational database is represented explicitly at the logical level and in exactly one way—by values in tables.

Interpretation This is perhaps the most fundamental rule. It establishes that: Everything is a table : Data, metadata, relationships, constraints—all represented as values in tables No hidden information : No data stored in pointers, object addresses, or invisible system structures Uniform representation : The same access method works for all information The Single Representation Principle Pre-relational databases used different representations for different data: User data in records Relationships in pointer chains Metadata in special system areas Indexes as separate navigational structures The relational model unifies everything: tables all the way down. Implications Self-describing databases : Metadata (table names, columns, types, constraints) is stored in tables (the system catalog) that can be queried just like user data Relationships are data : Foreign key relationships are stored as values, not pointers No navigational complexity : Access patterns don't depend on physical implementation

information-rule-example.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
-- The information rule in action: metadata as tables
-- Query the system catalog just like any other table
 
-- List all tables in PostgreSQL
SELECT table_name, table_type
FROM information_schema.tables
WHERE table_schema = 'public';
 
-- List all columns with their types
SELECT table_name, column_name, data_type, is_nullable
FROM information_schema.columns
WHERE table_schema = 'public';
 
-- List all foreign key relationships
SELECT
    tc.table_name AS referencing_table,
    kcu.column_name AS referencing_column,
    ccu.table_name AS referenced_table,
    ccu.column_name AS referenced_column
FROM information_schema.table_constraints tc
JOIN information_schema.key_column_usage kcu
    ON tc.constraint_name = kcu.constraint_name
JOIN information_schema.constraint_column_usage ccu
    ON tc.constraint_name = ccu.constraint_name
WHERE tc.constraint_type = 'FOREIGN KEY';
 
-- All the above use standard SQL against standard tables!

The Catalog is a Database

The system catalog (information_schema, pg_catalog, etc.) IS a relational database about your database. This self-describing property enables tools like schema browsers, ORM introspection, and automatic documentation generators—all using the same SQL skills you use for data.

Rule 2: Guaranteed Access Rule

Rule 2 Statement

Each and every datum (atomic value) in a relational database is guaranteed to be logically accessible by resorting to a combination of table name, primary key value, and column name.

Interpretation This rule guarantees addressability. Every piece of data can be located precisely through three coordinates: Table name : Which relation contains the data Primary key : Which tuple (row) within that relation Column name : Which attribute within that tuple No Navigation Required In hierarchical or network databases, accessing data might require: Starting from a root record Following a series of pointers Knowing the physical path to the data Rule 2 eliminates this. You specify WHAT you want, not HOW to get there. Primary Keys are Mandatory Rule 2 implicitly requires that every table has a primary key. Without a reliable way to identify individual rows, you cannot guarantee access to specific values. This is why database design always emphasizes key selection—it's not just good practice, it's fundamental to relational access.

Guaranteed Access in Practice

Demonstrating precise data addressability

Input

To access Alice Chen's salary from the Employee table:

Coordinates:
  - Table: Employee
  - Primary Key: emp_id = 1001
  - Column: salary

SQL Query:
  SELECT salary FROM Employee WHERE emp_id = 1001;

Output

Result: 95000.00

This access is:
  ✓ Guaranteed to work (if the key exists)
  ✓ Independent of physical storage
  ✓ Same syntax regardless of table size
  ✓ No navigation path needed

Compare to hierarchical navigation:
  FIND Department WHERE name = 'Engineering'
  THEN FIND Employee UNDER Department WHERE emp_id = 1001
  THEN GET salary
  
The relational approach is simpler AND more powerful.

Tables Without Primary Keys

SQL allows tables without primary keys, which technically violates Rule 2. While the DBMS permits this, it creates ambiguity—if duplicate rows exist, you cannot uniquely identify values. Best practice (and Rule 2 compliance) requires defining a primary key for every table.

Rule 3: Systematic Treatment of NULL Values

Rule 3 Statement

NULL values (distinct from the empty character string or a string of blank characters and distinct from zero or any other number) are supported in fully relational DBMS for representing missing information and inapplicable information in a systematic way, independent of data type.

Interpretation Rule 3 addresses a critical real-world problem: sometimes data is unknown or doesn't apply. Codd recognized that a principled solution was essential. NULL is NOT: Empty string '' (which is a known value—nothing) Zero (which is a known number) False (which is a known boolean) An error state NULL IS: A marker indicating absence of a value Semantically: "unknown" or "inapplicable" Systematic Treatment NULL must be handled consistently across all operations: Comparisons: NULL = NULL is UNKNOWN, not TRUE Arithmetic: NULL + 5 = NULL Aggregates: COUNT includes NULLs, SUM ignores them Sorting: NULLs sort first or last (configurable) This consistent treatment is what "systematic" means—NULL behavior is defined, not arbitrary.

Three-Valued Logic with NULL
Expression	Result	Explanation
NULL = NULL	UNKNOWN	We don't know if two unknowns are equal
NULL <> NULL	UNKNOWN	We don't know if two unknowns differ
5 > NULL	UNKNOWN	We can't compare known to unknown
NULL AND TRUE	UNKNOWN	Unknown AND anything that isn't FALSE = Unknown
NULL AND FALSE	FALSE	Regardless of unknown, FALSE wins
NULL OR TRUE	TRUE	Regardless of unknown, TRUE wins
NULL OR FALSE	UNKNOWN	Result depends on the unknown value
NOT NULL	UNKNOWN	Negation of unknown is unknown

null-handling.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
-- Proper NULL handling in SQL
 
-- Testing for NULL (= doesn't work!)
SELECT * FROM Employee WHERE manager_id IS NULL;     -- Correct
SELECT * FROM Employee WHERE manager_id = NULL;      -- WRONG! Returns nothing
 
-- Coalesce: provide default for NULL
SELECT name, COALESCE(manager_id, 0) AS mgr 
FROM Employee;
 
-- NULLIF: return NULL if values match
SELECT NULLIF(bonus, 0) AS actual_bonus  -- Treat 0 bonus as "no bonus"
FROM Employee;
 
-- NULL in aggregates
SELECT 
    COUNT(*) AS total_rows,        -- Counts all rows including NULL
    COUNT(bonus) AS with_bonus,    -- Counts non-NULL bonus values
    SUM(bonus) AS total_bonus,     -- Sums non-NULL values
    AVG(bonus) AS avg_bonus        -- Average of non-NULL values only
FROM Employee;
 
-- CASE with NULL
SELECT name,
    CASE 
        WHEN bonus IS NULL THEN 'No bonus assigned'
        WHEN bonus = 0 THEN 'Zero bonus'
        ELSE 'Has bonus: ' || bonus::text
    END AS bonus_status
FROM Employee;

NULL Complications

NULL introduces three-valued logic, which complicates reasoning. Many subtle bugs arise from unexpected NULL behavior: WHERE x <> 5 doesn't return rows where x IS NULL. Join conditions with NULL never match. Aggregate functions behave differently. Understanding NULL semantics is crucial for correct SQL.

Rule 4: Dynamic Online Catalog Based on Relational Model

Rule 4 Statement

The database description is represented at the logical level in the same way as ordinary data, so that authorized users can apply the same relational language to its interrogation as they apply to the regular data.

Interpretation Rule 4 extends Rule 1 specifically for metadata. The database catalog (schema information) must be: Stored in tables : Using the same relational structures as user data Queryable with SQL : Same language, same skills Online and dynamic : Always reflects current state, updated automatically Subject to security : Same permission system as data The System Catalog Modern databases implement this through system catalogs: PostgreSQL: pg_catalog schema, information_schema view MySQL: information_schema, mysql database SQL Server: sys schema, INFORMATION_SCHEMA views Oracle: DBA_*, ALL_*, USER_* views, INFORMATION_SCHEMA The SQL standard defines INFORMATION_SCHEMA with standard table structures for portability.

catalog-queries.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
-- Standard INFORMATION_SCHEMA queries (SQL standard, cross-database)
 
-- List all user tables
SELECT table_name, table_type
FROM information_schema.tables
WHERE table_schema NOT IN ('pg_catalog', 'information_schema');
 
-- Find all columns with their constraints
SELECT 
    t.table_name,
    c.column_name,
    c.data_type,
    c.character_maximum_length,
    c.is_nullable,
    c.column_default
FROM information_schema.tables t
JOIN information_schema.columns c 
    ON t.table_name = c.table_name
WHERE t.table_schema = 'public'
ORDER BY t.table_name, c.ordinal_position;
 
-- Find all primary keys
SELECT
    tc.table_name,
    string_agg(kcu.column_name, ', ') AS pk_columns
FROM information_schema.table_constraints tc
JOIN information_schema.key_column_usage kcu
    ON tc.constraint_name = kcu.constraint_name
WHERE tc.constraint_type = 'PRIMARY KEY'
GROUP BY tc.table_name;
 
-- Check which tables reference a given table
SELECT
    tc.table_name AS referencing_table,
    ccu.table_name AS referenced_table
FROM information_schema.table_constraints tc
JOIN information_schema.constraint_column_usage ccu
    ON tc.constraint_name = ccu.constraint_name
WHERE tc.constraint_type = 'FOREIGN KEY'
    AND ccu.table_name = 'employee';  -- find refs to 'employee'

Practical Applications

Rule 4 compliance enables: schema comparison tools, automatic documentation generators, ORM schema introspection, migration script generators, and dependency analysis. All these tools work because metadata IS data, queryable with standard SQL.

Rule 5: Comprehensive Data Sublanguage Rule

Rule 5 Statement

A relational system may support several languages and various modes of terminal use. However, there must be at least one language whose statements are expressible, per some well-defined syntax, as character strings and that is comprehensive in supporting all of the following items: data definition, view definition, data manipulation, integrity constraints, authorization, and transaction boundaries.

Interpretation Rule 5 requires a unified language capable of all database operations. While multiple interfaces may exist (graphical tools, APIs, embedded SQL), at least one complete language must exist. SQL Fulfills This Rule SQL is the comprehensive data sublanguage that modern databases use: Data Definition (DDL) : CREATE TABLE, ALTER TABLE, DROP TABLE CREATE INDEX, CREATE SEQUENCE CREATE SCHEMA, CREATE DATABASE View Definition : CREATE VIEW, CREATE MATERIALIZED VIEW ALTER VIEW, DROP VIEW Data Manipulation (DML) : SELECT, INSERT, UPDATE, DELETE MERGE, TRUNCATE Integrity Constraints : PRIMARY KEY, FOREIGN KEY, UNIQUE CHECK, NOT NULL, DEFAULT Authorization (DCL) : GRANT, REVOKE CREATE ROLE, ALTER ROLE Transaction Control (TCL) : BEGIN, COMMIT, ROLLBACK SAVEPOINT, SET TRANSACTION

SQL Statement Categories
Category	Purpose	Key Statements
DDL	Define structure	CREATE, ALTER, DROP, TRUNCATE
DML	Manipulate data	SELECT, INSERT, UPDATE, DELETE, MERGE
DCL	Control access	GRANT, REVOKE
TCL	Control transactions	BEGIN, COMMIT, ROLLBACK, SAVEPOINT
Views	Define virtual tables	CREATE VIEW, CREATE MATERIALIZED VIEW
Constraints	Enforce integrity	PRIMARY KEY, FOREIGN KEY, CHECK

Character String Representation Notably, Codd specified that statements must be expressible as character strings. This ensures: Standardization : A textual syntax can be formally defined Portability : Statements can be written, stored, and transmitted Tool support : IDEs, version control, and documentation can work with queries Reproducibility : Exact statements can be logged and replayed This is why SQL is text-based rather than a graphical or binary protocol.

Beyond SQL

While SQL is the standard comprehensive sublanguage, rule 5 allows additional languages too. Many databases support JSON query languages (PostgreSQL's jsonpath), graph queries (Cypher via extensions), or specialized analytical languages (Window functions). These supplement but don't replace SQL's comprehensive role.

Rule 6: View Updating Rule

Rule 6 Statement

All views that are theoretically updatable are also updatable by the system.

Interpretation Views provide logical data independence by presenting data differently than base tables. But if views are read-only, they're limited. Rule 6 requires that any view which CAN be updated (mathematically) SHOULD be updatable. When is a View Updatable? A view is potentially updatable when updates can be unambiguously translated to base table updates: UPDATABLE views typically: Select from a single base table Include the primary key Don't use DISTINCT, GROUP BY, HAVING Don't use aggregate functions Don't use set operations (UNION, INTERSECT) NON-UPDATABLE views typically: Join multiple tables (which row gets updated?) Use aggregates (what does updating SUM mean?) Use DISTINCT (which duplicate do we update?) Compute derived columns (can't update a calculation)

updatable-view.sql
-- UPDATABLE VIEW
-- Simple projection of base table
CREATE VIEW active_employees AS
SELECT emp_id, name, department, salary
FROM employee
WHERE is_active = true;
 
-- This is updatable:
UPDATE active_employees
SET salary = 50000
WHERE emp_id = 1001;
-- Translates to:
-- UPDATE employee 
-- SET salary = 50000 
-- WHERE emp_id = 1001;
 
-- This INSERT works too:
INSERT INTO active_employees 
    (emp_id, name, department, salary)
VALUES (1005, 'Eve', 'Sales', 60000);
-- (is_active defaults or uses CHECK OPTION)

non-updatable-view.sql
-- NON-UPDATABLE VIEW
-- Aggregation makes updates ambiguous
CREATE VIEW dept_stats AS
SELECT 
    department,
    COUNT(*) AS emp_count,
    AVG(salary) AS avg_salary
FROM employee
GROUP BY department;
 
-- This CANNOT be updated:
UPDATE dept_stats
SET avg_salary = 80000
WHERE department = 'Engineering';
-- ERROR: How do we change individual 
-- salaries to achieve avg of 80000?
 
-- Similarly ambiguous:
INSERT INTO dept_stats
VALUES ('Marketing', 5, 70000);
-- ERROR: What are the 5 employees?

Rule 6 Compliance is Partial

No database fully complies with Rule 6. The mathematical definition of 'theoretically updatable' is complex, and many edge cases exist. Modern databases support updatable views for simple cases and allow INSTEAD OF triggers for complex cases. This remains an area where theory exceeds practice.

Database Management SystemsData Models

The Relational Model

LevelBeginner

Duration75 mins

TopicData Models

3 / 5

Codd's 12 Rules

The Relational Manifesto

By the mid-1980s, the term "relational database" had become a marketing buzzword. Vendors slapped the label on products that were only superficially relational—databases that might use tables for display but lacked the mathematical foundations that gave the relational model its power. E.F. Codd, the inventor of the relational model, watched this corruption of his work with growing concern. In 1985, he published a remarkable article in Computerworld that would become one of the most influential pieces in database history: "Is Your DBMS Really Relational?" In this article and its sequel "Does Your DBMS Run By the Rules?", Codd articulated twelve rules (actually thirteen, numbered 0-12) that a database management system must satisfy to be called fully relational. These rules weren't arbitrary requirements—they were precise translations of the relational model's mathematical foundations into practical implementation criteria. More than requirements, Codd's rules became a benchmark for evaluating database systems and a roadmap for database development. Let's examine each rule in detail.

What You Will Learn

By the end of this page, you will understand each of Codd's 12 rules (plus Rule 0), the theoretical rationale behind each rule, how real database systems comply (or fail to comply) with these rules, and why these rules remain relevant benchmarks today.

Rule 0: The Foundation Rule

Rule 0 Statement

For any system that is advertised as, or claimed to be, a relational database management system, that system must be able to manage databases entirely through its relational capabilities.

Interpretation Rule 0 is the meta-rule that establishes the context for all other rules. It states that a system claiming to be relational must use relational features—not non-relational workarounds—to manage data. This might seem obvious, but consider systems that: Require pointer-based navigation for certain operations Need non-SQL commands to perform some administrative tasks Store metadata in non-relational structures Require external tools for backup/recovery If any essential database operation requires stepping outside the relational paradigm, the system isn't fully relational. Why This Matters Rule 0 ensures that the benefits of the relational model—data independence, declarative querying, optimization—apply to ALL database operations, not just simple ones. A system that's relational "except for" certain cases forces users back into the complexity the relational model was designed to eliminate.

Rule 0 Compliance Example

Evaluating a system against Rule 0

Input

System A:
  - Data queries: SQL ✓
  - Data modification: SQL ✓
  - Schema changes: SQL ✓
  - Backup/recovery: SQL commands ✓
  - Permission management: SQL (GRANT/REVOKE) ✓
  - Metadata access: SQL (information_schema) ✓

Output

System A Verdict: COMPLIANT with Rule 0
All database management uses relational capabilities.

System B:
  - Data queries: SQL ✓
  - Bulk loading: Proprietary binary format ✗
  - Statistics: Must run external utility ✗
  
System B Verdict: PARTIAL compliance only

Rule 1: The Information Rule

Rule 1 Statement

All information in a relational database is represented explicitly at the logical level and in exactly one way—by values in tables.

Interpretation This is perhaps the most fundamental rule. It establishes that: Everything is a table : Data, metadata, relationships, constraints—all represented as values in tables No hidden information : No data stored in pointers, object addresses, or invisible system structures Uniform representation : The same access method works for all information The Single Representation Principle Pre-relational databases used different representations for different data: User data in records Relationships in pointer chains Metadata in special system areas Indexes as separate navigational structures The relational model unifies everything: tables all the way down. Implications Self-describing databases : Metadata (table names, columns, types, constraints) is stored in tables (the system catalog) that can be queried just like user data Relationships are data : Foreign key relationships are stored as values, not pointers No navigational complexity : Access patterns don't depend on physical implementation

information-rule-example.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
-- The information rule in action: metadata as tables
-- Query the system catalog just like any other table
 
-- List all tables in PostgreSQL
SELECT table_name, table_type
FROM information_schema.tables
WHERE table_schema = 'public';
 
-- List all columns with their types
SELECT table_name, column_name, data_type, is_nullable
FROM information_schema.columns
WHERE table_schema = 'public';
 
-- List all foreign key relationships
SELECT
    tc.table_name AS referencing_table,
    kcu.column_name AS referencing_column,
    ccu.table_name AS referenced_table,
    ccu.column_name AS referenced_column
FROM information_schema.table_constraints tc
JOIN information_schema.key_column_usage kcu
    ON tc.constraint_name = kcu.constraint_name
JOIN information_schema.constraint_column_usage ccu
    ON tc.constraint_name = ccu.constraint_name
WHERE tc.constraint_type = 'FOREIGN KEY';
 
-- All the above use standard SQL against standard tables!

The Catalog is a Database

The system catalog (information_schema, pg_catalog, etc.) IS a relational database about your database. This self-describing property enables tools like schema browsers, ORM introspection, and automatic documentation generators—all using the same SQL skills you use for data.

Rule 2: Guaranteed Access Rule

Rule 2 Statement

Each and every datum (atomic value) in a relational database is guaranteed to be logically accessible by resorting to a combination of table name, primary key value, and column name.

Interpretation This rule guarantees addressability. Every piece of data can be located precisely through three coordinates: Table name : Which relation contains the data Primary key : Which tuple (row) within that relation Column name : Which attribute within that tuple No Navigation Required In hierarchical or network databases, accessing data might require: Starting from a root record Following a series of pointers Knowing the physical path to the data Rule 2 eliminates this. You specify WHAT you want, not HOW to get there. Primary Keys are Mandatory Rule 2 implicitly requires that every table has a primary key. Without a reliable way to identify individual rows, you cannot guarantee access to specific values. This is why database design always emphasizes key selection—it's not just good practice, it's fundamental to relational access.

Guaranteed Access in Practice

Demonstrating precise data addressability

Input

To access Alice Chen's salary from the Employee table:

Coordinates:
  - Table: Employee
  - Primary Key: emp_id = 1001
  - Column: salary

SQL Query:
  SELECT salary FROM Employee WHERE emp_id = 1001;

Output

Result: 95000.00

This access is:
  ✓ Guaranteed to work (if the key exists)
  ✓ Independent of physical storage
  ✓ Same syntax regardless of table size
  ✓ No navigation path needed

Compare to hierarchical navigation:
  FIND Department WHERE name = 'Engineering'
  THEN FIND Employee UNDER Department WHERE emp_id = 1001
  THEN GET salary
  
The relational approach is simpler AND more powerful.

Tables Without Primary Keys

SQL allows tables without primary keys, which technically violates Rule 2. While the DBMS permits this, it creates ambiguity—if duplicate rows exist, you cannot uniquely identify values. Best practice (and Rule 2 compliance) requires defining a primary key for every table.

Rule 3: Systematic Treatment of NULL Values

Rule 3 Statement

NULL values (distinct from the empty character string or a string of blank characters and distinct from zero or any other number) are supported in fully relational DBMS for representing missing information and inapplicable information in a systematic way, independent of data type.

Interpretation Rule 3 addresses a critical real-world problem: sometimes data is unknown or doesn't apply. Codd recognized that a principled solution was essential. NULL is NOT: Empty string '' (which is a known value—nothing) Zero (which is a known number) False (which is a known boolean) An error state NULL IS: A marker indicating absence of a value Semantically: "unknown" or "inapplicable" Systematic Treatment NULL must be handled consistently across all operations: Comparisons: NULL = NULL is UNKNOWN, not TRUE Arithmetic: NULL + 5 = NULL Aggregates: COUNT includes NULLs, SUM ignores them Sorting: NULLs sort first or last (configurable) This consistent treatment is what "systematic" means—NULL behavior is defined, not arbitrary.

Three-Valued Logic with NULL
Expression	Result	Explanation
NULL = NULL	UNKNOWN	We don't know if two unknowns are equal
NULL <> NULL	UNKNOWN	We don't know if two unknowns differ
5 > NULL	UNKNOWN	We can't compare known to unknown
NULL AND TRUE	UNKNOWN	Unknown AND anything that isn't FALSE = Unknown
NULL AND FALSE	FALSE	Regardless of unknown, FALSE wins
NULL OR TRUE	TRUE	Regardless of unknown, TRUE wins
NULL OR FALSE	UNKNOWN	Result depends on the unknown value
NOT NULL	UNKNOWN	Negation of unknown is unknown

null-handling.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
-- Proper NULL handling in SQL
 
-- Testing for NULL (= doesn't work!)
SELECT * FROM Employee WHERE manager_id IS NULL;     -- Correct
SELECT * FROM Employee WHERE manager_id = NULL;      -- WRONG! Returns nothing
 
-- Coalesce: provide default for NULL
SELECT name, COALESCE(manager_id, 0) AS mgr 
FROM Employee;
 
-- NULLIF: return NULL if values match
SELECT NULLIF(bonus, 0) AS actual_bonus  -- Treat 0 bonus as "no bonus"
FROM Employee;
 
-- NULL in aggregates
SELECT 
    COUNT(*) AS total_rows,        -- Counts all rows including NULL
    COUNT(bonus) AS with_bonus,    -- Counts non-NULL bonus values
    SUM(bonus) AS total_bonus,     -- Sums non-NULL values
    AVG(bonus) AS avg_bonus        -- Average of non-NULL values only
FROM Employee;
 
-- CASE with NULL
SELECT name,
    CASE 
        WHEN bonus IS NULL THEN 'No bonus assigned'
        WHEN bonus = 0 THEN 'Zero bonus'
        ELSE 'Has bonus: ' || bonus::text
    END AS bonus_status
FROM Employee;

NULL Complications

NULL introduces three-valued logic, which complicates reasoning. Many subtle bugs arise from unexpected NULL behavior: WHERE x <> 5 doesn't return rows where x IS NULL. Join conditions with NULL never match. Aggregate functions behave differently. Understanding NULL semantics is crucial for correct SQL.

Rule 4: Dynamic Online Catalog Based on Relational Model

Rule 4 Statement

The database description is represented at the logical level in the same way as ordinary data, so that authorized users can apply the same relational language to its interrogation as they apply to the regular data.

Interpretation Rule 4 extends Rule 1 specifically for metadata. The database catalog (schema information) must be: Stored in tables : Using the same relational structures as user data Queryable with SQL : Same language, same skills Online and dynamic : Always reflects current state, updated automatically Subject to security : Same permission system as data The System Catalog Modern databases implement this through system catalogs: PostgreSQL: pg_catalog schema, information_schema view MySQL: information_schema, mysql database SQL Server: sys schema, INFORMATION_SCHEMA views Oracle: DBA_*, ALL_*, USER_* views, INFORMATION_SCHEMA The SQL standard defines INFORMATION_SCHEMA with standard table structures for portability.

catalog-queries.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
-- Standard INFORMATION_SCHEMA queries (SQL standard, cross-database)
 
-- List all user tables
SELECT table_name, table_type
FROM information_schema.tables
WHERE table_schema NOT IN ('pg_catalog', 'information_schema');
 
-- Find all columns with their constraints
SELECT 
    t.table_name,
    c.column_name,
    c.data_type,
    c.character_maximum_length,
    c.is_nullable,
    c.column_default
FROM information_schema.tables t
JOIN information_schema.columns c 
    ON t.table_name = c.table_name
WHERE t.table_schema = 'public'
ORDER BY t.table_name, c.ordinal_position;
 
-- Find all primary keys
SELECT
    tc.table_name,
    string_agg(kcu.column_name, ', ') AS pk_columns
FROM information_schema.table_constraints tc
JOIN information_schema.key_column_usage kcu
    ON tc.constraint_name = kcu.constraint_name
WHERE tc.constraint_type = 'PRIMARY KEY'
GROUP BY tc.table_name;
 
-- Check which tables reference a given table
SELECT
    tc.table_name AS referencing_table,
    ccu.table_name AS referenced_table
FROM information_schema.table_constraints tc
JOIN information_schema.constraint_column_usage ccu
    ON tc.constraint_name = ccu.constraint_name
WHERE tc.constraint_type = 'FOREIGN KEY'
    AND ccu.table_name = 'employee';  -- find refs to 'employee'

Practical Applications

Rule 4 compliance enables: schema comparison tools, automatic documentation generators, ORM schema introspection, migration script generators, and dependency analysis. All these tools work because metadata IS data, queryable with standard SQL.

Rule 5: Comprehensive Data Sublanguage Rule

Rule 5 Statement

A relational system may support several languages and various modes of terminal use. However, there must be at least one language whose statements are expressible, per some well-defined syntax, as character strings and that is comprehensive in supporting all of the following items: data definition, view definition, data manipulation, integrity constraints, authorization, and transaction boundaries.

Interpretation Rule 5 requires a unified language capable of all database operations. While multiple interfaces may exist (graphical tools, APIs, embedded SQL), at least one complete language must exist. SQL Fulfills This Rule SQL is the comprehensive data sublanguage that modern databases use: Data Definition (DDL) : CREATE TABLE, ALTER TABLE, DROP TABLE CREATE INDEX, CREATE SEQUENCE CREATE SCHEMA, CREATE DATABASE View Definition : CREATE VIEW, CREATE MATERIALIZED VIEW ALTER VIEW, DROP VIEW Data Manipulation (DML) : SELECT, INSERT, UPDATE, DELETE MERGE, TRUNCATE Integrity Constraints : PRIMARY KEY, FOREIGN KEY, UNIQUE CHECK, NOT NULL, DEFAULT Authorization (DCL) : GRANT, REVOKE CREATE ROLE, ALTER ROLE Transaction Control (TCL) : BEGIN, COMMIT, ROLLBACK SAVEPOINT, SET TRANSACTION

SQL Statement Categories
Category	Purpose	Key Statements
DDL	Define structure	CREATE, ALTER, DROP, TRUNCATE
DML	Manipulate data	SELECT, INSERT, UPDATE, DELETE, MERGE
DCL	Control access	GRANT, REVOKE
TCL	Control transactions	BEGIN, COMMIT, ROLLBACK, SAVEPOINT
Views	Define virtual tables	CREATE VIEW, CREATE MATERIALIZED VIEW
Constraints	Enforce integrity	PRIMARY KEY, FOREIGN KEY, CHECK

Character String Representation Notably, Codd specified that statements must be expressible as character strings. This ensures: Standardization : A textual syntax can be formally defined Portability : Statements can be written, stored, and transmitted Tool support : IDEs, version control, and documentation can work with queries Reproducibility : Exact statements can be logged and replayed This is why SQL is text-based rather than a graphical or binary protocol.

Beyond SQL

While SQL is the standard comprehensive sublanguage, rule 5 allows additional languages too. Many databases support JSON query languages (PostgreSQL's jsonpath), graph queries (Cypher via extensions), or specialized analytical languages (Window functions). These supplement but don't replace SQL's comprehensive role.

Rule 6: View Updating Rule

Rule 6 Statement

All views that are theoretically updatable are also updatable by the system.

Interpretation Views provide logical data independence by presenting data differently than base tables. But if views are read-only, they're limited. Rule 6 requires that any view which CAN be updated (mathematically) SHOULD be updatable. When is a View Updatable? A view is potentially updatable when updates can be unambiguously translated to base table updates: UPDATABLE views typically: Select from a single base table Include the primary key Don't use DISTINCT, GROUP BY, HAVING Don't use aggregate functions Don't use set operations (UNION, INTERSECT) NON-UPDATABLE views typically: Join multiple tables (which row gets updated?) Use aggregates (what does updating SUM mean?) Use DISTINCT (which duplicate do we update?) Compute derived columns (can't update a calculation)

updatable-view.sql
-- UPDATABLE VIEW
-- Simple projection of base table
CREATE VIEW active_employees AS
SELECT emp_id, name, department, salary
FROM employee
WHERE is_active = true;
 
-- This is updatable:
UPDATE active_employees
SET salary = 50000
WHERE emp_id = 1001;
-- Translates to:
-- UPDATE employee 
-- SET salary = 50000 
-- WHERE emp_id = 1001;
 
-- This INSERT works too:
INSERT INTO active_employees 
    (emp_id, name, department, salary)
VALUES (1005, 'Eve', 'Sales', 60000);
-- (is_active defaults or uses CHECK OPTION)

non-updatable-view.sql
-- NON-UPDATABLE VIEW
-- Aggregation makes updates ambiguous
CREATE VIEW dept_stats AS
SELECT 
    department,
    COUNT(*) AS emp_count,
    AVG(salary) AS avg_salary
FROM employee
GROUP BY department;
 
-- This CANNOT be updated:
UPDATE dept_stats
SET avg_salary = 80000
WHERE department = 'Engineering';
-- ERROR: How do we change individual 
-- salaries to achieve avg of 80000?
 
-- Similarly ambiguous:
INSERT INTO dept_stats
VALUES ('Marketing', 5, 70000);
-- ERROR: What are the 5 employees?

Rule 6 Compliance is Partial

No database fully complies with Rule 6. The mathematical definition of 'theoretically updatable' is complex, and many edge cases exist. Modern databases support updatable views for simple cases and allow INSTEAD OF triggers for complex cases. This remains an area where theory exceeds practice.

Codd's 12 Rules

The Relational Manifesto

Rule 0: The Foundation Rule

Rule 1: The Information Rule

Rule 2: Guaranteed Access Rule

Rule 3: Systematic Treatment of NULL Values

Rule 4: Dynamic Online Catalog Based on Relational Model

Rule 5: Comprehensive Data Sublanguage Rule

Rule 6: View Updating Rule

There’s more to understand.

Codd's 12 Rules

The Relational Manifesto

Rule 0: The Foundation Rule

Rule 1: The Information Rule

Rule 2: Guaranteed Access Rule

Rule 3: Systematic Treatment of NULL Values

Rule 4: Dynamic Online Catalog Based on Relational Model

Rule 5: Comprehensive Data Sublanguage Rule

Rule 6: View Updating Rule

There’s more to understand.