1
ISYS2038 Database Design and Development
Semester 1, 2020
Team Assignment 2 – Specifications

Due Date: Monday 1 June, 11.59pm
Mark: 20% of the total assessment of this course

1) Background
In this assignment you are required to demonstrate and apply the concepts covered in Week
6 – Week 11. There are three different parts in this assignment and it is aim to get students to
demonstrate and apply the knowledge of advanced SQL, database administration approaches
and data analytics for the given questions in relation to the case study. Following the previous
case study and assignment you and your team members, employed by Best Innovative
Solution (BIS) Pty Ltd, continue to investigate and provide solution to existing client –
Doncaster Tai Chi (DTC) Pty. Ltd.

The dataset about DTC has been collected and you were given the dataset on Canvas for the
purpose of applying data analytics. Further information about the dataset is listed below:
• The dataset and header description are stored in two separate files.
• The dataset consists of binary, categorical and numerical data.
• There are 6 different attributes and 2000 cases (or instances).
• There are some missing values in the dataset.
• The dataset covers mainly on the aspect of program related data for corporate
organisations.

Note: All members of an assignment group equally share the assignment mark unless an
arrangement is made between the members about an alternative distribution of marks based
on member contributions. Any group issue(s) must be resolved within the group and
communicated to your Lecturer prior to the submission of the assignment.

2) Required Deliverables
As a contracted team, you need to produce and discuss the following deliverables/ questions
based on the case study.

In the report:
• Advanced SQL scripts of 5 queries
For each query,
o Explain and justify its business purpose
o Provide the SQL script with comments included
o Explain the design of SQL statement
o Provide screenshot of the result/ outcome of query

• Database Administration Approaches
Discuss the following questions:
o Data may need to be protected for both privacy and integrity reasons. What
data needs to be protected and why? Provide two examples to support the
discussion.
o As the company grows in size over time, what data backup challenges that
the database administrator may encounter? What would be your
recommendations? Provide two examples to support the discussion.
2

• Data Analytics
Answer the following questions:
o Using your knowledge of regression and data exploration, discuss how you
would produce a model to predict the likely operating cost for DTC. In your
explanations, include the screenshots of using Orange to produce the
prediction model.
o As there are some missing values in the dataset, discuss what you would do
with these missing values.

• Meeting Minutes
o Provide both meeting minutes and responsibility matrix using appropriate
templates
o Include under Appendix

In the MySQL Server:
• Data Manipulations
Create 5 Advance SQL statements that have the following requirements. Note that
each of the statements can cover one or more of the requirements.
o One of the 5 statements need to be creation of Trigger
o One of the 5 statements need to be creation of Procedure
o One of the 5 statements need to be creation of Function
o One of the 5 statements need to be creation of View
o One of the 5 statements need to be creation of Nested Query (could be
either standard or correlated sub-query)
o Three out of the 5 statements need to make use of joining tables
o Three of the 5 statements need to have a calculation
Make sure that you save your SQL scripts into .sql file extension or store them in a
word or notepad file. You need to provide these scripts and the screen shots of
results in the report. For trigger and procedure, you need to provide test data (both
valid and invalid data) to show that your code is fully tested.
In Orange:
• You need to download two files – the dataset (DTC-CorporateProgramDataset.xlsx)
and the header description (DTC-CorporateProgramDataset-header-description.txt).
These datasets are available in Canvas | Assignments | Assessment Task 2 | Team
Assignment 2 – Dataset and Specifications.
• You need to produce a prediction model as requested in the question in the “Data
Analytics section”.
• You need to submit Orange files that could be loaded into Orange without error(s)
for testing your prediction model.
• You can zip your Orange files and the report (PDF version) into a single zip file and
submit it via the Canvas submission link.

3) Report
Word Limit: 2500 – 3000 words
Font Size: 11pt or 12pt
Font Style: Calibri or Times New Roman
Spacing: Single or 1.5 Spacing

3
Submission Format:
For each team, the following documents must be submitted via Canvas:
• a single copy of final report (PDF version)
• Assignment Cover Sheet
• a Turnitin generated report (PDF version)

You can go to Assignments | Assessment Task 2 | Assessment Task 2B – Team Assignment 2
and click on Submit Assignment.

Use of Turnitin:
Please note that you need to use Turnitin to self-check your report for compliance of
academic integrity and plagiarism detection. Report that is not checked by Turnitin will not
be marked.

Turnitin is not the submission link of your final report (PDF version). After you have self-
checked your report, you need to submit the final version of your report together with its
Turnitin report via the designated submission link.

To self-check your report, you can go to Assignments | Important – Other Assignment
Related Info. | Turnitin - Self-Check Your Report *Not Final Submission* and click on Load
Turnitin - Self-Check Your Report *Not Final Submission* in a new window.

Some notes and guidelines:
• Report needs to include and discuss the required deliverables, as mentioned
previously in section 2.
• Report needs to include print screens, explanations and justifications of the SQL
statements created.
• The report covers a wider audience, including management and business users as
well as developers.
• Use headings for each section and subsection (i.e. 1.0 Advanced SQL Statements,
1.1. Trigger, 1.2. Procedure, 2.0 Database Administration Approaches, 2.1 Data
Protection, 2.2. Data Backup, 3.0 Data Analytics, etc.).
• Label the figures and tables in the report properly.
• Use Appendix as you see fit.

4) Marking Rubric:
Note: Marking rubric shows the mark out of 100 and the total mark will then be converted
to 20%.

Assessed
Components
Exceptional

Very Good Acceptable Needs
Improvement
Poor
SQL Scripts
(30 points)
SQL scripts
are
sophisticated
ly and
logically well
structured,
use
appropriate
syntax,
contain no
SQL scripts are
logically well
structured,
use correct
syntax but
contain very
little errors
and are able
to address
most/ all
SQL scripts
reasonably use
some
appropriate
syntax but
contain
reasonable
amount of
errors and are
able to
SQL scripts
somehow use
appropriate
syntax but full
of errors and
are able to
address
limited
business
purpose.
SQL scripts do
not use
appropriate
syntax or
overly
simplistic and
are unable to
address any/
very limited
4
error and are
able to
address all
business
purpose.
Discussions/
justifications
are critical,
well
articulated
and rigorous.
(24.00 –
30.00 points)
business
purpose.
Discussions/
justifications
are sound.
(21.00 – 23.99
points)
address some
business
purpose.
Discussions/
justifications
are sufficient.
(18.00 – 20.99
points)
Discussions/
justifications
are limited or
insufficient.
(15.00 – 17.99
points)
business
purpose.
Discussions/
justifications
are missing or
very limited.
(0.00 – 14.99
points)
Database
Administration
Approaches
(30 points)
Convincing,
critical, well
articulated
and rigorous
discussions/
justifications
for the
required
deliverables.
(24.00 –
30.00 points)
Sound
discussions/
justifications
for the
required
deliverables.
(21.00 – 23.99
points)
Satisfactory
discussions/
justifications
for the
required
deliverables.
(18.00 – 20.99
points)
Limited or
insufficient
discussions/
justifications
for the
required
deliverables.
(15.00 – 17.99
points)
No or very
limited
discussions/
justifications
for the
required
deliverables.
(0.00 – 14.99
points)
Data Analytics
(30 points)
Excellent
model
chosen to
produce
accurate
prediction.
Discussions/
justifications
are critical,
well
articulated
and rigorous.
(24.00 –
30.00 points)
Good model
chosen to
produce
accurate
prediction.
Discussions/
justifications
are sound.
(21.00 – 23.99
points)
Appropriate
model chosen
to produce
satisfactory
prediction.
Discussions/
justifications
are sufficient.
(18.00 – 20.99
points)
Appropriate
model chosen
to produce
sensible
prediction.
Discussions/
justifications
are limited or
insufficient.
(15.00 – 17.99
points)
Inappropriate
model chosen
and unable to
produce
sensible
prediction.
Discussions/
justifications
are missing or
very limited.
(0.00 – 14.99
points)
Overall
Presentation of
Report
(5 points)
Quality of
report is
truly
professional,
clear and
easy to
follow, has
good
structure,
cohesive and
well thought
with the
Quality of
report is
professional,
clear and easy
to follow and
has good
structure.
(3.50 – 3.99
points)
Quality of
report is
professional,
clear and easy
to follow but it
contains some
minor spelling
or
grammatical
errors.
(3.00 – 3.49
points)
Quality of
report is
somewhat
professional,
but still
difficult to
follow and/or
it contains
some spelling
or
grammatical
errors.
Quality of
report is
unprofessional
, difficult to
follow and/or
it contains
numerous
spelling or
grammatical
errors.
(0.00 – 2.49
points)
5
reader(s) in
mind.
(4.00 – 5.00
points)
(2.50 – 2.99
points)
Meeting
Minutes
(5 points)
Quality of
meeting
minutes are
truly
professional,
clear and
easy to
follow, has
good
structure
and well
thought with
the reader(s)
in mind.
Outstanding
and rigorous
demonstrati
on of team
work.
(4.00 – 5.00
points)
Quality of
meeting
minutes are
professional,
clear and easy
to follow and
has good
structure.
Sound
demonstration
of team work.
(3.50 – 3.99
points)
Quality of
meeting
minutes are
professional,
clear and easy
to follow but it
contains some
minor spelling
or
grammatical
errors.
Satisfactory
demonstration
of team work.
(3.00 – 3.49
points)
Quality of
meeting
minutes are
somewhat
professional,
but still
difficult to
follow and/or
it contains
some spelling
or
grammatical
errors. Limited
or insufficient
demonstration
of team work.
(2.50 – 2.99
points)
Quality of
meeting
minutes are
unprofessional
, difficult to
follow and/or
it contains
numerous
spelling or
grammatical
errors. No or
very limited
demonstration
of team work.
(0.00 – 2.49
points)
Overall High
Distinction
(HD)
Distinction (D) Credit (C) Pass (P) Fail (N)
80 or more 70 or more 60 or more 50 or more 0 or more