Entities and Attributes for Fleet Truck Maintenance

At the request of the Huffman Trucking Maintenance Department, Smith Consulting developed entities and attributes for their Fleet Trucking Maintenance database. Unfortunately, the creator of the database was not available and the development of the database system was not able to be completed. D Team realizes that the development of a reliable database system is needed to provide for the tracking of scheduled and unscheduled maintenance for their fleet. Therefore, a database along with forms, tables, queries, and reports that tracks information gathered by maintenance records, inventory of parts and purchases from vendors is proposed.

Huffman Trucking desires to advance and make certain that maintenance is performed and tracked for the entire fleet of trucks across all of the companies other locations. In an effort to better document our applications, Entity-Relationship Diagrams are needed. The expected results should convey and deliver the creation of Entity-Relationship Diagrams for Fleet Truck Maintenance.

The project will minimize down time for vehicles in their fleet by ensuring parts are on hand to perform the maintenance. Having these needs met with minimize potential revenue lost due to unavailable vehicles. The goal of the project is to create the entity relationship diagrams that will support the creation of a shared database that can be accessed by all of Huffman Trucking various locations.

The team is assuming that Smith Consulting accurately interpreted all of the entities. Secondly, it is being assumed that Huffman has the necessary infrastructure or the means to put into operation the appropriate infrastructure, hardware, software, and licensing. Therefore, assuming these components will not limit or influence the ERD. In a real life situation, one always has some working assumptions that one needs to document and clarify with the client.

In order to ensure a successful ERD, several tasks will need to be executed. One of the constraints we are presented with is the potential lack of documentation. One way to overcome this constraint will be to interview subject matter experts and the key players involved in the maintenance process. The first requirement will be to draft an Entity-Relationship Diagram (ERD) based upon the business rules and the entities, which were captured by Smith Consulting. All of Huffman’s hubs will use the database and with that, will need to be robust enough to handle the volume. In addition, data anomalies such as redundancies must be eliminated by normalizing the tables. One of the challenges will be to find a balance between normalization and performance. Team D understands the challenges involved in a high-performance transactional situation such as this one. The second requirement will be to construct an ERD in Microsoft Access.

As with any data design project, many business rules and impacts will have to be considered. The vehicle maintenance nature of this system will require that an inventory be kept that can reflect the status of parts available for maintaining the fleets vehicles. Scheduling maintenance for the fleet of vehicles also becomes important in ensuring that the fleet is properly maintained. Regular maintenance is critical in extending the useful life of the vehicles to maximize the return on capital investment. In order to effectively maintain the fleet, it becomes imperative that Huffman Trucking have sufficient parts in inventory so that maintenance is not deferred while parts are being acquired. A well designed maintenance system will ensure that inventory is sufficient for upcoming nominal maintenance, vehicles in the fleet are maintained when necessary, and additional inventory is on hand for unscheduled maintenance. Certain business rules will need to be implemented in the database to maintain the integrity of the system. Parts removed from inventory to be used in servicing a vehicle must be reflected accurately in the database. This becomes the basis of triggering to order new parts in maintaining necessary levels of inventory. The coming maintenance of a vehicle should also trigger an event to ensure that the parts anticipated to be needed for that maintenance are on hand. If the anticipated parts are not in inventory, then an order should be recommended by report or automatically initiated.

Based upon the original content provided by Smith consulting, a database design was obtained by first analyzing the key subjects, entities, and attributes. The key subjects are the following: Parts Inventory Purchases, Parts Inventory Issues, Parts Catalogue, Vendors, Parts Purchasing History, Vehicle Maintenance, Tire Maintenance, Maintenance Descriptions, Vehicle Types, Vehicles, and Maintenance Work Orders along with several entities to support each of the subjects. The tables were then normalized to third normal form (3NF). Accordingly, each of the tables will be joined by a series of primary and foreign keys thereby creating relationships among them. This approach will allow the design to be broken into smaller pieces, thus focusing on one entity at a time. Eventually, these smaller designs were merged into a cumulative design for the entire database.

If this project is not implemented, it will not be possible to automatically share data for each of the trucking hubs. Parts ordering and settlement will continue to be done in an inefficient and decentralized way. Parts inventory will not be able to be shared between hubs and maintenance schedules will be inconsistent between hubs. There will be several opportunities that will grow from this project. In the future, analysis of this data will lead to even more improvements in the maintenance program here at Huffman.

Relational Database Rationale

The entity relationship diagram our D Team has constructed is for a relational database. Relational databases are composed of multiple tables that contain both fields and attributes which share a specific relationship with one another. These relationships are sometimes referred to as parent-child relationships. The way it works is the primary key for one table will be used as the foreign key in a relating table thus accounting for the relationship. The table containing the primary key is known as the parent table and the opposite table housing the foreign key is the child table. Two other types of databases that we have discussed are flat file databases and data warehouses. A flat file database is made up of only one table which contains all the data gathered in separate fields on that one table. Flat file databases can be equated with spreadsheets like the type found in MS Excel. Formulas can be computed and reports can be generated, but data entry will be restricted to that one table. Like relational