GoalDescribe a database table in fields and records, and explain why each record needs a unique primary key.
New words
field · one column of a database table, with a heading that says what kind of data it holdsrecord · one row of a table: all the facts about one person or thingprimary key · a field whose value is different in every record, so that each record can be found exactlyautonumber · a data type where the computer gives each new record the next number by itself
Explanation
A database stores facts in a table. Each column is a field and has a field heading, such as Surname. Each row is a record, which holds all the facts about one person or thing. Sometimes two records look almost the same. Two different members of a club can have the same name, age and group. The computer needs a way to tell them apart, so we add a primary key. This is a field whose value is different in every record. Many databases use the autonumber data type, so the computer gives each new record the next number by itself.
MemberID
Surname
First name
Age
Group
1
Chen
Mia
11
Blue
2
Okafor
Ben
12
Red
3
Silva
Ana
11
Blue
4
Chen
Mia
11
Blue
5
Rahimov
Omar
12
Red
6
Tanaka
Yuki
10
Green
Worked examples
Surname
First name
Age
Group
Chen
Mia
11
Blue
Okafor
Ben
12
Red
Silva
Ana
11
Blue
Chen
Mia
11
Blue
Rahimov
Omar
12
Red
Tanaka
Yuki
10
Green
This chess-club table has no primary key. Records 1 and 4 are exactly the same. Are they one member entered twice, or two different members with the same name? Nobody can tell, so a teacher might change the wrong record.
Field
Has repeated values?
Can be the primary key?
Surname
yes: Chen twice
no
First name
yes: Mia twice
no
Age
yes: 11 three times
no
Group
yes: Blue three times
no
MemberID
no: 1, 2, 3, 4, 5, 6
yes
A good primary key must be different in every record. Looking at the table at the top of this lesson, Surname repeats (Chen), First name repeats (Mia), Age repeats and Group repeats. Only MemberID is different every time, so MemberID is the primary key.
What happens
Number the computer gives
Six records already exist
1 to 6
A new member joins
7
Record 4 is deleted
–
Another new member joins
8, not 4
With autonumber the computer counts up for you, and it never gives the same number twice. When record 4 is deleted, its number 4 is not used again. The next new member gets the next number after the highest one used so far.
Class activity
Lost-property records (no computer). Make eight cards for a pretend lost-property box, with Item, Colour and Where found written on each (for example ‘scarf, red, library’). Make sure that at least two cards look exactly the same. Shuffle and ask a partner to find ‘the red scarf’: is it easy to be sure which card is meant? Now number the cards 1 to 8 in the corner. These numbers are the primary key. Ask your partner to find card 5. Then ‘delete’ card 3, add a new card and give it the next number. Notice that the number 3 is never reused.
Practice
1
Why can none of the fields of this pet table be the primary key? Which field would you add to solve the problem?
Name
Animal
Age
Coco
cat
2
Rex
dog
3
Coco
dog
3
Rex
cat
2
Each value in every field appears twice (Coco twice, Rex twice, cat twice, dog twice, 2 twice, 3 twice), so no field is different in every record. Add a field PetID with the autonumber data type and make it the primary key.
2
Which field is the best primary key for the bus-pass table, and why are the other fields not suitable?
PassNo
Name
Route
101
Dana
5
102
Kai
5
103
Dana
7
104
Lena
3
PassNo. Every pass has its own number. Name is not suitable because Dana appears twice, and Route is not suitable because route 5 appears twice.
3
A table uses autonumber and holds records 1 to 5. Record 5 is deleted. Then two new records are added. What numbers do they get? Explain.
They get 6 and 7. A number that has been given out is never used again, even if its record is deleted, so the number 5 is not given to a new record.
4
Count the fields and the records in this table, and name the field headings.
Code
Plant
Height
Planted
P1
Bean
30
May
P2
Pea
45
April
P3
Bean
12
June
P4
Mint
20
May
P5
Pea
8
June
There are 4 fields (Code, Plant, Height, Planted) and 5 records. The field headings are the words in the top row.