**Course Title:** SQL Mastery: From Fundamentals to Advanced Techniques **Section Title:** Database Design and Normalization **Topic:** Understanding normalization and normal forms (1NF, 2NF, 3NF) Normalizing a database is the process of organizing data in a database to minimize data redundancy and dependency. This process involves breaking down large tables into smaller ones while linking them through relationships. In this topic, we'll delve into the world of normalization and explore the first three normal forms (1NF, 2NF, and 3NF). **What is Normalization?** Normalization is a technique used to design and structure relational databases. The main goals of normalization are: * Eliminate data redundancy and inconsistency * Improve data integrity and scalability * Reduce data anomalies (insertion, deletion, and update anomalies) **First Normal Form (1NF)** A table is in first normal form if it contains no repeating groups or arrays. In other words, each row in the table should have a unique combination of values. Example of a table that's not in 1NF: | Order ID | Customer Name | Order Date | Product 1 | Product 2 | | --- | --- | --- | --- | --- | | 1 | John Smith | 2022-01-01 | Book | Pen | To put this table into 1NF, we need to eliminate the repeating group (Product 1 and Product 2) and create a separate table for products: **Orders table** | Order ID | Customer Name | Order Date | | --- | --- | --- | | 1 | John Smith | 2022-01-01 | **Products table** | Product ID | Order ID | Product Name | | --- | --- | --- | | 1 | 1 | Book | | 2 | 1 | Pen | **Second Normal Form (2NF)** A table is in second normal form if it is in 1NF and all non-key attributes (columns) depend on the entire primary key. Example of a table that's not in 2NF: **Orders table** | Order ID | Order Date | Customer Name | | --- | --- | --- | | 1 | 2022-01-01 | John Smith | | 2 | 2022-01-02 | Jane Doe | | 3 | 2022-01-03 | John Smith | In this example, the Customer Name is dependent only on the Customer ID, not the Order ID. We should move the Customer Name to a separate table: **Orders table** | Order ID | Order Date | Customer ID | | --- | --- | --- | | 1 | 2022-01-01 | 1 | | 2 | 2022-01-02 | 2 | | 3 | 2022-01-03 | 1 | **Customers table** | Customer ID | Customer Name | | --- | --- | | 1 | John Smith | | 2 | Jane Doe | **Third Normal Form (3NF)** A table is in third normal form if it is in 2NF and there are no transitive dependencies. Example of a table that's not in 3NF: **Orders table** | Order ID | Order Date | Customer ID | Salesperson Name | | --- | --- | --- | --- | | 1 | 2022-01-01 | 1 | John Smith | | 2 | 2022-01-02 | 2 | Jane Doe | | 3 | 2022-01-03 | 1 | John Smith | In this example, the Salesperson Name is dependent on the Salesperson ID. We should create a separate table for salespeople: **Orders table** | Order ID | Order Date | Customer ID | Salesperson ID | | --- | --- | --- | --- | | 1 | 2022-01-01 | 1 | 1 | | 2 | 2022-01-02 | 2 | 2 | | 3 | 2022-01-03 | 1 | 1 | **Salespeople table** | Salesperson ID | Salesperson Name | | --- | --- | | 1 | John Smith | | 2 | Jane Doe | **Normalization Practical Exercise** Practice normalizing the given database schema using the concepts learned above. Suppose you have a database for an e-commerce website with the following table structure: **Products table** | Product ID | Product Name | Product Description | Product Price | Category Name | | --- | --- | --- | --- | --- | | 1 | iPhone 13 | iPhone 13 with 512GB storage | 999.99 | Smartphones | | 2 | Samsung TV | Samsung TV with 4K resolution | 1299.99 | Electronics | | 3 | Sony Laptop | Sony Laptop with 16GB RAM | 999.99 | Laptops | Normalize this table to 3NF. **Additional Resources** For more information on normalization and database design, visit these external resources: * [Database Normalization](https://en.wikipedia.org/wiki/Database_normalization) (Wikipedia) * [Normalization Tutorial](https://www.tutorialspoint.com/Normalization/Normalization) (Tutorialspoint) **Share Your Thoughts** Have questions or comments about normalization and database design? Share your thoughts below and help us improve this course material. Now that you've learned about normalization and the first three normal forms, you're ready to dive into the world of denormalization and performance trade-offs. Stay tuned for the next topic: [Dealing with Denormalization and Performance Trade-offs](https://link-to-next-topic).