5.1.5 Data: Types and Terminology

A computerised database stores data in one or more organised tables. Because the data is stored in named fields it has a context so it becomes information.

Database software is designed to make it easy and efficient to store, edit, sort and search information.

Database terminology:

• Field - a field is used to store an individual item of data.
  Example: typical fields might be 'surname', 'colour', 'height', 'DOB' etc.
• Key Field - a field that is unique for each record in the database and can therefore be used to identify just that record.
  Example: A field like 'surname' would not make a good key field because two records in a database of people could easily have the same surname.
• Record - a record is a group of related fields.
  Example: In a database of doctor's patients, each patient would be a separate record, with fields for 'surname', 'DOB', 'address', 'allergies' etc.
• File - a file is the group of records that make up the database.

Examples of use:

KLB school has a database with all the pupil details on it.

• The whole database is a file.
• Each pupil is a separate record in the file
• Each record is made up of fields such as 'first name', 'surname', 'DOB', 'tutor group', 'emergency contact number', 'tutor group' etc.
• The key field is the unique 'admin number' given to each pupil when they join the school.

Data Types:

When a database is designed, all the fields are set to accept a particular data type. This helps check for the wrong type of data being entered and makes sure the data is stored as efficiently as possible. It also means it will be sorted correctly.

• Alphanumeric/text fields - a field of this data type will accept both numbers and text.
  • When to use - if you want to enter text or a mixture of text and numbers.
  • When NOT to use - if you want to store only numbers or only dates. You can enter numbers or dates into text fields but they would get sorted as if they were words and could not easily be used for any calculations.
• Numeric fields - a field of this data type is used to store numbers. Number fields are used to store numbers because:
  • the data will be sorted correctly (i.e. the numerical data 2,3,20,30 would be sorted as 2,20,3,30 if it was stored in a text field)
  • validation rules such as range checks can used (i.e. range checks such as BETWEEN 10 and 20)

  There are two types of numeric field you need to know about:

  • Real - used for decimal numbers such as 3.4, 3.1427 and 6.0 etc. Real numbers can be formatted as currency (i.e. £5.67) or to a fixed number of decimal places (i.e. entering 3.1427 into a real number field formatted to two decimal places would mean it appeared as 3.14)
    When to use - if the field is going to be used to store numbers with decimal places such as 'height', 'length' etc.
  • Integer - used for whole numbers.
    When to use - if the field is only going to be used to store whole numbers, i.e. 'number of children', 'car doors' etc.
     
• Logical/Boolean fields - a field of this data type will only let you enter a 'Yes' or a 'No'. This may be as text (YES/NO or TRUE/FALSE) or as a tick/blank.
  When to use - when you only need to store something as 'true' or 'false' or store whether something exists or does not exist.
• Date fields - a field of this type stores days, months and years so that records can be sorted correctly and easily validated using range checks. Date fields can display the date information in different formats such as the full name of the day/month (28th August 1961) or the numerical versions (28/08/61)
  When to use - for any field which will be used to store date information.

Examples of use:

A football club uses a database to store details of its players.

• Data such as Surname and First name is stored in text fields.
• The players car registration is mixture of text and numbers so it is stored in a text field.
• The height of the player (in metres) is a decimal number so it is stored in a real number field.
• The number of goals scored is a whole number so it is stored in an integer number field.
• The player's date of birth (DOB) is a date so it is stored in a date field.
• The players telephone number has a space between the code and the actual number so it is stored in a text field.
• Information such whether the player is right or left footed is stored using a logical/Boolean field. The field is labelled as 'Right footed?' and the entry is either a TICK or left blank.

Examination questions on this topic

You will often get a question about a table from a database. It can be very easy to mix up a question about field names with one about field data types.

For example:

• If you are asked to name a field then look the row at the top which should have all the field names.  In this example, 'Item', 'Stock code' etc. are the field names.
• If you are asked to identify the key field then look for a column of numbers or numbers/text that identifies each item uniquely. In this example the 'stock code' is the key field as each item in stock would be given a unique code to avoid confusing it with any other item.
• If you are asked to describe a suitable data type for a particular field then you must look at the type of data being stored in each column.
  • If the data is text or a mixture of text & numbers (including spaces or brackets etc.) then the data type would be text.
  • If the data is numeric then the data type would be numeric but you also need to say if it should be real or integer. (Remember, use real if the data has decimal places and integer when all the data is whole numbers)
  • If the data is in the form of a date then the data type would be date. It does not matter if the date is stored in a numeric form (i.e. 28-08-61 or 28/8/1961) or written out fully (i.e.28th August 1961).
  • If the data seems to have only two options, particularly 'yes' or 'no' then the data type should be logical/Boolean.

For the example table above, the field names and data types would be as follows: