Module 5: Normalizing the Logical
Data Design THIS PAGE LEFT INTENTIONALLY BLANK

Module 1: Course
Overview
Module 4: Deriving a
Logical Data Design
Module 5: Normalizing the
Logical Data Design
Module 6: Deriving a
Physical Data Design
Module 7:
Implementing Data
Integrity
Module 2: Solution
Design Processes
Module 3: Using a
Conceptual Design for Data
Requirements
Module 8: Designing
Data Services
Module 9: Data Storage
Considerations
Designing Data
After completing this module, you will be able to:
"
Use primary and foreign keys to implement relationships between entities.
"
Explain the benefits of normalizing entities.
"
Normalize a table to third normal form.

Slide Objective
To provide an overview of
this module’s topics and
objectives.
Lead-in
This module discusses the
implementation of
relationships between
entities with keys and
database normalization
strategies.
96 Module 5: Normalizing the Logical Data Design
!
!!
! Implementing Entity Relationships
"
Using Keys

Benefits
$
Uniquely define an entity within a group of like entities
$
Help to move data design from logical to physical
$
Tie entities together to form relationshipsKeys are identifying values assigned to each instance of an entity within a data
model. As you learned in Module 4, “Deriving a Logical Data Design,” entities
within a data model represent a grouping of information about people, places,
objects, or ideas. When you begin to move a logical design to a physical design,
you use keys to uniquely identify each instance of an entity within the data
model.
Keys also provide the mechanism for tying entities together. In your physical
database design, you represent relationships between entities by adding the keys
from parent entity tables to child entity tables so that the entities are bound
together by the common key value.
Slide Objective
To introduce the concept of
keys within the physical data
model.
Lead-in
Keys are the mechanism for
implementing relationships.
98 Module 5: Normalizing the Logical Data Design

"
Surrogate keys
These keys are identifier values with no specific relation to entities other
than to uniquely identify them.
"
Composite keys
These keys include more than one value per entity. When one attribute
within an entity is not enough to uniquely identify each instance of the
entity, you can select more than one attribute to be a composite key. For
example, each project within a project-tracking database might have a
project number, and each project number might contain multiple jobs, each
with its own identifying job number. You might use recurring job numbers
within each project to denote a specific phase of the project. In this case, the
primary key of the project table would be a composite key consisting of
both the project number and the job number, because both numbers are
necessary to uniquely identify any one job.

Slide Objective
To introduce primary keys
and how they are defined
within the data model.
Lead-in
Primary keys define
uniqueness in the relational
data model.
Delivery Tip
Give an example of an
intelligent key and a
surrogate key.
Delivery Tip

however, the foreign key can serve as a member of a composite key for the
child.
Slide Objective
To introduce foreign key
concepts.
Lead-in
Foreign keys are used as
the links between entities
that make relationships
possible.
Delivery Tip
If necessary, remind
students of the basic
definition of a relationship.
Also remind them about
parent and child entities, as
well as the different types of
relationships.
100 Module 5: Normalizing the Logical Data Design
Examples of Primary and Foreign Keys The slide shows a logical data model with primary and foreign keys, as well as
entity relationships.
The primary keys identified for each entity are surrogate keys. As shown, each
employee, client, timesheet, and invoice instance of an entity has its own
unique identifier. The EmployeeID primary key in the Employee entity is used


Slide Objective
To introduce this activity.
Lead-in
In this activity, you will
identify keys in the
Ferguson and Bardell, Inc.
logical data model.
Delivery Tip
Make the students aware
that they are focusing on
keys in this activity. They
are identifying keys to
reinforce their knowledge so
that they will be able to build
a normalized data model.