主键约束 : Booking 中如果选择 hotelno, guestno, roomno 为主键分析 : 这就意味着一个客户预订某个旅馆的某个房间在整个一生中都只能订一次 ; 显然不符合实际情况如果选择 hotelno,guestno,datefrom 为主键分析 : 这就意味着一个客户

Size: px

Start display at page:

Download "主键约束 : Booking 中如果选择 hotelno, guestno, roomno 为主键分析 : 这就意味着一个客户预订某个旅馆的某个房间在整个一生中都只能订一次 ; 显然不符合实际情况如果选择 hotelno,guestno,datefrom 为主键分析 : 这就意味着一个客户"

稔医鄂
5 years ago
Views:

1 主键约束 : Booking 中如果选择 hotelno, guestno, roomno 为主键分析 : 这就意味着一个客户预订某个旅馆的某个房间在整个一生中都只能订一次 ; 显然不符合实际情况如果选择 hotelno,guestno,datefrom 为主键分析 : 这就意味着一个客户某天在某个旅馆只能订一个房间 ; 实际中, 某个客户可能在某天在某个旅馆要订多个房间, 例如在组织会议时, 就有这种情况, 因此不能满足业务要求如果选择 hotelno,roomno,guestno,datefrom 为主键考虑到某个旅馆的某个房间在某天只可能被一个客户预订, 因此不是最小属性集合正确的主键选择应该是 hotelno,roomno,datefrom 如果选择 hotelno,roomno, datefrom,dateto 为主键从语义上考虑, 没有问题, 能够确保行记录的唯一性但是从逻辑上考虑, 则有问题, 因为它意味着可以出现 hotelno,roomno, datefrom 字段值相同, 但是 dateto 字段值不相同的多行记录, 这就违背了某一房间在某一天只能被预定一次的约束业务约束 : (a) 一个旅馆对应一个旅馆编号, 没有重复的旅馆编号 ; 评析 : 这应

2 该是主键约束, 不是语义约束 ; (b). 价格必须在 10 美元到 1000 美元之间评析 : 这应该是域约束, 不是语义约束 ; (c) booking 表中的 hotelno 字段的取值必须要在 Hotel 表中已存在评析 : 这不是语义约束, 而是外键约束 ; (d). datefrom 和 dateto 必须大于今天日期 ; 评析 : 这是语义约束问题 :SQL 中如何表达该约束 ; (e) 比如规定酒店的数目, 超过该数目则不能添加该酒店评析 : 不符合实际情况, 不构成一条语义约束 (f) 一个客户可以定多个房间, 一个房间在一个时间只能有一个客户定 ; 这是主键约束, 不是语义约束 ; (g)dateto 必须大于或等于 datefrom 这是语义约束问题 :SQL 中如何表达该约束 ; 因此, 在考虑表的约束时, 要依次按照主键约束, 外键约束, 域约束, 语义约束来考虑, 要有先后顺序, 不能随意主键约束对任何表都是是必要的, 要最先考虑的然后是外键约束, 再接下来是域约束, 最后才是业务规则约束 ( 即语义约束 ) (yjm) 某一旅馆的某一个房间在某一天的预定在 booking 表中不能有重叠

3 // 对于某个客户要在某个旅馆, 定某个日期段 ( 从 startdate 至 enddate) 的某个类型的房间 : 列出可选的房间列表 (hotelname, roomno, type, price); 写一个存储过程 specified_hotelname specified_enddate) specified_hotelname AS hotelname, roomno, type, price FROM room WHERE hotelno =(SELECT hotelno FROM hotel WHERE hotelname=@ specified_hotelname)and type=@specified_type AND roomno NOT IN ( SELECT hotelno, roomno INTO temptbl FROM booking WHERE hotelno =(SELECT hotelno FROM hotel WHERE hotelname=@specified_hotelname)and specified_startdate AND datefrom specified_enddate) OR specified_startdate AND dateto specified_enddate) OR (datefrom specified_startdate AND dateto specified_enddate)) )

4 在 booking 表中, 某一旅馆的某一个房间在某一天不能出现重复预定的情况某个客户想要在 hotelno 为 01 的旅馆预定从 2012/4/10 至 2012/4/12 的一个双人间房间, 请列出他可选的房间列表 (roomno) SELECT roomno FROM room WHERE hotelno = 01 AND type= 双人间 AND roomno NOT IN ( SELECT roomno FROM booking WHERE hotelno = 01 AND ((datefrom>= 2012/04/10 AND datefrom <= 2012/04/12 ) OR (dateto>= 2012/04/10 AND dateto <= 2012/04/12 ) OR (datefrom < 2012/04/10 AND dateto > 2012/04/12 )) ) 3.22 今天是 2012/03/28, 问昨天每个旅馆空房损失分别是多少? SELECT hotelname SUM(price) AS lossprice FROM booking,hotel,room WHERE booking.hotelno=hotel.hotelno

5 =room.hotelno AND booking.roomno=room.roomno AND dateto< GROUP BY hotelname 评析 :1) 联接问题 : 必须按照表的内在关系来写等式, 必须是 : (B.hotelNo=R.hotelNo AND B.roomNo=R.roomNo) AND R.hotelNo = H.hotelNo 大家可以上机时比较上述联接与下面的联接 B.hotelNo=H.hotelNo AND B.roomNo=R.roomNo AND R.hotelNo = H.hotelNo 间结果的差异性 2) 日期条件的判别 : 应该是 : datefrom = today OR (datefrom < today AND dateto>=today) 3) 得出的不是空房损失, 而是收入, 因此不符合要求 ; 4) 考虑会不会存在如下情况 : 某个旅馆的某个房间会出现多行记录现在是考虑预订, 某个旅馆的某个房间在某天只可能存在一个预定, 因此不会有多行记录已被预定的房间 : SELECT hotelname, R.hotelNo, R.roomNo, price FROM booking AS B, hotel AS H, room AS R WHERE (B.hotelNo=R.hotelNo AND B.roomNo=R.roomNo) AND R.hotelNo = H.hotelNo AND datefrom = today OR (datefrom < today AND dateto>=today);

6 5) 知道了已被预定的房间, 空房就是所有房间减去已被预定的房间 ; SELECT hotelname, R.hotelNo, R.roomNo, price FROM hotel AS H, room AS R WHERE R.hotelNo = H.hotelNo EXCEPT SELECT hotelname, R.hotelNo, R.roomNo, price FROM booking AS B, hotel AS H, room AS R WHERE (B.hotelNo=R.hotelNo AND B.roomNo=R.roomNo) AND R.hotelNo = H.hotelNo AND datefrom = today OR (datefrom < today AND dateto>=today) 6) 上面得出的是所有的空房清单, 题目是要来求每个旅馆的空房损失, 而不是每个空房间的损失, 因此还要按照旅馆来进行分组统计 ; SELECT hotelname,hotelno, SUM(price)AS lostincome FROM ( SELECT hotelname, R.hotelNo, R.roomNo, price FROM hotel AS H, room AS R WHERE R.hotelNo = H.hotelNo EXCEPT SELECT hotelname, R.hotelNo, R.roomNo, price FROM booking AS B, hotel AS H, room AS R WHERE (B.hotelNo=R.hotelNo AND B.roomNo=R.roomNo) AND R.hotelNo = H.hotelNo AND datefrom = today OR (datefrom < today AND dateto>=today)

7 ) GROUP BY hotelname,hotelno ; CREATE TABLE Booking ( hotelno VARCHAR(4), guestid VARCHAR(12) NOT NULL, datefrom DATE, dateto DATE NOTNULL, roomno CHAR(4), PRIMARY(hotelNo, roomno, datefrom) FOREIGN KEY hotelno, roomno REFERENCES room(hotelno, roomno) FOREIGN KEY guestid REFERENCES guest(guestid) CHECK (dateto >=datefrom) }

数据完整性问题数据完整性的四大保障措施 : 主键约束 ; 外键约束 ; 域约束 ; 业务规则约束 ;

数据完整性问题数据完整性的四大保障措施 : 主键约束 ; 外键约束 ; 域约束 ; 业务规则约束 ; Database Systems 主题三 : 数据完整性实现技术 DDL 信息科学与工程学院 Jin-Min Yang 2018.09 数据完整性问题数据完整性的四大保障措施 : 主键约束 ; 外键约束 ; 域约束 ; 业务规则约束 ; DDL layout Definition of Schema of Table Trigger( 触发器 ): 业务规则约束 ; SQL Identifiers

主键约束 : Booking 中如果选择 hotelno, guestno, roomno 为主键 分析 : 这就 意味着一个客户预订某个旅馆的某个房间在整个一生中都只能订一 次 ; 显然不符合实际情况 如果选择 hotelno,guestno,datefrom 为主键 分析 : 这就意味着一个客户

主键约束 : Booking 中如果选择 hotelno, guestno, roomno 为主键分析 : 这就意味着一个客户预订某个旅馆的某个房间在整个一生中都只能订一次 ; 显然不符合实际情况如果选择 hotelno,guestno,datefrom 为主键分析 : 这就意味着一个客户