数据库疑难解答课件教学内容

《数据库疑难解答课件教学内容》由会员分享，可在线阅读，更多相关《数据库疑难解答课件教学内容（75页珍藏版）》请在金锄头文库上搜索。

1、数据库疑难解答,CCSE in Hunan University Jin-Min Yang 2016.05,教务数据库应用需求例子,在2010/01学期选了杨金民老师开设的“数据库系统”课程的学生姓名、学号、班级清单； 2010/01学期“数据库系统”课程的班级平均分清单； 2010/01学期“数据库系统”课程，在软件学院的2008级学生中没有选修该课程的同学清单； 2006级软件学院毕业班，输出其学生成绩排名；输出前25名，作为研究生保送生； 输出2010/01学期每门课程的最高分、最低分； 输出2010/01学期“数据库系统”课程，没有及格的同学的清单；,教务数据库应用需求例子(cont.

2、),统计软件学院2009年度教师的教学工作量清单； 软件学院2009年度教学工作量未达到要求（150学时）的教师清单； 输出软件学院2006级毕业班“张山”同学的成绩清单(课程名称，学分，成绩； 输出软件学院2010/01学期每门课的选修人数清单； 统计湖南大学各个学院2009年度的教学工作量清单； 对软件学院每个学生，给其家长输出2010/01学期成绩单；,作业3.21: 伦敦每个旅馆最常订的房间类型是？,Hotel (hotelNo, name, city) Room (roomNo, hotelNo, type, price) Booking (hotelNo, guestNo, dat

3、eFrom, dateTo, roomNo) Guest (guestNo, name, guestcity),booking,hotel,room,有多少行记录？ 实际应该有多少行记录？,作业3.21: 伦敦每个旅馆最常订的房间类型是？,Hotel (hotelNo, name, city) Room (roomNo, hotelNo, type, price) Booking (hotelNo, guestNo, dateFrom, dateTo, roomNo) Guest (guestNo, name, guestcity),伦敦每个旅馆最常订的房间类型是？,最常定的房间类型就是预订次

4、数最多的房间类型； 因此想要形成一个预订表：,在hotel表中,在room表中,因此要和Hotel以及room表做联接运算,伦敦每个旅馆最常订的房间类型是？,SELECT h.hotelNo, h.hotelName, r.type, COUNT(*) AS typeCount INTO tempTbl FROM Hotel h, Room r, Booking b WHERE h.city=London AND h.hotelNo=r.hotelNo AND （r.hotelNo=b.hotelNo AND r.roomNo=b.roomNo) GROUP BY h.hotelNo, h.h

5、otelName, r.type;,伦敦每个旅馆最常订的房间类型是？,tempTbl,伦敦每个旅馆最常订的房间类型是？,SELECT hotelNo, MAX(typecount) AS maxBook INTO TempTbl2 FROM tempTbl GROUP BY hotelNo;,tempTbl2,伦敦每个旅馆最常订的房间类型是？,temptbl,tempTbl2,伦敦每个旅馆最常订的房间类型是？,SELECT t1.hotelNo, t1.hotelName, type FROM tempTbl AS t1, tempTbl2 AS t2 WHERE t1.hotelNo=t2.

6、hotelNo AND t1.typeCount=t2.maxBook；,3.20 八月份每个旅馆平均订房数是多少？,SELECT AVG(BookNums) FROM ( SELECT HotelNo, count(*) AS BookNums FROM Booking WHERE year(dateFrom)= 2013 ANDMonth(dateFrom) = 8 GROUP BY H.hotelNo UNION SELECT HotelNo , 0 AS BookNums FROM Hotel WHERE hotelNo NOT IN (SELECT DISTINCT hotelNo

7、FROM Booking WHERE year(dateFrom)= 2013 AND Month(dateFrom) = 8) ),查询中特别要注意的地方,第7周星期四晚上9/10节课2班在中楼309的讨论课： 求编号为10005的宾馆在2014年1月14日这天的营业收入？ SELECT SUM(price) FROM room WHERE roomNo IN (SELECT roomNo FROM Booking WHERE hotelNo = 10005 AND dateFrom = 2014-01-14)； 对吗？,查询中特别要注意的地方,某个房间号，在每个宾馆都会存在，因此要： SE

8、LECT SUM(price) FROM room WHERE hotelNo = 10005AND roomNo IN (SELECT roomNo FROM Booking WHERE hotelNo = 10005 AND dateFrom = 2014-01-14)； 查询某个表中的某些行，第一要拿准的是主键，然后才是其它限定条件，尤其是对具有树形特征（即从属关系）（又叫1:m关系）的表间，例如room表，它是从属表，它的主键是多个属性组合构成（hotelNo，roomNo），其中的子集（hotelNo）是外键，因此注意力不能放在roomNo上，一定要放在主键（hotelNo，room

9、No）上。另一例子是开课表与排课表。,17,操作简单性的层次解决策略,应用程序 存储过程 视图 表 DBMS,数据库专业知识 (关系、联接，复杂的查询表达）,简单了解数据库常识 （表的5种操作）,编程人员，（函数调用，表的遍历）,普通用户 （点击鼠标，敲键盘）,数学、软件天才（数据处理特征发掘，思想的实现）,少,多,倒立金字塔,数据完整性的四大保障措施： 主键约束； 外键约束； 域约束； 业务规则约束；,数据完整性问题,19,数据安全性问题,用户访问数据库服务器的联接控制； 用户对数据访问的权限控制； 用户对数据访问的审计；,数据安全性问题,DLL layout,Table View（视图）：

10、简单性，层次性; Privilege (权限）; Transaction(事务); Trigger（触发器）：业务规则约束; Stored procedure (存储过程）：通用性，简单性; Object-relation data model(对象-关系数据模型);,SQL Identifiers,Identifiers are used to identify objects in the database such as tables, views, and columns. The identifer is the name of the database object. An SQL

11、 identifier (name) must follow these rules: only contain upper or lower case characters, digits, and underscore (_) character be no longer than 128 characters must start with a letter cannot contain spaces .,Example,CREATE TABLE Emp ( eno CHAR(5), ename VARCHAR(30) NOT NULL, bdate DATE, title CHAR(2

12、) CHECK (title IN (NULL,EE,SA,PR,ME), salary DECIMAL(9,2), supereno CHAR(5), dno CHAR(5), PRIMARY KEY (eno) FOREIGN KEY (dno) REFERENCES Dept(dno) ON DELETE SET NULL ON UPDATE CASCADE );,SQL Referential Integrity,Student,Enroll,在Student表中的某一行记录： 1）修改的其主关键字studentNo； 2）删除某一行记录； 3）插入一行新记录；,1) CASCADE

13、2) SET NULL 3) NO ACTION,SQL Referential Integrity Example,CREATE TABLE Enroll ( studentNo CHAR(5) NOT NULL, courseNo CHAR(5) NOT NULL, semester CHAR(7), grade SMALLINT, PRIMARY KEY (studentNo, courseNo, semester), FOREIGN KEY (studentNo) REFERENCES student ON DELETE CASCADE ON UPDATE CASCADE, FOREI

14、GN KEY (courseNo) REFERENCES course ON DELETE NO ACTION ON UPDATE CASCADE );,约束(Constraints)的类别,约束是针对表中每条记录而言的，也叫元组约束； 1）针对单个表，对每行, 其单个字段的约束；例如data type，domain，NOT NULL等； 2）针对单个表，同一行之间不同字段之间的约束；例如： CREATE TABLE student ( studentNo CHAR(10) NOT NULL, honors CHAR(1), grade DECIMAL(4,1), CHECK ( (honor

15、s = Y AND grade 85) OR honors = N) ); 3）针对同一个表，行之间的约束； 4）针对两个或多个表，其中行之间的约束； 通过Trigger来实现；,实现每学期选课不超过25学分,CREATE TRIGGER trigger_insertEnroll INSTEAD OF INSERT ON enroll REFERENCING NEW ROW AS newrow FOR EACH ROW credit integer SELECT SUM(credit) INTO credit FROM course WHERE courseId IN (SELECT cour

16、seId FROM enroll WHERE studentId = newrow.studentId AND semester =newrow.semester) OR courseId = newrow.courseId; WHEN ( credit = 25 ) INSERT INTO enroll(studentId, semester, courseId, teracherId) VALUES( newrow.studentId, newrow.semester, newrow.courseId, newrow.teracherId);,实现每个房间的预订不重叠冲突,CREATE TRIGGER trigger_insertBooking INSTEAD OF INSERT ON booking REFERENCING NEW ROW AS newrow FOR EACH ROW WHEN( NOT EXIST SELECT hotelNo, roomNo FROM booking WHERE hotel