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

[LookerExperts]

Knowledge Drop

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 data
2. Deletion Anomaly
   1. Deleting one row may cause loss of data that may be needed for other future rows
3. Modification Anomaly
   1. Changing data in one row forces changes in other rows because of duplication

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. 

Data Integrity

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

Entity Integrity: All primary key values must contain data values

---

1st Normal Form

• No multivalued attributes

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

This content is subject to limited support.