Normalization - 1NF, 2NF, 3NF and 4NF

The video introduces the concept of database normalization, covering 1NF, 2NF, 3NF, and 4NF. An example company selling video game consoles (Xbox One, PlayStation 4, PlayStation Vita) and offering newsletters is used. The initial sales records contain customer names, purchased items, shipping addresses, newsletter subscriptions, supplier information, and price, presented in a denormalized table with issues such as multiple values in single cells and unclear unique identifiers.

The first normal form requires each cell to contain a single value. This involves separating items purchased in a single transaction into multiple rows and ensuring entries in a column are of the same type. For example, 'wholesale' or 'toll-free' in supplier fields should be replaced with actual supplier names. Additionally, rows must be uniquely identified by adding a unique customer ID to differentiate individuals with the same name or address.

Second normal form dictates that all non-key attributes must be dependent on the entire primary key. The initial table, even in 1NF, had issues where attributes like price or supplier information were not dependent on the customer ID (the key). To address this, the data is separated into three tables: a 'customer' table (customer ID, name, shipping address, subscription), an 'item' table (item, supplier, supplier phone, price), and a 'junction' table to link customers and items, preserving transaction information. The 'item' becomes the primary key in its own table, and the junction table uses a compound key of customer ID and item to record purchases.

Third normal form requires that all columns be determined only by the key in the table and no other column (eliminating transitive dependencies). In the item table, the supplier's phone number was dependent on the supplier, not directly on the item itself. This led to redundancy if a supplier's phone number needed to be updated. To normalize, a new 'supplier' table is created with supplier ID and phone number, and the 'supplier' in the 'item' table becomes a foreign key referencing this new table. This ensures no redundant phone number entries and simplifies updates.

Fourth normal form addresses multi-valued dependencies, where a single column can have a different number of values than another column for the same key. The 'customer' table, even after 3NF, still contained customer ID, name, shipping address, and subscription in one table. A customer could have multiple newsletter subscriptions but only one shipping address, leading to redundant customer information if a customer subscribed to multiple newsletters. To achieve 4NF, the customer information and newsletter subscriptions are separated into two distinct tables: a 'customer' table (customer ID, name, shipping address) and a 'newsletter subscription' table (customer ID, newsletter). This removes the multi-valued dependency from the customer table, allowing for flexible management of subscriptions without duplicating core customer data. The video concludes by showing the final relationships between the normalized tables.