2023年11月25日

数据库代写 | Applying Data Analytics Assignment 3

本次澳洲代写主要为数据库相关的assignment

  • Background

In  this  assignment  you  are  required  to demonstrate and  apply  the  concepts and knowledge covered in Week 1 –Week 11.For the purposes of the report, assume that you are employed as a business analyst at Best Innovative Solution (BIS) Pty Ltd. You are assigned to investigate the questions from a new client and provide the solution.

 

The dataset about Hooray Partyware Pty. Ltd. has been collected and you are given the following two files for the purpose of applying data analytics:

  • dataset (HPData.xlsx)
  • header description (HPDataset-header-description.txt)

 

These  two  files  are  available for you  to download on Canvas|  Assignments  | Assessment  Task  3:  Individual  Final  Assessment  |  Individual  Assignment –Case Study,  Specifications  and  Dataset. Further  information  about  the  dataset  is  listed below:

  • The dataset and header description are stored in two separate files.
  • The dataset consists of binary, categorical and numerical data.
  • There are 8different attributes and 1000 cases (or instances).
  • There are some missing values in the dataset.
  • The dataset covers mainly the aspect of customer related data.

 

  • Questions from the new client

You need to provide:  (a) answers to all the following questions based on the case study, (b) mention the word count on the cover page of your report.

 

Question 1:Data Modelling

  • Provide an Entity Relationship Diagram (ERD). For each entity, provide at least one attribute/ field with a maximum of two attributes (excluding primary key).
  • Provide a Relational Model and show the most appropriate attributes/ fields, primary keys, and foreign keys. In addition, include the data type for each attribute/ field listed in each table/ entity.
  • You should apply third normal form (3NF) and show consistency in the use of notation.
  • If you discover while drawing the diagrams that the narrative of case study is incomplete, then provide  justifications,  assumptions,  or  reasonable explanations to complete the case study along with your diagrams. You could include  the  justifications, assumptions, or  reasonable  explanations  under Appendix section.

 

Question 2:SQL Scripts

  • Based on data modelling from Question 1, create 3 queries that have the following requirements. Note that each of the query can cover one or more of the requirements.

O One of the 3 queries should have a calculation.oOneofthe3queries should make use of Group By.

O One of the 3 queries should include a scalar function (one that returns a value).

O One of the 3 queries should demonstrate Nested Query (could be either standard or correlated sub-query).

O One of the 3 queries should demonstrate joining tables.

O One of the 3 queries should demonstrate the creation and testing of Function with test data.

 

  • For each query,

O Explain and justify its business purpose and business value or impact.

O Provide the SQL script.

O Explain the design of SQL script.

O Make use of table(s) with sample data to show potential result/ outcome of the query.