《同济大学数据库原理与设计习题课》由会员分享，可在线阅读，更多相关《同济大学数据库原理与设计习题课（54页珍藏版）》请在金锄头文库上搜索。
1、数据库技术与应用数据库技术与应用习题课11.单选题（10小题）2.关系模式设计（2题）E-R图转换成关系模式3.关系操作（2题）关系代数表达式SQL语句表达式4.数据库管理（2题）存储过程代码，测试用例及结果触发器代码，测试用例及结果5.程序填空（5个空）-ADO.NET数据库访问技术题型题型2 ER 模型的设计过程：数据库设计者的任务是把现实世界中的数据以及数据间的联系抽象出来，用“实体”、“属性”与“联系”来表示。 具体设计步骤为：3数据库设计与数据库设计与ER模型模型u 设计实体及其属性设计实体及其属性v 设计联系及其属性设计联系及其属性 设某商业集团数据库中有3个实体集。一是“公司”实
2、体集，属性有公司编号、公司名、地址等；二是“仓库”实体集，属性有仓库编号、仓库名、地址等；三是“职工”实体集，属性有职工编号、姓名、性别等。4数据库设计举例（一）数据库设计举例（一）实体及其属性的描述体及其属性的描述 公司与仓库间存在“隶属”联系，每个公司管辖若干仓库，每个仓库只能属于一个公司管辖；仓库与职工之间存在聘用联系，每个仓库可聘用多个职工，每个职工只能在一个仓库工作，仓库聘用职工有聘期和工资。5u 试画出试画出ERER图，并在图上注明属性、联系的类型。图，并在图上注明属性、联系的类型。v 将将ERER图转换成关系模式集，并指出每个关系模式的图转换成关系模式集，并指出每个关系模式的主键
3、和外健。主键和外健。联系及其属性的描述系及其属性的描述6仓库（仓库编号号，仓库名，名，地址，公司地址，公司编号）号）公司（公司（公司公司编号号，公司名，地址），公司名，地址）职工（工（职工工编号号，姓名，性，姓名，性别，仓库编号，聘期，工号，聘期，工资） 设某商业集团数据库中有3个实体集。一是“商店”实体集，属性有商店编号、商店名、地址等；二是“商品”实体集，属性有商品号、商品名、规格、单价等；三是“职工”实体集，属性有职工编号、姓名、性别、业绩等。 商店与商品间存在“销售”联系，每个商店可销售多种商品，每种商品也可以放在多个商店销售，每个商店每销售一种商品，有月销售量；商店与职工间存在着“聘
4、用”关系，每个商店有许多职工，每个职工只能在一个商店工作，商店聘用职工有聘期和月薪。问题：u 试画出试画出ER ER 图，并在图上注明属性、联系的类型。图，并在图上注明属性、联系的类型。v 将将ERER图转换成关系模式集，并指出每个关系模式主键和外键。图转换成关系模式集，并指出每个关系模式主键和外键。7数据库设计实例数据库设计实例(二二)8性别姓名职工编号月薪聘期地址商店名商店编号规格商品名商品号职工职工商店商店商品商品聘用销售MMN N1 1N N单价业绩月销售量商店（商店（商店商店编号号，商店名，地址），商店名，地址）商品（商品（商品号商品号，商品名，商品名，规格，格，单价）价）职工（工（
5、职工工编号号，姓名，性，姓名，性别，业绩，商店商店编号，聘期，月薪）号，聘期，月薪）销售（售（商店商店编号号，商品号商品号，月，月销售量）售量）性别姓名职工编号月薪聘期地址商店名商店编号规格商品名商品号职工职工商店商店商品商品聘用销售单价业绩月销售量 供应商、项目和零件三者之间具有多对多的联系。即一个供应商可以供给若干项目多种零件，每个项目可以使用不同供应商供应的零件，每种零件可由不同供应商供给。实体描述如下： 供应商： 供应商号、姓名、地址、电话号码、帐号。 项目：项目号、预算、开工日期。 零件：零件号、名称、规格、单价、描述。u试画出试画出ER ER 图，并在图上注明属性、联系的类型。图，
6、并在图上注明属性、联系的类型。v将将ERER图转换成关系模式集，并指出每个关系模式主键和外图转换成关系模式集，并指出每个关系模式主键和外键。键。9数据库设计实例数据库设计实例(三三)10规格名称零件号供应量开工日期预算项目号地址姓名供应商号零件零件项目项目供应商供应商供应电话号码单价帐号描述供供应（项目号目号，供供应商号商号，零件号零件号，供，供应量）量）供供应商（商（供供应商号商号,姓名姓名,帐号号,地址地址,电话号号码） 零件（零件（零件号零件号,名称名称,描述描述,规格格,单价）价）项目（目（项目号目号,预算算,开工日期）开工日期）数据库设计实例数据库设计实例(四四)111.大学教材管理
7、信息系统包含下列实体及其属性：库位：库位号,区域,具体位置；教材：教材编号,教材名称,作者,出版日期,单价,出版社名称, 库位号,数量；课程：课程号,课程名称,课程描述,学分,开课系部,课程类型, 教材编号；教师：教师号,教师姓名,所属系部;学生：学号,姓名,性别,专业；研究生 ：导师。2.上述实体之间的联系可描述为：一类教材可存放在一个库位上，一个库位只能存放一类教材；一个出版社可出版多套教材，任一套教材只能归属于一个出版社；一门课程只能采用一套教材，一套教材可为多门课程选用；一位教师可在一学期讲授多门课程，一门课程可由多位教师来教，班级信息用以区别多位教师上同一门课程；一名学生可选多门课程
8、，一门课程可由多名学生选修;研究生是学生的一个子类。u试画出试画出ER ER 图，并在图上注明属性、联系的类型。图，并在图上注明属性、联系的类型。v将将ERER图转换成关系模式集，并指出每个关系模式主键和图转换成关系模式集，并指出每个关系模式主键和外键。外键。1.根据上述需求分析，设计满足需求的E-R图； 数据库设计数据库设计 库位库位教材教材学生学生教师教师出版社出版社课程课程选用选用库存库存讲授讲授选修选修出版出版数量数量日期日期学期学期班级班级成绩成绩研究生研究生isa导师导师2.将设计出的E-R图转换为关系模式。库位(库位号,区域,具体位置)；教材(教材编号,教材名称,作者,出版日期,
9、单价,出版社名称,库位号,数量)；出版社(出版社名称,联系人,电话,地址,邮编,电子邮箱)；课程(课程号,课程名称,课程描述,学分,开课系部,课程类型,教材编号)；讲授(教师号,课程号,班号,学期)；选课(学号,课程号,成绩)；教师(教师号,教师姓名,所属系部)；学生(学号,姓名,性别,专业)研究生(学号，导师)关系模型中常用的操作如下：u交 v并w差x笛卡儿积y投影选择联接 15关系代数运算关系代数运算自然连接除法：设有关系R（X，Y）和S（Y），其中X、Y可以使单个属性或属性集，则除法运算定义为：16关系代数运算关系代数运算17选择运算选择运算 选择运算是从指定的关系中选择某些元组形成一个
10、新的关系，被选择的元组是用满足某个逻辑条件来指定的。选择运算表示为： 其中R是关系名，是选择运算符，F是逻辑表达式。u选择指定的属性，形成一个可能含有重复行的表；v删除重复行，形成新的关系。18投影运算投影运算 投影运算对指定的关系进行投影操作，分两步产生一个新关系： 连接运算连接运算定义从两个关系的广义笛卡儿积中选取给定属性间满足一定条件的元组 A,B为R和S上度数相等且可比的属性列 为算术比较符，为等号时称为等值连接19A BRS=rs|r R s S rA sBRS= rA sB(RS)A B定义从两个关系的广义笛卡儿积中选取在相同属性列B上取值相等的元组，并去掉重复的行。自然连接与等值
11、连接的不同自然连接中相等的分量必须是相同的属性组，并且要在结果中去掉重复的属性，而等值连接则不必。当R与S无相同属性时，R S RS20自然连接自然连接象集（Image Set）o关系R(X , Z), X、Z是属性组，x是X上的取值，定义x在R中的象集为Zx=tZ|t R tX=xo 从R中选出在X上取值为x的元组，去掉X上的分量，只留Z上的分量21除运算除运算XZ张军同学所同学所选修修的全部的全部课程程x=张军张军Z Zx姓名课程张军物理王红数学张军数学课程数学物理除运算除运算除定义R(X , Y) S(Y) = x | x=rX rR Yx y(S)R(X , Y) S(Y) = trX
12、| trR Yx y(S)R S是R中满足下列条件的元组在X属性列上的投影:元组在X上的分量值x的象集Yx包含S在Y上投影的集合中。22设有关系模式：S(snum,sname,age,sex)SC(snum,cnum,grade)C(cnum,cname,teacher)试用关系代数表达式表达每个查询语句。u检索学习课程号为C2的学生学号与成绩。23关系代数实例关系代数实例v检索学习课程为C2的学生学号与姓名。w 检索选修课程名为Maths的学生学号与姓名。x 检索选修课程号为C2或C4的学生学号。24y 检索至少选修课程号为C2和C4的学生学号。 检索不学课程号为C2的学生姓名与年龄。25检
13、索学习全部课程的学生姓名。检索所学课程包含学生S3所学课程的学生学号。26Select Select 属性名属性名| |表达式表达式| |聚合函数聚合函数From From 表表Where Where 条件条件Group by Group by 属性名属性名Having Having 条件条件Order by Order by 属性名或表达式属性名或表达式 asc|descasc|desc27SQL SQL 语句语句查询的结果也是表查询的结果也是表不允许出现聚合函数不允许出现聚合函数WHEREWHERE的条件表达的条件表达查询条件查询条件 谓词谓词算术比较、=、=、!、!=确定范围BETWEE
14、NAND、NOT BETWEENAND集合属于IN、NOT IN字符匹配LIKE、NOT LIKE空值判断IS NULL、IS NOT NULL逻辑运算AND 、OR 、 NOT 281.检索学习课程号为C2的学生学号与成绩。2.检索学习课程为C2的学生学号与姓名。3.检索选修课程名为Maths的学生学号与姓名。4.检索选修课程号为C2或C4的学生学号。5.检索至少选修课程号为C2和C4的学生学号。6.检索不学课程号为C2的学生姓名与年龄。7.检索学习全部课程的学生姓名。8.检索所学课程包含学生S3所学课程的学生学号。29用用SQLSQL实现上述操作实现上述操作Select snum,grad
15、eFrom scWhere cnum=c2;301 1、检索学习课程号为、检索学习课程号为C2C2的学生学号与成绩。的学生学号与成绩。2 2、检索学习课程为、检索学习课程为C2C2的学生学号与姓名。的学生学号与姓名。Select snum,snameSelect snum,snameFrom sFrom sWhere snum in(select snumWhere snum in(select snum from sc from sc where cnum= where cnum=c2c2); );select snum,snamefrom swhere snum in(select snu
16、m from sc where cnum in(select cnum from c where cname=Maths);313 3 3 3、检索选修课程名为、检索选修课程名为、检索选修课程名为、检索选修课程名为MathsMathsMathsMaths的学生学号与姓名。的学生学号与姓名。的学生学号与姓名。的学生学号与姓名。4 4、检索选修课程号为、检索选修课程号为C2C2或或C4C4的学生学号。的学生学号。Select snumSelect snumFrom scFrom scWhere cnum=Where cnum=c2c2 or or cnum=cnum=c4c4; ;Select d
17、istinct snumFrom scWhere snum in(select snum from sc where cnum=c2) and snum in(select snum from sc where cnum=c4);325 5 5 5、检索至少选修课程号为、检索至少选修课程号为、检索至少选修课程号为、检索至少选修课程号为C2C2C2C2和和和和C4C4C4C4的学生学号。的学生学号。的学生学号。的学生学号。select sname,agefrom swhere snum not in(select snum from sc where cnum=c2);336 6 6 6、检索不
18、学课程号为、检索不学课程号为、检索不学课程号为、检索不学课程号为C2C2C2C2的学生姓名与年龄。的学生姓名与年龄。的学生姓名与年龄。的学生姓名与年龄。select sname,agefrom swhere snum in (select snum from sc where cnumc2);347 7 7 7、检索学习全部课程的学生姓名。、检索学习全部课程的学生姓名。、检索学习全部课程的学生姓名。、检索学习全部课程的学生姓名。selectsnamefroms,scwheres.snum=sc.snumgroupbys.snamehavingcount(cnum)=(selectcount(*
19、)fromc);358 8、检索所学课程包含学生、检索所学课程包含学生S3S3所所学课程的学生学号。学课程的学生学号。selectsnum,cnumfromscwherecnumin(selectcnumfromscwheresnum=s3)groupbysnum,cnumHavingcount(cnum)=(selectcount(cnum)fromscwheresnum=s3)SELECTdistinctsnumFROMscsWHERENOTEXISTS(selectcnumfromscwheresnum=s3andcnumNOTIN(SELECTumFROMscWHEREsc.snum=
20、s.snum)第二种方法第二种方法36select snameselect snamefrom s,scfrom s,scwhere s.snum=sc.snumwhere s.snum=sc.snumgroup by snamegroup by snamehaving min(grade)=80 and avg(grade)=90;having min(grade)=80 and avg(grade)=90;37求所有获奖学金的同学名单（条件：每门课程成绩求所有获奖学金的同学名单（条件：每门课程成绩求所有获奖学金的同学名单（条件：每门课程成绩求所有获奖学金的同学名单（条件：每门课程成绩在在在
21、在80808080分以上，平均成绩在分以上，平均成绩在分以上，平均成绩在分以上，平均成绩在90909090以上）以上）以上）以上） 设某商业集团数据库有3个实体集。一是“商品”实体集，属性有商品号、商品名、规格、单价等；二是“商店”实体集，属性有商品号、商店名、地址等；三是“供应商”实体集，属性有供应商编号、供应商名、地址等。 供应商与商品之间存在“供应”联系，每个供应商可供应多种商品每种商品可向多个供应商订购，每个供应商应供应每种商品有个月供应量；商店与商品间存在“销售”联系，每个商店可销售多种商品，每种商品可在多个商店销售，每个商店销售每种商品有月计划数。（1）试画出ER图，并在图上注明属
22、性、联系的类型。（2）将ER图转换成关系模式集，并指出每个关系模式的主键和外键。38思考题（一）思考题（一）设有关系模式：S(snum,sname,age,sex)SC(snum,cnum,grade)C(cnum,cname,teacher)试用关系代数表达式和SQL语言表达下列查询语句：u求解所有同学都选修的课程名。v求解至少选修了两门课程的学生名单。w求解王焱同学未选修的课程名。x求既没有选修“数据库”又没选修“计算机应用基础”的学生名单。39思考题（二）思考题（二）创建存储过程创建存储过程CREATECREATE PROCPROCedure edure procedure_name p
23、rocedure_name ; ; number number parameter data_type parameter data_type = = default default OUTOUT PUTPUT ,n n ASAS sql_statementsql_statement s s procedure_name：给出存储过程名；给出存储过程名；s s number：用于对同名的存储过程进行分组的整数；用于对同名的存储过程进行分组的整数；s s parameter：给出参数名；：给出参数名；s s data_type：指出参数的数据类型；：指出参数的数据类型；s s=default：给
24、出参数的默认值；：给出参数的默认值；s sOUTPUT(或或OUT)：表示该参数为输出参数；：表示该参数为输出参数；s ssql_statement：表示包含在过程中的一个或多个：表示包含在过程中的一个或多个T-SQL语句。语句。例例1 1 创建一个存储过程，完成显示所有学生的平均分数，数据创建一个存储过程，完成显示所有学生的平均分数，数据源为关系源为关系SC(snum,cnum,score)SC(snum,cnum,score)。CREATE PROC CREATE PROC sc_proc;1 sc_proc;1 -创建创建SCSC的第一个存储过程的第一个存储过程ASAS SELECTSE
25、LECT snum, snum, AVGAVG(score) (score) asas _avg _avg FROMFROM sc sc GROUP BY GROUP BY snumsnum调用存储调用存储过程过程用用EXECUTEEXECUTE命令运行存储过程命令运行存储过程 EXEC sc_proc;1EXEC sc_proc;1例例2 2 创建一个存储过程，以显示指定学号的学生的平均分数，数创建一个存储过程，以显示指定学号的学生的平均分数，数据源为关系据源为关系SC (snum,cnum,sc)SC (snum,cnum,sc)。CREATE PROC CREATE PROC sc_pr
26、oc;2sc_proc;2 -创建关系创建关系SCSC的第二个存储过程的第二个存储过程_snum_snum CHAR CHAR(4)(4) -输入参数定义输入参数定义ASAS SELECT SELECT snum,snum,AVGAVG(score)(score) as as _avg_avg FROM FROM scsc WHERE WHERE snum=_snumsnum=_snum GROUP BY GROUP BY snumsnum调用存储过程调用存储过程DECLARE DECLARE temp temp CHARCHAR(10) (10) -输入参数定义输入参数定义SET SET t
27、emp=temp=s001s001EXEC EXEC sc_proc;2sc_proc;2 temp temp -调用存储过程调用存储过程例例3 3 创建一个存储过程，完成通过输入指定学号得到该生的平创建一个存储过程，完成通过输入指定学号得到该生的平均分数，并保存在变量均分数，并保存在变量_avg_avg中，数据源为关系中，数据源为关系SCSC。 SC(snum,cnum,score) SC(snum,cnum,score)CREATE PROC CREATE PROC sc_proc ; 3 sc_proc ; 3 _snum _snum CHARCHAR(4),(4),_avg _avg
28、INT OUTPUTINT OUTPUTASAS SELECT SELECT _avg=_avg=AVGAVG(score)(score) FROM FROM scsc WHERE WHERE snum=_snumsnum=_snum GROUP BY GROUP BY snumsnum调用存储过程调用存储过程DECLARE DECLARE temptemp CHAR CHAR(4)(4) -定义输入参数定义输入参数DECLARE DECLARE avg_out avg_out INT INT -定义输出参数定义输出参数SET SET temp=temp= S001 S001EXEC EXEC
29、 sc_proc; 3 temp, avg_out sc_proc; 3 temp, avg_out OUTOUTPRINT PRINT S001 S001的平均分数为的平均分数为: : + + CAST CAST(avg_out (avg_out ASAS CHARCHAR(3)(3)创建触发器创建触发器 CREATETRIGGER触发器名触发器名ON表名表名|视图视图FOR|AFTER|INSTEADOFINSERT,DELETE,UPDATEAS触发器要完成的操作触发器要完成的操作例例1 1 建立一个建立一个INSERTINSERT触发器，每当在触发器，每当在SCSC表中插入一条记录时，
30、检查表中插入一条记录时，检查学号学号snumsnum的参照完整性，即插入记录中的学号是否在的参照完整性，即插入记录中的学号是否在studentstudent表中存表中存在，如果存在则允许插入，否则回滚插入操作。关系模式如下：在，如果存在则允许插入，否则回滚插入操作。关系模式如下： sc sc（snum,cnumsnum,cnum,score,score） student(student(snumsnum,cname,sex,age),cname,sex,age)CREATE TRIGGER CREATE TRIGGER sc_insertsc_insert ON ON scscFOR INSE
31、RTFOR INSERTASAS DECLARE DECLARE _snum _snum AS CHARAS CHAR(3)(3) SELECT SELECT _snum=i.snum _snum=i.snum FROM FROM inserted iinserted i IF IF NOT EXISTS NOT EXISTS (SELECT (SELECT * * FROM FROM studentstudent WHERE WHERE snum=_snumsnum=_snum) ) BEGIN BEGIN PRINT PRINT 该生不存在！该生不存在！ ROLLBACK TRAN ROL
32、LBACK TRAN END END ELSE ELSE PRINT PRINT 插入成功！插入成功！ 测试一测试一INSERT INTO sc INSERT INTO sc VALUES(203, C2,88)VALUES(203, C2,88) -203-203学号实际上不存在学号实际上不存在-该生不存在！该生不存在！ -执行执行INSERTINSERT的结果的结果测试二测试二INSERT INTO sc INSERT INTO sc VALUES(202, C3,88)VALUES(202, C3,88)-插入成功！插入成功！ -执行执行INSERTINSERT的结果的结果例例2 2 建
33、立一个建立一个DELETEDELETE触发器，每当在触发器，每当在studentstudent表中删除一条表中删除一条记录时，也将在记录时，也将在SCSC表中删除相应的记录。关系模式如下：表中删除相应的记录。关系模式如下： sc sc ( (snum,cnumsnum,cnum,score),score) student(student(snumsnum,cname,sex,age),cname,sex,age)CREATE TRIGGER CREATE TRIGGER student_delete student_delete ON ON studentstudentFOR DELETEFO
34、R DELETEASAS DECLARE DECLARE _snum _snum AS CHARAS CHAR(3)(3) SELECT SELECT _snum=d.snum _snum=d.snum FROM FROM deleted ddeleted d IF IF NOT EXISTS NOT EXISTS (SELECT (SELECT * * FROMFROM sc sc WHERE WHERE snum=_snumsnum=_snum) ) PRINT PRINT 已删除没有选课的学生！已删除没有选课的学生！ ELSEELSE BEGIN BEGIN DELETE FROM DE
35、LETE FROM sc sc WHERE WHERE snum=_snumsnum=_snum PRINT PRINT 成功删除！成功删除！ END END测试一测试一DELETE DELETE FROM studentFROM studentWHERE snum=202WHERE snum=202-（所影响的行数为（所影响的行数为 2 2 行）行） 成功删除！成功删除！（所影响的行数为（所影响的行数为 1 1 行）行）测试二测试二DELETEDELETEFROM studnetFROM studnetWHERE snum=101WHERE snum=101-已删除没有选课的学生！已删除没有
36、选课的学生！（所影响的行数为（所影响的行数为 1 1 行）行） student_delete student_delete触发器的作用是在删除触发器的作用是在删除studentstudent表的学生记录表的学生记录的同时删除选课表的同时删除选课表sc sc的相关记录，以维护数据的参照完整性。的相关记录，以维护数据的参照完整性。学号为学号为202202的同学选修了两门课，学号为的同学选修了两门课，学号为101101的同学没的同学没有选修任何课程，测试用例设计如下：有选修任何课程，测试用例设计如下：例例3 3 建立一个建立一个INSTEAD OF INSERTINSTEAD OF INSERT触发
37、器，每当对视图触发器，每当对视图course_viewcourse_view进行插入操作时，就在视图触发器中用进行插入操作时，就在视图触发器中用SQLSQL语句语句完成对视图的数据表（也叫基本表）的元组插入。完成对视图的数据表（也叫基本表）的元组插入。course表表视图视图course_viewCREATE VIEW CREATE VIEW course_view course_view AS AS SELECT SELECT cnum,cname,creditscnum,cname,creditsFROM FROM coursecourseCREATE TRIGGER CREATE TRI
38、GGER instead_of_insertinstead_of_insert ONON course_view course_viewINSTEAD OF INSERTINSTEAD OF INSERTASASDECLAREDECLARE_cnum _cnum CHARCHAR(4),(4),_cname _cname CHARCHAR(20),(20),_credits _credits SMALLINTSMALLINTSETSET _cnum=( _cnum=(SELECTSELECT cnum cnum FROM FROM inserted)inserted)SETSET _cname
39、=( _cname=(SELECTSELECT cname cname FROMFROM inserted) inserted)SETSET _credits=( _credits=(SELECTSELECT credits credits FROMFROM inserted) inserted)INSERT into INSERT into coursecourseVALUESVALUES(_cnum,_cname,_credits,(_cnum,_cname,_credits,NULLNULL, ,NULLNULL, ,NULLNULL) )测试触发器：测试触发器：INSERT INTO INSERT INTO course_view course_view VALUESVALUES( (C139C139, , 软件开发技术基础软件开发技术基础, ,3 3) )(1行受影响行受影响)(1行受影响行受影响)course表表视图视图course_view测试触发器：测试触发器：INSERT INTO INSERT INTO course_view course_view VALUESVALUES( (C139C139, , 软件开发技术基础软件开发技术基础, ,3 3) )(1行受影响行受影响)(1行受影响行受影响)
