Assignment 1: ER Model & Relational
Schema
Overview
The purpose of
this task is to develop student’s skills in designing and implementing a
relational database for a given case study.
Timelines and Expectations
Percentage Value of Task: 20%
Due: Week 7 – Sunday, September 15th, 2019 at
11:55pm
Minimum time expectation:
Preparation for this task will take approximately 20 hours
Learning Outcomes Assessed
The following course learning
outcomes are assessed by completing this assessment:
K4. Design a relational database for a provided scenario utilising
tools and techniques including ER diagrams, relation models and normalisation.
K5. Describe relational algebra and
its relationship to Structured Query Language (SQL). A1. Design and implement a
relational database using a database management system.
Assessment Details
Background
You have been commissioned to create
a database for a data mining project related to mobility using GPS track logs.
Very large “trajectory” datasets are increasingly availability due to the
proliferation of positioning sensors and location-based services. However, a
successful integration of mobility data still requires the development of
conceptual and database frameworks that will support appropriate data
representation and manipulation capabilities.
GPS track logs come in many different kinds of formats,
for instance GPX[1] or NMEA2
files. These formats can support simple descriptive statistics such as:
distance travelled, average speed, time in motion vs. time stationary,
elimination of stationary segments. However, there are very few data mining
algorithms or libraries that can be used on this kind of file. Additionally,
when processing GPX files often there may have been added custom extensions to
deal with related to the domain, for instance data like heart rate, cadence,
power, and so on.
It
is important to understand the difference between the raw data from the GPS
device, the track log in GPX/NMEA, and a “route”, often called a semantic
trajectory. A route is derived from
the track, and contains meaning, or semantic tags. For instance there will now
be a start and end to the route, specific places that have been visited, and so
on. This is in contrast to the raw data which is merely a time-based sequence
of geographical coordinates. The track log has been “processed” or
“transformed” into the route.
Therefore it is important
to be able to transform from one file format into another, for instance to transform a
GPX tracklog into an ESRI Shapefile[2], or
into GML[3],
KML[4], RDF[5]
or GeoJSON[6]. format Track log data can also transformed into “LINESTRING” for insertion into a spatially-enabled relational database. MySQL for instance
provides many built-in functions like POINT, LINESTRING, POLYGON[7] etc. The main drawback with LINESTRING is that they often (depending on the
database) do not contain timestamp data. A
further solution is to store the track data as an array of objects, with
keys corresponding to different attributes such as latitude, longitude,
elevation, time from start, distance from start, speed, heart rate, etc.
Metadata can also be stored along the route to specify details about each
section. When parsing the array of track points, the metadata can be used to
split a route into a series of Segments.
This Assessment’s
modelling task is to develop a database schema to store track logs, and to keep a record of
any calculations and transformations that have been carried out on these track
logs into different formats.
Summary of operations:
•
•
•
LINESTRINGS
•
•
•
•
Some of the reports that will be
important to run from the database design include:
•
•
•
No normalisation
has been undertaken on these entities, so there may be many to many
relationships that are not resolved. Your submission should have all many to
many relationships resolved. You may add entities or attributes as you see fit.
The minimum entities you are expected
to have are listed below:
•
•
•
•
•
•
•
•
•
•
If you are interested in the various
standards available in this area, please refer to:
• ISO (International Standards Organization) TC 211 - Geographic information/Geomatics[8]
• OGS (Open Geospatial Consortium) Abstract Specifications11 -
very extensive, redundant and complimentary to ISO's.
Requirements
This assignment should be presented
in a report format, including the following items:
•
•
•
•
Academic Presentation
Assignment should be presented in
accordance with:
•
•
•
Submission
The assignment is to be
submitted via the Assignment 1 submission box in Moodle. This is to be found in the Assessments
section of the course Moodle shell.
Marking Criteria/Rubric
Assessment Criteria |
Marking Scale |
Poor Excellent 1 ....................... 5 |
|
Presentation
and Referencing • • |
0 |
0 |
|
ER
Diagram • • • • • |
0 |
0 |
|
0 |
|
0 |
|
0 |
|
Normalisation • • • |
0 |
0 |
|
0 |
|
Relational Schema • • • • |
0 |
0 |
|
0 |
|
0 |
|
0 |
|
Total Mark [75
marks] |
0.0 |
Total Worth [20%] |
0.0 |
Feedback
Feedback and marks will be
provided in Moodle. Marks will also be available in FDL Marks.
Plagiarism:
Plagiarism is the presentation of the
expressed thought or work of another person as though it is one's own without
properly acknowledging that person. You must not allow other students to copy
your work and must take care to safeguard against this happening. More
information about the plagiarism policy and procedure for the university can be
found at http://federation.edu.au/students/learning-and-study/online-help-with/plagiarism
Please refer to the Course Description for information
regarding late assignments, extensions, and special consideration. A reminder
all academic regulations can be accessed via the university’s website, see: http://federation.edu.au/staff/governance/legal/feduni-legislation
[7] https://dev.mysql.com/doc/refman/8.0/en/gis-mysql-specific-functions.html 9 https://www.opengeospatial.org/standards/geosparql
[8]
http://www.iso.org/iso/home/store/catalogue_tc/catalogue_tc_browse.htm?commid=54904
11 http://www.opengeospatial.org/standards/as