Planning and Creation of a Fleet Truck Maintenance Database for Huffman Trucking James Adams
Professor Troy Tuckett
March 11, 2013
Creating a Database Design for Fleet Truck Maintenance
The planning designing and creation of the Fleet Huffman Trucking Maintenance Database will identify attributes, tables and fields for all of the service categories for the Huffman maintenance fleet Trucking Company. This database will be developed and design according to the vehicle maintenance activity and repairs to keep vehicles save on the road and save money down the line on the up keep on the vehicles. The Huffman Company will use real time data information pertaining to all the work history activities in the fleet truck maintenance facility. The tables and entities describe in the fleet truck maintenance database will identify each attributes and a map between relationships that’s in the database, where each components interact with each other in a Query and SQL format. The table below will identify realistic data demonstrating each entity and attributes, regarding the relationship that each table brings to the database functions with populated information to support the table and entity.
Vehicles- a table displaying the vehicles in Huffman’s fleet.
| * Vehicle_ID (PK) * VIN * Vehicle_detail * Put_inservice_date * Purchase_invoice * Taken_outservice_date
| * Veh_detail_ID (PK) * Vehicle_ID * Mileage * Type_ID * Capacity * Class_code * Gross_weight
| Vehicle_Invoices- a table displaying invoices for all vehicles purchased.
| * Veh_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 * Bar Code
| 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 from 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
Creating the entity-relationship diagrams
The Entity Relationship Diagram (ERD) is the backbone in the design process of creating attributes and entity in Huffman Trucking database system. ERD primary position is to determine and identify the relationship of each table and establish all the attributes and entity when keeping records of the Huffman Trucking activities that’s in the database. The tables in the ERD will give a clear understanding on the position of the process when...
Please join StudyMode to read the full document