SQL For Business: Auto-Shop Database

Essay by maguirre2005University, Master'sA+, September 2006

download word file, 2 pages 3.8

Downloaded 64 times

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/