Relational Model

Introduction

The relational model was proposed by Dr. E.F. Codd of IBM in 1970 in his landmark paper "A Relational Model of Data for Large Shared Data Banks." It is based on the mathematical concept of a relation (a set of tuples) and uses first-order predicate logic for its formal foundation.

The relational model is the foundation of all SQL databases.

Core Concepts

Relation (Table)

A relation is a two-dimensional table consisting of rows and columns. Each relation has:

• A unique name
• A fixed set of columns (attributes)
• An unordered set of rows (tuples)

Attribute

An attribute is a named column in a relation. Each attribute has:

• A name (e.g., EmpID, Name)
• A domain — the set of valid values for that attribute

Tuple

A tuple is a single row in a relation — one instance of the entity. Each tuple has one value for each attribute (or NULL if the attribute is optional).

Domain

A domain is the set of all possible values for an attribute. Domains provide a semantic definition:

Schema vs. Instance (Relation)

Properties of a Relation

1. Unique name: Each relation has a distinct name in the database.
2. Unique attribute names: Each column within a relation has a unique name.
3. Atomic attribute values: Each cell contains exactly one value (1NF).
4. No duplicate tuples: Each row is unique (ensured by primary key).
5. No ordering of rows: The physical order of rows doesn't matter.
6. No ordering of columns: Columns are identified by name, not position.
7. All values in a column come from the same domain.

Integrity Constraints

1. Domain Constraints

Every value in a column must come from the defined domain of that attribute.

2. Entity Integrity Constraint

The primary key of a relation cannot contain NULL values. Every tuple must be uniquely identifiable.

3. Referential Integrity Constraint

If a foreign key value exists in one relation, it must either:

• Match a primary key value in the referenced relation, OR
• Be NULL

4. User-Defined Integrity Constraints

Business rules enforced via CHECK constraints:

• CHECK (Salary > 0)
• CHECK (Age BETWEEN 18 AND 65)
• CHECK (Grade IN ('A', 'B', 'C', 'D', 'F'))

Relational Schema Notation

Codd's 12 Rules (Brief Overview)

E.F. Codd defined 12 rules (Rule 0 to Rule 12) that a DBMS must satisfy to be considered fully relational. Key rules include:

• Rule 1: All data must be represented in tables
• Rule 2: Guaranteed access to any data by table name + primary key + column name
• Rule 6: View updating — all theoretically updateable views must be updatable
• Rule 9: Logical data independence
• Rule 10: Physical data independence