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 state table) and emergency contact (linked with the emergency table).
The emergency contact table contains emergency information for two possible contacts. Name, phone, and addresses of each contact are the fiends for this table.
The driver table contains fields concerning the driver only and has their home address, home state (linked with the state field), license number, home telephone and mobile telephone.
The berth table contains fields concerning the times and dates when the driver was in the berth and not driving. The driver can...