Fields

Computer Science · Databases

Give every field its job

Pick a field. Then answer Question 1 (does it hold another table's primary key values?). Only if the answer is no, answer Question 2 (is its value different for every record?).

The field → Question 1 → Question 2

On Choose a field from Pupils or Tutors. 6 branches to choose from.

Two linked tables. Pupils: Pupil ID (P01 to P05), Surname (Smith, Patel, Smith, Jones, Patel), Year group (9, 9, 10, 9, 10), Tutor ID (T1, T2, T1, T3, T1). Tutors: Tutor ID (T1, T2, T3), Department (Maths, English, Maths). Each heading is a field, and each field is a whole column.

Predict, then check

Look at the Pupils table above before you commit.

The Pupils table has the headings Pupil ID, Surname, Year group and Tutor ID. One row reads: P01, Smith, 9, T1. Sam points at the word 'Smith' and says, 'That's a field.' Which of these really is a field?

What you need to know

• A field is one attribute stored for every record, so it is a whole column, not a single box in the table.
• Every record has its own value in each field: 'Smith' is one value in the Surname field, and the row about Smith is a record.
• Have a goSam counts a table with 4 fields and 30 records and says, 'That's only 4 bits of data.' Gently correct Sam: how many values are stored?

  120 values: each of the 30 records has a value in each of the 4 fields.

  A field is stored for every record, so Sam counted columns, not the values stored in them.

• A field has a name that identifies it. The name labels the column and is not one of the data values.
• A field also has a data type, which sets what kind of data it holds. It applies to every record.
• Have a goA field is called Year group and the table treats its entries as numbers. Which part of the field says that: its name or its data type?

  Its data type.

  The name only identifies the field; the data type sets what kind of data it holds.

• A primary key is a field whose value is different for every record, so each value identifies exactly one record.
• A foreign key is a field that holds primary key values from another table. That is how the two tables link.
• A foreign key's values can repeat, because many records can link to the same record in the other table.
• Have a goFive pupils all have the same tutor, T2. How many times does T2 appear in the Tutor ID foreign key field of Pupils?

  Five times, once for each pupil's record.

  A foreign key's values can repeat because many records can link to the same record in the other table.

• To find a field's job, ask two questions: does it hold another table's primary key values? If not, is every value different?
• A field that is neither a primary key nor a foreign key is an ordinary field.

The big picture

A field is one attribute stored for every record, shown as a column with a field name and a data type. Two questions then decide a field's job: does it hold another table's primary key values (foreign key), and if not, is its value different for every record (it can be the primary key)? A field that is neither is an ordinary field.

⚠ Watch out

Thinking a foreign key must be unique, like a primary key. Different for every record is the primary key's rule; a foreign key's values repeat whenever several records link to the same record in the other table.

Memory hook

Two questions for every field: someone else's key? Different every time?

Check yourself

In a Cars table, Owner ID holds primary key values from an Owners table, and cars share owners. What kind of field is it, and why?