MIS602 - Data Modelling and Database Design Assignment

Assignment Task

Learning Outcomes

The Subject Learning Outcomes demonstrated by the successful completion of the task below include: 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 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.

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

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

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

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

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

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

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

8 List all the staff who are not supervisors. 

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

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

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. 

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

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.

14 Is there any mobile plan in the plan table that has never been used? Show this using

1. Sub-query
2. Joins 

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

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.

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. 

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.

This Database Design and Implementation has been solved by our PhD Experts at My Uni Paper. Our Assignment Writing Experts are efficient in providing a fresh solution to this question. We are serving more than 10000+ Students in Australia, the UK, and the US by helping them to score HD in their academics. Our Experts are well-trained to follow all marking rubrics and referencing styles.

Be it a used or new solution, the quality of the work submitted by our assignment experts remains unhampered. You may continue to expect the same or even better quality with the used and new assignment solution files respectively. There’s one thing to be noticed you could choose one between the two and acquire an HD either way. You could choose a new assignment solution file to get yourself an exclusive, plagiarism (with free Turnitin file), expert quality assignment or order an old solution file that was considered worthy of the highest distinction.