Glossary
1
- 1-to-1 relation
- A relationship between two tables in which each record from the first table matches exactly one record from the second and vice versa.
- 1-to-many relation
- A relationship between two tables in which each record from the first table matches zero or more records from the second, but each record from the second table matches exactly one record from the first.
A
- administration command
- A command for managing a database that isn't part of the SQL standard. Each RDBMS has its own idiosyncratic admin commands.
- aggregation
- Combining several values to produce one.
- aggregation function
- A function used to produce one value from many, such as maximum or addition.
- alias
- An alternate name used temporarily for a table or column.
- atomic
- An operation that cannot be broken into smaller operations.
- autoincrement
- Automatically add one to a value.
B
- B-tree
- A self-balancing tree data structure that allows search, insertion, and deletion in logarithmic time.
- base case
- A starting point for recursion that does not depend on previous recursive calculations.
Binary Large Object (blob) Bytes that are handled as-is rather than being interpreted as numbers, text, or other data types.
C
- client-server database
- A database that is managed by its own server process that clients interact with through network connections. The term is used in contrast to local database.
- cross join
- A join that creates the cross-product of rows from two tables.
common table expression (CTE) A temporary table created at the start of a query, usually to simplify writing the query.
- consistent
- A state in which all constraints are satisfied, e.g., all columns contain allowed values and all foreign keys refer to primary keys.
- A subquery that depends on a value or values from the enclosing query, and which must therefore be executed once for each of those values.
comma-separated values (CSV) A text format for tabular data that uses commas to separate columns.
- cursor
- A reference to the current location in the results of an ongoing query.
D
- data migration
- To move data from one form to another, e.g., from one set of tables to a new set or from one DBMS to another.
- database
- A collection of data that can be searched and retrieved.
database management system (DBMS) A program that manages a particular kind of database.
- denormalization
- To deliberately introduce duplication or other violate normal forms, typically to improve query performance.
- durable
- Guaranteed to survive shutdown and restart.
E
- entity-relationship diagram
- A graphical depiction of the relationships between tables in a database.
- exclusive or
- A Boolean operation that is true if either but not both of its conditions are true. SQL does not provide an exclusive or operator, but the same result can be achieved using operators it has.
- expression
- A part of a program that produces a value, such as
1+2.
F
- filter
- To select records based on whether they pass some Boolean test.
- foreign key
- A value in one table that identifies a primary key in another table.
- full outer join
- A join that produces the union of a left outer join and a right outer join.
G
- group
- A set of records that share a common property, such as having the same value in a particular column.
H
I
- in-memory database
- A database that is stored in memory rather than on disk.
- inclusive or
- A Boolean operator that is true if either or both of its conditions are true.
SQL's
oris inclusive. - index
- An auxiliary data structure that enables faster access to records.
- infinite recursion
- See "infinite recursion".
- isolated
- The appearance of having executed in an otherwise-idle system.
J
- join
- To combine records from two tables.
- join condition
- The criteria used to decide which rows from each table in a join are combined.
- join table
- A table that exists solely to enable information from two tables to be connected.
JavaScript Object Notation (JSON) A text format for representing numbers, strings, lists, and key-value maps.
K
L
- left outer join
- A join that is guaranteed to keep all rows from the first (left) table. Columns from the right table are filled with actual values if available or with null otherwise.
- local database
- A database that is stored on the same computer as the application using it and accessed directly through function calls. The term is used in contrast to client-server database.
M
- many-to-many relation
- A relationship between two tables in which each record from the first table may match zero or more records from the second and vice versa.
- materialized view
- A view that is stored on disk and updated on demand.
N
- normal form
- One of several (loosely defined) rules for organizing data in tables.
- NoSQL database
- Any database that doesn't use the relational model.
- null
- A special value representing "not known".
O
object-relational mapper (ORM) A library that translates objects in a program into database queries and the results of those queries back into objects.
P
- path expression
- An expression identifying an element or a set of elements in a JSON structure.
- primary key
- A value or values in a database table that uniquely identifies each record in that table.
- privilege
- The ability to take an action such as querying a table or deleting records.
Q
- query
- A command to perform some operation in a database (typically data retrieval).
R
- recursive CTE
- A common table expression that refers to itself. Every recursive CTE must have a base case and a recursive case.
- recursive case
- The second or subsequent step in self-referential accumulation of data.
relational database management system (RDBMS) A database management system that stores data in tables with columns and rows.
- right outer join
- A join that is guaranteed to keep all rows from the second (right) table. Columns from the left table are filled with actual values if available or with null otherwise. SQLite does not implement right outer join since its behavior can be reproduced by swapping the order of the tables and using a left outer join.
- role
- A collection of privileges in a database or other system that defines the set of operations a class of users can perform.
S
- statement
- A part of a program that doesn't produce a value.
- subquery
- A query used within another query.
T
- table
- A collection of related data in a database stored in columns and rows.
- table-valued function
- A function that returns multiple values rather than a single value.
- temporary table
- A table that is explicitly constructed in memory outside any particular query.
- ternary logic
- A logic based on three values: true, false, and "don't know" (represented as null).
- tombstone
- A marker value added to a record to show that it is no longer active. Tombstones are used as an alternative to deleting data.
- trigger
- An action that runs automatically when something happens in a database, typically insertion or deletion.
U
- upsert
- To update a record if it exists or insert (create) a new record if it doesn't.
Uniform Resource Identifier (URI) A string that identifies a resource (such as a web page or database) and the protocol used to access it.
V
- vectorization
- Performing the same operation on a stream of values rather than using a loop to operate on one value at a time.
- view
- A rearrangement of data in a database that is regenerated on demand.
W
- window function
- A function that combines data from adjacent rows in a database query's result.