Computer ScienceExam code: 0984

Database Concepts

Databases

When a table is created, each field is given a that says what kind of value it can hold. Choosing the right type makes the data smaller, faster and safer.

Data typeHoldsExample fields
IntegerWhole numbersAge, year of birth, ID number, quantity in stock
RealNumbers with a fractional partPrice, weight, percentage with decimals
Text / AlphanumericA sequence of charactersName, address, description
CharacterA single characterA grade letter ('A'), a size code ('S', 'M' or 'L')
Date / TimeA specific calendar date or timeDate of birth, date joined, last-login timestamp
BooleanA simple true/false value"Has paid?" "Is active?" "Newsletter subscriber?"

Choosing the right type

For each field, ask three short questions in order:

  1. Does it ever need decimals? Yes → Real. No → keep going.
  2. Is it a count or whole-number ID? Yes → Integer (an ID that contains letters, such as AB123, is text). No → keep going.
  3. Is it just true or false? Yes → Boolean. Is it a date or timestamp? Yes → Date/Time. Is it exactly one character (a code or flag)? Yes → Character. Otherwise → Text / Alphanumeric.

Two common slips to avoid. Phone numbers and postcodes are stored as text, not integers: they may start with a leading zero, contain spaces or letters, and never have arithmetic done on them. Calendar dates are stored as a Date type, not as text: only a true date field can sort chronologically and reject impossible values like 2024-02-30.

Common exam question

Choosing the data type for each field

Question: Complete a table giving the most suitable data type for each field, chosen from a list of types (2 marks).

Asked in 5 of the 17 papers. With four fields, all four right earns both marks and two or three right earns one. Decide from the example data:

  • a code mixing letters and digits, a name or a description: text/alphanumeric
  • only ever one of two values, such as Yes/No or T/F: Boolean (one scheme also took Boolean for a gender field holding M or F)
  • whole numbers: integer, even for money (a weekly rate in whole dollars was marked integer)
  • values with decimal places, such as a price of 12.95: real
  • a date: date/time

One paper turns it round and asks for a different field to fit each type. Another asks for a name and a type for each described item, one mark per pair; write the name as one word, such as DateJoined.

Why the right data type matters

  • Memory efficiency: an integer takes less space than the same number stored as text.
  • Correct comparisons: > and < compare numbers by value but text character by character, so as text "10" comes before "9".
  • Validation: a date field rejects a value like "purple" automatically.
  • Sorting: a properly-typed date field sorts chronologically; the same date as text would sort alphabetically.

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