A comprehensive database for efficient market management.
This Database organizes information on products, distributors, customers, orders, and other entities. The Market Management System addresses the need for an organized and streamlined approach to managing a market. It aims to provide a centralized database to store and manage information related to products, distributors, customers, orders, and other relevant entities.
The database includes the following entities:
- Products: Information about the products available in the market, including details such as name, description, price, and quantity.
- Suppliers: Details of the Suppliers supplying products to the market,
- Customers: Information about the market's customers,
- Orders: Details of the orders placed by customers,
- Available: Details about which products are available in which Store
The project consists of several phases:
- ER Diagram
- Design and creation of the database schema.
- Convert ER diagram to tables, define keys, create SQL script.
- Store real data in tables, including products, customers, distributors, and orders.
- Perform update and delete operations in the database, including adding a customer, placing an order, updating phone number, and deleting customers without orders.
- Execute SQL queries to retrieve necessary information about the online store, including top-selling products for each distributor and city, distributors with the highest sales, and the number of distributors selling specific products.
- Creation different views
- Modification of the database structure, add is_sold boolean attribute, 'True' when the first product is sold, using Transaction and Rollback
- Implementation of SQL assertions
- Creation of indexes to improve data retrieval performance.
- Creation of a new user in the DBMS with restricted write access to tables and views.
- Creation of a user named "stones_john" with the specified permissions.
All implemented phases are available in main.ipynb and extracted SQL server queries in the Queries folder.