Transcript lecture 4.ppt

Data Design
normalization
- Mr. Ahmad Al-Ghoul
learning Objectives
 Explain the concept of
table design
 Explain unnormalized
design and the first normal
form (1NF)
2

Normalization


Normalization
Normalization is a process by which analysts identify and correct
inherent problems and complexities in their table designs.
Table design





A table design specifies the fields and identifies the primary key
in a particular table or file.
You will use normalization to develop an overall database design
that is simple, flexible, and free of data redundancy
In addition to being simpler and more stable, normalized data
structure are more easily maintained than other data structure
Involves four stages: unnormalized design, first normal form,
second normal form, and third normal form
Most business-related databases must be designed in third
normal form
3
Normalization

Standard Notation Format
Designing tables is easier if you use a standard
notation format to show a table’s structure, fields,
and primary key
 Standard notation format starts with the name of the
table, followed by a parenthetical expression that
contains the fieldnames separated by commas, the
primary key field is underlined
Example: NAME (FIELD 1, FIELD 2, FIELD 3)

4
Normalization

Repeating Groups and Unnormalized Design

Repeating group: is a set of one or more fields that
can occur any number of times in a single record,
with each occurrence having different values.


Often occur in manual documents prepared by users
Unnormalized design

An unnormalized record is one that contains a repeating
group, which means that a single record has multiple
occurrences of a particular field, with each occurrence having
different values.
5
Normalization
Repeating
groups
In the ORDER table design, records 1 and 2 have repeating groups because
they contain several products. ORDER-NUM is the primary key for the
ORDER table, and PRODUCT-NUM serves as a primary key for the repeating
group. Because it contains a repeating group, the ORDER table design is
unnormalized. [1]
6
Normalization

Now let’s review the unnormalized ORDER
table design. Following the notation
guidelines, you can describe it as follows:

ORDER (ORDER-NUM, ORDER-DATE,
(PRODUCT-NUM, PRODUCT-DESC, NUMORDERED))
The ORDER-NUM field is underlined to show that it
is the primary key
 The PRODUCT-NUM is also underlined because it
acts as the primary key of the repeating group

7
Normalization

First Normal Form





A table is in first normal form (1NF) if it does not
contain a repeating group
To convert, you must expand the table’s primary key
to include the primary key of the repeating group
Let us look to previous example again
When you expand the primary key of ORDER table to
include PRODUCT-NUM, you eliminate the repeating
group and the ORDER table is now in 1NF
ORDER (ORDER-NUM, ORDER-DATE, PRODUCT-NUM,
PRODUCT-DESC, NUM-ORDERED)
8
Normalization
[1]
The ORDER table as it appears in 1NF. The repeating groups have been
eliminated. Notice that the repeating group for order 40311 has become
three separate records, and the repeating group for order 40312 has
become two separate records. The 1NF primary key is a combination of
ORDER-NUM and PRODUCT-NUM, which uniquely identifies each record.
9
Normalization



Note that neither ORDER-NUM nor PRODUCTNUM does not uniquely identify each product in
a multiple item product
Each record must reflect a specific product in a
specific order, you need both fields, ORDER-NUM
and PRODUCT-NUM, to identify a single record
uniquely.
The primary key in this example is the
combination of two fields: ORDER-NUM and
PRODUCT-NUM
10
Normalization

1NF



All key attributes defined
No repeating groups in a table
All attributes dependent on a primary key
11
Normalization

Dependencies

Dependencies can be identified

functional dependency (desirable dependencies) based
on primary key
 Less desirable dependencies


partial

- based on part of composite primary key
transitive

- one nonprime attribute depends on anther
nonprime attribute
12
Sequence Summary





Repeating group: is a set of one or more fields that
can occur any number of times in a single record,
with each occurrence having different values
An unnormalized record is one that contains a
repeating group
Normalization is a process for avoiding problems in
data design
A table is in first normal form (1NF) if it does not
contain a repeating group
To convert, you must expand the table’s primary key
to include the primary key of the repeating group
13
Sequence Summary

In this Sequence we have
 Defined the term normalization
 Explained table design
 Defined the standard notation format
 Defined and discussed the repeating groups
and unnormalized design
 Explained the first normal form (1NF)
 Explained the different types of attributes
dependencies
14
Reference
[1] System Analysis and Design, Sixth
Edition
Authors: Gary B. Shelly, Thomas J.
Cashman and Harry J. Rosenblatt
Publisher: SHELLY CASHMAN SEWIES.
15