Modifying a database to 3NF

Name:

Date:

Class:

Quick Questions

Shade in the A or B circle for each question.

1. A Students table has studentId (PK), name, houseId and houseName. houseName depends on houseId, not on studentId. What should you do to reach 3NF?

A Move houseName into a new House table with houseId as its primary key

B Leave houseName where it is because it is in the same row as studentId

2. After splitting a table to reach 3NF, how do you link the new table back to the original?

A Delete the key from the original table so the tables are separate

B Keep the new table's primary key in the original table as a foreign key

3. Which problem does 3NF fix?

A A non-key field that depends on another non-key field

B A table that has more than one row of data

4. Orders(orderId, productId, productName, price). productName and price depend on productId. Which design is in 3NF?

A Orders(orderId, productName) and Products(price)

B Orders(orderId, productId) and Products(productId, productName, price)

5. When you move fields into a new table, what must the new table have?

A Its own primary key

B No primary key because it is only a copy

Match the Pairs

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

Left column		Right column
1CREATE TABLE teachers (teacherId INT PRIMARY KEY, teacherName VARCHAR(50));		AINSERT INTO teachers SELECT DISTINCT teacherId, teacherName FROM subjects;
2ALTER TABLE subjects ADD FOREIGN KEY (teacherId) REFERENCES teachers(teacherId);		BLinks the original table to the new table so the data can be joined back together.
3Check no data is lost after splitting: joining the new tables should give back every original row.		CThe new table has no primary key. Every table made by splitting for 3NF needs its own primary key, such as teacherId INT PRIMARY KEY.
4Fields that depend on a non-key field (teacherName depends on teacherId, not subjectId) are moved into a new table.		DSELECT * FROM subjects INNER JOIN teachers ON subjects.teacherId = teachers.teacherId;
5CREATE TABLE teachers (teacherName VARCHAR(50));		ECreates a new table for fields that depend on a non-key field, with its own primary key.
6ALTER TABLE subjects DROP COLUMN teacherName; -- before copying it to teachers		FThe field was dropped before its data was copied into the new table, so the teacher names are lost forever.

Definitions

3NF

Fill in the Blanks

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

CREATE TABLE teacher ( teacherId INT ___________, room VARCHAR(10));

ALTER TABLE student ADD FOREIGN KEY (teacherId) __________ teacher(teacherId);

INSERT INTO teacher(teacherId, room) _______________ teacherId, room FROM student_old;

SELECT s.name, t.room FROM student s ____ teacher t ON s.teacherId = t.teacherId;

ALTER TABLE student ___________ room;

Spot the Error

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

Question 1

A CREATE TABLE classes (

B roomId INTEGER PRIMARY KEY,

C building TEXT

D );

Question 2

A INSERT INTO suppliers (name)

B SELECT supplier

C FROM products;

Question 3

A ALTER TABLE products

B ADD supplierName TEXT

C REFERENCES suppliers(id);

Question 4

A CREATE TABLE products (

B pId INTEGER PRIMARY KEY,

C supId INTEGER UNIQUE,

D FOREIGN KEY (supId)

E REFERENCES suppliers(id)

F );

Question 5

A SELECT COUNT(*)

B FROM products

C WHERE supplierId IS NOT NULL;

Answer Key

Modifying a database to 3NF

Section A: Quick Questions

A — Move houseName into a new House table with houseId as its primary key

B — Keep the new table's primary key in the original table as a foreign key

A — A non-key field that depends on another non-key field

B — Orders(orderId, productId) and Products(productId, productName, price)

A — Its own primary key

Section B: Match the Pairs

CREATE TABLE teachers (teacherId INT PRIMARY KEY, teacherName VARCHAR(50)); → Creates a new table for fields that depend on a non-key field, with its own primary key.

ALTER TABLE subjects ADD FOREIGN KEY (teacherId) REFERENCES teachers(teacherId); → Links the original table to the new table so the data can be joined back together.

Check no data is lost after splitting: joining the new tables should give back every original row. → SELECT * FROM subjects INNER JOIN teachers ON subjects.teacherId = teachers.teacherId;

Fields that depend on a non-key field (teacherName depends on teacherId, not subjectId) are moved into a new table. → INSERT INTO teachers SELECT DISTINCT teacherId, teacherName FROM subjects;

CREATE TABLE teachers (teacherName VARCHAR(50)); → The new table has no primary key. Every table made by splitting for 3NF needs its own primary key, such as teacherId INT PRIMARY KEY.

ALTER TABLE subjects DROP COLUMN teacherName; -- before copying it to teachers → The field was dropped before its data was copied into the new table, so the teacher names are lost forever.

Section C: Definitions

3NF — Third normal form: move fields that depend on a non-key field into a new table with its own primary key, then link the tables with a foreign key. Check no data is lost.

Section D: Fill in the Blanks

CREATE TABLE teacher (
  teacherId INT PRIMARY KEY,
  room VARCHAR(10));

ALTER TABLE student
ADD FOREIGN KEY (teacherId)
REFERENCES teacher(teacherId);

INSERT INTO teacher(teacherId, room)
SELECT DISTINCT teacherId, room
FROM student_old;

SELECT s.name, t.room
FROM student s JOIN teacher t
ON s.teacherId = t.teacherId;

ALTER TABLE student
DROP COLUMN room;

Section E: Spot the Error

A — The fields that depend on a non-key field must go into a NEW table. The classes table already exists, so the new table needs its own name, such as rooms.

B — Without DISTINCT, every product row copies its supplier, so the new table gets repeated suppliers. Use SELECT DISTINCT supplier so each supplier is stored once.

B — The link column must be a foreign key that holds the new table's primary key value. It should be supplierId INTEGER, not the supplier's name as text.

C — A foreign key must allow repeated values, because many products can share one supplier. UNIQUE stops that. The line should be just supId INTEGER,.

C — To check no data was lost, look for products that did not get linked to a supplier. That means IS NULL, and the count should be 0. IS NOT NULL counts the linked rows, which proves nothing.