database analytics

1. Using Crow’s foot notation, design a logical data model (ERD) for the AutoSale's database in a modelling tool of your choice. All entity types, attributes and the relationships between them must be labelled clearly. Your design should show all cardinality and participation constraints accurately. Discuss and justify your design choices in detail. [10]

2. Produce a script using appropriate SQL commands (DDL) to create a set of database tables using MySQL relational database. Your tables should also show all constraints applied at either the column or the table level. You are expected to implement the database so that it must meets the requirements described in the scenario. [10]

3. By using appropriate SQL statements (DML), populate all the tables in the database you created with some data (At least 10 records in each table). The data should be meaningful but does not need to be extensive. [10]

4. Critically evaluate following data analytics technologies/tools and explain how these can be utilised by the BuyUs company to derive value from the data [10]

I. Hadoop

II. Data warehouse

III. Data Mining

Section 2 - Data warehouse (20%)

Task 2: Designing Star Schema

Using Kimball’s four-step dimensional design process, Draw/design a Star Schema of BuyUs that will allow the company to investigate about why some cities have good sales whilst others have not. Discuss and justify your design choices. You may make any reasonable assumptions to support your design (state any assumptions you make). [20]

Section 3 - Data Analytics (40%)

In this scenario, you are hire as a Data Analyst by a reputable marketing firm. Your client is a telecom company based in the USA and operating in various States. The telecom company is going to launch a new service. Since existing customers are more likely to require new services from the company, they would like to find out in which State a new service can be advertised and sold. The company needs to determine that the services with high benefits could be sold.

To solve this problem, you will use publicly available data sets that have been prepared for you and are available in the appendices.

The given dataset has 3334 entries and 21 attributes, which show the following customer information.

Note: (You will find the required customer data set Excel sheet in the appendix)

Task 3 – Data Analysis

1. You must use Tableau to analyse and disclose various associations within the data set provided. Using Tableau, find answers to the following questions based on your analysis. You will need to provide a rationale and justification for your solution.

I. Using Tableau perform data integrity checks on each individual column of the given data set [5]

II. Using Tableau produce the summarized statistics on number of voice mail messages from the data set e.g. Distinct in all states [5]

III. Using Tableau find the average value of total night calls in minutes [5]

IV. Using Tableau find out the maximum number of voice mail messages for OH, OK and RI states [5]

V. Using Tableau find out the sum of total day calls for customers that have not churned and belong to OH and NY states [5]

Each answer of the above question must have a separate worksheet with a chart, graph or a table that shows your findings.

2. Critically reflect on your learning and evaluate the strengths and weaknesses of the data analytics tool used and recommend other data analytics tools which could have been used for the further enhancement. [15]

Grading Guidance

Distinction:

Excellent in-depth application and critical research on the processes and user requirements. Provide in-depth knowledge of how to design and evaluate a complete Database, data warehouse for a given scenario.

Excellent in-depth understanding and demonstration of data analytics using tableau.

Commendation:

Shows good research on the processes and user requirements. Provides good knowledge of how to design and Database, data warehouse for a given scenario. Shows good understanding and evaluation of data warehouse, data mining and Hadoop.

Pass:

Provides basic understanding of the deliverables. Provides end-to-end design and all requirements are met. Report has some errors and lacks adequate explanation. The robustness and correctness of Database, data warehouse and data analytics are not explained thoroughly. Evidence of design is shown but inadequate explanation using tableau.

Fail:

Provides incomplete attempt or lacks substantial parts of the deliverables. Fails to demonstrate understanding of the concepts required to implement deliverables. Work lacks serious clarity and detail. There are several errors in the report.

(last accessed on 05th August 2019)

Formative Feedback

There will be an opportunity for formative feedback during the semester. You are advised to start working on this assignment as early as possible so that you can seek clarification from the module tutor regarding any questions you might have during the semester. Note that tutors will not predict your grade, and you should not take the lack of comment on any aspect of your work as indicating that it is correct. You should make every effort to take advantage of formative feedback as tutors will not comment on draft work at other times. Remember that you will get more useful feedback from us by asking specific questions than just presenting us with your documentation and asking, ‘Is this right?’

Penalties for Exceeding Word Limits:

The actual word count is to be declared on the front of the assessment submission.

(Last accessed on 05th August 2019)

For clarity: a late piece of work that would have scored 65%, 55% or 45% had it been handed in on time will be awarded 55%, 45% or 35% respectively as 10% of the total available marks will have been deducted.

Failure to submit: The University requires all students to submit assessed coursework by the deadline stated in the assessment brief.  Where coursework is submitted without approval after the published hand-in deadline, penalties will be applied as defined in the University Policy on the Late Submission of Work.

Appendix : (double click on excel file icon to open)