Reference no: EM133185021
MIS602 Data Modelling and Database Design
Assessment - Database Implementation
Learning Outcome 1: 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 carefully the attached MIS602_Assessment 2_Data Implementation_ Case study and then derive the SQL queries to return the required information. Your focus should be providing the output as meaningful and presentable possible. Please note extra marks will be awarded for presentation and readability of SQL queries.
Please note 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:
1 List the total number of connections made.
2 List all the staff whose address contains the character string ‘st' ignoring the case.
3 List all the customers who are not active.
4 Which plan gives the second biggest data allowance?
5 List the staff who gets more than the average hourly rate.
6 List the top two selling plans.
7 List all the customers on "Extra small" plan.
8 From which towerId was the most recent call made?
9 List all the customers who owned a phone of the same colour as the customer ‘Rajoo' (i.e. surname).
10 List all the customers (customer Id and name) having more than one mobile number.
11 The company decided to re-name the budget3 plan to budget3Extra.
(a) How many customers will be affected? Show SQL to justify your answer.
(b) Write an SQL query which will update the database to reflect the upgrades?
12 List the names of all the staff who have a supervisor, along with their supervisor's name.
13 Who is the longest serving staff? Display the StaffId, full name, date joined and the total years served.
14 List all plans that have never been used by any customer. Show the query using:
i. Nested Query
ii. SQL Join
15 List the customer ID, customer name, phone number and the total number of hours (assume call duration is in seconds) the customer was on the phone during August of 2019 from each phone number the customer owns. Order the list from highest to the lowest number of hours.
16 i. Create a view that shows the popularity of each plan (i.e. how many plans are on each phone).
ii. Use this view in a query to determine the most popular plan.
17 List the total number of connections made on each tower location after 2018.
18 List all the details of the most recently joined and oldest joined Samsung phone user.
19 Produce a list of staff who joined on the same month. Order the result in the following format:
Month JoinedDate Name
July 2012-07-12 00:00:00 TERRENCE IANJAMIESON
July 2012-07-21 00:00:00 VINCENZOKNOL
March 2012-03-09 00:00:00 PATRICK MICHAELMARZELLA
March 2012-03-18 00:00:00 MICHAEL JOHNSCANLON
September 2012-09-23 00:00:00 CATHERINE MARYPORTELLI
September 2012-09-15 00:00:00 GLENDA JEANKHOR
20 In not more than 200 words, 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 argument
Attachment:- Database Implementation.rar