Database Normalization Project for ACE Training: 1NF, 2NF, and 3NF

This project presents a comprehensive solution for normalizing a database from unnormalized form (UNF) to the third normal form (3NF), based on a case study from ACE Training. The solution begins with an UNF table, detailing student and course information. The project meticulously applies the no...Read More

COVER PAGE

Contents
UNF Table....................................................................................................................................................3
1NF..............................................................................................................................................................3
2NF..............................................................................................................................................................4
3NF..............................................................................................................................................................5
References...................................................................................................................................................7

UNF Table
The table in un-normalized form as per the case study;
 StudentLetter (studentNumber, name, address, registrationDate, enrollmentDate, balance,
amountPaid, course, startDate, duration, weeklySession, location, cost, tutorName, email, tel )
1NF
Normalization to 1NF involves analyzing the table in UNF and eliminating any repeating groups in the
table and ensuring each column has a single valued attribute (Kaula, 2007). Considering the student
letter table shown in the section above, eliminating repeating groups will result to the following tables
 StudentLetter (studentNumber, firstName, lastName, street , town, postcode, registrationDate,
enrollmentDate, balance, amountPaid)
 Course (course, startDate, location, cost , studentNumber)
 WeeklySessions (course, Session, location)
 Tutor (tutorTitle, Name, email, tel , course)
The tables in 1NF can better be illustrated using the data in the case study as shown below;
 StudentLetter
Studen
tNumb
er
firstNa
me
LastNa
me
street Town Postco
de
registrationD
ate
enrollmentDate Balanc
e
amountP
aid
58547 Peter Parker 34 Derby
way
Liverpo
ol
L34
5WE
07 November
2013
04 November 2013 4400 1000
 Course
Course startDate Duration Cost studentNumber
Database
Technology
13/01/2014 11 weeks 3500 58547
Operating
Systems
20/01/2014 6 weeks 1000 58547
 WeeklySessions
Course Session Location
Database Technology Monday at 9am RM201
Database Technology Wednesday at 11am RM201
Operating Systems Monday at 2pm LB101
Operating Systems Tuesday at 10am LB211
 Tutor

Tutor Name email tel course
Dr Williams [e-mail] 0151233331 Operating
System
Dr Henderson [e-mail].u
k
0151 233 332 Database
Technology
2NF
Normalization to 2NF involves taking all the tables achieved while normalizing to 1NF and eliminating all
partial dependencies such that each table has only one candidate key. Considering the tables achieved
in the section above, the following has been done to normalize them to 2NF. A partial dependency
occurs when two columns in a table can be combined to form a candidate thus the table having more
than one candidate key
 StudentLetter (studentNumber, firstName, lastName, street , town, postcode, registrationDate,
enrollmentDate, balance, amountPaid)
This table does have any partial dependencies as it only has one candidate key which is the
studentNumber column.
 Course (course, startDate, location, cost , studentNumber)
This table does not have partial dependencies as it only has one candidate key which is the
course column
 WeeklySessions (course, Session, location)
This table does not have any partial dependencies thus its already in 2NF. The candidate key of
this table is a combination of course and session.
 Tutor (tutorTitle, Name, email, tel , course)
This table is already in 2NF as it does not have any partial dependencies. The candidate key in
this table is a combination of tutorTitle and name
The data in the tables in 2NF is shown below;
 StudentLetter
Student
Number
firstNa
me
LastName street Town Postcode registrationDate enrollmentDate Balance amountPaid
58547 Peter Parker 34 Derby
way
Liverp
ool
L34 5WE 07 November
2013
04 November
2013
4400 1000
 Course
Course startDate Duration Cost studentNumber
Database
Technology
13/01/2014 11 weeks 3500 58547
Operating 20/01/2014 6 weeks 1000 58547

Systems
 WeeklySessions
Course Session Location
Database Technology Monday at 9am RM201
Database Technology Wednesday at 11am RM201
Operating Systems Monday at 2pm LB101
Operating Systems Tuesday at 10am LB211
 Tutor
Tutor Name email tel course
Dr Williams [e-mail] 0151233331 Operating
System
Dr Henderson [e-mail].u
k
0151 233 332 Database
Technology
3NF
Normalization to 3NF involves taking the tables achieved as a result of normalization to 2NF and
eliminating all transitive dependencies such that each table has only one key attribute that will
functionally determine all the other non-key attributes (Milamila, 2014). Transitive dependency occurs
when a table has one or more key attributes that can functionally determine other non-key attributes.
Taking the entities achieved as a result of normalization to 2NF, the following operations can be
performed to normalize them to 3NF.
 StudentLetter (studentNumber, firstName, lastName, street, town, postcode, registrationDate,
enrollmentDate, balance, amountPaid)
In this relation the following transitive dependencies exist;
o studentNumberfirstName, lastName, steet, town, postcode, registrationDate, balance
o studentNumberenrollmentDate
o studentNUmberamountPaid
Eliminating the transitive dependencies will result to the following entities;
o Student (StudentNumber, firstName, lastName, street, town, postcode,
registrationDate, balance)
o Enrollment (studentNumber, course, enrollmentDate)
o Payment (paymentID, studentNumber, amountPaid)
 Course (course, startDate, location, cost , studentNumber)
This table does not have any transitive dependencies as it has only one key attribute which is
course which functionally determines all the other non-key attributes in the course table.
o Course (course, startDate, location, cost , studentNumber)
 WeeklySessions (course, Session, location)

This table does not have any transitive dependencies thus its already in 3NF. The primary key of
this table is a combination of course, session and location.
o WeeklySessions (course, Session, location)
 Tutor (tutorTitle, Name, email, tel , course)
This table is already in 3NF as it does not have any transitive dependencies. The primary key in
this table is a combination of tutorTitle and name which functionally determines the other non-
key attributes.
The entities in 3NF can be better illustrated using the data in the case study.
 Student
Student
Number
firstNa
me
LastName street Town Postcode registrationDate Balance
58547 Peter Parker 34 Derby
way
Liverp
ool
L34 5WE 07 November
2013
4400
 Enrollment
StudentNumber Course enrollmentDate
58547 Database Technology 04 November 2013
58547 Operating System 04 November 2013
 Payment
PaymentID StudentNumber amountPaid
1 58547 1000
 Course
Course startDate Duration Cost
Database
Technology
13/01/2014 11 weeks 3500
Operating
Systems
20/01/2014 6 weeks 1000
 WeeklySessions
Course Session Location Course
Database Technology Monday at 9am RM201 Database
Technology

Database Technology Wednesday at 11am RM201 Database
Technology
Operating Systems Monday at 2pm LB101 Operating Systems
Operating Systems Tuesday at 10am LB211 Operating Systems
 Tutor
Tutor Name email tel course
Dr Williams [e-mail] 0151233331 Operating
System
Dr Henderson [e-mail].u
k
0151 233 332 Database
Technology
References
Kaula, R. (2007). Normalizing with Entity Relationship Diagramming. [online] The Data Administration
Newsletter. Available at: http://tdan.com/normalizing-with-entity-relationship-diagramming/4583
[Accessed 3 Feb. 2020].
Millamila, M. (2014). Entity Relationship Diagrams and Normalization. [online] prezi. Available at:
https://prezi.com/tunrndt0nin4/entity-relationship-diagrams-and-normalization/ [Accessed 3 Feb. 2020]