Data Types & Primary Keys

🎯 Syllabus & Goals 3 min

Cambridge 9.1 · Basic data types Cambridge 9.1 · Primary keys Paper 2 · Algorithms, Programming and Logic

By the end of this lesson you can:

1. Name and describe the six database data types: text/alphanumeric, character, Boolean, integer, real and date/time.
2. Choose, with a reason, a suitable data type for each field in a table.
3. Identify a suitable primary key, or add one, and justify the choice.

Textbook: Chapter 9, §9.1.2–9.1.3 (pp. 342–344)

Recap / Warm-Up 5 min

Last lesson: a table holds records (rows), and each record has the same fields (columns). Validation keeps unreasonable data out.

Quick starter

In the PET table, which two fields contain repeated values that make them useless for telling pets apart?

🧠 Key Concept 14 min

1 · What is a data type?

Every field must be given a data type. The data type decides how a value is stored, how it is displayed, and what can be done with it. An integer field, for example, can be used in calculations.

2 · The six basic data types

3 · Primary keys

We must be able to pick out one record, and only one. So one field must hold a value that is never repeated in the table. That field is the primary key.

• An existing field can be the key if it is always unique — for example a book's ISBN.
• If every existing field could repeat, add a new field, such as a unique ID number.

Why does it matter? Search the PET table by name and you cannot tell which Luna you mean:

SELECT PetID, PetName, OwnerSurname
FROM PET
WHERE PetName = 'Luna';

Two records come back. The primary key tells them apart: P102 and P105. (You will write queries like this from Lesson 3.)

Worked Example 12 min

(a) Choosing a data type for every field

Choose the data type for each PET field and give a reason. This is how a 1-mark-per-field question is answered.

1. PetID → text. It mixes a letter and digits (P101).
2. PetName, Species, OwnerSurname → text. Each holds several letters.
3. DateOfBirth → date/time. It is a date; the software can then check it is a real date and sort it in date order.
4. Sex → character. Always exactly one letter, M or F.
5. WeightKg → real. Weights have decimal parts, such as 12.5.
6. Vaccinated → Boolean. Only two answers are possible: TRUE or FALSE.

(b) Choosing a primary key

1. Test Brand: many models share a brand → repeats → not suitable.A key must never repeat.
2. Test Colour, ScreenSize, Price, InStock: all can repeat → not suitable.Two phones can cost the same or be the same colour.
3. Test ModelCode: one code per model → unique → suitable.It uniquely identifies each record.

Answer: ModelCode (1) because each model has a different code, so it uniquely identifies each record (1).

Try It Yourself 12 min

📝 Exam Practice 10 min

Recap & Key Terms 3 min

Every field has one of six data types. Pick the type from what the data is and how it will be used. One field — the primary key — must be unique for every record; add an ID field if none is.

Data type

A classification of how data is stored and displayed, and which operations can be performed on it.

Text / alphanumeric

A number of characters (letters, digits, symbols).

Character

A single character.

Boolean

One of two values, e.g. TRUE/FALSE.

Integer / real

A whole number / a number with a decimal part.

Primary key

A field that uniquely identifies each record in a table.

Homework 1 min

Task (≤ 15 min): A theatre table, SHOW, stores for each performance: a show number such as SH104, the type of show, its title, the date, the ticket price and whether it is sold out. (a) Give a data type for each field. (b) State, with a reason, the primary key.