Relational Model, SQL and Normalization

SQL aggregate queries

Aggregate functions such as COUNT, SUM, AVG, MIN and MAX collapse a group of rows into one value. COUNT(*) counts every row, whereas COUNT(column) skips NULL values, and the other aggregate functions also ignore NULLs. A subquery that returns a single aggregated value can be used as a scalar operand in a comparison, so a row is kept only if its attribute satisfies the strict inequality against that value.

A correlated subquery refers to an alias of the outer query and is therefore re-evaluated for every outer row. When two correlated COUNT subqueries are compared, a row with an equal value is counted in neither subquery, because only strict inequalities are used, so ties affect both counts in the same way.

Relational division R ÷ S returns the values of the attributes of R that are paired with every value of S. It answers 'for all' questions. In relational algebra it is commonly computed as a difference of projections, with the natural join supplying the pairings before the division is taken.

Index choice also matters for these queries. An equality predicate can be served by either a hash index or a B+ tree, whereas a range predicate such as a greater-than comparison needs an ordered structure, because a hash index does not preserve key order.

Foreign key referencing in SQL

A foreign key declares that the values of a column in a referencing table must match values of a key, usually the primary key, in the referenced table. This is the referential integrity constraint. A NULL in the foreign key column is not matched against the referenced table, so it does not violate the constraint unless the column is declared NOT NULL.

• Insert into the referenced table — always safe, because new key values only add possible targets.
• Delete from the referencing table — always safe, because no referenced row loses a partner.
• Delete from the referenced table — may violate the constraint if referencing rows still point to the deleted key, unless ON DELETE CASCADE or ON DELETE SET NULL is declared.
• Insert into the referencing table — may violate the constraint if no matching key value exists in the referenced table.

A join along a foreign key pairs each referencing row with the referenced row that has the same key value. Because the referenced column is a key, each referencing row finds at most one partner, so conditions on the referenced table filter the joined rows directly.

Functional dependency closure

A functional dependency X \to Y holds when any two tuples that agree on X also agree on Y. The closure X^+ is the set of all attributes that X functionally determines under a given set F. It is computed by starting with X and repeatedly adding the right-hand side of any dependency whose left-hand side is already contained in the result, until nothing new is added.

Under Armstrong's axioms (reflexivity, augmentation and transitivity), a dependency X \to Y is derivable from F exactly when Y \subseteq X^+, so membership in the closure is the standard test.

• Closure — the set of attributes determined by a set of attributes, written X^+. For example, with F = {P → Q, Q → R}, we have P^+ = \{P, Q, R\}.
• Augmentation — if X \to Y holds, then XZ \to YZ holds for any set Z.
• Transitivity — if X \to Y and Y \to Z hold, then X \to Z holds.

Attributes that appear on no right-hand side are never derived unless they already belong to the left-hand side, which is a useful shortcut when checking derivability.

Relational algebra query expressions

The basic operators are selection σ, which keeps rows satisfying a condition; projection π, which keeps listed columns and removes duplicates; natural join ⋈, which matches tuples on all attributes that share a name; and renaming ρ. Expressions are read from the inside out, so the innermost operator is evaluated first.

A natural join keeps only pairs whose values are equal on every common attribute. When several relations are chained by joins, shared attributes such as a key and its foreign key connect them, and a final projection can return values from just one relation. The earlier joins then act only as filters on that relation.

A natural join drops a tuple when its value on a common attribute matches nothing on the other side. NULL does not equal any value, including another NULL, so a tuple with NULL on a join attribute does not join and disappears from the result.

A selection that uses only attributes of one relation may be applied before a join without changing the result, which is the basis of query optimisation.

Relational algebra set difference

The difference R − S returns the tuples that appear in R but not in S. Both operands must be union-compatible, meaning they have the same number of attributes with matching domains. Intersection can be expressed through difference as R \cap S = R - (R - S).