Assignment: Database Creation

User Generated

ezshaavrf

Programming

Description

Use the Attach file to get information. I used for the part area "many-to-many,"  My budget is very tight at the moment. Please Use one of the Following: either SQL Server Management, MySQL, or Access. The attach files below is the information need to do the assignment.

Database Creation:

Resources: The ERD and normalized tables from the Week Two individual assignment, either using SQL Server or Access.

1. Build a working sample of Huffman Trucking's fleet truck maintenance database.

2. Use the CREATE table statement to create a table for each entity for Huffman Trucking's fleet truck maintenance database.

3. Use the INSERT statement to populate the tables with realistic sample data, demonstrating each relationship in your entity relationship diagram. Include at least two entries for each table.

4. Use the SELECT statement to create the following queries: • Create a simple query for each table that returns all of the columns and all of the rows for each table.

5. Write a query that displays each part that has been purchased by Huffman Trucking Company. For each part, also retrieve its parts catalog information from the parts catalog table and vendor information from the vendor table.

6. Write a query that displays all of the rows in the vehicle maintenance table. For each vehicle maintenance row, join the corresponding information from the maintenance descriptions table and vehicles table.

7. Write a query that displays each row in the maintenance work order table. For each row in the maintenance work order table, join the corresponding information from the maintenance description table.

8. Write a query that counts the number of maintenance work orders for each vehicle in the maintenance work order table. Display the vehicle column and the corresponding count of work orders for each vehicle.

Use Reporting Services to design and create a report on the parts purchasing history for Huffman Trucking Company. The report should display all parts purchasing history including manufacturer and vendor information, parts catalog information, and associated parts inventory issues, and all parts inventory purchases. Format the report to present to senior management at Huffman Trucking.

Entities and Attributes Visio File

Entities and Atributes Excel

huffman Trucking maintenance



Unformatted Attachment Preview

Entity Relationship Diagram and Table Definition Name Richard Mikulaj DBM 502 Monday, December 15, 2014 Saqib Jamshed Field Name TypeID Description Field Name VIN ClassCode InServiceDate GrossWeight Mileage PurchasePrice AccumulatedDepreciation OutofServiceDate Capacity TypeID WorkOrderID Field Name VendorID Name Order Address Street City State Zip OrderContact OrderPhoneNumber OrderFaxNumber MANTENAN VEHICLE TYPES Data Type Length Null Number 10 N VarChar 255 Data Type Number VarChar DateTime Number Number Number VarChar DateTime Number Number Number Data Type Int VarChar VarChar VarChar VarChar VarChar VarChar VarChar VarChar VarChar VEHICLES Length Null 10 N 255 Field Name WorkOrderID Vehicle Maintenance Type ID PartID AssignmentTO DateStarted DateCompleted Hours Data Type Int Number Number Number VarChar DateTime DateTime Number Primary Key Y Foreign Key Primary Key Foreign Key Y 10 10 10 255 10 10 Y Y 10 VENDORS Length Null 10 N 255 255 255 255 255 255 255 255 255 Primary Key Y Foreign Key BillingAddress BillingStreet BillingCity BillingState BillingZip BillingContact BillingPhoneNumber BillingFaxNumber VarChar VarChar VarChar VarChar VarChar VarChar VarChar VarChar 255 255 255 255 255 255 255 255 Field Name TransactionID PurcahseDate PuchasePrice Quantity PARTS INVENTORY PURCHASES Data Type Length Null Primary Key INT 10 N Y DateTime Number 10 Number 10 VENDORS, PARTS CATALOGUE, & PARTS PURCHASING HISTORY Field Name Data Type Length Null Primary Key VendorID Number 10 TransactionID Number 10 PartID Number 10 CatalogueID Number 10 Foreign Key Foreign Key Y Y Y Y MANTENANCE_WORK_ORDER Length Null 10 N 10 10 10 255 Primary Key Y Foreign Key Y 10 Field Name VehicelPartID MaintenanceTypeID VehicleSerialNumber PutInServiceDate NextScheduleMaintenanceDate UnderWarrantFlag WORKORDERID Field Name ManufactureID Type Barcode PutInServiceDate RotationSchedule LastRotated DisposalDate VendorID MaintenanceTypeID VEHICLE MAINTENANCE Data Type Length Null Number 10 N VarChar 225 Number 25 DateTime DateTime VarChar 10 Number 10 TIRE_MAINTENANCE Data Type VarChar VarChar Number DateTime VarChar VarChar DateTime Number VarChar Length 255 255 20 Primary Key Y Null N Primary Key Y Null N Primary Key Y 255 255 10 255 MAINTENANCE DESCRIPTION Field Name Data Type Length MaintenanceTypeID Int 10 LevelCode VarChar 255 Description VarChar 255 AverageHoursRequired Number 10 DaysBetweenRecommendedMaintenance Number 10 MaximumDaysBetweenMaintenance Number 10 ViniD VarChar 20 Field Name TransactionID IssuesDate PurchasePrice Quantity Field Name PartID Description Type QuantityOnHand ReorderPoint ReorderQuantity Field Name CatalogueID TransactionID VendorID ManufacturerID QuantityPurchased DateOfPurchase Price Shipping Tax FOB PARTS INVENTORY ISSUES Data Type INT DateTime Number Number PARTS_CATALOG Data Type Int VarChar VarChar Number VarChar Number Length 10 Null N Primary Key Y Null N Primary Key Y Null N Primary Key Y 10 10 Length 10 255 255 10 255 10 PARTS PURCHASING HISTORY Data Type Length Int 10 VarChar 255 VarChar 255 VarChar 255 Number 10 DateTime Number 10 Number 10 Number 10 Number 10 Foreign Key Y Y Foreign Key Y Y Foreign Key Y Foreign Key Foreign Key Foreign Key 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
Purchase answer to see full attachment
User generated content is uploaded by users for the purposes of learning and should be used following Studypool's honor code & terms of service.

Explanation & Answer


Anonymous
Excellent resource! Really helped me get the gist of things.

Studypool
4.7
Trustpilot
4.5
Sitejabber
4.4

Related Tags