Database components

Learning intentions

You should be able to understand the key database components, including the table component, the field component, the record component, and the key component.

You should be able to explain the purpose of primary keys as unique identifiers for records.

You should be able to describe how data types and validation improve accuracy.

You should be able to recognise common database errors and how they affect integrity.

You should be able to use correct terminology when discussing database systems.

What is a table

A table stores related data about one topic, such as students or products. It organises data into rows and columns so it can be read and updated consistently by different people.

A good table keeps one kind of information in one place instead of scattering it across multiple documents. This makes it easier to keep data accurate over time, especially when records change.

Tables should be named clearly so users can predict what they contain before opening them.

Fields

A field is a category of data stored in a table, such as surname or date of birth. It appears as a column and describes one attribute of each record.

Fields work best when they store one kind of value only, rather than mixing different information together. Clear field names help users enter data correctly and understand it later when the database is being used for reports.

Most fields also have rules that control what can be entered, which reduces accidental mistakes.

Records

A record is one complete set of information about a single item. In a table, a record appears as a row containing a value in each field that applies to that item.

A record should be complete enough to be useful on its own, without requiring users to guess missing details. When records are consistent, it becomes easier to search, sort, and summarise information accurately across the whole table.

Primary keys

A primary key is a field used to uniquely identify each record in a table. Its value is unique for every record, even when other information might be the same, such as two students with the same surname.

A primary key value cannot be blank because every record must be identifiable. In many databases, the primary key is a number that increases automatically, but it can also be a carefully chosen code.

Why primary keys matter

Primary keys prevent confusion by making it clear which record is being referred to. This matters when records look similar, or when users are searching quickly.

Primary keys also support accurate updates because the database can target one record precisely. Without a reliable identifier, changes may be applied to the wrong record, especially when names repeat.

In a relational database, primary keys are also used to link data across tables.

Data types

A data type defines what kind of value a field can store. Common examples include text, number, date, and yes/no depending on the database software.

Choosing the correct data type helps prevent incorrect entries, such as letters in a number field. It also helps the database sort and calculate correctly, because it knows how the data should behave.

Validation rules

Validation rules restrict what can be entered into a field. They reduce errors by catching problems at the point of entry, when mistakes are easiest to fix.

A rule might require a value to be present, limit a number to a sensible range, or enforce a consistent format. Good validation improves the quality of the whole database because it prevents bad data from spreading into queries and reports.

Data integrity

Data integrity means the data is accurate, consistent, and trustworthy. It matters because database outputs are only as reliable as the data that was entered.

Integrity is supported by primary keys, validation rules, and consistent formats. When tables are linked, integrity also depends on links staying correct over time, even as records are added or removed.

Common database errors

Duplicate records occur when the same item is entered more than once, often with small differences. Missing data occurs when required fields are left blank, which can make records unusable.

Incorrect data types cause problems when sorting, filtering, or calculating. Inconsistent formats reduce usability, such as storing dates in multiple different styles.

Poor naming makes a database harder to understand and increases mistakes during use, especially for new users.