Achieving a Normalized Set of Relations | Database Design | Teradata Vantage - General Procedure for Achieving a Normalized Set of Relations - Advanced SQL Engine - Teradata Database

Database Design

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Published
July 2021
Language
English (United States)
Last Update
2021-07-27
dita:mapPath
kko1591750222108.ditamap
dita:ditavalPath
kko1591750222108.ditaval
dita:id
B035-1094
lifecycle
previous
Product Category
Teradata Vantageā„¢

Although a designer having relatively little experience with normalizing a relational database can often put relations into 2NF and even 3NF without giving the process much thought, the following procedure is provided so that even a novice can pursue a set of steps through to completion and achieve normalization of a fairly simple set of relations.

The following procedure provides a high-level method for achieving a normalized set of relations:

  1. Identify the attributes of the database.
  2. Group related attributes into relations.
  3. Identify the candidate keys for each relation.
  4. Select the most useful primary key from among the set of candidate keys.
  5. Identify and remove all repeating groups.

    The result is a relation in 1NF.

  6. If any of the resulting relations have identical primary keys, then combine them into a single relation.
  7. Identify all functional dependencies between the attributes of a relation and its primary key.
  8. Decompose the relations to a form where each nonkey attribute is dependent on all the attributes of the key. Do this by taking projections of the 1NF relation that eliminate any non-full functional dependencies.

    The result is a set of relations in 2NF.

  9. If any of the resulting relations have identical primary keys, then combine them into a single relation.
  10. Identify any transitive dependencies in the relations decomposed to this point.
    1. Examine relations for dependencies among nonkey attributes.
    2. Examine relations for dependencies among key within the primary key.
  11. Remove transitive dependencies by decomposition. Do this by taking projections of the 2NF relations produced by steps 8 and 9 that eliminate any transitive functional dependencies.

    The result is a set of relations in 3NF.

  12. If any of the resulting relations have identical primary keys, then combine them into a single relation if and only if no transitive dependencies result from the combination.
  13. Remove any remaining functional dependencies from the 3NF set of relations. Do this by taking projections of the 3NF relations produced by steps 8 and 9 that eliminate any remaining functional dependencies where the determinant is not a candidate key.

    The result is a set of relations in BCNF.

  14. Remove any multivalued dependencies that are not also functional dependencies. Do this by taking projections of the BCNF relations produced by step 13.

    The result is a set of relations in 4NF.

  15. The likelihood that any join dependencies not implied by the candidate keys remain by this point is virtually nil. If you can identify any such relations, then take projections of them to eliminate the non-implied join dependencies.

    The result is a set of relations in 5NF.

  16. Particularly for temporal data, but sometimes to eliminate nulls as well, take projections to 6NF.