Use Master
Go
if exists
	(select [name] 
		From sys.Databases
		Where [name]='AutoMart')
Begin
Drop Database AutoMart
End
Go
Create Database AutoMart
Go
Use AutoMart
Go

/*this login table is only used by customers who
access their accounts from the web. The UserID is their
email address */

Create table UserLogin
(
	UserID varchar(200) primary key, --email address
	UPassword varchar(20) not null
)
Go

/*Simplified customer table. I am not going to worry
about addresses and phones etc. */

Create table Customer
(
	CID int identity(1000,1) primary Key,
	CEmail varchar(200),
	CLastName varchar(30) Not Null,
	CfirstName varchar(25)
)
Go

--each customer has one or many vehicles
Create table Vehicle
(
	VLicense char(10) primary key,
	VType varchar(30) Not Null,
	VYear char(4),
	CID int Foreign Key references Customer(CID)
)
GO

--the services available at the automart
Create Table AutoService
(
	SID int identity(100,1) primary key,
	SName varchar(100) Not Null,
	SDescription varchar(255),
	SPrice smallmoney Not Null
)
Go

--the locations of the various automarts
Create table Location
(
	LID int identity (1,1) primary key,
	LName varchar(100) Not Null,
	LAddress varchar(100),
	LCity varchar(100),
	LState char(2),
	LZip char(9),
	LPhone char(10)
)
Go

--simplified
Create table Mechanic
(
	MID int identity (100,1) primary Key,
	MLastName varchar(30) Not Null,
	MFirstName varchar(25),
)
Go

--simplified
Create Table StoreManager
(
	SMID int identity(100,1) primary key,
	SMLastName varchar(30) not null,
	SMFirstName varchar(25)
)
GO

Create table ServiceHistory
(
	SHID int identity(1,1) primary key,
	SHDate DateTime not null,
	VLicense char(10) Foreign Key references Vehicle(VLicense),
	LID int Foreign Key references Location(LID)
)

GO

Create Table ServiceHistoryDetail
(
	SHDID int identity(1,1) primary Key,
	SHID int foreign Key references ServiceHistory(SHID),
	SID int foreign Key references AutoService(SID),
	SCharge smallmoney not null,
	MID int foreign Key references Mechanic(MID),
	SMID int foreign key references StoreManager(SMID)
)

--inserts for location
Insert into location(LName, LAddress, LCity, LState, LZip, LPhone)
Values('Central Area', '1200 16th Ave','Seattle', 'WA', '98122', '2065551000')
Insert into location(LName, LAddress, LCity, LState, LZip, LPhone)
Values('Kent East', '1919 204th Ave East','Kent', 'WA', '98222', '3605551000')
Insert into location(LName, LAddress, LCity, LState, LZip, LPhone)
Values('Bellevue', '836 8th Ave','Dellevue', 'WA', '98322', '3065552000')
Insert into location(LName, LAddress, LCity, LState, LZip, LPhone)
Values('South Tacoma', '101 456th Street','Tacoma', 'WA', '98422', '2535551000')
Insert into location(LName, LAddress, LCity, LState, LZip, LPhone)
Values('CDA', '902 4th Avenue','Coeur d Alene ', 'ID', '83814','2085551000')

--Insers for autoService
Insert into AutoService(SName, SDescription, SPrice)
Values('Oil change and Lube', 'Basic oil change and Lube', 45.50)
Insert into AutoService(SName, SDescription, SPrice)
Values('air filter change', 'Install clean air filter', 20.50)
Insert into AutoService(SName, SDescription, SPrice)
Values('Radiator flush', 'Flush and refill radiator', 89.99)
Insert into AutoService(SName, SDescription, SPrice)
Values('Change Transmission fluid', 'Replace transmission fluid', 65.40)
Insert into AutoService(SName, SDescription, SPrice)
Values('Wiper Blade', 'replace wiper blades', 25.00)
Insert into AutoService(SName, SDescription, SPrice)
Values('Rotate Tires', 'Rotate and balance tires', 40.50)
Insert into AutoService(SName, SDescription, SPrice)
Values('replace break lights', 'Replace rear break lights', 18.75)

--insert into Mechanic
Insert into Mechanic(MlastName, MFirstName)
Values('Wilson', 'Roberta')
Insert into Mechanic(MlastName, MFirstName)
Values('Taylor', 'Jim')
Insert into Mechanic(MlastName, MFirstName)
Values('Larson', 'Larry')
Insert into Mechanic(MlastName, MFirstName)
Values('Nelson', 'Jill')
Insert into Mechanic(MlastName, MFirstName)
Values('Sanderson', 'Richard')
Insert into Mechanic(MlastName, MFirstName)
Values('Thompson', 'Mark')
Insert into Mechanic(MlastName, MFirstName)
Values('Anderson', 'Lon')
Insert into Mechanic(MlastName, MFirstName)
Values('Ferris', 'Sam')
Insert into Mechanic(MlastName, MFirstName)
Values('Smith', 'Gary')
Insert into Mechanic(MlastName, MFirstName)
Values('Brown', 'Nancy')
Insert into Mechanic(MlastName, MFirstName)
Values('Kim', 'Li')
Insert into Mechanic(MlastName, MFirstName)
Values('Peterson', 'Carson')
Insert into Mechanic(MlastName, MFirstName)
Values('Nguyen', 'Tim')
Insert into Mechanic(MlastName, MFirstName)
Values('Manning', 'Bill')
Insert into Mechanic(MlastName, MFirstName)
Values('Vanderbuilt', 'Ken')

--insert into storemanger

Insert into StoreManager(SMLastName, SMFirstName)
Values('Johnson', 'Leah')
Insert into StoreManager(SMLastName, SMFirstName)
Values('Taylor', 'Zachary')
Insert into StoreManager(SMLastName, SMFirstName)
Values('Kennedy', 'Tom')
Insert into StoreManager(SMLastName, SMFirstName)
Values('Manning', 'Carol')
Insert into StoreManager(SMLastName, SMFirstName)
Values('Love', 'Laura')
Insert into StoreManager(SMLastName, SMFirstName)
Values('Sounder', 'Karen')

--insert into userlogin, Customer, Vehicle
Insert into UserLogin(userID, Upassword) 
Values('jason_summers@gmail.com','argo0')

Insert into Customer(CLastname, Cfirstname, Cemail) 
Values('Summers','Jason','jason_summers@gmail.com')

Declare @cid int
Set @cid = @@identity
Insert into Vehicle (VLicense, Vtype, VYear, CID)
Values('123 FMD','Ford Taurus','2002',@cid)

Insert into UserLogin(userID, Upassword) 
Values('nada@nothing.net','zerosum')

Insert into Customer(CLastname, Cfirstname, Cemail) 
Values('Nelson','Larry','nada@nothing.net')


Set @cid = @@identity
Insert into Vehicle (VLicense, Vtype, VYear, CID)
Values('245 FML','Dodge Ram Pickup','2003',@cid)Insert into UserLogin(userID, Upassword) 
Values('volt25@yahoo.com','vol25t')

Insert into Customer(CLastname, Cfirstname, Cemail) 
Values('Voldemort','Ron','volt25@yahoo.com')

Set @cid = @@identity
Insert into Vehicle (VLicense, Vtype, VYear, CID)
Values('456 LFD','Volkswagen Beetle','2001',@cid)

Insert into UserLogin(userID, Upassword) 
Values('cookie34@gmail.com','chdough')

Insert into Customer(CLastname, Cfirstname, Cemail) 
Values('Landers','Amanda','cookie34@gmail.com')


Set @cid = @@identity
Insert into Vehicle (VLicense, Vtype, VYear, CID)
Values('234 LSP','Windstar Van','2004',@cid)

Insert into UserLogin(userID, Upassword) 
Values('moonlight@morrison.com','mnlt300')

Insert into Customer(CLastname, Cfirstname, Cemail) 
Values('Morrison','Joe','moonlight@morrison.com')


Set @cid = @@identity
Insert into Vehicle (VLicense, Vtype, VYear, CID)
Values('909 LTC','Ford Escort','2002',@cid)

Insert into UserLogin(userID, Upassword) 
Values('happyfeet@aol.com','peguinsRus')

Insert into Customer(CLastname, Cfirstname, Cemail) 
Values('Brown','Stephanie','happyfeet@aol.com')


Set @cid = @@identity
Insert into Vehicle (VLicense, Vtype, VYear, CID)
Values('454 LCG','Ford Mustang','2003',@cid)

Insert into UserLogin(userID, Upassword) 
Values('trex30@yahoo.com','tyrannus')

Insert into Customer(CLastname, Cfirstname, Cemail) 
Values('Lowen','Sal','trex30@yahoo.com')

Set @cid = @@identity
Insert into Vehicle (VLicense, Vtype, VYear, CID)
Values('222 CIL','Chevy Truc','2000',@cid)

Insert into UserLogin(userID, Upassword) 
Values('lilac57@msn.com','jabc9097')

Insert into Customer(CLastname, Cfirstname, Cemail) 
Values('Song','Li','lilac57@msn.com')

Set @cid = @@identity
Insert into Vehicle (VLicense, Vtype, VYear, CID)
Values('429 FLC','Kia Sephia','2001',@cid)

Insert into UserLogin(userID, Upassword) 
Values('dodson@gmail.com','dod41')

Insert into Customer(CLastname, Cfirstname, Cemail) 
Values('Laura','Dodson','dodson@gmail.com')

Set @cid = @@identity
Insert into Vehicle (VLicense, Vtype, VYear, CID)
Values('130 DEC','Honda Civic','2003',@cid)

Insert into UserLogin(userID, Upassword) 
Values('chickenlittle@yaho.com','skyfall2')

Insert into Customer(CLastname, Cfirstname, Cemail) 
Values('Lewis','Sara','chickenlittle@yaho.com')

Set @cid = @@identity
Insert into Vehicle (VLicense, Vtype, VYear, CID)
Values('909 FML','Jeep','2000',@cid)

Insert into UserLogin(userID, Upassword) 
Values('wowmaster@msn.com','dragon43')

Insert into Customer(CLastname, Cfirstname, Cemail) 
Values('Masters','Mark','wowmaster@msn.com')

Set @cid = @@identity
Insert into Vehicle (VLicense, Vtype, VYear, CID)
Values('202 CAL','Ford Taurus','2001',@cid)

Insert into UserLogin(userID, Upassword) 
Values('lillypond@gmail.com','enlight9n')

Insert into Customer(CLastname, Cfirstname, Cemail) 
Values('Chung','Tracy','lillypond@gmail.com')

Set @cid = @@identity
Insert into Vehicle (VLicense, Vtype, VYear, CID)
Values('W9801K','Kia spectra','2001',@cid)

--insert into ServiceHistory, serviceHistoryDetail

Declare @SHID int
Insert into ServiceHistory(SHDate, VLicense, LID)
Values('1/4/2007','130 DEC',3)
SET @SHID = @@Identity
Insert into ServiceHistoryDetail(SHID, [SID],SCharge, MID, SMID)
Values (@SHID,100,45.50,102,101)
Insert into ServiceHistoryDetail(SHID, [SID], SCharge, MID, SMID)
Values(@SHID,104,25.00,103,101)

Insert into ServiceHistory(SHDate, VLicense, LID)
Values('1/4/2007','222 CIL',3)
SET @SHID = @@Identity
Insert into ServiceHistoryDetail(SHID, [SID],SCharge, MID, SMID)
Values (@SHID,100,45.50,102,101)

Insert into ServiceHistory(SHDate, VLicense, LID)
Values('1/4/2007','123 FMD',3)
SET @SHID = @@Identity
Insert into ServiceHistoryDetail(SHID, [SID],SCharge, MID, SMID)
Values (@SHID,100,45.50,102,101)
Insert into ServiceHistoryDetail(SHID, [SID],SCharge, MID, SMID)
Values (@SHID,102,89.99,104,101)

Insert into ServiceHistory(SHDate, VLicense, LID)
Values('1/6/2007','429 FLC',1)
SET @SHID = @@Identity
Insert into ServiceHistoryDetail(SHID, [SID],SCharge, MID, SMID)
Values (@SHID,100,45.50,105,103)

Insert into ServiceHistory(SHDate, VLicense, LID)
Values('1/6/2007','202 CAL',1)
SET @SHID = @@Identity
Insert into ServiceHistoryDetail(SHID, [SID],SCharge, MID, SMID)
Values (@SHID,100,45.50,106,103)
Insert into ServiceHistoryDetail(SHID, [SID],SCharge, MID, SMID)
Values (@SHID,103,64.40,105,103)
Insert into ServiceHistoryDetail(SHID, [SID],SCharge, MID, SMID)
Values (@SHID,105,40.50,108,103)

Insert into ServiceHistory(SHDate, VLicense, LID)
Values('1/8/2007','234 LSP',1)
SET @SHID = @@Identity
Insert into ServiceHistoryDetail(SHID, [SID],SCharge, MID, SMID)
Values (@SHID,100,45.50,105,103)

Insert into ServiceHistory(SHDate, VLicense, LID)
Values('1/6/2007','245 FML',4)
SET @SHID = @@Identity
Insert into ServiceHistoryDetail(SHID, [SID],SCharge, MID, SMID)
Values (@SHID,100,45.50,109,105)

Insert into ServiceHistory(SHDate, VLicense, LID)
Values('1/8/2007','454 LCG',4)
SET @SHID = @@Identity
Insert into ServiceHistoryDetail(SHID, [SID],SCharge, MID, SMID)
Values (@SHID,100,45.50,109,105)
Insert into ServiceHistoryDetail(SHID, [SID],SCharge, MID, SMID)
Values (@SHID,106,18.75,109,105)

Insert into ServiceHistory(SHDate, VLicense, LID)
Values('1/8/2007','456 LFD',3)
SET @SHID = @@Identity
Insert into ServiceHistoryDetail(SHID, [SID],SCharge, MID, SMID)
Values (@SHID,106,18.75,110,104)

Insert into ServiceHistory(SHDate, VLicense, LID)
Values('1/6/2007','429 FLC',2)
SET @SHID = @@Identity
Insert into ServiceHistoryDetail(SHID, [SID],SCharge, MID, SMID)
Values (@SHID,100,45.50,111,104)

Insert into ServiceHistory(SHDate, VLicense, LID)
Values('1/6/2007','909 FML',5)
SET @SHID = @@Identity
Insert into ServiceHistoryDetail(SHID, [SID],SCharge, MID, SMID)
Values (@SHID,100,45.50,113,105)

Insert into ServiceHistory(SHDate, VLicense, LID)
Values('1/6/2007','909 LTC',5)
SET @SHID = @@Identity
Insert into ServiceHistoryDetail(SHID, [SID],SCharge, MID, SMID)
Values (@SHID,100,45.50,113,105)
Insert into ServiceHistoryDetail(SHID, [SID],SCharge, MID, SMID)
Values (@SHID,101,20.50,114,105)

Insert into ServiceHistory(SHDate, VLicense, LID)
Values('1/6/2007','W9801K',5)
SET @SHID = @@Identity
Insert into ServiceHistoryDetail(SHID, [SID],SCharge, MID, SMID)
Values (@SHID,100,45.50,113,105)

Insert into ServiceHistory(SHDate, VLicense, LID)
Values('4/4/2007','222 CIL',3)
SET @SHID = @@Identity
Insert into ServiceHistoryDetail(SHID, [SID],SCharge, MID, SMID)
Values (@SHID,100,45.50,102,101)

Insert into ServiceHistory(SHDate, VLicense, LID)
Values('4/8/2007','234 LSP',1)
SET @SHID = @@Identity
Insert into ServiceHistoryDetail(SHID, [SID],SCharge, MID, SMID)
Values (@SHID,100,45.50,105,103)


Insert into ServiceHistory(SHDate, VLicense, LID)
Values('4/8/2007','454 LCG',4)
SET @SHID = @@Identity
Insert into ServiceHistoryDetail(SHID, [SID],SCharge, MID, SMID)
Values (@SHID,100,45.50,109,105)
Insert into ServiceHistoryDetail(SHID, [SID],SCharge, MID, SMID)
Values (@SHID,106,18.75,109,105)

Insert into ServiceHistory(SHDate, VLicense, LID)
Values('5/6/2007','909 FML',5)
SET @SHID = @@Identity
Insert into ServiceHistoryDetail(SHID, [SID],SCharge, MID, SMID)
Values (@SHID,100,45.50,113,105)

Insert into ServiceHistory(SHDate, VLicense, LID)
Values('5/4/2007','222 CIL',3)
SET @SHID = @@Identity
Insert into ServiceHistoryDetail(SHID, [SID],SCharge, MID, SMID)
Values (@SHID,100,45.50,102,101)