数据库疑难解答.ppt

数据库疑难解答,CCSE in Hunan University Jin-Min Yang 2016.05,教务数据库应用需求例子,在2010/01学期选了杨金民老师开设的“数据库系统”课程的学生姓名、学号、班级清单； 2010/01学期“数据库系统”课程的班级平均分清单； 2010/01学期“数据库系统”课程，在软件学院的2008级学生中没有选修该课程的同学清单； 2006级软件学院毕业班，输出其学生成绩排名；输出前25名，作为研究生保送生； 输出2010/01学期每门课程的最高分、最低分； 输出2010/01学期“数据库系统”课程，没有及格的同学的清单；,教务数据库应用需求例子(cont.),统计软件学院2009年度教师的教学工作量清单； 软件学院2009年度教学工作量未达到要求（150学时）的教师清单； 输出软件学院2006级毕业班“张山”同学的成绩清单(课程名称，学分，成绩； 输出软件学院2010/01学期每门课的选修人数清单； 统计湖南大学各个学院2009年度的教学工作量清单； 对软件学院每个学生，给其家长输出2010/01学期成绩单；,作业3.21: 伦敦每个旅馆最常订的房间类型是？,Hotel (hotelNo, name, city) Room (roomNo, hotelNo, type, price) Booking (hotelNo, guestNo, dateFrom, dateTo, roomNo) Guest (guestNo, name, guestcity),注意：别把关系论错,Room (roomNo, hotelNo, type, price) Hotel (hotelNo, name, city) Booking (hotelNo, guestNo, dateFrom, dateTo, roomNo),m : 1 : m,这种扇形关系不能完整表达room和booking之间的关系； 尽管也把三个表联接起来了，但是错误地表达了关系；,booking,hotel,room,有多少行记录？ 实际应该有多少行记录？,作业3.21: 伦敦每个旅馆最常订的房间类型是？,Hotel (hotelNo, name, city) Room (roomNo, hotelNo, type, price) Booking (hotelNo, guestNo, dateFrom, dateTo, roomNo) Guest (guestNo, name, guestcity),伦敦每个旅馆最常订的房间类型是？,最常定的房间类型就是预订次数最多的房间类型； 因此想要形成一个预订表：,在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.hotelName, 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.hotelNo AND t1.typeCount=t2.maxBook；,3.20 八月份每个旅馆平均订房数是多少？,SELECT AVG(BookNums) FROM ( SELECT HotelNo, count(*) AS BookNums FROM Booking WHERE year(dateFrom)= 2013 AND Month(dateFrom) = 8 GROUP BY H.hotelNo UNION SELECT HotelNo , 0 AS BookNums FROM Hotel WHERE hotelNo NOT IN (SELECT DISTINCT hotelNo 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)； 对吗？,查询中特别要注意的地方,某个房间号，在每个宾馆都会存在，因此要： SELECT 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，roomNo）上。另一例子是开课表与排课表。,17,操作简单性的层次解决策略,应用程序 存储过程 视图 表 DBMS,数据库专业知识 (关系、联接，复杂的查询表达）,简单了解数据库常识 （表的5种操作）,编程人员，（函数调用，表的遍历）,普通用户 （点击鼠标，敲键盘）,数学、软件天才（数据处理特征发掘，思想的实现）,少,多,倒立金字塔,数据完整性的四大保障措施： 主键约束； 外键约束； 域约束； 业务规则约束；,数据完整性问题,19,数据安全性问题,用户访问数据库服务器的联接控制； 用户对数据访问的权限控制； 用户对数据访问的审计；,数据安全性问题,DLL layout,Table View（视图）：简单性，层次性; 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 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) 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 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, FOREIGN 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 ( (honors = 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 courseId 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 hotelNo = newrow.hotelNo AND roomNo =newrow.roomNo AND dateTo = newrow.dateFrom AND dateFrom = newrow. dateTo) INSERT INTO booking VALUES( newrow.guestId, newrow.hotelNo, newrow.roomNo, newrow.dateFrom, newrow.dateTo);,执行业务规则约束,CREATE VIEW staff (Deptname, Number,Name,Job, Birthday) AS SELECT eno,ename,title, bdate, dname FROM emp, dept WHERE emp.dno = dept.dno and DeptName = Accounting; CREATE TRIGGER trigger_insertEmp INSTEAD OF INSERT ON staff REFERENCING NEW ROW AS new FOR EACH ROW WHEN (EXISTS (SELECT dno FROM dept WHERE dname = new.deptname) BEGIN String Deptno SELECT dno into Deptno FROM dept WHERE dname = new.deptname INSERT INTO Emp VALUES (new.eno, new.ename,new.job, new.birthday,0, Deptno); END,触发器例子，执行审计,CREATE TRIGGER cheatingEmployee AFTER UPDATE OF salary ON Emp REFERENCING OLD ROW AS old NEW ROW AS new FOR EACH ROW WHEN (new.salary old.salary*1.1) INSERT INTO auditEmp VALUES (new.eno, date(now(), new.salary, old.salary);,Event,Condition,Action,Motivation of view (视图),在2011/01学期选了杨金民老师开设的“数据库系统”课程的学生姓名、学号、班级清单; 输出软件学院的学生列表”; 输出所有课程名称及其教材的列表;,Student,enroll,Course,SQL查询特征(1),在2010/01学期选了杨金民老师开设的“数据库系统”课程的学生姓名、学号、班级清单； SELECT sno, sname, classno FROM student AS s, teacher AS t, course AS c, enroll AS e WHERE t.tname=杨金民 AND ame=数据库系统 AND e.semester=2010/01 AND s.sno=e.sno AND t.tno=e.tno AND o=o; 要写出该查询语句，对一般用户实在是太难了，因为联接运算难懂；,特征(2),2)为软件学院的学生辅导员，输出软件学院的学生列表： SELECT student.* FROM student, department WHERE student.deptNo = department.deptNo AND deptname = Software college; 用户仅仅只关注表中的某一特定部分的行记录;,Student,特征(3),3)输出所有课程名称及其教材的列表： SELECT name, textbook FROM course; 3) 对于教材科的工作人员，仅仅只关心课程表中的课程名称和教材两个字段;,Course,Creating Views,在2010/01学期选了杨金民老师开设的“数据库系统”课程的学生姓名、学号、班级清单； CREATE VIEW specified_student AS SELECT sno, sname, classno FROM student AS s, teacher AS t, course AS c, enroll AS e WHERE t.tname=杨金民 AND ame=数据库系统AND e.semester=2010/01 AND s.sno=e.sno AND t.tno=e.tno AND o=o;,用户：SELECT * FROM specified_student; 使用简单很多了,使用存储过程来使得视图通用化 例如：上课点名清单,CREATE PROCEDURE Specified_students(semesterV IN VARCHAR, courserV IN VARCHAR, teacherV IN VARCHAR）AS BEGIN SELECT studentNo, name, class FROM student AS S, enroll AS E, course AS C, teacher AS T WHERE S.studentNo = E.studentNo AND C.courseNo = E.courseNo AND T.teacherNo =E.teacherNo AND.name = courseV AND .semester =semesterV AND T.name=teacherV; END; CALL Specified_students( 2014/01,数据库系统 , 杨金民);,存储过程：实现简单化 伦敦每个旅馆最常订的房间类型是？,CREATE PROCEDURE PopularRoomType(city IN VARCHAR）AS BEGIN SELECT h.hotelNo, h.hotelName, r.type, COUNT(*) AS typeCount INTO tempTbl FROM Hotel h, Room r, Booking b WHERE h.city = city AND h.hotelNo=r.hotelNo AND （r.hotelNo=b.hotelNo AND r.roomNo=b.roomNo) GROUP BY h.hotelNo, h.hotelName, r.type; SELECT hotelNo, MAX(typecount) AS maxBook INTO TempTbl2 FROM tempTbl GROUP BY hotelNo; SELECT t1.hotelNo, t1.hotelName, type FROM tempTbl AS t1, tempTbl2 AS t2 WHERE t1.hotelNo=t2.hotelNo AND t1.typeCount=t2.maxBook； END CALL PopularRoomType( 长沙);,Stored procedure,A way of providing further abstract to data operation. CREATE PROCEDURE AddStudent(studentNo IN student.studentNo%TYPE, name IN VARCHAR）AS BEGIN INSERT INTO student(studentNo, name) VALUES( studentNo, name); END; CALL AddStudent( 20090430117, 张蔷);,SQL Security,Security in SQL is based on: authorization identifiers; Ownership; database object; privileges. An authorization identifier : user, role; A user: user id, password; Whenever a user creates an object, the user is the owner of the object , have full privileges on the object.,SQL Privileges,Privileges give users the right to perform operations on database objects. The set of privileges are: SELECT - the user can retrieve data from table INSERT - the user can insert data into table UPDATE - the user can modify data in the table DELETE - the user can delete data (rows) from the table REFERENCES - the ability to reference columns of a named table in integrity constraints USAGE 可使用 除了表之外的其它对象，例如domains) Notes: INSERT , UPDATE and REFERENCES can be restricted to certain columns.,SQL GRANT Command,The GRANT command is use to give privileges on database objects to users. GRANT privilegeList | ALL PRIVILEGES ON ObjectName TO AuthorizationIdList | PUBLIC WITH GRANT OPTION The privilege list is one or more of the following privileges: SELECT DELETE INSERT (columnName ,. UPDATE (columnName ,. REFERENCES (columnName ,. USAGE,Required Privileges Examples,What privileges are required for the folowing statements? UPDATE Emp SET salary=salary*1.1 WHERE eno IN ( SELECT eno FROM WorksOn WHERE hours 30); DELETE FROM dept WHERE dno NOT IN (SELECT dno FROM WorksOn); INSERT INTO WorksOn (eno,pno) VALUES (E5,P5);,Transaction Definition in SQL,BEGIN TRANSACTION; UPDATE Account SET balance = balance - 50 WHERE num = S1; UPDATE Account SET balance = balance + 50 WHERE num = C1; COMMIT; 2) Transaction to calculate totals for all saving and checking accounts: BEGIN TRANSACTION; SELECT SUM(balance) WHERE accType = Savings; SELECT SUM(balance) WHERE accType = Checking; COMMIT; A transaction in SQL ends by: Commit accepts updates of current transaction. Rollback aborts current transaction.,Triggers Example,Consider this situation where triggers are useful. The WorksOn relation has a foreign key to Emp (eno). If a user inserts a record in WorksOn and the employee does not exist, the insert fails. However with triggers, we can accept the insertion into WorksOn and then create a new record in Emp so that the foreign key constraint is not violated.,Triggers Syntax,CREATE TRIGGER BEFORE | AFTER | INSTEAD OF FOR EACH ROW WHEN () Notes: BEFORE, AFTER, INSTEAD OF indicate when a trigger is executed. is the events that the trigger will be executed for. It will be one of these events: INSERT ON R DELETE ON R UPDATE OF A1,A2,An on R,Types of Triggers,There are two types of triggers: row-level triggers that are executed for each row that is updated, deleted, or inserted. Statement-level triggers that are only executed once per statement regardless of how many tuples are affected. Inserting the clause FOR EACH ROW indicates a row-level trigger (the default is a statement-level trigger).,Triggers Syntax - Referencing,The referencing clause allows you to assign names to the row or table being affected by the triggered event: INSERT statements imply a new tuple (for row-level) or new set of tuples (for statement-level). DELETE implies an old tuple or table. UPDATE implies both. These tuples or tables can be referred to using the syntax: NEW OLD TUPLE TABLE AS Example: Statement-level trigger on an update: REFERENCING OLD TABLE AS oldTbl NEW TABLE as newTbl,Example of object-relation data model,Relation data model,Collection types in SQL 1999,Set type, array type: CREATE TYPE Publisher AS ( name VARCHAR(16), branch VARCHAR(24), ); CREATE TYPE book AS ( title varchar(64), author-array varchar(20) array10, publisher Publisher, keyword-set setof(varchar(20) ); CREATE TABLE books OF book;,Operation example in object-relation data model (1),To insert a tuple into the relation books: INSERT INTO books VALUES(Compilers, ARRAYSmith,Jones, Publisher(McGraw Hill,New York ), SET(parsing,analysis); SELECT title, FROM books;,Operation example in object-relation data model (2),Collection-valued attributes can be treated much like relations, using the keyword unnest; To find all books that have the word “database” as one of their keywords, SELECT title FROM books WHERE database IN (UNNEST(keyword-set) To get a relation containing pairs of the form “title, author-name” for each book and each author of the book SELECT B.title, A FROM books AS B, UNNEST (B.author-array) AS A,Operation example in object-relation data model (3),We can access individual elements of an array by using indices E.g. If we know that a particular book has three authors, we could write: SELECT author-array1, author-array2, author-array3 FROM books WHERE title = Database System Concepts SELECT title, A AS author, AS pub_name, publisher.branch AS pub_branch, K AS keyword FROM books AS B, UNNEST(B.author-array) AS A, UNNEST(B.keyword-list) AS K;,Operation example in object-relation data model (4),SELECT title, author, Publisher(pub_name, pub_branch) AS publisher, SET(keyword) AS keyword-list FROM flat-books GROUP BY title, author, publisher To nest on both authors and keywords: SELECT title, SET(author) AS author-list, Publisher(pub_name, pub_branch) AS publisher, SET(keyword) AS keyword-list FROM flat-books GROUP BY title, publisher,Operation example in object-relation data model (5),Subqueries in the select clause: SELECT title, (SELECT author FROM flat-books AS M WHERE M.title=O.title) AS author-set, Publisher(pub-name, pub-branch) AS publisher, (SELECT keyword FROM flat-books AS N WHERE N.title = O.title) AS keyword-set FROM flat-books AS O;,Reference Declaration in SQL1999,Type Department has a field name and a field head which is a reference to the type Person, with table people as scope: CREATE TYPE Department( name VARCHAR(20), head REF(Person) SCOPE people); We can then create a table departments as follows CREATE TABLE departments OF Department;,Type operation of reference,1) INSERT INTO departments VALUES (CS, null); 2) UPDATE departments SET head = (SELECT REF(p) FROM people AS p WHERE name=John) WHERE name = CS; 3) SELECT head name, head address FROM departments;,正确性； 性能； 简单性；,操作系统,全局Schema 联接信息 分段信息 复制信息,增强性能 共享, 提高可用性； 简化复杂性；,SQL,SQL,Database A,DDBMS,Network,SQL,Schema A,正确性； 性能； 简单性；,操作系统,Database B,Schema B,分布式数据库DDB,DDB其实质是一个中介；实体数据由实体数据库维护； 对用户来说，它与通常的数据库没有丝毫差异； 内部维护着实体数据库的语义信息，其功能就是分解和分发任务，收集和组合结果；,结果,PROJ,P1 P2 P3 P4 P5,Instrumentation P2 Database Develop CAD/CAM Maintenance CAD/CAM,150000 135000 250000 310000 500000,Montreal Paris Boston,PNO,PNAME,BUDGET,LOC,PROJ1,P1 P2,Instrumentation P2 Database Develop,150000 135000,Montreal,PNO,PNAME,BUDGET,LOC,$200000,PROJ2,P3 P4 P5,CAD/CAM Maintenance CAD/CAM,250000 310000 500000,Paris Boston,PNO,PNAME,BUDGET,LOC,=$200000,PROJ1,150000 135000 250000 310000 500000,BUDGET,P1 P2 P3 P4 P5,PNO,PROJ2,P1 P2 P3 P4 P5,Instrumentation P2 Database Develop CAD/CAM Maintenance CAD/CAM,Montreal Paris Boston,PNO,PNAME,LOC,4.2 分布式数据库中数据分段,4.3 DDBMS形式,DDBMS,形式 1,DBMS A,DDBMS,DBMS B,DDBMS,DBMS C,DDBMS,形式2,DBMS A,DBMS B,DBMS C,形式3,DBMS A,DBMS B,DDBMS,DBMS C,4.3 DDBMS形式(cont.),DBMS A,DBMS B,DBMS C,DDBMS,DDBMS,DDBMS,DBMS A,DBMS B,DBMS C,DBMS A,DBMS B,DBMS C,DDBMS,4.4 分布式数据库中的透明问题,网络透明(Network Transparency) 复制透明(Replication Transparency) 分段透明(Fragmentation Transparency),层次结构,少,多,倒立金字塔,数据库中的对象,SQL DDL 创建； 删除； 修改；,数据库设计人员,SQL DML SELECT； DELETE； UPDATE； INSERT； Statistics； CALL；,数据库使用人员,什么时候定义成视图？什么时候定义成存储过程？,DBMS 的三级模式(Schema)架构,用户A应用A,用户B应用B,用户C应用C,用户D应用D,用户E应用E,外模式1,外模式2,外模式3,模 式,内模式,数据库,外模式模式映射,模式内模式映射,三级模式结构及二级映像实现了数据库系统的数据独立性,External schema,Conceptual schema,Internal schema,(恒定不变，可添加),(可能变动),数据库系统特性,对于一个现有的数据库系统，已有的、向外部开放的数据库对象（存储过程、视图、用户）不能改变（指删除、修改），只能添加；否则使用它们的应用程序就不能工作，会出现应用程序中确定的表的样式将与数据库中的不一致 表现形式是： 1）应用程序发给数据库的SQL不能执行，报错； 2）数据库给应用程序的结果（表结构）与应用程序想要的不一致, 错位；,数据库系统特性,一个数据库有很多很多的应用程序和用户，随着时间的推移，DBA难以完全掌握清楚； 可以修改未向外部开放的数据库对象（表），满足新业务需求，例如：对student表进行修改，添加“国籍”字段。因为student表没有直接开放给外部。 修改之后要： 1）在其上添加相应的新视图，满足新应用程序的要求； 2）修改现有的与之相关的视图、存储过程，使得它们仍然不变并且有效，即修改映射；,应用程序的开发可完全独立于具体的数据库系统,应用程序关注的是表，基本模型是： 1）加载驱动程序； 2）建立联接； 3) 调用存储过程或者发送DML语句； 4）得到响应结果（表）； 5）对结果表逐行、然后逐列扫描、处理； . 关闭联接；,对接: 是参数化的,体现了应用特性：是固定的,应用程序代码实例,/1) 加载MySQL数据库JDBC驱动程序： Class.forName(“com.mysql.jdbc.Driver“); /2) 建立与数据库的链接： Connection connection = DriverManager.getConnection( “jdbc:mysql:/00:3306/education“,“root“,“admin“); /3) 向数据库发送数据操作指令，响应结果放在resultSet中: Statement statement = connection.cre