Foreign key Created 2026-10-31 Updated 2026-11-05
A foreign key is a database integrity constraint requiring designated database attributes to match a referenced identifier in a database relation. The referenced database relation may be the same one: a self-reference can represent each employee's manager. The identifier being referenced must have the required uniqueness, but the referencing database attributes need not be unique; several employees can have the same manager.[ref]
Constraints in the PostgreSQL documentation. version 18, section 5.5.5, foreign keys and self-referential tree example
For a simple example, let
Instrument(id) contain identifiers . A measurement carrying instrument_id=4 has a matching scientific instrument; one carrying instrument_id=7 violates the foreign key. Deleting scientific instrument four while measurements still refer to it requires a stated policy: reject the deletion, cascade a suitable deletion, or apply another permitted referential action.[ref]SQL NULL and compound foreign keys require an explicit matching policy. A nullable simple reference can represent no parent, whereas
NOT NULL can require a reference. A foreign key enforces reference existence; it does not by itself prove that a self-referencing structure has no directed cycles. Past exam of the computer science course of the University of Oxford 2015 Databases 1 a iii Solution 2026-10-31
An arithmetic mean is database aggregation, which is outside classical relational algebra. Use SQL:For non-null prices of qualifying sales, the result isEach sale contributes once, even when a car was sold on several invoices. Do not use
SELECT AVG(s.price) AS average_sale_price
FROM Sales AS s
JOIN Car AS c ON c.serialnumber = s.serialnumber
WHERE c.make = 'Saab';DISTINCT inside AVG: two different sales at the same price must both count. The car's database primary key makes the relational join match each sale to exactly one car, so the relational join introduces no extra weighting.If there are no qualifying non-null prices,
AVG returns SQL NULL. It ignores null prices if the implementation permits them; it does not replace an absent arithmetic mean with zero.[ref]Aggregate Functions in the PostgreSQL documentation. Section 9.21,
avg and the paragraph on aggregates with no selected rows Relational model Created 2026-10-31 Updated 2026-11-05
The relational model represents data as database relations, which are sets of database tuples over specified value domains. A relational schema names the database attributes and states database integrity constraints; an instance supplies their current values. Relational algebra describes operations on this logical data, while storage order, indexes and access paths belong to its implementation.
For example, the database relation
Reading(sample, value) can contain . A relational selection for values above five followed by relational projection onto sample identifiers returns . Changing the underlying file layout or adding an index need not change that query's meaning. Mathematical database relations have set semantics; SQL often uses bag semantics and has additional SQL NULL behavior.In his 1970 paper, E. F. Codd argued for data independence: application logic should not rely on particular stored orderings, indexes or navigational paths. His relational formulation made that separation central to database design.[ref]
SQL NULL Created 2026-10-31 Updated 2026-11-05
SQL NULL marks a missing or unknown value. Ordinary comparisons involving SQL NULL, including
NULL = NULL, produce the truth value unknown. To test a scalar value for absence, use IS NULL; to compare while treating two missing values alike, use IS NOT DISTINCT FROM.[ref]Comparison Functions and Operators in the PostgreSQL documentation. version 18, section 9.2, null comparisons and predicates
This three-valued logic changes query reasoning.
5 NOT IN (2, NULL) is unknown: five differs from two, but the missing value might be five. A relational selection retains true conditions, so unknown does not pass the filter.[ref]Subquery Expressions in the PostgreSQL documentation. version 18, section 9.24.3, NOT IN with null inputs
Database aggregation has operation-specific rules. On values
2, NULL, 4, COUNT(*) counts three rows, COUNT(value) counts two non-null values and AVG(value) is three. With no input rows, COUNT returns zero while AVG and SUM return SQL NULL. Therefore a blanket rule that all database aggregation ignores SQL NULL is insufficient.[ref]Aggregate Functions in the PostgreSQL documentation. version 18, section 9.21, count variants and empty-input behavior