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 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.
MIS602 Assignment 2 Page 2 of 4
Provide SQL statements and the query output for the following:
No Question Marks
1 What is the total number of active phone numbers currently in the database? 3 Marks
2 Display a list of customers living in north or south Clayton. 3 Marks
3 List all the unique phone colours owned by the users. 3 Marks
4 List all the customers who are iPhone users. 3 Marks
5 How many employees have resigned company? 3 Marks
6 Display all the staff, and their supervisor details if they have one. 4 Marks
7 List all the staff who are supervisors. 4 Marks
8 On average, how many calls were made in each month of 2019. Order by month in ascending
order.
4 Marks
9 What is the second highest pay rate for a staff member? 4 Marks
10 List all the customers who are IPhone users. 4 Marks
11 List all the customers who have more than one phone number registered. 4 Marks
12 Display the average age of users based on mobile plan. 5 Marks
13 Assume 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,
a) How much income has the company generated through this fixed charge in dollars
from 2019 onwards?
b) What is the total income is generated in total for both call and fixed charges in this
period.
5 Marks
14 List all the customers, and the staff who processed their mobile registrations. 5 Marks
15 List the details of the staff who served the highest number of customers. 5 Marks
16 Evaluate 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
MIS602 Assignment 2 Page 3 of 4
17 List all the active phone numbers that has never made a call. Show this query using:
a) Nested Query
b) SQL Join
5 Marks
18 i. Create a view showing the popularity of all towers based on number of connections
made
ii. Use this view to generate the most popular tower location
5 Marks
19 Write 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 Marks
20 How long has the oldest and youngest active female staff worked in the company? 5 Marks
21 In 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 Marks
Total 90 Marks
Submission Instructions
1. 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.
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.