Database Design for GPS Tracklogs: ER Model and Normalization

Desklib provides solved assignments and past papers to help students succeed.

Assignment 1
ER Model & Relational Schema

Table of Contents
Introduction................................................................................................................................2
Entity Relationship Diagram......................................................................................................3
Normalization.............................................................................................................................4
Relationship table:......................................................................................................................4
Database Schema:......................................................................................................................4
Conclusion:................................................................................................................................8
2

Introduction
In this we assignment we create the Entity-Relationship diagram for the GPS tracklogs in to
store the data related to the GPS tracks and the algorithm used for tracking. We will also
discuss the normalization of the developed database. We will mention the data schema also in
this report.
3

Entity Relationship Diagram
Figure 1Entity Relationship Diagram
4

Normalization
The normalization process id used in the above ERD to remove the data redundancy. This
also helps to save the storage space by avoiding the data duplicity in the system. In the
normalization process, the partial and transitive dependencies have to remove to get the
efficient storage database (Al-Masree, 2015).
Following the relationship table and database schema is created on the basis of the
normalization process.
Relationship table:
Tables Table description
Meta_data This table stores the metadata of the
tracking data.
Routing This table stores the routes for the GPS
tracking
Tracking This table stores the tracking records of the
GPS.
Point This table records the location point for
GPS tracking.
Resultofalgo This table stores the result coming from the
various algorithms.
Formattedtrack This table store the formatting of the file
which is generated in the system.
Experiment This table stores all the experiments.
Algorithm This table records the algorithms which are
used to track the GPS location.
Fileformat This table used to store the format of the
file.
Transformation This table records the transformation
records of the files generated in the GPS
tracking system (Roy-Hubara, et. al., 2018).
5

Database Schema:
Meta_data Table:
Entity Data type Constraint
dataid Varchar(10) Primary Key
elevation Varchar(20)
Speed Varchar(20)
heartrate Varchar(20)
startingdistance Float
startdatetime Date time
routeid Varchar(10) Foreign key
power Varchar(20)
Routing Table:
Entity Data type Constraint
routeid Varchar(10) Primary Key
trackid Varchar(10) Foreign key
startpointroute Varchar(50)
endpointroute Varchar(50)
Tracking Table:
Entity Data type Constraint
Trackid Varchar(10) Primary Key
fileformatid Varchar(10) Foreign key
trackname Varchar(20)
tracklocation Varchar(80)
timestamp Varchar(20)
Point Table:
Entity Data type Constraint
pointid Varchar(10) Primary Key
6

Trackid Varchar(10) Foreign key
longitude Varchar(20)
lattitude Varchar(20)
timestamp Varchar(20)
Resultofalgo table:
Entity Data type Constraint
resultid Varchar(10) Primary Key
trackid Varchar(10) Foreign key
algoid Varchar(10) Foreign key
Formattedtrack table:
Entity Data type Constraint
trackid Varchar(10) Primary Key
transformationid Varchar(10) Foreign key
Experiment Table:
Entity Data type Constraint
expid Varchar(10) Primary Key
Startingdate Date time
Endingdate Date time
expnotes Varchar(100)
Algorithm table:
Entity Data type Constraint
Algoid Varchar(10) Primary Key
Algoname Varchar(50)
experimentid Varchar(10) Foreign key
Algolevel Varchar(10)
7

Fileformat table:
Entity Data type Constraint
Fileid Varchar(10) Primary Key
Filename Varchar(50)
Filetype Varchar(10)
Formatid Varchar(10) Foreign key
Transformation table:
Entity Data type Constraint
transformationid Varchar(10) Primary Key
experimentid Varchar(10) Foreign key
Formatsrc Varchar(100)
Targetformat Varchar(30)
8

Conclusion:
The ERD is successfully created for the GPS tracking system. We have shown all the
possible entities with suitable attributes. The primary and foreign keys are also mentioned in
the ERD. The database is designed according to the normalization process. We have also
described the database schema and relationship table in order understand the database.
9

References
Al-Masree, H. K. (2015). Extracting Entity Relationship Diagram (ERD) from relational database
schema. International Journal of Database Theory and Application, 8(3), 15-26.
Roy-Hubara, N., Rokach, L., Shapira, B., & Shoval, P. (2018). Evaluation of a Design Method for Graph
Database. In Enterprise, Business-Process and Information Systems Modeling (pp. 291-303). Springer, Cham.
10