Reference no: EM132365924

Assignment - 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.

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 GPX1 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 Shapefile3, or into GML4, KML5, RDF6 or GeoJSON7. 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, POLYGON8 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.

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.