Trimester

Unit Code
Unit Title
Assessment
Author

Assessment Type
Assessment Title
Purpose of the
assessment (with
ULO Mapping)

Weight
Total Marks

Assessment Details and Submission Guidelines
T1 2020
MN405
Data and Information Management
Deepani Guruge
Assignment 1 (Individual)
Data Modelling and Data Managing
The purpose of this assignment is to develop skills in managing data in databases
and to gain understanding of data model development and implementation using a
commercially available database management system development tool.
On completion of this assignment students will be able to:
a. Model organisational information requirements using conceptual data
modelling techniques.
b. Convert the conceptual data models into relational data model and verify
their structural characteristics with normalisation techniques.
(Part A 5%+ Part B 15% )=20% of total assessment for the unit
Part A 25 marks
Part B 50 marks
Final mark for Part B= [Part B)] * % Factor for Part C
Final mark for Part B is calculated based on % of the scores obtained for
Part C (performance evaluation)

No specific word limit
Part A 25 marks –WEEK 3 -12th April 2020 before 5:00 PM
Pa Part B 50 marks- WEEK 8 – 10th May 2020
Description
of This assignment consists of three parts:
this assignment: Part A: Summit database and MS word document with answers to SQL queries
Part B: Submit MS word document with answers to B1,B2, B3
Part C: Performance demonstration (10 marks). During week 7 or week 8 lab classes
Submission
 Part A – Submit on Moodle WEEK 3
Guidelines
 Part B – Submit on Moodle WEEK 8 save as
“MN405_T3_2019_Assigment1_your_name.doc”
 The assignment must be in MS Word format, 1.5 spacing, 11-pt Calibri (Body)
font and 2.5 cm margins on all four sides of your page with appropriate section
headings.
 Reference sources must be cited in the text of the report, and listed
appropriately at the end in a reference list using IEEE referencing style.
Extension
 If an extension of time to submit work is required, a Special Consideration
Application must be submitted directly to the School's Administration Officer,
in Melbourne on Level 6 or in Sydney on Level 7. You must submit this
application three working days prior to the due date of the assignment. Further
information is available at:
http://www.mit.edu.au/about-mit/institute-publications/policies-proceduresand-guidelines/specialconsiderationdeferment
Word limit
Due Date

Prepared by: Dr. Deepani Guruge

Moderated by: Dr Mohammad Mohammad

March,2020

MN405

Academic
Misconduct

Data and Information Management

Page | 2

 Academic Misconduct is a serious offence. Depending on the seriousness of the
case, penalties can vary from a written warning or zero marks to exclusion from
the course or rescinding the degree. Students should make themselves familiar
with the full policy and procedure available at:
http://www.mit.edu.au/about-mit/institute-publications/policiesprocedures-and-guidelines/Plagiarism-Academic-Misconduct-PolicyProcedure. For further information, please refer to the Academic Integrity
Section in your Unit Description.

Prepared by: Dr. Deepani Guruge

Moderated by: Dr Mohammad Mohammad

March, 2020

MN405

Data and Information Management

Page | 3

Assignment Description
Part A: Data Modelling (25 marks)
Question A1

- Create the Database

(10 Marks)

The snapshot of Party_BookingDB database structure is given below. Party_BookingDB is a database
that keeps track of information about the Bookings, Staff, Menus and Customers registered in the
system.
Assume that, you are working as an IT specialist in an organisation and are required to extract information
from this database by building the database and executing SQL queries according to the instructions given
below.
The primary keys and foreign keys are marked and entered in the database structure as shown in
Figure1.

Figure 1: Snapshot of Party_BookingDB database © Database Answers Ltd. 2016

Prepared by: Dr. Deepani Guruge

Moderated by: Dr Mohammad Mohammad

March, 2020

MN405

Data and Information Management

Page | 4

a. First, you need to create the above database in MS Access. Create only 5 tables.

NB: You only have to create 5 tables for Customers, Bookings, Menus, Menus_Booked, and
Staff. You do not have to create other tables in the ER.
(5 Marks)
b. Populate those tables with suitable data (at least 3 records per table).
i.

You can use Datasheet view in MS Access or SQL statement (as given below) to enter suitable
data records.
INSERT into TableName
VALUES (“..”,”..”,.....)
Include Proper foreign keys to create relationships in between tables.
Hint: If you want to create a one-to-many relationship in your database, include one side
primary key in the many side table as foreign keys.
(5 Marks)

ii.

NB:
You need to upload your database on submission link before the due date.
This is an individual assignment; it should be your own individual work (You should not copy Ms
Access Database). If not, it is considered as cheating and you will get zero marks for the whole
assignment.

Question A2 - Write SQL queries –basic skills

(15 Marks)

Write SQL queries for the following questions.
Execute the following queries on the “Party_BookingDB” database you created in MS Access. Include screen
shots of the outputs and all SQL statements you used to answer following questions:
(3 marks for each screen shot & remaining marks for the SQL query)
a.

b.
c.

Display details of all Customers recorded in the database. Your result set should be sorted on
ascending order of the Customer_id.
(5 Marks)
Assume you need to find details of the Bookings in the Bookings table.

(5 Marks)

To count how many bookings recorded in the booking table.

(5 Marks)

Prepared by: Dr. Deepani Guruge

Moderated by: Dr Mohammad Mohammad

March, 2020

MN405

Data and Information Management

Page | 5

Part B: Conceptual data models and SQL Queries (50 marks)
Question B1- Write SQL queries –

(30 Marks)

Write SQL queries for the following questions.
Execute the following queries on the “Party_BookingDB” database you created in MS Access. Include screen
shots of the outputs and all SQL statements you used to answer following questions:
(3 marks for each screen shot & remaining marks for the SQL query)

a. Prepare a list of all the records in the “Booking” table where date_of _event is on 4th June 2020
which are booked on 2nd February 2020.
(5 Marks)
b. Assume you need to find out BookingID, CustomerID, date_booked and menu_id of all bookings.
(Hint: Here you need to join two tables Bookings and Menus_Booked)
(10 Marks)
c. Assume that you want to count how many bookings are there in the “Bookings” table for the
customer with customer_id =124. Write a query to find the number.
(5 Marks)
d. Staff at “Party_Booking” needs to prepare a report with CustomerID, StaffID(Booking taken by),
first_name(staff) and date_booked of all events scheduled for 24th August 2020.
(Hint: Join Bookings and staff)
(10 Marks)

Question B2

- ER-to-Relational Mapping

(10 Marks)

This question is on “ER-to-Relational Mapping”. Figure 2 shows the ER diagram that captures important
information about customer and loans.

Figure 2: Data Model for a bank loan1

a. You are required to mark cardinality according to the following statements.
I.
Customer can borrow one or more loans
II.
One loan belongs to only one customer.
(5 Marks)

Prepared by: Dr. Deepani Guruge

Moderated by: Dr Mohammad Mohammad

March, 2020

MN405

Data and Information Management

Page | 6

b. Convert the ER diagram into a relational database schema. Be certain to indicate primary keys
(underline). For example, Customer entity can be mapped to relational database schema as given
below.
(5 Marks)
Eg. Customer (Cust_ID:text; name:….)
Borrower (cust_id, loan_id, access_date)
Customer (cust_id, name, street_address, city)
Loan (loan_id, amount, cust_id)

Question B3

Research and trends

(10 Marks)

a. Refer to the paper “Visualizing Big Data with augmented and virtual reality: challenges and
research agenda”, which is accessible through this URL:
https://link.springer.com/article/10.1186/s40537-015-0031-2 , write a paragraph explaining the
usage of AR and VR in Big Data visualization with proper references.
(4 Marks)
b. Create 2 data visualisations using Tableau. Tableau (www.tableau.com ) is a data visualization
software. Use data provided in “Resources/ Sample Data (https://public.tableau.com/enus/s/resources )” section in the Tableau.
(6 Marks)

First you need to install Tableau App. Instructions are given below.
Tableau (www.tableau.com) is a data visualization tool. Tableau can help anyone see and
understand their data. Connect to almost any database, drag and drop to create visualizations.
Install Tableau Public (https://public.tableau.com/s/ ) on your laptop / computer and create any
2 visualisations.

Follow the following instructions:
i. First go to Tableau Public https://public.tableau.com/s/ and enter your email
address and select “Download the App”

ii. Then you can download the software and run the .exe file to install.

Prepared by: Dr. Deepani Guruge

Moderated by: Dr Mohammad Mohammad

March, 2020

MN405

Data and Information Management

Page | 7

iii. Now you will get the following starting screen. Here you can upload MS Excel or
MS Access file. Watch this video to find more details on “How to”.
https://public.tableau.com/en-us/s/resources

iv. You can use any data set in available in Resource section of Tableau
(https://public.tableau.com/en-us/s/resources) to create 2 visualisations. It
should be your own individual work.

Prepared by: Dr. Deepani Guruge

Moderated by: Dr Mohammad Mohammad

March, 2020

MN405

Data and Information Management

Page | 8

Part C: (10 marks)
c. Performance Evaluation
Demonstrate your progress to your tutor in week 7 or week 8 lab classes.
This will allow students to demonstrate their understandings and skills (to their teacher) as they
perform these activities. The teacher evaluates you on the quality of your ability to perform specific
tasks and the products you created in the process. Your final mark for Part B of the assignment will be
calculated based on this evaluation as describe below.
(10 Marks)

Example for calculation of final marks for Assignment 1
Example 1: If a student obtained
Part A Part B –
Part C –Performance
Evaluation
Total Part B(out of 50)

15/25
40/50
10/10
(40)*10/10

Example 2: If a student obtained
Part A Part B –
Part C –Performance
Evaluation
Total Part B(out of 50)

15/30
40/60
0/10
(40)*0/10

Other useful Resources



Getting started -https://public.tableau.com/en-us/s/resources
Data visualization field guide: a definition, examples, and learning resources
https://www.tableau.com/learn/articles/data-visualization

Prepared by: Dr. Deepani Guruge

Moderated by: Dr Mohammad Mohammad

March, 2020

MN405

Data and Information Management

Page | 9

Marking Criteria:
Marks are allocated for each part as below.
Section
PART A

PART B

PART C

Data
Modelling

(60 marks)

Due date
Week 3

Question B1

Week 8

Question B2
ER-to-Relational
Mapping

Week 8

Question B3

Week 8

Performance
Evaluation
for Part B
(%)

Prepared by: Dr. Deepani Guruge

Only during
week 7 or week
8 lab classes

Description of the section
Model building: Build and
upload your database on the
submission link
Submit SQL Queries separately
in MS word document
SQL query writing

Marks
10

Questions on mapping
conceptual data models into
relational data model/ Issues
related to integrity of
database.
Model organisational
information requirements
TOTAL marks for the
assignment
Demonstrate your progress to
your tutor in week 7 or week 8
lab classes.
Final mark for part B will be
calculated based on this

10

Moderated by: Dr Mohammad Mohammad

March, 2020

15
30

10
75

MN405

Data and Information Management

Page | 10

Marking Rubric for Assignment 1
Excellent

Very Good

Good

Satisfactory

Unsatisfactory

100%

80%

60%

40%

0%-20%

Demonstra
ted
excellent
model
building
ability.

Demonstrate
d model
building
ability.

Demonstrate
d reasonable
model
building
ability.

Demonstrate
d some
model
building
ability but
not
complete.

Did not
demonstrate
the model
building
ability.

Part B – Question
B1
Model
organisational
information
requirements
using conceptual
data modelling
techniques and
Query Writing
Skills
(30 marks)
Part B – Question
B2 -Convert the
conceptual data
model into
relational data
model.
(10 marks)

Evidence of
accurate
and wellwritten
queries

Evidence of
good query
writing skills.

Generally
relevant.

Demonstrated
reasonable
query writing
skills.

Did not
demonstrate
evidence of
understanding
the topic.

Demonstrat
ed
excellent
ability to
think
critically.

Demonstrate
d an ability to
think
critically.

Demonstrated
reasonable
ability to think.

Demonstrated
some ability
to think
critically but
not complete.

Did not
demonstrate
ability to think
critically.

Part B – Question
B3 -Model
organisational
information (By
using online
software Tool and
Big data
technologies)
(10 marks)
Q4.
Performance
Evaluation

Demonstrat
ed
excellent
knowledge
on the topic

Demonstrate
d good
knowledge on
the topic.

Demonstrated
reasonable
knowledge on
the topic

Demonstrated
some
knowledge on
the topic.

Did not
demonstrate
any
knowledge on
the topic.

Demonstrat
ed
excellent
knowledge
on the topic

Demonstrate
d good
knowledge on
the topic.

Demonstrated
reasonable
knowledge on
the topic

Demonstrated
some
knowledge on
the topic.

Did not
demonstrate
any
knowledge on
the topic.

PART A
Model building
(25 marks)

(10 marks)

Prepared by: Dr. Deepani Guruge

Moderated by: Dr Mohammad Mohammad

March, 2020