Assessed Module Learning outcomes ALO1: Critically evaluate the appropriateness of how software is structured and how a database is designed. AL02: Apply good software development practice by employing a programming language in combination with the design and implementation of relational databases. . ALO3: Design and implement a computer program of moderate size and complexity. ALO4: Plan and implement testing and verification of the implemented system 1. DESCRIPTION OF THE COURSEWORK. IBM have made available a data set that describes the working of a small chain of coffee shops in the USA. The csv files are available from https://www.kaggle.com/vichang/coffee-shop-sample-data-1113 and the DLE. Dates.csv customer.csv generations.csv (The data from this file should not be used in this coursework) pastry inventory.csv product.csv sales targets.csv (The data from this file should not be used in this coursework) sales_outlet.csv staff.csv 201904 sales reciepts.csv The files describe the staff, shops and products. The shops sell pastries. An important issue is that if the pastries are not sold then they are thrown away. The file "pastry inventory.csv" contains information about the pastries thrown away as waste. The file "staff.csv" contains a reference to HQ. Please assume the HQ has the same address as the warehouse. Design a database to store the data in the csv files. The database design should include an entity relation diagram, using the crowsfoot notation. You can either use MySQL workbench or https://app.diagrams.net/, or another graphical package approved by the module leader. The database design should follow the third normal form (3NF). A SQL schema for all the tables should be produced. Write a short discussion of the design choices for the database. Write a python script to read in the csv files and populate the database. The database should be stored in a SQLite database in a single file. The data from the csv files may have missing values. Discuss how you cleaned up the data. Write a GUI interface, using for example Tkinter, to input new customers to the database. Coursework 2021-22 Page 2 of 4 COMP5000 Demonstrate that the information about the new customer has been correctly added to the database by including a screenshot. Develop an additional GUI that has buttons to do the following analysis after the data has been extracted from the database using SQL. Calculate the total numbers of customers who are Baby Boomers (ages between 1946 and 1964.) Draw a histogram of the "% waste". Calculate the mean waste for the sales outlets. Provide screen-shots to show the statistical analysis part of the GUI working. < What you need to submit to the DLE a) A document (in pdf or word) with a summary of your results. This docum formal academic report. The document should include: i) The entity relationship diagram and the design choices for the databa ii) The SQL schema for all the tables. iii) A discussion of any data cleaning required, if there are for example missing values. 06:14 Demonstrate that the information about the new customer has been correctly added to the database by including a screenshot. Develop an additional GUI that has buttons to do the following analysis after the data has been extracted from the database using SQL. Draw a histogram of the "% waste". . Calculate the mean waste for the sales outlets. Provide screen-shots to show the statistical analysis part of the GUI working. Calculate the total numbers of customers who are Baby Boomers (ages between 1946 and 1964.) What you need to submit to the DLE a) A document (in pdf or word) with a summary of your results. This document is not a formal academic report. The document should include: 2. MARKING SCHEME The total number of marks for this coursework is 100. 3 Subtask 1 The entity relationship diagram. 2 The written discussion of the design choices for the database. The SQL schema for the tables. 4 5 6 7 8 9 i) The entity relationship diagram and the design choices for the database. ii) The SQL schema for all the tables. iii) A discussion of any data cleaning required, if there are for example missing values. iv) The screen-shots showing the working of the GUIS. b) All the python code written for the coursework. I suggest you put all the python code in a zip file. c) The SQLite database you created. The discussion on how you cleaned the data up. The python code to create the database. A brief report about any data cleaning required. The GUI to add a customer to the database. 4G The analysis part of the GUI which produces the histogram plot of waste (5 marks), the mean waste for each sales outlet (5 marks), and the number of customers who are baby boomers (5 marks). The documentation in the python scripts. 3. GRADE CRITERIA Coursework 2021-22 50-59% 60-69% More than 70% When awarding marks for individual criteria, I shall employ the following guidelines, Mark 0-49% < Page 3 of 4 R Weighting 15 marks 10 marks 5 marks 10 marks 10 marks 10 marks 20 marks 15 marks 5 marks Criteria The quality of the work has not met the learning outcomes. The understanding and application of fundamental concepts and techniques is COMP5000 questionable. Work of this quality would not be acceptable in professional employment. The quality of work has only met the threshold level but still requires further work to get it to a better standard. The submission contains logical or analytical errors related to analysis and design techniques. It only demonstrates a basic understanding of the subject competence. Further improvement is required to demonstrate personal thoroughness, effort and independent learning. The quality of the work submitted suggests that you are able to apply the analysis and design techniques well. The work you have submitted is substantially correct and complete. It demonstrates a good unders subject competence and personal thoroughness, effort and indep learning. The quality of work is outstanding with no significant flaws. It dem high level of subject knowledge and competence; personal thorough effort and independent learning; and possibly significant additional analytical/critical thought. Well done!