DataBase Normalization

(From https://www.geeksforgeeks.org/introduction-of-database-normalization/)

Database normalization is the process of organizing data in a database so that it is consistent, efficient, and easy to manage.

There are several levels of normalization, each with their own benefits and use cases. Here we shall cover the basics of normalization, including first normal form (1NF), second normal form (2NF), third normal form (3NF), as well as other advanced normal forms.

Anomalies

The primary objective for normalizing the relations is to eliminate the anomalies below. Failure to reduce anomalies results in data redundancy, which may threaten data integrity and cause additional issues as the database increases.

Insertion Anomalies: Insertion anomalies occur when it is not possible to insert data into a database because the required fields are missing or because the data is incomplete. For example, if a database requires that every record has a primary key, but no value is provided for a particular record, it cannot be inserted into the database.
Deletion anomalies: Deletion anomalies occur when deleting a record from a database and can result in the unintentional loss of data. For example, if a database contains information about customers and orders, deleting a customer record may also delete all the orders associated with that customer.
Updation anomalies: Updation anomalies occur when modifying data in a database and can result in inconsistencies or errors. For example, if a database contains information about employees and their salaries, updating an employee’s salary in one record but not in all related records could lead to incorrect calculations and reporting.

Normalisation consists of a set of procedures that assist you in developing an effective database structure.

Functional Dependency and its Types

Functional Dependency is a constraint between two sets of attributes about a database. A function dependency A -> B means for all instances of a particular value of A, there is the same value of B. For example in the below table, A -> B is true, but B -> A is not true as there are different values of A for B = 3.

...

Trivial Functional Dependency

X -> Y is trivial only when Y is a subset of X

Examples:

ABC -> AB
ABC -> A
ABC -> ABC

Non Trivial Functional Dependencies

X -> Y is a non-trivial functional dependency when Y is not a subset of X.

X -> Y is called completely non-trivial when X intersect Y is NULL.

Example:

Id -> Name
Name -> Date_of_Birth

Semi Non Trivial Functional Dependencies

X -> Y is called semi non-trivial when X intersect Y is not NULL.

Examples:

AB -> BC
AD -> DC