Database Systems:

Design,
Implementation,
and Management,
14e
Module 6: Normalization of
Database Tables

Coronel, Carlos and Morris, Steven, Database Systems: Design, Implementation, and Management, 14 Edition. © 2023 Cengage.
All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part. 1
 Chapter Objectives

By the end of this chapter, you should be able to:

1. Explain normalization and its role in the database design process

2. Identify each of the normal forms: 1NF, 2NF, 3NF, BCNF, 4NF, and 5 NF

3. Explain how normal forms can be transformed from lower normal forms to higher
normal forms

4. Apply normalization rules to evaluate and correct table structures

5. Identify situations that require denormalization to generate information efficiently

6. Use a data-modeling checklist to check that the ERD meets a set of minimum
requirements

Coronel, Carlos and Morris, Steven, Database Systems: Design, Implementation, and Management, 14 Edition. © 2023 Cengage.
All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part. 2
 Database Tables and Normalization (1 of 2)

• Normalization is a process for evaluating and correcting table structures to minimize

data redundancies
− It reduces the likelihood of data anomalies
− Assigns attributes to tables based on determination

• Normalization works through a series of stages called normal forms and the first
three are described as follows:
− First normal form (1NF)
− Second normal form (2NF)
− Third normal form (3NF)

Coronel, Carlos and Morris, Steven, Database Systems: Design, Implementation, and Management, 14 Edition. © 2023 Cengage.
All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part. 3
Database Tables and Normalization (2 of 2)

• From a structural point of view, higher normal forms are better than lower normal
forms
− For most purposes in business database design, 3NF is as high as you need to go
in the normalization process

• Denormalization produces a lower normal form

− The result of denormalization is increased performance but greater data
redundancy

Coronel, Carlos and Morris, Steven, Database Systems: Design, Implementation, and Management, 14 Edition. © 2023 Cengage.
All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part. 4
 The Need for Normalization

• Database designers commonly use normalization in the following two situations:

− When designing a new database structure
− To analyze the relationship among the attributes within each entity and determine
if the structure can be improved through normalization

• The main goal of normalization is to eliminate data anomalies by eliminating

unnecessary or unwanted data redundancies

• Normalization uses the concept of functional dependencies to identify which

attribute determines other attributes

Coronel, Carlos and Morris, Steven, Database Systems: Design, Implementation, and Management, 14 Edition. © 2023 Cengage.
All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part. 5
 The Normalization Process (1 of 4)

• The objective of normalization is to ensure that each table conforms to the concept
of well-formed relations and has the following characteristics:
− Each table represents a single subject
− Each row/column intersection contains only one value and not a group of values
− No data item will be unnecessarily stored in more than one table
− All nonprime attributes in a table are dependent on the primary key
− Each table has no insertion, update, or deletion anomalies

Coronel, Carlos and Morris, Steven, Database Systems: Design, Implementation, and Management, 14 Edition. © 2023 Cengage.
All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part. 6
 The Normalization Process (2 of 4)

• An objective is to ensure that all tables are in at least in 3NF

− Higher forms, such as 5NF and DKNF are not likely to be encountered in business
environment

• The normalization process works one relation at a time, identifying the dependencies
of a relation (table)

Coronel, Carlos and Morris, Steven, Database Systems: Design, Implementation, and Management, 14 Edition. © 2023 Cengage.
All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part. 7
 The Normalization Process (3 of 4)
Normal Forms
Table 6.2
Normal Form Characteristic Section
First normal form (1NF) Table format, no repeating groups, and PK 6-3a
identified
Second normal form (2NF) 1NF and no partial dependencies 6-3b
Third normal form (3NF) 2NF and no transitive dependencies 6-3c
Boyce-Codd normal form (BCNF) 3NF and every determinant is a candidate key 6-6a
(special case of 3NF)
Fourth normal form (4NF) BCNF and no independent multivalued 6-6b
dependencies
Fifth normal form (5NF or PJNF) 4NF and cannot have lossless decomposition into 6-6c
smaller tables

Coronel, Carlos and Morris, Steven, Database Systems: Design, Implementation, and Management, 14 Edition. © 2023 Cengage.
All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part. 8
 Knowledge Check Activity 6-1: Answer

• What is normalization?
• Answer: Normalization is the process for assigning attributes to entities.
Properly executed, the normalization process eliminates uncontrolled
data redundancies, thus eliminating the data anomalies and the data
integrity problems that are produced by such redundancies.

Coronel, Carlos and Morris, Steven, Database Systems: Design, Implementation, and Management, 14 Edition. © 2023 Cengage.
All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part. 9
 Database Design - Recap
• When we design a database, how do we know that the design is
“correct”, i.e. it would not create problems when processing the
database?
• Is there a way to verify the correctness of the design?

ER Design
• Provides a set of guidelines, does not result in a unique
database schema
• Does not provide a way of evaluating alternative schemas
Normalization theory provides a mechanism for analyzing and
refining the schema produced by an E-R design

Coronel, Carlos and Morris, Steven, Database Systems: Design, Implementation, and Management, 14 Edition. © 2023 Cengage.
All Rights Reserved. May not be scanned, copied or duplicated, or posted to a10
publicly accessible website, in whole or in part. 10
 Normalization Theory
• Result of E-R analysis need further refinement
• Appropriate decomposition can solve problems
• The underlying theory is referred to as normalization theory and
is based on functional dependencies (and other kinds, like
multivalued dependencies)
• Normalization
– Process for evaluating and correcting table structures to
minimize data redundancies
– Usually involves dividing large tables into smaller (and less
redundant) tables and defining relationships between them
• Works through a series of stages called normal forms:
– First normal form (1NF)
– Second normal form (2NF)
– Third normal form (3NF)
Coronel, Carlos and Morris, Steven, Database Systems: Design, Implementation, and Management, 14 Edition. © 2023 Cengage.
All Rights Reserved. May not be scanned, copied or duplicated, or posted to a11
publicly accessible website, in whole or in part. 11
 How Normalization Supports Database Design

Coronel, Carlos and Morris, Steven, Database Systems: Design, Implementation, and Management, 14 Edition. © 2023 Cengage.
All Rights Reserved. May not be scanned, copied or duplicated, or posted to a12
publicly accessible website, in whole or in part. 12
 Unnormalized Sample Data
Case Scenario:
• A cyber café wants to capture data about its members
• A log book is used to record down the following:
• Entry No, Member email, password, name, phone, IN time and OUT time

An example of a logbook entry

Entry no

*note the duplicate entries

Coronel, Carlos and Morris, Steven, Database Systems: Design, Implementation, and Management, 14 Edition. © 2023 Cengage.
All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole13
or in part. 13
 Unnormalized Design Causes Update
Problems

When the password for Thom Luce was changed, it was

not changed in both his entries.

Which password is the correct password?

Coronel, Carlos and Morris, Steven, Database Systems: Design,Exhibit 4-3: Arcade Database
Implementation, Update Problems
and Management, 14 Edition. © 2023 Cengage.
Due to Duplicate Data
All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole14 or in part. 14
 Normalized Design Eliminates Update Problems

If the database was normalized properly, there would only be one update
done to the password, and the password would only appear in one place.

Coronel, Carlos and Morris, Steven, Database Systems: Design, Implementation, and Management, 14 Edition. © 2023 Cengage.
All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole15
or in part. 15
 Unnormalized Design Creates Insert
Problems

A new member cannot be created unless

they have already made a visit, otherwise
there would be no primary key value.
This field may not be left blank.
Coronel, Carlos and Morris, Steven, Database Systems: Design, Implementation, and Management, 14 Edition. © 2023 Cengage.
All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole16
or in part. 16
 Normalized Design Eliminates the Insert Problem

In a normalized database , the addition of Raymond Frost as a new

member is separate from the logging of his visits.

Coronel, Carlos and Morris, Steven, Database Systems: Design, Implementation, and Management, 14 Edition. © 2023 Cengage.
All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole17
or in part. 17
 Unnormalized Design Creates Delete Problems

If a member makes only one visit, deleting that record will cause the loss
of the member data.

For example, there is only one record to indicate Sean McGann’s

member data.
If we had to delete record 005 (lets say we got the date wrong), then we
would lose all of Sean’s data.
Coronel, Carlos and Morris, Steven, Database Systems: Design,Exhibit 4-7: Arcade Database
Implementation, Delete Problem
and Management, 14 Edition. © 2023 Cengage.
Due to Duplicate Data
All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole18 or in part. 18
Normalized Design Eliminates the Delete Problem

Now, deleting visit 005 would not cause the loss of Sean McGann’s member data
because his visitation data is separate from his member data.

Coronel, Carlos and Morris, Steven, Database Systems: Design, Implementation, and Management, 14 Edition. © 2023 Cengage.
All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole19
or in part. 19
 First Normal Form (1NF)

1NF: A table in which all fields contain a single value.

Lets assume that a member can have more than one phone
number. The example above would be a violation of the 1NF
rule; therefore this table would have to be redesigned.

Coronel, Carlos and Morris, Steven, Database Systems: Design, Implementation, and Management, 14 Edition. © 2023 Cengage.
All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole20
or in part. 20
 Fixing Normalization Violations

Step 1: Tables
• Create new table(s)
• Rename original table if necessary

Step 2: Relationships
• Establish relationships between original and new table(s)

Step 3: Fields
• Transfer fields and rename as needed

Step 4: Keys
• Choose PK and FK for all tables
(PK and FK keys will be discussed in more details in a few slides later)

Coronel, Carlos and Morris, Steven, Database Systems: Design, Implementation, and Management, 14 Edition. © 2023 Cengage.
All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole21
or in part. 21
 Solving a 1NF Violation

Step 1: Tables
- Since the phone number column violated
the 1NF rule, make a new table to hold
phone numbers: DIRECTORY.

Coronel, Carlos and Morris, Steven, Database Systems: Design,Exhibit 4-10: Arcadeand
Implementation, Database Solving 14 Edition. © 2023 Cengage.
Management,
All Rights Reserved. May not be scanned, copied or duplicated, or posted tothe 1NF Violation
a publicly accessible website, in whole22
or in part. 22
 Solving a 1NF Violation

Step 2: Relationships
- A member has multiple phone numbers and
a phone number belongs to one member

Coronel, Carlos and Morris, Steven, Database Systems: Design,Exhibit 4-10: Arcadeand
Implementation, Database Solving 14 Edition. © 2023 Cengage.
Management,
All Rights Reserved. May not be scanned, copied or duplicated, or posted tothe 1NF Violation
a publicly accessible website, in whole23
or in part. 23
 Solving a 1NF Violation

Step 3: Fields

- The phone field is transferred to the

DIRECTORY table

Coronel, Carlos and Morris, Steven, Database Systems: Design,Exhibit 4-10: Arcadeand
Implementation, Database Solving 14 Edition. © 2023 Cengage.
Management,
All Rights Reserved. May not be scanned, copied or duplicated, or posted tothe 1NF Violation
a publicly accessible website, in whole24
or in part. 24
 Solving a 1NF Violation

Step 4: Keys
(keys will be discussed in more details in a few slides later)

- The email column in MEMBER becomes a FK

(MEMBER$email) in DIRECTORY
- The PK in DIRECTORY becomes MEMBER$email
and phone since two members could have the
same phone number
(e.g. two members from the same household).

Coronel, Carlos and Morris, Steven, Database Systems: Design,Exhibit 4-10: Arcadeand
Implementation, Database Solving 14 Edition. © 2023 Cengage.
Management,
All Rights Reserved. May not be scanned, copied or duplicated, or posted tothe 1NF Violation
a publicly accessible website, in whole25
or in part. 25
Tables in 1NF Eliminate Repeating Data Problems

Now all tables have fields

that contain only single
values.

Coronel, Carlos and Morris, Steven, Database Systems: Design,Exhibit 4-11: 1NF Solution
Implementation, with Sample Data
and Management, 14 Edition. © 2023 Cengage.
All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole26 or in part. 26
 Determinants
• An important concept in Normalization is something called a determinant.
• Determinant: a field or group of fields that controls or determines the values in
another field.
• From the previous example, the value of email will determine the values in all
the other fields.

• That is, if you know someone’s email, you can determine the rest of their
information.
– Stated another way, given an email, you will be able to find/retrieve the [EXACTLY
ONE] member’s name, phone, etc.

– A determinant MUST return only ONE value

➢ Given the StudentID ‘ABC123456’, how many [Name, DOB, IC-No] will you
retrieve?
➢ Given the Name ‘Lim Ah Kau’, how many [StudentID, DOB, IC-No] will you
retrieve?
➢ Which is the determinant : StudentID or Name ?
Coronel, Carlos and Morris, Steven, Database Systems: Design, Implementation, and Management, 14 Edition. © 2023 Cengage.
All Rights Reserved. May not be scanned, copied or duplicated, or posted to a27
publicly accessible website, in whole or in part. 27
 Functional Dependency
• Determinants are based on the concepts of functional dependency
• A functional dependency occurs when the value of one (set of)
attribute(s) determines (arrow symbol) the value of a second (set
of) attribute(s):
• StudentID StudentName (i.e. One studentID will return one name)
• StudentID  (DormName, DormRoom, Fee)
• (i.e. One studentID will return one dorm name, one dorm room no
and one fee)
• The attribute on the left side of the functional dependency is called
the determinant.
• Functional dependencies may be based on equations:
• ExtendedPrice = Quantity x UnitPrice
• (Quantity, UnitPrice)  ExtendedPrice
• Functional dependencies are not equations!

Coronel, Carlos and Morris, Steven, Database Systems: Design, Implementation, and Management, 14 Edition. © 2023 Cengage.
All Rights Reserved. May not be scanned, copied or duplicated, or posted to a28
publicly accessible website, in whole or in part. 28
 Composite Determinants
• Composite determinant = a determinant of a functional
dependency that consists of more than one attribute
(StudentID, CourseCode) 🡪 (Grade)

StudentName CourseCode Grade

ABC1234 AACS3013 A
ABC1234 AACS2373 A
ABC1234 AACS1234 B
ABC4567 AACS3013 B+
ABC4567 AACS2373 A-
ABC4567 AACS1234 A

Coronel, Carlos and Morris, Steven, Database Systems: Design, Implementation,

Not unique and Management,
Each combination 14 Edition. © 2023 Cengage.
is unique
29
All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part. 29
 The Normalization Process (4 of 4)

• A partial dependency exists when there is a functional dependence in which the

determinant is only part of the primary key
− The assumption is that there is only one candidate key
− Partial dependencies tend to be straightforward and easy to identify

• A transitive dependency exists when the attribute is dependent on another attribute

that is not part of the primary key
− Transitive dependencies are more difficult to identify among a set of data
− They occur only when a functional dependence exists among non-prime
attributes

Coronel, Carlos and Morris, Steven, Database Systems: Design, Implementation, and Management, 14 Edition. © 2023 Cengage.
All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part. 30
 What Makes Determinant Values Unique?
• A determinant is unique in a relation if and only if, it
determines every other column in the relation.

• You cannot find the determinants of all functional

dependencies simply by looking for unique values in ONE
column:

– Data set limitations

Combination of columns may create a unique
determinant
– Must be logically a determinant
Make sense; according to business rules

Coronel, Carlos and Morris, Steven, Database Systems: Design, Implementation, and Management, 14 Edition. © 2023 Cengage.
All Rights Reserved. May not be scanned, copied or duplicated, or posted to a31
publicly accessible website, in whole or in part. 31
 Normalization
− Works through a series of
stages / process (normal
forms)
• First normal form (1NF)
• Second normal form (2NF)
• Third normal form (3NF)
• Boyce-Codd normal form
(BCNF)
▪ Each stages have to qualify
before proceed to another
stages

Coronel, Carlos and Morris, Steven, Database Systems: Design, Implementation, and Management, 14 Edition. © 2023 Cengage.
All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part. 32
 Dependency Diagram
• A dependency diagram is a graphical representation used in database design to
visually depict the relationships and dependencies among attributes in a database
table.

• It helps identify which attributes are dependent on others and is commonly used
during the normalization process to eliminate redundancy and ensure data integrity.

• Key Components of a Dependency Diagram:

1. Attributes

2. Primary Key

3. Partial Dependencies

4. Transitive Dependencies

5. Arrow

Coronel, Carlos and Morris, Steven, Database Systems: Design, Implementation, and Management, 14 Edition. © 2023 Cengage.
All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part. 33
 Dependency Diagram
Key Components of a Dependency Diagram
1. Attributes:
Represented as boxes or columns, they include all the fields or attributes in a table.

[Link] Key:
Highlighted in the diagram to indicate the attribute(s) that uniquely identify a record
in the table.

[Link] Dependencies:
Occur when a non-prime attribute (non-primary key attribute) is dependent on part
of a composite primary key, rather than the whole key. This happens in tables that
are in 1NF but not 2NF.

Coronel, Carlos and Morris, Steven, Database Systems: Design, Implementation, and Management, 14 Edition. © 2023 Cengage.
All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part. 34
 Dependency Diagram
Key Components of a Dependency Diagram
4. Transitive Dependencies:
Occur when a non-prime attribute depends on another non-prime attribute instead
of the primary key. This happens in tables that are in 2NF but not 3NF.
5. Arrows:
Used to show the dependencies between attributes. For example:
o A direct dependency is shown by an arrow pointing from one attribute to
another.
o Multiple arrows can represent various dependencies in a table.

Coronel, Carlos and Morris, Steven, Database Systems: Design, Implementation, and Management, 14 Edition. © 2023 Cengage.
All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part. 35
Example of Normalization Process

Coronel, Carlos and Morris, Steven, Database Systems: Design, Implementation, and Management, 14 Edition. © 2023 Cengage.
All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part. 36
 Dependency Diagram

Coronel, Carlos and Morris, Steven, Database Systems: Design, Implementation, and Management, 14 Edition. © 2023 Cengage.
All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part. 37
Conversion to First Normal Form (1NF) (1 of
3)
• A repeating group derives its name from the fact that a group of multiple entries of
the same type can exist for any single key attribute occurrence
− Normalizing the table structure will reduce data redundancies

• Normalization starts with the following three-step procedure:

− Eliminate the repeating groups
− Identify the primary key
− Identify all dependencies

Coronel, Carlos and Morris, Steven, Database Systems: Design, Implementation, and Management, 14 Edition. © 2023 Cengage.
All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part. 38
Conversion to First Normal Form (1NF) (2 of
3)
• A dependency diagram depicts all dependencies found within given table structure
− It helps to get an overview of all relationships among table’s attributes
− Their use makes it less likely that an important dependency will be overlooked

• The term 1NF describes the tabular format in which the following occur:
− All key attributes are defined
− There are no repeating groups in the table
− All attributes are dependent on the primary key

• All relational tables satisfy 1NF requirements

• Some tables contain partial dependencies

Coronel, Carlos and Morris, Steven, Database Systems: Design, Implementation, and Management, 14 Edition. © 2023 Cengage.
All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part. 39
Conversion to First Normal Form (1NF) (3 of
3)

Figure 6.3 First Normal Form (1NF)

Dependency Diagram

Coronel, Carlos and Morris, Steven, Database Systems: Design, Implementation, and Management, 14 Edition. © 2023 Cengage.
All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part. 40
Coronel, Carlos and Morris, Steven, Database Systems: Design, Implementation, and Management, 14 Edition. © 2023 Cengage.
All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part. 41
Conversion to Second Normal Form (2NF) (1
of 2)
• Conversion to 2NF occurs only when the 1NF has a composite primary key
− If the 1NF has a single-attribute primary key, then the table is automatically in
2NF

• The 1NF-to-2NF conversion is simple, you take the following steps:

− Step 1: Make new tables to eliminate partial dependencies
− Step 2: Reassign corresponding dependent attributes

• A table is in 2NF under the following circumstances :

− When it is in 1NF
− When it includes no partial dependencies

Coronel, Carlos and Morris, Steven, Database Systems: Design, Implementation, and Management, 14 Edition. © 2023 Cengage.
All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part. 42
Conversion to Second Normal Form (2NF)
(2 of 2)

Figure 6.4 Second Normal Form

(2NF) Conversion Results

Coronel, Carlos and Morris, Steven, Database Systems: Design, Implementation, and Management, 14 Edition. © 2023 Cengage.
All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part. 43
 Conversion to Third Normal Form (3NF) (1
of 2)
• The data anomalies created by the database organization shown in Figure 6.4 are
easily eliminated by completing the following two steps:
− Step 1: Make new tables to eliminate transitive dependencies
− Step 2: Reassign corresponding dependent attributes

• A table is in 3NF under the following circumstances:

− When it is in 2NF
− When it contains no transitive dependencies

Coronel, Carlos and Morris, Steven, Database Systems: Design, Implementation, and Management, 14 Edition. © 2023 Cengage.
All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part. 44
Conversion to Third Normal Form (3NF) (2
of 2)

Figure 6.5 Third Normal Form (3NF)

Conversion Results

Coronel, Carlos and Morris, Steven, Database Systems: Design, Implementation, and Management, 14 Edition. © 2023 Cengage.
All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part. 45
 Improving the Design (1 of 2)

• The following are various types of issues you need to address to produce a good
normalized set of table:
− Minimize data entry errors
− Evaluate naming conventions
− Refine attribute atomicity
▪ An atomic attribute is an attribute that cannot be further subdivided
▪ Atomicity is a characteristic an attribute that cannot be divided into smaller
units
− Identify new attributes
− Identify new relationships
Coronel, Carlos and Morris, Steven, Database Systems: Design, Implementation, and Management, 14 Edition. © 2023 Cengage.
All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part. 46
 Improving the Design (2 of 2)

• The following are various types of issues you need to address to produce a good
normalized set of table (continued):
− Refine primary keys as required for data granularity
▪ Granularity refers to the level of detail represented by the values stored in a
table’s row
− Maintain historical accuracy
− Evaluate using derived attributes

Coronel, Carlos and Morris, Steven, Database Systems: Design, Implementation, and Management, 14 Edition. © 2023 Cengage.
All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part. 47
 Knowledge Check Activity 6-2

• When is a table in 3NF?

Coronel, Carlos and Morris, Steven, Database Systems: Design, Implementation, and Management, 14 Edition. © 2023 Cengage.
All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part. 48
 Knowledge Check Activity 6-2: Answer

• When is a table in 3NF?

• Answer: A table is in 3NF when it is in 2NF and it contains no transitive
dependencies.

Coronel, Carlos and Morris, Steven, Database Systems: Design, Implementation, and Management, 14 Edition. © 2023 Cengage.
All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part. 49
 Surrogate Key Considerations

• Surrogate keys are used by designers when the primary key is considered to be
unsuitable

• A surrogate key is a system-defined attribute generally created and managed via the
DBMS

• Usually it is a numeric value which is automatically incremented for each new row

Coronel, Carlos and Morris, Steven, Database Systems: Design, Implementation, and Management, 14 Edition. © 2023 Cengage.
All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part. 50
 Higher-Level Normal Forms

• Tables in 3NF will perform suitably for business transactional databases

• Higher normal forms are sometimes useful

• This section covers the following higher-level normal forms:

− Boyce-Codd normal form (BCNF)
− Fourth normal form (4NF)
− Fifth normal form (5NF)

Coronel, Carlos and Morris, Steven, Database Systems: Design, Implementation, and Management, 14 Edition. © 2023 Cengage.
All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part. 51
 The Boyce-Codd Normal Form (1 of 4)

• A table is in BCNF when it is in 3NF and every determinant in the table is a candidate
key
− Recall from Chapter 3 that a candidate key has the same characteristics as
primary key but was not chosen to be the primary key

• When a table contains only one candidate key, the 3NF and the BCNF are equivalent

• BCNF can be violated only when the table contains more than one candidate key

• BCNF is considered to be a special case of 3NF

Coronel, Carlos and Morris, Steven, Database Systems: Design, Implementation, and Management, 14 Edition. © 2023 Cengage.
All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part. 52
The Boyce-Codd Normal Form (2 of 4)

• Figure 6.7 Tables with Multiple Candidate Keys

• Figure 6.8 A Table That Is in 3NF but not in BCNF

Coronel, Carlos and Morris, Steven, Database Systems: Design, Implementation, and Management, 14 Edition. © 2023 Cengage.
All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part. 53
The Boyce-Codd Normal Form (3 of 4)

Figure 6.9 Decomposition to BCNF

Coronel, Carlos and Morris, Steven, Database Systems: Design, Implementation, and Management, 14 Edition. © 2023 Cengage.
All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part. 54
The Boyce-Codd Normal Form (4 of 4)

Sample Data for a BCNF Conversion

Table 6.5
Stu_ID Staff_ID Class_code Enroll_grade
125 25 21334 A
125 20 32456 C
135 20 28458 B
144 25 27563 C
144 20 32456 B

Coronel, Carlos and Morris, Steven, Database Systems: Design, Implementation, and Management, 14 Edition. © 2023 Cengage.
All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part. 55
 Fourth Normal Form (4NF) (1 of 2)

• The discussion of 4NF is academic if you make sure that your tables conform to the
following two rules:
− All attributes must be dependent on the primary key, but they must be
independent of each other
− No row may contain two or more multivalued facts about an entity

• A table is in 4NF under the following circumstances:

− When it is in 3NF
− When it has no multivalued dependencies

Coronel, Carlos and Morris, Steven, Database Systems: Design, Implementation, and Management, 14 Edition. © 2023 Cengage.
All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part. 56
Fourth Normal Form (4NF) (2 of 2)

Figure 6.12 A Set of Tables in 4NF

Coronel, Carlos and Morris, Steven, Database Systems: Design, Implementation, and Management, 14 Edition. © 2023 Cengage.
All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part. 57
 Fifth Normal Form (5NF) (1 of 2)

• Fifth normal form, also known as project join normal form (PJNF), addresses the
issue in which a table cannot be decomposed anymore without losing data or
creating incorrect information

• Lossless decomposition occurs when the decomposed tables are joined and the
original table is recreated

• Higher normal forms can provide value, however, the value is limited by the additional
processing necessary to work with the data

• The lower normal forms are generally highly desirable and should always be
considered during the database design process

Coronel, Carlos and Morris, Steven, Database Systems: Design, Implementation, and Management, 14 Edition. © 2023 Cengage.
All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part. 58
Fifth Normal Form (5NF) (2 of 2)

Figure 6.13 Fifth Normal Form

(5NF)

Coronel, Carlos and Morris, Steven, Database Systems: Design, Implementation, and Management, 14 Edition. © 2023 Cengage.
All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part. 59
Normalization and Database Design (1 of 4)

• Normalization should be part of the design process

− Proposed entities must meet the required normal form before table structures
are created

• Principles and normalization procedures to be understood to redesign and modify

databases include the following:
− ERD is created through an iterative process
− Normalization focuses on the characteristics of specific entities

Coronel, Carlos and Morris, Steven, Database Systems: Design, Implementation, and Management, 14 Edition. © 2023 Cengage.
All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part. 60
Normalization and Database Design (2 of 4)

• Figure 6.14 Initial Contracting Company ERD

• Figure 6.15 Modified Contracting Company ERD

Coronel, Carlos and Morris, Steven, Database Systems: Design, Implementation, and Management, 14 Edition. © 2023 Cengage.
All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part. 61
Normalization and Database Design (3 of 4)

• Figure 6.16 Incorrect M:N Relationship

Representation

• Figure 6.17 Final Contracting Company ERD

Coronel, Carlos and Morris, Steven, Database Systems: Design, Implementation, and Management, 14 Edition. © 2023 Cengage.
All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part. 62
Normalization and Database Design (4 of 4)

Figure 6.18 The Implemented

Database

Coronel, Carlos and Morris, Steven, Database Systems: Design, Implementation, and Management, 14 Edition. © 2023 Cengage.
All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part. 63
 Denormalization (1 of 2)

• Important database design goals include the following:

− Creation of normalized relations
− Considering processing requirements and speed

• A problem with normalization is that as tables are decomposed to conform to

normalization requirements, the number of database tables expands

• Joining a larger number of tables takes additional input/output (I/O) operations and
processing logic, thereby reducing system speed

Coronel, Carlos and Morris, Steven, Database Systems: Design, Implementation, and Management, 14 Edition. © 2023 Cengage.
All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part. 64
 Denormalization (2 of 2)

• Defects in unnormalized tables include the following:

− Data updates are less efficient because tables are larger
− Indexing is more cumbersome
− There are no simple strategies for creating virtual tables known as views

• Unnormalized database tables often lead to various data redundancy disasters in

production databases

Coronel, Carlos and Morris, Steven, Database Systems: Design, Implementation, and Management, 14 Edition. © 2023 Cengage.
All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part. 65
 Data-Modeling Checklist (1 of 5)

• Business rules
− Properly document and verify all business rules with the end users
− Ensure that all business rules are written precisely, clearly, and simply
▪ The business rules must help identify entities, attributes, relationships, and
constraints
− Identify the source of all business rules, and ensure that each business rule is
justified, dated, and signed off by an approving authority

Coronel, Carlos and Morris, Steven, Database Systems: Design, Implementation, and Management, 14 Edition. © 2023 Cengage.
All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part. 66
 Data-Modeling Checklist (2 of 5)

• Data modeling
− Naming conventions: all names should be limited in length (database-dependent
size)
− Entity names:
▪ Should be nouns that are familiar to business and should be short and
meaningful
▪ Should document abbreviations, synonyms, and aliases for each entity
▪ Should be unique within the model
▪ Composite entities may include a combination of abbreviated names of the
entities linked through the composite entity

Coronel, Carlos and Morris, Steven, Database Systems: Design, Implementation, and Management, 14 Edition. © 2023 Cengage.
All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part. 67
 Data-Modeling Checklist (3 of 5)

• Data modeling (continued)

− Entities:
▪ Each entity should represent a single subject
▪ Each entity should represent a set of distinguishable entity instances
▪ All entities should be in 3NF or higher
Any entities below 3NF should be justified
▪ The granularity of the entity instance should be clearly defined
▪ PK should be clearly defined and support the selected data granularity

Coronel, Carlos and Morris, Steven, Database Systems: Design, Implementation, and Management, 14 Edition. © 2023 Cengage.
All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part. 68
 Data-Modeling Checklist (4 of 5)

• Data modeling (continued)

− Attributes:
▪ Should be simple and single-valued (atomic data)
▪ Should document default values, constraints, synonyms, and aliases
▪ Derived attributes should be clearly identified and include source(s)
▪ Should not be redundant unless this is required for transaction accuracy,
performance, or maintaining a history
▪ Nonkey attributes must be fully dependent on the PK attribute

Coronel, Carlos and Morris, Steven, Database Systems: Design, Implementation, and Management, 14 Edition. © 2023 Cengage.
All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part. 69
 Data-Modeling Checklist (5 of 5)

• Data modeling (continued)

− Relationships:
▪ Should clearly identify relationship participants
▪ Should clearly define participation, connectivity, and document cardinality
− ER model:
▪ Should be validated against expected processes: inserts, updates, and
deletions
▪ Should evaluate where, when, and how to maintain a history
▪ Should not contain redundant relationships except as required (see attributes)
▪ Should minimize data redundancy to ensure single-place updates
Coronel, Carlos and Morris, Steven, Database Systems: Design, Implementation, and Management, 14 Edition. © 2023 Cengage.
All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part. 70
 Knowledge Check Activity 6-3

• When is a table in BCNF?

Coronel, Carlos and Morris, Steven, Database Systems: Design, Implementation, and Management, 14 Edition. © 2023 Cengage.
All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part. 71
 Knowledge Check Activity 6-3: Answer

• When is a table in BCNF?

Answer: A table is in Boyce-Codd Normal Form (BCNF) when it is in 3NF
and every determinant in the table is a candidate key.

Coronel, Carlos and Morris, Steven, Database Systems: Design, Implementation, and Management, 14 Edition. © 2023 Cengage.
All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part. 72
 Summary

Now that the lesson has ended, you should be able to:

1. Explain normalization and its role in the database design process

2. Identify each of the normal forms: 1NF, 2NF, 3NF, BCNF, 4NF, and 5 NF

3. Explain how normal forms can be transformed from lower normal forms to higher
normal forms

4. Apply normalization rules to evaluate and correct table structures

5. Identify situations that require denormalization to generate information efficiently

6. Use a data-modeling checklist to check that the ERD meets a set of minimum
requirements

Coronel, Carlos and Morris, Steven, Database Systems: Design, Implementation, and Management, 14 Edition. © 2023 Cengage.
All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part. 73
 EXERCISE

Coronel, Carlos and Morris, Steven, Database Systems: Design, Implementation, and Management, 14 Edition. © 2023 Cengage.
All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part. 74