PLEASE answer this question using the following table i have created below thank
ID: 3851632 • Letter: P
Question
PLEASE answer this question using the following table i have created below thank you
2. Write a stored procedure named sp_InsertRoomType that can be used to insert a row of data into the RoomType table then insert the @@identity of the new room type into the RackRate table. This will require a separate procedure named sp_InsertRackRate (that will be called from within sp_InsertRoomType after the new roomtype is added).
2A. Demonstrate: Weber Suites is expanding. Using sp_InsertRoomType, Insert a new RoomType called 'MasterSuite' with a year round rate for 2016 of $299 per night. Have the stored procedure's output pass its @@identity to sp_InsertRackRate, and add the details for the MasterSuite with a rackrate description: 'Special Rate for new Master Suites'.
2B. 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:stageFARMS.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:stageFARMS.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
Explanation / Answer
1)Answer for 1st Question:
Creating a stored procedure: For ROOMTYPE Table
DELIMITER //
1A)Creating another Stored Procedure:
Creating a stored procedure: For ROOMTYPE Table
DELIMITER //
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.