Unquestionably, Nogales Auto-Shop Database (NASD) was intended to upgrade from a paper based system to a software management database. Essentially, the data captured for the five tables ranges from customer's personal information, vehicles unique characteristics, part/operation inventory, work order history, to work order item definition. Altogether, the data being capture will smooth the retrieving of information in the form of queries and reports.
Mainly, the project plan was outlined by assigning each team member with an explicit task. First, Yolanda classified the customers' personal information; then; Frank identified the vehicle's distinctive traits. Additionally, Juan's challenge was to incorporate data from both tables into the work order inventory. Concurrently, Juan had to establish the relationship with a fourth table, the work order history. Finally, the last table was created by Frank; the work order item definition would endow with the last piece to the puzzle for the completion of the first section of the project.
Therefore, the team members came to the table design phase where both graphical and short hand methods were utilized to show table layouts. Subsequently, the team moved on to the query design stage, and each member had to bring into play the best of their abilities and knowledge during one of our most challenging sessions. However, learning from one another was the hands on activity that night. The short hand and graphical design of the tables are shown as followed:
Short Hand
Customers (CustID, CreditCard, Last_name, First_name, Address, City, State, ZipCode, Telephone, Vehicle)
Graphical
Customers
Column
Name Column
Data Type
CustID int primary key
CreditCard Char (20)
Last_name Char (25)
First_name Char (25)
Address Char (50)
City Char (25)
State Char (2)
ZipCode Char (10)
Telephone Char (15)
Vehicle Varchar (10)
Short Hand
WorkOrder (WO_key, CustID, Vehicle, Appointment, PlannedActivities, ActualActivities, WOI)
Graphical
Work Order
Column
Name Column
Data Type
WO int primary key
CustID Int
Vehicle varchar (10)
Appointment Char (20)
PlannedActivities Char (50)
ActualActiviites Char (50)
WOI int
Short Hand
WorkOrderItem (WOI, PartsOperation, Quantity, Labor, ActualActivities)
Graphical
Work Order Item
Column
Name Column
Data Type
WOI int primary key
PartsOperation Int
Quantity int
Labor int
ActualActiviites Char (50)
Short Hand
Vehicle (VehicleID, TagNumber, Make, Model, CarYear)
Graphical
Vehicle
Column
Name Column
Data Type
VehicleID Varchar (10) primary key
TagNumber Vachar (10)
Make varchar (50)
Model varchar (50)
CarYear Varchar (10)
Short Hand
PartOperation (PartsOperation, PartDescription, Price)
Graphical
Part Operation
Column
Name Column
Data Type
PartOperation Int primary key
PartDescription char (50)
Price Decimal (5,2)
Nevertheless, the queries were created following the guidelines Juan suggested because they applied to his business needs. Below are the commands for the queries:
1) Invoice
Select Customers.CustID, Last_name, First_name, PartOperation.PartsOperation, PartDescription, Price, WorkOrderItem.PartsOperation, Quantity, Labor, ActualActivities
From Customers, PartOperation, WorkOrderItem
Where WorkOrderItem.PartsOperation= PartOperation.PartsOperation
2) Last visit by a customer
Select Customers.CustID, Last_name,First_name, WorkOrder.CustID, Appointment, PlannedActivities
From Customers, WorkOrder
Where Customers.CustID =WorkOrder.CustID
3) Average Labor cost
SELECT AVG(Labor) FROM WorkOrderItem
4) Parts cost over 50.00
Select Customers.CustID, Customers.Vehicle, WorkOrder.WOI, PartOperation.PartsOperation, Price
From Customers, WorkOrder, PartOperation, WorkOrderItem
Where Customers.CustID = WorkOrder.CustID
And WorkOrderItem.WOI = WorkOrder.WOI
And WorkOrderItem.PartsOperation = PartOperation.PartsOperation
And Price > = '50.00'
5) All Customers and Labor cost
Select Customers.CustID, Customers.Vehicle, WorkOrder.WOI, PartOperation.PartsOperation, WorkOrderItem.Labor
From Customers, WorkOrder, PartOperation, WorkOrderItem
Where Customers.CustID = WorkOrder.CustID
And WorkOrderItem.WOI = WorkOrder.WOI
And WorkOrderItem.PartsOperation = PartOperation.PartsOperation
And WorkOrderItem.Labor = WorkOrderItem.Labor
6) Group cars from older to newer
Select Model, Make, CarYear
From Vehicle
Group by CarYear, Make, Model;
All in all, the queries were tested and up this point the project was successful.
References
Pratt, P. J. (2003). A guide to SQL. : . Retrieved July 29, 2006, from http://ecampus.phoenix.edu/eBookLibrary/content/