Online Clothing Store Database

Description

This project involves creating a comprehensive database for an online clothing store. The database consists of five tables and is designed to handle various store operations efficiently. Joins and views are utilized to retrieve specific results, and a custom function was developed to apply discounts on orders exceeding £150.

Key Features

• Five Tables: Designed and created using best practices for relational databases.
• Joins & Views: Used for retrieving specific sets of data efficiently.
• Discount Function: Automatically applies discounts for qualifying orders (over £150).

Reflection

The sequence of table creation and command execution was critical in ensuring smooth database setup and functionality. Starting with an Entity-Relationship Diagram (ERD) allowed me to visualize the relationships between different entities clearly. This step helped in optimizing the database structure for efficient data management and retrieval.

Tech Stack

• Database: MySQL
• Query Language: SQL
• Diagramming Tool: ERD

Files

1. seed_and_table_creation.sql: Contains the SQL script for seeding the database and creating the tables.
2. queries_and_functions.sql: Includes the queries used for joins, views, and the discount function.
3. ERD_Diagram.png: Visual representation of the database schema showing the relationships between tables.

How to Use

1. Set up the Database: Run the script in seed_and_table_creation.sql to create and seed the necessary tables.
2. Run Queries: Use the queries_and_functions.sql file to apply specific queries and the discount function.
3. View ERD: Refer to the ERD_Diagram.png for a visual understanding of the database structure.

Future Improvements

• Implement user authentication for order tracking.
• Expand the product catalog and add filtering options for various clothing categories.
• Introduce advanced reporting features for sales and customer trends.