Naming columns, primary keys and foreign keys

Name:

Date:

Class:

Quick Questions

Shade in the A or B circle for each question.

1. Which is the better name for the primary key column in a table of students?

A student_id

B x

2. A Results table links to the Students table. Which foreign key name should Results use?

A student_id

B stu_ref

3. Which set of column names follows one consistent style?

A first_name, LastName, DOB

B first_name, last_name, date_of_birth

4. True or false: a foreign key can have a different name from the primary key it links to, as long as the data matches.

A True: any name is fine

B False: it should reuse the same name

5. Which column name is more descriptive for storing a student's year level?

A year_level

B col3

Match the Pairs

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

Left column		Right column
1student_id		AA descriptive column name that says what data it holds, in the same lowercase underscore style
2first_name		BA key column name: lowercase, descriptive, and ending in the _id suffix
3A primary key in the Student table is named with the _id suffix		CThe foreign key name does not match the primary key name. It should be student_id.
4A foreign key in the Enrolment table reuses the primary key name from the Student table		DThe column names use mixed styles. They should all be lowercase with underscores: first_name, last_name, year_level.
5Enrolment table: StudentRef (foreign key to Student.student_id)		ECREATE TABLE Student (student_id INTEGER PRIMARY KEY);
6Student table columns: student_id, FirstName, last name, Year-Level		Fstudent_id INTEGER REFERENCES Student(student_id)

Definitions

student_id

Fill in the Blanks

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

CREATE TABLE student ( __________ INTEGER PRIMARY KEY, first_name TEXT);

CREATE TABLE result ( mark INTEGER, __________ INTEGER);

CREATE TABLE class ( class___ INTEGER PRIMARY KEY, class_name TEXT);

CREATE TABLE student ( _________ TEXT, first_name TEXT);

ALTER TABLE mark ADD FOREIGN KEY (__________) REFERENCES subject(subject_id);

Spot the Error

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

Question 1

A CREATE TABLE student (

B student_id INT PRIMARY KEY,

C first_name TEXT,

D YearLevel INT

E );

Question 2

A CREATE TABLE result (

B result_id INT PRIMARY KEY,

C stu_id INT,

D mark INT

E );

Question 3

A CREATE TABLE teacher (

B first_name TEXT,

C id INT PRIMARY KEY,

D last_name TEXT

E );

Question 4

A CREATE TABLE book (

B book_key INT PRIMARY KEY,

C title TEXT,

D author TEXT

E );

Question 5

A CREATE TABLE club (

B c1 TEXT,

C club_id INT PRIMARY KEY,

D suburb TEXT

E );

Answer Key

Naming columns, primary keys and foreign keys

Section A: Quick Questions

A — student_id

B — first_name, last_name, date_of_birth

B — False: it should reuse the same name

A — year_level

Section B: Match the Pairs

student_id → A key column name: lowercase, descriptive, and ending in the _id suffix

first_name → A descriptive column name that says what data it holds, in the same lowercase underscore style

A primary key in the Student table is named with the _id suffix → CREATE TABLE Student (student_id INTEGER PRIMARY KEY);

A foreign key in the Enrolment table reuses the primary key name from the Student table → student_id INTEGER REFERENCES Student(student_id)

Enrolment table: StudentRef (foreign key to Student.student_id) → The foreign key name does not match the primary key name. It should be student_id.

Student table columns: student_id, FirstName, last name, Year-Level → The column names use mixed styles. They should all be lowercase with underscores: first_name, last_name, year_level.

Section C: Definitions

student_id — A key column name in lowercase with an _id suffix. A primary key uses it in its own table, and a foreign key reuses the exact same name in a related table.

Section D: Fill in the Blanks

CREATE TABLE student (
  student_id INTEGER PRIMARY KEY,
  first_name TEXT);

CREATE TABLE result (
  mark INTEGER,
  student_id INTEGER);

CREATE TABLE class (
  class_id INTEGER PRIMARY KEY,
  class_name TEXT);

CREATE TABLE student (
  last_name TEXT,
  first_name TEXT);

ALTER TABLE mark
ADD FOREIGN KEY (subject_id)
REFERENCES subject(subject_id);

Section E: Spot the Error

D — Column names must follow one style. The others use lowercase snake_case, so YearLevel should be year_level.

C — A foreign key reuses the exact name of the primary key it points to. It should be student_id, not stu_id.

C — A primary key is named after its table with an _id suffix. A bare id is not descriptive, so it should be teacher_id.

B — Key columns use the _id suffix. book_key should be book_id.

B — Column names should be descriptive so the reader knows what they store. c1 should be club_name.