This is an individual coursework. You are required to develop an information system based on a computer sales and maintenance store of your choice. The store has a number of branches and there are a number of personnel working in each branch. Personnel can be reassigned to other branches to work. The store’s services include computer sales, repair computer parts and installing and upgrading software and hardware.
There are multiple owners of the store and they wish to have an information system that gives them up to date information about the current status of the business. The owners are often in different locations and therefore the system needs to be available from multiple remote locations. Furthermore, the owners have very little technical skill and therefore, the information presented to the owners must be in visual form (e.g. a dashboard).
The store would like to use the system for analysing sales, cost and customer trends. They expect it to be constructed in a way such that a range of queries are available to them. The owner’s most urgent need is to have information concerning the volume of sales of different products and services, the volume of sales in each location, and the performance of their sales personnel.
You may assume the following for simplification purposes:
• There are only 6 months of trading data available.
• The store has 8 personnel.
• The store has 3 branches in the city.
• The store provides only 12 different products and services.
Requirements:
1. You must store the data in a relational format using MySQL. You can assume that this database is automatically updated when a sales event occurs. Therefore, you only need to populate the database with static data.
2. Populate the database with a small amount of data. The data should be meaningful but does not need to be extensive. The following sites may be useful for quickly generating data:
• http://www.databasetestdata.com/
• http://www.generatedata.com/
3. Use pivot tables in Excel to visualise the data in the system.
4. The connectivity will be handled using ODBC (Open Database Connectivity).
5. You must supply at least five visualisations for your system. An example of a useful interactive visualisation might be the following:
• A chart that shows the overall sales per month in the six months of trading. Allow the owner to further select a specific product or services to see its sales over the six months.
• A chart that shows the overall sales per employee.
State any assumptions that you make in modelling the business.
6. You must produce a report with some design documents. Detailed report requirements are described under report section.
Report:
Your final report must contain the following sections:
1. A short introduction to explain the store requirements and assumptions
2. An Entity Relationship Diagram (ERD) of the conceptual design and the relational schema developed from this
3. The SQL code used to create your database tables
4. The SQL code for your queries
5. A screenshot of the data returned from one query that will be used for a visualisation
6. A short description of the purpose of each pivot table and visualisation
7. Screenshots of your visualisations
8. A paragraph on what you would do differently if you were to do the coursework again
9. A self-assessment form and references
Last Completed Projects
| topic title | academic level | Writer | delivered |
|---|