Integrity constraints and relational algebra
Constraints keep data valid, and relational algebra provides the mathematical operations underlying every SQL query.
Domain and key constraints
Domain constraints restrict each attribute's values to its type and allowed range, such as an age between 0 and 150 or a status from a fixed list. Key constraints require primary and unique key values to be distinct. NOT NULL constraints forbid missing values where data is required. The DBMS rejects any change that violates a declared constraint.
Entity and referential integrity
Entity integrity requires that primary key values are never null, so every row can be identified. Referential integrity requires that every foreign key value either matches an existing primary key in the referenced table or is null. Together they ensure relationships always point to real data.
Enforcing referential integrity
When a referenced row is deleted or its key updated, the DBMS can reject the change (RESTRICT or NO ACTION), cascade it to referencing rows (CASCADE, for example deleting an order's line items with the order), set referencing values to null (SET NULL), or set them to a default (SET DEFAULT). The right choice depends on the meaning of the relationship.
Selection, projection, and renaming
Relational algebra is a set of operations taking relations as input and producing relations as output. Selection (sigma) keeps tuples satisfying a condition, such as students with major = 'CS'. Projection (pi) keeps only chosen attributes, removing duplicates. Renaming (rho) gives relations or attributes new names, needed when combining a relation with itself.
Set operations
Union, intersection, and difference combine relations with compatible schemas, meaning the same number of attributes with matching domains. Union gives tuples in either; intersection, in both; difference, in the first but not the second. For example, students enrolled in Maths minus those enrolled in Physics gives students taking Maths but not Physics.
Products and joins
The Cartesian product pairs every tuple of one relation with every tuple of another, so relations of 100 and 1,000 tuples give 100,000 pairs. A join combines a product with a selection: the natural join matches tuples with equal values in their shared attributes, and a theta join uses any condition. Joins reconnect data that normalisation has split into separate tables.
Why algebra matters
Because relational algebra operations take and return relations, they compose into expressions, like arithmetic. Queries written in SQL are translated into algebra expressions, which optimisers rewrite into equivalent but cheaper forms, for example pushing selections below joins so fewer tuples are joined. Codd also defined relational calculus, a declarative logic-based counterpart with the same expressive power.
Worked example
Student(id, name, major) and Enrolled(id, course). To find names of students enrolled in DB101: select Enrolled tuples where course = 'DB101', natural join with Student on id, then project name. Written as algebra: pi_name(Student join sigma_course='DB101'(Enrolled)). Selecting first means joining only the few DB101 enrolments rather than every enrolment.
Key takeaways
Domain, key, and NOT NULL constraints keep values valid. Entity integrity forbids null primary keys; referential integrity requires foreign keys to match existing keys or be null, enforced by RESTRICT, CASCADE, SET NULL, or SET DEFAULT. Relational algebra operates on relations: selection filters rows, projection chooses columns, renaming relabels, union, intersection, and difference combine compatible relations, and products and joins combine tables. Algebra expressions compose, underlie SQL, and let optimisers rewrite queries efficiently.
Flashcard
Restricts an attribute's allowed values
Check your understanding
Deleting a customer should automatically delete their saved addresses. Which referential action fits?