-- Bethesda Car Rental1 --
USE BethesdaCarRental1;
GO
CREATE SCHEMA Assets;
GO
CREATE SCHEMA Transactions;
GO
CREATE SCHEMA HumanResources;
GO
CREATE TABLE HumanResources.Employees
(
EmployeeNumber nvarchar(10) not null,
FirstName nvarchar(25),
LastName nvarchar(25),
Title nvarchar(50),
PhoneNumber nvarchar(25),
Constraint PK_Employees Primary Key(EmployeeNumber)
);
GO
CREATE TABLE Assets.Categories
(
Category nvarchar(25) not null,
Daily money not null,
Weekly money not null,
Monthly money not null,
Weekend money not null,
Constraint PK_Categories Primary Key(Category)
);
GO
CREATE TABLE Assets.Availabilities
(
Availability nvarchar(20) not null,
Description nvarchar(max),
Constraint PK_Availabilities Primary Key(Availability)
);
GO
CREATE TABLE Assets.Vehicles
(
TagNumber nvarchar(10) not null,
Category nvarchar(25),
Make nvarchar(40) not null,
Model nvarchar(40) not null,
Doors tinyint,
Passengers tinyint,
Condition nvarchar(25),
Availability nvarchar(20),
Constraint PK_Cars Primary Key(TagNumber),
Constraint FK_CarsCategories Foreign Key(Category) References Assets.Categories(Category)
);
GO
CREATE TABLE Transactions.RentalOrders
(
ReceiptNumber int identity(100001, 1),
RentStartProcessedBy nvarchar(10),
RentEndProcessedBy nvarchar(10),
CustomerFirstName nvarchar(25),
CustomerLastName nvarchar(25),
CustomerAddress nvarchar(50),
CustomerCity nvarchar(40),
CustomerState nchar(2),
CustomerZIPCode nvarchar(10),
TagNumber nvarchar(10) not null,
Condition nvarchar(25),
TankLevel nvarchar(25),
MileageStart int,
MileageEnd int,
MileageTotal int,
StartDate date,
EndDate date,
TotalDays int,
RateApplied money,
SubTotal money,
TaxRate decimal(4, 2),
TaxAmount money,
OrderTotal money,
OrderStatus nvarchar(40),
Notes nvarchar(max),
Constraint PK_RentalOrders Primary Key(ReceptNumber),
Constraint FK_RentStartProcessedBy Foreign Key(RentStartProcessedBy) References HumanResources.Employees(EmployeeNumber),
Constraint FK_RentEndProcessedBy Foreign Key(RentEndProcessedBy) References HumanResources.Employees(EmployeeNumber),
Constraint FK_RentalCars Foreign Key(TagNumber) References Assets.Vehicles(TagNumber)
);
GO
INSERT INTO HumanResources.Employees(EmployeeNumber, FirstName, LastName, Title, PhoneNumber)
VALUES(N'92735', N'Jeffrey', N'Leucart', N'Owner - General Manager', N'240-773-2010'),
(N'29268', N'Kathleen', N'Rawley', N'Technician', N'240-773-2011'),
(N'73948', N'Alison', N'Garlow', N'Accounts Associates', N'240-773-2012'),
(N'40508', N'David', N'Stillson', N'Accounts Manager', N'240-773-2013'),
(N'24793', N'Michelle', N'Taylor', N'Accounts Associates', N'240-773-2014'),
(N'20480', N'Peter', N'Futterman', N'Accounts Associates', N'240-773-2015'),
(N'72084', N'Georgia', N'Rosen', N'Customer Service Representative', N'240-773-2016'),
(N'38240', N'Karen', N'Blakney', N'Accounts Associates', N'240-773-2017');
GO
INSERT INTO Assets.Categories
VALUES(N'Economy', 34.95, 28.75, 24.95, 24.95),
(N'Compact', 38.95, 32.75, 28.95, 28.95),
(N'Standard', 45.95, 39.75, 35.95, 34.95),
(N'Full Size', 50.00, 45.00, 42.55, 38.95),
(N'Mini Van', 55.00, 50.00, 44.95, 42.95),
(N'SUV', 56.95, 52.95, 44.95, 42.95),
(N'Pickup Truck', 62.95, 52.75, 46.95, 44.95),
(N'Passenger Van', 69.95, 64.75, 52.75, 49.95);
GO
INSERT INTO Assets.Availabilities
VALUES(N'Available', N'The car is available for rent.'),
(N'Rented', N'The car is currently with a customer and not with the store.'),
(N'Needs Repair', N'The car cannot currently be rented to a customer.'),
(N'Being Serviced', N'The car is probably currently in the shop for repairs.');
GO
INSERT INTO Assets.Vehicles(TagNumber, Category, Make, Model, Doors, Passengers, Condition, Availability)
VALUES(N'2AM9952', N'Economy', N'Ford', N'Fiesta SE', 4, 5, N'Driveable', N'Available'),
(N'6AD8274', N'Mini Van', N'Mazda', N'CX-9', 4, 5, N'Excellent', N'Available'),
(N'8AG3584', N'Passenger Van', N'Toyota', N'Sienna LE FWD', 4, 8, N'Excellent', N'Available'),
(N'KER204', N'Compact', N'Ford', N'Focus SF', 4, 5, N'Excellent', N'Being Serviced'),
(N'3AD9283', N'Economy', N'Kia', N'Rio EX', 4, 5, N'Excellent', N'Rented'),
(N'8AE9294', N'Full Size', N'Lincoln', N'MKT 3.5L', 4, 5, N'Excellent', N'Available'),
(N'KLT840', N'Passenger Van', N'Ford', N'E-350 XL', 3, 15, N'Driveable', N'Available'),
(N'8AL8033', N'Compact', N'Toyota', N'Corolla LE', 4, 5, N'Excellent', N'Available'),
(N'4AF9284', N'Pickup Truck', N'Ford', N'F-150 Reg Cap 4X4', 2, 2, N'Needs Repair', N'Available'),
(N'ADG279', N'SUV', N'GMC', N'Acadia SLE', 4, 5, N'Excellent', N'Rented'),
(N'1AD8049', N'Standard', N'Dodge', N'Charger SXT', 4, 5, N'Excellent', N'Being Serviced'),
(N'9MD3974', N'Passenger Van', N'Toyota', N'Sienna LE FWD', 4, 8, N'Driveable', N'Rented'),
(N'5AJ9274', N'Full Size', N'BMW', N'528i', 4, 5, N'Excellent', N'Available'),
(N'GTH295', N'Economy', N'Kia', N'Rio EX', 4, 5, N'Excellent', N'Available'),
(N'8AT2408', N'Compact', N'Mazda', N'Miata MX-5', 2, 2, N'Excellent', N'Available'),
(N'6AP2486', N'Economy', N'Fiat', N'500', 2, 4, N'Excellent', N'Available'),
(N'2AL9485', N'Compact', N'Chrysler', N'200', 2, 2, N'Excellent', N'Available'),
(N'DFP924', N'Passenger Van', N'Toyota', N'Sienna LE FWD', 4, 8, N'Driveable', N'Available'),
(N'2MD8382', N'SUV', N'Toyota', N'RAV4 I4 4X4', 4, 5, N'Excellent', N'Available'),
(N'8AR9374', N'Standard', N'Honda', N'Accord LX', 4, 5, N'Excellent', N'Rented'),
(N'5MD2084', N'Mini Van', N'Chevrolet', N'Equinox LS', 4, 5, N'Driveable', N'Available'),
(N'BND927', N'Economy', N'Ford', N'Fiesta SE', 4, 5, N'Driveable', N'Available'),
(N'6AP2749', N'Compact', N'Toyota', N'Corolla LE', 4, 5, N'Excellent', N'Rented'),
(N'8AL7394', N'Pickup Truck', N'Ford', N'F-250 SD Reg Cab 4X4', 2, 2, N'Excellent', N'Available'),
(N'4MD2840', N'Passenger Van', N'Chevrolet', N'2500 LS', 3, 15, N'Excellent', N'Rented'),
(N'G249580', N'Compact', N'Nissan', N'Sentra SR', 4, 5, N'Excellent', N'Available'),
(N'3AK7397', N'Compact', N'Chrysler', N'200', 2, 2, N'Excellent', N'Available'),
(N'VGT927', N'Pickup Truck', N'Toyota', N'Tundra Dbl Cab 4X4', 2, 5, N'Excellent', N'Available'),
(N'2AT9274', N'Compact', N'Ford', N'Focus SF', 4, 5, N'Excellent', N'Available'),
(N'6AH8429', N'Full Size', N'Lincoln', N'MKT 3.5L', 4, 5, N'Needs Repair', N'Available'),
(N'8MD9284', N'Mini Van', N'Ford', N'Escape SE I4', 4, 5, N'Excellent', N'Available'),
(N'PLD937', N'Compact', N'Chevrolet', N'Imapala LT', 4, 5, N'Excellent', N'Being Serviced'),
(N'5AK2974', N'Economy', N'Fiat', N'500', 2, 4, N'Excellent', N'Available'),
(N'1MD9284', N'Mini Van', N'Ford', N'Escape SE I4', 4, 5, N'Excellent', N'Being Serviced'),
(N'SDG624', N'Standard', N'Chevrolet', N'Volt', 4, 5, N'Excellent', N'Available'),
(N'2AR9274', N'Economy', N'Kia', N'Rio SX', 4, 5, N'Excellent', N'Available'),
(N'JWJ814', N'Full Size', N'Cadillac', N'CTS-V', 4, 5, N'Excellent', N'Available'),
(N'7MD9794', N'Compact', N'Ford', N'Focus SF', 4, 5, N'Excellent', N'Rented'),
(N'UQW118', N'Passenger Van', N'Chevrolet', N'2500 LS', 3, 15, N'Needs Repair', N'Available'),
(N'2MD9247', N'SUV', N'Toyota', N'RAV4 I4 4X4', 4, 5, N'Excellent', N'Available');
GO
/*
CREATE TABLE Assets.OrdersStatus
(
OrderStatus nvarchar(40) not null,
Description nvarchar(max),
Constraint PK_OrdersStatus Primary Key(OrderStatus)
);
GO
INSERT INTO Assets.OrdersStatus
VALUES(N'Processing', N'The rental order is currently being processed.'),
(N'Car With Customer', N'The car has been rented and is with the customer.'),
(N'Order Complete', N'The rental order is now complete.'),
(N'Unknown', N'The order status is not specified.');
GO
*/
/*
INSERT INTO Transactions.Customers(AccountNumber, FirstName, LastName, Address, City, State, ZIPCode, PhoneNumber, EmailAddress)
VALUES(N'203841', N'Michelle', N'Russell', N'10070 Weatherwood Drv', N'Rockville', N'MD', N'20853', N'301-773-2100', N'mrussel@googlemail.com'),
(N'480308', N'Gregory', N'Strangeman', N'5530 Irving St', N'College Park', N'MD', N'20740', N'240-777-4410', N'imstrange@yahoo.com'),
(N'720480', N'Marcel', N'buhler', N'6800 Haxell Crt', N'Alexandria', N'VA', N'22314', N'703-358-9074', N'mbuhler1200@hotmail.vom'),
(N'270840', N'Joan', N'Altman', N'3725 South Dakota Ave NW', N'Washington', N'DC', N'20012', N'202-286-1739', N'jaltman@gmail.com'),
(N'830483', N'Thomas', N'Filder', N'4905 Herrenden St', N'Arlington', N'VA', N'22204', N'703-532-5037', N'tfilderman@msn.com'),
(N'824802', N'Scott', N'Bierend', N'3082 Rickenbacher Ave', N'Takoma Park', N'MD', N'20910', N'301-897-2203', N'scottbeer1@yahoo.com');
GO
*/
/*
CREATE VIEW Assets.Vehicles
AS
SELECT TagNumber,
Category,
Make,
Model,
Doors,
Passengers,
Condition,
Availability
FROM Assets.Cars
GO
CREATE VIEW Transactions.NewRentalOrders
AS
SELECT RentStartProcessedBy,
RentEndProcessedBy,
CustomerFirstName,
CustomerLastName,
CustomerAddress,
CustomerCity,
CustomerState,
CustomerZIPCode,
TagNumber,
Condition,
TankLevel,
MileageStart,
StartDate,
RateApplied,
OrderStatus
FROM Transactions.RentalOrders;
GO
CREATE VIEW Transactions.RentalOrdersUpdates
AS
SELECT RentStartProcessedBy,
RentEndProcessedBy,
CustomerFirstName,
CustomerLastName,
CustomerAddress,
CustomerCity,
CustomerState,
CustomerZIPCode,
TagNumber,
Condition,
TankLevel,
MileageStart,
MileageEnd,
MileageTotal,
StartDate,
EndDate,
TotalDays,
RateApplied,
SubTotal,
TaxRate,
TaxAmount,
OrderTotal,
OrderStatus,
Notes
FROM Transactions.RentalOrders;
GO
CREATE VIEW HumanResources.Clerks
AS
SELECT EmployeeNumber,
FirstName + N' ' + LastName AS Clerk
FROM HumanResources.Employees;
GO
CREATE VIEW Assets.VehiclesSelected
AS
SELECT TagNumber,
Make + N' ' + Model AS Vehicle
FROM Assets.Cars;
GO
*/