Secondary key

Name:

Date:

Class:

Quick Questions

Shade in the A or B circle for each question.

1. A student table has studentId as its primary key. Which field is a good secondary key for finding students quickly by name?

A surname

B studentId

2. Which statement about secondary key values is correct?

A Every value must be unique, just like a primary key

B Values can repeat, such as two students with the surname Nguyen

3. A search for surname = 'Smith' returns three records. What does this show?

A A secondary key can match more than one record

B The surname must be the primary key

4. Which search is using a primary key?

A Finding the student with studentId = 7

B Finding all students with surname = 'Lee'

5. What is an index on a secondary key used for?

A Making every value in the field unique

B Making searches on that field faster

Match the Pairs

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

Left column		Right column
1SELECT * FROM students WHERE lastName = 'Nguyen';		ASELECT * FROM students ORDER BY lastName;
2CREATE INDEX idx_lastName ON students(lastName);		BSecondary key values do not have to be unique. Two students can have the same surname. Only a primary key must be unique.
3A secondary key is a non-primary field used to sort records, and its values do not have to be unique		CBuilds an index on a secondary key so searches by surname run faster
4A primary key finds exactly one record, while a secondary key can match many records		DSearches the students table using lastName, a secondary key, and may return more than one record
5A secondary key must be unique, so no two records can share the same lastName.		EUsing a surname as the primary key is wrong because surnames repeat. lastName is better as a secondary key, with a unique field like studentId as the primary key.
6PRIMARY KEY (lastName)		FWHERE studentId = 1 (primary) versus WHERE house = 'Red' (secondary)

Definitions

secondary key

Fill in the Blanks

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

CREATE _____ idxSurname ON students(lastName);

SELECT firstName, lastName FROM students WHERE ________ = 'Nguyen';

SELECT * FROM students ORDER BY ________;

CREATE INDEX idxHouse ON students(_____);

SELECT * FROM students WHERE _________ = 3;

Spot the Error

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

Question 1

A CREATE INDEX idx_surname

B ON students(studentId);

C SELECT * FROM students

D WHERE lastName = 'Nguyen';

Question 2

A -- primary key: lastName

B SELECT * FROM students

C WHERE lastName = 'Smith';

Question 3

A SELECT * FROM students

B WHERE lastName = Nguyen

C ORDER BY firstName;

Question 4

A found <- false

B for i from 1 to n

C if rec[i].surname <- key then

D found <- true

E end if

Question 5

A CREATE TABLE students (

B studentId INT PRIMARY KEY,

C lastName VARCHAR(30) UNIQUE,

D firstName VARCHAR(30)

E );

Answer Key

Secondary key

Section A: Quick Questions

A — surname

B — Values can repeat, such as two students with the surname Nguyen

A — A secondary key can match more than one record

A — Finding the student with studentId = 7

B — Making searches on that field faster

Section B: Match the Pairs

SELECT * FROM students WHERE lastName = 'Nguyen'; → Searches the students table using lastName, a secondary key, and may return more than one record

CREATE INDEX idx_lastName ON students(lastName); → Builds an index on a secondary key so searches by surname run faster

A secondary key is a non-primary field used to sort records, and its values do not have to be unique → SELECT * FROM students ORDER BY lastName;

A primary key finds exactly one record, while a secondary key can match many records → WHERE studentId = 1 (primary) versus WHERE house = 'Red' (secondary)

A secondary key must be unique, so no two records can share the same lastName. → Secondary key values do not have to be unique. Two students can have the same surname. Only a primary key must be unique.

PRIMARY KEY (lastName) → Using a surname as the primary key is wrong because surnames repeat. lastName is better as a secondary key, with a unique field like studentId as the primary key.

Section C: Definitions

secondary key — A non-primary field used to search or sort records, such as surname. Unlike a primary key, its values need not be unique, and it is often indexed to make searches faster.

Section D: Fill in the Blanks

CREATE INDEX idxSurname
ON students(lastName);

SELECT firstName, lastName FROM students
WHERE lastName = 'Nguyen';

SELECT * FROM students
ORDER BY lastName;

CREATE INDEX idxHouse
ON students(house);

SELECT * FROM students
WHERE studentId = 3;

Section E: Spot the Error

B — The index is meant to speed up searches by surname, so it must be built on the secondary key lastName. studentId is the primary key and already has an index. It should be ON students(lastName);

A — lastName is not unique because many students can share a surname, so it is a secondary key, not a primary key. The comment should say secondary key: lastName.

B — A text value searched on a secondary key such as a surname must be in quotes. Without them SQL treats Nguyen as a column name. It should be WHERE lastName = 'Nguyen'

C — The search must compare the surname with the key using =. The <- arrow assigns a value instead of testing it. It should be if rec[i].surname = key then

C — A secondary key need not be unique, because two students can share a surname. UNIQUE would reject the second Nguyen. Only the primary key studentId must be unique, so remove UNIQUE.