USE masterGO--創建數據庫HotelIF EXISTS (SELECT name FROM sys.databases WHERE name = 'Hotel')    DROP DATABASE HotelGOexec sp_configure 'show advanced options',1reconfiguregoexec sp_configure 'xp_cmdshell',1reconfiguregouse mastergoEXEC xp_cmdshell 'mkdir E:/DB', NO_OUTPUTCREATE DATABASE [Hotel] ON  PRIMARY ( NAME = 'Hotel', FILENAME = 'E:/DB/Hotel.mdf' , SIZE = 3072KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB ) LOG ON ( NAME = 'Hotel_log', FILENAME = 'E:/DB/Hotel_log.ldf' , SIZE = 1024KB , MAXSIZE = 20MB , FILEGROWTH = 10%)GO--創建表USE HotelGO--創建結賬狀態表ResideStateIF EXISTS (SELECT * FROM sysobjects WHERE name = 'ResideState')    DROP TABLE ResideStateGOCREATE TABLE ResideState(    ResideId int IDENTITY(1,1) NOT NULL, --結賬狀態ID    ResideName varchar(50) NULL          --結賬狀態名稱)ALTER TABLE ResideState  ADD CONSTRAINT PK_ResideState PRIMARY KEY (ResideId)GO--創建客房類型表RoomTypeIF EXISTS (SELECT * FROM sysobjects WHERE name = 'RoomType')    DROP TABLE RoomTypeGOCREATE TABLE RoomType (    TypeID int IDENTITY(1,1) NOT NULL,--客房類型id    TypeName nvarchar(50) NULL,          --客房類型名稱    TypePrice decimal(18, 2) NULL     --客房類型價格)GOALTER TABLE RoomType  ADD CONSTRAINT PK_RoomType PRIMARY KEY (TypeID)GOALTER TABLE RoomType  ADD CONSTRAINT CK_RoomPrice CHECK(TypePrice >= 0)GO--創建客房狀態表RoomStateIF EXISTS (SELECT * FROM sysobjects WHERE name = 'RoomState')    DROP TABLE RoomStateGOCREATE TABLE RoomState (    RoomStateID int IDENTITY(1,1) NOT NULL,--房間狀態id    RoomStateName nvarchar(20) NULL        --房間狀態名稱)GOALTER TABLE RoomState   ADD CONSTRAINT PK_RoomState PRIMARY KEY (RoomStateID)GO--創建客房信息表RoomIF EXISTS (SELECT * FROM sysobjects WHERE name = 'Room')    DROP TABLE RoomGOCREATE TABLE Room (    RoomID int NOT NULL,                 --房間id    Description nvarchar(200) NOT NULL,  --房間描述    BedNum int NULL,                     --床位數    GuestNum int NULL,                   --房客數目    RoomStateID int NOT NULL,            --房間狀態id    RoomTypeID int NOT NULL              --客房類型id)GOALTER TABLE Room  ADD CONSTRAINT PK_RoomID PRIMARY KEY (RoomID)GOALTER TABLE Room  ADD CONSTRAINT DF_RoomStateID  DEFAULT (2) FOR RoomStateID,  CONSTRAINT DF_BedNum  DEFAULT (2) FOR BedNum,  CONSTRAINT DF_GuestNum DEFAULT (0) FOR GuestNum,  CONSTRAINT CK_GuestNum CHECK(GuestNum >= 0),  CONSTRAINT FK_RoomStateID FOREIGN KEY(RoomStateID) REFERENCES RoomState(RoomStateID),  CONSTRAINT FK_RoomTypeID FOREIGN KEY(RoomTypeID) REFERENCES RoomType(TypeID)GO--創建客人信息表GuestRecordIF EXISTS (SELECT * FROM sysobjects WHERE name = 'GuestRecord')    DROP TABLE GuestRecordGOCREATE TABLE GuestRecord (    GuestID int IDENTITY(1,1) NOT NULL,--入住流水ID    IdentityID varchar(50) NOT NULL,   --身份證號    GuestName nvarchar(20) NOT NULL,   --客人姓名    RoomID int NULL,                   --客房ID    ResideID int NULL,                 --入住狀態ID    ResideDate datetime NULL,          --入住日期    LeaveDate datetime NULL,           --結賬日期    Deposit decimal(18, 2) NULL,       --押金    TotalMoney decimal(18, 2) NULL     --總金額)GOALTER TABLE GuestRecord  ADD CONSTRAINT PK_GuestID PRIMARY KEY(GuestID),  CONSTRAINT DF_ResideID DEFAULT (1) FOR ResideID,  CONSTRAINT CK_LeaveDate CHECK (LeaveDate>=ResideDate),  CONSTRAINT FK_RoomID FOREIGN KEY(RoomID) REFERENCES Room(RoomID),  CONSTRAINT FK_ResideID FOREIGN KEY(ResideID) REFERENCES ResideState(ResideID)GO--插入數據--數據插入結賬狀態表ResideStateINSERT INTO ResideState (ResideName) VALUES('未結帳')INSERT INTO ResideState (ResideName) VALUES('結帳')--數據插入客房類型表RoomTypeINSERT INTO RoomType (TypeName, TypePrice) VALUES ('標準間',180)INSERT INTO RoomType (TypeName, TypePrice)  VALUES ('單人間',128)INSERT INTO RoomType (TypeName, TypePrice) VALUES ('三人間',208)INSERT INTO RoomType (TypeName, TypePrice)  VALUES ('單人間',99)INSERT INTO RoomType (TypeName, TypePrice) VALUES ('總統套房',998)INSERT INTO RoomType (TypeName, TypePrice)  VALUES ('長包房',108)INSERT INTO RoomType (TypeName, TypePrice)  VALUES ('豪華標準間',268)INSERT INTO RoomType (TypeName, TypePrice)  VALUES ('單人套房',368)INSERT INTO RoomType (TypeName, TypePrice)  VALUES ('雙人套房',568)--數據插入客房狀態表RoomStateINSERT INTO RoomState (RoomStateName)  VALUES ('已入住')INSERT INTO RoomState (RoomStateName)  VALUES ('空閑')INSERT INTO RoomState (RoomStateName)  VALUES ('維修')--數據插入客房信息表RoomINSERT INTO Room (RoomID,Description,BedNum,GuestNum,RoomStateID,RoomTypeID) VALUES (1008,'這是雙人標準間',2,2,1,1)INSERT INTO Room (RoomID,Description,BedNum,GuestNum,RoomStateID,RoomTypeID) VALUES (1018,'這是雙人標準間',2,0,2,1)INSERT INTO Room (RoomID,Description,BedNum,GuestNum,RoomStateID,RoomTypeID) VALUES (1028,'這是雙人標準間',2,1,1,1)INSERT INTO Room (RoomID,Description,BedNum,GuestNum,RoomStateID,RoomTypeID) VALUES (1038,'這是雙人標準間',2,0,3,1)INSERT INTO Room (RoomID,Description,BedNum,GuestNum,RoomStateID,RoomTypeID) VALUES (1048,'這是單人間',1,1,1,1)INSERT INTO Room (RoomID,Description,BedNum,GuestNum,RoomStateID,RoomTypeID) VALUES (1058,'這是單人間',1,1,1,1)INSERT INTO Room (RoomID,Description,BedNum,GuestNum,RoomStateID,RoomTypeID) VALUES (1068,'這是單人套房',1,1,1,8)INSERT INTO Room (RoomID,Description,BedNum,GuestNum,RoomStateID,RoomTypeID) VALUES (1078,'這是單人套房',1,0,2,8)INSERT INTO Room (RoomID,Description,BedNum,GuestNum,RoomStateID,RoomTypeID) VALUES (1088,'這是豪華雙人標準間',2,1,1,7)INSERT INTO Room (RoomID,Description,BedNum,GuestNum,RoomStateID,RoomTypeID) VALUES (1098,'這是豪華雙人標準間',2,0,2,7)--數據插入客人信息表GuestRecordINSERT INTO GuestRecord (IdentityID,GuestName,RoomID,ResideDate,LeaveDate,Deposit,TotalMoney,ResideID) VALUES ('11010119910101001x','王笑',1008,'2009-9-9 12:30:00','2009-9-10 11:30:02',1000,180,2)INSERT INTO GuestRecord (IdentityID,GuestName,RoomID,ResideDate,LeaveDate,Deposit,TotalMoney) VALUES ('110101199110100114','張淼',1008,'2009-9-9 12:30:00','2009-9-10 11:30:02',1000,180)INSERT INTO GuestRecord (IdentityID,GuestName,RoomID,ResideDate,LeaveDate,Deposit,TotalMoney) VALUES ('230121197902030121','劉元元',1028,'2009-9-20 22:23:20',null,3000,null)INSERT INTO GuestRecord (IdentityID,GuestName,RoomID,ResideDate,LeaveDate,Deposit,TotalMoney) VALUES ('321007198606161231','丁一',1048,'2009-9-29 02:30:40',null,1000,null)INSERT INTO GuestRecord (IdentityID,GuestName,RoomID,ResideDate,LeaveDate,Deposit,TotalMoney) VALUES ('210119760210010083','趙玲',1058,'2009-9-18 16:33:13',null,800,null)INSERT INTO GuestRecord (IdentityID,GuestName,RoomID,ResideDate,LeaveDate,Deposit,TotalMoney) VALUES ('21201019910710001x','譚壇',1068,'2009-10-3 6:36:09',null,1500,null)INSERT INTO GuestRecord (IdentityID,GuestName,RoomID,ResideDate,LeaveDate,Deposit,TotalMoney) VALUES ('110102197801070121','周舟',1088,'2009-10-4 7:50:40',null,5000,null)