Krishna Bakery Pvt. Ltd is a bakery shop. It deals with confectioneries, cakes, cookies, breads, loafs, and other snacks items. It has multiple customers. The manager wants an online application to keep track of its branch (to be opened soon, all over Nepal) with a unique branchID, branchName, address, phone and email.
Each customer has a unique customerID, fullName, username, password, address, phone and email. A customer should be able to make multiple orders. Any order can have more than one items along with its quantity and optional delivery address. The delivery for Kathmandu District will be free but there will be nominal delivery charge for all other places based upon the company terms and conditions. The orders will be delivered at home (or at requested place by customer) or alternatively, the customer can pick-up their order at specified date and time. Furthermore, the customer should be able to track their order history.
Regarding the payment scheme, it offers, eSewa online payment, SMS fund transfer, bank transfer and COD (Cash On Delivery, terms and conditions are applied).
The manager wants the following reports but not limited to:
i)Lists of all the sales for the day ( End-of-day sales report) and/or during the specified duration
ii)Top 5 most liked items.
iii)Top 5 most profitable items.
iv)List of items that were never sold.
v) List of dues to be paid by any customer ( assuming, items were sold on credit for some institutions or individual having creditLimit ) .
- Design ER- Model
- Design the Relational Model
- Write SQL : DDL and DML statements to fulfill the reports
- Implement it in
- SQL Server 2014
- Oracle 11g XE
- Use PHP, Java, Android, ASP .NET or any other visual language to build the application that supports at least CRUD operations.