3.1 The Relational Model
Introduced by E.F. Codd in 1970, the Relational Model organizes all data into mathematically defined tables called relations. In relational terminology, a table is a relation, each row represents a unique record called a tuple, and each column represents a field called an attribute.
Figure 3.1: In the relational model, each row is a tuple, each column is an attribute belonging to a typed domain, and tables are connected via Foreign Keys.
3.2 Types of Keys
Keys are the foundational mechanism in relational databases for enforcing entity identity, preventing duplicate rows, and guaranteeing referential integrity between interconnected tables.
Figure 3.2: A Candidate Key is a minimal Super Key; the database designer picks one Candidate Key to be the Primary Key, leaving the rest as Alternate Keys.
Key Classifications
- Super Key: A set of one or more attributes that collectively identifies a unique tuple in a relation. May contain redundant attributes.
- Candidate Key: A minimal super key from which no attribute can be removed without destroying the uniqueness property. A table can possess multiple candidate keys.
- Primary Key: The specific candidate key chosen by the database architect as the principal identifier for rows in the table. Must be strictly UNIQUE and NOT NULL.
- Alternate Key: Candidate keys that were not selected as the primary key.
- Foreign Key: An attribute or collection of attributes in one table that references the primary key of another table, guaranteeing Referential Integrity.
3.3 Relational Algebra Basics
Relational Algebra is a formal, procedural mathematical query language that serves as the theoretical underpinning of SQL. When an SQL query is executed, the database query parser and optimizer convert the declarative SQL text into a relational algebra expression tree for evaluation.
Fundamental Relational Operators
- Select ($sigma_{condition}(R)$): Filters rows that satisfy a specified predicate condition (equivalent to SQL
WHERE).σage > 18(Students) - Project ($pi_{A_1, A_2}(R)$): Filters columns, retaining only specified attributes and removing duplicates (equivalent to SQL
SELECT col1, col2).πname, email(Students) - Cartesian Product ($R imes S$): Combines every tuple of relation $R$ with every tuple of relation $S$. If $R$ has $n$ rows and $S$ has $m$ rows, result has $n imes m$ rows (equivalent to SQL
CROSS JOIN). - Natural Join ($R owtie S$): Equi-joins two tables on all shared attributes with the same name, suppressing duplicate join columns (equivalent to SQL
NATURAL JOIN). - Set Operators: Union ($R cup S$), Intersection ($R cap S$), Set Difference ($R - S$). Both relations must be union-compatible (same degree and compatible attribute domains).
