Reference no: EM13770116

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

CREATE TABLES

Create table Parts_Inventory_Issues
CREATE TABLE Parts_Inventory_Issues(Transaction_ID number(10), Part_ID number(10), Issue_Date date, Purchase_Price number(10,2), Quantity number(10),
PRIMARY KEY(Transaction_ID));

-- Create Table Parts_Catalogue
CREATE TABLE Parts_Catalogue(Part_ID number(10), Description varchar2(20), Type varchar2(20), Manufacturer_name varchar2(20), Quantity_On_Hand
number(10), Reorder_Point varchar2(20), Reorder_Qty number(10), PRIMARY KEY(Part_ID));

-- Create table Parts_Inventory_Purchases
CREATE TABLE Parts_Inventory_Purchases(Transaction_ID number(10), Part_ID number(10), Purchase_Date date, Purchase_Price number(10,2), Quantity
number(10), PRIMARY KEY(Transaction_ID));

-- Create table Vehicle_Types
CREATE TABLE Vehicle_Types(Type_ID number(10), Description varchar2(20), PRIMARY KEY(Type_ID));

-- Create table Vehicles
CREATE TABLE Vehicles (VIN number(10), Type_ID number(10), Vehicle_Serial_Number varchar2(20), Class_Code number(10), Put_into_service_Date date,
Gross_Weight number(10), Mileage number(10), Purchase_Price number(10,3), Accumulated_Depreciation varchar2(20), Takenout_serviceDate date, Capacity
number(10), PRIMARY KEY(VIN));

-- Create table Vendors
CREATE TABLE Vendors(Vendor_ID number(10), Name varchar2(20), Order_address varchar2(20), Street varchar2(20), City varchar2(20), State varchar2(20), Zip
number(6), Order_Contact varchar2(20), Order_Phone_Number varchar2(20), Order_Fax_Number varchar2(20), BAddress varchar2(20), BStreet varchar2(20), BCity
varchar2(20), BState varchar2(20), BZip number(6), Billing_Contact varchar2(20), Billing_Phone_Number varchar2(20), Billing_Fax_Number varchar2(20),
PRIMARY KEY(Vendor_ID));

-- Create table Parts_Purchase_History
CREATE TABLE Parts_Purchasing_History(Transaction_ID number(10), Catalogue_ID number(10), Vendor_ID number(10), Qty_Purchased number(10), Manufacturer_ID
number(10), Order_Qty number(10), Price number(10,2), Shipping number(10,2), Tax number(10,2), FOB varchar2(20), PRIMARY KEY(Transaction_ID));

-- Create table Maintenance_Work_Order
CREATE TABLE Maintenance_Work_Order(Work_Order_ID number(10), Vehicle_ID varchar2(20), Part_ID number(10), Maintenance_Type_ID number(10), Assignment_TO
varchar2(20), Date_Started date, Date_Completed date, Hours number(10), PRIMARY KEY(Work_Order_ID));

-- Create table Maintenance_Descriptions
CREATE TABLE Maintenance_Descriptions(Maintenance_Type_ID number(10), Level_Code varchar2(10), Description varchar2(40), Average_Hours_Required
number(10), Days_Between_RMaintenance number(10), Max_Days_Between_Maintenance number(10), PRIMARY KEY (Maintenance_Type_ID));

-- Create table Vehicle_Maintenance
CREATE TABLE Vehicle_Maintenance (Vehicle_Maintenance_ID number(10), VIN number(10), Vehicle_Part_ID number(10), Maintenance_Type_ID number(10),
Put_in_Service_Date date, Last_MDate Date, Next_Sched_MDate Date, Under_Warranty_Flag varchar2(5), PRIMARY KEY(Vehicle_Maintenance_ID));

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

INSERT INTO "VENDORS" (VENDOR_ID, NAME, ORDER_ADDRESS, STREET, CITY, STATE, ZIP, ORDER_CONTACT, ORDER_PHONE_NUMBER, ORDER_FAX_NUMBER, BADDRESS,
BSTREET, BCITY, BSTATE, BZIP, BILLING_CONTACT, BILLING_PHONE_NUMBER, BILLING_FAX_NUMBER) VALUES ('1', 'Auto Garage', 'Autohall Garage', '10 Ocean
Road', 'Miami', 'FL', '67888', 'John Smith', '678-2020', '678-2021', 'Autohall Accounts', '12 Ocean Road', 'Miami', 'FL', '67888', 'Jane Smith',
'678-2022', '678-2021');
INSERT INTO "VENDORS" (VENDOR_ID, NAME, ORDER_ADDRESS, STREET, CITY, STATE, ZIP, ORDER_CONTACT, ORDER_PHONE_NUMBER, ORDER_FAX_NUMBER, BADDRESS,
BSTREET, BCITY, BSTATE, BZIP, BILLING_CONTACT) VALUES ('2', 'Honda Garage', 'Honda Garage', '12 Regent Street', 'Miami', 'FL', '67885', 'John Jones',
'678-3030', '678-3031', 'Honda Accounts', '14 Regent Street', 'Miami', 'FL', '67885', 'Adam Greene');

INSERT INTO "VEHICLE_TYPES" (TYPE_ID, DESCRIPTION) VALUES ('1', 'Car');
INSERT INTO "VEHICLE_TYPES" (TYPE_ID, DESCRIPTION) VALUES ('2', 'Truck');

INSERT INTO "VEHICLES" (VIN, TYPE_ID, VEHICLE_SERIAL_NUMBER, CLASS_CODE, PUT_INTO_SERVICE_DATE, GROSS_WEIGHT, MILEAGE, PURCHASE_PRICE,
ACCUMULATED_DEPRECIATION, TAKENOUT_SERVICEDATE, CAPACITY) VALUES ('1', '1', '122341AB', '10', TO_DATE('01-JUN-2015', 'DD-MON-RR'), '15000', '12500',
'2500', '500', TO_DATE('06-JUN-2015', 'DD-MON-RR'), '10');
INSERT INTO "VEHICLES" (VIN, TYPE_ID, VEHICLE_SERIAL_NUMBER, CLASS_CODE, PUT_INTO_SERVICE_DATE, GROSS_WEIGHT, MILEAGE, PURCHASE_PRICE,
ACCUMULATED_DEPRECIATION, TAKENOUT_SERVICEDATE, CAPACITY) VALUES ('2', '1', '134234DC', '15', TO_DATE('05-JUN-2015', 'DD-MON-RR'), '25000', '35000',
'5600', '1,200', TO_DATE('05-JUN-2015', 'DD-MON-RR'), '50');

INSERT INTO "MAINTENANCE_DESCRIPTIONS" (MAINTENANCE_TYPE_ID, LEVEL_CODE, DESCRIPTION, AVERAGE_HOURS_REQUIRED, DAYS_BETWEEN_RMAINTENANCE,
MAX_DAYS_BETWEEN_MAINTENANCE) VALUES ('10', '10', 'Tire Maintenance', '5', '2', '5');

INSERT INTO "MAINTENANCE_DESCRIPTIONS" (MAINTENANCE_TYPE_ID, LEVEL_CODE, DESCRIPTION, AVERAGE_HOURS_REQUIRED, DAYS_BETWEEN_RMAINTENANCE,
MAX_DAYS_BETWEEN_MAINTENANCE) VALUES ('20', '15', 'Suspension', '10', '1', '5');

INSERT INTO "PARTS_CATALOGUE" (PART_ID, DESCRIPTION, TYPE, MANUFACTURER_NAME, QUANTITY_ON_HAND, REORDER_POINT, REORDER_QTY) VALUES ('1', 'Michelin
Tires', 'Tires', 'Michelin', '15', '1', '5');
INSERT INTO "PARTS_CATALOGUE" (PART_ID, DESCRIPTION, TYPE, MANUFACTURER_NAME, QUANTITY_ON_HAND, REORDER_POINT, REORDER_QTY) VALUES ('2', 'Air Suspension
Pump', 'Suspension', 'Performance', '35', '2', '10');

INSERT INTO "PARTS_INVENTORY_ISSUES" (TRANSACTION_ID, PART_ID, ISSUE_DATE, PURCHASE_PRICE, QUANTITY) VALUES ('100', '1', TO_DATE('31-MAY-2015',
'DD-MON-RR'), '25.50', '25');
INSERT INTO "PARTS_INVENTORY_ISSUES" (TRANSACTION_ID, PART_ID, ISSUE_DATE, PURCHASE_PRICE, QUANTITY) VALUES ('101', '2', TO_DATE('01-JUN-2015',
'DD-MON-RR'), '15.50', '10');

INSERT INTO "PARTS_INVENTORY_PURCHASES" (TRANSACTION_ID, PART_ID, PURCHASE_DATE) VALUES ('100', '1', TO_DATE('31-MAY-2015', 'DD-MON-RR'));
INSERT INTO "PARTS_INVENTORY_PURCHASES" (TRANSACTION_ID, PART_ID, PURCHASE_DATE) VALUES ('101', '2', TO_DATE('01-JUN-2015', 'DD-MON-RR'));

INSERT INTO "MAINTENANCE_WORK_ORDER" (WORK_ORDER_ID, VEHICLE_ID, PART_ID, MAINTENANCE_TYPE_ID, ASSIGNMENT_TO, DATE_STARTED, DATE_COMPLETED, HOURS) VALUES
('1000', '1', '2', '10', 'John D', TO_DATE('01-JUN-2015', 'DD-MON-RR'), TO_DATE('05-JUN-2015', 'DD-MON-RR'), '24');

INSERT INTO "MAINTENANCE_WORK_ORDER" (WORK_ORDER_ID, VEHICLE_ID, PART_ID, MAINTENANCE_TYPE_ID, ASSIGNMENT_TO, DATE_STARTED, DATE_COMPLETED, HOURS) VALUES
('1001', '2', '1', '20', 'Tom S', TO_DATE('05-JUN-2015', 'DD-MON-RR'), TO_DATE('05-JUN-2015', 'DD-MON-RR'), '10');

INSERT INTO "PARTS_PURCHASING_HISTORY" (TRANSACTION_ID, CATALOGUE_ID, VENDOR_ID, QTY_PURCHASED, MANUFACTURER_ID, ORDER_QTY, PRICE, SHIPPING, TAX, FOB)
VALUES ('100', '1', '2', '10', '1', '20', '15.50', '5', '2', '22.50');

INSERT INTO "PARTS_PURCHASING_HISTORY" (TRANSACTION_ID, CATALOGUE_ID, VENDOR_ID, QTY_PURCHASED, MANUFACTURER_ID, ORDER_QTY, PRICE, SHIPPING, TAX, FOB)
VALUES ('101', '2', '1', '15', '1', '35', '20', '2', '1', '15.50');

INSERT INTO "VEHICLE_MAINTENANCE" (VEHICLE_MAINTENANCE_ID, VIN, VEHICLE_PART_ID, MAINTENANCE_TYPE_ID, PUT_IN_SERVICE_DATE, LAST_MDATE, NEXT_SCHED_MDATE, UNDER_WARRANTY_FLAG) VALUES ('10', '2', '1', '100', TO_DATE('01-JUN-2015', 'DD-MON-RR'), TO_DATE('01-JUN2015', 'DD-MON-RR'), TO_DATE('01-JUN-2016', 'DD-MON-RR'), 'Y');

INSERT INTO "VEHICLE_MAINTENANCE" (VEHICLE_MAINTENANCE_ID, VIN, VEHICLE_PART_ID, MAINTENANCE_TYPE_ID, PUT_IN_SERVICE_DATE, LAST_MDATE, NEXT_SCHED_MDATE, UNDER_WARRANTY_FLAG) VALUES ('20', '2', '1', '110', TO_DATE('05-JUN-2015', 'DD-MON-RR'), TO_DATE('05-JUN2015', 'DD-MON-RR'), TO_DATE('31-DEC-2015', 'DD-MON-RR'), 'N');

 

 

 

Create a simple query for each table that returns all of the columns and all of the rows for each table.
SELECT * FROM MaintenanceDexcriptions;
SELECT * FROM MaintenanceCodeLockUp;
SELECT * FROM Maintenance{erformed;
SELECT * FROM TireType;
SELECT * FROM TireManufacturer;
SELECT * FROM VehicleMaintenance;
SELECT * FROM PartsInventoryIssues;
SELECT * FROM PartsCatalogue;
SELECT * FROM PartsUsed;
SELECT * FROM TireMaintenance ;
SELECT * FROM VehicleClassCodeLookUp;
SELECT * FROM TireService ;
SELECT * FROM PartsPuchasingHistory;
SELECT * FROM MaintenanceWorkOrders;
SELECT * FROM PartsInventoryPurchases;
SELECT * FROM PartsManufacturerLockUp;
SELECT * FROM Vendors;
SELECT * FROM Venhicles;
SELECT * FROM VenhicleTypes;

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

select PuchaseOrderNumber, PartsCatalogue.*,Vendor.* from PartsPuchasingHistory, PartsCatalogue,Vendor where PartsPuchasingHistory.CatalogueID= PartsCatalogue.CatalogueID AND PartsPuchasingHistory.VendorID=Vendor.VendorID;

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

select * from VehicleMaintenance where MaintenanceWorkOrders.workOrderID= VehicleMaintenance.workOrderID AND VenhicleTypes.vehicleTypetID= Venhicles.vehicleTypetID AND Venhicles.VehicleClassCode=VehicleClassCodeLookUp.VehicleClassCode AND MaintenanceWorkOrders.VIN=Vehicle.VIN;

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

select * from MaintenanceWorkOrders where MaintenanceWorkOrders.workOrderID= VehicleMaintenance.workOrderID;

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

select* vehicle_id, COUNT(work_order_id)From maintenance_work_order GROUP BY vehicle_id;