Hey guys, just wondering if you could all point me in the right direction here. I'm just starting out using Microsoft SQL server Management studio, just trying to make a basic database for Hotel/Housekeeping, any tips, suggestions would help!! Thanks in advance!
Here is my code so for:
CREATE DATABASE HOTELHOUSEKEEPING
CREATE TABLE RESERVATION
(
RESERVATION_ID int NOT NULL,
ROOM_NUM int NOT NULL,
RESERVATION_NAME varchar(100) NOT NULL,
HOUSEKEEPING_STATUS int NOT NULL,
RESERVATION_START datetime NOT NULL,
RESERVATION_END datetime NOT NULL,
CONSTRAINT "PK_RESERVATION" PRIMARY KEY CLUSTERED
("RESERVATION_ID"),
);
CREATE TABLE ROOMS
(
ROOM_NUM int NOT NULL,
ROOM_SIZE varchar(5) NOT NULL,
HOUSEKEEPING_STATUS int NOT NULL,
CONSTRAINT "PK_ROOMS" PRIMARY KEY CLUSTERED
("ROOM_NUM"),
);
CREATE TABLE HOUSEKEEPING
(
HOUSEKEEPING_STATUS int not null,
ROOM_NUM int not null,
RESERVATION_ID int NULL,
CONSTRAINT "PK_HOUSEKEEPING_STATUS" PRIMARY KEY CLUSTERED
("HOUSEKEEPING_STATUS"),
);
ALTER TABLE RESERVATION
ADD CONSTRAINT "FK_RESERVATION_ROOMS" FOREIGN KEY
("ROOM_NUM") REFERENCES "dbo"."ROOMS" ("ROOM_NUM");
ALTER TABLE RESERVATION
ADD CONSTRAINT "FK_RESERVATION_HOUSEKEEPING" FOREIGN KEY
("HOUSEKEEPING_STATUS") REFERENCES "dbo"."HOUSEKEEPING" ("HOUSEKEEPING_STATUS");
ALTER TABLE HOUSEKEEPING
ADD CONSTRAINT "FK_HOUSEKEEPING_ROOMS" FOREIGN KEY
("ROOM_NUM") REFERENCES "dbo"."ROOMS" ("ROOM_NUM");
ALTER TABLE ROOMS
ADD CONSTRAINT "FK_ROOMS_HOUSEKEEPING" FOREIGN KEY
("HOUSEKEEPING_STATUS") REFERENCES "dbo"."HOUSEKEEPING" ("HOUSEKEEPING_STATUS");
INSERT "RESERVATION" VALUES('11',’101’,’Drew Klemz’,Clean’,’12/01/2016’,’12/12/2016’)
INSERT "RESERVATION" VALUES('21',’102’,’Bill Johnson’,Clean’,’11/30/2016’,’12/12/2016’)
INSERT "RESERVATION" VALUES('31',’103’,’Jason Frees’,Clean’,’12/01/2016’,’12/10/2016’)
INSERT "RESERVATION" VALUES('41',’104’,’Sarah Gieseke’,Clean’,’12/01/2016’,’12/07/2016’)
INSERT "RESERVATION" VALUES('51',’105’,’Tony Newell’,Clean’,’12/01/2016’,’12/22/2016’)
INSERT "ROOMS" VALUES(’101’,’Queen’,’Clean’)
INSERT "ROOMS" VALUES(’102’,’King’,’Clean’)
INSERT "ROOMS" VALUES(’103’,’King’,’Clean’)
INSERT "ROOMS" VALUES(’104’,’Queen’,’Clean’)
INSERT "ROOMS" VALUES(’105’,’King’,’Clean’)
INSERT "ROOMS" VALUES(’106’,’King’,’Clean’)
INSERT "ROOMS" VALUES(’201’,’Queen’,’Clean’)
INSERT "ROOMS" VALUES(’202’,’Queen’,’Dirty’)
INSERT "ROOMS" VALUES(’203’,’Queen’,’Dirty’)
INSERT "ROOMS" VALUES(’204’,’King’,’Dirty’)
INSERT "HOUSEKEEPING" VALUES(’Dirty’,’101’,’11’)
INSERT "HOUSEKEEPING" VALUES(’Dirty’,’102’,’21’)
INSERT "HOUSEKEEPING" VALUES(’Dirty’,’103’,’31’)
INSERT "HOUSEKEEPING" VALUES(’Dirty’,’104’,’41’)
INSERT "HOUSEKEEPING" VALUES(’Dirty’,’105’,’51’)
INSERT "HOUSEKEEPING" VALUES(’Dirty’,’106’,’’)
INSERT "HOUSEKEEPING" VALUES(’Clean’,’201’,’’)
INSERT "HOUSEKEEPING" VALUES(’Dirty’,’202’,’’)
INSERT "HOUSEKEEPING" VALUES(’Dirty’,’203’,’’)
INSERT "HOUSEKEEPING" VALUES(’Dirty’,’204’,’’)
Also I have a few business rules I have to follow that I am trying to create
• A Room cannot be Occupied and clean, but a room can be Not occupied and Not clean, meaning it needs to be cleaned.
• Room that is occupied and dirty can request housekeeping, meaning change sheets, pillow cases, fresh towels, etc (this is what Housekeeping does)
• A room can only have one Customer, but a customer may have many rooms.
I also have to make some views, I think I know how to make these but I want to make sure I'm on the right track before continuing.