Data and Information Management MN405 Assessment

Prepared by: Dr. Deepani Guruge Moderated by: Dr Mohammad Mohammad July,2020
Assessment Details and Submission Guidelines
Trimester T2 2020
Unit Code MN405
Unit Title Data and Information Management
Assessment
Author
Deepani Guruge
Assessment Type Assignment 1 (Individual)
Assessment Title Data Modelling and Data Managing
Purpose of the
assessment (with
ULO Mapping)
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.
Weight (Part A 5%+ Part B 15% )=20% of total assessment for the unit
Total Marks Part A 25 marks
Part B 50 marks
Word limit No specific word limit
Due Date Part A 25 marks –WEEK 3 – 6
th August 2020 before 11:55 PM
Pa Part B 50 marks- WEEK 8 –3
rd September 2020 before 11.55PM
Description of
this assignment:
This assignment consists of 2 parts:
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
Submission
Guidelines
• Part A – Submit on Moodle WEEK 3
• Part B – Submit on Moodle WEEK 8 save as
“MN405__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
Academic
Misconduct
• 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:
MN405 Data and Information Management Page | 2
Prepared by: Dr. Deepani Guruge Moderated by: Dr Mohammad Mohammad July, 2020
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.
MN405 Data and Information Management Page | 3
Prepared by: Dr. Deepani Guruge Moderated by: Dr Mohammad Mohammad July, 2020
Assignment Description
Part A: Data Modelling (25 marks)
Question A1 – Create the Database (10 Marks)
The snapshot of “Customer_ Delivery” database structure is given below. Customer_ Delivery is a
database that keeps track of information about the Customer orders, deliveries, Staff, products 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.
NOTE: The primary keys (PK) and foreign keys (FK)are marked in the database structure as shown in
Figure1. Some keys are primary keys (PK) as well as foreign keys (FK), they are marked as PF.
Figure 1: Snapshot of Customer_ Delivery database © Database Answers Ltd. 2016
MN405 Data and Information Management Page | 4
Prepared by: Dr. Deepani Guruge Moderated by: Dr Mohammad Mohammad July, 2020
a. First, you need to create the above database in MS Access. (5 Marks)
You only need to create 6 tables including 5 tables for Customer, Employee, Actual_Orders,
Deliveries, Trucks and any other table (either Products table or Actual_Order_Products table)
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 (“..”,”..”,…..)
ii. 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)
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 “Customer_ Delivery” 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. Display details of all Employees recorded in the database. Your result set should be sorted on
descending order of the employee_name. (5 Marks)
b. Write a Query to find details of the Deliveries. (5 Marks)
c. Write a query to find the details of the customer whose phone number is “0477988779”
(5 Marks)
MN405 Data and Information Management Page | 5
Prepared by: Dr. Deepani Guruge Moderated by: Dr Mohammad Mohammad July, 2020
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 “Customer_ Delivery” 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 “Actual_Orders” table where actual _order _date is on 4th
August 2020 which is not delivered.
Assume “Order _status_code” is “Delivered” for all the orders already delivered and
“Not_Delivered” for others. (5 Marks)
b. Write a Query to find out staff details; employee_name and phone, who is responsible for
delivering the order where “actual_order_ID ” is “C005”.
(Hint: Here you need to join two tables) (10 Marks)
c. Assume that you want to count how many orders are there in the “Actual_orders” table with
product_id =124. Write a query to find the number. (5 Marks)
d. Write a query to find out following information.
Details of the delivery (Delivery _Id, delivery_status), employee who is handling these orders
(employee_name, employee_phone) and details of the truck assigned for these deliveries
(Truck_id, truck_licence_number). (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 students..
MN405 Data and Information Management Page | 6
Prepared by: Dr. Deepani Guruge Moderated by: Dr Mohammad Mohammad July, 2020
Figure 2: Data Model for an enrolment
a. You are required to mark cardinality according to the following statements.
I. One student can enrol in one or more courses
II. One course can have many students enrolled.
(5 Marks)
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:….)
Question B3 Research and trends (10 Marks)
a. Discuss why “Big Data” is important.
You are required to write a paragraph explaining the term and why it is important with proper
references. (5 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. (5 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.
MN405 Data and Information Management Page | 7
Prepared by: Dr. Deepani Guruge Moderated by: Dr Mohammad Mohammad July, 2020
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.
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
MN405 Data and Information Management Page | 8
Prepared by: Dr. Deepani Guruge Moderated by: Dr Mohammad Mohammad July, 2020
Marking Criteria:
Marks are allocated for each part as below.
Section Due date Description of the section Marks
PART A Data
Modelling
Week 3 Model building: Build and
upload your database on the
submission link
Submit SQL Queries separately
in MS word document
10
15
PART B (60 marks) Question B1 Week 8 SQL query writing 30
Question B2
ER-to-Relational
Mapping
Week 8 Questions on mapping
conceptual data models into
relational data model/ Issues
related to integrity of
database.
10
Question B3

Week 8 Model organisational
information requirements
10
TOTAL marks for the
assignment
75
MN405 Data and Information Management Page | 9
Prepared by: Dr. Deepani Guruge Moderated by: Dr Mohammad Mohammad July, 2020
Marking Rubric for Assignment 1
Excellent
100%
Very Good
80%
Good
60%
Satisfactory
40%
Unsatisfactory
0%-20%
PART A
Model building
(25 marks)
Demonstrat
ed
excellent
model
building
ability.
Demonstrate
d model
building
ability.
Demonstrated
reasonable
model building
ability.
Demonstrated
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)
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.
Part B – Question
B2 -Convert the
conceptual data
model into
relational data
model.
(10 marks)
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)
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.
Harvard Ciation