Hello, dear friend, you can consult us at any time if you have any questions, add WeChat: daixieit

ASSESSMENT 2

 BRIEF Subject Code and Title MIS602 Data Modelling and Database Design Assessment Database Implementation Individual/Group Individual Length N/A Learning Outcomes

The Subject Learning Outcomes: demonstrated by the successful completion of the task below include: b) Design solutions applying relational database techniques to complex problems and communicate these solutions to all stakeholders.

Task Summary

 In this assessment, you are required to demonstrate your ability to understand the requirements for various data information requests from an existing database and develop appropriate SQL statements to satisfy those requirements.

Context

Being able to query a database is a fundamental skill that is required by all information systems professionals who work with relational databases. In this assessment, you will utilize the basic query skills that are typically used to extract information for analysis, reporting and data cleansing in a data management setting. Timely provisioning of key business information promotes effective communication and enhanced solution delivery.

 Task Instructions

Please read and examine the attached MIS602_Assessment 2_Data Implementation_ Case study carefully and then derive the SQL queries to return the required information. Your focus should be on providing the output as meaningful and presentable as possible. Please note that extra marks will be awarded for the presentation and readability of SQL queries, including the ordering of the columns. Please note that all the SQL queries should be generated using MySQL server either using MySQL workbench or MySQL Command Line Client.

Provide SQL statements and the query output for the following:

No Question

1 Display all the customers having full names longer than 10 characters.    3 Marks

2 List all the names of the customers if their names contain the string 'ON', ignoring the case. 3 Marks

3 Which plan has the highest number of cancelled phone numbers? 3 Marks

4 How many customers have more than one active phone number? 3 Marks

5 Generate a report displaying the total number of customers by each state. 4 Marks

6 Is there any staff who is also a customer? Write an SQL query to support your answer. 4 Marks

7 Display the supervisor's details and the total number of staff they supervise. 4 Marks

8 List all the staff who are not supervisors. 4 Marks

9 Did the total number of calls increase during the COVID lockdown period (2020) compared to 2019? Substantiate your answer with an SQL query. 4 Marks

10 What is the second least popular phone colour among customers? 4 Marks

11 List all the customers who are not active. A customer should be considered not active if they have no active phone number registered under their name. 4 Marks

12 What plan is most popular among customers who are born before the 1960s? 5 Marks

 13 Write a query to display the total income generated by the company in 2019 from call charges based on gender. Assume call duration is recorded in seconds and calls are charged per minute. 5 Marks

14 Is there any mobile plan in the plan table that has never been used? Show this using i. Sub-query ii. Joins 5 Marks

15 Which tower(s) were used by customer 20010 to make the first call? 5 Marks

16 Did any customer break their mobile plan before the mobile plan duration ends? Assume the plan duration is in months. Justify your answer with an SQL query by producing a list of customers who cancelled the plan before the duration expires. 5 Marks

17   i. Create a view showing the popularity of phone colours based on the total number of active users.

       ii. Use this view to show the least popular colour.

18 Write a query to fetch the data about the first call, including the customer details, the phone number the call was made and the total number of towers used for the connection. The expected output of the query is shown below. 5 Marks

19 Who is the youngest and oldest customer of postcode 3181, having an iPhone? 5 Marks

20  In not more than 200 words, comment on whether the tables are in 3NF. Justify your argument with relevant examples, and then explain at least two ways to improve this database based on what you have learned in weeks 1-8. Draw specific examples from the database to support your answer. 5 Marks

Submission Instructions 1. The database used for this assignment is available in the 'MIS602_Assessment 2_Database SQL Files' folder. You need to download and import the file into MySQL Workbench. 2. Copy and paste all the SQL questions into a word document. Under each question, provide the corresponding SQL query, the first 10 lines of the result and the output as evidence of running the query. Submit the word document via the Assessment link in the main navigation menu.

Referencing It is essential that you use the appropriate APA style for citing and referencing research. Please see more information on referencing here http://library.laureate.net.au/research_skills/referencing