Introduction to the Relational Model | Relational Model and Relational Algebra | 2114112 Database Management System | Relational Model and Relational Algebra |

Database Fundamentals

The Relational Data Model

The elegant, mathematically grounded model that revolutionized databases in 1970 — and still dominates commercial data processing today.

👨‍🔬

Introduced in 1970 by Edgar (Ted) Codd at IBM San Jose Research Laboratory. Codd's model offered a simple, elegant structure grounded in set theory and first-order predicate logic — separating conceptual representation of data from physical storage.

00 A Brief History

1970

Codd publishes "A Relational Model of Data for Large Shared Data Banks" — the paper that introduced the relational model to the world.

1970s

IBM's System R project develops SQL (Structured Query Language) as the practical implementation language for relational databases.

1980s–Now

Relational model supplants earlier hierarchical and network models and becomes the dominant data model for commercial data-processing applications worldwide.

01 Basic Concepts & Structure

The core construct of the relational model is a relation — informally represented as a two-dimensional table of values. Every relation is composed of three fundamental elements: tuples, attributes, and domains.

📄

Tuple (Row)

Each row in the table. Represents a collection of related data values for a specific real-world entity or relationship.

🏷️

Attribute (Column)

The column headers of a relation. Each attribute specifies the meaning and interpretation of data in that column.

🔒

Domain

The permitted set of values and data type for each attribute. In the flat relational model, all domain values must be atomic — indivisible single units.

ℹ️

Atomicity: In the formal flat relational model, domain values cannot be sets, lists, or complex nested structures. Every cell must contain a single, indivisible value — e.g., a phone number, not a list of phone numbers.

🔬 Anatomy of a Relation — EMPLOYEE Table

	emp_id	name	salary	hire_date	dept_id
tuple 1	E001	Alice Sharma	55,000	2019-03-12	D10
tuple 2	E002	Bob Mehta	72,000	2017-08-01	D20
tuple 3	E003	Carol Patil	48,500	2021-11-30	D10

Primary Key (underlined) Foreign Key Each row = one tuple Each column = one attribute

02 Schema vs. Instance

📐 Relation Schema Time-Invariant

• Definition: The logical design or blueprint — the relation's name + list of attributes + their domains.
• Degree (Arity): The number of attributes in the schema. A 5-column table has degree 5.
• Stability: The schema rarely changes; it defines the structure, not the data.

-- Schema notation EMPLOYEE(emp_id, name, salary, dept_id) -- Degree = 4 attributes

📸 Relation Instance Snapshot in Time

• Definition: The actual data contents of the relation at a specific point in time — mathematically a set of tuples.
• Cardinality: The total number of tuples (rows) in the instance. Changes as rows are inserted or deleted.
• Fluidity: Instances change constantly; the schema template governs what's valid.

-- Instance at time T (cardinality = 3) (E001, Alice, 55000, D10) (E002, Bob, 72000, D20) (E003, Carol, 48500, D10)

03 Key Characteristics of Relations

Because relations are mathematically defined as sets of tuples, they inherit special properties that differentiate them from ordinary data files or spreadsheets.

🔀

No Ordering of Tuples

In set theory, elements have no sequence. Tuples within a relation are not ordered — there is no "first" or "last" row in a formal relation. Any ordering seen in a query result is imposed externally.

🔁

Uniqueness of Tuples

Sets cannot contain duplicates, so no two tuples can be identical in a formal relation. Note: practical SQL systems relax this rule and allow duplicate rows (multisets) unless a constraint is applied.

💡

Formal vs. Practical: The mathematical relational model is stricter than SQL. Real-world SQL databases use multisets (bags) that allow duplicate rows, whereas formal relational algebra treats relations as true sets — duplicates are automatically eliminated.

04 Integrity Constraints

Integrity constraints ensure the database remains an accurate reflection of the real-world enterprise it models. Violations of these constraints are rejected by the DBMS.

Constraint Type	What it Enforces	Level
Domain Constraint	Every value in a tuple must come from the predefined domain of its attribute (correct data type/range).	Attribute
Key Constraint	No two tuples can have the same value for the primary/candidate key — guarantees tuple uniqueness.	Relation
Entity Integrity	No primary key attribute can ever be NULL — every tuple must be uniquely identifiable.	Relation
Referential Integrity	A foreign key value must either match an existing primary key in the referenced relation, or be NULL.	Database

🟡 Domain Constraints Attribute Level

• The most elementary constraint — every value stored in a tuple must be drawn from the predefined domain for that attribute.
• Examples: an age attribute must store integers, a date field must hold valid dates, a salary field cannot hold text strings.

🔑 Key Constraints Relation Level

• Superkey: Any set of attributes that uniquely identifies a tuple.
• Candidate Key: A minimal superkey — no redundant attributes. A relation can have multiple candidate keys.
• Primary Key: The one candidate key chosen as the principal row identifier — denoted by underlining in schema notation.

-- EMPLOYEE schema — primary key underlined EMPLOYEE(emp_id, name, salary, dept_id) -- Superkeys: {emp_id}, {emp_id, name}, {emp_id, name, salary, dept_id} … -- Candidate keys: {emp_id} (assuming emp_id is the only minimal one) -- Primary key: emp_id

🔴 Entity Integrity Constraint No NULL Primary Keys

• States that no primary key attribute can ever hold a NULL value.
• Rationale: the primary key is the only mechanism to uniquely identify a tuple. A NULL primary key would make the tuple unidentifiable — destroying the core guarantee of the relational model.

🚫

Inserting a row with a NULL primary key is always rejected by any conformant relational DBMS, regardless of other attribute values.

🟢 Referential Integrity & Foreign Keys Database Level

• A foreign key is a set of attributes in one relation (the referencing relation) whose values must match the primary key of another relation (the referenced relation).
• A foreign key value may also be NULL — indicating that the referencing tuple does not currently reference any other tuple.
• Dangling references — pointing to non-existent rows — are strictly forbidden.

-- EMPLOYEE.dept_id references DEPARTMENT.dept_id EMPLOYEE(emp_id, name, salary, dept_id) DEPARTMENT(dept_id, dept_name, location) FOREIGN KEY (dept_id) REFERENCES DEPARTMENT(dept_id) -- Every dept_id in EMPLOYEE must exist in DEPARTMENT, or be NULL

05 Relational Query Languages

The relational model supports high-level query languages that shield users from writing complex procedural code to traverse physical files. The three pillars are Relational Algebra, Relational Calculus, and SQL.

Relational Algebra

Procedural · Functional

• Operates on relations using a set of formal operations.
• Core operators: σ (Select), π (Project), ⋈ (Join), ∪ (Union), × (Cartesian Product).
• Describes how to compute a result step by step.
• Foundation for query optimization in DBMSs.

Relational Calculus

Declarative · Logic-based

• Based on mathematical predicate logic.
• User describes the desired output, not the step-by-step procedure.
• Two forms: Tuple Relational Calculus (TRC) and Domain Relational Calculus (DRC).
• Equivalent in expressive power to relational algebra.

SQL

Structured Query Language · Commercial Standard

• Developed during IBM's System R project in the 1970s.
• Practical commercial standard for all relational databases.
• Incorporates elements of both relational algebra and calculus.
• Covers data definition (DDL) and manipulation (DML).

Language	Style	Specifies How?	Used In Practice?
Relational Algebra	Procedural	✓ Step-by-step	Internally (query optimizers)
Relational Calculus	Declarative	✗ Describes result only	Theoretical basis for SQL
SQL	Hybrid	Both (mixed)	✓ Universal commercial standard

The Relational Data Model — Codd's 1970 vision that gave us the mathematical elegance powering every modern relational database.