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
Learning Outcomes The Subject Learning Outcomes demonstrated by successful
completion of the task below include:
) 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 XXXXXXXXXXPage 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 XXXXXXXXXXPage 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 XXXXXXXXXX:00:00 TERRENCE IANJAMIESON
July XXXXXXXXXX:00:00 VINCENZOKNOL
March XXXXXXXXXX:00:00 PATRICK MICHAELMARZELLA
March XXXXXXXXXX:00:00 MICHAEL JOHNSCANLON
September XXXXXXXXXX:00:00 CATHERINE MARYPORTELLI
September XXXXXXXXXX: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
co
esponding 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:
li
ary.laureate.net.au
esearch_skills
eferencing
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 To
ens University Australia Academic Integrity Policy and Procedure
viewable online at http:
www.to
ens.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:
li
ary.laureate.net.au
esearch_skills
eferencing
http:
li
ary.laureate.net.au
esearch_skills
eferencing
http:
www.to
ens.edu.au/policies-and-forms
http:
www.to
ens.edu.au/policies-and-forms
http:
www.to
ens.edu.au/policies-and-forms
MIS602 Assignment 2 XXXXXXXXXXPage 4 of 4
Assessment Ru
ic
Assessment Criteria Fail
(Unacceptable) 0-
49%
Pass
(Functional)
50-64%
Credit
(Proficient) 65-
74%
Distinction
(Advanced)
75 -84%
High Distinction
(Exceptional)
85-100%
➢ Co
ect and complete
queries
➢ Display only required
information
➢ Demonstration of
additional AQL knowledge
100%
Queries are
inco
ectly answered
with marks only
sufficient to attain a
F(ail).
Queries co
ectly
answered with
sufficient marks to
attain a (P)ass
Queries co
ectly
answered with
sufficient marks to
attain a (C)redit.
Display only
equired
information