Third normal form (3NF) (Data and Programming Techniques)

Name:

Date:

Class:

Quick Questions

Shade in the A or B circle for each question.

1. A table is in 3NF when it is in 2NF and has no transitive dependency.

A True

B False

2. Customer(customerId, name, postcode, suburb). Suburb depends on postcode. Which describes the problem?

A A non-key field (suburb) depends on the whole primary key

B A non-key field (suburb) depends on another non-key field (postcode)

3. Which fix brings Customer(customerId, name, postcode, suburb) into 3NF?

A Move postcode and suburb into a new Postcode table and keep postcode in Customer as a foreign key

B Delete the suburb values from the Customer table and keep the table as it is

4. Which is a transitive dependency?

A customerId -> name

B customerId -> postcode -> suburb

5. A table is in 3NF but has not yet been checked for 2NF. Is that possible?

A No, 3NF requires the table to already be in 2NF

B Yes, 3NF can be reached without 2NF

Match the Pairs

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

Left column		Right column
1Students(StudentID, Name, Postcode, Suburb)		APostcode was left out of Students, so there is no foreign key linking the two tables. Students cannot be matched to their postcode.
2Postcodes(Postcode, Suburb)		BStudentID -> Postcode -> Suburb
33NF removes a non-key field that depends on another non-key field by moving it to a new table.		CHaving a primary key does not make a table 3NF. Suburb still depends on Postcode, a non-key field, so the transitive dependency remains.
4A transitive dependency is a chain where the key determines a non-key field, which then determines another non-key field.		DStudents(StudentID, Name, Postcode) Postcodes(Postcode, Suburb)
5Students(StudentID, Name, Postcode, Suburb) This table is in 3NF because it has a primary key.		EA new table that holds the suburb for each postcode, so Suburb depends only on its own key.
6Students(StudentID, Name, Suburb) Postcodes(Postcode, Suburb)		FNot in 3NF: Suburb depends on Postcode, which is a non-key field, so there is a transitive dependency.

Definitions

3NF

Fill in the Blanks

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

CREATE TABLE Postcode ( postcode CHAR(4) ___________, suburb VARCHAR(30));

CREATE TABLE Student (postcode CHAR(4), ___________ (postcode) REFERENCES Postcode(postcode));

studentId -> postcode -> ______

SELECT Student.name, Postcode.suburb FROM Student JOIN Postcode ON Student.postcode = _________________;

ALTER TABLE _______ DROP COLUMN suburb;

Spot the Error

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

Question 1

A Cust(id, name, pcode, suburb)

B Postcode(pcode, suburb)

C PK Cust: id

D FK: Cust.pcode -> Postcode

Question 2

A CREATE TABLE Postcode (

B pcode CHAR(4) NOT NULL,

C suburb VARCHAR(30)

D );

Question 3

A Cust(id, name, pcode, suburb)

B suburb -> pcode

C Transitive: id->pcode->suburb

Question 4

A Step 1: table is in 2NF

B Step 2: find non-key fields

C Step 3: does one set another?

D Step 4: delete the field

E Step 5: link tables with a FK

Question 5

A SELECT c.name, p.suburb

B FROM Customer c

C JOIN Postcode p

D ON c.name = p.pcode;

Answer Key

Third normal form (3NF) (Data and Programming Techniques)

Section A: Quick Questions

A — True

B — A non-key field (suburb) depends on another non-key field (postcode)

A — Move postcode and suburb into a new Postcode table and keep postcode in Customer as a foreign key

B — customerId -> postcode -> suburb

A — No, 3NF requires the table to already be in 2NF

Section B: Match the Pairs

Students(StudentID, Name, Postcode, Suburb) → Not in 3NF: Suburb depends on Postcode, which is a non-key field, so there is a transitive dependency.

Postcodes(Postcode, Suburb) → A new table that holds the suburb for each postcode, so Suburb depends only on its own key.

3NF removes a non-key field that depends on another non-key field by moving it to a new table. → Students(StudentID, Name, Postcode) Postcodes(Postcode, Suburb)

A transitive dependency is a chain where the key determines a non-key field, which then determines another non-key field. → StudentID -> Postcode -> Suburb

Students(StudentID, Name, Postcode, Suburb) This table is in 3NF because it has a primary key. → Having a primary key does not make a table 3NF. Suburb still depends on Postcode, a non-key field, so the transitive dependency remains.

Students(StudentID, Name, Suburb) Postcodes(Postcode, Suburb) → Postcode was left out of Students, so there is no foreign key linking the two tables. Students cannot be matched to their postcode.

Section C: Definitions

3NF — Third normal form: the table is in 2NF and no non-key field depends on another non-key field (no transitive dependency), e.g. suburb depends on postcode, so move it to its own table.

Section D: Fill in the Blanks

CREATE TABLE Postcode (
  postcode CHAR(4) PRIMARY KEY,
  suburb VARCHAR(30));

CREATE TABLE Student (postcode CHAR(4),
  FOREIGN KEY (postcode)
  REFERENCES Postcode(postcode));

studentId -> postcode -> suburb

SELECT Student.name, Postcode.suburb
FROM Student JOIN Postcode
ON Student.postcode = Postcode.postcode;

ALTER TABLE Student
DROP COLUMN suburb;

Section E: Spot the Error

A — suburb depends on pcode, which is a non-key field, so this is a transitive dependency. suburb must be removed from Cust and stored in the Postcode table.

B — pcode is the field that decides the suburb, so it must be the PRIMARY KEY of the new Postcode table. Customer can then refer to it with a foreign key.

B — The dependency runs from postcode to suburb, because the postcode decides the suburb. The line should read pcode -> suburb. This non-key to non-key dependency breaks 3NF.

D — The dependent field must be moved to a new table, not deleted. Deleting it would lose data. The determining field becomes the key of the new table.

D — After splitting for 3NF, the join must match the foreign key to the primary key: c.pcode = p.pcode. Matching name to pcode gives meaningless results.