Choosing a suitable primary key from a table

Name:

Date:

Class:

Quick Questions

Shade in the A or B circle for each question.

1. A Students table has StudentID, Name and DateOfBirth. Which field is the most suitable primary key?

A StudentID

B Name

2. Why is DateOfBirth a poor choice of primary key?

A Dates cannot be stored in a database

B Two people can be born on the same date

3. A Cars table has a Colour field where 'Red' appears in 4 records. Can Colour be the primary key?

A Yes, because every car has a colour

B No, because its values are repeated

4. Which statement about a primary key is correct?

A It is never left blank in any record

B It may be left blank in some records

5. A Books table has Title, Author and Genre, and some titles and authors repeat. Nothing is unique. What is the best solution?

A Add a new field such as BookID

B Use Author as the key

Match the Pairs

Draw a line connecting each item on the left to its match on the right.

Left column		Right column
1PRIMARY KEY (studentId)		AAn ID field that must always hold a value, so no record can have a blank key
2studentId INTEGER NOT NULL		BLast names can be shared by different students, so they are not unique. Use an ID number instead.
3Two students can share a first name or date of birth, so these fields are unsuitable as a primary key		CChooses studentId as the field that identifies each record, because its values are unique and never blank
4When no field is unique, add a new ID field to the table and use it as the primary key		DALTER TABLE Pets ADD petId INTEGER
5PRIMARY KEY (house)		EfirstName, dateOfBirth
6PRIMARY KEY (lastName)		FThe house field has repeated values such as Red and Blue, so it cannot identify one record. Use a unique field such as studentId.

Definitions

PRIMARY KEY

Fill in the Blanks

Complete each line of code by filling in the missing parts.

Student(StudentID, Name, DOB, Form) Primary key: _________

CREATE TABLE Student ( StudentID INTEGER ___________, Name TEXT);

Can DOB be the primary key? __ Two pupils can share a date of birth.

Pet(Name, Breed, Age) No unique field, so add a new field: _____

Key values must be ______ and never blank

Spot the Error

Shade in the letter of the line that contains an error.

Question 1

A CREATE TABLE Student (

B StudentID INTEGER,

C Name VARCHAR(30),

D PRIMARY KEY (Name)

E );

Question 2

A CREATE TABLE Pupil (

B Name VARCHAR(30),

C DOB DATE PRIMARY KEY,

D PupilID INTEGER PRIMARY KEY

E );

Question 3

A PRIMARY KEY: Genre

B Table: Book

C Fields: ISBN, Title, Genre

D Reason: each book has its own ISBN

Question 4

A Table: Pupil

B Fields: Name, DOB, House

C Problem: no field is unique

D Fix: use House as the key

Question 5

A Key field: StaffID (blanks ok)

B Table: Staff

C Fields: StaffID, Name, Dept

D Each StaffID is different

Answer Key

Choosing a suitable primary key from a table

Section A: Quick Questions

A — StudentID

B — Two people can be born on the same date

B — No, because its values are repeated

A — It is never left blank in any record

A — Add a new field such as BookID

Section B: Match the Pairs

PRIMARY KEY (studentId) → Chooses studentId as the field that identifies each record, because its values are unique and never blank

studentId INTEGER NOT NULL → An ID field that must always hold a value, so no record can have a blank key

Two students can share a first name or date of birth, so these fields are unsuitable as a primary key → firstName, dateOfBirth

When no field is unique, add a new ID field to the table and use it as the primary key → ALTER TABLE Pets ADD petId INTEGER

PRIMARY KEY (house) → The house field has repeated values such as Red and Blue, so it cannot identify one record. Use a unique field such as studentId.

PRIMARY KEY (lastName) → Last names can be shared by different students, so they are not unique. Use an ID number instead.

Section C: Definitions

PRIMARY KEY — The field chosen to identify each record. Its values must be unique and never blank, like an ID number. Names and dates of birth can repeat, so they are unsuitable.

Section D: Fill in the Blanks

Student(StudentID, Name, DOB, Form)
Primary key: StudentID

CREATE TABLE Student (
  StudentID INTEGER PRIMARY KEY,
  Name TEXT);

Can DOB be the primary key? No
Two pupils can share a date of birth.

Pet(Name, Breed, Age)
No unique field, so add a new field: PetID

Key values must be unique and never blank

Section E: Spot the Error

D — Name is the wrong primary key because two students can share the same name. The key should be the unique ID field, so the line should be PRIMARY KEY (StudentID).

C — DOB has been made a primary key, but pupils can share a date of birth. Only PupilID, which is different for every pupil, should be the primary key. The line should be DOB DATE,

A — Genre is a poor key because many books share the same genre, so its values repeat. The key should be ISBN, which is different for every book: PRIMARY KEY: ISBN.

D — House values repeat because many pupils are in the same house. When no field is unique, the fix is to add a new ID field such as PupilID and use that as the key.

A — A primary key must never be blank, because every record needs a value that identifies it. The line should say that StaffID has no blanks.