**Course Title:** SQLite Mastery: Lightweight Database Management **Section Title:** SQLite Database Design and Normalization **Topic:** Design and normalize a database schema for a real-world use case.(Lab topic) **Introduction** In this lab topic, we will apply the database design principles and normalization techniques learned in the previous sections to a real-world use case. We will design and normalize a database schema for a fictional e-commerce company, "OnlineShop". The goal is to create a well-structured and efficient database schema that can support the company's operations and scale with growth. **Case Study: OnlineShop** OnlineShop is an e-commerce company that sells products online. The company has the following requirements: * Store information about products, customers, orders, and payments. * Track inventory levels and update product quantities in real-time. * Generate reports on sales, revenue, and customer behavior. * Support multiple payment methods, including credit cards and PayPal. **Step 1: Identify Entities and Attributes** The first step in designing a database schema is to identify the entities and attributes that will be stored in the database. Based on the requirements, we can identify the following entities: * **Products**: + Product ID (primary key) + Product Name + Product Description + Price + Inventory Quantity * **Customers**: + Customer ID (primary key) + Name + Email + Address + Phone Number * **Orders**: + Order ID (primary key) + Customer ID (foreign key) + Order Date + Total Amount * **Order Items**: + Order Item ID (primary key) + Order ID (foreign key) + Product ID (foreign key) + Quantity * **Payments**: + Payment ID (primary key) + Order ID (foreign key) + Payment Method (credit card, PayPal) + Payment Date + Amount **Step 2: Create Entity-Relationship Diagram (ERD)** An ERD is a visual representation of the entities and their relationships. Based on the entities and attributes identified, we can create the following ERD: ```mermaid erDiagram CUSTOMER ||--o{ ORDER : places ORDER ||--|{ ORDER_ITEM : contains ORDER_ITEM }|..|{ PRODUCT : is ORDER ||--|{ PAYMENT : has PAYMENT }|..|{ ORDER : belongs ``` **Step 3: Normalize the Database Schema** Based on the ERD, we can normalize the database schema using the normalization techniques learned earlier. We will apply the following normalization rules: * 1NF: Ensure that each row has a unique combination of values. * 2NF: Ensure that each non-key attribute depends on the entire primary key. * 3NF: Ensure that each non-key attribute depends on the primary key. After applying the normalization rules, we get the following normalized database schema: ```sql CREATE TABLE customers ( customer_id INTEGER PRIMARY KEY, name TEXT NOT NULL, email TEXT NOT NULL, address TEXT NOT NULL, phone_number TEXT NOT NULL ); CREATE TABLE products ( product_id INTEGER PRIMARY KEY, product_name TEXT NOT NULL, product_description TEXT NOT NULL, price REAL NOT NULL, inventory_quantity INTEGER NOT NULL ); CREATE TABLE orders ( order_id INTEGER PRIMARY KEY, customer_id INTEGER NOT NULL, order_date DATE NOT NULL, total_amount REAL NOT NULL, FOREIGN KEY (customer_id) REFERENCES customers (customer_id) ); CREATE TABLE order_items ( order_item_id INTEGER PRIMARY KEY, order_id INTEGER NOT NULL, product_id INTEGER NOT NULL, quantity INTEGER NOT NULL, FOREIGN KEY (order_id) REFERENCES orders (order_id), FOREIGN KEY (product_id) REFERENCES products (product_id) ); CREATE TABLE payments ( payment_id INTEGER PRIMARY KEY, order_id INTEGER NOT NULL, payment_method TEXT NOT NULL, payment_date DATE NOT NULL, amount REAL NOT NULL, FOREIGN KEY (order_id) REFERENCES orders (order_id) ); ``` **Conclusion** In this lab topic, we designed and normalized a database schema for a real-world use case. We applied the database design principles and normalization techniques learned earlier to create a well-structured and efficient database schema that can support the company's operations and scale with growth. For further reading, we recommend the following resources: * [Data Modeling with Entity-Relationship Diagrams](https://www.lucidchart.com/pages/data-modeling-with-entity-relationship-diagrams) * [Normalization in Database Design](https://www.studytonight.com/dbms/data-normalization) **Lab Exercise** Design and normalize a database schema for a real-world use case of your choice. Ensure that you apply the database design principles and normalization techniques learned earlier. **Leave a comment or ask for help** If you have any questions or need help with the lab exercise, please leave a comment below.