4.6你完整性约束条件
(1)实体完整性
关系模型的实体完整性在CREAT TABLE中用PRIMARY KEY定义。对单属性构成的码有两种说明方法，一种是定义成列级约束条件，一种是定义为表级约束条件。对多个属性构成的码只有一种说明方法，即定于为表级约束条件。

 (2)参照完整性
关系模型的参照完整性在CREAT TABLE中用FOREIGN KEY定义哪些列为外码，用REFERENCES短语指明这些外码参照哪些表的主码。

 (3) 用户自定义完整性
用户自定义完整性就是针对某一具体应用的数据必须满足的语义要求
5 建立关系图6 创建视图
管理员信息视图
CREATE VIEW dbo.ViewAdminInfo
AS
SELECT Aid, Aname, Alimit
FROM dbo.AdminInfo

总收入视图
CREATE VIEW dbo.ViewAllCost
AS
SELECT SUM(Ccost) AS AllCost
FROM dbo.GuestInfo
标准房收入视图
CREATE VIEW dbo.ViewBiaozhunCost
AS
SELECT SUM(a.Ccost) AS BiaozhunCost
FROM dbo.GuestInfo a INNER JOIN
      dbo.RoomInfo b ON a.Rnum = b.Rnum
WHERE (b.Rtype = '标准')

收入视图
CREATE VIEW dbo.ViewCost
AS
SELECT dbo.ViewAllCost.AllCost, dbo.ViewPutongCost.putongCost,
      dbo.ViewBiaozhunCost.BiaozhunCost, dbo.ViewHaohuaCost.HaohuaCost
FROM dbo.ViewAllCost CROSS JOIN
      dbo.ViewPutongCost CROSS JOIN
      dbo.ViewBiaozhunCost CROSS JOIN
      dbo.ViewHaohuaCost

空房信息视图
CREATE VIEW dbo.ViewEmptyRoomInfo
AS
SELECT Rnum, Rtype, Rprice, Rstates
FROM dbo.RoomInfo
WHERE (Rstates = '空')

满房信息视图
CREATE VIEW dbo.ViewFullRoomInfo
AS
SELECT dbo.RoomInfo.*
FROM dbo.RoomInfo
WHERE (Rstates = '满')

客户信息视图
CREATE VIEW dbo.ViewGuestInfo
AS
SELECT dbo.GuestInfo.*
FROM dbo.GuestInfo

豪华房收入视图
CREATE VIEW dbo.ViewHaohuaCost
AS
SELECT SUM(a.Ccost) AS HaohuaCost
FROM dbo.GuestInfo a INNER JOIN
      dbo.RoomInfo b ON a.Rnum = b.Rnum
WHERE (b.Rtype = '豪华'

普通房收入视图
CREATE VIEW dbo.ViewPutongCost
AS
SELECT SUM(a.Ccost) AS PutongCost

上一页  [1] [2] [3] [4] 下一页