IB Computer Science Topic A3.3: Database Programming Key Definitions

The four guarantees a database makes about every transaction: atomicity, consistency, isolation and durability. Together they ensure that grouped operations are processed reliably and leave the data in a valid state.

A SQL function that reads the values of a column across many rows and returns one summary value. The five named in the syllabus are AVERAGE (written AVG in most systems), COUNT, MAX, MIN and SUM.

The subset of SQL that defines and changes the structure of a database, using commands such as CREATE, ALTER, DROP, TRUNCATE and RENAME. DDL acts on database objects rather than on the rows stored inside them.

The subset of SQL that works with the rows held in a structure that already exists, using SELECT, INSERT, UPDATE and DELETE. DML cannot change the definition of a table.

A named SELECT statement stored in the database and queried like a table, returning data drawn from one or more base tables.

A join combines rows from two tables into one result by matching values in a column they share, normally a foreign key in one table and the primary key it refers to in the other.

A view whose result rows are stored physically in the database and refreshed periodically, so reads are fast but the data is only as current as the last refresh. Also called a snapshot view.

A marker stored in a column to record that the value is unknown or missing. It is not zero and not an empty string, and every aggregate function except COUNT(*) skips it rather than treating it as a value.

A sequence of database operations that the database treats as one indivisible unit of work, so that either every operation in it takes effect or none of them does.