MySql/SQL - Fleet Truck Maintenance.

Entities and Attributes for Fleet Truck
Maintenance
This document was created by Smith Systems Consulting at the request of Huffman
Trucking Maintenance Department. The descriptions provided are based on interviews
with maintenance personnel who use the Fleet Truck Maintenance database. The creator
of the database was not available.


PARTS INVENTORY Purchases
This table represents truck maintenance parts that have been purchased.

Transaction ID
Purchase Date
Purchase Price
Quantity


PARTS INVENTORY ISSUES
This table represents parts that have been taken out of inventory.

Transaction ID
Issue Date
Purchase Price
Quantity


PARTS CATALOGUE
The data in this catalogue are all parts used in the truck maintenance operation of
Huffman Trucking.

ID
Description
Type
Manufacturer
Quantity On Hand
Reorder Point
Reorder Quantity

VENDORS
These are the approved vendors which can be used to supply parts to the Huffman Truck
Maintenance operation.
Vendor ID
Name
Order address
Street
City
State
Zip
Order Contact
Order Phone Number
Order Fax Number
Billing Address
Street
City
State
Zip
Billing Contact
Billing Phone Number
Billing Fax Number


Parts Purchasing History
This data represents the history of parts purchased for the Huffman Trucking Truck
Maintenance operation.

Transaction ID
Catalogue ID
Quantity Purchased
Manufacturer ID
Order Quantity
Price
Shipping
Tax
FOB


VEHICLE MAINTENANCE
This table contains all of the maintenance performed on each vehicle.

Vehicle Serial Number
Vehicle Part ID
Maintenance Type ID
Put in Service Date
Last Maintenance Date
Next Scheduled Maintenance Date
Under Warranty Flag
Tire Maintenance
This table contains all of the maintenance performed on each tire on each vehicle.

Barcode
Type
Vehicle Type
Manufacturer ID
Put in Service Date
Rotation Schedule
Last Rotated
Disposal Date


MAINTENANCE DESCRIPTIONS
This data contains a description of the all maintenance operations performed by the
Huffman Trucking maintenance operation.

Maintenance Type ID
Level Code
Description
Average Hours Required
Days Between Recommended Maintenance
Maximum Days Between Maintenance


VEHICLE TYPES
This is a description of all vehicle types maintained by the Huffman Trucking
maintenance operation.

Type ID
Description

VEHICLES
Detailed data on each vehicle for Huffman Trucking is in this table.

VIN
Type ID
Class Code
Put into service Date
Gross Weight
Mileage
Purchase Price
Accumulated Depreciation
Taken out of service Date
Capacity

MAINTENANCE WORK ORDER
This table contains the details of each work order performed by the Huffman Trucking
maintenance operation.

Work Order ID
Vehicle
Part ID
Maintenance Type ID
Assignment TO
Date Started
Date Completed
Hours