Huffman Trucking Database

Huffman Trucking Database

The driving log was broken up into 11 tables. The database is in the third normal form or Boyce Codd Normal Form (BCNF) (Pratt & Adamski, 2005, p. 153). This level of normalization ensures no repeating groups, no key column is dependent on only a portion of the primary key, and the only determinants that are contained are candidate keys. The company has four locations in the United States and services 925 drivers. Normalization to the third normal form is sufficient due to the relatively small database, the number of daily updates and the nature of the information. The database is searchable, and due to the naming methodology, is platform independent. Care was taken to omit reserved words from the major databases: Oracle, Sybase, MySQL, PostgreSQL, and DB2 (Box, 2004). An Entity Relationship Diagram for the database is shown in Figure 1.

Figure 1. Driving Log ERD

The state table was created to make a drop down list field to select a state from a list while entering data to the database. The state field is pre-populated with all the states of the union and is connected to the violations table and the driver tables.

The violations table was created to document driving violations to local and state laws. The date of the citation, nature of the offense, state of the occurrence (linked with the state table), municipality where committed, status of the citation, and the disposition and driving points deducted from the driver’s record are kept here. The restrictions field was added to allow the employer to be alerted when a driver exceeds the allowed points to their record and may be suspended from driving.

The employees table has the name, employee ID license number, state (linked with the s...

... middle of paper ...

...he exam. A restrictions field was added to enable Huffman Trucking staff to enter possible restrictions to driving that the driver may have due to the results of the exam.

The database was created to facilitate information sharing between the trucker and the company. The database will also allow truckers to update the database while on the road using the Internet. This practice will allow the company to keep near real-time updates to the status of truckers driving log and allow them to ensure compliance with local and federal laws.

References

Pratt, P. J., & Adamski, J. J. (2005). Database Design 1:Normalization. In MacMendelsohn (Ed.), Concepts of Database Management (Fifth ed., p. 153). United States of America: Thomson.

Box, D. (2004, April 29). SQL API Portability. Retrieved August 16, 2008, from http://home.fnal.gov/~dbox/SQL_API_Portability.html