Republic of the Philippines

NUEVA VIZCAYA STATE UNIVERSITY

Bambang, Nueva Vizcaya
INSTRUCTIONAL MODULE
IM No.05: CPE6-1S-2022-2023

College : College of Engineering

Campus : Bambang Campus

DEGREE PROGRAM BSCPE COURSE NO. CPE6

SPECIALIZATION Information Sys. COURSE TITLE Database Management System
YEAR LEVEL 2nd Year TIME FRAME 3hrs WK NO. 6 IM NO. 05

I. UNIT TITLE/CHAPTER TITLE

Normalization
II. LESSON TITLE
1. Constraints
2. Anomalies
3. Normal Forms
4. Dependencies

III. LESSON OVERVIEW

We need to ensure that the table and relationship structures created will allow data to be stored efficiently
with no unnecessary data redundancy and with minimal risk of inconsistent data.

Normalization of data structure, an important element of conceptual design, can help to ensure that these
goals are met. In addition, some special characteristics commonly found in business related
organizational pose special challenges in the design of databases. In particular, computed and summary
data and attributes whose values change over time require special attention in the conceptual design
process. Finally, there is a need to describe individual attributes in greater detail than is provided by the
E-R model or data structure diagrams. In particular, the domain of each attribute needs to be fully
described so that all restrictions on valid values of attributes are captured during conceptual design. .
IV. DESIRED LEARNING OUTCOMES
At the end of the lessons students are able to:
1. Identify the various normal forms from first normal form through third normal form.
2. Describe the insertion, modification, and deletion anomalies associated with tables that are not fully
normalized.
3. Describe the domain of an attribute appropriately, including the specification of any types of domain
restrictions that might apply.
V. LESSON CONTENT
1. Introduction
a. Normalization - process of evaluating table structures and reorganizing them as necessary to
produce a set of stable, well-structured relations.

b. Relation - two-dimensional array that has a single-valued entry in each cell, has no duplicate
rows, and has columns whose meaning is identical across all rows.

c. Normal Form - a state of a relation that corresponds to the type of dependencies that remain in
the relation.

Properties of a Relation
1. Entries in columns are atomic
2. Entries in columns are from the same domain
3. Each row is unique.
4. The sequence of columns (left to right) is insignificant.
5. The sequence of row (top to bottom) is insignificant

NVSU-FR-ICD-05-00 (081220) Page 1 of 10

“In accordance with Section 185, Fair Use of Copyrighted Work of
Republic Act 8293, the copyrighted works included in this material may
be reproduced for educational purposes only and not for commercial distribution”
 Republic of the Philippines
NUEVA VIZCAYA STATE UNIVERSITY
Bambang, Nueva Vizcaya
INSTRUCTIONAL MODULE
IM No.05: CPE6-1S-2022-2023
2. Integrity Constraints
a. Domain Constraints define the type, length, format, and allowable values for individual data
items.

b. Referential Integrity Constraints a foreign key may have either a null entry - as long as it is not
part of its table's primary key or an entry that matches the primary key value in a table to which it
is related. (Every non-null foreign key value must reference an existing primary key value)

c. Primary Key Constraints all primary key entries are unique, and no part of the primary key may
be null

3. Anomaly A condition that may prevent the storage or retention of relevant data or may create the
potential for inconsistent data.

COURSE
Employee-ID Course-ID Fee
E130 C200 75.00
E200 C300 100.00
E250 C200 75.00
E425 C400 150.00
E500 C300 100.00
E575 C500 50.00

Types of Anomalies:

a. Insertion Anomaly - Suppose a new course (C600) is offered by the company. This course
cannot be added to the table until at least one employee has enrolled for the course. (Since each
row of the table must contain an Employee-ID)

b. Deletion Anomaly - Suppose that employee E425 decided to withdraw from course C400.
Since that employee is the only enrolled for this course, if this row is deleted from the table, we lose
the information that the fee for course C400 is 150.00.

c. Modification Anomaly - Suppose that the fee for course C200 is to be increased from 75.00 to
100.00. This modification must be made in each of the rows in which course C200 appears, else
the data will be inconsistent.
(Two occurrences of course C200 are shown in table COURSE)

Types of Keys

a. Superkey is an attribute or set of attributes that uniquely identifies an entity or a record.

b. Candidate Key is a superkey where no proper subset is a superkey.
c. Primary Key is a candidate key selected to be the identifier of a record. Cannot contain null
entries.
d. Secondary key an attribute (or combination of attributes) used strictly for data retrieval
purposes.
e. Foreign Key - an attribute which is a primary key in one relation but a non-key attribute in a
relation.

NVSU-FR-ICD-05-00 (081220) Page 2 of 10

“In accordance with Section 185, Fair Use of Copyrighted Work of
Republic Act 8293, the copyrighted works included in this material may
be reproduced for educational purposes only and not for commercial distribution”
 Republic of the Philippines
NUEVA VIZCAYA STATE UNIVERSITY
Bambang, Nueva Vizcaya
INSTRUCTIONAL MODULE
IM No.05: CPE6-1S-2022-2023
4. Dependency is a relation between two or more attributes

a. Functional Dependency: X functionally determines Y if there is only one possible value for
Y associated with each value of X.
Represented as: X→Y
Common examples of functional dependencies are the following:
1. SSN → NAME, ADDRESS, BIRTHDATE: A person's name, address, and birth date are
functionally dependent on that person's Social Security number.

2. VIN → MAKE, MODEL, COLOR: The make, model and color of a vehicle are functionally
dependent on the vehicle identification number.

3. ISBN → TITLE: The title of a book is functionally dependent on the book's international
standard book number (ISBN)

Determinant - The attribute on the left-hand side of the arrow in a functional dependency;
X is a determinant in the following functional dependency: X → Y
b. Partial Dependency - A dependency in which the value of a non-key attribute can be uniquely
identified using only part of a concatenated primary key

*Pk (Primary Key) *Nk (Non Primary Key)

c. Transitive Dependency - A dependency in which the value of one non-key attributes is

functionally determined by another non-key attribute

*Pk (Primary Key) *Nk (Non Primary Key)

5. Steps in Normalization

a. First Normal Form (lNF) - any repeating groups have been removed, so that there is a single
value at the intersection of each row and column of the table.
b. Second Normal Form(2NF) - any partial functional dependencies have been removed
c. Third Normal Form (3NF) - any transitive dependencies have been removed
d. Boyce-Codd Normal Form (BCNF) - any remaining anomalies that result from functional
dependencies have been removed.
e. Fourth Normal Form (4NF) - any multi-valued dependencies have been removed.
f. Fifth Normal Form (5NF) - any remaining anomalies have been removed

NVSU-FR-ICD-05-00 (081220) Page 3 of 10

“In accordance with Section 185, Fair Use of Copyrighted Work of
Republic Act 8293, the copyrighted works included in this material may
be reproduced for educational purposes only and not for commercial distribution”
 Republic of the Philippines
NUEVA VIZCAYA STATE UNIVERSITY
Bambang, Nueva Vizcaya
INSTRUCTIONAL MODULE
IM No.05: CPE6-1S-2022-2023

ILLUSTRATIVE EXAMPLE

XYZ UNIVERSITY
REPORT ON GRADES
2ND TRIMESTER, SY 2020-2021
ID Number : 9729344
Name : Emily Williams
Course : BSIT
Course Code Description Instructor Instructor Code Units Grade
CSC 222 Database Management Einstein Eins 3 85
CSC 223 Programming Languages Mozart Moz 3 82
Math 4 Statistics Bohr Bohr 3 84

Normalize the data in the Report on Grades by constructing a table and enter sample data from
the report. Notice that the attributes in the header are entered in the left-most columns of the table
below (Table III-A). While the attributes in the body of the report are entered in the right-most
columns.
1. First Normal Form. Table III-A contains repeating groups: the course data (starting with
COURSE CODE and extending to the remaining columns) repeat for each student. As a
result, there are multiple values at the intersections of certain rows and columns. For example,
there are three values for COURSE CODE (CSC 222, CSC 223 and Math 4) for Williams
Table III – A
ID Course Instructor
Name Course Description Instructor Units Grade
number Code Code
9729344 Emily BSIT CSC 222 Database Einstein Eins 3 85
Williams Management
CSC 223 Programming Mozart Moz 3 82
Languages
Math 4 Statistics Bohr Bohr 3 84
9722565 Steve BSIM Physics College White Wht 3 79
Roberts 1 Physics
CSC 222 Database Einstein Eins 3 81
Management

To convert Table III-A to a relation in first normal form, we must remove the repeating groups,
which we accomplish easily by extending downward the data in columns 1-3 for each student to
fill the remaining rows. Table III-B shows the result.

Table III - B – in 1NF

ID Name Course Course Description Instructor Instructor Units Grade
number Code Code

9729344 Emily BSIT CSC 222 Database Einstein Eins 3 85

Williams Management
9729344 Emily BSIT CSC 223 Programming Mozart Moz 3 82
Williams Languages
9729344 Emily BSIT Math 4 Statistics Bohr Bohr 3 84
Williams
9722565 Steve BSIM Physics College White Wht 3 79
Roberts 1 Physics
9722565 Steve BSIM CSC 222 Database Einstein Eins 3 81
Roberts Management

NVSU-FR-ICD-05-00 (081220) Page 4 of 10

“In accordance with Section 185, Fair Use of Copyrighted Work of
Republic Act 8293, the copyrighted works included in this material may
be reproduced for educational purposes only and not for commercial distribution”
 Republic of the Philippines
NUEVA VIZCAYA STATE UNIVERSITY
Bambang, Nueva Vizcaya
INSTRUCTIONAL MODULE
IM No.05: CPE6-1S-2022-2023
GRADE_REPORT (ID NUMBER, NAME, COURSE, COURSE CODE, DESCRIPTION,
INSTRUCTOR, INSTRUCTOR CODE ,UNITS, GRADE)

Table III - B is in first normal form, since there is a single data value at the intersection of each
row and column, but if you will examine the table you will find much of data redundancy. This
table is subject to anomalies discussed earlier.
a. Insertion Anomaly - we cannot insert a new course into the table until a student enrolled
that course.
b. Modification Anomaly - if student Steve Roberts changes his course to BSIT from BSIM,
we must record this fact in several rows in the table.

2. Second Normal Form. To further normalize this relation, we must analyze the functional
dependencies and select a primary key for the relation. The following are the functional
dependencies:

a. ID NUMBER → NAME, COURSE

b. COURSE CODE → DESCRIPTION, UNITS, INSTRUCTOR, INSTRUCTOR CODE
c. ID NUMBER, COURSE CODE → GRADE

A Candidate key for the relation consists of a set of determinants that uniquely determines the
right-hand-side attributes, and is non-redundant. Inspecting the above functional dependencies
shows that there is one candidate key for this relation, which consists of the attributes ID NUMBER
AND COURSE CODE.
Notice that in Table III-B, ID NUMBER AND COURSE CODE are underlined as primary key.
To transform the relation in Table III-B to second normal form, we must remove the partial
dependencies. Examining Table III - B, we notice that we can create three new relations from the
one shown.

STUDENT (ID NUMBER, NAME, COURSE)

COURSE_INSTRUCTOR (COURSE CODE, DESCRIPTION, INSTRUCTOR, INSTRUCTOR
CODE, UNITS)
REGISTRATION (ID NUMBER, COURSE CODE, GRADE)

The corresponding tables will be generated below

STUDENT TABLE
ID Number Name Course
Emily
9729344 BSIT
Williams
Steve
9722565 BSIM
Roberts

NVSU-FR-ICD-05-00 (081220) Page 5 of 10

“In accordance with Section 185, Fair Use of Copyrighted Work of
Republic Act 8293, the copyrighted works included in this material may
be reproduced for educational purposes only and not for commercial distribution”
 Republic of the Philippines
NUEVA VIZCAYA STATE UNIVERSITY
Bambang, Nueva Vizcaya
INSTRUCTIONAL MODULE
IM No.05: CPE6-1S-2022-2023

REGISTRATION TABLE
ID Number Course Code Grade
9729344 CSC 222 85

9729344 CSC 223 82

9729344 Math 4 84
9722565 Phys 1 79
9722565 CSC 222 81

COURSE_INSTRUCTOR
Course Description Instructor Instructor Units
code Code
Database
CSC 222 Einstein Eins 3
Management
CSC 223 Programming Languages Mozart Moz 3
Math 4 Statistics Bohr Bohr 3
Phys 1 College Physics 1 White Wht 3
Database
CSC 222 Binstein Bins 3
Management

3. Third Normal Form. If you notice, STUDENT and REGISTRATION are already in third normal
form. The other relation, COURSE_INSTRUCTOR, is in second normal form. As a result, it is
subject to the same anomalies we discussed earlier.
a. Modification Anomaly. If we wish to change the Instructor for CSC 222, then we must make
changes in multiple rows.
b. Deletion Anomaly. Suppose we want to delete the course Math 4 from the table. If there is
only one row for Math 4, we may lose the information about the instructor.

The anomalies in the COURSE_INSTRUCTOR relation exist because data concerning the entity
INSTRUCTOR are “hidden” within COURSE_INSTRUCTOR. The functional dependencies in
this relation are the following:

COURSE CODE → DESCRIPTION, UNITS, INSTRUCTOR CODE , INSTRUCTOR

INSTRUCTOR CODE → INSTRUCTOR
Since INSTRUCTOR is functionally dependent on INSTRUCTOR CODE (a nonkey attribute),
this relation is an example of a transitive dependency. Therefore, we conclude that COURSE
INSTRUCTOR is not yet in third normal form.

NVSU-FR-ICD-05-00 (081220) Page 6 of 10

“In accordance with Section 185, Fair Use of Copyrighted Work of
Republic Act 8293, the copyrighted works included in this material may
be reproduced for educational purposes only and not for commercial distribution”
 Republic of the Philippines
NUEVA VIZCAYA STATE UNIVERSITY
Bambang, Nueva Vizcaya
INSTRUCTIONAL MODULE
IM No.05: CPE6-1S-2022-2023

To remove the transitive dependency, we divide it into two relations: COURSE and
INSTRUCTOR.
COURSE INSTRUCTOR
Course Description Instructor Units Instructor Instructor
Code Code Code
CSC 222 Database Eins 3 Einstein Eins
Management
Programming
CSC 223 Moz 3 Mozart Moz
Languages
Math 4 Statistics Bohr 3 Bohr Bohr
Phys 1 Colleae Physics 1 Wht 3 White Wht
CSC 222 Database Bins 3 Einstein Eins
Management

Below are the 3NF tables generated from Report on Grades:

The 3NF relations are free of anomalies described earlier. Since each entity is described in a
separate relation, we can easily insert or delete data concerning that entity without reference to
other entities. Also, update to the data for a particular entity are easy to accomplish, since changes
are confined to a single row within a relation.

4. BOYCE-CODD Normal Form (BCNF) is a special case of 3NF that requires every determinant
in the table be a candidate key. BCNF can be violated only if the table contains more than one
candidate key.

NVSU-FR-ICD-05-00 (081220) Page 7 of 10

“In accordance with Section 185, Fair Use of Copyrighted Work of
Republic Act 8293, the copyrighted works included in this material may
be reproduced for educational purposes only and not for commercial distribution”
 Republic of the Philippines
NUEVA VIZCAYA STATE UNIVERSITY
Bambang, Nueva Vizcaya
INSTRUCTIONAL MODULE
IM No.05: CPE6-1S-2022-2023

As you examine, note these functional dependencies:

A, B → C, D, E
C→B

The table structure has no partial dependencies, nor does it contain

transitive dependencies. The condition C → B indicates that a nonkey attribute
determines part of a primary key – and this dependency is not transitive. Clearly,
the table structure meets the 3NF requirements but fails to meet the BCNF
requirements.

To convert the table structure that meets both 3NF and in BCNF, first
change the primary key (A, C).

At this point, the table is in 1NF because it contains partial dependency. Next, follow our
standard decomposition procedure.

Sample problem:
• Each CLASS_CODE identifies a class uniquely.
• A student can take many classes, therefore can earn many grades.
• A staff member can teach many classes, but each class is taught by only one staff member.

Denormalization although the creation of normalized relations is an important database design goal,
it is only one of many such goals. Good database design also considers the processing requirement.
As tables are normalized the number of database tables expands, takes additional disk I/O operations
and processing logic, thereby reducing system speed.

Consequently, there may be very occasional circumstances that allow some degree of
denormalization in order to increase processing speed.

NVSU-FR-ICD-05-00 (081220) Page 8 of 10

“In accordance with Section 185, Fair Use of Copyrighted Work of
Republic Act 8293, the copyrighted works included in this material may
be reproduced for educational purposes only and not for commercial distribution”
 Republic of the Philippines
NUEVA VIZCAYA STATE UNIVERSITY
Bambang, Nueva Vizcaya
INSTRUCTIONAL MODULE
IM No.05: CPE6-1S-2022-2023
Summary

Normalization is the process of grouping attributes into well-structured relations.

Normal form is a state of a relation that corresponds to the type of dependencies that remain in
the relation.
Steps in Normalization

1. First Normal form (1NF) – Any repeating groups have been removed, so that there is a single value
at the intersection of each row and column of the table.
2. Second Normal form (2NF) –Any partial functional dependencies have been removed.
3. Third Normal form (3NF) – Any transitive dependencies have been removed.
4. Boyce-Codd Normal form (BCNF) – Any remaining anomalies that result from functional
dependencies have been removed.
5. Fourth Normal form (4NF) – Any multi-valued dependencies have been removed.
6. Fifth Normal form (5NF) – Any remaining anomalies have been removed.
Additional normal form:

Domain/Key normal form (D/KNF) – assures that all of the other normal forms have been attained
and that a relation can have no remaining anomalies.

NVSU-FR-ICD-05-00 (081220) Page 9 of 10

“In accordance with Section 185, Fair Use of Copyrighted Work of
Republic Act 8293, the copyrighted works included in this material may
be reproduced for educational purposes only and not for commercial distribution”
 Republic of the Philippines
NUEVA VIZCAYA STATE UNIVERSITY
Bambang, Nueva Vizcaya
INSTRUCTIONAL MODULE
IM No.05: CPE6-1S-2022-2023
VI. EVALUATION (Note: Not to be included in the student’s copy of the IM)

VII. LEARNING ACTIVITIES: Provide what is asked in the problem(s) below. Use long bond papers,
Handwritten (Deadline: To be posted)

1. The table shows a class list for the XYZ UNIVERSITY, convert this user view to a set of 3NF relations.
Assume the following:
a. An instructor has a unique code.
b. A student has a unique course.
c. A course has a unique description.

XYZ UNIVERSITY
First Semester, SY 2020-2021
Course No. : CoE 412
Description : Operating Systems
Instructor Code : 0001
Instructor Name : Mr. X
ID NUMBER STUDENT NAME COURSE
38214 BRIGHT BSCpE
40875 CORTEZ BSECE
51893 EDWARD BSIT

VIII. ASSIGNMENT: Provide what is asked in the problem(s) below. Use long bond papers, handwritten.
(Deadline: To be posted)
1. Break up the dependency diagram to create new dependency diagrams in 3NF

IX. REFERENCES
Books
Begg, C., Connolly, T. (2015). Database Systems: A Practical Approach to Design, Implementation, and
Management. Pearson Education Limited.
Churcher, C. (2014). Beginning Database Design: From Novice to Professional., Apress Publishing.
Coronel, M., Coronel R. (2011). Database Systems: Design Implementation and Management, Cengage Learning.
Online References:
What is Normalization? 1NF, 2NF, 3NF, BCNF Database (n.d). Retrieved from https://www.guru99.com
/database-normalization.html

Normalization of Database (n.d). Retrieved from https://www.studytonight.com/dbms/database-

normalization.php

DBMS Tutorial (n.d.). Retrieved from https://www.w3schools.in/dbms/

DBMS Tutorial (n.d.). Retrieved from https://www.javatpoint.com/dbms-tutorial

NVSU-FR-ICD-05-00 (081220) Page 10 of 10

“In accordance with Section 185, Fair Use of Copyrighted Work of
Republic Act 8293, the copyrighted works included in this material may
be reproduced for educational purposes only and not for commercial distribution”