Computer Science terms in Databases

The four properties that a DBMS guarantees for every transaction so that transactions are processed reliably: atomicity, consistency, isolation and durability.

An SQL function that takes the values of a column from many rows (a whole table or one group) and returns a single summary value; the standard ones are AVG, COUNT, MAX, MIN and SUM.

A data mining technique that identifies records, events or observations that differ significantly from the normal pattern of the data, such as fraudulent transactions or faulty sensor readings.

Data stored so that new records are only ever added to the end of the existing data, and existing records are never modified or removed.

A value that cannot be split into smaller parts that the database needs to use separately, so that each field of each record holds exactly one such value.

The property that a transaction is all or nothing: either every one of its changes is applied, or, if any part fails, none of them is.

A consistency model used by many distributed NoSQL databases that puts availability before immediate consistency. Its letters stand for basic availability (every request gets an answer, even when some copies cannot be reached), soft state (copies may change without new input while updates spread between them) and eventual consistency.

Data sets so large, so fast-growing or so varied in form that they cannot be stored and processed efficiently with traditional single-server database tools.

A data type that stores a large block of raw binary data whose contents the DBMS does not interpret.

The technologies and processes that collect, analyse and present an organisation's data, through reports, dashboards, OLAP and data mining, to support decision-making.

A field, or a minimal set of fields, whose values are unique for every record in a table, so that it could be chosen as the primary key.

The result that a distributed data store cannot guarantee all three of consistency (every read sees the latest write), availability (every request receives a response) and partition tolerance (the system continues when network messages between nodes are lost); when a network partition occurs, it must give up either consistency or availability.

The maximum number of instances of one entity that can be related to a single instance of another entity in a relationship, recorded as one or many.

The result of combining two tables so that every row of the first is paired with every row of the second, giving (rows in first) × (rows in second) rows.

One part of an SQL statement, starting with a keyword such as SELECT, FROM, WHERE or ORDER BY, that does one job in the statement.

A database hosted and run by a cloud provider and accessed over the internet, often as a managed service in which the provider handles installation, maintenance, backups and scaling.

A temporary name given to a column of the result set, or to a table within one query, using the keyword AS.

The TCL command that ends a transaction successfully, making all its changes permanent and visible to other transactions.

A primary key made of two or more fields, whose combined values are unique for each record even though each field on its own may repeat.

A key whose value is formed by joining the values of two or more fields into a single value.

A high-level description of the data an organisation needs, as entities, their attributes and the relationships between them, independent of any database management system or storage method.

The methods a DBMS uses to manage transactions that run at the same time so that they do not interfere with each other and the result is the same as if they had run one after another.

The property that a transaction takes the database from one valid state to another, so that every constraint and rule holds after the transaction commits.

A way of drawing ERDs in which entities are boxes, relationships are lines between them, and a pair of symbols at each end of a line shows the cardinality and modality at that end.

The state in which every copy, view and use of a piece of data shows the same value, so that no two parts of the database contradict each other.

The part of SQL whose statements create, change and remove the structure of a database, such as tables, views and indexes, rather than the data in them.

A table that documents every field in a database, giving its name, data type, size or format, key status, whether it may be left empty, its validation rules and a description of what it holds.

The same value or set of values stored more than once, in the same table or in different tables or files.

The ability to change the schema at one level without having to change the schema, or the programs that use it, at the next level up.

The accuracy, validity and reliability of data over its whole life, maintained by rules that the data must obey whenever it is entered or changed.

The part of SQL whose statements read, add, change and remove the data stored in the tables, leaving the structure unchanged.

A smaller data warehouse, or a subset of one, focused on a single subject or department, such as sales, finance or marketing.

The automatic discovery of useful, previously unknown patterns, relationships and anomalies in large data sets, using statistical and machine learning techniques.

Dividing a database's data into separate parts stored on different nodes, either by rows (horizontal partitioning, also called sharding) or by columns (vertical partitioning).

Data that is unnecessary because the same information is already stored elsewhere in the database or can be derived from other stored data.

A central database that stores integrated, historical data copied from many operational systems, organised by subject and optimised for analysis and reporting rather than for day-to-day transactions.

An organised, persistent collection of related data, stored so that it can be searched, updated and shared by many users and programs.

A separate data structure, kept by the DBMS, that stores the values of one or more columns in sorted order with a pointer to each matching row, so that rows can be found without reading the whole table.

The software that creates and maintains a database and controls every access to it, handling storage, retrieval, integrity rules, concurrent use, security and recovery.

A formal description of the structure of a database: its tables, the fields in each table, their data types, the keys and relationships that link the tables, and the constraints the data must obey.

Data types that store calendar dates (DATE), times of day (TIME) or both together (DATETIME or TIMESTAMP) in a standard form that the DBMS can validate, compare, sort and use in date arithmetic.

A programming language in which the programmer states what result is wanted, and the system works out how to produce it.

A fault in a poorly designed table where deleting a record unintentionally removes the only copy of another fact.

The deliberate introduction of redundancy into a normalised database, by combining tables or storing copies or precomputed values, so that data can be read faster or with simpler queries.

A value that can be calculated from other data already stored in the database.

A table in a star schema that describes one aspect of the facts, such as date, product, store or customer, with descriptive attributes used to filter and group the facts.

A database whose data is stored across several computers (nodes) at one or more sites, connected by a network, but which appears to its users as a single database.

The property of a distributed database that hides from users and applications the fact that data is spread across several sites, so they work with it as if it were a single, local database.

A NoSQL database that stores each record as a self-describing document, such as a JSON document, whose fields can be queried and indexed and can differ from one document to the next.

The rule that every value in a field belongs to the field's allowed set of values (its domain), defined by its data type, length, format and range.

An OLAP operation that moves from summarised data to more detailed data along a dimension's hierarchy, such as from year to quarter to month; the reverse operation, which summarises, is called roll-up.

The property that once a transaction has been committed, its changes are permanent and survive a later crash or power failure.

A person, object, place, event or concept about which data is stored.

The rule that every record has a primary key value that is not null and is unique within its table, so that every record can be identified.

A diagram that models the data of a system as entities, the attributes of each entity and the relationships between entities, including how many instances of each entity may take part in each relationship.

A guarantee that if no new updates are made to a data item, all copies of it will become identical after some time, although reads in the meantime may return an older value.

The process that copies data from source systems (extract), cleans and converts it into one consistent format (transform), and adds it to the data warehouse (load).

The central table of a star schema, holding one row per business event, such as a sale, with numeric measures (quantity, revenue) and a foreign key to each dimension table.

One column of a table, holding a single property of the entity, with the same data type in every record.

The state of a table in which every field holds a single atomic value, there are no repeating groups, and every record is uniquely identified by a primary key.

A text data type, written CHAR(n), that stores exactly n characters in every record, padding shorter values with spaces.

A numeric data type, written DECIMAL(p, s), that stores numbers exactly using p digits in total, s of them after the decimal point.

A database that stores all its data in a single table or file, with no links to other tables.

A design in which records in the same collection can have different fields and structures, so new attributes can be added without changing a table definition.

A field in one table whose values must match values of the primary key in another table (or the same table), creating a link between the records.

A relationship between fields in which each value of one field, or set of fields (the determinant), is always associated with exactly one value of another field, written determinant → dependent field.

A system that captures, stores, analyses and displays data linked to locations on the Earth's surface, usually as layers on a map.

The process by which a distributed database splits a query into sub-queries for the sites that hold the relevant data, runs them, and combines the partial results into one answer, choosing a plan that keeps network transfer low.

A NoSQL database that stores data as nodes (entities) connected by edges (relationships), both of which can have properties, and answers queries by following the edges.

The clause that collects the rows of a result with equal values in the named columns into groups, so that the query returns one row for each group.

The clause that filters groups made by GROUP BY, keeping only the groups for which its condition is true.

Data organised as a tree, in which each item has one parent (except the item at the top) and may have any number of children, to any depth.

Increasing a system's capacity by adding more servers (nodes) and spreading the data and the workload across them.

A programming language in which the programmer writes the sequence of steps, such as assignments, loops and selections, that the computer carries out in order.

A database that keeps its data in main memory (RAM) rather than on disk, giving very low latency for reads and writes.

The process of recreating an index from the current data, or reorganising its pages, so that it is compact and in order again after many changes to the table.

A fault in a poorly designed table where a fact cannot be recorded without also recording an unrelated fact, because part of the primary key would otherwise be empty.

Data combined from several sources and converted into consistent formats, names, codes and units, so that the same fact is represented the same way whatever its source.

A rule stored in the database schema that the DBMS checks on every insert, update and delete, rejecting any change that would break it.

The property that concurrent transactions do not see each other's unfinished changes, so the result is the same as if the transactions had run one after another.

An operation that combines rows from two tables into one result set by pairing each row of one table with the rows of the other that satisfy a join condition, usually that a foreign key equals the primary key it refers to.

A NoSQL database that stores each item as a unique key paired with a value, and retrieves a value only by giving its key.

A table that resolves a many-to-many relationship between two tables by holding their primary keys as foreign keys, usually combined to form its own composite primary key.

The property of a distributed database that lets users query data without knowing or stating at which site or node it is stored.

A marker that a transaction places on a piece of data (a record, a page or a whole table) so that other transactions cannot change it, or sometimes even read it, until the lock is released.

A description of the data as relational tables, giving every field with a generic data type, the primary and foreign keys and the integrity constraints, without detail of how a particular DBMS stores the data.

A view whose result rows are computed once and physically stored, then refreshed periodically or on demand, so reading it is fast but its data may be out of date (stale) between refreshes.

The minimum number of instances of one entity that must be related to a single instance of another entity in a relationship, recorded as zero (the relationship is optional) or one (it is mandatory).

A situation in which one field determines a set of values in each of two or more other fields that are independent of each other, so that storing them in one table forces every combination of the values to be recorded.

Data in a data warehouse that is not changed or deleted once loaded; users only read it, and new data is added in scheduled loads.

The process of organising the fields of a relational database into tables so that each fact is stored once, by removing repeating groups and unwanted dependencies according to a series of rules called normal forms.

A database that stores data in a non-relational model, such as key–value pairs, documents, wide rows or graphs, usually with a flexible schema and designed to scale across many servers.

The set of difficulties that arise when data held as objects in an object-oriented program must be stored in, and rebuilt from, the flat tables of a relational database.

A software library that converts automatically between objects in a program and rows in relational tables, generating the SQL needed to save and load objects.

A multidimensional view of data in which each axis is a dimension, such as time, product or region, and each cell holds numeric measures, such as sales, for one combination of dimension values.

The interactive analysis of large volumes of historical data from several points of view (dimensions), using complex, read-only queries that summarise data, to support decision-making.

The processing of large numbers of short, concurrent transactions that insert, update or read a few records each, as in the operational systems that run an organisation's daily activity.

The clause that sorts the rows of a result set by one or more columns, each in ascending (ASC, the default) or descending (DESC) order.

A functional dependency in which a non-key field depends on only part of a composite primary key rather than on the whole key.

A description of how a particular DBMS stores the data: the files and their organisation, the indexes, the exact data types and sizes, and where data is placed on storage devices.

A field, or a set of fields, whose value uniquely identifies each record in a table; it can never be empty (null) and no two records may share it.

A request to a database for data that meets stated conditions, written in SQL as a SELECT statement.

A pattern of use in which data is read far more often than it is changed; the opposite, a write-intensive workload, is dominated by frequent inserts and updates.

One row of a table, holding all the stored data about a single instance of the entity.

The rule that every foreign key value must match an existing primary key value in the table it refers to (or be null where that is allowed), so that no link points to a record that does not exist.

A database that organises data into tables of rows and columns, where tables are linked to each other through shared key values.

An operator that compares two values and gives true or false, such as =, <>, <, >, <= and >=.

A link between two tables, made by a foreign key, that states how many records in one table can be associated with a record in the other: one-to-one (1:1), one-to-many (1:M) or many-to-many (M:N).

A field or set of fields that occurs more than once for a single record, either as several values packed into one field or as a set of numbered columns.

Storing copies (replicas) of the same data on more than one node, so that the data stays available if a node fails and reads can be served from the nearest copy.

The table of rows and columns that a query returns.

A database structure that is fixed in advance, so that every record in a table must have exactly the defined fields and types, and any change to the structure must be made to the whole table.

The TCL command that cancels a transaction, undoing every change it made since BEGIN TRANSACTION, so that the database returns to its state before the transaction started.

The state of a table that is in 1NF and in which every non-key field depends on the whole primary key, so that there are no partial dependencies.

Data that does not follow a fixed table structure but contains tags or labels that name each value and show how values are nested, as in JSON or XML documents.

A data mining technique that finds events or items that frequently occur in a particular order over time, usually within a set time window, across many sequences such as each customer's purchase history.

OLAP operations that select part of a cube: a slice fixes one dimension at a single value, giving a two-dimensional layer, and a dice selects a range of values on two or more dimensions, giving a smaller sub-cube.

A database that stores geometric and geographic data, such as points, lines and polygons, and supports queries about location, distance, containment and overlap.

An index on geometric data that groups nearby objects into nested bounding rectangles, such as an R-tree, so that a location query only examines objects whose rectangles could match.

A data warehouse design with one central fact table linked by foreign keys to several denormalised dimension tables, so that any query needs only one join from the fact table to each dimension.

The standard language for defining, querying and changing the data in a relational database.

Data organised around the main subjects of an organisation, such as customers, products and sales, rather than around the applications that created it.

An artificial primary key with no meaning outside the database, usually a whole number generated automatically by the DBMS for each new record.

A set of data about one entity, arranged in rows and columns, in which every row has the same fields.

The state of a table that is in 2NF and in which no non-key field depends on another non-key field, so that there are no transitive dependencies.

Data that is recorded with the time period it refers to and kept over many years, so that changes and trends over time can be analysed.

A sequence of database operations that is treated as one indivisible unit of work, so that either all of its changes are made or none of them are.

The part of SQL whose statements start and end transactions: BEGIN TRANSACTION starts one, COMMIT makes its changes permanent, and ROLLBACK cancels it.

A file on non-volatile storage to which the DBMS writes a record of every change, and every commit, before applying them to the database, so that after a failure committed transactions can be redone and unfinished ones undone.

A functional dependency in which a non-key field depends on another non-key field, and so depends on the primary key only indirectly.

A protocol that makes a transaction spanning several sites atomic: in the first phase a coordinator asks every site to prepare and vote, and in the second it tells all sites to commit only if every vote was yes, otherwise to roll back.

The state of data that has not been normalised, in which a table may contain repeating groups, fields holding several values, or no primary key.

Data with no predefined model of fields and types, whose meaning cannot be split into the columns of a table.

A view through which rows can be inserted, updated or deleted, with the changes passed on to its base table; a view that does not allow this is read-only.

A fault in a poorly designed table where one fact is stored in several records, so changing it means updating every copy, and missing one leaves the data inconsistent.

A text data type, written VARCHAR(n), that stores up to n characters, using only as much space as each value needs plus a small marker recording its length.

Increasing a system's capacity by adding resources, such as processor cores, memory or storage, to the existing server.

A named query stored in the database whose result can be used in other queries as if it were a table.

A view that stores only its defining query; each time the view is used, the DBMS runs the query against the base tables, so the result is always up to date.

The clause of an SQL statement that filters rows, keeping only those for which its condition is true.

A NoSQL database that stores data in rows identified by a row key, in which each row can have a different and very large set of named columns, grouped into column families.

A character in a LIKE pattern that stands for other characters; % matches any sequence of zero or more characters.