2009-2010(1)数据库原理复习题.doc

2009-2010(1)数据库原理复习题SQL语言可以直接以命令方式交互使用，也可以嵌入到程序设计语言中以程序方式使用 图3-1 样本数据库例6：规定学生表的性别字段不允许为空值。ALTER TABLE 学生 ALTER COLUMN 性别char(2) NOT NULL INSERT INTO student.院系VALUES(2,计算机学院,张秋霞,教二楼3层)违背PRIMARY KEY 约束CREATE TABLE student.院系(编号 tinyint PRIMARY KEY, 名称 char(30) UNIQUE NOT NULL, 负责人 char(10), 办公地点 char(40)INSERT INTO student.院系VALUES(3,经济管理学院,张秋霞,教二楼3层)违背UNIQUE约束CREATE TABLE student.院系(编号 tinyint PRIMARY KEY, 名称 char(30) UNIQUE NOT NULL, 负责人 char(10), 办公地点 char(40)INSERT INTO student.院系(编号,负责人,办公地点) VALUES(3,张秋霞,教二楼3层)违背NOT NULL约束INSERT INTO student.学生(学号,院系,姓名,性别,生源,状态) VALUES(04014111,1,程鸣,女,湖南,正常)注意：学生表的院系列是计算列，是不能被插入语句操作的。 CREATE TABLE student.学生(学号 char(8) PRIMARY KEY, 院系 AS CONVERT(Tinyint,SUBSTRING(学号,3,2) PERSISTED NOT NULL FOREIGN KEY REFERENCES student.院系(编号), 姓名 char(10) NOT NULL, 性别 char(2) CHECK (性别=男 OR 性别=女), 生源 char(6), 状态 char(4) CHECK (状态IN(正常,留级,休学,退学)+CREATE TABLE student.选课(学号 char(8) FOREIGN KEY REFERENCES student.学生, 课程编号 char(6) FOREIGN KEY REFERENCES student.课程, 考试成绩 tinyint CHECK (考试成绩 BETWEEN 0 AND 100) DEFAULT NULL PRIMARY KEY (学号,课程编号) )INSERT INTO student.选课VALUES(04014111,B00016,DEFAULT)INSERT INTO student.选课 VALUES(04014111,X00201,NULL)+插入操作时检查完整性？检查实体完整性规则检查参照完整性规则检查用户定义完整性规则 检查实体完整性规则如果插入元组的主关键字的属性不为空值、并且相应的属性值在关系中不存在（即保持唯一性），则可以执行插入操作，否则不可以执行插入操作。 检查参照完整性规则如果是向被参照关系插入元组，则无须检查参照完整性；如果是向参照关系插入元组，则要检查外部关键字属性上的值是否在被参照关系中存在对应的主关键字的值，如果存在则可以执行插入操作，否则不允许执行插入操作。另外，如果插入元组的外部关键字允许为空值，则当外部关键字是空值时也允许执行插入操作。 检查用户定义完整性规则如果插入的元组在相应的属性值上遵守了用户定义完整性规则(包括CHECK约束、UNIQUE约束、NULL或NOT NULL约束)，则可以执行插入操作，否则不可以执行插入操作。 +ALTER TABLE student.课程ADD 责任教师char(6) NULLCONSTRAINT ref_teacher FOREIGN KEY REFERENCES student.教师(教师编号)ON DELETE SET NULL ON UPDATE CASCADEDELETE student.教师 WHERE 教师编号=011122删除成功。如果有课程记录的责任教师参照被删除记录，则将该课程记录的责任教师字段置为NULL。删除操作只需要检查参照完整性 如果删除的是参照关系的元组，则不需要进行参照完整性检查，可以执行删除操作。如果删除的是被参照关系的元组，则检查被删除元组的主关键字属性的值是否被参照关系中某个元组的外部关键字引用，如果未被引用则可以执行删除操作；否则可能有4种情况：拒绝删除、空值删除、级联删除、默认值删除执行更新操作时检查完整性 执行更新操作可以看作是先删除旧的元组，然后再插入新的元组。所以执行更新操作时的完整性检查综合了插入和删除操作的数据完整性检查。例3-68：查询哪些学生（学号和姓名）有未完成考试的课程（考试成绩为NULL）。 例3-69：查询哪些学生（学号和姓名）没有未完成考试的课程（考试成绩为NULL）。视图示意从基本表院系和教师派生出虚拟的教师列表建立一个“教师列表”视图，包含院系表中的名称字段（在视图中命名为院系）和教师表中的姓名、性别及职称字段。 变量的赋值n对变量赋值的命令是 SELECT =,= n或SET =n或SELECT =,= FROM n前两种格式是直接对变量进行赋值，第三种格式是将SELECT查询的结果赋值给变量游标的使用n打开游标n从游标中读记录n循环控制遍历游标的所有记录n关闭游标n释放游标n与程序设计语言中的文件相对照：nDECLARE CURSOR相当于说明了一个文件nOPEN相当于打开文件nFETCH相当于读一条记录nCLOSE相当于关闭文件nDEALLOCATE语句相当于删除文件。 存储过程的作用n可以避免在网络上传输大量无用的信息或原始数据，只需要传输调用存储过程的指令和数据库服务器返回的处理结果 n把完成某一数据库处理的功能设计为存储过程，则可以在各个程序中反复调用，从而减轻程序的编写工作量 n可以利用存储过程间接实现一些安全控制功能n创建存储过程通常是在数据库设计和开发阶段完成的n存储过程可以嵌套，即在一个存储过程中可以调用另外一个存储过程n存储过程一般用来完成数据查询和数据处理操作n在存储过程中不可以使用创建数据库对象的语句（如CREATE TABLE等各种CREATE语句）触发器n触发器是一类特殊的存储过程，它在满足某个特定条件时自动触发执行。n分为DML(数据操作语言)触发器、DDL(数据定义语言)触发器和LOGIN触发器nDML触发器在执行数据操作语言时触发nDDL触发器在执行数据定义语言时触发nLOGIN触发器在有用户登录时触发DML触发器n定义在表上，当对表进行插入、删除或修改时触发，即可能使数据发生变化时触发nDML触发器分为插入触发器、删除触发器和更新触发器三类n触发器的三个要素：表、触发的事件、采取的动作n事件发生时自动触发，用户无选择的权利deleted表和inserted表 n当DML触发器激活时系统会自动产生两个特殊的临时表：deleted表和inserted表n当发生INSERT操作时新插入的记录也存储在inserted表n当发生DELETE操作时被删除的记录也存储在deleted表n当发生UPDATE操作时修改前的旧记录也存储在deleted表、修改后的新记录也存储在inserted表deleted表和inserted表的作用？n可以使用deleted表和inserted表判断正在操作的记录是否符合要求，从而检查错误并采取相应的措施 n找出数据修改前、后表的状态差异，并基于该差异采取相应的措施n可以扩展表之间的参照完整性 deleted表和inserted表只在触发器内可用，一旦触发器完成任务，这两个系统产生的临时表将自动删除 n使用DML触发器在“课程”和“教师”表之间强制实现业务规则；规定“专业基础”课的责任教师必须是“教授”或“副教授”，这时候“课程”表对“教师”表不是简单的参照，而必须定义相应的检查或约束规则，此规则不能简单用CHECK约束 实现，可以用触发器实现。create trigger T_借还书登记_Ins on 借还书登记 FOR INSERTASBEGINDECLARE tol_num smallintDECLARE num smallintSELECT tol_num=可借阅数量FROM INSERTED i,读者 r,读者身份表 bWHERE r.读者类型=b.读者类型 AND i.读者编号=r.读者编号SELECT num=count(*)FROM INSERTED i,借还书登记 bWHERE b.还书日期 IS NULL AND i.读者编号=b.读者编号IF num=tol_numBEGINRAISERROR(已经超过借阅数量，不能借阅！,16,1)ROLLBACKENDENDinsert into 借还书登记(书号,读者编号) values(b401,101)insert into 借还书登记(书号,读者编号) values(b401,102)create proc f_day1card varchar(10),book_no varchar(20)asbegindeclare 允许天数 intdeclare 借阅天数 intdeclare day intselect 允许天数=可借阅天数 FROM 读者 r,读者身份表 bWHERE r.读者类型=b.读者类型 AND 读者编号=cardSELECT 借阅天数=DATEDIFF(day,借阅日期,GETDATE()FROM 借还书登记WHERE 还书日期 IS NULL AND 读者编号=card AND 书号=book_noIF 借阅天数 IS NULL SET day=借阅天数ELSE SET day=借阅天数-允许天数RETURN dayENDdeclare count_day intexecute count_day=f_day1 101,b101print 天数,count_day数据库设计的基本任务n数据库设计的基本任务就是根据用户的信息需求，处理需求和数据库的支撑环境（包括DBMS、操作系统、硬件），设计一个结构合理、使用方便、效率较高的数据库。 n信息需求是指在数据库中应该存储和管理哪些数据对象； n处理需求是指需要进行哪些业务处理和操作，如对数据对象的查询、增加、删除、修改、统计等操作。 数据库设计步骤n需求分析阶段n概念结构设计阶段n逻辑结构设计阶段n物理结构设计阶段n数据库实施阶段n数据库运行和维护阶段nPowerDesigner是一个功能强大而使用方便的工具集，为新一代数据库应用的建模提供了全面的支持。nPowerDesigner 12.0提供了如下7种模型： n需求分析模型(Requirements Model RQM)n企业业务流程模型(Business Process ModelBPM)n概念数据模型(Conceptual Data ModelCDM)n物理数据模型(Physical Data ModelPDM)n面向对象模型(Object Oriented ModelOOM)n信息流动模型(Information Liquidity Model ILM)nXML模型(XML Model)n概念模型设计的结果是得到数据库的概念结构，或称概念数据模型。n概念数据模型与具体的硬件环境和软件环境均无关。 设计概念模型的一般步骤 （1）先设计面向全局应用的全局概念结构的初步框架，即先建立起整个系统的总体框架；（2）然后根据部门或功能划分成局部应用；（3）依据划分后的局部应用完成局部E-R图的设计；（4）最后将局部E-R图合并、转换成全局E-R图，完成概念模型的设计。 概念数据模型设计的基本内容 概念模型的设计或描述的最常用工具是E-R图，这一步的工作至少要包括以下内容：（1）确定实体(包括弱实体)；（2）确定实体的属性；（3）确定实体的标识属性（关键字）；（4）确定实体间的联系和联系类型；（5）画出表示概念模型的E-R图（利用相应的建模工具，如PowerDesinger）。 n各局部E-R图之间的冲突主要有：n属性冲突n命名冲突n结构冲突n属性冲突n属性的值域冲突，简称域冲突，即属性值的类型、取值范围或取值集合不同。n属性取值单位冲突，即属性值的单位不同。n命名冲突n同名异义，即不同意义的实体名、属性名或联系名在不同的局部E-R图中有相同的名字。n异名同义（一义多名），即相同意义的实体名、属性名或联系名在不同的局部E-R图中有不同的名字。n属性冲突和命名冲突的解决方法n可以通过讨论，协商统一即可。 n使用PowerDesigner设计数据库的处理流程是：n设计概念数据模型；n由概念数据模型（CDM）生成初步的物理数据模型（PDM）；n在生成的物理数据模型中完成物理数据库设计；n生成创建目标数据库的脚本甚至可以直接创建目标数据库。 逻辑阶段设计的任务是将概念模型转换成某个具体的DBMS所支持的数据模型，一般称之为逻辑模型，这里只介绍到关系模型的转换。转换后的数据模型应与转换前的E-R模型保持一致的应用语义。 逻辑模型设计的一般过程nE-R模型向关系模型的转换 n使用PowerDesigner将概念模型转换成逻辑（物理）模型 n使用PowerDesigner设计逻辑（物理）模型 n关系模式优化 E-R模型向关系模型的转换n如果在概念模型设计阶段已经将多对多联系转换成了一对多联系，则在逻辑模型设计阶段把E-R模型转换为关系模型将非常简单： n将每一个实体转换为一个关系模式，使其包含对应实体的全部属性，并根据语义确定关键字（实际在概念模型阶段已经确定）n将一对多的联系直接并入n端实体的关系模式，这只需要将“1”端实体的关系模式的主关键字纳入n端实体的关系模式，并作为外部关键字n将一对一联系的两个关系模式合并为一个关系模式数据库管理系统的身份识别机制n身份识别的3个层次l系统登录l数据库访问l数据操作n一般系统管理员用户管理数据库管理员用户n数据库管理员用户管理数据库对象用户n数据库对象用户向一般用户进行查询和操作授权数据库预定义角色 ndb_owner：在数据库中有全部权限，即具有数据库管理员全部权限的角色。建立一个用户名为qiu、口令为whx的登录用户，并且在第一次登录时强制必须修改口令CREATE LOGIN qiu WITH PASSWORD=whx MUST_CHANGE,CHECK_EXPIRATION = ON暂时禁止用户wu登录ALTER LOGIN wu DISABLE重新允许用户wu登录ALTER LOGIN wu ENABLE假设在“学生”数据库中，并假设用户zhang和wang为该数据库的用户。例14：建立角色course_man，并指定由用户zhang管理。CREATE ROLE course_man AUTHORIZATION zhang例15：将角色course_man的名称修改为tech_man。ALTER ROLE course_man WITH NAME = tech_man例16：指定用户wang为角色tech_man的成员。sp_addrolemember rolename = tech_man,membername = wang例5-18：授予用户wang创建视图的权利，并且允许该用户将创建视图的权利授予其他合法用户。GRANT CREATE VIEW TO wang WITH GRANT OPTION例5-19：将选课表的查询权限和对考试成绩字段的修改权限授予角色tech_man。GRANT SELECT,UPDATE(考试成绩) ON 选课 TO tech_man WITH GRANT OPTION撤销角色tech_man对选课表的SELECT权限再授权的权利(不撤销该角色成员对选课表的SELECT权限)。REVOKE GRANT OPTION FOR SELECT ON 选课 FROM tech_man CASCADE禁止用户wang对选课表进行更新。DENY UPDATE ON 选课 TO wangCreate table stu2 as select * from stu where 1=2Create table stu3 as select 学号，姓名，专业from stu where 专业=“信息管理”insert into stu4 select * from stuinsert into stu5（学号，姓名） select 学号，姓名 from stucreate view stu_view as select 学号，姓名，专业 from stuinsert into stu_view values(101,张三,电商)游标查询更新处理例1Declare sno char(8),sname char(10),sex char(2),where char(6)Declare student_cursor cursor for select 学号，姓名，性别，生源 from 学生Open student_cursorPrint -学生列表-Print 学号 姓名 性别 生源Fetch from student_cursor into sno,sname,sex,whereWhere fetch_status=0Begin Print sno+ +sname+sex+ +where Fetch from student_cursor into sno,sname,sex,whereEndClose student_cursorDeallocate student_cursor例2Declare sno char(8),sname char(10)Declare cname char(24),grade tinyintDeclare student_cursor cursor for select 学号，姓名 from 学生Open student_cursorFetch from student_cursor into sno,snameWhere fetch_status=0Begin Print 学生：+sno+ +sname+选修了课程 Declare course_cursor cursor select 课程名称，考试成绩 from课程 join 选课 on 课程.课程编号=选课.课程编号 where 学号=sno Open course_cursor Fetch from course_cursor into cname,grade Where fetch_status=0 BeginPrint cname+ +str(grade,4) Fetch from course_cursor into cname,grade End Close course_cursor Deallocate course_cursor Fetch from student_cursor into sno,snameEndClose student_cursorDeallocate student_cursor例3 利用游标进行更新Declare grade tinyintDeclare grade_cursor cursor for select 考试成绩 from 选课Open grade_cursorFetch from grade_cursor into gradeWhere fetch_status=0Begin If grade between 55 and 59 BeginUpdate 选课 set 考试成绩=60 where current of grade_cursorPrint 修改了一个考试成绩 End Fetch from grade_cursor into gradeEndClose grade_cursorDeallocate grade_cursor1.为职工表建立一个更新触发器，当修改工资值超过6%时，给出警示信息。Create trigger salary on 职工 for update，insertAsDeclare salary_old int , salary_new intSelect salary_old=工资 from deletedSelect salary_new=工资 from insertedIf salry_newsalary_old *(1+0.06) BeginRaiserror(工资不得超过6%，16，1)Rollback transaction EndElse Raiserror(工资修改成功！，16，1)2.为订购单表建立一个插入和更新触发器，约束规则是：当订购单金额小于3000时，必须使用本地供应商（发出订购单的仓库和供应商在同一个城市），如果不满足要求，则拒绝操作，并给出错误信息。Create trigger city_trigger on 订购单 for update,insertAsDeclare city char(10),addr char(30),money moneySelect money=金额 from insertedIf money3000 and money is null Select city=城市 from 职工 a join inserted b on a.职工号=b.职工号 Select addr=地址 from 供应商 a join inserted b on a.供应商号=b.供应商号 If city!=addrBegin RAISERROR(必须使用本地供应商！，16，1) ROLLBACK TRANSACTIONEnd3. 使用DML触发器在“课程”和“教师”表之间强制实现业务规则；规定“专业基础”课的责任教师必须是“教授”或“副教授”，这时候“课程”表对“教师”表不是简单的参照，而必须定义相应的检查或约束规则，此规则不能简单用CHECK约束 实现，可以用触发器实现。Create trigger isprofessor on 课程 for AFTER update,insertAsDeclare duty char(6),grade char(6),ctype char(8)Select duty=责任教师，ctype=课程性质 from insertedIf ctype=专业基础 and duty is not null Select grade=职称 from 教师 t join inserted i on t.教师编号=i.责任教师 If grade not like %