Dr. Edgar F.
Codd, the pioneer of relational databases, proposed 12 rules (often referred to as
Codd’s 12 rules) to define what a fully relational database management system (RDBMS)
should satisfy. Here’s a simple explanation of each:
Rule 0: The Foundation Rule
Definition: A system must qualify as a relational database management system and
must support the relational model's principles.
Meaning: The system must use relational concepts and manage data through tables,
rows, and columns.
Rule 1: Information Rule
Definition: All information in a database must be represented as values within tables.
Meaning: Data should only be stored in table cells (rows and columns), not in
separate files or structures.
Rule 2: Guaranteed Access Rule
Definition: Each piece of data (value) in the database must be accessible by a
combination of table name, primary key, and column name.
Meaning: Every value should be uniquely retrievable using its table, row, and
column.
Rule 3: Systematic Treatment of Null Values
Definition: The database must support null values to represent missing, unknown, or
inapplicable data.
Meaning: Nulls should be treated systematically and should not cause inconsistencies
or errors.
Rule 4: Dynamic Online Catalog
Definition: The database's structure (schema) must be stored within the database itself
and be accessible through standard query languages.
Meaning: The metadata (e.g., table structures, constraints) must be queryable like
data.
Rule 5: Comprehensive Data Sublanguage Rule
Definition: The database must support at least one language that allows data
definition, manipulation, and query (e.g., SQL).
Meaning: This language should let users define tables, retrieve data, and update it
seamlessly.
Rule 6: View Updating Rule
Definition: Changes made to views (virtual tables) must be reflected in the underlying
base tables.
Meaning: If a user updates a view, the database should correctly update the
associated tables.
Rule 7: High-Level Insert, Update, and Delete
Definition: The system must support set-level operations for insertion, updating, and
deletion.
Meaning: It should handle operations on multiple rows or records at once, not just
one row at a time.
Rule 8: Physical Data Independence
Definition: The database's logical structure should remain unchanged even if its
physical storage changes.
Meaning: Users don’t need to worry about how or where data is physically stored.
Rule 9: Logical Data Independence
Definition: Changes to the database schema should not affect how data is accessed.
Meaning: Applications accessing the data should remain unaffected if structural
changes (e.g., adding a column) are made to the schema.
Rule 10: Integrity Independence
Definition: Integrity constraints (e.g., primary keys, foreign keys) must be stored in
the database and not in application programs.
Meaning: Rules like "no duplicate primary keys" should be enforced at the database
level.
Rule 11: Distribution Independence
Definition: A database must work correctly whether it's distributed across multiple
locations or centralized.
Meaning: Users shouldn’t need to know whether the database is stored on one server
or several.
Rule 12: Non-Subversion Rule
Definition: If the database has lower-level access methods, they must not bypass the
database's integrity rules.
Meaning: All access to data, even through low-level methods, must follow the
database's security and integrity constraints.
Summary
These 12 rules ensure that an RDBMS remains consistent, reliable, and truly relational. Not
all databases meet all of these rules; they serve as an ideal standard for designing relational
databases
Let’s create two tables:
1. Customer Table (Customer Info)
2. Orders Table (Customer Orders)
We’ll then relate Super Key, Candidate Key, Primary Key, Alternative Key, and
Composite Key in this scenario.
Customer Table (Customer Info)
CustomerID (PK) Name Email PhoneNumber
101 John Doe john@example.com 9876543210
102 Jane Smith jane@example.com 1234567890
Orders Table (Customer Orders)
OrderID (PK) CustomerID (FK) OrderDate OrderAmount
201 101 2024-12-10 150.00
202 102 2024-12-11 200.00
Relation of Keys
Key Type Customer Table Orders Table Explanation
Primary CustomerID OrderID Uniquely identifies each record in
Key (PK) the respective table. CustomerID
for customers, and OrderID for
orders.
Foreign - CustomerID CustomerID in Orders Table
Key (FK) references the CustomerID in
Customer Table, establishing a
relationship between the two tables.
Super Key CustomerID, OrderID, Any combination of attributes that
Email, CustomerID + uniquely identifies records. E.g.,
CustomerID + OrderDate CustomerID + PhoneNumber in
PhoneNumber Customer Table, or OrderID +
CustomerID in Orders Table.
Candidate CustomerID, OrderID Minimal attributes that uniquely
Key Email identify a record. E.g., CustomerID
and Email in Customer Table, and
OrderID in Orders Table.
Alternative Email - If CustomerID is the primary key,
Key then Email in Customer Table is
an alternative key.
Composite - CustomerID + If neither OrderID nor CustomerID
OrderDate
Key can uniquely identify an order, the
combination of CustomerID +
OrderDate could serve as a
composite key in Orders Table.
Key Relationship Between Tables:
Primary Key in Customer Table (CustomerID) → Foreign Key in Orders Table
(CustomerID): This links a customer's information to their orders.
Keys like OrderID ensure each order is unique, while CustomerID connects orders to
the correct customer.
