Normalization

Data Redundancy & Anomalies

Data Redundancy

Occurs when the same data is stored in multiple places or repeated within the database
leads to inefficiencies and inconsistencies.
Causes data anomalies

Issues:

Insertion Anomalies: New records can’t be added without creating dummy data.

Update Anomalies: Changing data requires updating multiple places, risking inconsistencies.

Deletion Anomalies: Deleting data can unintentionally remove important related information.

How to Avoid Data Redundancy

Functional Dependencies (FD)

Ensures that each attribute in a table belongs to that table, minimizing redundancy.
A relationship where one attribute (X) uniquely determines another attribute (Y), written as X → Y. (Y is functionally dependent on X)
The reverse Y → X may not be true

Transitive Dependency

a column’s value relies upon another column through a second intermediate column
only occur in a relation of three of more attributes.
X → Z is a transitive dependency if the following dependencies hold true:
- X determines Y (X→Y)
- Y does not determine X (Y does not → X)
- Y determines Z ( Y→Z)
- X is a primary key

Partial dependency occurs when a non-prime attribute (a column that’s not part of any candidate key) is dependent on part of a composite primary key, but not on the whole key.

Normalization

primary method to avoid redundancy and anomalies by organizing the database structure effectively
provide mechanisms for transforming schemas in order to remove redundancy.
New Database Design:
- After creating the ERD, normalization is used to analyze the relationships within entities and improve the database structure.
Existing Data Structures
- Helps to analyze and improve existing database structures by analyzing attribute relationships and transforming the design.
Each normal form involves a set of dependency properties that a schema must satisfy
Higher normal forms have less redundancy, fewer update problems

FIRST NORMAL FORM (1NF)

domain of an attribute must include only atomic (simple, indivisible) values
value of any attribute in a tuple/record must be a single value from the domain of that attribute.
disallows relations with relations or relations/tables as attribute values within tuples.

SECOND NORMAL FORM (2NF)

Must be in 1NF and have no partial dependencies
Every non-key attribute must depend on the whole primary key

THIRD NORMAL FORM (3NF)

Must be in 2NF and have no transitive dependencies
Non-key attributes should not depend on other non-key attributes.

Examples

1NF

Using row order to convey information violates 1NF

e.g. height of a group of people, tallest is in the first row

solution: devote a separate column

Mixing data types violates 1NF

Designing a table without a primary key violates 1NF

Storing a repeating group of data items in a single row violates 1NF

2NF

**deletion anomaly **

update anomaly

**insertion anomaly **

3NF
transitive dependency

Examples:

unnormalized: The table has a repeating field – Activity ID. It also has a multi-valued field – City, State, Zip. These problems would have to be resolved to move the data into 1NF