CCSE in Hunan University 数据库疑难解答 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) m : Hotel (hotelNo, name, city) Booking (hotelNo, guestNo, dateFrom, dateTo, roomNo) m : 1 这种扇形关系不能完整表达room和booking之间的关系； 尽管也把三个表联接起来了，但是错误地表达了关系；

hotel room booking hotelNo roomNo guestNo dateFrom 01 108 g1 11/04/01 hotelName 01 希尔顿 02 华天 hotelNo roomNo type price 01 108 106 02 213 211 booking hotelNo roomNo guestNo dateFrom 01 108 g1 11/04/01 106 g2 11/04/02 g3 11/04/03 g4 11/04/05 02 213 g5 11/03/29 211 11/04/04 有多少行记录？ 实际应该有多少行记录？

作业3.21: 伦敦每个旅馆最常订的房间类型是？ Hotel (hotelNo, name, city) Room (roomNo, hotelNo, type, price) Booking (hotelNo, guestNo, dateFrom, dateTo, roomNo) Guest (guestNo, name, guestcity)

伦敦每个旅馆最常订的房间类型是？ 最常定的房间类型就是预订次数最多的房间类型； 因此想要形成一个预订表： hotelNo hotelName roomNo type dateFrom dateTo guestNo …… … 在room表中 在hotel表中 因此要和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 hotelNo hotelName type typeCount 01 希尔顿 单人间 123 双人间 300 总统间 7 02 华天 312 231 18 家庭套件 320

伦敦每个旅馆最常订的房间类型是？ SELECT hotelNo, MAX(typecount) AS maxBook INTO TempTbl2 FROM tempTbl GROUP BY hotelNo; tempTbl2 hotelNo maxBook 01 300 02 320

伦敦每个旅馆最常订的房间类型是？ temptbl hotelNo hotelName type typeCount 01 希尔顿 单人间 123 双人间 300 总统间 7 02 华天 312 231 18 家庭套件 320 tempTbl2 hotelNo maxBook 01 300 02 320

伦敦每个旅馆最常订的房间类型是？ SELECT t1.hotelNo, t1.hotelName, type FROM tempTbl AS t1, tempTbl2 AS t2 WHERE t1.hotelNo=t2.hotelNo AND t1.typeCount=t2.maxBook； hotelNo hotelName type 01 希尔顿 双人间 02 华天 家庭套间

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 <= DATE ‘2014-01-14’ AND DateTo >= ‘2014-01-14’)； 对吗？

查询中特别要注意的地方 某个房间号，在每个宾馆都会存在，因此要： SELECT SUM(price) FROM room WHERE hotelNo = ‘10005’AND roomNo IN (SELECT roomNo FROM Booking WHERE hotelNo = ‘10005’ AND dateFrom <= DATE ‘2014-01-14’ AND DateTo >= ‘2014-01-14’)； 查询某个表中的某些行，第一要拿准的是主键，然后才是其它限定条件，尤其是对具有树形特征（即从属关系）（又叫1:m关系）的表间，例如room表，它是从属表，它的主键是多个属性组合构成（hotelNo，roomNo），其中的子集（hotelNo）是外键，因此注意力不能放在roomNo上，一定要放在主键（hotelNo，roomNo）上。另一例子是开课表与排课表。

操作简单性的层次解决策略 应用程序 存储过程 视图 表 DBMS 普通用户 （点击鼠标，敲键盘） 多 编程人员，（函数调用，表的遍历） 简单了解数据库常识 （表的5种操作） 数据库专业知识 (关系、联接，复杂的查询表达） 数学、软件天才（数据处理特征发掘，思想的实现） 少 倒立金字塔

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

数据安全性问题 公共Internet 加密技术 用户、权限管理，审计 应用服务器 数据库 用户 偷看 非法数据访问： 读取非允许的数据； 篡改 抵赖 假冒 公共Internet 非法数据访问： 读取非允许的数据； 改/删/加非允许的数据； 加密技术 用户、权限管理，审计

数据安全性问题 用户访问数据库服务器的联接控制； 用户对数据访问的权限控制； 用户对数据访问的审计；

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 在Student表中的某一行记录： 1）修改的其主关键字studentNo； 2）删除某一行记录； 3）插入一行新记录； Name studentNo sex birthdate Mike 2008043101 male 1990/12/14 Tom 2008043214 female 1992/02/21 Mary 2008043332 1988/07/09 Enroll studentNo courseNo semester grade 2008043332 430803 2010/01 90 2008043101 430317 2009/01 56 2008/02 430007 2004/02 77 430601 2000/01 87 2002/02 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);

执行业务规则约束 BEGIN 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 (视图) Student Course Name studentNo sex birthdate Mike 2008043101 male 1990/12/14 Tom 2008043214 female 1992/02/21 Mary 2008043332 1988/07/09 Name courseNo textbook syllabus database 430601 a introduction operating system 430803 b all java 430317 c section enroll 在2011/01学期选了杨金民老师开 设的“数据库系统”课程的学生姓 名、学号、班级清单; 输出软件学院的学生列表”; 输出所有课程名称及其教材的列表; studentNo courseNo semester grade 2008043332 430803 2010/01 90 2008043101 430317 2009/01 56 2008/02 430007 2004/02 77 430601 2000/01 87 2002/02

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 c.cname=’数据库系统’ AND e.semester=’2010/01’ AND s.sno=e.sno AND t.tno=e.tno AND c.cno=e.cno; 要写出该查询语句，对一般用户实在是太难了，因为联接运算难懂；

特征(2) 2)为软件学院的学生辅导员，输出软件学院的学生列表： SELECT student.* FROM student, department WHERE student.deptNo = department.deptNo AND deptname = ‘Software college’; 用户仅仅只关注表中的某一特定部分的行记录; Student Name studentNo sex birthdate deptNo Mike 2008043101 male 1990/12/14 043 Jim 1990/06/07 012 Tom 2008043214 female 1992/02/21 Mary 2008043332 1988/07/09 042

特征(3) 3)输出所有课程名称及其教材的列表： SELECT name, textbook FROM course; 3) 对于教材科的工作人员，仅仅只关心课程表中的课程名称和 教材两个字段; Course Name courseNo textbook syllabus database 430601 a introduction operating system 430803 b all java 430317 c section network 430105 d homework

Creating Views 用户：SELECT * FROM specified_student; 使用简单很多了 在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 c.cname=’数据库系统’AND e.semester=’2010/01’ AND s.sno=e.sno AND t.tno=e.tno AND c.cno=e.cno; 用户：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: SELECT SUM(balance) WHERE accType = 'Savings'; SELECT SUM(balance) WHERE accType = 'Checking'; 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 <name> Notes: BEFORE | AFTER | INSTEAD OF <events> [<referencing clause>] [FOR EACH ROW] [WHEN (<condition>)] <action> Notes: BEFORE, AFTER, INSTEAD OF indicate when a trigger is executed. <events> 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 <name> Example: Statement-level trigger on an update: REFERENCING OLD TABLE AS oldTbl NEW TABLE as newTbl

Example of object-relation data model title Author-set Publisher (name, branch) Keyword-set compiler {smith,John} (Oxford, Beijing) {word, sentence, syntax} network {Jack, Smith} (Amason, Changsha) (transport, fault-tolerance} database {Jim, Tom, Phillipe} (Greatwall, Changsha) {relation, record, foreign key}

Relation data model title Author compiler smith John network Jack database Jim Tom Phillipe title Keyword compiler word sentence syntax network transport fault-tolerance database relation record foreign key title publisher branch compiler Oxford Beijing network Amason Shanghai database Greatwall Changsha

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) array[10], 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’, ARRAY[`Smith’,`Jones’], Publisher(‘McGraw Hill’,`New York’ ), SET(`parsing’,`analysis’)); SELECT title, publisher.name 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-array[1], author-array[2], author-array[3] FROM books WHERE title = `Database System Concepts’ SELECT title, A AS author, publisher.name 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;

分布式数据库DDB SQL 结果 DDBMS SQL SQL Database Database A B Schema A Schema B 对用户来说，它与通常的数据 库没有丝毫差异； 内部维护着实体数据库的语义 信息，其功能就是分解和分发任 务，收集和组合结果； SQL 结果 增强性能 共享, 提高可用性； 简化复杂性； 全局Schema 联接信息 分段信息 复制信息 DDBMS SQL SQL 正确性； 性能； 简单性； Database A 正确性； 性能； 简单性； Database B Schema A Schema B 操作系统 操作系统 Network

4.2 分布式数据库中数据分段 PROJ PROJ1 PROJ2 PNAME LOC P1 P2 P3 P4 P5 PNO PNAME BUDGET LOC PROJ2 P1 P2 P3 P4 P5 Instrumentation P2 Database Develop CAD/CAM Maintenance 150000 135000 250000 310000 500000 Montreal Paris Boston PNO PNAME LOC P1 P2 P3 P4 P5 Instrumentation P2 Database Develop CAD/CAM Maintenance Montreal Paris Boston PROJ1 <$200000 PNO PNAME BUDGET LOC P1 P2 Instrumentation P2 Database Develop 150000 135000 Montreal PROJ1 PNO BUDGET P1 P2 P3 P4 P5 150000 135000 250000 310000 500000 PROJ2 >=$200000 PNO PNAME BUDGET LOC P3 P4 P5 CAD/CAM Maintenance 250000 310000 500000 Paris Boston

4.3 DDBMS形式 DDBMS DDBMS DDBMS 形式 1 DBMS A DBMS B DBMS C DDBMS DBMS A 形式2 形式3 DDBMS DBMS A DBMS B DBMS C

4.3 DDBMS形式(cont.) DDBMS DBMS A DBMS B DBMS C DDBMS DDBMS DBMS A DBMS

4.4 分布式数据库中的透明问题 网络透明(Network Transparency) 复制透明(Replication Transparency) 分段透明(Fragmentation Transparency)

层次结构 应用程序 普通用户 可视化业务表单； 键盘输入，鼠标点击 存储过程 编程人员 函数调用，二维表 视图 简单了解数据库常识 SQL:单表SELECT； INSERT，UPDATE, DELETE 表 数据库专业知识 联接，统计 数据库管理系统 数学、软件天才 挖掘硬件特性、软件特性、数据特性、访问特性 多 少 倒立金字塔

数据库中的对象 应用程序 (表和存储过程) 数据库使用人员 存储过程 视图 SQL DML 表 SELECT； DELETE； 触发器(完整性约束） 用户(权限） 索引 数据库管理系统 （DBMS) SQL DML SELECT； DELETE； UPDATE； INSERT； Statistics； CALL； 数据库设计人员 SQL DDL 创建； 删除； 修改； 什么时候定义成视图？什么时候定义成存储过程？

DBMS 的三级模式(Schema)架构 External schema Conceptual schema Internal 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://192.168.105.100:3306/education","root","admin"); //3) 向数据库发送数据操作指令，响应结果放在resultSet中: Statement statement = connection.createStatement(); ResultSet rs = statement.executeQuery( "SELECT name, phone FROM student WHERE sex =’男’" ); System.out.println(“姓名 电话号码”); //4)遍历结果集： while (rs.next() ) System.out.println(rs.getString(1) + “ ” + rs.getString(2)); //5) 关闭与数据库的联接: connection.close();

应用程序与数据库的可对接性 可对接性的实现：视图和存储过程 可对接性 Application DBMS Database SQL Database DML或存储过程可执行性； 返回的结果与应用程序想象的一致（指Schema, 不是data） Driver , Connection, Statement, ResultSet, schema 主机(Host), 端口(Port), 服务器(Server), 数据库(Database)

期中考试 希尔顿是一跨国旅馆经营公司，其数据库系统中有如下4个表： Hotel (hotelNo, name, city) Room (roomNo, hotelNo, type, price) Booking (hotelNo, guestId, dateFrom, dateTo, roomNo) Guest (guestId, name, guestcity, creditcard) 1) 使用SQL的DDL写出Booking表的定义，要求至少包含一条业务规则约束； 2) 在Booking表中，某一旅馆的某一个房间在某一天不能出现重复预定的情况。今天是2012/04/09，某个客户想要在hotelNo为’01’的旅馆预定从’2012/4/10’至’2012/4/12’的一个’双人间’房间，请列出可供他选择的房间号 (roomNo)； 3)今天是2012/04/09, 问昨天每个旅馆的空房损失分别是多少？

期中考试 CREATE TABLE Booking ( hotelNo VARCHAR(4), guestId VARCHAR(12) NOT NULL, dateFrom DATE, dateTo DATE NOTNULL, roomNo CHAR(4), PRIMARY KEY(hotelNo, roomNo, dateFrom) FOREIGN KEY hotelNo, roomNo REFERENCES room(hotelNo, roomNo) FOREIGN KEY guestId REFERENCES guest(guestId) CHECK (dateTo >=dateFrom) )

期中考试 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’) ) ); 可简化为dateFrom <=‘2012/04/12’ AND dateTo >=‘2012/04/10’

期中考试（3） 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 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 = DATE ‘2012/04/08’ OR (dateFrom < DATE ‘2012/04/08’ AND dateTo>=DATE ‘2012/04/08’)) ) GROUP BY hotelname，hotelNo ； 可简化为dateFrom <=‘2012/04/08’ AND dateTo >=‘2012/04/08’

期中考试结果 分数 人数 情况 19 1 1题准确，2、3题思路正确 17 2 外键不正确，2、3题有思路 15 外键正确 12 10 主键正确 2题有思路 9 13 主键接近准确，外键不正确 5 29 有PRIMARY和Foreign KEY，但是都错 11 有PRIMARY KEY，无Foreign Key CREATE TABLE错，没有出现PRIMARY KEY