Database Normalization Process

Step 1: Unnormalized Form (UNF)

Data in an unnormalized form often contains repeated groups of data.

Problem: Redundant data and update anomalies.

Normalization Process: Separate the data into distinct records.

Resulting Form: Unnormalized data becomes 1NF.

Assessor Table:

Assessor Name	Assessor ID	Assessor Address	Post Code	Assessor Mobile Number	Assessor Email Address	Course Name	Course ID	Venue Name	Venue ID	Venue Address	Post Code	Venue Mobile Number	Manager Name	Venue Email Address	Venue Cost	Google Map Link	Student Name	Student ID	Student Address	Student Post Code	Student Email Address	Student Mobile Number	Notes
[Assessor Name]	[Assessor ID]	[Assessor Address]	[Post Code]	[Assessor Mobile Number]	[Assessor Email Address]	[Course Name]	[Course ID]	[Venue Name]	[Venue ID]	[Venue Address]	[Post Code]	[Venue Mobile Number]	[Manager Name]	[Venue Email Address]	[Venue Cost]	[Google Map Link]	[Student Name]	[Student ID]	[Student Address]	[Student Post Code]	[Student Email Address]	[Student Mobile Number]	[Notes]

Course Table:

Course Name	Course ID	Course Date	Course Cost	Assessor Name	Assessor ID	Course Duration	Venue Name	Venue ID	Venue Address	Post Code	Venue Mobile Number	Manager Name	Venue Email Address	Venue Cost	Google Map Link	Notes
[Course Name]	[Course ID]	[Course Date]	[Course Cost]	[Assessor Name]	[Assessor ID]	[Course Duration]	[Venue Name]	[Venue ID]	[Venue Address]	[Post Code]	[Venue Mobile Number]	[Manager Name]	[Venue Email Address]	[Venue Cost]	[Google Map Link]	[Notes]

Venue Table:

Venue Name	Venue ID	Venue Address	Post Code	Venue Mobile Number	Manager Name	Venue Email Address	Venue Cost	Google Map Link	Course Name	Course ID	Assessor Name	Assessor ID	Assessor Address	Assessor Post Code	Assessor Mobile Number	Assessor Email Address	Student Name	Student ID	Student Address	Student Post Code	Student Email Address	Student Mobile Number	Notes
[Venue Name]	[Venue ID]	[Venue Address]	[Post Code]	[Venue Mobile Number]	[Manager Name]	[Venue Email Address]	[Venue Cost]	[Google Map Link]	[Course Name]	[Course ID]	[Assessor Name]	[Assessor ID]	[Assessor Address]	[Assessor Post Code]	[Assessor Mobile Number]	[Assessor Email Address]	[Student Name]	[Student ID]	[Student Address]	[Student Post Code]	[Student Email Address]	[Student Mobile Number]	[Notes]

Course Schedule Table:

Course Name	Course ID	Course Date	Course Duration	Course Cost	Venue Name	Venue ID	Venue Address	Post Code	Venue Mobile Number	Manager Name	Venue Email Address	Venue Cost	Google Map Link	Assessor Name	Assessor ID	Assessor Address	Assessor Post Code	Assessor Mobile Number	Assessor Email Address	Student Name	Student ID	Student Address	Student Post Code	Student Email Address	Student Mobile Number	Notes
[Course Name]	[Course ID]	[Course Date]	[Course Duration]	[Course Cost]	[Venue Name]	[Venue ID]	[Venue Address]	[Post Code]	[Venue Mobile Number]	[Manager Name]	[Venue Email Address]	[Venue Cost]	[Google Map Link]	[Assessor Name]	[Assessor ID]	[Assessor Address]	[Assessor Post Code]	[Assessor Mobile Number]	[Assessor Email Address]	[Student Name]	[Student ID]	[Student Address]	[Student Post Code]	[Student Email Address]	[Student Mobile Number]	[Notes]

Student Table:

Student Name	Student ID	Address	Post Code	Email Address	Student Mobile Number	Course Name	Course ID	Course Date	Achieved (Yes/No)	Assessor Name	Assessor ID	Assessor Address	Post Code	Assessor Mobile Number	Assessor Email Address	Notes
[Student Name]	[Student ID]	[Address]	[Post Code]	[Email Address]	[Student Mobile Number]	[Course Name]	[Course ID]	[Course Date]	[Achieved (Yes/No)]	[Assessor Name]	[Assessor ID]	[Assessor Address]	[Post Code]	[Assessor Mobile Number]	[Assessor Email Address]	[Notes]

Step 2: First Normal Form (1NF)

In 1NF, all columns contain atomic values. There are no repeating groups.

Problem: Partial dependencies exist.

Normalization Process: Eliminate partial dependencies.

Resulting Form: Data is now in 2NF.

Assessor Table:

Assessor ID	Assessor Name	Assessor Address	Post Code	Mobile Number	Email
[AssessorID]	[AssessorName]	[AssessorAddress]	[PostCode]	[MobileNumber]	[Email]

Course Table:

Course ID	Course Name	Course Date	Duration	Cost	Assessor ID
[CourseID]	[CourseName]	[CourseDate]	[Duration]	[Cost]	[AssessorID]

Venue Table:

Venue ID	Venue Name	Venue Address	Post Code	Mobile Number	Manager Name	Email	Cost	Google Maps Link
[VenueID]	[VenueName]	[VenueAddress]	[PostCode]	[MobileNumber]	[ManagerName]	[Email]	[Cost]	[GoogleMapsLink]

Student Table:

Student ID	Student Name	Address	Post Code	Email	Mobile Number
[StudentID]	[StudentName]	[Address]	[PostCode]	[Email]	[MobileNumber]

Course Schedule Table:

Schedule ID	Course ID	Venue ID	Student ID	Achieved ( Yes / No)	Notes
[ScheduleID]	[CourseID]	[VenueID]	[StudentID]	[Achieved]	[Notes]

Step 3: Second Normal Form (2NF)

2NF eliminates partial dependencies, ensuring each non-key attribute depends on the entire primary key.

Problem: Transitive dependencies exist.

Normalization Process: Remove transitive dependencies.

Resulting Form: Data is now in 3NF.

Assessor Table:

Assessor ID	Assessor Name	Assessor Address	Post Code	Mobile Number	Email
[AssessorID]	[AssessorName]	[AssessorAddress]	[PostCode]	[MobileNumber]	[Email]

Course Table:

Course ID	Course Name	Course Date	Duration	Cost	Assessor ID
[CourseID]	[CourseName]	[CourseDate]	[Duration]	[Cost]	[AssessorID]

Venue Table:

Venue ID	Venue Name	Venue Address	Post Code	Mobile Number	Manager Name	Email	Cost	Google Maps Link
[VenueID]	[VenueName]	[VenueAddress]	[PostCode]	[MobileNumber]	[ManagerName]	[Email]	[Cost]	[GoogleMapsLink]

Student Table:

Student ID	Student Name	Address	Post Code	Email	Mobile Number
[StudentID]	[StudentName]	[Address]	[PostCode]	[Email]	[MobileNumber]

Course Schedule Table:

Schedule ID	Course ID	Venue ID	Student ID	Achieved ( Yes / No)	Notes
[ScheduleID]	[CourseID]	[VenueID]	[StudentID]	[Achieved]	[Notes]

Step 4: Third Normal Form (3NF)

3NF removes all transitive dependencies, ensuring that non-key attributes depend only on the primary key.

Problem: The data still may have anomalies.

Normalization Process: Resolve any remaining dependencies to reach BCNF.

Resulting Form: Data is in BCNF.

Assessor Table:

Assessor ID	Assessor Name	Assessor Address	Post Code	Mobile Number	Email
[AssessorID]	[AssessorName]	[AssessorAddress]	[PostCode]	[MobileNumber]	[Email]

Course Table:

Course ID	Course Name	Course Date	Duration	Cost	Assessor ID
[CourseID]	[CourseName]	[CourseDate]	[Duration]	[Cost]	[AssessorID]

Venue Table:

Venue ID	Venue Name	Venue Address	Post Code	Mobile Number	Manager Name	Email	Cost	Google Maps Link
[VenueID]	[VenueName]	[VenueAddress]	[PostCode]	[MobileNumber]	[ManagerName]	[Email]	[Cost]	[GoogleMapsLink]

Student Table:

Student ID	Student Name	Address	Post Code	Email	Mobile Number
[StudentID]	[StudentName]	[Address]	[PostCode]	[Email]	[MobileNumber]

Course Schedule Table:

Schedule ID	Course ID	Venue ID	Student ID	Achieved ( Yes / No)	Notes
[ScheduleID]	[CourseID]	[VenueID]	[StudentID]	[Achieved]	[Notes]

Step 5: Boyce-Cord Normal Form (BCNF)

BCNF ensures that every non-key attribute depends only on the primary key, eliminating any remaining dependencies and ensuring data consistency.

Resulting Form: Data is in BCNF.

Assessor Table:

Assessor ID	Assessor Name	Assessor Address	Post Code	Mobile Number	Email
[AssessorID]	[AssessorName]	[AssessorAddress]	[PostCode]	[MobileNumber]	[Email]

Course Table:

Course ID	Course Name	Course Date	Duration	Cost	Assessor ID
[CourseID]	[CourseName]	[CourseDate]	[Duration]	[Cost]	[AssessorID]

Venue Table:

Venue ID	Venue Name	Venue Address	Post Code	Mobile Number	Manager Name	Email	Cost	Google Maps Link
[VenueID]	[VenueName]	[VenueAddress]	[PostCode]	[MobileNumber]	[ManagerName]	[Email]	[Cost]	[GoogleMapsLink]

Student Table:

Student ID	Student Name	Address	Post Code	Email	Mobile Number
[StudentID]	[StudentName]	[Address]	[PostCode]	[Email]	[MobileNumber]

Course Schedule Table:

Schedule ID	Course ID	Venue ID	Student ID
[ScheduleID]	[CourseID]	[VenueID]	[StudentID]

Student Achieved Table:

Schedule ID	Achieved ( Yes / No)	Notes
[ScheduleID]	[Achieved]	[Notes]