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

Due Date: Wednesday 17 June, 11.59pm
Mark: 50% of the total assessment of this course

1) Background
In this assignment you are required to demonstrate and apply the concepts and knowledge
covered in Week 1 – Week 11. For the purposes of the report, assume that you are employed
as a business analyst at Best Innovative Solution (BIS) Pty Ltd. You are assigned to investigate
the questions from a new client and provide the solution.

The dataset about Just Decorate Pty. Ltd. has been collected and you are given the following
two files for the purpose of applying data analytics:
• dataset (JDCustomers.xlsx)
• header description (JDCustomersDataset-header-description.txt)
These two files are available for download on Canvas | Assignments | Assessment Task 3:
Individual Final Assessment | Individual Assignment – Case Study, Specifications and Dataset
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 1000 cases (or instances).
• There are some missing values in the dataset.
• The dataset covers mainly on the aspect of customer related data.

2) Questions from new client
You need to provide answers to all the following questions based on the case study.

Question 1 Data Modelling
• Provide an Entity Relationship Diagram (ERD). For each entity, provide at least one
attributes/ fields with a maximum of two attributes (excluding primary key).
• Provide a Relational Model and show the most appropriate attributes/ fields,
primary keys and foreign keys. In addition, include the data type for each attribute/
field listed in each table/ entity.
• You should apply third normal form (3NF) and show consistency in the use of
notation.
• If you discover while drawing the diagrams that the narrative of case study is
incomplete, then provide justifications, assumptions, or reasonable explanations to
complete the case study along with your diagrams. You could include the
justifications, assumptions or reasonable explanations under Appendix section.

Question 2 SQL Scripts
• Based on data modelling from Question 1, create 3 queries that have the following
requirements. Note that each of the query can cover one or more of the
requirements.
o One of the 3 queries should have a calculation
o One of the 3 queries should make use of Group By
2
o One of the 3 queries should include a scalar function (one that returns a
value)
o One of the 3 queries should demonstrate Nested Query (could be either
standard or correlated sub-query)
o One of the 3 queries should demonstrate joining tables
o One of the 3 queries should demonstrate the creation and testing of Trigger
with both valid and invalid test data
• For each query,
o Explain and justify its business purpose
o Provide the SQL script
o Explain the design of SQL script
o Make use of table(s) with sample data to show potential result/ outcome of
query

Question 3 Data Analytics with Orange
After you have dealt with the missing values, discuss how you would produce a model to
predict the spending score of customers for Just Decorate Pty. Ltd. using the knowledge of
data visualisation and regression. In your explanations, include the screenshots of using
Orange to produce the visualisation and prediction model. In addition, explain how your
results from data visualisation and regression could help Just Decorate Pty. Ltd. to expand its
business.

Question 4 Database Administration Approaches
Recommend two security features of database management system that Just Decorate Pty.
Ltd. can use to protect the data of the company. Justify each of the recommended security
feature.

Question 5 Big Data and Analytics
Suggest two varieties of data sources that Just Decorate Pty. Ltd. can use to further
improve the understanding of its business. Provide justifications for each of the suggested
data source.

3) Report
Word Limit: 1500 – 2000 words (+/- 10%), excluding print screens, figures, diagrams, tables,
appendix and references.
Font Size: 11pt or 12pt
Font Style: Calibri or Times New Roman
Spacing: Single or 1.5 Spacing

Some notes and guidelines (in addition to Lecture 12 slides):
• Report needs to include and discuss all the questions given in Part 2) Questions from
new client.
• You do not need to access MySQL server to work on Question 2 SQL Scripts. You can
make use of tables with sample data (or examples of records) to support your
explanations. The tables could be created by typing in MS Word or MS Excel.
• You need to use diagramming tool(s) to produce your diagrams for Question 1 Data
Modelling. Do not submit diagrams that are drawn using pens/ pencils and papers.
• Organise the report with sections and subsections.
• Report needs to include justifications and explanations of the SQL queries.
• Report covers a wider audience, including management and business users as well
as developers.
3
• Label the print screens, figures, diagrams, and tables in the report properly.
• Include References and Appendix sections in your report as you see fit.
• Use Harvard referencing style if you are citing references from web resources to
support your discussions.

Submission Format:
For each individual, the following documents must be submitted via Canvas. You can zip the
following files and submit as a zip file.
• a single copy of final report (PDF version)
• Assignment Cover Sheet
• a Turnitin generated report (PDF version)
• Orange files (Orange loadable dataset(s) in .xlsx, Orange workflow(s) in .ows, Excel
calculations in .xlsx)

You can go to Assignments | Assessment Task 3: Individual Final Assessment | Assessment
Task 3 – Individual Assignment 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 (or receipt/ evidence of submission to Turnitin if it takes too long to generate
the 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.

4) Marking Rubric:

Note: Marking rubric shows the mark out of 100 points and the total mark will then be
converted to 50%.

Assessed
Components
Exceptional

Very Good Acceptable Needs
Improvement
Poor
Data
Modelling
with ERD and
Relational
Model
(30 points)
Outstanding
demonstration
of using
appropriate
data modelling
techniques.
Diagrams
produced are
outstanding,
elegant and
have very few
or no
mistakes.
Sound
demonstration
of using
appropriate
data modelling
techniques.
Diagrams
produced are
sound and
have few
mistakes.
(21.00 – 23.99
points)
Satisfactory
demonstration
of using
appropriate
data modelling
techniques.
Diagrams are
produced at
satisfactory
level but still
have some
mistakes.
Limited
demonstration
of using
appropriate
data modelling
techniques.
Diagrams are
somewhat
complete but
still have a lot
of mistakes.
(15.00 – 17.99
points)
No
demonstration
or very little
demonstration
of using
appropriate
data modelling
techniques.
Diagrams are
incomplete
and have a lot
of mistakes.
4
(24.00 – 30.00
points)
(18.00 – 20.99
points)
(0.00 – 14.99
points)
SQL Scripts
(15 points)
SQL scripts are
sophisticatedly
and logically
well
structured,
use
appropriate
syntax,
contain no
error and are
able to
address all
business
purpose.
Discussions/
justifications
are critical,
well
articulated
and rigorous.
(12.00 – 15.00
points)
SQL scripts are
logically well
structured,
use correct
syntax but
contain very
little errors
and are able
to address
most/ all
business
purpose.
Discussions/
justifications
are sound.
(10.50 – 11.99
points)
SQL scripts
reasonably use
some
appropriate
syntax but
contain
reasonable
amount of
errors and are
able to
address some
business
purpose.
Discussions/
justifications
are sufficient.
(9.00 – 10.49
points)
SQL scripts
somehow use
appropriate
syntax but full
of errors and
are able to
address
limited
business
purpose.
Discussions/
justifications
are limited or
insufficient.
(7.50 – 8.99
points)
SQL scripts do
not use
appropriate
syntax or
overly
simplistic and
are unable to
address any/
very limited
business
purpose.
Discussions/
justifications
are missing or
very limited.
(0.00 – 7.49
points)
Data
Analytics with
Orange
(15 points)
Excellent
model chosen
to produce
accurate
prediction and
visualisation.
Discussions/
justifications
are insightful,
critical, well
articulated
and rigorous.
(12.00 – 15.00
points)
Good model
chosen to
produce
accurate
prediction and
visualisation.
Discussions/
justifications
are insightful
and sound.
(10.50 – 11.99
points)
Appropriate
model chosen
to produce
satisfactory
prediction and
visualisation.
Discussions/
justifications
are somewhat
insightful and
sufficient.
(9.00 – 10.49
points)
Appropriate
model chosen
to produce
sensible
prediction and
visualisation.
Discussions/
justifications
are limited or
insufficient
and barely
have insights.
(7.50 – 8.99
points)
Inappropriate
model chosen
and unable to
produce
sensible
prediction and
visualisation.
Discussions/
justifications
are missing or
very limited
and have no
insights.
(0.00 – 7.49
points)
Database
Administratio
n Approaches
(20 points)
Convincing,
critical, well
articulated
and rigorous
discussions/
justifications
for the
required
recommendati
ons.
Sound
discussions/
justifications
for the
required
recommendati
ons.
(14.00 – 15.99
points)
Satisfactory
discussions/
justifications
for the
required
recommendati
ons.
(12.00 – 13.99
points)
Limited or
insufficient
discussions/
justifications
for the
required
recommendati
ons.
(10.00 – 11.99
points)
No or very
limited
discussions/
justifications
for the
required
recommendati
ons.
(0.00 – 9.99
points)
5
(16.00 – 20.00
points)
Big Data and
Analytics
(20 points)
Convincing,
critical, well
articulated
and rigorous
discussions/
justifications
for the
required
suggestions.
(16.00 – 20.00
points)
Sound
discussions/
justifications
for the
required
suggestions.
(14.00 – 15.99
points)
Satisfactory
discussions/
justifications
for the
required
suggestions.
(12.00 – 13.99
points)
Limited or
insufficient
discussions/
justifications
for the
required
suggestions.
(10.00 – 11.99
points)
No or very
limited
discussions/
justifications
for the
required
suggestions.
(0.00 – 9.99
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