For faster services, inquiry about  new assignments submission or  follow ups on your assignments please text us/call us on +1 (251) 265-5102

WhatsApp Widget

MIS602 Data Modelling and Database Design

ASSESSMENT 2 BRIEFSubject Code and TitleMIS602 Data Modelling and Database DesignAssessmentDatabase ImplementationIndividual/GroupIndividualLengthN/ALearning OutcomesThe 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.Submission12 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)Weighting35%Total Marks100 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 including ordering of the columns

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:

NoQuestionMarks1What is the total number of active phone numbers currently in the database?3 Marks2Display a list of customers living in north or south Clayton.3 Marks3List all the unique phone colours owned by the users.3 Marks4List all the customers who are iPhone users.3 Marks5How many employees have resigned company?3 Marks6Display all the staff, and their supervisor details if they have one.4 Marks7List all the staff who are supervisors.4 Marks8On average, how many calls were made in each month of 2019. Order by month in ascending order.4 Marks9What is the second highest pay rate for a staff member?4 Marks10List all the customers who are IPhone users.4 Marks11List all the customers who have more than one phone number registered.4 Marks12Display the average age of users based on mobile plan.5 Marks13Assume in 2019, the company decided to apply a 20 cent call charge for every call made, as a fixed charge regardless of the call duration, and 0.10 cents for each minute spent on call. Assuming the call durations are in seconds, How much income has the company generated through this fixed charge in dollars from 2019 onwards?   What is the total income is generated in total for both call and fixed charges in this period.5 Marks14List all the customers, and the staff who processed their mobile registrations.5 Marks15List the details of the staff who served the highest number of customers.5 Marks16Evaluate the ‘calledNumber’ column in calls, and write a query to find how many of these numbers are registered in this database (mobile network).5 Marks

17List all the active phone numbers that has never made a call. Show this query using: Nested QuerySQL Join5 Marks18Create a view showing the popularity of all towers based on number of connections madeUse this view to generate the most popular tower location5 Marks19Write a query to fetch the data about the first call. Include the phone number, towerID and the customer details (customerID and name) on which the number was registered. Note: you may get multiple records.5 Marks20How long has the oldest and youngest active female staff worked in the company?5 Marks21In 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 argument.6 MarksTotal 90 Marks

Submission Instructions

The database used for this assignment is available in ‘MIS602_Assessment 2_Database SQL Files’

folder. You need to download and import the file into MySQL Workbench.

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.

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

I am aware that I need to keep a copy of all submitted material and their drafts, and I will do so accordingly.

Assessment 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 queriesDisplay only required informationDemonstration 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)assQueries correctly answered with sufficient marks to attain a (C)redit. Display only required information.Queries correctly answered with sufficient marks to attain a (D)istinction. Display only required information Display additional SQL knowledge such as column formatting, table alias, additional calculations etcQueries correctly answered with sufficient marks to attain a (HD) High Distinction. Display only required information. Display additional SQL knowledge. Well formatted queries. Results returned in a meaningful order.

Get expert help for Data Modelling and Database Design and many more. 24X7 help, plag free solution. Order online now!

The post MIS602 Data Modelling and Database Design appeared first on Universal Assignment.

GET THE WORK DONE AT ESSAYLINK.NET

WhatsApp
Hello! Need help with your assignments?

For faster services, inquiry about  new assignments submission or  follow ups on your assignments please text us/call us on +1 (251) 265-5102

We accept Cash App, Zelle, Apple Pay, Google Pay, and Stripe. Contact support for more info!Submit Your Questions to Writers for FREE!!

X
GET YOUR PAPER DONE