Normalization

1
Normalization

• Normreview.ppt

2
Normalization

• Procedure to derive logical structures that are
• non-redundant
• redundancy causes problems with storage and data
  retrieval
• efficient
• lack of efficiency problems with
  deleting/changing data
• simple
• no variable length records

3
Normalization Process Steps

• Unnormalized data
• repeating fields or group of fields redundancies
• 1st Normal Form
• get rid of the repeating elements
• 2nd Normal Form
• All non-key elements must be functionally
  dependent on the entire primary key
• 3rd Normal Form
• get rid of transitive dependencies

4
Multi-valued attributes

• Classes of Attributes
• A single-valued attribute can have only a single
  value.
• Examples
• A person can have only one social security
  number.
• A manufactured part can have only one serial
  number.
• Multivalued attributes can have many values.
• Examples
• A person may have several college degrees.
• A household may have several phones with
  different numbers

5
Multivalued Attributes

• Example
• Car (CarId, CarModel, CarYear, Car_color)
• Multivalued nature of the attribute --
• Color for top, color for trim, color for body
• The relational DBMS cannot implement multivalued
  attributes.

6
Multivalued Attributes

• Car (CarId, CarModel, CarYear, CarCllor)
• Create a new entity composed of the original
  multivalued attributes components
• E.g. CarColor (CarId , ColorSection ,CarCcolor)
• E.g. Car (CarId, CarModel, CarYear)

7
Derived Attribute

• A derived attribute is not physically stored
  within the database instead, it is derived by
  using an algorithm.
• Example AGE can be derived from the data of
  birth and the current date.

Source Rob and Coronel, 2000)
8
1st Normal Form

• A relation is in 1st Normal Form if all its
  attributes are simple
• no repeating groups
• Key of the new relation
• key of the old relation concatinated with the
  identifier of the repeating group

9
2nd Normal Form

• A relation is in 2nd Normal Form if
• it is in 1st NF and
• every non-key attribute is Functionally dependent
  on the whole primary key
• Key of the new relation
• the attribute that was the partial key
  determinant from the old relation

10
3rd Normal Form

• A relation is in 3rd Normal Form if
• it is in 2nd NF and
• each non-key attribute is functionally dependent
  on the primary key and independent of other
  non-key attributes
• avoid transient dependency
• Key of the new relation
• the determinant from the old relation

11
Transient Dependency

• EIN, Empl Ln, Empl Dept, Dept MNR
• 01 King Accounting Smith
• 02 Shaw Accounting Smith
• 03 Ball Finance Jones
• All elements are functionally dependent on the
  value of a primary BUTgtgtgt

12
Transient Dependency

• However,, Dept Manager has a Transient Dependency
  on the Employee Department. Normalize!!
• EIN, Empl LN, Empl Dept
• 01 King Accounting
• Dept Name, Dept Mnr
• Accounting Smith

13
Relational Concepts

• Data organization into simple tables
• Two-dimensional tables
• All items in one column are of the same kind
• All items assigned a distinct name
• Each item is a simple number or character string
• Each row is distinct

14
Normalization Example

• Sales (SalespersonNo, SalespersonName,SalesArea,
  CustomerNo, CustomerName, WarehouseNo,
  WarehouseLocation, SalesAmount)

Note Customers are assigned to warehouses
SalesAmount represents the value of the most
current sales transaction between a customer and
a salesperson.