AdvertisementYour brand could be hereYour brand hereOn every Darslab page · students, parents and teachersinfo@darslab.comAsk for pricesPricesinfo@darslab.com
☰ Year plan · Computing

Keys, types and forms

Week 18 · Unit 7.4: The power of data · 3 lessons
1

Records with a unique key

Computing 7: Unit 7.4, pp. 112–149
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.

MemberIDSurnameFirst nameAgeGroup
1ChenMia11Blue
2OkaforBen12Red
3SilvaAna11Blue
4ChenMia11Blue
5RahimovOmar12Red
6TanakaYuki10Green
Worked examples
SurnameFirst nameAgeGroup
ChenMia11Blue
OkaforBen12Red
SilvaAna11Blue
ChenMia11Blue
RahimovOmar12Red
TanakaYuki10Green
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.
FieldHas repeated values?Can be the primary key?
Surnameyes: Chen twiceno
First nameyes: Mia twiceno
Ageyes: 11 three timesno
Groupyes: Blue three timesno
MemberIDno: 1, 2, 3, 4, 5, 6yes
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 happensNumber the computer gives
Six records already exist1 to 6
A new member joins7
Record 4 is deleted–
Another new member joins8, 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?
NameAnimalAge
Cococat2
Rexdog3
Cocodog3
Rexcat2
2
Which field is the best primary key for the bus-pass table, and why are the other fields not suitable?
PassNoNameRoute
101Dana5
102Kai5
103Dana7
104Lena3
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.
4
Count the fields and the records in this table, and name the field headings.
CodePlantHeightPlanted
P1Bean30May
P2Pea45April
P3Bean12June
P4Mint20May
P5Pea8June