ICDL Databases cheat sheet

A database is an organised collection of related data stored so it can be retrieved, managed, and updated efficiently.

A DBMS is the software (e.g. Access, MySQL) that sits between users and the stored data and enforces structure and access rules.

A table stores data about one entity type; each row (record) is one instance, each column (field) is one attribute.

A primary key uniquely identifies each record and cannot be null or duplicated; a composite key uses two or more fields together.

A foreign key is a field in one table that references the primary key of another table, creating a link between them.

Data types constrain stored values: text/short text, number, currency, date/time, yes-no (boolean), and auto-number (autonumber).

A flat-file database holds all data in one table; a relational database splits data across linked tables to reduce duplication.

Data redundancy is storing the same fact more than once; normalisation reduces it and prevents update, insert, and delete anomalies.

Field properties such as required, default value, validation rule, and input mask control what data can be entered.

Design view defines field names, data types, and properties; Datasheet view is used to enter and edit actual records.

Set the primary key before creating relationships; a table without a primary key cannot be the 'one' side of a relationship.

One-to-many: one record in Table A relates to many in Table B; the foreign key lives on the 'many' side.

Many-to-many requires a junction (linking) table holding foreign keys to both parent tables, usually with a composite primary key.

Referential integrity blocks orphan records: a foreign key value must match an existing primary key value, or be null if allowed.

Cascade Update and Cascade Delete propagate primary key changes or deletions to related child records when integrity is enforced.

A validation rule is a logical expression (e.g. >=0 And <=100) checked on entry; validation text is the message shown when it fails.

An input mask controls the format of entered characters; a lookup field presents a list of values from a table or a fixed list.

Indexing a field speeds searches and sorting on that field but slows inserts and updates because the index must be maintained.

A select query retrieves records matching criteria without changing stored data; action queries (update, delete, append, make-table) do change data.

Criteria on the same row of the design grid are combined with AND; criteria on different rows are combined with OR.

Wildcards in criteria: * matches any number of characters, ? matches one character; use them with the Like operator.

Comparison operators =, <>, <, >, <=, >= and Between ... And ... define numeric and date ranges.

Is Null finds empty fields; Not Is Null finds populated fields; an empty string is not the same as a null value.

Totals queries use aggregate functions: Sum, Avg, Count, Min, Max; Group By groups rows and Where filters before aggregation.

A parameter query prompts the user at run time by placing a bracketed prompt such as [Enter a date] in the criteria row.

A calculated field builds a new column from an expression, e.g. Total: [Quantity]*[UnitPrice], and can be sorted or filtered like any field.

Joins in a query define how tables combine: inner join returns only matching rows; outer join keeps unmatched rows from one side.

A form is a screen interface bound to a table or query, used to view, enter, and edit records one at a time.

Controls are bound (tied to a field), unbound (e.g. a label or button), or calculated (an expression such as =[Price]*[Qty]).

Form Layout view arranges controls visually; Design view adds controls, sets properties, and edits the underlying record source.

A main form with a subform displays one-to-many data, showing the parent record and its related child records together.

A combo box lets users pick from a list and can limit entry to listed values; a list box shows the list permanently expanded.

Tab order and tab stops control the sequence in which controls receive focus as the user presses Tab.

A form header/footer shows information once per form; a detail section repeats for each record.

Command buttons run actions via the wizard: navigate records, open other forms or reports, run queries, or save and delete records.

Form properties such as Record Source, Allow Edits, and Data Entry control which data is shown and whether it can be changed.

A report presents data for printing or export and is bound to a table or query; it does not allow record editing.

Report sections: report header/footer (once), page header/footer (every page), group header/footer (per group), and detail (per record).

Sorting and grouping are set in the report's Group, Sort, and Total pane; grouping adds a header and footer for each group value.

Aggregate totals in a report use Sum, Avg, Count, Min, Max in group or report footers to summarise data.

A calculated control on a report uses an expression such as =[Quantity]*[UnitPrice] to display derived values.

Print Preview shows the final paginated layout; Layout view allows resizing while still displaying live data.

Export formats include PDF (fixed layout), Excel/CSV (data reuse), and Word (further editing); the report's record source determines content.

A report can be created from a table, a query, or the Report Wizard; basing it on a query lets you filter and pre-sort the data.

Keep Together and Can Grow/Can Shrink properties prevent records or sections from splitting awkwardly across pages.