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]
Dependency-preserving decomposition Created 2026-10-31 Updated 2026-11-05
A decomposition in which the database integrity constraints can be enforced using constraints on individual component database relations, without computing their relational join.
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.
Use an entity-relationship model with Customer, Conference, Event, Venue and Building. The following entity-relationship diagram uses minimum–maximum cardinalities at each end: the label beside an entity counts how many instances of that entity can be associated with one instance at the opposite end. A double link into the specialization circle means total specialization; its d means disjoint.
Every conference belongs to exactly one customer and contains at least one event. A customer may book zero or more conferences. Every event belongs to exactly one conference and occurs at exactly one venue; a venue may host zero or more events. The event identifier is local to its conference, so its full candidate key is (conferenceid, eventNo). Choosing an event number is an explicit additional identifier assumption; a globally unique event identifier would be another valid design.
Every conference is exactly one of the two entity subtypes, SingleDay and MultiDay. Both inherit the conference identifier and the customer and event relationships. For SingleDay, store heldDate, and enforce
These cross-event database integrity constraints accompany the diagram; simple endpoint cardinalities alone cannot express them. Since there is at least one event, their common venue determines the conference's single room.
For this design, a venue is a room with the given identifier, name and capacity, and each room belongs to exactly one building. A building may contain zero or more rooms. Introducing Building and its identifier is an explicit modeling choice that makes the single-building requirement enforceable: one common room implies one common building. If an application also used non-room venues, it could instead introduce a room entity subtype and restrict SingleDay events to it.
The start and end values are full timestamps, not times of day alone, and each event has . MultiDay events may use different dates and rooms; the design does not force every pair of events to use different rooms or dates. It imposes no uniqueness of customer names, no venue-exclusivity rule, and no attendance–capacity rule because no attendance data are supplied. Such extra scheduling restrictions could be added as further database integrity constraints without altering the basic entity-relationship model.
The PostgreSQL Global Development Group is the collective project attribution used for the development and documentation of PostgreSQL, an open-source database management system. Its documentation specifies concrete behavior for database transactions, database integrity constraints, database query operations and storage mechanisms; these specifications are more precise than inferring behavior from SQL syntax alone.[ref]
For example, PostgreSQL documents transaction isolation levels in terms of visible snapshots and permitted anomalies. The project name here identifies the responsible development community, rather than one individual author or a separate database product.
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]