上一章，根據ER圖，本章主要是用T-SQL語句建立數據庫以及各種表。

數據庫建立（T-SQL）

CREATE DATABASE RoomManagementSystem ON PRIMARY (NAME = RoomManagementSystem,FILENAME = 'F:\Myblog\RoomManagementSystem\DB\RoomManagementSystem.mdf',SIZE = 5MB,MAXSIZE = UNLIMITED,FILEGROWTH = 1 MB ) LOG ON (NAME = RoomManagementSystem_Log,FILENAME = 'F:\Myblog\RoomManagementSystem\DB\RoomManagementSystem_Log.ldf',SIZE = 3MB,MAXSIZE = 20 MB,FILEGROWTH = 10% )

建立所有的表（T-SQL）

--創建客房類型表RoomTypeInfo() CREATE TABLE RoomTypeInfo (rType_Id Char(2) not null,rType_Name char(5) not null,rType_Price decimal(18,2) not null,rType_Num int null,rType_Total int not null,rType_conf varchar(255) not null ) GO ALTER TABLE RoomTypeInfo ADD CONSTRAINT CK_rType_Price CHECK(rType_Price>0),CONSTRAINT FK_rTypeId PRIMARY KEY(rType_Id)--員工表 staff CREATE TABLE staff (Staff_ID char(8) not null,Staff_pwd nvarchar(10) not null,Staff_name nvarchar(50) not null,Staff_sex nvarchar(4) null,Staff_entrytime date null ) go alter table staff add constraint FK_StaffId primary key(Staff_ID),constraint CK_StaffSex check(Staff_sex in( '男','女'))--客戶信息表Customer CREATE TABLE Customer (C_ID char(8) not null,C_pwd nvarchar(10) not null,C_name nvarchar(50) not null,C_sex nvarchar(4) null ) go alter table Customer add constraint FK_CId primary key(C_ID),constraint CK_CSex check(C_sex in( '男','女'))--客房信息表RoomTypeInfo(RoomID,RoomtypeID,RoomNum,RoomState,...) CREATE TABLE RoomInfo (Room_ID Char(5) not null,rType_Id Char(2) not null,RoomNum Char(5) not null,Room_Floor char(1) not null,Room_State varchar(4) not null ) GO ALTER TABLE RoomInfo ADD CONSTRAINT FK_rType_Id FOREIGN KEY(rType_Id) REFERENCES RoomTypeInfo(rType_Id),CONSTRAINT CK_Room_State CHECK(Room_State in ('空閑','入住','預定','停用')),CONSTRAINT DF_Room_Stat DEFAULT('空閑') FOR Room_State,CONSTRAINT PK_RoomId primary key(Room_ID)--預約表ReservationInfo CREATE TABLE ReservationInfo (ReserveId char(15) not null,rType_Id Char(2) not null,C_ID Char(8) not null,ReserveDate date not null,CheckInDate date not null,CheckOutDate date not null,TotalDates int not null,Price int null,IsPay bit not null) GO ALTER TABLE ReservationInfo ADD CONSTRAINT FK_rTypeId1 FOREIGN KEY(rType_Id) REFERENCES RoomTypeInfo(rType_Id),CONSTRAINT FK_CID1 FOREIGN KEY(C_ID) REFERENCES Customer(C_ID),CONSTRAINT PK_ReserveId primary key(ReserveId)CREATE TABLE RoomTypeStateInfo (State_Id char(6) not null,rType_Id char(2),rType_Total int,EmptyNum int,BookNum int,StopNum int ) go alter TABLE RoomTypeStateInfo add constraint PK_StateId primary key(State_Id),CONSTRAINT FK_rTypeId2 FOREIGN KEY(rType_Id) REFERENCES RoomTypeInfo(rType_Id)--訂單表OrderInfo CREATE TABLE OrderInfo (OrderId char(15) not null,C_ID Char(8) not null,RoomNum Char(5) not null,OrderDays int not null,OrderPrice decimal(18,2) not null,OrderIntegral int not null,OrderFinishDat datetime not null ) GO ALTER TABLE OrderInfo ADD CONSTRAINT FK_OrderId FOREIGN KEY(OrderId) REFERENCES ReservationInfo(ReserveId),CONSTRAINT FK_C_ID FOREIGN KEY(C_ID) REFERENCES Customer(C_ID),CONSTRAINT CK_OrderPrice check(OrderPrice>0),CONSTRAINT PK_OrderId primary key(OrderId)--住房登記CheckingInfo CREATE TABLE CheckingInfo (OrderId char(15) not null,Room_ID Char(5) not null,C_ID Char(8) not null,Staff_ID char(8) not null,CheckInDate datetime null,CheckOutDate datetime null,isEmpty varchar(2) ) GO ALTER TABLE CheckingInfo ADD CONSTRAINT FK_RoomID foreign key (Room_ID) references dbo.RoomInfo(Room_ID),CONSTRAINT FK_CID2 FOREIGN KEY(C_ID) REFERENCES Customer(C_ID),CONSTRAINT FK_Staff_Id foreign key (Staff_ID) references dbo.staff(Staff_ID),CONSTRAINT PK_OrderId1 primary key(OrderId)--消費結賬 Consumption CREATE TABLE Consumption (OrderId char(15) not null,C_ID Char(8) not null,TotalDays int not null,SumPrice int not null,ActualPaid int not null,Balance int not null,Integral int not null,FinishDate datetime not null ) ALTER TABLE Consumption add CONSTRAINT PK_OrderId2 primary key(OrderId),CONSTRAINT FK_OrderId2 foreign key (OrderId) references OrderInfo(OrderId),CONSTRAINT FK_CID3 foreign key (C_ID) references Customer(C_ID)

總結

以上是生活随笔為你收集整理的酒店客房管理系统之系统实施--数据库的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。