Knowledge Drop

DATABASE (Concept): Data Normalization: 1st, 3rd Normal Form


Userlevel 5

Last tested: Aug 31, 2018
 

Data normalization is the process of improving the logical design of your database so that it satisfies certain constraints that avoid unnecessary duplication of data.  

Purpose: The goal of data normalization is to avoid anomalies:

  1. Insertion Anomaly
    1. Adding new row forces user to create duplicate dataInsertion anomoly.png
  2. Deletion Anomaly
    1. Deleting one row may cause loss of data that may be needed for other future rows Blank Diagram (3).png
  3. Modification Anomaly
    1. Changing data in one row forces changes in other rows because of duplicationUpdate Anomaly.png

Data Redundancy

Data Redundancy: When the same piece of data is held in 2 separate places. A positive type of data redundancy works to safeguard data and promote consistency. It is common practice to have a piece of data stored in multiple places, however, the key is to have a centralized field or space for that data, so that there is one place to update all the redundant data. 

Blank Diagram (1).png

Data Integrity

Referential Integrity: Foreign Key of one table must match the Primary Key of another. 

Integrity.png

Entity Integrity: All primary key values must contain data values


1st Normal Form

  • No multivalued attributes

First NF.png

3rd Normal Form

  • 1st Normal rules
  • Every non-key attribute is defined by the primary key, not by part of the key
  • No partial dependencies

2nd NF (1).png

 

This content is subject to limited support.                

 

 

 


0 replies

Be the first to reply!

Reply