ITECH 2004 DATA MODELLING CRICOS Provider No. 00103D ITECH2004 Assignment1 ER-model 2019 (partner).docx Page 1 of 5 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 1st, 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 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 1 https://wiki.openstreetmap.org/wiki/GPX 2 https://www.gpsinformation.org/dale/nmea.htm ITECH 2004 DATA MODELLING CRICOS Provider No. 00103D ITECH2004 Assignment1 ER-model 2019 (partner).docx Page 2 of 5 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 3 https://www.esri.com/library/whitepapers/pdfs/shapefile.pdf 4 https://en.wikipedia.org/wiki/Geography_Markup_Language 5 https://developers.google.com/kml/documentation/ 6 https://en.wikipedia.org/wiki/Resource_Description_Framework 7 https://geojson.org/ 8 https://dev.mysql.com/doc/refman/8.0/en/gis-mysql-specific-functions.html 9 https://www.opengeospatial.org/standards/geosparql ITECH 2004 DATA MODELLING CRICOS Provider No. 00103D ITECH2004 Assignment1 ER-model 2019 (partner).docx Page 3 of 5 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/Geomatics10 • 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. 10 http://www.iso.org/iso/home/store/catalogue_tc/catalogue_tc_browse.htm?commid=54904 11 http://www.opengeospatial.org/standards/as ITECH 2004 DATA MODELLING CRICOS Provider No. 00103D ITECH2004 Assignment1 ER-model 2019 (partner).docx Page 4 of 5 • 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. ITECH 2004 DATA MODELLING CRICOS Provider No. 00103D ITECH2004 Assignment1 ER-model 2019 (partner).docx Page 5 of 5 Marking Criteria/Rubric Assessment Criteria Marking Scale Poor Excellent 1 ....................... 5 Presentation and Referencing • Overall presentation of the report 0 • Full APA referencing of all materials used and full disclosure of assistance from all sources including tutors and other students 0 ER Diagram • Completeness of diagram 0 • Correct notation and convention used 0 • All assumptions clearly noted 0 • Primary and foreign keys 0 • Resolution of many to many relationships 0 Normalisation • All entities and relationship in appropriate normal form 0 • Discussion of normalisation for all entities and relationships 0 • Appropriate interpretation of each normal form, arguments for leaving the schema in the normal form you consider optimal. 0 Relational Schema 0 • Primary keys used 0 • Foreign keys correctly identified including parent entity 0 • Schema is a correct translation of the E-R diagram submitted with appropriate tables, columns, primary keys, and foreign keys 0 • Types and restrictions on attributes given 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