Data Modelling and Database Design - Australia Assignments

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 the 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:55 pm AEST/AEDT Sunday end of Module 4.2 (week 8) 6 Week Cycle: Due by 11:55 pm 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 the attached MIS602_Assessment 2_Data Implementation_ Case study carefully and
then derive the SQL queries to return the required information. Your focus should be on providing the output
as meaningful and presentable as possible. Please note that extra marks will be awarded for the presentation
and readability of SQL queries, including the ordering of the columns.
Please note that 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	Display all the customers having full names longer than 10 characters.	3 Marks
2	List all the names of the customers if their names contain the string ‘ON’, ignoring the case.	3 Marks
3	Which plan has the highest number of cancelled phone numbers?	3 Marks
4	How many customers have more than one active phone number?	3 Marks
5	Generate a report displaying the total number of customers by each state.	4 Marks
6	Is there any staff who is also a customer? Write an SQL query to support your answer.	4 Marks
7	Display the supervisor’s details and the total number of staff they supervise.	4 Marks
8	List all the staff who are not supervisors.	4 Marks
9	Did the total number of calls increase during the COVID lockdown period (2020) compared to 2019? Substantiate your answer with an SQL query.	4 Marks
10	What is the second least popular phone colour among customers?	4 Marks
11	List all the customers who are not active. A customer should be considered not active if they have no active phone number registered under their name.	4 Marks
12	What plan is most popular among customers who are born before the 1960s?	5 Marks
13	Write a query to display the total income generated by the company in 2019 from call charges based on gender. Assume call duration is recorded in seconds and calls are charged per minute.	5 Marks
14	Is there any mobile plan in the plan table that has never been used? Show this using i. Sub-query ii. Joins	5 Marks
15	Which tower(s) were used by customer 20010 to make the first call?	5 Marks
16	Did any customer break their mobile plan before the mobile plan duration ends? Assume the plan duration is in months. Justify your answer with an SQL query by producing a list of customers who cancelled the plan before the duration expires.	5 Marks
17	i. Create a view showing the popularity of phone colours based on the total number of active users. ii. Use this view to show the least popular colour.	5 Marks

MIS602 Assignment 2 Page 3 of 4

Write a query to fetch the data about the first call, including the customer details, the phone
number the call was made and the total number of towers used for the connection. The
expected output of the query is shown below.

customerId	given	phoneNumber	calldate	totTowersUsed
21862	GLEN	413621268	2016-09-24	3

5 Marks

Who is the youngest and oldest customer of postcode 3181, having an iPhone?

5 Marks

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
answer.

5 Marks

Total

85 Marks

Submission Instructions
1. The database used for this assignment is available in the ‘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 the 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.