同济大学数据库习题课.ppt

1,数据库技术与应用,习题课,2,关系代数运算,关系模型中常用的操作如下： 1.交 2.并 3.差 4.笛卡儿积 5.投影 6.选择 7.联接,3,关系代数运算,8.自然连接 9.除法：设有关系R（X，Y）和S（Y），其中X、Y可以使单个属性或属性集，则除法运算定义为：,4,关系代数实例,设有关系模式： S(snum,sname,age,sex) SC(snum,cnum,score) C(cnum,cname,teacher) 试用关系代数表达式表达每个查询语句。 1.检索学习课程号为C2的学生学号与成绩。,5,2.检索学习课程为C2的学生学号与姓名。 3.检索选修课程名为Maths的学生学号与姓名。 4.检索选修课程号为C2或C4的学生学号。,6,5.检索至少选修课程号为C2和C4的学生学号。 6.检索不学课程号为C2的学生姓名与年龄。,7,7.检索学习全部课程的学生姓名。 8.检索所学课程包含学生S3所学课程的学生学号。,8,Take a break!,9,数据库设计与ER模型,ER 模型的设计过程：数据库设计者的任务就是要把现实世界中的数据以及数据间的联系抽象出来，用“实体”、“属性”与“联系”来表示。具体设计步骤为：,首先设计实体及其属性 再设计联系及其属性,10,数据库设计举例（一）,设某商业集团数据库中有3个实体集。一是“公司”实体集，属性有公司编号、公司名、地址等；二是“仓库”实体集，属性有仓库编号、仓库名、地址等；三是“职工”实体集，属性有职工编号、姓名、性别等。,实体及其属性的描述,11,公司与仓库间存在“隶属”联系，每个公司管辖若干仓库，每个仓库只能属于一个公司管辖；仓库与职工之间存在聘用联系，每个仓库可聘用多个职工，每个职工只能在一个仓库工作，仓库聘用职工有聘期和工资。,联系及其属性的描述,（1）试画出ER图，并在图上注明属性、联系的类型。 （2）将ER图转换成关系模式集，并指出每个关系模式的主键和外健。,12,仓库（仓库编号，仓库名， 地址，公司编号）,公司（公司编号，公司名，地址）,职工（职工编号，姓名，性别， 仓库编号，聘期，工资）,13,数据库设计实例(二),设某商业集团数据库中有3个实体集。一是“商店”实体集，属性有商店编号、商店名、地址等；二是“商品”实体集，属性有商品号、商品名、规格、单价等；三是“职工”实体集，属性有职工编号、姓名、性别、业绩等。 商店与商品间存在“销售”联系，每个商店可销售多种商品，每种商品也可以放在多个商店销售，每个商店每销售一种商品，有月销售量；商店与职工间存在着“聘用”关系，每个商店有许多职工，每个职工只能在一个商店工作，商店聘用职工有聘期和月薪。 （1）试画出ER 图，并在图上注明属性、联系的类型。 （2）将ER图转换成关系模式集，并指出每个关系模式主键和外键。,14,商店（商店编号，商店名，地址）,商品（商品号，商品名，规格，单价）,职工（职工编号，姓名，性别，业绩，商店编号，聘期，月薪）,销售（商店编号，商品号，当年月份，月销售量）,15,数据库设计实例(三),供应商、项目和零件三者之间具有多对多的联系。即一个供应商 可以供给若干项目多种零件，每个项目可以使用不同供应商供应 的零件，每种零件可由不同供应商供给。 实体描述如下： 供应商： 供应商号、姓名、地址、电话号码、帐号。 项目：项目号、预算、开工日期。 零件：零件号、名称、规格、单价、描述。 （1）试画出ER 图，并在图上注明属性、联系的类型。 （2）将ER图转换成关系模式集，并指出每个关系模式主键和外键。,16,供应（项目号，供应商号，零件号，供应量）,供应商（供应商号,姓名,帐号,地址,电话号码）,零件（零件号, 名称,描述,规格,单价）,项目（项目号,预算,开工日期）,17,数据库设计思考题,一个图书借阅管理数据库要求提供下述服务： （1）可随时查询书库中现有书籍的品种、数量与存放位置。所有各类书籍均可由书号唯一标识。 （2）可随时查询书籍借还情况，包括借书人单位、姓名、借书证号、借书日期和还书日期。规则约定：任何人可借多种书，任何一种书可为多个人所借，借书证号具有唯一性。 （3）当需要时，可以通过数据库中保存的出版社的电报编号、电话、邮编及地址等信息向相应出版社增购有关书籍。规则约定：一个出版社在规划的日期可出版多种书籍，同一本书仅为一个出版社出版，出版社名具有唯一性。 根据上述需求分析，试完成下列设计： 1. 构造满足需求的E-R图。 2. 转换为等价的关系模式。,18,19,SQL 语句,Select 属性名|表达式|聚合函数 From 表 Where 条件 Group by 属性名 Having 条件 Order by 属性名或表达式 asc|desc Compute,查询的结果也是表,不允许出现聚合函数,20,WHERE的条件表达,21,用SQL实现上述操作,1.检索学习课程号为C2的学生学号与成绩。 2.检索学习课程为C2的学生学号与姓名。 3.检索选修课程名为Maths的学生学号与姓名。 4.检索选修课程号为C2或C4的学生学号。 5.检索至少选修课程号为C2和C4的学生学号。 6.检索不学课程号为C2的学生姓名与年龄。 7.检索学习全部课程的学生姓名。 8.检索所学课程包含学生S3所学课程的学生学号。,22,1.检索学习课程号为C2的学生学号与成绩。,Select snum,score From sc Where cnum=c2;,23,2.检索学习课程为C2的学生学号与姓名。,Select snum,sname From s Where snum in(select snum from sc where cnum=c2);,Select snum,sname From s,sc Where s.snum=sc.snum and cnum=c2,也可写为：,24,3.检索选修课程名为Maths的学生学号与姓名。,Select snum,sname From s Where snum in(select snum from sc where cnum in(select cnum from c where cname=Maths);,Select snum,sname From s,sc,c Where s.snum=sc.snum and um=um and cname=Maths,也可写为：,25,4.检索选修课程号为C2或C4的学生学号。,Select snum From sc Where cnum=c2 or cnum=c4;,26,5.检索至少选修课程号为C2和C4的学生学号。,Select distinct snum From sc Where snum in(select snum from sc where cnum=c2) and snum in(select snum from sc where cnum=c4);,27,6、检索不学课程号为C2的学生姓名与年龄。,select sname,age from s where snum not in(select snum from sc where cnum=c2);,select sname,age from s where snum in (select snum from sc where cnumc2);,28,7、检索学习全部课程的学生姓名。,select sname from s,sc where s.snum=sc.snum group by s.snun,sname having count(cnum)=( select count(*) from c);,29,8、检索所学课程包含学生S3 所学课程的学生学号。,select snum,cnum from sc where cnum in( select cnum from sc where snum=s3) group by snum,cnum Having count(cnum)=(select count(cnum) from sc where snum=s3),30,日常事务处理中的SQL查询,通配符的使用 单个字符： _ 多个字符：%,设有关系模式： S(snum,sname,age,sex,homeAddress) SC(snum,cnum,score),Select count(*) From S Where homeAddress like %湖南%,31,注意知识的灵活性,统计每位学生的平均成绩并按从高到低排序,select snum,avg(score) as avg_score from sc group by snum order by avg_score desc,select snum,avg(score) as avg_score from sc group by snum order by avg(score) desc,32,33,Like运算符与通配符,34,Like运算符,集合运算符,35,36,6、检索不学课程号为C2的学生姓名与年龄。,SELECT sname,age FROM s EXCEPT SELECT snum FROM sc WHERE cnum=c2);,37,求所有获奖学金的同学名单（条件：每门课程成绩在80分以上，平均成绩在90以上）,select sname from s,sc where s.snum=sc.snum group by s.snum,sname having min(score)=80 and avg(score)=90;,38,思考题（一）,设某商业集团数据库有3个实体集。一是“商品”实体集，属性有商品号、商品名、规格、单价等；二是“商店”实体集，属性有商品号、商店名、地址等；三是“供应商”实体集，属性有供应商编号、供应商名、地址等。 供应商与商品之间存在“供应”联系，每个供应商可供应多种商品每种商品可向多个供应商订购，每个供应商应供应每种商品有个月供应量；商店与商品间存在“销售”联系，每个商店可销售多种商品，每种商品可在多个商店销售，每个商店销售每种商品有月计划数。 （1）试画出ER图，并在图上注明属性、联系的类型。 （2