PLEASE answer this question using the following table i have created below thank you

3. Write a stored procedure named sp_UpdateRackRates that can be used to update prices on the RackRate Table. The procedure will require a HotelID, an optional RoomTypeID, a begin date*, as well as a percentage increase (or decrease) parameter to be passed. Write a single procedure with IF/ELSE statements. If the RoomTypeID is not provided, you will assume that ALL roomtypes (for the hotelid) are to be updated. Otherwise, the Proc will set the new rackrates for the particular Hotel's RoomTypeID (not all the RoomTypes in that hotel) - rounding either down to the nearest dollar for price decreases, or rounding up to the nearest dollar for price increases. *You will only update the rates WHERE the begin date provided falls BETWEEN the current Begin and End Rack Rate Dates.

3A. Demonstrate: It's summertime in Park City, and Sunridge B&B wants to decrease rates (Rounding down to the nearest dollar) 6.66% for roomtype 1 only, Make sue you also update the begin date but keep the current end date 11/14/2017. Call sp_UpdateRackRates in order to make this happen.

3B. Select * From RackRate to show the results.

3C. Demonstrate: Calling the exact same sp_UpdateRackRates, with different parameters only - increasing 5.55% (Rounding up to the nearest dollar) the price of ALL roomtypes at Weber Suites. Make sue you also update the begin date but keep the current end date 7/31/2017.

3D. Select * From RackRate to show the results.

USE Master --this will switch to Master DB

IF EXISTS (SELECT * FROM sysdatabases WHERE name = 'FARMS') DROP DATABASE FARMS

GO --This will pause the script to make sure everything has happened CREATE DATABASE FARMS

ON PRIMARY

(NAME = 'FARMS', FILENAME = 'C:\stage\FARMS.mdf', --Change the path for local SIZE = 12MB, -- 500kb, 500, TB, GB MAXSIZE = 50 MB, FILEGROWTH = 10% -- 5MB )

LOG ON (NAME = 'FARMS_Log', FILENAME = 'C:\stage\FARMS.ldf', --Change the path for local SIZE = 4MB, -- 500kb, 500, TB, GB -- 10% Read 25% R/Write MAXSIZE = 13MB, FILEGROWTH = 10% -- 5MB ) GO

-- With the Fry_Ship database now created, switch to it and begin creating the individual -- tables for the database

USE FARMS

CREATE TABLE RESERVATION ( ReservationID smallint NOT NULL IDENTITY(5000,1), ReservationDate date NOT NULL, ReservationStatus char(1) NOT NULL, ReservationComments varchar(200) NULL, CreditCardID smallint NOT NULL )

CREATE TABLE GUEST ( GuestID smallint NOT NULL IDENTITY(1500,1), GuestFirst varchar(20) NOT NULL, GuestLast varchar(20) NOT NULL, GuestAddress varchar(30) NOT NULL, guestAddress2 varchar(10) null, GuestCity varchar(20) NOT NULL, GuestState char(2) NULL, GuestPostalCode char(10) NOT NULL, GuestCountry varchar(20) NOT NULL, GuestPhone varchar(20) NULL, GuestEmail varchar(20) NULL, GuestComments varchar(200) NULL )

CREATE TABLE ROOMTYPE ( RoomTypeID smallint NOT NULL IDENTITY(1,1), RTDescription varchar(200) NOT NULL )

CREATE TABLE CREDITCARD ( CreditCardID smallint NOT NULL IDENTITY(1,1), GuestID smallint NOT NULL, CCType varchar(5) NOT NULL, CCNumber varchar(16) NOT NULL, CCCompany varchar(40) NULL, CCCardHolder varchar(40) NOT NULL, CCExpiration smalldatetime NOT NULL )

CREATE TABLE DISCOUNT ( DiscountID smallint NOT NULL IDENTITY(1,1), DiscountDescription varchar(50) NOT NULL, DiscountExperiation date NOT NULL, DiscountRules varchar(100) NULL, DiscountPercent decimal(4,2) NULL, DiscountAmount smallmoney NULL )

CREATE TABLE ROOM ( RoomID smallint NOT NULL IDENTITY(1,1), RoomNumber varchar(5) NOT NULL, RoomDescription varchar(200) NOT NULL, RoomSmoking bit NOT NULL, RoomBedConfiguration char(2) NOT NULL, HotelID smallint NOT NULL, RoomTypeID smallint NOT NULL )

CREATE TABLE RACKRATE ( RackRateID smallint NOT NULL IDENTITY(1,1), ROOMTYPEID smallint NOT NULL, HotelID smallint NOT NULL, RackRate smallmoney NOT NULL, RackRateBegin date NOT NULL, RackRateEnd date NOT NULL, RackRateDescription varchar(200) NOT NULL )

CREATE TABLE FOLIO ( FolioID smallint NOT NULL IDENTITY(1,1), ReservationID smallint NOT NULL, GuestID smallint NOT NULL, RoomID smallint NOT NULL, QuotedRate smallmoney NOT NULL, CheckinDate smalldatetime NOT NULL, Nights tinyint NOT NULL, FStatus char(1) NOT NULL, Comments varchar(200) NULL, DiscountID smallint NOT NULL )

CREATE TABLE BILLING ( FolioBillingID smallint NOT NULL IDENTITY (1,1), FolioId smallint NOT NULL, BillingCategoryID smallint NOT NULL, BillingDescription char(30) NOT NULL, BillingAmount smallmoney NOT NULL, BillingItemQty tinyint NOT NULL, BillingItemDate date NOT NULL, )

CREATE TABLE HOTEL ( HotelID smallint NOT NULL IDENTITY(2100,100), HotelName varchar(30) NOT NULL, HotelAddress varchar(30) NOT NULL, HotelCity varchar(20) NOT NULL, HotelState char(2) NULL, HotelCountry varchar(20) NOT NULL, HotelPostalCode char(10) NOT NULL, HotelStarRating char(1) NULL, HotelPictureLink varchar(100) NULL, TaxLocationID smallint NOT NULL )

CREATE TABLE PAYMENT ( PaymentID smallint NOT NULL IDENTITY(8000,1), FolioID smallint NOT NULL, PaymentDate date NOT NULL, PaymentAmount smallmoney NOT NULL, PaymentComments varchar(200) NULL )

CREATE TABLE BILLINGCATEGORY ( BillingCategoryID smallint NOT NULL IDENTITY(1,1), BillingCatDescription varchar(30) NOT NULL, BillingCatTaxable bit NOT NULL )

CREATE TABLE TAXRATE ( TaxLocationID smallint NOT NULL IDENTITY(1,1), TaxDescription varchar(30) NOT NULL, RoomTaxRate decimal(6,4) NOT NULL, SalesTaxRate decimal(6,4) )

--Now lets ensure that all the tables were created correctly by using the GO command --before altering the tables and adding in the contraints

GO

ALTER TABLE RESERVATION ADD CONSTRAINT PK_ReservationID PRIMARY KEY (ReservationID)

ALTER TABLE GUEST ADD CONSTRAINT PK_GuestID PRIMARY KEY (GuestID)

ALTER TABLE ROOMTYPE ADD CONSTRAINT PK_RoomTypeID PRIMARY KEY(RoomTypeID)

ALTER TABLE CREDITCARD ADD CONSTRAINT PK_CreditCardID PRIMARY KEY(CreditCardID)

ALTER TABLE DISCOUNT ADD CONSTRAINT PK_DiscountID PRIMARY KEY(DiscountID)

ALTER TABLE ROOM ADD CONSTRAINT PK_RoomID PRIMARY KEY(RoomID)

ALTER TABLE RACKRATE ADD CONSTRAINT PK_RackRateID PRIMARY KEY(RackRateID )

ALTER TABLE FOLIO ADD CONSTRAINT PK_FolioID PRIMARY KEY(FolioID)

ALTER TABLE BILLING ADD CONSTRAINT PK_FolioBillingID PRIMARY KEY(FolioBillingID)

ALTER TABLE HOTEL ADD CONSTRAINT PK_HotelIDCheck PRIMARY KEY(HotelID)

ALTER TABLE PAYMENT ADD CONSTRAINT PK_PaymentID PRIMARY KEY(PaymentID)

ALTER TABLE BILLINGCATEGORY ADD CONSTRAINT PK_BillingCategoryID PRIMARY KEY(BillingCategoryID)

ALTER TABLE TAXRATE ADD CONSTRAINT PK_TaxLocationID PRIMARY KEY(TaxLocationID)

-- Alter Foreign keys GO

ALTER TABLE RESERVATION ADD CONSTRAINT FK_RESERVATION FOREIGN KEY (CreditCardID) REFERENCES CreditCard(CreditCardID) ON UPDATE Cascade ON DELETE Cascade

ALTER TABLE CREDITCARD ADD CONSTRAINT FK_CREDITCARD FOREIGN KEY (GuestID) REFERENCES Guest(GuestID) ON UPDATE Cascade ON DELETE Cascade

ALTER TABLE ROOM ADD CONSTRAINT FK_ROOM FOREIGN KEY (HotelID) REFERENCES HOTEL(HotelID) ON UPDATE Cascade ON DELETE Cascade,

CONSTRAINT FK_ROOMTYPEID FOREIGN KEY (RoomTypeID) REFERENCES ROOMTYPE(RoomTypeID) ON UPDATE No Action ON DELETE No Action

ALTER TABLE RACKRATE ADD CONSTRAINT FK_RACKRATE FOREIGN KEY (RoomTypeID) REFERENCES ROOMTYPE(RoomTypeID) ON UPDATE Cascade ON DELETE Cascade,

CONSTRAINT FK_HOTELID2 FOREIGN KEY (HotelID) REFERENCES HOTEL(HotelID) ON UPDATE No Action ON DELETE No Action

ALTER TABLE HOTEL ADD CONSTRAINT FK_HOTEL2 FOREIGN KEY (TaxLocationID) REFERENCES TAXRATE(TaxLocationID) ON UPDATE Cascade ON DELETE Cascade

ALTER TABLE BILLING ADD CONSTRAINT FK_BILLING FOREIGN KEY (FolioID) REFERENCES FOLIO(FolioID) ON UPDATE Cascade ON DELETE Cascade,

CONSTRAINT FK_Billingcat FOREIGN KEY (BillingCategoryID) REFERENCES BILLINGCATEGORY(BillingCategoryID) ON UPDATE No Action ON DELETE No Action

-- ...... ALTER TABLE FOLIO ADD CONSTRAINT FK_BILLINGIt FOREIGN KEY (ReservationID) REFERENCES RESERVATION(ReservationID) ON UPDATE Cascade ON DELETE Cascade,

CONSTRAINT FK_ROOMIEID FOREIGN KEY (RoomID) REFERENCES ROOM(RoomID) ON UPDATE No Action ON DELETE No Action,

CONSTRAINT FK_THEDISCOUNT FOREIGN KEY (DiscountID) REFERENCES DISCOUNT(DiscountID) ON UPDATE No Action ON DELETE No Action

ALTER TABLE PAYMENT ADD CONSTRAINT FK_PAYFORIT FOREIGN KEY (FolioID) REFERENCES FOLIO(FolioID) ON UPDATE Cascade ON DELETE Cascade