SQL HELP- The database will be posted believe that will be needed to answer the

ID: 3853212 • Letter: S

Question

SQL HELP- The database will be posted believe that will be needed to answer the question.. Thank you for your help

3B. Demonstrate this function TWICE with a query that contains folio ids (1 and 4).

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:stageFARMSldf', --Change the path for local

SIZE = 4MB, -- 500kb, 500, TB, GB -- 10% Read 25% R/Write

MAXSIZE = 13MB,

FILEGROWTH = 10% -- 5MB

)

-- With the 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

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

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

BULK INSERT RESERVATION FROM 'c:stagearms1-1Reservation.txt' WITH (FIELDTERMINATOR = '|', FIRSTROW =1)

BULK INSERT GUEST FROM 'c:stagearms1-1Guest.txt' WITH (FIELDTERMINATOR = '|', FIRSTROW =1)

BULK INSERT CREDITCARD FROM 'c:stagearms1-1CreditCard.txt' WITH (FIELDTERMINATOR = '|', FIRSTROW =1)

BULK INSERT ROOMTYPE FROM 'c:stagearms1-1RoomType.txt' WITH (FIELDTERMINATOR = '|', FIRSTROW =1)

BULK INSERT DISCOUNT FROM 'c:stagearms1-1Discount.txt' WITH (FIELDTERMINATOR = '|', FIRSTROW =1)

BULK INSERT ROOM FROM 'c:stagearms1-1Room.txt' WITH (FIELDTERMINATOR = '|', FIRSTROW =1)

BULK INSERT RACKRATE FROM 'c:stagearms1-1RackRate.txt' WITH (FIELDTERMINATOR = '|', FIRSTROW =1)

BULK INSERT FOLIO FROM 'c:stagearms1-1Folio.txt' WITH (FIELDTERMINATOR = '|', FIRSTROW =1)

BULK INSERT BILLING FROM 'c:stagearms1-1Billing.txt' WITH (FIELDTERMINATOR = '|', FIRSTROW =1)

BULK INSERT HOTEL FROM 'c:stagearms1-1Hotel.txt' WITH (FIELDTERMINATOR = '|', FIRSTROW =1)

BULK INSERT PAYMENT FROM 'c:stagearms1-1Payment.txt' WITH (FIELDTERMINATOR = '|', FIRSTROW =1)

BULK INSERT BILLINGCATEGORY FROM 'c:stagearms1-1BillingCategory.txt' WITH (FIELDTERMINATOR = '|', FIRSTROW =1)

BULK INSERT TAXRATE FROM 'c:stagearms1-1TaxRate.txt' WITH (FIELDTERMINATOR = '|', FIRSTROW =1)

Explanation / Answer

DESCRIPTION:

IF EXISTS : This will select the name 'FARMS' from the database table then as per the drop condition the database table FARMS will be deleted(dropped)

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

DROP DATABASE FARMs

CREATING DATABASE TABLES

THE PRIMARY TABLE IS CREATED//PRIMARY TABLE

GO : This will pause the code to make sure everything has happened

ON PRIMARY

(NAME = 'FARMS',//name

FILENAME = 'C:stageFARMS.mdf', --Change the path for local//address

SIZE = 12MB, -- 500kb, 500, TB, GB//size of table

MAXSIZE = 50 MB,//maximum size of table

FILEGROWTH = 10% -- 5MB//file growth range

)

//LOGON TABLE

LOG ON

(NAME = 'FARMS_Log',//name

FILENAME = 'C:stageFARMSldf', --Change the path for local//address

SIZE = 4MB, -- 500kb, 500, TB, GB -- 10% Read 25% R/Write//size of table

MAXSIZE = 13MB,//maximum size of table

FILEGROWTH = 10% -- 5MB//file growth range

)

CREATING INDIVIDUAL TABLES FOR OPERATIONS

USE FARMS

CREATE TABLE RESERVATION //reservation table

(

ReservationID smallint NOT NULL IDENTITY(5000,1), //booking id

ReservationDate date NOT NULL, //date should not be null

ReservationStatus char(1) NOT NULL, //status should not be null i.e., it should be either failed or confirmed

ReservationComments varchar(200) NULL, //remarks

CreditCardID smallint NOT NULL //credit card

)

GUEST TABLE FOR GUEST DETAILS

CREATE TABLE GUEST//guest details

(

GuestID smallint NOT NULL IDENTITY(1500,1),//guest id

GuestFirst varchar(20) NOT NULL,//guest first name

GuestLast varchar(20) NOT NULL,//guest last name

GuestAddress varchar(30) NOT NULL,//address

guestAddress2 varchar(10) null,//optional address

GuestCity varchar(20) NOT NULL,//city

GuestState char(2) NULL,//state

GuestPostalCode char(10) NOT NULL,//postal code

GuestCountry varchar(20) NOT NULL,//country

GuestPhone varchar(20) NULL,//mobile number

GuestEmail varchar(20) NULL,//email id

GuestComments varchar(200) NULL//remarks

)

GUEST ROOM DETAILS TABLE

CREATE TABLE ROOMTYPE //room type details

(

RoomTypeID smallint NOT NULL IDENTITY(1,1),//room number

RTDescription varchar(200) NOT NULL.//room description

)

CREDIT CARD DETAILS TABLE

CREATE TABLE CREDITCARD // credit details

(

CreditCardID smallint NOT NULL IDENTITY(1,1),//credit card ID

GuestID smallint NOT NULL,//Guest ID

CCType varchar(5) NOT NULL,//card number details

CCNumber varchar(16) NOT NULL,//cvv number

CCCompany varchar(40) NULL,//company

CCCardHolder varchar(40) NOT NULL,//card holder name

CCExpiration smalldatetime NOT NULL//expiry date

)

CREATE DISCOUNTS TABLE

CREATE TABLE DISCOUNT

(

DiscountID smallint NOT NULL IDENTITY(1,1),//Discount id

DiscountDescription varchar(50) NOT NULL,//description

DiscountExperiation date NOT NULL,//expiry date

DiscountRules varchar(100) NULL,//rules

DiscountPercent decimal(4,2) NULL,//discount percentage

DiscountAmount smallmoney NULL//discount amount

)

CREATE ROOM DETAILS TABLE

CREATE TABLE ROOM //room

(

RoomID smallint NOT NULL IDENTITY(1,1),//room id

RoomNumber varchar(5) NOT NULL,//room number

RoomDescription varchar(200) NOT NULL,//description

RoomSmoking bit NOT NULL,//no smoking/smoking

RoomBedConfiguration char(2) NOT NULL,//bed configuration

HotelID smallint NOT NULL,//hotel id

RoomTypeID smallint NOT NULL//room type

)

CREATE RACKRATE TABLE

CREATE TABLE RACKRATE

(

RackRateID smallint NOT NULL IDENTITY(1,1),//rack rate ID

RoomTypeID smallint NOT NULL,//type

HotelID smallint NOT NULL,//hotel id

RackRate smallmoney NOT NULL,//small amount

RackRateBegin date NOT NULL,//begin date

RackRateEnd date NOT NULL,//end date

RackRateDescription varchar(200) NOT NULL//room description

)

CREATE FOLIO TABLE

CREATE TABLE FOLIO

(

FolioID smallint NOT NULL IDENTITY(1,1),//ID

ReservationID smallint NOT NULL,//Reservation ID

GuestID smallint NOT NULL,//guest id

RoomID smallint NOT NULL,//room id

QuotedRate smallmoney NOT NULL,//rate

CheckinDate smalldatetime NOT NULL,//check-in date

Nights tinyint NOT NULL,//number of nights

FStatus char(1) NOT NULL,//fstatus

Comments varchar(200) NULL,//comments

DiscountID smallint NOT NULL//discounts ID

)

CREATE TABLE BILLING DETAILS

CREATE TABLE BILLING

(

FolioBillingID smallint NOT NULL IDENTITY (1,1),//id

FolioId smallint NOT NULL,//folio id

BillingCategoryID smallint NOT NULL,//category id

BillingDescription char(30) NOT NULL,//description

BillingAmount smallmoney NOT NULL,//small amount

BillingItemQty tinyint NOT NULL,

BillingItemDate date NOT NULL,

)

CREATE HOTEL TABLE

CREATE TABLE HOTEL

(

HotelID smallint NOT NULL IDENTITY(2100,100),//hotel id

HotelName varchar(30) NOT NULL,//name

HotelAddress varchar(30) NOT NULL,//address

HotelCity varchar(20) NOT NULL,//city

HotelState char(2) NULL,//state

HotelCountry varchar(20) NOT NULL,//country

HotelPostalCode char(10) NOT NULL,//postal code

HotelStarRating char(1) NULL,//star reviews

HotelPictureLink varchar(100) NULL,//pictutre

TaxLocationID smallint NOT NULL//tax ID

)

CREATE PAYMENTS TABLE

CREATE TABLE PAYMENT

(

PaymentID smallint NOT NULL IDENTITY(8000,1),//Payment ID

FolioID smallint NOT NULL,//Folio ID

PaymentDate date NOT NULL,//Payment Date

PaymentAmount smallmoney NOT NULL,//Payment amounts

PaymentComments varchar(200) NULL//Payments Remarks

)

CREATE BILLING TABLE

CREATE TABLE BILLINGCATEGORY

(

BillingCategoryID smallint NOT NULL IDENTITY(1,1),//category

BillingCatDescription varchar(30) NOT NULL,//description

BillingCatTaxable bit NOT NULL//taxable

)

CREATE TAX RATES TABLE

CREATE TABLE TAXRATE

(

TaxLocationID smallint NOT NULL IDENTITY(1,1),//tax location

TaxDescription varchar(30) NOT NULL,//tax description

RoomTaxRate decimal(6,4) NOT NULL,//room tax rate

SalesTaxRate decimal(6,4)//sales tax rate

)

ALTER: ALTERING IS THE CHANGING OF EXISTING DATA IN THE TABLES

ALTERING RESERVES TABLE

ALTER TABLE RESERVATION

ADD CONSTRAINT PK_ReservationID

PRIMARY KEY (ReservationID)

ALTERING GUEST DETAILS TABLE

ALTER TABLE GUEST

ADD CONSTRAINT PK_GuestID

PRIMARY KEY (GuestID)

ALTERING ROOM DETAILS TABLE

ALTER TABLE ROOMTYPE

ADD CONSTRAINT PK_RoomTypeID

PRIMARY KEY(RoomTypeID)

ALTERING CREDIT DETAILS TABLE

ALTER TABLE CREDITCARD

ADD CONSTRAINT PK_CreditCardID

PRIMARY KEY(CreditCardID)

ALTERING DISCOUNTS TABLE

ALTER TABLE DISCOUNT

ADD CONSTRAINT PK_DiscountID

PRIMARY KEY(DiscountID)

ALTERING ROOM DETAILS TABLE

ALTER TABLE ROOM

ADD CONSTRAINT PK_RoomID

PRIMARY KEY(RoomID)

ALTERING RACKTABLE DETAILS

ALTER TABLE RACKRATE

ADD CONSTRAINT PK_RackRateID

PRIMARY KEY(RackRateID )

ALTERING FOLIO DETAILS TABLE

ALTER TABLE FOLIO

ADD CONSTRAINT PK_FolioID

PRIMARY KEY(FolioID)

ALTERING BILLINGS TABLE

ALTER TABLE BILLING

ADD CONSTRAINT PK_FolioBillingID

PRIMARY KEY(FolioBillingID)

ALTERING HOTEL DETAILS TABLE

ALTER TABLE HOTEL

ADD CONSTRAINT PK_HotelIDCheck

PRIMARY KEY(HotelID)

ALTERING PAYMENTS TABLE

ALTER TABLE PAYMENT

ADD CONSTRAINT PK_PaymentID

PRIMARY KEY(PaymentID)

ALTERING BILLING TABLES

ALTER TABLE BILLINGCATEGORY

ADD CONSTRAINT PK_BillingCategoryID

PRIMARY KEY(BillingCategoryID)

ALTERING TAX RATES TABLE

ALTER TABLE TAXRATE

ADD CONSTRAINT PK_TaxLocationID

PRIMARY KEY(TaxLocationID)

FOREIGN KEY: FOREIGN KEY IS THE VALUE OF A VARIABLE WHICH CANNOT BE NULLVALUE OR REPEATED VALUE THAT IS A FOREIGN KEY MUST HAVE A UNIQUE VALUE