Database Management System Project: Online Shop Management Pt.2

Database Management System

According to the course assignment specifications, this project must contain the following things:

1. Minimum 3 Tables and Maximum 5 Tables
2. Standard Queries Showing Their Outputs
3. Triggers
4. Cursors
5. Arrays
6. Functions
7. Procedures
8. File I/0

Requirements

• Admin can set a discount for all or specific type of products
• Admin can remove the discount on all or specific types of products
• Admin can calculate gross sale for a month
• Admin can calculate total salary + commission to be paid to employees for a month
• Admin can pay the commission to all or specific employee any time
• Admin can get single customer or employee info
• Admin can get info of most sold products by category
• Admin can get the info of top 10 most spending customers
• Admin can get a list of products that are less than 10 available in stock
• Admin can award club points to customers
• Admin can update current shipment status of all the orders
• Admin can restock all or specific products
• A customer can’t order products that are unavailable in stock
• A customer can delete an order.
• A customer gets 5% of total order price(not including club point discount price) as club points
• A customer can use club points to buy products
• Every operation (including procedures, function calls, triggers, updates, deletions) are tracked in a table with date
• Operators get 2% commission on the total price of the order
• The total price of an order is automatically calculated
• Only operators can permit an order
• Customers actual payment is automatically calculated
• The quantity of products in stock after each order automatically decreases
• Total quantity sold of a product increases after every order
• Customer’s total spent money increases after every order
• Order details will be automatically filled out

Originally published at imad062.github.io.