This project is done as the final assignment of the advanced database course.
Task: ABC company is an online shop that sells products around Europe. I am responsible for designing a database for their website. In particular, I am tasked with designing and developing a comprehensive database schema.
To achieve this, I have decided to use the MariaDB MySQL database.
I have defined my main entities, such as customers, products, suppliers, etc.
I created an ER model to define the relationships between my entities.
Then, I created an ER diagram that represents the details of each table's attributes, defines primary and foreign keys, and normalizes the tables to achieve the 3rd Normal Form (3NF).
My final ER diagram contains the following tables:
- categories
- comments
- currencies
- customers
- images
- order_segments
- orders
- product_suppliers
- product_tags
- products
- returned_orders
- suppliers
- tags
I have inserted at least 10 records for each of these tables.
I have created stored procedures in my database to report the following:
- Detailed information about suppliers and the number of products they provide.
- The 10 best-selling products with the total amount and their suppliers.
- A list of customers and their total purchases.
- A list of returned items.
- A list of products in the fashion category that were sold last month.
Additionally, I have provided my queries in the reports folder separately.
Sabera Jamila Zinia
Gisma University of Applied Sciences
ID: GH1026804