MIS602_Assessment _2_Brief_Database Implementation_Module 4.2 Page 1 of 3Task SummaryIn this assessment, you are required to demonstrate your ability to understand the requirements for variousdata information requests from an existing database and develop appropriate SQL statements to satisfy thoserequirements.ContextBeing able to query a database is a fundamental skill that is required by all information systems professionalswho … Continue reading “MIS602 Data Modelling and Database Design | My Assignment Tutor”
MIS602_Assessment _2_Brief_Database Implementation_Module 4.2 Page 1 of 3Task SummaryIn this assessment, you are required to demonstrate your ability to understand the requirements for variousdata information requests from an existing database and develop appropriate SQL statements to satisfy thoserequirements.ContextBeing able to query a database is a fundamental skill that is required by all information systems professionalswho work with relational databases. In this assessment, you will utilize the basic query skills that are typicallyused to extract information for analysis, reporting and data cleansing in a data management setting. Timelyprovisioning of key business information promotes effective communication and enhanced solution delivery.Task Instructions1. Please read the attached MIS602_Assessment 2_Data Implementation_ Case study.2. Examine the relational model and ER diagram and derive the SQL queries to return the requiredinformation.3. Provide SQL statements and the query output for the following:1. List all the customers who live in any part of CAULFIELD. List only the Customer ID, fullname, date of birth and suburb (5 marks)2. List all of the active staff. Show their Staff ID, full name and weekly salary assuming that theywork a 38 hour week (3 marks)3. Which plan has the most expensive contract to break? (5 marks)4. Which brands of mobile phone does this company sell? List only the unique brand names (3marks)5. Which customer is not able to purchase a phone? Use a query to explain why. Hint: Reviewthe customer data (3 marks)6. How many of each phone plan have been sold? (5 marks)7. What is the average age of an Apple phone user? (8 marks)8. What are the first and most recent mobile phone purchases? (3 marks)9. i. For calls made in 2018 how many calls were made on the weekend? (5 marks)ii. For calls made in 2018 how many calls were made on each day of the weekend? (3 marks) ASSESSMENT 2 BRIEFSubject Code and TitleMIS602 Data Modelling and Database DesignAssessmentDatabase ImplementationIndividual/GroupIndividualLengthN/ALearning OutcomesThe Subject Learning Outcomes demonstrated by successful completion ofthe task below include:b) Design solutions applying relational database techniques tocomplex problems and communicate these solutions to allstakeholders.SubmissionDue by 11:55pm AEST/AEDT Sunday end of Module 4.2Weighting35%Total Marks100 Marks MIS602_Assessment _2_Brief_Database Implementation_Module 4.2 Page 2 of 310. Provide a listing of the utilization of each tower and its location i.e. how busy each tower isbased on the number of connections. Put the busiest tower at the top of the list (3 marks)11. Did any users on the ‘Large’ plan exceed their monthly allowance during August 2018? (8marks)12. The company is upgrading all their 3G towers from to 5G.i. How many towers will be upgraded? (1 mark)ii. what SQL will be needed to update the database to reflect the upgrades? (2 marks)13. i. List the full name, join date, resigned date of each staff member and name, join date andresigned date of their manager (3 marks)ii. What do you observe with the data? (2 marks)14. How much revenue was generated in 2017 by each plan from call charges. Format the outputas currency i.e. $123.45 (8 marks)15. List the customers who made phone calls longer than 200 minutes (5 marks)16. Which customers have more than one mobile phone? List the customer name, suburb andstate. Order by the customer name (8 marks)17. Are there any mobile phone plans that are currently unused? This can be obtained a numberof ways. Demonstrate this by using the following two query typesi. Nested subquery (3 marks)ii. Outer join (3 marks)18. List the oldest and the youngest customers in the postcodes 3000 and 3102. Show thecustomer full name age and suburb details (3 marks)19. i. Create a view that shows the popularity of each phone colour (1 mark)ii. Use this view in a query to determine the least popular phone colour (2 marks)20. The billing team is getting returned mail because of bad customer addresses. This is causing aloss in revenue.i. Review the customer data and find at least 3 issues (3 marks)ii. Provide the SQL statements to correct the data problems (2 marks)Total 100 marks4. The database used for this assignment is available in ‘MIS602_Assessment 2_Database SQL Files’folder. Please download the files from this folder.5. All statements and first 10 lines of output need to be neatly presented. Please save all your SQLstatements and output into a single word document and submit it via the Assessment link in the mainnavigation menu.ReferencingIt is essential that you use appropriate APA style for citing and referencing research. Please see moreinformation on referencing here http://library.laureate.net.au/research_skills/referencingSubmission InstructionsSubmit Assessment 2 via the Assessment link in the main navigation menu in MIS602 Database Modelling andDatabase Design. The Learning Facilitator will provide feedback via the Grade Centre in the LMS portal.Feedback can be viewed in My Grades.Academic Integrity DeclarationI declare that except where I have referenced, the work I am submitting for this assessment task is my ownwork. I have read and am aware of Torrens University Australia Academic Integrity Policy and Procedureviewable online at http://www.torrens.edu.au/policies-and-formsI am aware that I need to keep a copy of all submitted material and their drafts, and I will do so accordingly.MIS602_Assessment 2_Brief_Data Implementation_ Module 4.1 Page 3 of 3Assessment Rubric Assessment CriteriaFail(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 requiredinformation Demonstration of additionalAQL knowledge100%Queries are incorrectlyanswered with marksonly sufficient to attaina F(ail).Queries correctlyanswered with sufficientmarks to attain a (P)assQueries correctlyanswered withsufficient marks toattain a (C)redit.Display only requiredinformation.Queries correctlyanswered with sufficientmarks to attain a(D)istinction.Display only requiredinformationDisplay additional SQLknowledge such as columnformatting, table alias,additional calculations etcQueries correctlyanswered with sufficientmarks to attain a (HD)High Distinction.Display only requiredinformation.Display additional SQLknowledge. Wellformatted queries.Results returned in ameaningful order.
