Transcript lecture 5.ppt

Data Design
Normalization
- Mr. Ahmad Al-Ghoul
learning Objectives
 Explain the second normal
form (2NF)
 Explain the third normal
form (3NF)
 Describe the advantages
of normalization
2
Normalization

Second Normal Form

To understand second normal form (2NF), you
must understand the concept of functional
dependence
3
Normalization




An attribute X is said to be functionally dependent on an
attribute Y if each value of Y is associated with only one value
of X
For example, each ORDER-DATE value is functionally
dependent on the ORDER-NUM. In contrast, a PRODUCTDESC is not dependent on the order number. For a particular
order number, there might be several product description one
for each item ordered
Another way of phrasing functional dependency is to say that
the value of X can be determined from the value of Y, or that
Y functionally determines X
So ORDER-NUM functionally determines the value of ORDERDATE , or the value of ORDER-DATE can be determined from
the value of the ORDER-NUM, i.e. given the value of a
ORDER-NUM we can always establish the value of the
associated ORDER-DATE.
4
Normalization




A table design is in second normal form (2NF) if it is in
1NF and if all fields that are not part of the primary key
are functionally dependent on the entire primary key
If any field in a 1NF table depends on only one of the
fields in a combination primary key, then the table is not
in 2NF
If 1NF design has a primary key that consists of only one
field, the problem of partial dependence does not a rise,
because the entire primary key is a single field
1NF table with a single field primary key is automatically
in 2NF
5
Normalization

Now examine the 1NF design for the ORDER table




ORDER (ORDER-NUM, ORDER-DATE, PRODUCT-NUM, PRODUCTDESC, NUM-ORDERED)
The NUM-ORDERD field depends on the entire primary
key, because NUM-ORDERD refers to a specific product
number and a specific order number
ORDER-DATE field depends on the order number, which
is only a part of the primary key
PRODUCT-DESC field depends on the product number,
which is only a part of the primary key
6
Normalization

The objective for converting a table from
1NF to 2NF is to break the original table
into two tables or more new tables and
reassign the fields so that each nonkey
field will depend on the entire primary
key in its table
7
Normalization

To do that you follow these steps:
1.
2.
3.
Create and name a separate table for each field in the
existing primary key. The ORDER table’s primary key has two
fields, ORDER-NUM and PRODUCT-NUM, so you must create
two tables; ORDER and PRODUCT
Create a new table for each possible combination of the
original primary key fields. You would create and name a
new table with a combination primary key of ORDER-NUM
and PRODUCT-NUM. this table describes individual lines in an
order, so it is named ORDER-LINE
Study the three tables and place each field with its
appropriate primary key, which is the minimal key on which it
functionally depends
8
Normalization

The three tables for the previous example
would be shown as



ORDER (ORDER-NUM, ORDER-DATE)
PRODUCT (PRODUCT-NUM, PRODUCT-DESC)
ORDER-LINE ( ORDER-NUM, PRODUCT-NUM,
NUM-ORDERD)
9
Normalization
ORDER, PRODUCT, and ORDER-LINE tables in 2NF. All fields are
functionally dependent on the primary key. [1]
10
Normalization

Second Normal Form

Four kinds of problems are found with 1NF
description that do not exist with 2NF

Consider the work necessary to change a particular product’s
description


Suppose 500 current orders exists for product number 304.
changing the product description involves modifying 500
records for product number 304. updating all 500 records
would be time consuming and expansive.
1NF tables can contain inconsistent data. Because someone
must enter the product description in each record, nothing
prevents product number 304 from having different product
descriptions in different records
11
Normalization

Second Normal Form

Four kinds of problems are found with 1NF
description that do not exist with 2NF

Adding a new product is a problem


Because the primary key must include an order number and a
product number, you need values for both fields in order to add
a record
Deleting a product is a problem

If all the related records are deleted once an order is filled and
paid for, what happens if you delete the only record that
contains product number 633? The information about that
product number and its description is lost
12
Normalization

2NF


No attribute dependent on a portion of a
primary key
Attributes may be functionally dependent on
nonkey attributes
13
Normalization

Third Normal Form



A design is in 3NF if every nonkey field depends on
the key, the whole key, and nothing but the key
3NF design avoids redundancy and data integrity
problems that still can exist in 2NF designs
A table design is in third normal form (3NF) if it is in
2NF and if no nonkey field is dependent on another
nonkey field
14
Normalization


Consider the following CUSTOMER table design
CUSTOMER (CUSTOMER-NUM, CUSTOMER-NAME,
ADDRESS, SALES-REP-NUM, SALES-REP-NAME)
2NF design for the CUSTOMER table.
[1]
15
Normalization





The previous table still has four potential problems
similar to the four 1Nf problems describes earlier
Changing the name of a sales rep still requires changing
every record in which that sales rep name appears
Nothing prevent a sales rep from having different names
in different records
Sales rep name is included in the CUSTOMER table, you
must create a dummy CUSTOMER record to add a new
sales rep who has not yet been assigned any customer
If you delete all the records for customers of sales rep
number 22, you will lose that sales rep’s number and
name
16
Normalization



Those potential problems are caused
because the design is not in 3NF
A table design is in third normal form
(3NF) if it is in 2NF and if no nonkey field
is dependent on any anther nonkey field
Nonkey field is a field that is not a
candidate key for the primary key
17
Normalization



The CUSTOMER table is not in 3NF because one
nonkey field, SALES-REP-NAME, depends on
anther nonkey field, SALES-REP-NUM
To convert the table to 3NF, you must remove all
fields from the 2NF table that depend on
another nonkey field and place them in a new
table that uses the nonkey field as a primary key
To reach 3NF, you must remove SALES-REPNAME and place it into a new table that uses
SALES-REP-NUM as the primary key
18
Normalization

3NF produces two separate tables


CUSTOMER (CUSTOMER-NUM, CUSTOMER-NAME,
ADDRESS, SALES-REP-NUM)
SALES-REP (SALES-REP-NUM, SALES-REP-NAME)
[1]
19
Normalization

3NF


Remove any transitive dependencies
No attribute dependent on a nonkey attribute
20
Normalization

Advantages of Normalisation


after describing normalization in detail it is worth
mentioning some of its advantages briefly. Data in
Third Normal Form (3NF) consists of tables of closely
associated attributes which are entirely dependent on
‘the key, the whole key, and nothing but the key’
This has the effect of minimising data duplication
across different tables, thereby resolving many of the
problems associated with data redundancy.
21
Sequence Summary



An attribute X is said to be functionally
dependent on an attribute Y if each value of Y is
associated with only one value of X
A record is in second normal form (2NF) if it is in
1NF and all nonkey fields functionally depend on
the entire primary key
A record is in third normal form (3NF) if it is in
2NF and if no field depends on a nonkey field
22
Sequence Summary

In this Sequence we have
 Defined and explained the term functionally
dependent
 Explained the second normal form (2NF)
 Explained the problems that can be solved by
2NF
 Explained the third normal form (3NF)
 Described the advantages of normalization
23
Reference
[1] System Analysis and Design, Sixth
Edition
Authors: Gary B. Shelly, Thomas J.
Cashman and Harry J. Rosenblatt
Publisher: SHELLY CASHMAN SEWIES.
24