The purpose of this assignment is to allow students to get familiarized with the design and development of a database. You are being provided with requirements for a hypothetical system, named Supermarket Database (SuperDB) that manages sales data. You will undertake the roles of both Database Designer and Database Administrator (DBA) to analyse the requirements, create the specification and design and develop a database that conforms to the specifications.
In particular, in this assignment, you will: • Analyse requirements for a database application, create specifications and record your assumptions
The assignment accounts for 60% towards your final grade.
The SuperDB database stores the sales of a global supermarket chain. The objectives of the system are to store/manage the sales of the supermarkets to customers and to analyse the sales and export useful statistics across multiple dimensions, which are used by supermarket managers for guiding their marketing strategy.
There are three types of users in SuperDB. For each user, the system must verify its identity when accessing the application (via username / password). Note that each user can change his/her password. Additionally, the following information must be kept for each user: Identity, Full Name and Role.
The roles and responsibilities of each user are described below:
In addition to users, the system must keep and manage information about the supermarket chain assets such as Stores, Employees, Products, Clients, etc. A basic set of information that SuperDB should support is listed below: E-1. Customer: Identity, First Name, Last Name, Citizenship (Country), Date of Birth,
E-2. Supplier: Identity, Name E-3. Store: Store Name, Address, Manager, Number of Employees E-4. Employee: Identity, First Name, Surname, Citizenship (Country), Date of Birth,
E-5. Product: Name, Product Category, Barcode Code, Brand, Country of Origin, Size
E-6. Product Category: Name, Super-category to which the category belongs (if any) E-7. Product Offers: It is any combination of products under an offer (e.g., ketchup +
mayonnaise at a specific offer price)
E-8. Customer Invoice: Customer invoices store product sales information to customers. Each invoice records the date and time it was created, the customer it belongs to (if registered in the system) and includes at least one product. Finally, each invoice calculates the total amount of all its products (i.e., quantity * price) E-9. Vendor Invoice: Vendor invoices store product sales information from vendors toeach store SuperDB must support all data management functionality for all information above (i.e., to store, manipulate and search for the data).
Additionally, besides managing the products, SuperDB must be able to generate the following reports:
R-1. Report with products whose name is similar to the name provided by the user. R-2. Report with monthly invoices sorted by total amount in descending order.
R-3. Report with k-highest spending customers for a specific time period. R-4. Report with brands that have no sales in a specific time period. R-5. Products that were sold in all Customer invoices for a specific time period. R-6. List of store sales per year for all years. Note, if a store has not made sales at any time, it will still have to be displayed with a 0 value.
R-7. List of stores with sales above average sales at a specific period. R-8. List with the top 10 suppliers with the highest volume of sales and the 5 with the
lowest volume of sales. Note: You should produce a single report and not two.
R-9. Store Progress Report: Starting from a store’s second month of sales, the system calculates the difference of sales of volume with the previous month.
Report Example (for a specific store)
| Period | Sales | Difference (%) |
|---|---|---|
| February’17 | 1,000.00 ltr | -- |
| March’17 | 1,500.00 ltr | 50% |
| April’17 | 1,200.00 ltr | -20% |
| … | … | … |
R-10. Most Important Stores according to pareto principle
(http://en.wikipedia.org/wiki/Pareto_principle). In particular, for a specific period (year, month), the report should show all stores that contribute to the 80% of the total sales of the period. The rest of the stores should be displayed as other stores
Report Example
| Period: May/2018 - September /2018 | |||
| Store | Sales | Contribution | Cumulative |
| Store - A | 30,000.00 € | 30% | 30% |
| Store - B | 25,000.00 € | 25% | 55% |
| Store - C | 20,000.00 € | 20% | 75% |
| Store - D | 10,000.00 € | 10% | 85% |
| Others (Total) | 15,000.00 € | 15% |
|
not be sufficiently
IMPORTANT: The requirements provided in the previous section may be defined. During the specifications, you will need to record your assumptions and how these have influenced your models.
CS 340 Milestone One Guidelines and Rubric Overview: For this assignment, you will implement the fundamental operations of create, read, update,
Retail Transaction Programming Project Project Requirements: Develop a program to emulate a purchase transaction at a retail store. This
7COM1028 Secure Systems Programming Referral Coursework: Secure
Create a GUI program that:Accepts the following from a user:Item NameItem QuantityItem PriceAllows the user to create a file to store the sales receip
CS 340 Final Project Guidelines and Rubric Overview The final project will encompass developing a web service using a software stack and impleme
