Database Design Report: ERD and Normalization for Tracking System

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

Contents
Introduction...........................................................................................................................................3
ERD: (Entity relationship diagram)........................................................................................................4
Normalisation........................................................................................................................................4
The list of relationship:..........................................................................................................................5
Database Schema:.................................................................................................................................5
Conclusion.............................................................................................................................................8
Reference:.............................................................................................................................................9
Table of figure
Figure 1: ERD.........................................................................................................................................4
Table of tables:
Table 1: Relationship table....................................................................................................................5
Table 2: User table.................................................................................................................................6
Table 3: Device table.............................................................................................................................6
Table 4: Location Point.........................................................................................................................7
Table 5: Track table...............................................................................................................................7

Introduction
The report consists of the ERD and the explanation of the normalization forms which will
help the user to understand and also there is detailed discussion about the entity and attributes
of the ERD. The tool used for the ERD is the draw.io which is online tool.

ERD: (Entity-relationship diagram)
Figure 1: ERD
Normalization
This is the process in a database which helps to minimize the redundancy from the tables or
the relations. This helps to eliminate the characteristics of undesirable things such as deletion,
update, etc. This also helps to divide the table from larger table to smaller table and this helps
to show the relationship between the tables.

There is four types of normal form:
1. I NF: Contains the atomic value
2. 2NF: Non-prime attributes are fully functional dependent on the primary key
3. 3NF: There should be no transition dependency.
4. BCNF: Should not have multi-valued dependency (Kumar, & Azad, 2019).
The list of relationship:
Table 1: Relationship table
Table Name Description
User A table of user consists of the user_id, name
and cont_no attributes and there is user_id
which is primary key and this has the
relationship with the table track which is one
too many as the user can track many times.
Location_Point A table consists of the Loc_Id which is
primary key, time, latitude; longitude and
date are the attributes of the location point
table. The relationship of the location point is
one too many with the track table, as the
tracking of the location can be more than one
time that is if two different users will go to
the same location.
Device A table consists of the device_id as the
primary key and has the manufacturer,
model, serial_no and type are the attributes of
the table. This has relationship with the track
that is one too many that mean one device

can track many tracks at one time.
Track This table consists of the unique_id which
will work as the primary key, name, date, are
the attributes and there is foreign key which
will work as for the reference of the other
table. The foreign key loc_id, user_id, and
device Id.
Database Schema:
Table 2: User table
Attributes Primary key//
Foreign Key
Data type Description
User_Id This is the primary
key used for unique
identification. This
will be not null
Int (10) This will help to
get the different
user id with the
name of the user
and the contact
number of the user.
Name Not PK Varchar(20) Name of the user.
Cont_No Not Pk Int (10) The contact
number of the user.
Table 3: Device table
Attributes Primary key/
Foreign Key
Date type Description
Device_id This is the primary
key used for unique
identification. This
will be not null
Int (10) This will help to
get the unique id.

Manufacture Not PK Varchar(20) Manufacture of the
device.
Model Not PK Varchar(20) Model of device.
Serial_no Not PK Int(10) Serial_no of the
device
Type Not PK Varchar(20) Type of the device.
Table 4: Location Point
Attributes Primary key/
Foreign Key
Data type Description
Loc_id This is the primary
key used for unique
identification. This
will be not null
Int(10) This will help to
get the unique id.
Time Not PK Time Time of the
location point.
Latitude Not PK Varchar(20) Latitude of the
location point
Longitude Not PK Varchar(20) Longitude of
location.
Date Not PK Date Date of location
point.
Table 5: Track table
Attributes Primary key/
Foreign Key
Data type Description
Unique_id This is the primary
key used for unique
identification. This
will be not null
Int(10) This will help to
get the unique id.
Name Not PK Varchar(20) Name of the track.

Loc_Id Foreign Key Int(10) This is the foreign
key which helps for
the reference of the
other table.
Device_id Foreign Key Int(10) This is the foreign
key which helps for
the reference of the
other table.
Date Not PK Date Date of location
point.
User_Id Foreign Key Int(10) This is the foreign
key which helps for
the reference of the
other table.

Conclusion
The report gives detail about the normalization, entity relationship diagram, etc. This help
was the user to understand and also there is detailed discussion about the entity and attributes
of the ERD. The tool used for the ERD is the draw.io which is online tool.

Reference:
Das, S., Grbic, M., Ilic, I., Jovandic, I., Jovanovic, A., Narasayya, V. R., ... & Chaudhuri, S.
(2019, June). Automatically Indexing Millions of Databases in Microsoft Azure SQL
Database. In Proceedings of the 2019 International Conference on Management of Data (pp.
666-679). ACM.
Ghawi, R. (2019, May). Interactive Decomposition of Relational Database Schemes Using
Recommendations. In International Conference: Beyond Databases, Architectures and
Structures (pp. 97-108). Springer, Cham.
Kumar, K., & Azad, S. K. (2019). Determining All Possible Candidate Keys for Relational
Database Design. In Innovations in Soft Computing and Information Technology(pp. 241-
248). Springer, Singapore.