Database Created 2026-10-31 Updated 2026-11-05
A database is an organized collection of data maintained for queries and updates. A database management system manages that collection, enforcing access rules and database integrity constraints. A relational database represents it through database relations, rather than exposing only an arbitrary collection of bytes in files.
For example, a laboratory can store biological sample identifiers, measurement values and scientific instrument identifiers as database tuples. A database query can select measurements above a threshold and join them to scientific instrument records. A foreign key can require every recorded scientific instrument identifier to name an existing scientific instrument. This makes the connection a maintained rule rather than a convention that every application must remember.
The logical relational schema and actual storage are distinct. A database query plan may use an index or scan database pages while returning the same specified result. Database transactions coordinate related updates, and database integrity constraints restrict the permitted states. PostgreSQL's documentation gives a concrete implementation in which named tables contain rows with named, typed columns and databases group tables.[ref]
Entity-relationship model Created 2026-10-31 Updated 2026-11-05
An entity-relationship model describes identifiable entities, their database attributes and the relationships between them before committing to a physical database layout. Entity types classify the objects being recorded; relationship types describe associations, possibly with their own database attributes. Peter Pin-Shan Chen developed the model as a way to retain the meaning of data while relating several existing database models.[ref]
For example, a laboratory records scientific instrument entities and measurement entities, with a relationship saying which scientific instrument produced each measurement. An scientific instrument's serial number is an identifying database attribute. A calibration date can describe an scientific instrument, while a usage duration can describe a particular relationship occurrence. Distinguishing these placements prevents unrelated facts from being conflated.
Cardinality and participation constraints add rules: every measurement might require exactly one scientific instrument, while an scientific instrument can produce zero or many measurements. An entity-relationship diagram records these choices. Translating the design to a relational schema introduces database relations, identifiers and appropriate foreign keys; the diagram itself does not require a particular index or file structure.
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]
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.
First use relational selection to keep the Saab cars, then a natural join to find their sales, and finally a relational join to identify the corresponding salespeople. A relational projection gives
The foreign keys ensure that every sale has the referenced car and salesperson. Several qualifying sales by the same person produce just one occurrence of their name under set semantics. Two distinct people with the same name also yield that name only once: this is the requested relational projection onto names.
Find the salesperson identifiers satisfying each price condition using relational selection and relational projection:
Intersect these identifier sets, relational join the result to the salesperson records, and apply relational projection to names:
If intersection is not a primitive of the chosen relational algebra, use with relational difference. There is no need to access Car: the foreign key already ensures that every sale refers to a car.
Intersecting names instead of identifiers is wrong. Two different salespeople called Alex, one making only sales and the other only sales, would make Alex appear in both name sets although neither person satisfies the condition. Nor can one sale satisfy both price tests: the two distinct prices require distinct sale records.