Computer ScienceExam code: 0984

Database Concepts

Databases

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.

Common exam question

Choosing and justifying a primary key

Question: Name the field that should be the primary key and give a reason for choosing it, or describe what a primary key is for (1–2 marks).

Asked in 7 of the 17 papers. Naming the field earns one mark and the reason the other. Choose the code or number field whose values all differ (a catalogue number, a room code); the reason is that it is a unique identifier, so it uniquely identifies each record. That is also the answer when you are asked what a primary key is for.

Two versions turn it round. Asked why a named field would not do, say its values repeat, so the data is not unique. Asked whether any given field would suit, the answer can be no: if every field could hold the same value in two records (a name, a supplier, a size), none is unique, and the mark needs the "no" with that reason.

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.

Common exam question

Counting the fields and records in a table

Question: State how many fields and how many records there are in the database table shown (1–2 marks).

Asked in 4 of the 17 papers, one mark for each number. Fields are the columns and records are the rows of data; the heading row only holds the field names, so it is not a record. The tables printed are long (two had 18 and 19 rows), so tick each row off as you count it.

Never read the number of records off the last ID code. The codes in these tables skip values: one table's codes ended at 37 but it held only 18 records. Every column counts as a field, including a run of similar ones such as a separate T/F column for each day of the week. One more paper tests the words themselves in a gap-fill: rows are records and columns are fields.

Quick check

4 short questions on this note. See what has stuck, and where to look if something hasn't.

About 2 minutes

Build on this topic