DBMS L5

Database Normalization

Normalization

a database design technique that reduces data redundancy and eliminates undesirable characteristics like Insertion, Update, and Deletion Anomalies

Data Normalization

Primarily a tool to validate and improve a logical design so that it satisfies certain constraints that avoid unnecessary duplication of data.

Well-Structured Relations

A relation that contains minimal data redundancy and allows users to insert, delete, and update rows without causing data inconsistencies

Insertion Anomaly

adding new rows forces user to create duplicate data

Deletion Anomaly

deleting rows may cause a loss of data that would be needed for other future rows

Modification Anomaly

changing data in a row forces changes to other rows because of duplication

General rule of thumb

A table should not pertain to more than one entity type.

Edgar Codd

The inventor of the relational model

Best Normal Form

in most practical applications, normalization achieves its best in 3rd Normal Form.

1st Normal Form Rules

● Each table cell should contain a single value.

● Each record needs to be unique

SQL KEY

a value used to identify records in a table uniquely.

PRIMARY KEY

A single column value used to identify a database record uniquely.

COMPOSITE KEY

A primary key composed of multiple columns used to identify a record uniquely

2nd Normal Form Rules

● Rule 1 - Be in 1NF

● Rule 2 - Single Column Primary Key that does not functionally dependent on any subset of candidate key relation

FOREIGN KEY

It references the primary key of another table. It helps connect your tables.

3rd Normal Form Rules

There are no transitive functional dependencies

BCNF (Boyce-Codd Normal Form)

• Even when a database is in 3rd Normal Form, still there would be anomalies resulted if it has more than one Candidate Key

• is also referred as 3.5 Normal Form

4NF (Fourth Normal Form) Rules

If no database table instance contains two or more, independent and multivalued data describing the relevant entity, then it is in 4NF.

5NF (Fifth Normal Form) Rules

only if it is in 4NF and cannot be decomposed into any number of smaller tables without loss of data.

6NF (Sixth Normal Form) Rules

not standardized yet, however, it is being discussed by database experts for some time