客房管理系统论文 第3页
FROM dbo.GuestInfo a INNER JOIN
dbo.RoomInfo b ON a.Rnum = b.Rnum
WHERE (b.Rtype = '普通')
房间信息视图
CREATE VIEW dbo.ViewRoomInfo
AS
SELECT dbo.RoomInfo.*
FROM dbo.RoomInfo
7 创建触发器
在表GuestInfo中，创建一个触发器：
CREATE TRIGGER insert_tri ON dbo.GuestInfo
FOR INSERT
AS
update RoomInfo
set Rstates='满',Cid=(select Cid from inserted)
where Rnum=(select Rnum from inserted)
功能：当向表GuestInfo中插入一个客户信息时，触发表RoomInfo的某房间的状态变为“满”
8 数据库实施阶段
8.1建立管理员信息列表
CREATE TABLE [dbo].[AdminInfo] (
[Aid] [int] IDENTITY (1, 1) NOT NULL ,
[Aname] [varchar] (16) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[Amima] [varchar] (16) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[Alimit] [char] (1) COLLATE Chinese_PRC_CI_AS NOT NULL
) ON [PRIMARY]
GO
8.2建立入住时间列表
CREATE TABLE [dbo].[timeInfo] (
[Cpnum] [char] (18) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[Cadd] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[Cintime] [smalldatetime] NOT NULL ,
[Cday] [tinyint] NULL ,
[Rprice] [smallmoney] NOT NULL ,
[Rstates] [char] (2) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[Cid] [int] NULL
) ON [PRIMARY]
GO8.3建立客户信息列表
CREATE TABLE [dbo].[GuestInfo] (
[Cid] [int] IDENTITY (1, 1) NOT NULL ,
[Cname] [varchar] (8) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[Csex] [char] (2) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[Cage] [tinyint] NOT NULL ,
[Cpnum] [char] (18) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[Cadd] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[Cintime] [smalldatetime] NOT NULL ,
[Cday] [tinyint] NULL ,
[Rnum] [char] (5) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[Couttime] [smalldatetime] NULL ,
[Cdeposit] [int] NOT NULL ,
[Ccost] [int] NULL ,
[Aid] [int] NOT NULL
) ON [PRIMARY]
GO8.4建立客房费用列表
CREATE TABLE [dbo].[moneyInfo]（
[Cname] [varchar] (8) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[Csex] [char] (2) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[Cage] [tinyint] NOT NULL ,
[Cpnum] [char] (18) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[Cadd] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[Cintime] [smalldatetime] NOT NULL ,
[Amima] [varchar] (16) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[Alimit] [char] (1) COLLATE Chinese_PRC_CI_AS NOT NULL
) ON [PRIMARY]
GO8.5建立房间信息列表
CREATE TABLE [dbo].[RoomInfo] (
[Rnum] [varchar] (5) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[Rtype] [char] (4) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[Rprice] [smallmoney] NOT NULL ,
[Rstates] [char] (2) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[Cid] [int] NULL
) ON [PRIMARY]
GO
9 设计结果
（1）管理员表查询结果