# webcast_Normalization by cuiliqing

VIEWS: 16 PAGES: 27

• pg 1
Lecture 4
• Normalization

• An Normalization
example

INSS 651   1
Normalization
A process for evaluating and correcting table
structure

Minimize data redundancy

Eliminate Anomalies

INSS 651                2
Is Normalization Necessary?
NO

But it is helpful to maintain data integrity and
consistency

INSS 651                  3
Denormalization
• Reversing normalization
• i.e from 3rd NF to 2nd NF
• Or 2nd to 1st NF

INSS 651   4
Anomalies
• Update—requires update in multiple
locations
• Deletion—A deletion may lose important
information
• Insertion—Requires complete definitions,
ie does see page 187 (an employee can
not be entered unless he is assigned a
project
INSS 651                  5
Normalization Process
• 1st NF
• 2nd NF
• 3rd NF

• Almost for 90-98% application 3rd NF is
sufficient

INSS 651                6
Dependency
When an attribute value depends on
attribute B then B is dependent on A
A---B
or values of B can be determined by value of
A, reverse may or may not be true
Ex:
ssn--Name
INSS 651                7
Un-normalized relation

Remove REPEATING groups
1st NF

Remove PARTIAL dependency

2nd NF

Remove TRANSIENT dependency

3rd NF
Every determinant is a candidate key

Boyce-CODD NF

If we can convert a relation into 3NF almost 90-98% of anomalies are removed

INSS 651                                    8
Un-normalized form
A relation is in un-normalized form, if it contains repeating group
Typically shown in parentheses

Ex: PART NO DESC.          VENDOR-NAME ADDRESS UNIT-COST
1234         LOGIC       INTEL              SAN JOSE 150.00
chip        LSI LOGIC          SAN JOSE 120.00
5678         MEMORY INTEL                   SAN JOSE       50.00
chip

SUPPLIER
(Part_no, Part_DESC, (Vendor_name, Vendor_address, Unit_cost))

INSS 651                            9
Another Way

(Part_NO, V_NAME)-> Unit_cost
Part_NO->P_Desc (Partial dependency)
V-Name->V_DESC (Partial Dependency)

Part_NO   P-DESC   V_NAME   V_ADDRESS   UNIT_COST

INSS 651               10
1st NF
A relation is in 1st NF if it does NOT contain any repeating groups
(Part_no, Part_DESC, (Vendor_name, Vendor_address, Unit_cost))

1st NF..remove repeating groups
Break it into TWO relations

One without repeating group and
ONE with repeating group AND PK of other relation

S1 (Part_no, Part_DESC)
S2 (Vendor_name, Part_no, Vendor_address, Unit_cost

INSS 651                        11
2nd NF
A relation is in 2nf NF if it is in 1stNF and it does not
contain any partial dependency

Partial dependency: A partial dependency exists if an
attribute is dependent ONLY on PART of the PK and the
WHOLE PK

We must examine each relation for partial dependency

NOTE: A partial dependency can only exist if there are
more than ONE attribute as PK

INSS 651                       12
S1 (Part_no, Part_DESC)
S2 (Vendor_name, Part_no, Vendor_address,
Unit_cost
Note S1 is already in 2nd NF since there is only
attribute as PK
In S2:
Question is Vendor_address dependent on BOTH
vendor_name AND Part_NO?

Question is Unit_price dependent on BOTH
vendor_name AND Part_NO?
INSS 651                 13
Question is Vendor_address dependent on BOTH
vendor_name AND Part_NO?

Give me vendor_no and I can find
vendor_address, we do NOT need Part_No to
depends ONLY Vendor_name, hence the partial
dependency

INSS 651                14
Question is Unit_price dependent on BOTH
vendor_name AND Part_NO?
YES if you examine the table, price changes
with vendor and part_no, ie price depnds
on both Part_no AND which vendor
supplies it

INSS 651               15
Remove Partial Dependency

VENDOR_name
UNIT_PRICE
PART#

Create TWO tables:
One with Partial dependency and other without it
S22(Vendor_name, Part_no, Unit_price)

INSS 651                  16
3rd NF
A relation is in 3rd NF if it is in 2nd NF and it
does not contain any transitive
dependency
Transitive dependency: A transitive
dependency exists when some of the non-
key attributes are dependent on other non-
key attributes

INSS 651                  17
So far we have three relations that are in
at least 2nd NF
S1 (Part_no, Part_DESC)
S22(Vendor_name, Part_no, Unit_price)

S1, S21 & S22 are also in 3rd NF since there
is ONLY ONE non_key attribute and
transitive dependency can NOT exist
INSS 651                 18
ERD

PART   PART-SUPPLIED        VENDOR

INSS 651            19
Q6/p 175
A Using notation from the book
a. (C1, C3)- C2,C4,C5
(i.e., C2, C4, C5) are functionally dependent on C1 and C3
Above relation is in at least 1stNF, since there are No
repeating groups

C1C2 there is PARTIAL dependency since C2 depends
on PART of the PK and the whole PK

C4--C5 (transitive dependency since C5 ( a non-PK
attribute) depends on another non-PK attribute (C4)

INSS 651                        20
Part b

Table 1

Primary key: C1
C1   C2             Foreign key: None
Normal form: 3NF

Table 2

C1   C3   C4   C5   Primary key: C1 + C3
Foreign key: C1 (to Table 1)
Normal form: 2NF, because the
table exhibits the transitive
dependencies C4          C5

INSS 651                      21
Part c

Table 1
Primary key: C1
C1    C2   Foreign key: None
Normal form: 3NF

Table 2
Primary key: C1 + C3
C1    C3    C4   Foreign key: C1 (to Table 1)
C4 (to Table 3)
Normal form: 3NF

Table 3
Primary key: C4
C4    C5    Foreign key: None
Normal form: 3NF

INSS 651                  22
Q8/P177

Table P5.8 Sample ITEM Records
Sample Value        Sample Value        Sample Value
Attribute Name
ITEM_ID          231134-678          342245-225          254668-449
ITEM_LABEL       HP DeskJet 895Cse   HP Toner            DT Scanner
ROOM_NUMBER      325                 325                 123
BLDG_CODE        NTC                 NTC                 CSF
BLDG_NAME        Nottooclear         Nottoclear          Canseefar
BLDG_MANAGER     I. B. Rightonit     I. B. Rightonit     May B. Next

INSS 651         23
Problem 8 Solution

ITEM_ID ITEM_DESCRIPTION BLDG_ROOM BLDG_CODE BLDG_NAME BLDG_MANAGER

INSS 651                          24
ITEM_ID ITEM_DESCRIPTION BLDG_ROOM BLDG_CODE BLDG_NAME BLDG_MANAGER

Problem 9 Solution: All tables in 3NF

ITEM_ID ITEM_DESCRIPTION BLDG_ROOM BLDG_CODE

BLDG_CODE BLDG_NAME       EMP_CODE

EMP_CODE   EMP_LNAME EMP_FNAME           EMP_INITIAL
INSS 651                            25
1
EMPLOYEE

(0,N)

manages

(1,1)
M                1                     M
BUILDING             contains              ITEM
(0,N)               (1,1)

EMPLOYEE                       BUILDING                    ITEM
EMP_CODE      1                BLDG_CODE    1              ITEM_ID
EMP_LNAME                      BLDG_NAME                   ITEM_DESCRIPTION
EMP_FNAME              M       EMP_CODE                    ITEM_ROOM
EMP_INITIAL                                         M      BLDG_CODE

INSS 651                    26
INSS 651   27

To top