DataSys+ key terms

A database that stores data in tables made of rows and columns and uses relationships between tables. It is queried with languages such as SQL and follows the relational model.

A column or set of columns whose values uniquely identify each row in a database table. It cannot contain duplicate or null values.

A column or set of columns in one table that refers to the primary key of another table. It enforces referential integrity between related records.

Structured Query Language, a standard language for defining, querying, updating, and managing data in relational databases. It includes statements for creating tables and retrieving rows.

The process of organizing relational database tables to reduce duplicated data and avoid update anomalies. It typically involves dividing data into related tables and defining keys.

A data structure that speeds up searches on a database table by providing quick lookup paths to rows. It uses extra storage and must be updated when data changes.

Data that describes other data, such as a file's author, creation date, format, or size. In databases, it describes table structures, constraints, and other schema information.

The basic storage structure in a relational database: a named set of rows, each row having the same set of named columns with a defined data type per column. Also called a relation or entity.

A single record in a table, holding one value for each column. Also called a tuple or record. Rows are unordered in the relational model; only an ORDER BY clause gives a result a defined sequence.

A named attribute of a table that holds one value per row, with a declared data type and optional constraints. Also called a field or attribute. All values in a column share the same type.

Any minimal set of columns that could serve as the primary key because it uniquely identifies every row. One candidate key is chosen as the primary key; the others are usually enforced as unique constraints.

The rule that every non-null foreign key value must correspond to an existing key value in the referenced table. The database rejects inserts, updates and deletes that would break this rule.

A rule that no two rows may hold the same value in the specified column or column combination. Unlike a primary key, a unique constraint usually permits null values and a table may have several of them.

A column rule that forbids the absence of a value, so every row must supply a value for that column. It is a column-level check and is implied automatically by a primary key.

A declared predicate that rejects rows for which the condition evaluates FALSE. UNKNOWN, including many NULL-related results, can pass, so required presence needs a separate NOT NULL constraint.

A value the database supplies automatically when an INSERT statement omits the column. It applies only when no value is given; explicitly inserting NULL stores NULL rather than the default.

A marker meaning that a value is unknown or not applicable, distinct from zero or an empty string. Comparisons with NULL yield unknown, so tests must use IS NULL or IS NOT NULL.

The rule that SQL conditions evaluate to true, false or unknown, with unknown arising whenever NULL takes part in a comparison or arithmetic expression. A WHERE clause keeps only rows whose condition is true.

A function returning the first of its arguments that is not null, or null if all are null. Commonly used to substitute a default for missing values in query output.

The declared category of values a column may hold, such as integer, decimal, character string, date or Boolean. The type determines allowed operations, storage size and which implicit conversions the database permits.

CHAR stores fixed-length strings padded with spaces to the declared length, while VARCHAR stores variable-length strings up to a maximum. CHAR suits uniform codes; VARCHAR suits names and addresses of varying length.

An operator or function that converts a value from one data type to another, for example turning a string into a number or a date. It may be explicit in the statement or applied implicitly by the database.

Automatic type conversion the database performs when operands of different types meet in an expression or comparison. It can silently change results or prevent an index from being used, so explicit conversion is safer.

The DDL statement that defines a new table by naming it and listing its columns with data types and constraints. Variants can also copy structure from an existing table or create the table only if absent.

The required consistent set of backup bases, changes, logs, metadata and key access needed to reach the chosen recovery point under the actual DBMS.

An observation about where query execution spends time, useful for distinguishing locking, I/O, CPU and other hypotheses under representative workload.

The principle that users and systems should be granted only the minimum access rights necessary to perform their tasks, reducing the potential damage from accidents or malicious activity.

A control that divides critical tasks among different individuals so that no single person can complete a sensitive action alone, helping to prevent fraud and errors.

An access control model where permissions are assigned to roles rather than individual users, and users are assigned to roles based on their job responsibilities.

An authentication method that requires two or more independent credentials from different categories (something you know, have, or are) to verify a user's identity.