IM - Chap6

• Process for evaluating and correcting table structures to minimize data redundancies and reduce data anomalies

  normalization
• Series of stages called normal forms:

  • First Normal Form (1NF)
  • Second Normal Form (2NF)
  • Third Normal Form (3NF)
• 2NF is better than 1NF; 3NF is better than 2NF
• The normal form needed by most business database for normalization
• Highest level of normalization is not always most desirable
• produces a lower normal form

  denormalization
• Increases performance but also increases data redundancy

  denormalization
• suited to help designer avoid data integrity problems

  relational database environment
• Each table represents a single subject
• ●No data item will be unnecessarily stored in more than one table
• ●All nonprime attributes in a table are dependent on the primary key
• ●Each table is void of insertion, update, and deletion anomalies
• ensure that all tables are in at least 3NF


  objective of normalization
• Progressively breaks table into new set of relations based on identified dependencies

  normalization process
• Attribute B is _____________ on the Attribute A if each value of A determines one and only one value of B.


  Functionally dependent
• PROJ_NUM -> PROJ_NAME.

  PROJ_NUM is known as the __


  determinant attribute
• PROJ_NUM -> PROJ_NAME.

  PROJ_NAME is known as the _________


  dependent attribute
• This happens if all of the rows in the table that agree in value for the Attribute A also agree in value for attribute B

  functional dependence
• If Attribute B is functionally dependent on a composite key A but not on any subset of that composite key, the attribute B is fully functionally dependent on A

  Fully functional dependence
• Exists when there is a functional dependence in which the determinant is only part of the primary key

  Partial Dependency
• Exists when there are functional dependencies such that X → Y, Y → Z, and X is the primary key

  transitive dependency
• The existence of a functional dependence among non-prime attributes is a sign of transitive dependency
• Group of multiple entries of same type can exist for any single key attribute occurrence

  repeating group
• Relational table must not contain repeating groups
• a three-step procedure that reduces data redundancies


  normalization
• Conversion to 1NF:

  ● Step 1: Eliminate the Repeating Groups

  ○Eliminate nulls: each repeating group attribute contains an appropriate data value

  ●Step 2: Identify the Primary Key

  ○Must uniquely identify attribute value

  ○New key must be composed

  ●Step 3: Identify All Dependencies

  ○Dependencies are depicted with a diagram
• Depicts all dependencies found within given table structure

  dependency diagram
• In a dependency diagram, the arrows above the attributes indicate desirable dependencies (i.e., based on the PK) and the arrows below the attributes indicate less desirable dependencies (partial and transitive)
• ●First normal form describes tabular format:

  ○All key attributes are defined

  ○No repeating groups in the table

  ○All attributes are dependent on primary key

  ●All relational tables satisfy 1NF requirements

  ●Some tables contain partial dependencies

  ○Dependencies are based on part of the primary key

  ○Should be used with caution
• The normal form which eliminates partial dependencies

  2nd Normal Form
• Conversion to 2NF occurs only when the 1NF has a composite key

  ○If the 1NF key is a single attribute, then the table is automatically in 2NF
• In converting to 2NF, a new table is created for each component of the PK acting as the determinant in a partial dependency

  These components also remain on the original table to serve as FK to the original table
• Conversion to 2NF:

  ● Step 2: Reassign Corresponding Dependent Attributes

  ○The dependencies for the original key components are found by examining the arrows below the dependency diagram in Fig 6.3○The attributes in a partial dependency are removed from the original table and placed in the new table with the dependency’s determinant○Any attributes that are not dependent in a partial dependency remain in the original table○At this point, most anomalies have been eliminated
• A table is said to be in 2NF when:

  
  

  • It is in 1NF and
  • It has no partial dependencies
• A normal form containing no partial and transitive dependencies

  3rd Normal Form
• Conversion to 3NF:

  ●Step 1: Make New Tables to Eliminate Transitive Dependencies

  ○For every transitive dependency, write its determinant as PK for new table (JOB_CLASS)

  ■ Determinant: any attribute whose value determines other values within a row

  ○The determinant should remain in the original table to serve as a FK
• Conversion to 3NF:

  ●Step 2: Reassign Corresponding Dependent Attributes

  ○Identify attributes dependent on each determinant identified in Step 1

  ■Identify dependency

  ○Name table to reflect its contents and function
• A table is said to be in 3rd Normal Form when

  • It is in 2NF
  • It contains no transitive dependencies
• 1NF->2NF – remove partial dependencies

  2NF->3NF – remove transitive dependencies
• ●In both cases of normalizing to a higher form, the answer is create a new table

  ○The determinant of the problem dependency remains in the original table and is placed as the PK of the new table

  ○The dependents of the problem dependency are removed from the original table and placed as nonprime attributes in the new table