Normalization

1
Chapter 5

• Normalization
• An Normalization example

2
Learning Objectives

• What normalization is and what role it plays in
  the database design process
• About the normal forms 1NF, 2NF, 3NF, BCNF, and
  4NF
• How normal forms can be transformed from lower
  normal forms to higher normal forms
• That normalization and ER modeling are used
  concurrently to produce a good database design
• That some situations require denormalization to
  generate information efficiently

3
Normalization

• A process for evaluating and correcting table
  structure
• Minimize data redundancy
• Eliminate Anomalies

4
Is Normalization Necessary?

• NO
• But it is helpful to maintain data integrity and
  consistency

5
Anomalies

• Updaterequires update in multiple locations
• DeletionA deletion may lose important
  information
• InsertionRequires complete definitions, ie does
  see page 187 (an employee can not be entered
  unless he is assigned a project

6
Normalization Process

• 1st NF
• 2nd NF
• 3rd NF
• Almost for 90-98 application 3rd NF is
  sufficient

7
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
• SSN, CID--?Grade

8

• 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

9
The Need for Normalization (continued)

• Structure of data set in Figure 5.1 does not
  handle data very well
• The table structure appears to work report is
  generated with ease
• Unfortunately, the report may yield different
  results, depending on what data anomaly has
  occurred

10
1st NF

• Remove repeating groups
• ASSIGNMENT
• (Proj_num, proj_name(Emp_num,E_name,job_class,chg_
  hours,Hour))

11
A Dependency Diagram First Normal Form (1NF)
12
Second Normal Form (2NF) Conversion Results
13
Third Normal Form (3NF) Conversion Results
14
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

15

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

16
Another Way

• (Part_NO, V_NAME)-gt Unit_cost
• Part_NO-gtP_Desc (Partial dependency)
• V-Name-gtV_DESC (Partial Dependency)

Part_NO
P-DESC
V_NAME
V_ADDRESS
UNIT_COST
17
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

18
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

19

• 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?

20

• Question is Vendor_address dependent on BOTH
  vendor_name AND Part_NO?
• Answer NO
• Give me vendor_no and I can find vendor_address,
  we do NOT need Part_No to know vendor_address, ie
  Vendor_address depends ONLY Vendor_name, hence
  the partial dependency

21

• 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

22
Remove Partial Dependency

• VENDOR _ADDRESS
• VENDOR_name
• UNIT_PRICE
• PART
• Create TWO tables
• One with Partial dependency and other without it
• S21 (Vendor_name, vendor_address)
• S22(Vendor_name, Part_no, Unit_price)

23
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

24

• So far we have three relations that are in at
  least 2nd NF
• S1 (Part_no, Part_DESC)
• S21 (Vendor_name, vendor_address)
• 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

25
ERD
VENDOR
PART
PART-SUPPLIED
26
Q6/p 184

• A Using notation from the book
• (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)

27
Part b
28
Part c
29
Q8/P187
Table P5.8 Sample ITEM Records
Attribute Name Sample Value Sample Value Sample Value
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
30
Problem 8 Solution
BLDG_MANAGER
ITEM_DESCRIPTION
BLDG_CODE
BLDG_NAME
ITEM_ID
BLDG_ROOM
31
BLDG_MANAGER
ITEM_DESCRIPTION
BLDG_CODE
BLDG_NAME
ITEM_ID
BLDG_ROOM
Problem 9 Solution All tables in 3NF
ITEM_DESCRIPTION
ITEM_ID
BLDG_ROOM
BLDG_CODE
BLDG_NAME
BLDG_CODE
EMP_CODE
EMP_FNAME
EMP_INITIAL
EMP_CODE
EMP_LNAME
32
(No Transcript)
33
Denormalization

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

34
Convert into 3NF

• INVOICE
• (Inv_num, cust_num,lastname,Firstname,street,city,
  state,zip,date,(partnum, description,price,numship
  ped))