Demonstrating Codd’s Twelve Rules in a Relational Database

Relational database management systems (RDBMS) are built on the foundation of Codd’s Twelve Rules. These rules ensure that a database system is truly relational and adheres to standards set by Edgar F. Codd in his seminal 1970 paper. Through practical demonstrations, one can prove that a database management system (DBMS) satisfies these rules. Below, we provide an overview of how to demonstrate each of Codd’s Twelve Rules using SQL commands.

The Information Rule

The Information Rule mandates that all data should be represented as values in tables. To demonstrate this rule, create a table with multiple data types, such as integers, strings, and dates. Fetch the data to show that it can be retrieved in a structured format.

Demonstration

SQL:

CREATE TABLE Employees ( EmployeeID INT PRIMARY KEY, FirstName VARCHAR(50), LastName VARCHAR(50), HireDate DATE ); INSERT INTO Employees (EmployeeID, FirstName, LastName, HireDate) VALUES (1, 'John', 'Doe', '2023-05-20'); SELECT * FROM Employees;

This SQL script both creates the table and inserts a sample record, followed by a query to fetch the data.

Guaranteed Access Rule

The Guaranteed Access Rule requires that every data element is accessible without ambiguity. Each row should be uniquely identified by its primary key.

Demonstration

SQL:

SELECT FirstName FROM Employees WHERE EmployeeID 1;

This query demonstrates that every row in the table can be uniquely identified by its primary key.

Systematic Treatment of Null Values

The Systematic Treatment of Null Values rule ensures that NULLs are handled systematically. This means that NULL values should be treated consistently and can be queried.

Demonstration

SQL:

INSERT INTO Employees (EmployeeID, FirstName, LastName, HireDate) VALUES (2, 'Jane', NULL, '2022-01-15'); SELECT * FROM Employees WHERE LastName IS NULL;

This script inserts a record with a NULL value and queries it to ensure NULLs are treated systematically.

Dynamic On-Line Catalog Based on the Relational Model

The Dynamic On-Line Catalog ensures that the database metadata can be queried dynamically. This rule relies on the database’s ability to provide metadata about the schema, such as the tables and their columns.

Demonstration

SQL:

SELECT * FROM information_ WHERE table_name 'Employees';

This query from PostgreSQL demonstrates how to access the schema metadata of the 'Employees' table.

Comprehensive Data Sublanguage Rule

The Comprehensive Data Sublanguage rule mandates that the system should support data definition, manipulation, and transaction control using SQL. This ensures that the database can manage complex operations.

Demonstration

SQL:

BEGIN; INSERT INTO Employees (EmployeeID, FirstName, LastName, HireDate) VALUES (3, 'Jane Doe', NULL, '2022-01-15'); COMMIT;

This example demonstrates creating and managing a transaction using SQL.

View Updating Rule

The View Updating Rule asserts that it should be possible to update a view and have these changes reflect in the underlying table.

Demonstration

SQL:

CREATE VIEW EmployeeNames AS SELECT FirstName, LastName FROM Employees; UPDATE EmployeeNames SET FirstName 'Jane' WHERE LastName 'Doe';

This demonstration creates a view and updates the view, which in turn updates the underlying table.

High-Level Insert, Update, and Delete Operations

The High-Level Insert, Update, and Delete Operations rule mandates that operations can be performed on sets of rows rather than individual rows, improving efficiency.

Demonstration

SQL:

DELETE FROM Employees WHERE HireDate '2020-01-01';

This script demonstrates deleting multiple rows at once, based on a condition.

Access to Data on Logical Level

The Access to Data on Logical Level ensures that data can be accessed logically without concern for physical storage. The query should not specify how the data is stored, but rather how it is presented.

Demonstration

SQL:

SELECT EmployeeID, FirstName, LastName FROM Employees WHERE HireDate > '2020-01-01';

This query demonstrates accessing data in a logical manner without specifying the physical storage format.

Logical Data Independence

The Logical Data Independence rule ensures that changing the structure of the database (e.g., adding a new column) does not affect existing applications. Applications should continue to function without modification.

Demonstration

SQL:

ALTER TABLE Employees ADD COLUMN Salary DECIMAL(10,2); SELECT * FROM Employees;

This example adds a new column to the table and demonstrates that existing queries still work.

Physical Data Independence

The Physical Data Independence rule ensures that changing the storage structure or method does not affect applications. This rule is often demonstrated at a higher level through real-world scenarios.

Demonstration

SQL:

-- Typically requires changing database configuration or storage engine.

This demonstration is more conceptual and may require actual changes to the database configuration in a real-world scenario.

Integrity Independence

The Integrity Independence rule ensures that integrity constraints (e.g., primary keys, foreign keys) can be enforced independently of application code.

Demonstration

SQL:

ALTER TABLE Employees ADD CONSTRAINT fk_department FOREIGN KEY (DepartmentID) REFERENCES Departments (DepartmentID); SELECT * FROM Employees;

This example defines a foreign key constraint and demonstrates that it can be enforced independently.

Non-Subversion Rule

The Non-Subversion Rule ensures that if a lower-level interface is provided, integrity constraints defined at a higher level cannot be bypassed.

Demonstration

SQL:

CREATE PROCEDURE AddEmployee(IN empID INT, IN firstName VARCHAR(50)) BEGIN IF NOT EXISTS (SELECT 1 FROM Employees WHERE EmployeeID empID) THEN INSERT INTO Employees (EmployeeID, FirstName) VALUES (empID, firstName); ELSE SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT 'Employee ID already exists.'; END IF; END;

This stored procedure demonstrates enforcing constraints at a higher level to prevent bypassing integrity rules.

By executing these demonstrations, one can effectively show that a database system adheres to Codd’s Twelve Rules, ensuring it is truly relational and meets the standards set by Edgar F. Codd.