Database and DAO classes description

Database description

The database in this application can be divided into 3 parts, which are used for providing different functionalities. The main part is used to store information about users and their orders. The second part can be named as area part, which stores GeoJSON Polygon and MultiPolygon objects and is used for storing needed geographical areas, such as city centers. The third part stores different key-value pairs, such as gasoline price. Below is provided ER and relational diagrams of the database parts.

The main part ER diagram

The area part ER diagram

The data key-value part ER diagram

The main part relational schema

The area part relational schema

The data key-value part relational schema

DAO and model classes description

The DAO classes of the application provide functionality for manipulating data between software and database. Almost all the DAO classes have at least 5 methods, which are create, read, readAll, update and delete. Some of the classes may have additional methods createMultiple or search. These methods can be described as follow:

1. create add new row to the SQL table.
2. read get row by primary key from the SQL table.
3. readAll get all rows from the SQL table.
4. update change row by primary key in the SQL table.
5. delete remove row from the SQL table by primary key.
6. createMultiple add multiple rows to the SQL table.
7. search find rows with provided column values in the SQL table. For manipulating this data on the software side model classes have been made and each DAO class uses a corresponding model class. Below are described each DAO class and its model class in detail.

Address

The AddressDAO class manipulates the Address SQL table, which contains address information. This table is related to the Client and Manufacturer tables as many-to-many and to the Order table as one-to-many. Below is a list of Address model class fields:

1. addressId id of the address in the table, primary key, integer
2. street address street name, string
3. building building number with possible letter indexes, string
4. flat flat number, integer
5. city city name, string
6. lat latitude coordinate, double
7. lon longitude coordinate, double

Client

The ClientDAO class manipulates the Client SQL table, which contains username and name of the client. The client is a person, who orders items from the Manufacturer, who provides them. The Client table is related to the Address table as many-to-many and to the Order table as one-to-many. Below are listed Client model class fields:

1. clientUsername username of the client, primary key, string
2. name name of the client, string

Manufacturer

The ManufacturerDAO class manipulates the Manufacturer SQL table, which contains username and name of the manufacturer. The manufacturer is a person, who provides items to the Client, who orders them. The Manufacturer table is related to the Address table as many-to-many and to the Order table as one-to-many. Below are listed Manufacturer model class fields:

1. manufacturerUsername username of the manufacturer, primary key, string
2. name name of the manufacturer, string

Order

The OrderDAO class manipulates the OrderData SQL table, which contains information related to the orders. This information stands for from-to, from which manufacturer to which client and from what address to what address. The Order SQL table is one of the most used tables in the application since the routing is made based on the chosen orders. The Order table is related to the Client, Manufacturer and Address tables as many-to-one. Below is a list of Order model class fields:

1. orderId id of the order, primary key, integer
2. manufacturerUsername username of the manufacturer, string
3. clientUsername username of the client, string
4. shipmentAddressId id number of the shipment address or manufacturer address id
5. deliveryAddressId id number of the delivery address or client address id

Area

The AreaDAO class manipulates the Area SQL table, which contains area information. The Area and AreaCoordinates tables together store GeoJSON polygon and multipolygon objects. This table is related to the AreaCoordinates table as one-to-many. Below is a list of Area model class fields:

1. areaName name of the area, primary key, string
2. type type of GeoJSON object Polygon or MultiPolygon only, string

AreaCoordinates

The AreaCoordinatesDAO manipulates the AreaCoordinates table. The Area and AreaCoordinates tables together store GeoJSON polygon and multipolygon objects. The AreaCoordinates table stores coordinates of these objects. The AreaCoordinates table is related to the Area table as many-to-one. Below is a list of AreaCoordinates model class:

1. coordinateId id of the AreaCoordinate in the table, primary key, integer
2. orderNumber the order number of the coordinate in the array, integer
3. polygonNumber polygon number, where coordinate belongs to, used for MultiPolygons, for Polygons its value is 0, integer
4. lat latitude coordinate, double
5. lon longitude coordinate, double

TMS(traffic measurement station)

The TMSDAO class manipulates the TMS table data. Each TMS or traffic measurement station has coordinates and sensors. In the application for providing taking into account the traffic situation, average speed sensors of the TMSs are in use.The TMS and Area tables are related since each station represents the area on the road with the real time average speed. On the server set up, all TMSs with speed sensors are added to the TMS table and also to the Area table with areaName in form tms + stationId. Below is listed TMS model class fields:

1. stationId id of the station, primary key, integer
2. sensor1Id sensor id, pointed to direction 1, integer
3. sensor2Id sensor id, pointed to direction 2, integer
4. lat latitude coordinate, double
5. lon longitude coordinate, double

Data

The DataDAO class manipulates the Data SQL table. The Data table contains information in form key-value or name-value pairs with the last updated date. An example of such a name-value pair may be gasoline price. The Data SQL table is not related to any other table. Below is listed Data model class fields:

1. name name of the pair, primary key, string
2. value value of the pair, string
3. lastUpdated last updated date, updates automatically on row update, datetime