Reference no: EM133028320
MIS602 Data Modelling and Database Design - Torrens University Australia
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.
Provide SQL statements and the query output for the following:
1 List the total number of customers in the customers table.
2 List all the customers who live in any part of CLAYTON. List only the Customer ID, full name, date of birth and suburb.
3 List all the staff who have resigned.
4 Which plan gives the biggest data allowance?
5 List the customers who do not own a phone.
6 List the top two selling plans.
7 What brand of phone(s) is owned by the youngest customer?
8 From which number was the oldest call (the first call) was made?
9 Which tower received the highest number of connections?
10 List all the customerIDs of all customers having more than one mobile number.
Note: Only CustomerId should be displayed.
11 The company is considering changing the plan durations with 24 and 36 days to 30 days.
(a) How many customer will be effected? Show SQL to justify your answer.
(b) What SQL will be needed to update the database to reflect the upgrades?
12 List the staffId, full name and supervisor name of each staff member.
13 List all the phone numbers which have never made any calls. Show the query using:
i. Nested Query
ii. SQL Join
14 List the customer ID, customer name, phone number and the total number of hours 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.
15 i. Create a view that shows the popularity of each phone colour.
ii. Use this view in a query to determine the least popular phone colour.
16 List all the plans and total number of active phones on each plan.
17 List all the details of the oldest and youngest customer in postcode 3030.
18 Produce a list of customers sharing the same birthday.
19 Find at least 3 issues with the data and suggest ways to avoid these issues.
20 In not more 200 words, explain at least two ways to improve this database based on what you have learned in weeks 1-8.
Document Compiling
1. The database used for this assignment is available in ‘MIS602_Assessment 2_Database SQL Files' folder. Please download the files from this folder.
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 appropriate APA style for citing and referencing research.
Attachment:- Assessment _Brief.rar