12 Codd S Rules in SQL
Dr. E. F. Codd proposed 12 rules (numbered from 0 to 12) to define what is required from a Database Management System (DBMS) to be considered relational — meaning it should manage data using relational algebra and support full relational capabilities.
These rules are conceptual and serve as a guideline for evaluating relational database systems like MySQL, PostgreSQL, Oracle, SQL Server, etc.
? Codd's 12 Rules in SQL (Relational Database Management Systems)
Rule 0: Foundation Rule
A system must qualify as a relational database system and use its relational capabilities to manage the database.
Rule 1: Information Rule
All data should be represented logically in tables (relations), and the data should be stored in cells at the intersection of rows and columns.
? Everything (data, metadata) should be stored in table format.
Rule 2: Guaranteed Access Rule
Every data item (atomic value) should be accessible by a combination of table name, primary key (row), and column name.
? No hidden pointers or location-based access.
Rule 3: Systematic Treatment of Null Values
Null values must be uniformly handled as representing missing, inapplicable, or unknown information.
? Null ? 0 or empty string; and should be treated consistently.
Rule 4: Dynamic Online Catalog Based on the Relational Model
The database should store its structure (metadata) using the same relational format as normal data, and users should be able to query this catalog using SQL.
? Example: Accessing information_schema in MySQL/PostgreSQL.
Rule 5: Comprehensive Data Sub-language Rule
The system must support at least one language that:
Can define data (DDL)
Manipulate data (DML)
Define constraints
Handle transactions and views
? SQL is the language used in modern RDBMS.
Rule 6: View Updating Rule
All theoretically updatable views should be updatable through the system.
? Not all views in practice are updatable; depends on DBMS.
Rule 7: High-Level Insert, Update, and Delete
The system must support set-level (not just row-by-row) operations.
? You should be able to update/delete multiple rows at once.
Rule 8: Physical Data Independence
Changes in physical storage (e.g., how data is stored on disk) should not affect how you access the data.
? SQL queries should work even if the internal structure is optimized.
Rule 9: Logical Data Independence
Changes in logical schema (tables, columns) should not affect applications, as long as the changes do not alter the data semantics.
? Hard to fully implement; even modern RDBMS struggle here.
Rule 10: Integrity Independence
Integrity constraints (like
NOT NULL,FOREIGN KEY, etc.) should be stored in the catalog and not in the application code.
? Should be enforced by the DBMS itself.
Rule 11: Distribution Independence
The system should function regardless of whether data is distributed across multiple locations.
? Users should not need to know where the data resides physically.
Rule 12: Non-subversion Rule
If the system provides low-level access (e.g., file-level), it should not bypass integrity rules and constraints.
? Access through all levels must enforce all data rules.
? Summary Table
| Rule No. | Rule Name | Description |
|---|---|---|
| 0 | Foundation Rule | Must be a relational system |
| 1 | Information Rule | Data stored in tables |
| 2 | Guaranteed Access | Access by table, primary key, column |
| 3 | Null Handling | Consistent treatment of nulls |
| 4 | Active Catalog | Metadata in relational form |
| 5 | Comprehensive Language | Support full SQL features |
| 6 | View Updating | Views should be updatable |
| 7 | Set-Level Operations | Support multi-row ops |
| 8 | Physical Data Independence | No effect of physical changes |
| 9 | Logical Data Independence | No effect of schema changes |
| 10 | Integrity Independence | Constraints in DB, not code |
| 11 | Distribution Independence | No user concern of data location |
| 12 | Non-subversion | All access obeys DB rules |
Let me know if you want a printable version or quiz-style explanation for learning!
