NORMALIZATION PROCESS

Build 1NF Table

A table of columns and rows with a "key", possible more than one column, to uniquely identify each row and each cell contains non-repeating, atomic information.

  • Include PK.
  • Each column you create should be useful to somebody (i.e., somebody might need to search through that information).
  • Use non-meaningful PKs. A good example I once read explains that if you start the Employee ID with C to mean the person is stationed in Chicago, what will you do if the person is transferred to Los Angeles? If you make non-meaningful PKs, changes in the environment or business rules will not render them ineffective.
  • Look for all those columns that repeat themselves, and evaluate creating unique identifiers for them.
  • State Assumptions.
  • If you can’t think of many, don’t worry, you’ll have a chance to go through them later.
  • Cells must contain data with atomic values.
  • Do not use special characters. It might be difficult for others to enter them. For example, if you find “quotes”, eliminate them.
  • Avoid spaces. Two ways of getting around:
  • Instead ofWord Type or word type
  • Useword_type or wordType

Establish Functional Dependencies

An attribute (or set of attributes) X fully determines another attribute Y if each unique value of X (not a sub-set of X) has associated with it precisely one value of Y

  • For each distinct value of one field (or a set of fields), there is only one possible value for the other attribute:
  • Student ID NumberStudent Name
  • AB
  • All attributes must have an arrow pointing at them. Exception: trivial dependencies (i.e., when something depends on itself) and PKs
  • Revisit your assumptions:
  • Student ID Numbers are uniquely assigned to each student.

Build 2NF tables

A Table is in 2nd Normal Form if it is in 1st Normal Form and all non-key attributes are fully (not by any subset) determined by the key field(s).

  • Based on the functional dependencies.
  • Make sure the PK fully determines all the other attributes (not a subset).
  • Student ID NumberName, Address, Phone
  • AB, C, D

Build 3NF tables

A table is in 3rd Normal Form if it is in 2nd Normal Form and there is no functional dependency amongst non-key attributes. In other words, each non-key attribute depends solely upon the key.

  • Get rid of functional dependencies amongst the non-key attributes (i.e., each row is determined ONLY by the PK).
  • Identify FK:
  • Attributes in one table that correspond to a PK in another table.

.

.

.

Prove Lossless Decomposition

Rejoin the 3NF tables to get back to the 1NF table by "reconnecting" the tables on common attributes, usually the key attributes.

  • Make sure it is possible to reconstruct the original 1NF

ER DIAGRAM

  • Note the relationship between the tables.

CSS 412 – Information Management for ManagersTA Session10/10/18