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