MIS602 Assignment 2 XXXXXXXXXXPage 1 of 4 ASSESSMENT 2 BRIEF Subject Code and Title MIS602 Data Modelling and Database Design Assessment Database Implementation Individual/Group Individual Length N/A...

high


MIS602 Assignment 2 Page 1 of 4 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 successful completion of the task below include: b) Design solutions applying relational database techniques to complex problems and communicate these solutions to all stakeholders. Submission 12 Week Cycle: Due by 11:55pm AEST/AEDT Sunday end of Module 4.2 (week 8) 6 Week Cycle: Due by 11:55pm AEST/AEDT Sunday end of Module 4.2 (week 4) Weighting 35% Total Marks 100 Marks 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. MIS602 Assignment 2 Page 2 of 4 Provide SQL statements and the query output for the following: No Question Marks 1 List the total number of connections made. 2 Marks 2 List all the staff whose address contains the character string ‘st’ ignoring the case. 3 Marks 3 List all the customers who are not active. 3 Marks 4 Which plan gives the second biggest data allowance? 3 Marks 5 List the staff who gets more than the average hourly rate. 3 Marks 6 List the top two selling plans. 3 Marks 7 List all the customers on “Extra small” plan. 5 Marks 8 From which towerId was the most recent call made? 5 Marks 9 List all the customers who owned a phone of the same colour as the customer ‘Rajoo’ (i.e. surname). 5 Marks 10 List all the customers (customer Id and name) having more than one mobile number. 5 Marks 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? 5 Marks 12 List the names of all the staff who have a supervisor, along with their supervisor’s name. 5 Marks 13 Who is the longest serving staff? Display the StaffId, full name, date joined and the total years served. 3 Marks 14 List all plans that have never been used by any customer. Show the query using: i. Nested Query ii. SQL Join 5 Marks 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. 5 Marks 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. 5 Marks 17 List the total number of connections made on each tower location after 2018. 3 Marks MIS602 Assignment 2 Page 3 of 4 18 List all the details of the most recently joined and oldest joined Samsung phone user. 5 Marks 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 5 Marks 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. 7 Marks Total 85 Marks Submission Instructions 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. Please see more information on referencing here http://library.laureate.net.au/research_skills/referencing Submission Instructions Submit Assessment 2 via the Assessment link in the main navigation menu in MIS602 Database Modelling and Database Design. The Learning Facilitator will provide feedback via the Grade Centre in the LMS portal. Feedback can be viewed in My Grades. Academic Integrity Declaration I declare that except where I have referenced, the work I am submitting for this assessment task is my own work. I have read and am aware of Torrens University Australia Academic Integrity Policy and Procedure viewable online at http://www.torrens.edu.au/policies-and-forms I am aware that I need to keep a copy of all submitted material and their drafts, and I will do so accordingly. http://library.laureate.net.au/research_skills/referencing http://library.laureate.net.au/research_skills/referencing http://www.torrens.edu.au/policies-and-forms http://www.torrens.edu.au/policies-and-forms http://www.torrens.edu.au/policies-and-forms MIS602 Assignment 2 Page 4 of 4 Assessment Rubric Assessment Criteria Fail (Unacceptable) 0- 49% Pass (Functional) 50-64% Credit (Proficient) 65- 74% Distinction (Advanced) 75 -84% High Distinction (Exceptional) 85-100% ➢ Correct and complete queries ➢ Display only required information ➢ Demonstration of additional AQL knowledge 100% Queries are incorrectly answered with marks only sufficient to attain a F(ail). Queries correctly answered with sufficient marks to attain a (P)ass Queries correctly answered with sufficient marks to attain a (C)redit. Display only required information