同济大学数据库原理与设计习题课.ppt

数据库技术与应用 习题课 1 单选题 10小题 关系模式设计 2题 E R图转换成关系模式关系操作 2题 关系代数表达式SQL语句表达式数据库管理 2题 存储过程代码 测试用例及结果触发器代码 测试用例及结果程序填空 5个空 ADO NET数据库访问技术 题型 2 ER模型的设计过程 数据库设计者的任务是把现实世界中的数据以及数据间的联系抽象出来 用 实体 属性 与 联系 来表示 具体设计步骤为 3 数据库设计与ER模型 设计实体及其属性设计联系及其属性 设某商业集团数据库中有3个实体集 一是 公司 实体集 属性有公司编号 公司名 地址等 二是 仓库 实体集 属性有仓库编号 仓库名 地址等 三是 职工 实体集 属性有职工编号 姓名 性别等 4 数据库设计举例 一 实体及其属性的描述 公司与仓库间存在 隶属 联系 每个公司管辖若干仓库 每个仓库只能属于一个公司管辖 仓库与职工之间存在聘用联系 每个仓库可聘用多个职工 每个职工只能在一个仓库工作 仓库聘用职工有聘期和工资 5 试画出ER图 并在图上注明属性 联系的类型 将ER图转换成关系模式集 并指出每个关系模式的主键和外健 联系及其属性的描述 6 仓库 仓库编号 仓库名 地址 公司编号 公司 公司编号 公司名 地址 职工 职工编号 姓名 性别 仓库编号 聘期 工资 设某商业集团数据库中有3个实体集 一是 商店 实体集 属性有商店编号 商店名 地址等 二是 商品 实体集 属性有商品号 商品名 规格 单价等 三是 职工 实体集 属性有职工编号 姓名 性别 业绩等 商店与商品间存在 销售 联系 每个商店可销售多种商品 每种商品也可以放在多个商店销售 每个商店每销售一种商品 有月销售量 商店与职工间存在着 聘用 关系 每个商店有许多职工 每个职工只能在一个商店工作 商店聘用职工有聘期和月薪 问题 试画出ER图 并在图上注明属性 联系的类型 将ER图转换成关系模式集 并指出每个关系模式主键和外键 7 数据库设计实例 二 8 商店 商店编号 商店名 地址 商品 商品号 商品名 规格 单价 职工 职工编号 姓名 性别 业绩 商店编号 聘期 月薪 销售 商店编号 商品号 月销售量 供应商 项目和零件三者之间具有多对多的联系 即一个供应商可以供给若干项目多种零件 每个项目可以使用不同供应商供应的零件 每种零件可由不同供应商供给 实体描述如下 供应商 供应商号 姓名 地址 电话号码 帐号 项目 项目号 预算 开工日期 零件 零件号 名称 规格 单价 描述 试画出ER图 并在图上注明属性 联系的类型 将ER图转换成关系模式集 并指出每个关系模式主键和外键 9 数据库设计实例 三 10 供应 项目号 供应商号 零件号 供应量 供应商 供应商号 姓名 帐号 地址 电话号码 零件 零件号 名称 描述 规格 单价 项目 项目号 预算 开工日期 数据库设计实例 四 11 大学教材管理信息系统包含下列实体及其属性 库位 库位号 区域 具体位置 教材 教材编号 教材名称 作者 出版日期 单价 出版社名称 库位号 数量 课程 课程号 课程名称 课程描述 学分 开课系部 课程类型 教材编号 教师 教师号 教师姓名 所属系部 学生 学号 姓名 性别 专业 研究生 导师 上述实体之间的联系可描述为 一类教材可存放在一个库位上 一个库位只能存放一类教材 一个出版社可出版多套教材 任一套教材只能归属于一个出版社 一门课程只能采用一套教材 一套教材可为多门课程选用 一位教师可在一学期讲授多门课程 一门课程可由多位教师来教 班级信息用以区别多位教师上同一门课程 一名学生可选多门课程 一门课程可由多名学生选修 研究生是学生的一个子类 试画出ER图 并在图上注明属性 联系的类型 将ER图转换成关系模式集 并指出每个关系模式主键和外键 根据上述需求分析 设计满足需求的E R图 数据库设计 库位 教材 学生 教师 出版社 课程 选用 库存 讲授 选修 出版 数量 日期 学期 班级 成绩 研究生 isa 导师 将设计出的E R图转换为关系模式 库位 库位号 区域 具体位置 教材 教材编号 教材名称 作者 出版日期 单价 出版社名称 库位号 数量 出版社 出版社名称 联系人 电话 地址 邮编 电子邮箱 课程 课程号 课程名称 课程描述 学分 开课系部 课程类型 教材编号 讲授 教师号 课程号 班号 学期 选课 学号 课程号 成绩 教师 教师号 教师姓名 所属系部 学生 学号 姓名 性别 专业 研究生 学号 导师 关系模型中常用的操作如下 交并差笛卡儿积投影选择 联接 15 关系代数运算 自然连接除法 设有关系R X Y 和S Y 其中X Y可以使单个属性或属性集 则除法运算定义为 16 关系代数运算 17 选择运算 选择运算是从指定的关系中选择某些元组形成一个新的关系 被选择的元组是用满足某个逻辑条件来指定的 选择运算表示为 其中R是关系名 是选择运算符 F是逻辑表达式 选择指定的属性 形成一个可能含有重复行的表 删除重复行 形成新的关系 18 投影运算 投影运算对指定的关系进行投影操作 分两步产生一个新关系 连接运算 定义从两个关系的广义笛卡儿积中选取给定属性间满足一定条件的元组A B为R和S上度数相等且可比的属性列 为算术比较符 为等号时称为等值连接 19 RS r A s B R S A B 定义从两个关系的广义笛卡儿积中选取在相同属性列B上取值相等的元组 并去掉重复的行 自然连接与等值连接的不同自然连接中相等的分量必须是相同的属性组 并且要在结果中去掉重复的属性 而等值连接则不必 当R与S无相同属性时 RS R S 20 自然连接 象集 ImageSet 关系R X Z X Z是属性组 x是X上的取值 定义x在R中的象集为Zx t Z t R t X x 从R中选出在X上取值为x的元组 去掉X上的分量 只留Z上的分量 21 除运算 XZ 张军同学所选修的全部课程 x 张军 Zx 除运算 除定义R X Y S Y x x r X r R Yx y S R X Y S Y tr X tr R 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 试用关系代数表达式表达每个查询语句 检索学习课程号为C2的学生学号与成绩 23 关系代数实例 检索学习课程为C2的学生学号与姓名 检索选修课程名为Maths的学生学号与姓名 检索选修课程号为C2或C4的学生学号 24 检索至少选修课程号为C2和C4的学生学号 检索不学课程号为C2的学生姓名与年龄 25 检索学习全部课程的学生姓名 检索所学课程包含学生S3所学课程的学生学号 26 Select属性名 表达式 聚合函数From表Where条件Groupby属性名Having条件Orderby属性名或表达式 asc desc 27 SQL语句 查询的结果也是表 不允许出现聚合函数 WHERE的条件表达 28 1 检索学习课程号为C2的学生学号与成绩 2 检索学习课程为C2的学生学号与姓名 3 检索选修课程名为Maths的学生学号与姓名 4 检索选修课程号为C2或C4的学生学号 5 检索至少选修课程号为C2和C4的学生学号 6 检索不学课程号为C2的学生姓名与年龄 7 检索学习全部课程的学生姓名 8 检索所学课程包含学生S3所学课程的学生学号 29 用SQL实现上述操作 Selectsnum gradeFromscWherecnum c2 30 1 检索学习课程号为C2的学生学号与成绩 2 检索学习课程为C2的学生学号与姓名 Selectsnum snameFromsWheresnumin selectsnumfromscwherecnum c2 selectsnum snamefromswheresnumin selectsnumfromscwherecnumin selectcnumfromcwherecname Maths 31 3 检索选修课程名为Maths的学生学号与姓名 4 检索选修课程号为C2或C4的学生学号 SelectsnumFromscWherecnum c2 orcnum c4 SelectdistinctsnumFromscWheresnumin selectsnumfromscwherecnum c2 andsnumin selectsnumfromscwherecnum c4 32 5 检索至少选修课程号为C2和C4的学生学号 selectsname agefromswheresnumnotin selectsnumfromscwherecnum c2 33 6 检索不学课程号为C2的学生姓名与年龄 selectsname agefromswheresnumin selectsnumfromscwherecnum c2 34 7 检索学习全部课程的学生姓名 selectsnamefroms scwheres snum sc snumgroupbys snamehavingcount cnum selectcount fromc 35 8 检索所学课程包含学生S3所学课程的学生学号 selectsnum cnumfromscwherecnumin selectcnumfromscwheresnum s3 groupbysnum cnumHavingcount cnum selectcount cnum fromscwheresnum s3 SELECTdistinctsnumFROMscsWHERENOTEXISTS selectcnumfromscwheresnum s3 andcnumNOTIN SELECTumFROMscWHEREsc snum s snum 第二种方法 36 selectsnamefroms scwheres snum sc snumgroupbysnamehavingmin grade 80andavg grade 90 37 求所有获奖学金的同学名单 条件 每门课程成绩在80分以上 平均成绩在90以上 设某商业集团数据库有3个实体集 一是 商品 实体集 属性有商品号 商品名 规格 单价等 二是 商店 实体集 属性有商品号 商店名 地址等 三是 供应商 实体集 属性有供应商编号 供应商名 地址等 供应商与商品之间存在 供应 联系 每个供应商可供应多种商品每种商品可向多个供应商订购 每个供应商应供应每种商品有个月供应量 商店与商品间存在 销售 联系 每个商店可销售多种商品 每种商品可在多个商店销售 每个商店销售每种商品有月计划数 1 试画出ER图 并在图上注明属性 联系的类型 2 将ER图转换成关系模式集 并指出每个关系模式的主键和外键 38 思考题 一 设有关系模式 S snum sname age sex SC snum cnum grade C cnum cname teacher 试用关系代数表达式和SQL语言表达下列查询语句 求解所有同学都选修的课程名 求解至少选修了两门课程的学生名单 求解王焱同学未选修的课程名 求既没有选修 数据库 又没选修 计算机应用基础 的学生名单 39 思考题 二 创建存储过程 CREATEPROC edure procedure name number parameterdata type default OUT PUT n ASsql statement sprocedure name 给出存储过程名 snumber 用于对同名的存储过程进行分组的整数 s parameter 给出参数名 sdata type 指出参数的数据类型 s default 给出参数的默认值 sOUTPUT 或OUT 表示该参数为输出参数 ssql statement 表示包含在过程中的一个或多个T SQL语句 例1创建一个存储过程 完成显示所有学生的平均分数 数据源为关系SC snum cnum score CREATEPROCsc proc 1 创建SC的第一个存储过程ASSELECTsnum AVG score as avgFROMscGROUPBYsnum 调用存储过程 用EXECUTE命令运行存储过程EXECsc proc 1 例2创建一个存储过程 以显示指定学号的学生的平均分数 数据源为关系SC snum cnum sc CREATEPROCsc proc 2 创建关系SC的第二个存储过程 snumCHAR 4 输入参数定义ASSELECTsnum AVG score as avgFROMscWHEREsnum snumGROUPBYsnum 调用存储过程 DECLARE tempCHAR 10 输入参数定义SET temp s001 EXECsc proc 2 temp 调用存储过程 例3创建一个存储过程 完成通过输入指定学号得到该生的平均分数 并保存在变量 avg中 数据源为关系SC SC snum cnum score CREATEPROCsc proc 3 snumCHAR 4 avgINTOUTPUTASSELECT avg AVG score FROMscWHEREsnum snumGROUPBYsnum 调用存储过程 DECLARE tempCHAR 4 定义输入参数DECLARE avg outINT 定义输出参数SET temp S001 EXECsc proc 3 temp avg outOUTPRINT S001的平均分数为 CAST avg outASCHAR 3 创建触发器 CREATETRIGGER触发器名ON表名 视图FOR AFTER INSTEADOF INSERT DELETE UPDATE AS触发器要完成的操作 例1建立一个INSERT触发器 每当在SC表中插入一条记录时 检查学号snum的参照完整性 即插入记录中的学号是否在student表中存在 如果存在则允许插入 否则回滚插入操作 关系模式如下 sc snum cnum score student snum cname sex age CREATETRIGGERsc insertONscFORINSERTASDECLARE snumASCHAR 3 SELECT snum i snumFROMinsertediIFNOTEXISTS SELECT FROMstudentWHEREsnum snum BEGINPRINT 该生不存在 ROLLBACKTRANENDELSEPRINT 插入成功 测试一INSERTINTOscVALUES 203 C2 88 203 学号实际上不存在 该生不存在 执行INSERT的结果 测试二INSERTINTOscVALUES 202 C3 88 插入成功 执行INSERT的结果 例2建立一个DELETE触发器 每当在student表中删除一条记录时 也将在SC表中删除相应的记录 关系模式如下 sc snum cnum score student snum cname sex age CREATETRIGGERstudent deleteONstudentFORDELETEASDECLARE snumASCHAR 3 SELECT snum d snumFROMdeleteddIFNOTEXISTS SELECT FROMscWHEREsnum snum PRINT 已删除没有选课的学生 ELSEBEGINDELETEFROMscWHEREsnum snumPRINT 成功删除 END 测试一DELETEFROMstudentWHEREsnum 202 所影响的行数为2行 成功删除 所影响的行数为1行 测试二DELETEFRO