Planning and Creation of a Fleet Truck Maintenance Database for Huffman Trucking In creating a database, the most vital steps are identifying the table elements. Upon analysis of the Entities and Attributes for Fleet Truck Maintenance form, the elements of the database tables can be identified. In a table, or relation, the entities and attributes of a table define the structure of a database. An entity is an object of importance about which data can be captured. An attribute is the information that describes the entity. When creating the tables for the Huffman Trucking Fleet Truck Maintenance, entities and their relationships must be taken into consideration (Koenke & Auer, 2010).
Selection of Entities and Attributes In the Entities and Attributes for Fleet Truck Maintenance document, the entities and their attributes are clearly defined. The entities are listed in all capital letters. The entity’s respective attributes are listed below the entity. For example, for the entity “Parts_Inventory_Issues”, the attributes listed in the document are: * Transaction ID * Issue Date * Purchase Price * Quantity
In creating the table, the attributes must be examined to ensure that the table is organized logically and contains no chances of redundancy. Because of this, the entities and attributes used in the actual database will be modified from the attributes listed in the document. Repeating attributes will be deleted or reorganized to ensure logical structure. Each table will contain a primary key. The creation of a table will have an added attribute; it will be the table’s primary key. For instance, in the table “Part_Issues”, the primary key will be “Issue_ID.” The attributes “Purchase Price” and “Quantity” will be deleted since that data is present in another table and does not directly relate to part issues. The attribute “Description” will be added in order to be able to document the nature of the part issue. Additionally, the rest of the tables will also be assigned a primary key so that each table has a unique identifier. The chart below shows each table name and its corresponding attributes. Entity | Attributes | Vehicles- The table will display the inventory vehicles that are at Huffmans fleet. | * Vehicle_ID (PK) * VIN * Vehicle_detail * Put_inservice_date * Purchase_invoice * Taken_outservice_date | Vehicle_detail- Will display the specific vehicle information | * Veh_detail_ID (PK) * Vehicle_ID * Mileage * Type_ID * Capacity * Class_code * Gross_weight | Vehicle_Invoices- The table will display the invoices for the vehicles that are purchased. | * Vehicle_Invoice_ID (PK) * Vehicle_ID * Date_Purchased * Price * Shipping * Tax * FOB | Vehicle_Type- a table displaying the types of vehicles. | * Type_ID (PK) * Description | Maintenance_Descriptions- a table with general details of each maintnenace type | * Maintenance_Type_ID (PK) * Level_code * Description * Avg_hours_req * Days_between_rec_maint * Max_days_between_maint | Tire_Maintenance- a table displaying the maintenance record of vehicle tires. | * Tire_Maint_rec_ID (PK) * Part_ID * Vehicle_type * Manufacturer_ID * Put_inservice_date * Rotation_schedule * Last_rotated * Disposal_date | Maintenance_work_order- a table displaying all maintenance work orders for fleet vehicles. | * Work_order_ID (PK) * Vehicle_ID * Maintenance_Type_ID * Vehicle_Part_ID * Assignment_To * Date_Started * Date_complete * Hours | Vehicle_Maintenace_Record- a table displaying the maintenance recorded for each flee vehicle. | * Veh_Maintenance_rec_ID (PK) * Vehicle_ID * Work_orders * Next_scheduled_maint_date * Under_warranty_flag | Parts_Catalog- a table displaying the vehicle parts used in maintenance. | * Part_ID (PK) * Part_Type * Manufacturer * Vendor | Part_Type- a table displaying descriptions of each part type. | * Part_Type_ID (PK) * Description | Part_Invoices- a table displaying invoice detail for each part. | * Part_Invoice_ID (PK) * Date_Purchased * Quantity_Purchased * Order_Quantity * Price * Shipping * Tax * FOB | Part_Issues- a table displaying parts that have been taken out of inventory and why. | * Issue_ID * Part_ID * Issue_Date * Issue_Description | Vendors- a table with information about vendors. | * Vendor_ID (PK) * Vendor_name * Order_Address_ID * Billing_Address_ID * Phone * Fax | Vendor_Addresses- a table displaying vendor addresses. | * Address_ID (PK) * Vendor * Street * City * State * Zip | Chart 1. The entities and attributes chosen for the database are displayed. Creation of the Entity Relationship Diagram The next step in creating the database for the Entities and Attributes for Fleet Truck Maintenance document is to establish the relationships within the database. Relationships between tables can be illustrated in an Entity Relationship Model or ERD. In an ERD, the tables and their attributes are shown and their relationships are demonstrated using lines. In an ERD, there are two main types of relationships (Kroenke & Auer, 2010): 0 Relationship Classes- associations among entity classes 1 Relationship Instances- Associations among entity instances
Their Binary Types can further organize relationships: One-to-One relationships, One-to-Many relationships, and Many-to-Many relationships. The traditional model standards for ERDs call for diamonds representing relationships and rectangles representing classes. However, ERDs are loosely standardized and often look different from one another due to software such as Microsoft Access and MySql Workbench that create ERDs using proprietary applications. When creating the ERD for the Entities and Attributes for Fleet Truck Maintenance form, the relationships and their types must be established. The entities have been established in the chart above. The entities will be organized into tables with their respective attributes displayed below the name of the table. The top attribute will be assigned as a primary key in order to ensure each table has a unique identifier. These primary keys will also serve as foreign keys when inserted into other tables within the ERD. In order to display the relationships, lines will be drawn between each table in the ERD. MySQL Workbech, for instance, creates relationships in each table by having a dotted or solid line connect each table to its relation. MySQL Workbench uses Crows Foot Notation in its diagram creation application. Crows Foot Notation allows for relationships in an ERD to be displayed by their binary type. The types of Crow’s Foot Notation’s and their respective Binary Types are displayed below:

Figure 1. This diagram illustrates the binary types and thier respective Crow's Foot Notation (Interpreting Entity-Relationship Diagrams, n.d.).

In order to create the ERD with the entities and attributes listed in Chart 1, their relationships must be established. For instance, the table “Vehicles” is directly related to the table “Maintenance_Work_Order” which contains the records of maintenance performed on Huffman’s vehicles. The relationship is one-to-many since a single vehicle can have many work orders. The ERD below details each table and its respective relationships along with Crow’s Feet Notation to show binary type. Figure 2. The Huffman Fleet Truck Maintenance ERD shows entity relationships in binary type.

In order to determine the relationships from the ERD, the Crows Feet Notation can be used. As stated before, the “Vehicles” table is related to the “Maintenance_Work_Order” table. The line connects these two tables. The two dashes representing one vehicle can be seen on the line beginning from the “Vehicles” table. The line leads to the Maintenance_Work_Order” and ends with a Crow’s Foot. The specific attributes related are shown as well. In order to properly relate the tables, the primary key from “Vehicles” will be inserted into the table “Miantenance_Work_Order.” Once the primary key is inserted, the “Maintenance_Work_Order” table contains the foreign key “Vehicle_ID” in its table. This foreign key demonstrates the relationship between “Vehicles” and “Maintenance_Work_Order”: one car can have many work orders. It is therefore a one-to-many relationship. The other relationships within the ERD are created similarly.
Database Normalization In databases, the data must be stored logically and efficiently. An entity relationship diagram, or ERD, helps database designers organize the entities in order to logically store the data within the database. The current database Huffman Fleet Truck Maintenance must be normalized from its initial form. In its initial form, the entities and attributes listed in the Entities and Attributes for Fleet Truck Maintenance were at times redundant and unnecessary (Kroenke & Auer, 2010). Creating a database with the entities and attributes listed may have created anomalies within it. Anomalies are inaccuracies within the database that can result from deletion, insertion, or update of data. Normalization ensures that the database contains little or no anomalies. In order to normalize the database, the major types of Normalization should be followed (Stephens, 2009): * First Normal Form (1NF) * Second Normal Form (2NF) * Third Normal Form (3NF) * Boyce-Codd Normal Form (BCNF) * Fourth Normal Form (4NF) * Fifth Normal Form (5NF) * Domain/Key Normal Form (DKNF)
Many databases can be normalized to the Third Normal Form and be free of anomalies. In the case of Huffman’s Fleet Truck Maintenance Database, normalization can stop at Third Normal Form since that level of normalization has eliminated the most anomalies. The database displayed in the ERD is normalized in First Normal Form. Each column has a unique name and a single data type. No two rows within an entity contain identical values. Each column in the database contains a single value and there are no repeating groups within any of the columns. For instance, in the original Entities and Attributes for Fleet Truck Maintenance document of entities and attributes, many of the attributes appeared multiple times in different tables. In order to normalize the database, many of these extra occurrences were deleted. “Transaction_ID” was one of the attributes that appeared multiple times within the database. Through normalization, it was found that most of them were not needed and would only cause anomalies if they were left alone. The ERD follows all the rules of the First Normal Form so it is normalized in 1NF. The database is also normalized in Second Normal Form. The simplest requirement of Second Normal Form is that the database adheres to First Normal Form. Since it has already been established that the above database adheres to the First Normal Form, the only requirement left is that all the non-key fields depend on all of the key fields. The original document Entities and Attributes for Fleet Truck Maintenance almost all of the tables contained attributes that did not depend on the key field. At times, the tables were organized so that several attributes appeared. For example, in the “Vehicles” table, there were far too many attributes. Several attributes that did not depend on the key field were deleted or placed into a new table. The new table is entitled “Vehicle_Detail” and contains attributes about weight, mileage, and capacity while the “Vehicle” table contains vehicle data such as invoice number, date entered into service, and VIN. These tables were then connected through relationships using the primary keys and foreign keys. The other tables were normalized in this manner as well to ensure that the database meets Second Normal Form standards.
Finally, the database is in Third Normal Form. It meets the first requirement of Second Normal Form and also meets the second requirement of Third Normal Form: the database must not contain any transitive dependencies (Stephens, 2009). Every column depends on the primary key. In the “Vehicles” table, all the attributes depend on the “Vehicle_ID” key. There are no columns of data that do not belong. The same is done with the other tables. This means that the database will contain no anomalies due to modification of data.
Structured Query Language and Test Data Once the database is normalized, data is ready to be entered into the tables. This is done with the help of Structured Query Language, or SQL. SQL is a programming language used to manage data in relational databases such as Huffman Fleet Truck Maintenance (Kroenke & Auer, 2010). It can enter, update, or delete data using commands called queries. A database can be created using queries. First, a CREATE DATABASE query is used to create the database. The query written in order to create the Huffman Fleet Truck Maintenance Database MySQL is as follows: delimiter $$ CREATE DATABASE `huffman database` /*!40100 DEFAULT CHARACTER SET latin1 */$$

The query above, when inserted into MySQL, will create the database without any tables present. In order to add the tables, additional CREATE queries must be used. For instance, in order to create the “Vendors” table, the following query is input into MySQL: delimiter $$ CREATE TABLE `vendors` ( `Vendor_ID` int(11) unsigned NOT NULL, `Vendor_name` varchar(20) NOT NULL DEFAULT '', `Order_address_ID` int(11) unsigned NOT NULL, `Billing_address_ID` int(11) unsigned NOT NULL, `Phone` varchar(17) NOT NULL DEFAULT '', `Fax` varchar(17) NOT NULL DEFAULT '', PRIMARY KEY (`Vendor_ID`), KEY `Order_address_ID` (`Order_address_ID`), KEY `Billing_address_ID` (`Billing_address_ID`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1$$ The same is done with the rest of the tables. The additional queries needed to create the table are in the notepad file Huffman Fleet Truck Data Query Database attached with this document. The next step in creation of the database is adding the relationships between the tables. Each table must have a foreign key constraint added in order to create a relationship. In order to do this, input ALTER TABLE followed by the table name. The command ADD CONSTRAINTS is then input as well as seen in the example below:
ALTER TABLE vendors
ADD CONSTRAINT `vendors_ibfk_1` FOREIGN KEY (`Order_address_ID`) REFERENCES `vendor_addresses` (`Address_ID`)
ON DELETE CASCADE ON UPDATE CASCADE;

The ALTER TABLE command above inserts the foreign key into the “Vendors” table thus creating a relationship between “Vendors” and “Vendor Addresses.”
Some of the queries most commonly used are INSERT, SELECT, CREATE, DELETE, and UPDATE (Erack Network, 2008). There are many SQL clients that can help create use these queries to manage data in the database. For example, in order to insert maintenance types into the “Maintenance_Descriptions” table, the following query would be used: UPDATE `huffman database`.`maintenance_description` SET `Maintenance_Type_ID`=1, `Level_code`='Routine', `Description`='Preventive Maintenance', `Avg_hours_req`=8, `Days_between_rec_maint`=1, `Max_days_between_maint`=1 WHERE `Maintenance_Type_ID`='101'; UPDATE `huffman database`.`maintenance_description` SET `Maintenance_Type_ID`=2, `Level_code`='Moderate', `Description`='Fixing of some components', `Avg_hours_req`=24, `Days_between_rec_maint`=3, `Max_days_between_maint`=5 WHERE `Maintenance_Type_ID`='102'; UPDATE `huffman database`.`maintenance_description` SET `Maintenance_Type_ID`=3, `Level_code`='Severe', `Description`='Required replacement of components in order to function', `Avg_hours_req`=48, `Days_between_rec_maint`=5, `Max_days_between_maint`=10 WHERE `Maintenance_Type_ID`='103'; DELETE FROM `huffman database`.`maintenance_description` WHERE `Maintenance_Type_ID`='104';

Figure 3. The Maintenance Description table data inserted with UPDATE queries is present in the table. This query is used in MySQL to set the Maintenance types for the Huffman Fleet Truck Maintenance database. The query also contains an example of the DELETE command that is used to delete data from a table. In this instance, the number 104 was deleted from the “Maintenance_Type_ID” field. The update query is used to update data already within the table. The UPDATE command is very strong, therefore it is wise to use a WHERE condition within the query to ensure that only the targeted data is changed as is seen in the example above. In the “Vehicle_types” table, data concerning the type of vehicle in the fleet is stored. Vehicle types such as large cargo truck, medium cargo truck, and pickup truck are stored. The number of tires or wheels is also listed in the description of this table. The UPDATE commands to change this table are: INSERT INTO `huffman database`.`vehicle_type` (`Type_ID`, `Description`) VALUES (301, 'Large cargo truck, 18 wheels'); INSERT INTO `huffman database`.`vehicle_type` (`Type_ID`, `Description`) VALUES (302, 'Large cargo truck, 16 wheels'); INSERT INTO `huffman database`.`vehicle_type` (`Type_ID`, `Description`) VALUES (303, 'Medium Cargo Truck, 10 wheels, Mack type'); INSERT INTO `huffman database`.`vehicle_type` (`Type_ID`, `Description`) VALUES (304, 'Large Pickup truck, 6 wheels'); INSERT INTO `huffman database`.`vehicle_type` (`Type_ID`, `Description`) VALUES (305, 'Medium Pickup truck, 4 wheels'
Commands for the other tables are built similarly; with the INSERT INTO command followed by the table name followed by the column names. The VALUES section then holds the values being inserted into the table. These commands are useful in creating the data within each table of the database. Figure 4. The data in MySQL is present within the vehicle type table after being inserted via query commands.

Conclusion The creation of the Huffman Fleet Truck Maintenance Database has many steps. This is to ensure that the final database is an accurate, logical, and normalized database with as few anomalies as possible. The entities and attributes were first chosen. The Entity Relationship Diagram was then built. Finally, the ERD was normalized and the queries written. Together, these components make up the final database for Huffman Trucking.

References
Erack Network. (2008) Retrieved June 7, 2012 from http://www.tizag.com/sqlTutorial/sqlqueries.php
Interpreting entity-relationship diagrams. (n.d.). Retrieved from http://www.philblock.info/hitkb/i/interpreting_entity-relationship_diagrams.html
Kroenke, D. M., & Auer, D. J. (2010). Database concepts (4th ed.). Upper Saddle River, NJ: Pearson/Prentice Hall.
Stephens, R. (2009). Beginning database design solutions. Indianapolis, IN: Wiley.