Relational Model and Relational Algebra

• Relation = set of tuples (rows) over a schema of attributes (columns)
• It is a set: no duplicate tuples, row order irrelevant
• Domain = allowed atomic values, e.g. age: integers 0-150
• Degree = number of attributes; cardinality = number of tuples

• Foreign key references a candidate/primary key, in another or the same relation
• Value must match an existing key or be NULL (referential integrity)

CREATE TABLE Employee (
  id INT PRIMARY KEY,
  email TEXT UNIQUE,
  boss_id INT REFERENCES Employee(id)
);

boss_id points back into the same relation

• Relations are sets, so π always dedupes rows

• π_name(σ_age>20(R)) works
• σ_age>20(π_name(R)) fails: age was projected away
• Selections commute: σ_c1(σ_c2(R)) = σ_c2(σ_c1(R)) = σ_c1∧c2(R)

• ρ_S(A→B)(R) renames the relation to S and attribute A to B

• R ∪ S, R ∩ S, R − S need union-compatible relations
• Same degree and matching domains per column
• Result takes attribute names from R
• R − S ≠ S − R: difference is not commutative
• R ∩ S = R − (R − S): intersection is derived

• Product alone gives meaningless pairs; follow it with a selection

π_id(Customer) − π_cust_id(Order)

customers who never ordered

• Theta join R ⋈_θ S = σ_θ(R × S)
• Equi-join: theta join with only = conditions
• Natural join ⋈: equates same-named attributes, keeps one copy each

• No common attributes: natural join becomes a Cartesian product
• Accidentally shared column names: silently joins on the wrong columns

• R ÷ S: values of attrs(R) − attrs(S) paired with every tuple of S
• The 'for all' query: students who took all required courses

• Algebra mirrors it: π(σ(R × S))
• SQL tables are bags; algebra relations are sets
• SELECT keeps duplicates; add DISTINCT to match π
• UNION, INTERSECT, EXCEPT drop duplicates; ALL keeps them

• No division operator in SQL

• Fundamental: σ π ∪ − × ρ
• Derived: intersection, joins, division
• R ∩ S = R − (R − S)

• Union compatible: same degree and same domains per column