IFS

• Normalization


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


  • Reduces data anomalies
  • Works through a 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
• For most business database design purposes, 3NF is as high as we need to go in normalization process
• Highest level of normalization is not always most desirable
• Repeating group


  An attribute, or group of attributes, within a table that occurs with multiple values for a single occurrence of the nominated key attribute(s) for that table
• Unnormalized form (UNF)


  Data in a form that contains repeating groups
• Normalization is three-step procedure

  1. Eliminate the Repeating Groups

  2. Identify the Primary Key

  3. Identify All Dependencies
• All relational tables must satisfy 1NF requirements
• Partial dependency


  Attributes which are only dependent on part of the composite primary key
• Transitive dependency


  Attribute is dependent on any other attribute except the primary key
• A table is in second normal form (2NF) when it is in 1NF and it includes no partial dependencies
• Conversion to Second Normal Form

  1. Write each PK component on a separate line

  2. Assign corresponding dependent attributes
• A table with a PK consisting of one attribute is automatically in 2NF when it is in 1NF
• But it may still contain transitive dependencies
• Conversion to Third Normal Form

  1. Identify each new determinant

  2. Identify the dependent attributes

  3. Remove the dependent attributes from the transitive dependencies
• A determinant is any attribute whose value determines other values within a tuple of the relation
• Tuple


  A row in a relation (table)
• Conversion to Third Normal Form

  1. Step 1: Identify the determinants

  2. Step 2: Identify the dependent attributes

  3. Step 3: Remove the dependent attributes from the transitive dependencies

  4. Draw new dependency diagram to show all tables defined in steps 1–3
• A table is in Boyce-Codd normal form (BCNF) when every determinant in a table is a candidate key
• Candidate key


  Has same characteristics as the primary key, but for some reason, not chosen to be primary key
• When a table contains only one candidate key, the 3NF and the BCNF are equivalent
• BCNF can be violated only when a table contains more than one candidate key
• Third Normal Form (3NF)

  A table is in 3NF when it is in 2NF and there are no transitive dependencies
• A table can be in 3NF and fail to meet BCNF
• C determines B


  Table is not in BCNF
• Denormalization


  The process of intentionally creating redundant data to improve query performance
• Unnormalized tables in production database tend to suffer from less efficient data updates, more cumbersome indexing, and no simple strategies for creating virtual tables known as views
• Normalization is an important part—but only part—of the database design process
• Tables in 3NF may contain multivalued dependencies that produce either numerous null values or redundant data
• It may be necessary to convert 3NF table to fourth normal form (4NF) by splitting the table to remove multivalued dependencies
• Examples of test or exam questions

  • Definitional questions on normalization, 1NF, 2NF, 3NF, partial dependency, transitive dependency, primary key dependency, use of dependency diagrams, steps involved in transforming a table from 1NF to 2NF to 3NF