微信客服：xiaoxionga100
微信客服：ITCS521
sql代写-MIS602-Assignment 2
时间：2022-04-07
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 Due by 11:55pm AEST/AEDT Sunday end of Module 4.2
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.
Provide SQL statements and the query output for the following:
No Question Marks
1 Find all the customers whose surname or given name contains the string ‘IND’. 2 Marks
MIS602 Assignment 2 Page 2 of 4
2 Find the total number of female staffs currently working in the company?
3 Marks
3 List all the staff who have resigned after 2018 who were living in any part of CARLTON.
3 Marks
4 List all the staff who gets a pay rate below average pay rate?
3 Marks
5 Find the supervisor name of the youngest staff.
3 Marks
6 List the most popular plan. If there are more plans (ties), you should display both.
3 Marks
7 List the total number of plans sold by each staff including their name.
5 Marks
8 List the customer id, name, phone number and the details of the staff who sold the plan
to the customer?
5 Marks
9 List the all the staff (staffid and name) who are active not having any supervisor
assigned.
5 Marks
10 How many calls were made in total during the weekends of 2019?
5 Marks
11 The company is considering giving a 10% increase in payrate to all the staff joined before
2012.
(a) How many staff will be affected? Show your SQL query to justify your answer.
(b) What SQL will be needed to update the database to reflect the pay rate increase?
5 Marks
12 Which tower (Towerid, Location) was used by the customer 20006 to make his/her first
call.
5 Marks
13 List all the unique customers (CustomerId, name) having a same colour phone as
CustomerId 20008.
5 Marks
14 List the CustomerID, Customer name, phone number and the total duration customer
was on the phone during the month of August, 2019 from each phone number the
customer owns. Order the list from highest to the lowest duration.
5 Marks
15 i. Create a view that shows the popularity of the plans based on number of plans sold.
ii. Use this view in a query to determine the most popular plan.
5 Marks
16 List all the plans and total number of active phones on each plan.
3 Marks
17 Write an SQL query to join all the seven tables presented in this database taking at least
two columns from each table.
5 Marks
18 List the details of the youngest customer (CustomerId, name, dob, postcode) in postcode
3030 along with total time spent on calls in minutes. Assume call durations are in
seconds.
5 Marks
MIS602 Assignment 2 Page 3 of 4
19 After evaluating all the tables, explain with reasons whether any of the tables violate the
conditions of 3rd Normal Form.
3 Marks
20 In not more 200 words, explain at least two ways to improve this database based on
what we have learned in 1st - 8th Week.
5 Marks
Total 85
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. 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.
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.
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 etc
Queries 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.
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 Due by 11:55pm AEST/AEDT Sunday end of Module 4.2
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.
Provide SQL statements and the query output for the following:
No Question Marks
1 Find all the customers whose surname or given name contains the string ‘IND’. 2 Marks
MIS602 Assignment 2 Page 2 of 4
2 Find the total number of female staffs currently working in the company?
3 Marks
3 List all the staff who have resigned after 2018 who were living in any part of CARLTON.
3 Marks
4 List all the staff who gets a pay rate below average pay rate?
3 Marks
5 Find the supervisor name of the youngest staff.
3 Marks
6 List the most popular plan. If there are more plans (ties), you should display both.
3 Marks
7 List the total number of plans sold by each staff including their name.
5 Marks
8 List the customer id, name, phone number and the details of the staff who sold the plan
to the customer?
5 Marks
9 List the all the staff (staffid and name) who are active not having any supervisor
assigned.
5 Marks
10 How many calls were made in total during the weekends of 2019?
5 Marks
11 The company is considering giving a 10% increase in payrate to all the staff joined before
2012.
(a) How many staff will be affected? Show your SQL query to justify your answer.
(b) What SQL will be needed to update the database to reflect the pay rate increase?
5 Marks
12 Which tower (Towerid, Location) was used by the customer 20006 to make his/her first
call.
5 Marks
13 List all the unique customers (CustomerId, name) having a same colour phone as
CustomerId 20008.
5 Marks
14 List the CustomerID, Customer name, phone number and the total duration customer
was on the phone during the month of August, 2019 from each phone number the
customer owns. Order the list from highest to the lowest duration.
5 Marks
15 i. Create a view that shows the popularity of the plans based on number of plans sold.
ii. Use this view in a query to determine the most popular plan.
5 Marks
16 List all the plans and total number of active phones on each plan.
3 Marks
17 Write an SQL query to join all the seven tables presented in this database taking at least
two columns from each table.
5 Marks
18 List the details of the youngest customer (CustomerId, name, dob, postcode) in postcode
3030 along with total time spent on calls in minutes. Assume call durations are in
seconds.
5 Marks
MIS602 Assignment 2 Page 3 of 4
19 After evaluating all the tables, explain with reasons whether any of the tables violate the
conditions of 3rd Normal Form.
3 Marks
20 In not more 200 words, explain at least two ways to improve this database based on
what we have learned in 1st - 8th Week.
5 Marks
Total 85
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. 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.
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.
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 etc
Queries 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.
