Ask DBMS Expert


Home >> DBMS

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;

DBMS, Programming

  • Category:- DBMS
  • Reference No.:- M91339879
  • Price:- $30

Priced at Now at $30, Verified Solution

Have any Question?


Related Questions in DBMS

Data mining assignment -in this assignment you are asked to

Data Mining Assignment - In this assignment you are asked to explore the use of neural networks for classification and numeric prediction. You are also asked to carry out a data mining investigation on a real-world data ...

Sql query assignment -for this assignment you are to write

SQL Query Assignment - For this assignment you are to write your answers in a word document. This assignment is in three parts: Part A (reporting queries), Part B (query performance), Part C (query design). For this assi ...

The groceries datasetimagine 10000 receipts sitting on your

The groceries Dataset Imagine 10000 receipts sitting on your table. Each receipt represents a transaction with items that were purchased. The receipt is a representation of stuff that went into a customer's basket. That ...

You are in a real estate business renting apartments to

You are in a real estate business renting apartments to customers. Your job is to define an appropriate schema using SQL DDL in MySQL. The relations are Property(Id, Address, NumberOfUnits), Unit(ApartmentNumber, Propert ...

Objectivethe objective of this lab is to be familiar with a

OBJECTIVE: The objective of this lab is to be familiar with a process in big data modeling. You're required to produce three big data models using the MS PowerPoint software. This tool is available on UMUC Virtual Deskto ...

The relation memberstudentid organizationid roleid stores

The relation Member(StudentId, OrganizationId, RoleId) stores the membership information of student joining organization. For example, ('S1', 'O2', 'R3') indicates that student with Id 'S1' joined the organization with i ...

Relational database exerciseyou have been assigned to a new

Relational Database Exercise: You have been assigned to a new development team. A client is requesting a relational database system to manage their present store with the anticipation of adding more stores in the future. ...

Relational database design a given the following business

Relational Database Design A) Given the following business rules, identify entity types, attributes (at least two attributes for each entity, including the primary key) and relationships, and then draw an Entity-Relation ...

We can represent a data set as a collection of object nodes

We can represent a data set as a collection of object nodes and a collection of attribute nodes, where there is a link between each object and each attribute, and where the weight of that link is the value of the object ...

Data model development and implementationpurpose of the

Data model development and implementation Purpose of the assessment (with ULO Mapping) The purpose of this assignment is to develop data models and map Database System into a standard development environment to gain unde ...

  • 4,153,160 Questions Asked
  • 13,132 Experts
  • 2,558,936 Questions Answered

Ask Experts for help!!

Looking for Assignment Help?

Start excelling in your Courses, Get help with Assignment

Write us your full requirement for evaluation and you will receive response within 20 minutes turnaround time.

Ask Now Help with Problems, Get a Best Answer

Why might a bank avoid the use of interest rate swaps even

Why might a bank avoid the use of interest rate swaps, even when the institution is exposed to significant interest rate

Describe the difference between zero coupon bonds and

Describe the difference between zero coupon bonds and coupon bonds. Under what conditions will a coupon bond sell at a p

Compute the present value of an annuity of 880 per year

Compute the present value of an annuity of $ 880 per year for 16 years, given a discount rate of 6 percent per annum. As

Compute the present value of an 1150 payment made in ten

Compute the present value of an $1,150 payment made in ten years when the discount rate is 12 percent. (Do not round int

Compute the present value of an annuity of 699 per year

Compute the present value of an annuity of $ 699 per year for 19 years, given a discount rate of 6 percent per annum. As