Data Normalization in DBMS

• Database Management Systems (DBMS) are software systems used to store, retrieve, and run queries on data
• A DBMS serves as an interface between end-users and databases, enabling efficient data management
• Data normalization is the process of organizing data in a database to reduce redundancy and improve data integrity
• It involves dividing databases into tables and defining relationships between the tables
• Normalization aims to isolate data so that amendments to an attribute can be made in one table only
• The goal is to eliminate redundant data and ensure data dependencies are logical

Benefits of Data Normalization

• Minimizes data redundancy, reducing storage space
• Eliminates data anomalies, preventing update, insertion, and deletion anomalies
• Improves data consistency, ensuring data is accurate and reliable
• Simplifies data modification, making it easier to update and maintain the database
• Enhances data integrity by enforcing constraints and rules
• Makes database design more flexible and adaptable to changes
• Optimizes query performance by reducing the amount of data that needs to be scanned

Normal Forms

• Normal forms are a series of guidelines that help to ensure that a database is normalized
• Each normal form builds on the previous one, with higher normal forms providing stronger guarantees of data integrity
• The most commonly used normal forms are First Normal Form (1NF), Second Normal Form (2NF), Third Normal Form (3NF), and Boyce-Codd Normal Form (BCNF)

First Normal Form (1NF)

• A table is in 1NF if each column contains only atomic values
• Atomic value means that a column cannot hold multiple values or composite values
• Eliminate repeating groups by creating separate tables for each set of related attributes
• Identify each table with a primary key

Second Normal Form (2NF)

• A table is in 2NF if it is in 1NF and all non-key attributes are fully functionally dependent on the primary key
• This means that every non-key attribute must depend on the entire primary key, not just part of it
• 2NF is only relevant for tables with composite primary keys
• If a table has a single-column primary key, it is automatically in 2NF if it is in 1NF
• Remove partial dependencies by creating new tables and relating them using foreign keys

Third Normal Form (3NF)

• A table is in 3NF if it is in 2NF and no non-key attribute is transitively dependent on the primary key
• Transitive dependency means that a non-key attribute depends on another non-key attribute
• Eliminate transitive dependencies by creating new tables and relating them with foreign keys

Boyce-Codd Normal Form (BCNF)

• BCNF is a stronger version of 3NF
• A table is in BCNF if for every functional dependency X -> Y, X is a superkey
• Every determinant in the table must be a superkey
• A superkey is a set of attributes that uniquely identifies a tuple in a table
• BCNF addresses certain types of anomalies that 3NF does not handle
• BCNF is rarely violated in practical database design

Higher Normal Forms

• Fourth Normal Form (4NF): Deals with multi-valued dependencies
• Fifth Normal Form (5NF): Deals with join dependencies
• Domain-Key Normal Form (DKNF): Aims to eliminate all anomalies by specifying domain constraints and key constraints
• These higher normal forms are less commonly used in practice

Denormalization

• Denormalization is the process of adding redundancy to a database to improve performance
• It is the reverse of normalization
• Denormalization can be useful in situations where query performance is critical
• It can improve performance by reducing the number of joins required to retrieve data
• It should be used with caution, as it can increase data redundancy and the risk of data anomalies
• Examples of denormalization techniques include adding redundant columns, creating summary tables, and pre-joining tables

Functional Dependency

• Functional dependency is a constraint between two sets of attributes in a relation from a database
• Given a relation R, a set of attributes X in R is said to functionally determine another set of attributes Y, also in R, if and only if each X value is associated with precisely one Y value
• The dependency is represented as X -> Y
• X is called the determinant, and Y is called the dependent

Keys in Database

• Super Key: A set of attributes that uniquely identifies a tuple within a relation
• Candidate Key: A minimal super key, meaning no subset of the key is also a super key
• Primary Key: A candidate key chosen to be the main identifier for the relation
• Foreign Key: An attribute in one table that refers to the primary key of another table, used to establish relationships between tables

Anomalies in Databases

• Update Anomaly: Occurs when the same data is stored multiple times, and an update to the data is not reflected in all locations
• Insertion Anomaly: Occurs when it is not possible to insert a new record into the database due to a lack of related data
• Deletion Anomaly: Occurs when deleting a record results in the loss of other related data