Analysis代做、SQL编程语言调试、data代写、代写SQL代写

日期：2020-06-20 11:05

Punky Pet Shop Supplies Analysis

Because of your recent training in database design and information retrieval, Punky Pets has just hired you to design and build a database system to analyse their data and update their current relational database system.

Punky Pets is an online and storefront pet shop in ten locations around Australia. It sells pet supplies and some live animals (only at its stores). Punky Pets holds open days at their pet stores. Current pet owners are encouraged to bring their well-behaved pets. Punky Pets invites animal shelters and pet societies to have stalls to encourage rescue pet adoptions and provide information on pet care. Mobile food vendors are invited to cater for the events. The events appear to be successful. However, they do require staff time to organise and necessitate additional staff on these event days. As part of the agreement to be allowed a stall, each stall holder is requested to collect information from people that come to their stall.

Not knowing how to merge the data with their current customer database, Punky Pets has been putting the data into an Excel file. A sample can be seen in Table 1 below. The spreadsheet is used to generate emails to potential customers and for management reports on the success of each event. Now that the events have been running for two years, the spreadsheet has become very large and difficult to analyse and manage. Punky Pets considers itself a very ethical organisation and wants to make sure that the gathered data are treated with respect. Only those people that have asked for further contact are placed on their mailing list for newsletters and future promotions. Punky Pets also want to make sure that current customers do not receive duplicate emails. Pets of loyalty members are treated as if they are family and are sent birthday cards with treats.

Punky Pets’ current customer database has approximately 10,000 active customers (i.e., they have made purchases over the past 12 months) and 20,000 inactive customers. The data they keep about their current customers is a CustomerId (a unique number for each customer), first name, last name, date of birth (used to validate online accounts), gender, address and discount given if a loyalty member. For loyalty members: pet name, pet birthday, pet type and if still owned (do not want to send a birthday treat to a dead pet). For each purchase made they record: product purchased, qty, amount paid, date, store, store address, staff member who fulfilled the order, and payment type. This system is linked to an inventory and accounting system.

Table 1: Sample data from the events.

In questions 1 to 5 treat the system as if it were an OLTP (On Line Transaction Processing) development.

Question 1  

Start your report with a section to that explains and shows the conversion of the Excel spreadsheet into normalized tables. To do this describe and illustrate the process of normalizing the attributes shown in Table 1 to produce a set of well-designed 3NF relations. Do not include the current customer database.

Question 2

Now it is time to merge the view from Section 1 with the current database. You are to build the new database in MySQL. Do not add new fields unless required to achieve normalization. Attach the final .mwb file below. You may place any comments or assumptions about the model in Section 2 of your report.

Build your database in MySQL. It is your choice to either create a model of the tables and then have MySQL draw the entity relationship (ERD). You may need to adjust the ERD for it to be correct, OR start with the ERD and then adjust the tables.

Hints: 1. Either way, draw an ER diagram representing a logical model of the relations that you have identified in Question 1 on paper first. Then convert as per the steps used in the course. Careful of the normalization when merging.

2. For the final tables and ERD be sure to check that the multiplicities, primary keys, and foreign keys are shown. Include any assumptions you have made.

3. Forward Engineer to test.

DO NOT forget to ATTACH .mwb FILE.

Question 3

In Section Three of your report describe testing plans and procedures for the database you designed in Question 2. Use examples and references to support your plans.  Max 300 words excluding counting your references.

Add enough data to your database for some of the testing. Save the insert commands in a .sql file and attach below.

Question 4

In Section Four of your report, describe security and recovery plans and procedures for the database you designed in Question 2. Use examples and references to support your plans.  Max 500 words excluding counting your references.

Question 5

Based on your data model in question 2, compose two (2) interesting and useful queries.    For each query, in section 5 of your report, explain in one or two sentences why the query is interesting and useful.

Attach the two queries saved in two sql files, one to this question and one to the next.

Question 6

Attach your second query here (.sql file).

Question 7

In section 6 of your report, explain how, if this system was implemented using a distributed database management system (DDBMS), it could differ from the current system. Some of the differences you should address include (but should not be limited to): the data structure; and the advantages and disadvantages of implementing a DDBMS . 500 words maximum not counting your references.

Note: you must use supporting references.

Question 8

In section 7 of your report, you are to write a short research and reflection essay based on the topic below. The maximum word length is 1,000 words excluding counting your references.

This essay should demonstrate what you have learnt in this course, your ability to acquire new skills through a short literature search and reflection on your experience in this course.

Topic: Scientists around the world are currently working on projects to find a vaccine or a cure for COVID19. Imagine you were appointed as the global DBA for a project to bring together information from those that are willing to share. Reflecting on your team work this semester with the knowledge and skills that you have acquired, how will you be able to assist with the planning and implementation of such a database. Your answer should concentrate on three issues including one that involves teamwork.

Question 9

You should attach your Word file here as a backup once you have submitted your final version to Turnitin.