How does referential integrity work in databases?

Database normalization is a process of organizing data in a database to reduce redundancy and improve data integrity.

Goals of normalization:

• Eliminate redundant data (repetition of data across the database)
• Ensure data dependencies make sense (only storing related data in a table)
• Simplify the database structure for easier maintenance

Normal forms:

• First Normal Form (1NF): Ensures that each table cell contains only atomic (indivisible) values, and each record is unique.
• Second Normal Form (2NF): Meets 1NF and all non-key attributes are fully functional dependent on the primary key.
• Third Normal Form (3NF): Meets 2NF and all attributes are only dependent on the primary key.
• Boyce-Codd Normal Form (BCNF): A stronger version of 3NF.

Example:

Suppose we have a table:

OrderID	CustomerName	CustomerAddress	Product	Quantity
1	Alice	123 Main St	Widget	4
2	Bob	456 Elm St	Gadget	2
3	Alice	123 Main St	Gizmo	1

This table is not normalized because customer data is repeated.

Normalization steps:

1. Create separate Customer table:

CustomerID	CustomerName	CustomerAddress
1	Alice	123 Main St
2	Bob	456 Elm St

2. Link orders to customers via foreign key:

OrderID	CustomerID	Product	Quantity
1	1	Widget	4
2	2	Gadget	2
3	1	Gizmo	1

This reduces redundancy and improves data consistency.

In summary, normalization is a systematic approach to decomposing tables to eliminate redundancy and dependency, thereby ensuring data integrity and efficient data management.