Reference no: EM133424144

Write a 1500 word report on database design and its implementation based on the scenario below (100 marks) "The Mutz Nutz" is a dog grooming salon that provides a variety of grooming treatments for dogs. The number of dogs attending the salon is increasing and the spreadsheet they are using is getting more and more complicated to use. The salon have approached you and would like you to create a database to store this information. This will help them to grow their business, for example, keeping track of appointments, generating invoices, running business reports, targeted marketing etc. The amount of data that will be stored means that a Relational Database would be the best solution to achieve this. There are two separate tasks in this CW, with multiple parts to each.

TASK 1 - Relational Database and TASK 2 - Data Visualisation

TASK 1 - Create a Relational Database This Dataset should be used for TASK 1: MutzNutzCWTask1RelationalDatabase This spreadsheet contains relevant information about the dataset which you must use when answering the following questions. Please note that code should be written in the report with screenshots showing successful execution of the commands - code must not be included as a screenshot as it will not be marked. A) With specific reference to the dataset, explain why it should be converted into a relational database and what are the advantages of doing so. B) Demonstrate the detailed processes of implementing the relational database:

i. Show each stage of normalisation; table design, rules, changes made and the justification for them.

ii. Produce a "Physical" Entity-Relationship Diagram of the final DB schema using Crow's Foot notation.

iii. Write SQL commands to create the tables and populate them with data. Include proof of successful creation of the database.

C) Write some SQL code for the following queries together with a screenshot of the results and a brief explanation of key elements of the code:

i. Write a query to output the owners contact details along with their dogs name. You should output the column headings as specified here: 'Owner Name', Telephone Number', 'Email Address', 'Dogs Name'.

ii. Find which dog has visited the salon the most. Output their name, contact details and the total number of visits. iii. Find which owner has spent the most on treatments overall at the salon. Output the owners name and total amount spent. You should only output one result. iv. Output a list of treatments and how many times they have been carried out. Output the treatment name and a total figure of how many times it has been requested. The output list should be ordered by most to least popular.

TASK 2 - Data Visualisation This dataset should be used for TASK 2: MutzNutzCWTask2DataVisualisation This dataset is different to the one used in TASK 1. Further explanation is given in the spreadsheet about the dataset. Use this to do the following:

A) You need to create 4 graphs; each one should be of a different type. Each graph should illustrate appropriate information from the data, and you should justify your choices. You must demonstrate their implementation in Python. Both the code (in-text) and the output graph (screenshot) itself should be included in the report.