Databases — Normalisation

Question 1

True/False

All row data must depend on their respective keys.

Answer 1

True.

Question 2

Explain

Purpose of data normalisation

Answer 2

Improve data integrity.

Question 3

Recall

How is data converted to a relation?

Answer 3

By normalising it to 1NF.

Question 4

Recall

What does the first normal form (1NF) of database normalisation entail?

Answer 4

• Ensuring all data fields are atomic — that they only have one value within them
• Remove all repeating attributes, make them a new table.

Question 5

Recall

What does the second normal form (2NF) of database normalisation entail?

Answer 5

• Removing partial dependencies — If data has a composite key, and a non-key value doesn’t depend on every element of the composite key, remove it, potentially make it a new table.

[e.g., Student(Name (PK), CourseID (PK), CourseName) — CourseName only depends on CourseID, not StudentName]

[Data must also already be a relation (in 1NF)]

Question 6

Define

Partial Dependency

Answer 6

Where a non-key data value depends on only part of a composite key.

(Data must have a composite key for this to be relevant)

Question 7

What does the third normal form (3NF) of database normalisation entail?

Answer 7

• Removing transitive dependencies — Where a non-key value depends on another non-key value.

(Data must also already be in 2NF)

Question 8

Define

Transitive Dependency

Answer 8

Where a non-key value depends on another non-key value.