Table of Contents Link to heading

• Data Anomalies in Poorly Designed Schemas
• The Design Principle: One Entity per Relation
• What Normalisation Does
• Normalisation Process
  • Step 1: Identify the Unnormalised Form (UNF)
  • Step 2: First Normal Form (1NF)
  • Step 3: Second Normal Form (2NF)
  • Step 4: Third Normal Form (3NF)
  • Step 5: Consolidation
• The Four Rules of the First Three Normal Forms

Data Anomalies in Poorly Designed Schemas Link to heading

When a single relation captures data about multiple entities — or mixes entity data with relationship data — the result is a set of structural problems collectively called data anomalies:

1. Redundancy — the same fact is stored in multiple rows. If every row of a StudentCourse table repeats the course room number, that room number exists hundreds or thousands of times unnecessarily.

2. Update Anomaly — because the same fact appears in multiple rows, updating it requires touching every row that contains it. Miss one row, and the database now contains two contradictory values for the same fact.

3. Insertion Anomaly — the schema forces unrelated data to be inserted together. If a course cannot exist in the database without at least one enrolled student, then the course cannot be recorded until someone enrols — even if the course is being planned in advance.

4. Deletion Anomaly — deleting a record causes unintended loss of other information. If the last student drops a course, deleting that enrolment row also deletes the only record of the course’s existence.

A concrete example of a poorly designed relation:

student	course	room
Henry	INFS1025	C2-04
Mai	INFS1025	C2-04
Duc	INFS1025	C2-04

• The room number C2-04 is redundant — stored once per student, not once per course
• Updating the room for one row leaves the others with the old value (update anomaly)
• Dropping all students removes all knowledge of where the course is held (deletion anomaly)
• A room cannot be pre-assigned to a course that has no students yet (insertion anomaly)

The Design Principle: One Entity per Relation Link to heading

The underlying rule of good database design is simple: each relation should capture data about exactly one type of entity or object. When a relation stores facts about multiple entities simultaneously, anomalies are inevitable.

Normalisation is the formal process of enforcing this principle.

What Normalisation Does Link to heading

Normalisation is the process of decomposing large, poorly structured relational schemas into smaller, well-structured ones — where each relation captures only one type of information, each fact is stored exactly once, and all attributes are functionally dependent on the entire Primary Key.

Normalisation:

• Eliminates redundant data by ensuring each fact exists in exactly one place
• Prevents update, insertion, and deletion anomalies
• Produces schemas that are more easily maintained, extended, and queried

The trade-off: normalised schemas require more joins at query time. In environments with very high read volume and infrequent updates, a degree of controlled denormalisation (intentionally introducing redundancy) may be acceptable for performance — but normalisation should always be the starting point.

Normalisation Process Link to heading

Step 1: Identify the Unnormalised Form (UNF) Link to heading

The UNF is the raw, unstructured data as received — typically from a sample report, spreadsheet, or business document. Identify all data items and locate:

• Repeating groups: a set of attributes that repeat with the same key (e.g., a student’s name appearing once per course enrolled)
• Multi-value attributes: a single attribute cell containing multiple values

Step 2: First Normal Form (1NF) Link to heading

1NF requires that all attribute values are atomic — each cell holds exactly one indivisible value — and that there are no repeating groups.

To achieve 1NF:

1. Identify and declare the Primary Key for the relation
2. Remove the repeating group into a new child relation
3. Bring the parent’s PK into the child relation as part of its key
4. Identify the child relation’s PK (may be the parent PK alone, or a composite of the parent PK and an additional attribute)

After achieving 1NF, check for partial dependencies:

• A partial dependency exists when a non-key attribute depends on only part of a composite PK, not the whole PK
• This can only occur when the PK is composite — a partial dependency on a single-attribute PK is not possible

Example of a partial dependency:

OrderLine(orderNo, productCode, productName, quantity, unitPrice)
PK(orderNo, productCode)

productName → productCode only (not the full PK)

productName partially depends on productCode, not on the combination (orderNo, productCode).

Step 3: Second Normal Form (2NF) Link to heading

2NF removes partial dependencies.

To achieve 2NF:

1. Move the partially dependent attribute(s) and the PK subset they depend on into a new child relation
2. The new child relation’s PK is the part of the original composite PK that the attributes depend on
3. The original relation retains the full composite PK, and the moved PK subset becomes a FK referencing the new child

Continuing the example:

Order(orderNo, customerName, ...)
PK(orderNo)

Product(productCode, productName, unitPrice)
PK(productCode)

OrderLine(orderNo, productCode, quantity)
PK(orderNo, productCode)
FK(orderNo) → Order(orderNo)
FK(productCode) → Product(productCode)

After achieving 2NF, check for transitive dependencies:

• A transitive dependency exists when a non-key attribute depends on another non-key attribute
• For a transitive dependency to exist, the relation must have at least two non-key attributes, and one must determine the other

Example of a transitive dependency:

Book(bookName, authorName, authorEmail)
PK(bookName)

bookName → authorName
authorName → authorEmail
Therefore: bookName → authorEmail (transitively)

Step 4: Third Normal Form (3NF) Link to heading

3NF removes transitive dependencies.

To achieve 3NF:

1. Move the transitively dependent attribute(s) and the non-key attribute they depend on into a new child relation
2. The determinant non-key attribute becomes the PK of the new child relation
3. The original relation retains the determinant attribute as a FK referencing the new child

Continuing the example:

Book(bookName, authorName)
PK(bookName)
FK(authorName) → Author(authorName)

Author(authorName, authorEmail)
PK(authorName)

After 3NF, verify full dependency — every non-key attribute must depend on the entire PK and nothing else. If this holds, the schema is in 3NF.

Step 5: Consolidation Link to heading

When normalising multiple source reports or forms independently, the process produces multiple sets of 3NF relations. Consolidation merges these into a single unified schema.

Rule: Join all relations that have the same Primary Key — combine their attributes.

Example — eight relations before consolidation:

1. Unit(unitNo, unitName, unitDescrip, unitValue)
2. Lecturer(lecturerNo, lecturerName, lecturerOfficeNo, lecturerPhoneNo)
3. UnitAdvisor(lecturerNo, unitNo)
4. Unit(unitNo, unitName)
5. Student(stuNo, stuName, stuAddr, modeOfStudy, mentorNo)
6. Mentor(mentorNo, mentorName)
7. AcademicRecord(stuNo, unitNo, yearSemester, grade)
8. Unit(unitNo, unitName)

After consolidation (merge 1 + 4 + 8, merge 2 + 6):

Unit(unitNo, unitName, unitDescrip, unitValue)
PK(unitNo)

Lecturer(lecturerNo, lecturerName, lecturerOfficeNo, lecturerPhoneNo)
PK(lecturerNo)

UnitAdvisor(lecturerNo, unitNo)
PK(lecturerNo, unitNo)

Student(stuNo, stuName, stuAddr, modeOfStudy, mentorNo)
PK(stuNo)

AcademicRecord(stuNo, unitNo, yearSemester, grade)
PK(stuNo, unitNo, yearSemester)

The Four Rules of the First Three Normal Forms Link to heading

Rule 1: No repeating groups or multi-value attributes

A repeating group occurs when a student’s name appears multiple times — once per course — in the same relation:

student	age	course
Henry	20	OOP
Henry	20	SRUX
Mai	18	OOP

A multi-value attribute packs multiple values into a single cell:

courses
DDWT, SRUX, OOP

The correct form — each cell holds one value:

courses
DDWT
SRUX
OOP

Rule 2: All attribute values must be atomic

Atomic means each column holds exactly one indivisible piece of data.

Non-atomic (course code and name combined in one column):

courseCodeAndCourseName
INFS1025 DDWT
COMP1046 OOP

Atomic (each value in its own column):

courseCode	courseName
INFS1025	DDWT
COMP1046	OOP

Rule 3: Candidate Keys must be identified

For each relation, determine the minimum set(s) of attributes that uniquely identify every tuple. Select one as the PK; enforce the others as Unique Keys.

Rule 4: Every attribute must be relevant to the Candidate Key(s)

All data in a relation must describe the entity identified by the CK. A relation should contain data about exactly one type of object.

Before decomposition (multiple entity types mixed in one table):

title	format	author	genreID	genre	price	publisher
SQL for Dummies	E-book	Taylor A	1	SQL	49.99	Wiley

After decomposition (each entity in its own relation):

Book: (title, authorID, genreID, publisherID)

Author: (authorID, authorName)

Publisher: (publisherID, publisherName)

Genre: (genreID, genreName)

BookFormat: (title, formatID, price) — price depends on both the book and its format