0984

Database Concepts

Databases · 5 question types

Practise

Exam Frequency Analysis

Past paper frequency (2018 to 2024)

This topic accounts for approximately 4% of your exam marks.

stable
Rare
Stable4%

SQL SELECT queries and database structure (tables, fields, records) appear as 4 to 6 mark questions.

Every single-table database is made of three building blocks: , and (usually) a .

A single relational database table of students with columns ID, NAME, AGE and COURSE; labels show that columns are the fields, rows are the records, and the ID column is the primary key that uniquely identifies each record.
Source: Table 660.webp by GeeksforGeeks

Fields

A field is one specific piece of information about each item; it appears in the table as a single column.

Examples of fields for a customer database: CustomerID, FirstName, LastName, DateOfBirth, PhoneNumber.

Records

A record is the collection of fields that all belong to one specific item; it appears in the table as a single row.

For the customers table, each customer is one record. If there are 200 customers, there are 200 records.

Easy way to remember: fields are columns, records are rows.

Primary keys

A primary key is a single field whose value is unique for every record in the table. It uniquely identifies each record so the database can tell one from another.

Examples:

  • StudentID in a school students table.
  • CarRegistration in a vehicles table.
  • ProductCode in a shop's products table.
  • MemberID in a club's members table.

A primary key cannot be left blank, and no two records can share the same primary-key value. This matters because the database needs an unambiguous way to refer to each record, especially when records are updated or deleted.

Exam tip

Defining a primary key

What comes up: "What is a primary key?" or "State the purpose of a primary key" (one mark).

Write: a primary key is a field that uniquely identifies each record in the table — every record has a different value for that field, and none can be blank.

Watch out: saying only that it is "an important field" or "the first field listed" does not earn the mark. The examiner's credited idea is the uniquely-identifies-a-record concept, so that phrase (or an equivalent) must appear in your answer.

A worked example: a students table

StudentIDFirstNameLastNameMarkSubmittedPercentage
2014501AishaKhanY62
2014502BenCooperN18
2014503ChenWeiY81
2014504DaraO'NeillY73
2014505EmmaFosterY41
2014506FaisalAhmedN55

In this table:

  • The (columns) are StudentID, FirstName, LastName, MarkSubmitted, Percentage. There are 5 fields.
  • Each row is a for one student. There are 6 records.
  • The primary key is StudentID because every student has a unique seven-digit ID.