FIT9132 Introduction to Databases: Conceptual Model for TDS

This report details the conceptual database model for a Traffic Demerit System (TDS). It begins with an Entity Relationship Diagram (ERD) illustrating the relationships between entities such as drivers, vehicles, manufacturers, offences, and suspensions. The model incorporates key attributes like Vehicle Identification Numbers (VINs), driver license numbers, and manufacturer codes. The report emphasizes the importance of cardinality and optionality in defining relationships. It then explains the normalization process, using examples to demonstrate the conversion of multi-valued attributes to atomic attributes and the elimination of partial dependencies to achieve the second normal form. The report also discusses the use of manufacturer codes to establish one-to-many relationships. Finally, it provides a bibliography of relevant database management system literature.

Running head: CONCEPTUAL DATABASE MODEL
Conceptual Model: A Case Study of Traffic Demerit System
Name of the Student
Name of the University
Author’s note:

1CONCEPTUAL DATABASE MODEL
Conceptual Entity Relationship Diagram:
Figure 1: Conceptual ERD of Traffic Demerit System
(Source: Created by Author)
The proposed ERD has been created using crow’s foot notation. There are total seven
entities in the entity relationship model. The database model describes that each vehicle is used
by a single driver and one manufacturer has developed the model. For a vehicle database stores
the vehicle identification number, model, driver who owns the vehicle, and manufacturer. As
many vehicles can be owned by a single driver and made by one manufacturer, the driver and
manufacturer codes are used in the vehicle entity. One driver can face many suspensions but one
suspension is associated with only one driver. For each demerit, the driver will get a point which
will be calculated to check if points sum achieved 12 or not. If point sum achieved 12 points,

2CONCEPTUAL DATABASE MODEL
then the database will create a new row in suspension entity for that driver. The suspension start
and end data also stored in the suspension table. One demerit is stored for each offence the driver
will commit. Each offence will be registered by an officer. The details such as name, id and rank
of the officer will be saved.
Defining the carnality and optionality is very important for the database. This is because
the cardinality and optionality decide the relation between the entities. Taken as an example, a
vehicle must be owned by a driver and a driver can have many vehicles. This imposes that a
vehicle will have many-to-one relation with the driver. The optionality will be one for each end.
This means, a driver must have a vehicle and a vehicle must be owned by a driver.
The normalization is the way of organizing the attributes in the entities so that data
anomalies can be prevented easily. The following table is created to show the normalization
process. Only two attributes from each manufacturer and vehicle has been selected so that
process can be visualized easily.
Man_code Man_name VIN Model
1023 Mazda 098645634 CX-5
298347923 CX-30
1056 BMW 213781234 BMW-i8
812378123 X5
The vehicle attributes are multivalued because each manufacturer manufactures more
than one vehicle. Now the first normal form does not support any multivalued attributes. To
normalize the table, the multivalued are converted into atomic attributes.

3CONCEPTUAL DATABASE MODEL
Man_code Man_name VIN Model
1023 Mazda 098645634 CX-5
1023 Mazda 298347923 CX-30
1056 BMW 213781234 BMW-i8
1056 BMW 812378123 X5
For the second normal form, the partial dependency has to be considered. In the above
table, the model is dependent on the vin. The model has partial dependency on the VIN which is
not a key attribute. To normalize the table, a new table is introduced called vehicle.
Man_code Man_name
1023 Mazda
1023 Mazda
1056 BMW
1056 BMW
VIN Model Man_code
098645634 CX-5 1023
298347923 CX-30 1023
213781234 BMW-i8 1056

4CONCEPTUAL DATABASE MODEL
812378123 X5 1056
The manufacturer code has been used in the vehicle table so that one-to-many
relationship can be established. For each vehicle there should be only one manufacturer number.
This indicates which manufacturer has manufactured the vehicle. The same normalization
approach is used for every entity in the database.

5CONCEPTUAL DATABASE MODEL
Bibliography:
Van Aken, Dana, Andrew Pavlo, Geoffrey J. Gordon, and Bohan Zhang. "Automatic database
management system tuning through large-scale machine learning." In Proceedings of the 2017
ACM International Conference on Management of Data, pp. 1009-1024. ACM, 2017.
Yu, Xiangyao, Yu Xia, Andrew Pavlo, Daniel Sanchez, Larry Rudolph, and Srinivas Devadas.
"Sundial: harmonizing concurrency control and caching in a distributed OLTP database
management system." Proceedings of the VLDB Endowment 11, no. 10 (2018): 1289-1302.