**Course Title:** SQL Mastery: From Fundamentals to Advanced Techniques **Section Title:** Database Design and Normalization **Topic:** Design a database schema for a real-world scenario and apply normalization principles.(Lab topic) **Objective:** In this lab topic, you will design a database schema for a real-world scenario and apply normalization principles to ensure data integrity and reduce data redundancy. You will apply the concepts learned in the previous topics, including database design principles, normalization, and denormalization. **Real-World Scenario:** Suppose we are designing a database schema for an e-commerce website that sells books, electronics, and clothing. The website has multiple users, each with their own profile, order history, and shopping cart. The website also has multiple products, each with its own description, price, and images. **Step 1: Identify the Entities and Attributes** To design a database schema, we need to identify the entities and attributes involved in the real-world scenario. An entity is a thing or concept that has independent existence, while an attribute is a characteristic of an entity. In our example, we have the following entities: * **Users**: with attributes such as User ID, Name, Email, Password, and Address * **Products**: with attributes such as Product ID, Name, Description, Price, and Category * **Orders**: with attributes such as Order ID, User ID, Order Date, and Total Cost * **Order Items**: with attributes such as Order ID, Product ID, Quantity, and Subtotal * **Shopping Cart**: with attributes such as User ID, Product ID, Quantity, and Subtotal **Step 2: Define the Relationships** Once we have identified the entities and attributes, we need to define the relationships between them. A relationship is a connection between two or more entities. In our example, we have the following relationships: * A **User** can place many **Orders**, but an **Order** is associated with only one **User** (one-to-many). * An **Order** can have many **Order Items**, but an **Order Item** is associated with only one **Order** (one-to-many). * A **Product** can be part of many **Order Items**, but an **Order Item** is associated with only one **Product** (many-to-one). * A **User** can have many **Shopping Cart** items, but a **Shopping Cart** item is associated with only one **User** (one-to-many). **Step 3: Apply Normalization Principles** Once we have defined the relationships, we need to apply normalization principles to ensure data integrity and reduce data redundancy. We will apply the first three normal forms (1NF, 2NF, and 3NF) to our database schema. **Step 3.1: First Normal Form (1NF)** To comply with 1NF, we need to ensure that each row in the table has a unique combination of values. In our example, we need to ensure that each **Order** has a unique **Order ID**, and each **Order Item** has a unique combination of **Order ID** and **Product ID**. **Step 3.2: Second Normal Form (2NF)** To comply with 2NF, we need to ensure that each non-key attribute in a table depends on the entire primary key. In our example, we need to ensure that each **Order Item** has a **Quantity** that depends on the entire primary key (**Order ID** and **Product ID**). **Step 3.3: Third Normal Form (3NF)** To comply with 3NF, we need to ensure that if a table is not in second normal form, it should be split into two tables, one containing the data that depends on the entire primary key, and the other containing the data that does not. In our example, we need to ensure that the **Product** table does not contain any data that depends on the **Order ID**. **Database Schema:** Based on the above steps, we can design a database schema for our e-commerce website. ```sql -- Create the tables CREATE TABLE Users ( User ID INT PRIMARY KEY, Name VARCHAR(255), Email VARCHAR(255), Password VARCHAR(255), Address VARCHAR(255) ); CREATE TABLE Products ( Product ID INT PRIMARY KEY, Name VARCHAR(255), Description VARCHAR(255), Price DECIMAL(10, 2), Category VARCHAR(255) ); CREATE TABLE Orders ( Order ID INT PRIMARY KEY, User ID INT, Order Date DATE, Total Cost DECIMAL(10, 2), FOREIGN KEY (User ID) REFERENCES Users(User ID) ); CREATE TABLE Order Items ( Order ID INT, Product ID INT, Quantity INT, Subtotal DECIMAL(10, 2), PRIMARY KEY (Order ID, Product ID), FOREIGN KEY (Order ID) REFERENCES Orders(Order ID), FOREIGN KEY (Product ID) REFERENCES Products(Product ID) ); CREATE TABLE Shopping Cart ( User ID INT, Product ID INT, Quantity INT, Subtotal DECIMAL(10, 2), PRIMARY KEY (User ID, Product ID), FOREIGN KEY (User ID) REFERENCES Users(User ID), FOREIGN KEY (Product ID) REFERENCES Products(Product ID) ); ``` **Conclusion:** In this lab topic, we designed a database schema for a real-world scenario and applied normalization principles to ensure data integrity and reduce data redundancy. We identified the entities and attributes, defined the relationships, and applied normalization principles to create a well-designed database schema. **Practice Exercise:** Design a database schema for an e-commerce website that sells books, electronics, and clothing. Ensure that the schema complies with the first three normal forms (1NF, 2NF, and 3NF). **Additional Resources:** * For more information on database normalization, please visit the following link: [Database Normalization](https://en.wikipedia.org/wiki/Database_normalization) * For more information on database design principles, please visit the following link: [Database Design Principles](https://docs.microsoft.com/en-us/azure/architecture/data-guide/database-design-principles) **Leave a comment or ask a question:** If you have any questions or need further clarification on any of the concepts discussed in this topic, please leave a comment below. Our instructors and peers will be happy to help. **What's next?** In the next topic, we will discuss understanding transactions and ACID properties (Atomicity, Consistency, Isolation, Durability).