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: •
One file format can be
transformed into another file format •
Algorithms (simple) on
individual track logs:- distance travelled, average speed etc – works on GPX, LINESTRINGS •
Algorithms (simple) on
individual track logs:- creating stop points, other significant points – works
on GPX, LINESTRINGS •
Algorithms (complex) on
individual track logs:- intersection with landscape features/points of interest
(POI’s) etc – works on Shapefiles.
•
Algorithms on multiple track
logs (data mining):- association rules (fuzzy spatio-temporal), clustering
algorithms, Frechet distance of similarity between tracks – works on arrays of
objects •
Algorithms on multiple track
logs (semantic):- GeoSPARQL9 only works on RDF and concept
hierarchies •
Algorithms on multiple track
logs (‘group’ or ’common’ behaviours among moving entities):- some examples of these
patterns are flocks, moving clusters, convoy queries, closed swarms, group
patterns, periodic patterns – works on LINESTRINGS, arrays of objects Some of the reports that will be
important to run from the database design include: •
a list of all tracks (raw data)
in the database •
a list of transformed formats
available for a particular track •
a list of algorithms that have
been applied to each of the different formats of tracks, and the results of
these algorithms 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: •
Each Track will have a unique
ID, a name, a date and location, and will be comprised of multiple Points. •
Each Point will have a
Latitude, Longitude, Date and Time. •
There will be many types of
File Format, including the original “raw data” format of either GPX or NMEA,
and transformed formats of Shapefile, LineString, GML, RDF and so on. •
Transformations are used to
change from one file format to another. •
There are many Algorithms
possible, some simple (e.g. descriptive statistics, preprocessing), and some
complex (e.g. data mining, semantic operations), but all will have Results •
Results can be simple values
(calculation of average Speed, distance travelled), a complex value (series of
Points that constitute a cluster), or even a geometry (a derived line segment
or polygon that represents an area of significance). A Result will reference in
some way the file and algorithm from which it is derived. It should also have a
date and name. •
Complex Algorithms (data
mining) include segmentation, clustering, prediction •
Complex Algorithms
(behavioural) include flocking, following, avoidance etc. •
Complex Algorithms also include
those based specifically by querying semantic (RDF) formatted data. •
Algorithms,Transformations,
File Formats, Results constitute the parts of a specific Experiment. There will
be many Experiments. An Experiment will have a name and date range (start and
finish), and notes. 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: •
An ER Diagram with all entity
names, attribute names, primary and foreign keys, relationships, cardinality
and participation indicated. All many to many relationships should be resolved.
•
A discussion of normalisation
including the normal form that each entity is in and why that is optimal. Also,
a discussion of how normalisation was achieved for that entity. We want 3NF
unless there is a compelling reason to keep a particular relation in 2NF. •
A list of relationships with
all table names, attributes, primary and foreign keys indicated as per the
conventions given in the lecture slides (i.e. entity/table names in capitals,
attributes as proper nouns, primary key underlined and foreign keys in
italics). •
A database schema indicating
the type and purpose of all attributes. Academic Presentation Assignment should be presented in
accordance with: •
General Guide to Referencing: https://federation.edu.au/__data/assets/pdf_file/0020/313328/FedUni-GeneralGuide-to-Referencing-2016ed.pdf •
General Guide to Writing and
Study Skills: http://federation.edu.au/__data/assets/pdf_file/0018/190044/GeneralGuide-to-Writing-and-Study-Skills.pdf •
Guide to Layout and Appearance:
https://federation.edu.au/__data/assets/pdf_file/0017/190043/General-Guideto-Layout-and-Appearance.pdf 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
•
Overall presentation of the
report
•
Full APA referencing of all
materials used and full disclosure of assistance from all sources including
tutors and other students
ER
Diagram
•
Completeness of diagram
•
Correct notation and
convention used
•
All assumptions clearly noted
•
Primary and foreign keys
•
Resolution of many to many
relationships
Normalisation
•
All entities and relationship
in appropriate normal form
•
Discussion of normalisation
for all entities and relationships
•
Appropriate interpretation of
each normal form, arguments for leaving the schema in the normal form you
consider optimal.
Relational Schema
•
Primary keys used
•
Foreign keys correctly
identified including parent entity
•
Schema is a correct
translation of the E-R diagram submitted with appropriate tables, columns,
primary keys, and foreign keys
•
Types and restrictions on
attributes given
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
[1] https://wiki.openstreetmap.org/wiki/GPX
2 https://www.gpsinformation.org/dale/nmea.htm
[2] https://www.esri.com/library/whitepapers/pdfs/shapefile.pdf
[3] https://en.wikipedia.org/wiki/Geography_Markup_Language
[4] https://developers.google.com/kml/documentation/
[5] https://en.wikipedia.org/wiki/Resource_Description_Framework
[6] https://geojson.org/
[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
