Relational Model & Codd's Rules

The Relational Model was proposed by Edgar F. Codd in 1970. It completely revolutionized the database industry, replacing the older, complex Hierarchical and Network models with a simple, mathematically sound structure based on set theory. It is the primary data model for commercial data processing applications (used by MySQL, PostgreSQL, Oracle).

1. Basic Structure & Terminology

In the relational model, data is represented as a collection of relations.

• Relation: A mathematical term for a Table. It is a subset of the Cartesian product of a list of domains.
• Tuple: A mathematical term for a Row (or record) in a table. It represents a single, complete entity.
• Attribute: A mathematical term for a Column (or field) in a table.
• Domain: The set of permitted values for a given attribute (e.g., the domain of an Age attribute might be integers from 0 to 150).
• Degree: The number of attributes (columns) in a relation.
• Cardinality: The number of tuples (rows) in a relation.

2. Properties of a Relation

For a table to be considered a true mathematical "Relation", it must adhere to strict properties:

1. Values are Atomic: Each intersection of a row and column contains exactly one value. No arrays or lists are allowed in a single cell.
2. Column Values are of the Same Kind: All values in a column must belong to the same domain.
3. Each Row is Unique: No two rows can be identical. (This is enforced mathematically, though practically SQL allows duplicates unless a Primary Key is defined).
4. Order of Columns is Irrelevant: The mathematical definition of a relation is a set of unordered attributes.
5. Order of Rows is Irrelevant: The rows are an unordered mathematical set.

3. Keys

We must have a way to uniquely identify tuples within a relation.

• Super Key: A set of one or more attributes that, taken collectively, allow us to identify uniquely a tuple in the relation.
• Candidate Key: A minimal super key. A super key for which no proper subset is also a super key.
• Primary Key: A candidate key that is chosen by the database designer as the principal means of identifying tuples.
• Foreign Key: An attribute (or set of attributes) in one relation that refers to the Primary Key of another relation. This is how relationships between tables are formed.

4. Codd's 12 Rules

In 1985, Dr. E.F. Codd published a set of 13 rules (numbered 0 to 12) to determine if a DBMS could truly be considered "Relational". If a system fails these rules, it is not a true RDBMS.

Some of the most important rules include:

• Rule 0 (Foundation Rule): The system must qualify as relational, as a database, and as a management system.
• Rule 1 (Information Rule): All information in a relational database is represented explicitly at the logical level in exactly one way—by values in tables.
• Rule 2 (Guaranteed Access Rule): 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.
• Rule 3 (Systematic Treatment of Null Values): Null values are supported for representing missing information and inapplicable information in a systematic way, independent of regular data type.
• Rule 4 (Dynamic Online Catalog): The database description (schema/metadata) is represented at the logical level in the same way as ordinary data, so authorized users can query it using the same relational language (SQL).