1数据库原理复习题.docx
- 文档编号:11694488
- 上传时间:2023-03-30
- 格式:DOCX
- 页数:23
- 大小:421.63KB
1数据库原理复习题.docx
《1数据库原理复习题.docx》由会员分享,可在线阅读,更多相关《1数据库原理复习题.docx(23页珍藏版)》请在冰豆网上搜索。
1数据库原理复习题
2009-2010
(1)数据库原理复习题
SQL语言可以直接以命令方式交互使用,也可以嵌入到程序设计语言中以程序方式使用
例6:
规定学生表的性别字段不允许为空值。
ALTERTABLE学生ALTERCOLUMN性别char
(2)NOTNULL
INSERTINTOstudent.院系
VALUES(2,'计算机学院','张秋霞','教二楼3层')
违背PRIMARYKEY约束
CREATETABLEstudent.院系
(编号tinyintPRIMARYKEY,
名称char(30)UNIQUENOTNULL,
负责人char(10),
办公地点char(40))
INSERTINTOstudent.院系
VALUES(3,'经济管理学院','张秋霞','教二楼3层')
违背UNIQUE约束
CREATETABLEstudent.院系
(编号tinyintPRIMARYKEY,
名称char(30)UNIQUENOTNULL,
负责人char(10),
办公地点char(40)
)
违背NOTNULL约束
INSERTINTOstudent.学生(学号,院系,姓名,性别,生源,状态)
VALUES('04014111',1,'程鸣','女','湖南','正常')
注意:
学生表的院系列是计算列,是不能被插入语句操作的。
CREATETABLEstudent.学生
(学号char(8)PRIMARYKEY,
院系ASCONVERT(Tinyint,SUBSTRING(学号,3,2))PERSISTEDNOTNULL
FOREIGNKEYREFERENCESstudent.院系(编号),
姓名char(10)NOTNULL,
性别char
(2)CHECK(性别='男'OR性别='女'),
生源char(6),
状态char(4)CHECK(状态IN('正常','留级','休学','退学')))
+++++++
INSERTINTOstudent.选课VALUES('04014111','B00016',DEFAULT)
INSERTINTOstudent.选课VALUES('04014111','X00201',NULL)
+++++++++
插入操作时检查完整性?
检查实体完整性规则
检查参照完整性规则
检查用户定义完整性规则
检查实体完整性规则
如果插入元组的主关键字的属性不为空值、并且相应的属性值在关系中不存在(即保持唯一性),则可以执行插入操作,否则不可以执行插入操作。
检查参照完整性规则
如果是向被参照关系插入元组,则无须检查参照完整性;如果是向参照关系插入元组,则要检查外部关键字属性上的值是否在被参照关系中存在对应的主关键字的值,如果存在则可以执行插入操作,否则不允许执行插入操作。
另外,如果插入元组的外部关键字允许为空值,则当外部关键字是空值时也允许执行插入操作。
检查用户定义完整性规则
如果插入的元组在相应的属性值上遵守了用户定义完整性规则(包括CHECK约束、UNIQUE约束、NULL或NOTNULL约束),则可以执行插入操作,否则不可以执行插入操作。
+++++
ALTERTABLEstudent.课程
ADD责任教师char(6)NULL
CONSTRAINTref_teacherFOREIGNKEYREFERENCESstudent.教师(教师编号)
ONDELETESETNULL
ONUPDATECASCADE
DELETEstudent.教师WHERE教师编号='011122'
删除成功。
如果有课程记录的责任教师参照被删除记录,则将该课程记录的责任教师字段置为NULL。
删除操作只需要检查参照完整性
如果删除的是参照关系的元组,则不需要进行参照完整性检查,可以执行删除操作。
如果删除的是被参照关系的元组,则检查被删除元组的主关键字属性的值是否被参照关系中某个元组的外部关键字引用,如果未被引用则可以执行删除操作;否则可能有4种情况:
–拒绝删除、空值删除、级联删除、默认值删除
执行更新操作时检查完整性
执行更新操作可以看作是先删除旧的元组,然后再插入新的元组。
所以执行更新操作时的完整性检查综合了插入和删除操作的数据完整性检查。
例3-68:
查询哪些学生(学号和姓名)有未完成考试的课程(考试成绩为NULL)。
例3-69:
查询哪些学生(学号和姓名)没有未完成考试的课程(考试成绩为NULL)。
视图示意
从基本表院系和教师派生出虚拟的教师列表
建立一个“教师列表”视图,包含院系表中的名称字段(在视图中命名为院系)和教师表中的姓名、性别及职称字段。
变量的赋值
⏹对变量赋值的命令是
SELECT@<变量名>=<表达式>[,@<变量名>=<表达式>…]
⏹或
SET@<变量名>=<表达式>
⏹或
SELECT@<变量名>=<表达式>[,@<变量名>=<表达式>…]FROM<表名>…
⏹前两种格式是直接对变量进行赋值,第三种格式是将SELECT查询的结果赋值给变量
游标的使用
⏹打开游标
⏹从游标中读记录
⏹循环控制遍历游标的所有记录
⏹关闭游标
⏹释放游标
⏹与程序设计语言中的文件相对照:
⏹DECLARECURSOR相当于说明了一个文件
⏹OPEN相当于打开文件
⏹FETCH相当于读一条记录
⏹CLOSE相当于关闭文件
⏹DEALLOCATE语句相当于删除文件。
存储过程的作用
⏹可以避免在网络上传输大量无用的信息或原始数据,只需要传输调用存储过程的指令和数据库服务器返回的处理结果
⏹把完成某一数据库处理的功能设计为存储过程,则可以在各个程序中反复调用,从而减轻程序的编写工作量
⏹可以利用存储过程间接实现一些安全控制功能
⏹创建存储过程通常是在数据库设计和开发阶段完成的
⏹存储过程可以嵌套,即在一个存储过程中可以调用另外一个存储过程
⏹存储过程一般用来完成数据查询和数据处理操作
⏹在存储过程中不可以使用创建数据库对象的语句(如CREATETABLE等各种CREATE语句)
触发器
⏹触发器是一类特殊的存储过程,它在满足某个特定条件时自动触发执行。
⏹分为DML(数据操作语言)触发器、DDL(数据定义语言)触发器和LOGIN触发器
⏹DML触发器在执行数据操作语言时触发
⏹DDL触发器在执行数据定义语言时触发
⏹LOGIN触发器在有用户登录时触发
DML触发器
⏹定义在表上,当对表进行插入、删除或修改时触发,即可能使数据发生变化时触发
⏹DML触发器分为插入触发器、删除触发器和更新触发器三类
⏹触发器的三个要素:
表、触发的事件、采取的动作
⏹事件发生时自动触发,用户无选择的权利
deleted表和inserted表
⏹当DML触发器激活时系统会自动产生两个特殊的临时表:
deleted表和inserted表
⏹当发生INSERT操作时新插入的记录也存储在inserted表
⏹当发生DELETE操作时被删除的记录也存储在deleted表
⏹当发生UPDATE操作时修改前的旧记录也存储在deleted表、修改后的新记录也存储在inserted表
deleted表和inserted表的作用?
⏹可以使用deleted表和inserted表判断正在操作的记录是否符合要求,从而检查错误并采取相应的措施
⏹找出数据修改前、后表的状态差异,并基于该差异采取相应的措施
⏹可以扩展表之间的参照完整性
deleted表和inserted表只在触发器内可用,一旦触发器完成任务,这两个系统产生的临时表将自动删除
⏹使用DML触发器在“课程”和“教师”表之间强制实现业务规则;规定“专业基础”课的责任教师必须是“教授”或“副教授”,这时候“课程”表对“教师”表不是简单的参照,而必须定义相应的检查或约束规则,此规则不能简单用CHECK约束实现,可以用触发器实现。
createtriggerT_借还书登记_Inson借还书登记FORINSERT
AS
BEGIN
DECLARE@tol_numsmallint
DECLARE@numsmallint
SELECT@tol_num=可借阅数量
FROMINSERTEDi,读者r,读者身份表b
WHEREr.读者类型=b.读者类型ANDi.读者编号=r.读者编号
SELECT@num=count(*)
FROMINSERTEDi,借还书登记b
WHEREb.还书日期ISNULLANDi.读者编号=b.读者编号
IF@num>=@tol_num
BEGIN
RAISERROR('已经超过借阅数量,不能借阅!
',16,1)
ROLLBACK
END
END
insertinto借还书登记(书号,读者编号)values('b401','101')
insertinto借还书登记(书号,读者编号)values('b401','102')
createprocf_day1
@cardvarchar(10),@book_novarchar(20)
as
begin
declare@允许天数int
declare@借阅天数int
declare@dayint
select@允许天数=可借阅天数FROM读者r,读者身份表b
WHEREr.读者类型=b.读者类型AND读者编号=@card
SELECT@借阅天数=DATEDIFF(day,借阅日期,GETDATE())
FROM借还书登记
WHERE还书日期ISNULLAND读者编号=@cardAND书号=@book_no
IF@借阅天数ISNULL
SET@day=@借阅天数
ELSE
SET@day=@借阅天数-@允许天数
RETURN@day
END
declare@count_dayint
execute@count_day=f_day1'101','b101'
print'天数',@count_day
数据库设计的基本任务
⏹数据库设计的基本任务就是根据用户的信息需求,处理需求和数据库的支撑环境(包括DBMS、操作系统、硬件),设计一个结构合理、使用方便、效率较高的数据库。
⏹信息需求是指在数据库中应该存储和管理哪些数据对象;
⏹处理需求是指需要进行哪些业务处理和操作,如对数据对象的查询、增加、删除、修改、统计等操作。
数据库设计步骤
⏹需求分析阶段
⏹概念结构设计阶段
⏹逻辑结构设计阶段
⏹物理结构设计阶段
⏹数据库实施阶段
⏹数据库运行和维护阶段
⏹PowerDesigner是一个功能强大而使用方便的工具集,为新一代数据库应用的建模提供了全面的支持。
⏹PowerDesigner12.0提供了如下7种模型:
⏹需求分析模型(RequirementsModel——RQM)
⏹企业业务流程模型(BusinessProcessModel——BPM)
⏹概念数据模型(ConceptualDataModel——CDM)
⏹物理数据模型(PhysicalDataModel——PDM)
⏹面向对象模型(ObjectOrientedModel——OOM)
⏹信息流动模型(InformationLiquidityModel——ILM)
⏹XML模型(XMLModel)
⏹概念模型设计的结果是得到数据库的概念结构,或称概念数据模型。
⏹概念数据模型与具体的硬件环境和软件环境均无关。
设计概念模型的一般步骤
(1)先设计面向全局应用的全局概念结构的初步框架,即先建立起整个系统的总体框架;
(2)然后根据部门或功能划分成局部应用;
(3)依据划分后的局部应用完成局部E-R图的设计;
(4)最后将局部E-R图合并、转换成全局E-R图,完成概念模型的设计。
概念数据模型设计的基本内容
概念模型的设计或描述的最常用工具是E-R图,这一步的工作至少要包括以下内容:
(1)确定实体(包括弱实体);
(2)确定实体的属性;
(3)确定实体的标识属性(关键字);
(4)确定实体间的联系和联系类型;
(5)画出表示概念模型的E-R图(利用相应的建模工具,如PowerDesinger)。
⏹各局部E-R图之间的冲突主要有:
⏹属性冲突
⏹命名冲突
⏹结构冲突
⏹属性冲突
⏹属性的值域冲突,简称域冲突,即属性值的类型、取值范围或取值集合不同。
⏹属性取值单位冲突,即属性值的单位不同。
⏹命名冲突
⏹同名异义,即不同意义的实体名、属性名或联系名在不同的局部E-R图中有相同的名字。
⏹异名同义(一义多名),即相同意义的实体名、属性名或联系名在不同的局部E-R图中有不同的名字。
⏹属性冲突和命名冲突的解决方法
⏹可以通过讨论,协商统一即可。
⏹使用PowerDesigner设计数据库的处理流程是:
⏹设计概念数据模型;
⏹由概念数据模型(CDM)生成初步的物理数据模型(PDM);
⏹在生成的物理数据模型中完成物理数据库设计;
⏹生成创建目标数据库的脚本甚至可以直接创建目标数据库。
逻辑阶段设计的任务是将概念模型转换成某个具体的DBMS所支持的数据模型,一般称之为逻辑模型,这里只介绍到关系模型的转换。
转换后的数据模型应与转换前的E-R模型保持一致的应用语义。
逻辑模型设计的一般过程
⏹E-R模型向关系模型的转换
⏹使用PowerDesigner将概念模型转换成逻辑(物理)模型
⏹使用PowerDesigner设计逻辑(物理)模型
⏹关系模式优化
E-R模型向关系模型的转换
⏹如果在概念模型设计阶段已经将多对多联系转换成了一对多联系,则在逻辑模型设计阶段把E-R模型转换为关系模型将非常简单:
⏹将每一个实体转换为一个关系模式,使其包含对应实体的全部属性,并根据语义确定关键字(实际在概念模型阶段已经确定)
⏹将一对多的联系直接并入n端实体的关系模式,这只需要将“1”端实体的关系模式的主关键字纳入n端实体的关系模式,并作为外部关键字
⏹将一对一联系的两个关系模式合并为一个关系模式
数据库管理系统的身份识别机制
⏹身份识别的3个层次
●系统登录
●数据库访问
●数据操作
⏹一般系统管理员用户管理数据库管理员用户
⏹数据库管理员用户管理数据库对象用户
⏹数据库对象用户向一般用户进行查询和操作授权
数据库预定义角色
⏹db_owner:
在数据库中有全部权限,即具有数据库管理员全部权限的角色。
建立一个用户名为qiu、口令为whx的登录用户,并且在第一次登录时强制必须修改口令
CREATELOGINqiu
WITHPASSWORD='whx'MUST_CHANGE,
CHECK_EXPIRATION=ON
暂时禁止用户wu登录
ALTERLOGINwuDISABLE
重新允许用户wu登录
ALTERLOGINwuENABLE
假设在“学生”数据库中,并假设用户zhang和wang为该数据库的用户。
例14:
建立角色course_man,并指定由用户zhang管理。
CREATEROLEcourse_manAUTHORIZATIONzhang
例15:
将角色course_man的名称修改为tech_man。
ALTERROLEcourse_manWITHNAME=tech_man
例16:
指定用户wang为角色tech_man的成员。
sp_addrolemember@rolename='tech_man',@membername='wang'
例5-18:
授予用户wang创建视图的权利,并且允许该用户将创建视图的权利授予其他合法用户。
GRANTCREATEVIEWTOwangWITHGRANTOPTION
例5-19:
将选课表的查询权限和对考试成绩字段的修改权限授予角色tech_man。
GRANTSELECT,UPDATE(考试成绩)
ON选课TOtech_man
WITHGRANTOPTION
撤销角色tech_man对选课表的SELECT权限再授权的权利(不撤销该角色成员对选课表的SELECT权限)。
REVOKEGRANTOPTIONFORSELECT
ON选课
FROMtech_manCASCADE
禁止用户wang对选课表进行更新。
DENYUPDATEON选课TOwang
Createtablestu2asselect*fromstuwhere1=2
Createtablestu3asselect学号,姓名,专业
fromstuwhere专业=“信息管理”
insertintostu4select*fromstu
insertintostu5(学号,姓名)select学号,姓名fromstu
createviewstu_viewasselect学号,姓名,专业fromstu
insertintostu_viewvalues(‘101’,’张三’,’电商’)
游标查询更新处理
例1
Declare@snochar(8),@snamechar(10),@sexchar
(2),@wherechar(6)
Declarestudent_cursorcursorforselect学号,姓名,性别,生源from学生
Openstudent_cursor
Print‘-----------学生列表------------‘
Print‘学号姓名性别生源’
Fetchfromstudent_cursorinto@sno,@sname,@sex,@where
Where@@fetch_status=0
Begin
Print@sno+’‘+@sname+@sex+’‘+@where
Fetchfromstudent_cursorinto@sno,@sname,@sex,@where
End
Closestudent_cursor
Deallocatestudent_cursor
例2
Declare@snochar(8),@snamechar(10)
Declare@cnamechar(24),@gradetinyint
Declarestudent_cursorcursorforselect学号,姓名from学生
Openstudent_cursor
Fetchfromstudent_cursorinto@sno,@sname
Where@@fetch_status=0
Begin
Print‘学生:
’+@sno+’‘+@sname+’选修了课程’
Declarecourse_cursorcursorselect课程名称,考试成绩from课程join选课on课程.课程编号=选课.课程编号where学号=@sno
Opencourse_cursor
Fetchfromcourse_cursorinto@cname,@grade
Where@@fetch_status=0
Begin
Print‘@cname+’‘+str(@grade,4)
Fetchfromcourse_cursorinto@cname,@grade
End
Closecourse_cursor
Deallocatecourse_cursor
Fetchfromstudent_cursorinto@sno,@sname
End
Closestudent_cursor
Deallocatestudent_cursor
例3利用游标进行更新
Declare@gradetinyint
Declaregrade_cursorcursorforselect考试成绩from选课
Opengrade_cursor
Fetchfromgrade_cursorinto@grade
Where@@fetch_status=0
Begin
If@gradebetween55and59
Begin
Update选课set考试成绩=60wherecurrentofgrade_cursor
Print‘修改了一个考试成绩’
End
Fetchfromgrade_cursorinto@grade
End
Closegrade_cursor
Deallocategrade_cursor
1.为职工表建立一个更新触发器,当修改工资值超过6%时,给出警示信息。
Createtriggersalaryon职工forupdate,insert
As
Declare@salary_oldint,@salary_newint
Select@salary_old=工资fromdeleted
Select@salary_new=工资frominserted
If@salry_new>@salary_old*(1+0.06)
Begin
Raiserror(‘工资不得超过6%‘,16,1)
Rollbacktransaction
End
Else
Raiserror(‘工资修改成功!
‘,16,1)
2.为订购单表建立一个插入和更新触发器,约束规则是:
当订购单金额小于3000时,必须使用本地供应商(发出订购单的仓库和供应商在同一个城市),如果不满足要求,则拒绝操作,并给出错误信息。
Createtriggercity_triggeron订购单forupdate,insert
As
Declare@citychar(10),@addrchar(30),@moneymoney
Select@money=金额frominserted
If@money<3000and@moneyisnull
Select@city=城市from职工ajoininsertedbona.职工号=b.职工号
Select@addr=地址from供应商ajoininsertedbona.供应商号=b.供应商号
If@city!
=@addr
Begin
RAISERROR(‘必须使用本地供应商!
‘,16,1)
ROLLBACKTRANSACTION
End
3.使用DML触发器在“课程”和“教师”表之间强制实现业务规则;规定“专业基础”课的责任教师必须是“教授”或“副教授”,这时候“课程”表对“教师”表不是
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- 数据库 原理 复习题