Database Normalization

Normalization

What You’ll Learn

This section of notes covers the process of database normalization in which relations (tables) created from the conversion of the E-R model are analyzed for potential flaws (anomalies) and these flaws are corrected. The following specific topics are covered:


Textbook Resources

Connolly, Begg, Holowczak Database Systems:
Conolly&Begg
Rob/Coronel
(5th ed)
Elmasri/Navathe
(3rd) ed.
Kroenke
(7th ed.)
Hoffer, Prescott &
McFadden (6th ed)
Ch. 8 5th Ed: 13 and 14
6th Ed: 14 and 15
Ch. 4 Ch. 14 and 15 Ch. 5 Ch. 5 and Appendix B

The Relational Model

As a reminder, the database development process we are following has the these steps:

  1. Gather user/business requirements.
  2. Develop the conceptual E-R Model (shown as an E-R Diagram) based on the user/business requirements.
  3. Convert the E-R Model to a set of relations in the (logical) relational model
  4. Normalize the relations to remove any anomalies.
  5. Implement the database by creating a table for each normalized relation in a relational database management system.

What is Normalization?

  • Normalization is a process in which we systematically examine relations for anomalies and, when detected, remove those anomalies by splitting up the relation into two new, related, relations.
  • Normalization is an important part of the database development process: Often during normalization, the database designers get their first real look into how the data are going to interact in the database.
  • Finding problems with the database structure at this stage is strongly preferred to finding problems further along in the development process because at this point it is fairly easy to cycle back to the conceptual model (Entity Relationship model) and make changes.
  • Normalization can also be thought of as a trade-off between data redundancy and performance. Normalizing a relation reduces data redundancy but introduces the need for joins when all of the data is required by an application such as a report query.

Recall, the Relational Model consists of the elements: relations, which are made up of attributes.

  • A relation is a set of attributes with values for each attribute such that:
    1. Each attribute (column) value must be a single value only.
    2. All values for a given attribute (column ) must be of the same data type.
    3. Each attribute (column) name must be unique.
    4. The order of attributes (columns) is insignificant
    5. No two tuples (rows) in a relation can be identical.
    6. The order of the tuples (rows) is insignificant.
  • From our discussion of E-R Modeling, we know that an Entity typically corresponds to a relation and that the Entity’s attributes become attributes of the relation.
  • We also discussed how, depending on the relationships between entities, copies of attributes (the identifiers ) were placed in related relations as foreign keys.

The next step is to identify functional dependencies within each relation. Click on the __Next Page link below to learn more about the normalization process.

Tags: , , , , , , , , , , , , , , , , , ,

5 Responses to “Database Normalization”

  1. anup #

    Its a best web site to understand normalization of database,m resally thank for admin……

    January 12, 2014 at 9:57 pm
    • Vincent6767 #

      Yes it is, This website provide examples that are easy to understand. My understanding about normalization has improve because of this website :D

      May 29, 2014 at 2:39 am
  2. Dinesh Shaw #

    it helped me very well thank you very much admin sir

    May 27, 2014 at 10:13 am

Trackbacks/Pingbacks

  1. Logical meaning of all the conditions given in all normal forms(1NF, 2NF, 3NF, BCNF)? | Developers Questions - Msn4Free.com - December 9, 2013

    […] I read all the theoretical blogs that tells about what is normalizationa and all normal forms(1NF, 2NF, 3NF, BCNF). There are some links that I read, LINK 1, LINK 2, LINK 3. […]

  2. Interview | Pearltrees - January 13, 2014

    […] Database Normalization Full Tutorial – Page 3 […]

Leave a Reply

You must be logged in to post a comment.