TUTORIAL NORMALIZATION
WIA2001/WIB2001
1. Explain the purpose of data normalization and describe the main steps in the
normalization process.
To evaluate and correct the table structures to minimize data redundancies.
To reduce data abnormalizes.
To assign attributes to tables based on determination.
1NF:
Ensure data is in table format, no repeating groups (single value attributes) and
primary key identified.
2NF: Ensure there is no partial dependencies among entities within the same table.
3NF: Ensure there is no transitive dependencies among entities within the same table.
The process of organising data so that it avoids data inconsistency prevents update anomalies.
The main steps include: 1NF to remove repeating groups; 2NF to remove partial dependencies;
3NF to remove transitive dependencies and BCNF to remove remaining anomalies from
dependencies.
2. Explain the concepts of:
i. Full functional dependency
- All non-key attributes depend on the primary key within the same table.
- Full functional dependency occurs when a non-key attribute (an attribute that is not
part of the primary key) is functionally dependent on the entire primary key.
- In other words, for a relation (table) to have full functional dependency, it means
that no proper subset of the primary key can determine the non-key attribute.
- This concept is closely related to the Second Normal Form (2NF) in the
normalization process, where we aim to eliminate partial functional dependencies
by ensuring that non-key attributes depend on the entire primary key, not just a part
of it.
ii. Partial functional dependency
- Non-key attributes depend on part of the primary key within the same table.
- Partial functional dependency occurs when a non-key attribute is functionally
dependent on only a part of the primary key.
- In this case, the non-key attribute depends on a subset of the primary key, but not on
the entire key.
- Partial functional dependencies can lead to data anomalies and inconsistencies, and
they are a violation of database normalization principles. That's why they need to be
resolved during the normalization process, typically by breaking the table into
multiple tables or reorganizing the data.
iii. Transitive functional dependency
- Non-key attributes depend on another non-key attributes within a table.
- Transitive functional dependency occurs when a non-key attribute depends on
another non-key attribute, which, in turn, depends on the primary key.
- In this situation, there is an indirect relationship between the non-key attribute and
the primary key through other non-key attributes.
- Transitive dependencies can also lead to data anomalies and are addressed in the
normalization process, specifically in the Third Normal Form (3NF). To eliminate
transitive dependencies, you may need to break the table into multiple tables or
restructure the data so that the dependencies are directly from the non-key
attributes to the primary key.
3. Table below shows the invoice for a patient in a clinic with InvNo and MedID as the
primary keys.
INVOICETREATMENT
InvNo InvDate PatID PatName PatAdd MedID MedName Desc Type Price
10-01- Marzuki bin Petaling
V023 P102 M024 Cetirizine Flu Tablet 26.00
2019 Ahmad Jaya
10-01- Marzuki bin Petaling DCM
V023 P102 M085 Cough Liquid 17.00
2019 Ahmad Jaya Expectorant
10-01- Marzuki bin Petaling
V023 P102 M001 Paracetamol Fever Tablet 20.00
2019 Ahmad Jaya
19-01- Aida bt
V014 P104 Bangsar M001 Paracetamol Fever Tablet 24.00
2019 Razali
19-01- Aida bt Painkill
V014 P104 Bangsar M011 Ibuprofen Tablet 15.00
2019 Razali er
19-01- Aida bt DCM
V014 P104 Bangsar M085 Cough Liquid 27.00
2019 Razali Expectorant
a. Identify all functional dependencies exist from the above table.
b. Produce the First Normal Form (1NF), Second Normal Form (2NF) and Third
Normal Form (3NF) of this table by normalization
Answer:
First Normal Form (1NF):
Contain atomic (indivisible) values / single value. Each row must be unique.
Same table as question.
Second Normal Form (2NF): Remove partial dependencies.
InvNo → InvDate, PatID, PatName, PatAdd
MedID → MedName, Desc, Type, Price
PatID → PatName, PatAdd
Third Normal Form (3NF): Remove transitive dependencies.
InvNo → InvDate, PatID, PatName, PatAdd
PatID → PatName, PatAdd
Table 1: Invoices
InvNo (Primary Key)
InvDate
PatID (Foreign Key)
Table 2: Patients
PatID (Primary Key)
PatName
PatAdd
Table 3: Medications
MedID (Primary Key)
MedName
Desc
Type
Price
4. Given two relational schemas as below:
Enrollment (StudentID, courseID, course_Instructor, Student_Name,
Student_Degree, Student_add, course_Name, Instructor_Name, Instructor_Off,
Grade)
StudentSupervisor (StudentID, specialization, supervisor)
Perform the normalization process to 1NF, 2NF and 3NF.
Answer:
Unnormalized Form (UNF) & First Normal Form (1NF):
Contain atomic (indivisible) values / single value. Each row must be unique.
Same table as question. (no repeating groups, and each cell contains single values)
Second Normal Form (2NF): Remove partial dependencies.
StudentID → Student_Name, Student_Degree, Student_add
CourseID → course_Instructor, course_Name, Instructor_Name, Instructor_Off
StudentID, CourseID → Grade
StudentID, specialization → supervisor
Third Normal Form (3NF): Remove transitive dependencies.
StudentID → Student_Name, Student_Degree, Student_add
CourseID → course_Instructor, course_Name
course_Instructor → Instructor_Name, Instructor_Off
StudentID, CourseID → Grade
StudentID, specialization → supervisor
Table 1: Students
StudentID (Primary Key)
Student_Name
Student_Degree
Student_add
Table 2: Courses
courseID (Primary Key)
course_Instructor
course_Name
Instructor_Name
Instructor_Off
Table 3:CourseInstructor
course_Instructor (Primary Key)
Instructor_Name
Instructor_Off
Table 4: StudentSupervisors
StudentID (Primary Key, Foreign Key referencing Students)
Specialization (Primary Key)
Supervisor
Table 5: StudentGrade
StudentID (Primary Key, Foreign Key referencing Students)
CourseID (Primary Key, Foreign Key referencing Courses)
Grade
5. Based on the report below, normalize table from UNF, 1NF, 2NF and 3NF.
Unnormalized Form (UNF) : There is no row order in relational database.
Pet → PetID, PetName, PetType, PerAge, Owner, [VisitDate, Procedure]
First Normal Form (1NF):
A procedure may occur on multiple dates, there visitDate is included as part of the key.
Pet → PetID, PetName, PetType, PerAge, Owner
Pet_Visit → PetID, VisitDate, ProcedureNo, ProcedureDescription
Second Normal Form (2NF): Remove partial dependencies.
Pet → PetID, PetName, PetType, PerAge, Owner
Pet_Visit → PetID, VisitDate,
Procedure → ProcedureNo, ProcedureDescription
Third Normal Form (3NF): Remove transitive dependencies.
Same as 2NF
