Learning Team Week Final Project
DBM/380

Learning Team Week Final Project
The Huffman Trucking Company started in Cleveland, Ohio in 1936. The company is privately owned, and began as a single tractor-trailer company. This company is growing is size and acquiring rapid business growth. At present time, the company owns 800 road tractors, 2,100 45' trailers, and 260 “roll A on/roll-off" units. The Huffman Trucking provides logistics services to U.S. Government as well as to the private companies. (Huffman Trucking, 2012). Learning team D’s project is to develop database design documentation for Huffman Trucking for their fleet truck maintenance. Huffman trucking interviewed members of their staff from different departments and proposed entities and attributes for their database. Entities proposed are numerous: Parts Inventory Purchases, Parts Inventory Issues, Parts Catalogue, Vendors, Parts Purchasing History, Vehicle Maintenance, Tire Maintenance, Maintenance Descriptions, Vehicle Types, Vehicles, and Maintenance Work Order. (Apollo Group, 2007). The expected end result from Huffman trucking is documentation showing entity relationship diagrams with referential integrity, data entry forms, SQL table join query code with results, and a normalized database that observes the business rules proposed by Huffman Trucking.
Stages of Development
In the first stage of development, we took the list of entities and their attributes proposed by our client and acquainted ourselves with their business and processes. The entities proposed by Huffman Trucking were numerous: Parts Inventory Purchases, Parts Inventory Issues, Parts Catalogue, Vendors, Parts Purchasing History, Vehicle Maintenance, Tire Maintenance, Maintenance Descriptions, Vehicle Types, Vehicles, and Maintenance Work Order. Initially we organized these entities along with their attributes according to what Huffman Trucking desired to see.
In the second stage of database design, we have taken the initial design and organized it for better functionality. Many of the entities and attributes of our initial design overlapped and made our design prone to redundancy. In this stage, we have taken into account the business rules and user requirements to improve function while simplifying design (Coronel, Morris, & Rob, 2013, Chapter 9). Many changes were made during this stage: we merged many tables, removed and/or renamed attributes (many attributes did the same thing and had different names in different tables), and added attributes to tables for operational purposes (we added a part id to our table that records transactions for inventory purposes). At the end of this stage, we had turned the original database of 11 tables into a more organized layout with 7 tables that had attributes that more accurately reflected the business functions of our client.
In the third stage of database design, we took the improved design and applied normalization to it for better functionality. Many of the entities and attributes of our initial design overlapped and made our design prone to redundancy, but we addressed them during this stage of design to promote a relationship with well formed relationships. Another issue we addressed was the naming convention used for tables, entities, and attributes. Our tables and attributes were modified to conform to the standard naming convention (no spaces in table or attribute names, and table name is abbreviated at the beginning of attribute names), and changed to convey which table they are from, and what they are used for. In this stage, we have taken into account the necessity of normalization to improve function and remove redundancy (Coronel, Morris, & Rob, 2013, Chapter 9).
Normalization
The normalization has a crucial meaning for database functionality and data integrity that we refer to as good table structure. “The Normalization is a process for evaluating and correcting table structures to minimize data redundancies and reducing data abnormalities”. (Coronel, Morris, & Rob. 2013, Chapter 6). The normalization process involves the series of stages called normal forms as 1NF, 2NF, 3NF, 4NF etc. In order to apply the best possible level of normalization, the database designer should consider database system respond to end-user queries as well as avoiding chances for data redundancy and data abnormalities. Most business databases require 3NF stage. The normalization process is a step by step action when the designer starts from lower stage and move up to the next stage. Any upper stage contains the previous stage, except the lowest stage which is 1NF (Coronel, Morris, & Rob. 2013, Chapter 6). In 1NF we correctly identified all FK and PK keys, as well as removed all repeating groups from table and even merged some tables together for better database design and functionality. Each table represents data that refer to single subject and data are uniquely identifiable by PK. The next step is the 2NF where all partial dependencies were eliminated; no attribute is dependent on only a portion of the primary key. We solved this problem by creating and changing tables and providing appropriate relationships between them. Finally, the 3NF was achieved by eliminating transitive dependencies or derived attributes by the similar approach that was used in 2NF stage. Not all transitive dependencies were eliminated, for example by the zip code we can determine the city and stated. The zip code value almost never change, therefore it is unnecessary to make a database design more complex (Coronel, Morris, & Rob. 2013, Chapter 6). The detail changes were applied to database design and these changes are described below as well as SQL JOIN queries.
Huffman Trucking gave a substantial list of entities and their attributes for the proposed database. Entities proposed are numerous: Parts Inventory Purchases, Parts Inventory Issues, Parts Catalogue, Vendors, Parts Purchasing History, Vehicle Maintenance, Tire Maintenance, Maintenance Descriptions, Vehicle Types, Vehicles, and Maintenance Work Order. These entities, tables, and attributes were normalized and organized for efficiency (Kroenke & Auer, 2011, Chapter 5). The normalized entity sets are listed below with their attributes in parenthesis with the new naming conventions to remove spaces and simplify table/attribute names (primary key is underlined). The final change we made during our final stage of design was the reorganization of the transaction table (used to be inventory issues table). This table was renamed to better reflect its function and also given a stronger primary key (TRANSACTION_ID) because originally it had a composite key that was not unique enough to differentiate between transactions. * Transaction (TRANSACTION_ID, WRK_ORDER_ID (FK), ISSUE_DATE, CATALOGUE_ID (FK), PRICE, QTY_ISSUED) * PartsCatalogue (CATALOGUE_ID, PARTS_DESC, VENDOR_ID, QTY_ON_HAND, RE_ORDER_POINT, RE_ORDER_QTY) * Vendors (VENDOR_ID, VENDOR_NAME, ORDER_ADDRESS, ORDER_CITY, ORDER_STATE, ORDER_ZIP, ORDER_CONTACT, ORDER_CONTACT_PH, ORDER_CONTACT_FAX, BILLING_ADDRESS, BILLING_CITY, BILLING_STATE, BILLING_ZIP, BILLING_CONTACT, BILLING_CONTACT_PH, BILLING_CONTACT_FAX) * PurchasingHistory (ORDER_ID, CATALOGUE_ID (FK), QTY_ORDERED, VENDOR_ID (FK), SHIPPING_PRICE, TAX, FOB) * Vehicles (VIN_NUM, VEHICLE_TYPE, CLASS_CODE, INTO_SERVICE_DATE, GROSS_WEIGHT, MILEAGE, PURCHASE_PRICE, ACCUMULATED_DEPRECIATION, OUT_OF_SERVICE_DATE, CAPACITY) * MaintenanceDescriptions (MAINT_TYPE_ID, LEVEL_CODE, MAINT_DESC, HOURS_REQ, DAYS_REC_BETWEEN_MAINT, MAXIMUM_DAYS_BETWEEN_MAINT)

* WorkOrders (WRK_ORDER_ID, CATALOGUE_ID (FK), MAINT_TYPE_ID (FK), VIN_NUM (FK), WARRANTY, PART_DISPOSAL_DATE, ASSIGNED_TO, DATE_START, DATE_FINISH, NEXT_SCHED_MAINT, WRK_HOURS)

Relationship Structure
Our database design has several relationships that connect different tables. There are two relationships in the Transaction table. WRK_ORDER_ID is a FK in Inventory table to create a 1:M relationship with the WordOrders table (a work order may have many parts from the Inventory table, and a part from the Inventory table may have only one work order). CATALOGUE_ID is a FK in the Inventory table to create a 1:M relationship with the PartsCatalogue table (an Inventory entity may have one part from the Catalogue table, and a Catalogue part may belong to many Inventory entities). There are two relationships in the PurchasingHistory table. CATALOGUE_ID is a FK in the PurchasingHistory table to create a M:M relationship with PartsCatalogue table (an entity from PurchasingHistory may have many parts from Catalogue, and a part from Catalogue may belong to many entities from PurchasingHistory). VENDOR_ID is also a FK in the PurchasingHistory table to create a 1:M relationship with the Vendors table (a parts purchase may belong to one Vendor, and a Vendor may belong to many parts purchases). There are three relationships in the Maintenance Work Order table. VIN is a FK in the WorkOrder table to create a 1:M relationship with the Vehicle table (a maintenance work order can have on VIN_NUM, and a VIN_NUM can have multiple maintenance work orders). CATALOGUE_ID is a FK in the WorkOrder table that creates a 1:M relationship with the PartsCatalogue table (a maintenance work order may have one part from the Parts Catalogue, and a part from the Parts Catalogue may belong to many maintenance work orders). The MAINT_TYPE_ID attribute is a FK in the WorkOrder table to create a 1:M relationship with the MaintenanceType table (a maintenance work order can have only one type from the MaintenanceType table, and a Maintenance Type can belong to many maintenance work orders). These relationships connect our tables together to create the relationships in our database.

Join Query
Our Join Query was simple and straight forward. We chose to join the Transaction table (TRANSACTION_ID), the WorkOrder table (WRK_ORDER_ID, CATALOGUE_ID, MAINT_TYPE_ID, VIN_NUM), MaintenanceDescriptions table (MAINT_DESC), PartsCatalogue table (PARTS_DESC), and Vehicle table (CLASS_CODE, VEHICLE_TYPE). This join shows the Transaction ID, Work Order, Catalogue Part, Maintenance Type ID, Parts Description, Maintenance Description, VIN, Vehicle Class Code, and Vehicle Type side by side. This specific join helps to confirm that the right parts were used for the type of vehicle (class 3 vehicle repaired with class 3 parts) and that the parts match the maintenance type (brakes were used for brake maintenance). This is mainly for inventory purposes for ensuring that the correct part number was entered by the mechanic workers or other staff when maintenance was performed. Join Functions are important for many business operations and data discovery.

The code that we used for performing this join is as follows:
SELECT Transaction.TRANSACTION_ID, Transaction.WRK_ORDER_ID, WorkOrders.CATALOGUE_ID, WorkOrders.MAINT_TYPE_ID, PartsCatalogue.PARTS_DESC, MaintenanceDescriptions.MAINT_DESC, WorkOrders.VIN, Vehicles.CLASS_CODE, Vehicles.VEHICLE_TYPE

FROM (PartsCatalogue INNER JOIN [Transaction] ON PartsCatalogue.CATALOGUE_ID=Transaction.CATALOGUE_ID) INNER JOIN (Vehicles INNER JOIN (MaintenanceDescriptions INNER JOIN WorkOrders ON MaintenanceDescriptions.MAINT_TYPE_ID=WorkOrders.MAINT_TYPE_ID) ON Vehicles.VIN_NUM=WorkOrders.VIN) ON PartsCatalogue.CATALOGUE_ID=WorkOrders.CATALOGUE_ID;

Entry Forms
Our final changes to our normalize database was to add entry forms to make it more user friendly. Two common functions that Huffman Trucking performs with their database on a regular basis are work order form data entry, and new part data entry. The two forms we created accommodate these business functions to make business operations more user friendly, accurate, and efficient. The first form was created for work order form data entry, and includes all the fields for the table: Work Order ID, Catalogue ID, Maintenance Type ID, Vehicle Vin, Under Warranty Selection, Parts Disposal Date (optional), Mechanic the work order was assigned to, the Start Date, the Finish Date, the Next Scheduled Maintenance, and the Hours Worked. The form used for new part data entry is similar, and contains all the fields for the parts catalogue table: Catalogue ID, Part Descriptions, Vendor ID, Quantity on Hand, Re-Order Point, and Re-Order Quantity. These forms make data entry much easier for end users that are not acquainted with data entry for databases.

Conclusion
Throughout the weeks we have made many changes to the initial design. In our final design stage, our reordered design was well organized, and addressed the issues of redundancy in the original database design. Normalizing these components has produced a more efficient and functional database. The first issue we addressed in our database design was the redundancy among the tables originally proposed by Huffman Trucking. This was done by merging tables to create a more organized database which resulted in downsizing the database layout from 11 to 7 tables with unique and well organized attributes. These issues caused major redundancy in our design, and our re-organization has promoted accuracy and functionality. After these issues were addressed, we focused on placing our table layout into well-formed and normalized relationships. After the tables were normalized, they each addressed only one subject, and did not have any partial dependencies. Our normalized design is functional and prevents the redundancies that the original non-normalized design created. Once changes are made during this phase, was to cut out the transaction table (originally Inventory Issues Table); we added a unique primary key in place of the weak composite key that made it hard to distinguish the difference between different transactions. Our final changes were to change our table and attribute names to conform to the standard naming convention for databases by using short, meaningful names that did not use spaces. Our final database design fulfills all the original needs of the clients while preventing the problems and inefficiencies that the original design created. This successful design is normalized, organized, user friendly and has the necessary business operations that work efficiently and accurately.

References
Coronel, C., Morris, S., & Rob, P. (2013). Database Systems Design, Implementation, and
Management (10th ed.). Retrieved from The University of Phoenix eBook Collection database. Kroenke, D. M., & Auer, D. J. (2011). Database Concepts (5th ed.). Retrieved from The
University of Phoenix eBook Collection database.
Apollo Group. (2007). Entities and Attributes for Fleet Truck Maintenance. Retrieved from https://ecampus.phoenix.edu/secure/aapd/cist/vop/Business/Huffman/IT/Databases/HuffmanDatabasesEandAFleetTruckMaintenanceRev.pdf
Huffman Trucking. (2012). Retrieved from
https://ecampus.phoenix.edu/secure/aapd/CIST/VOP/Business/Huffman//internet/index.asp