1、1,数据库技术与应用,习题课,2,数据库设计与ER模型,ER 模型的设计过程：数据库设计者的任务就是要把现实世界中的数据以及数据间的联系抽象出来，用“实体”、“属性”与“联系”来表示。具体设计步骤为：,首先设计实体及其属性 再设计联系及其属性,3,数据库设计举例（一）,设某商业集团数据库中有3个实体集。一是“公司”实体集，属性有公司编号、公司名、地址等；二是“仓库”实体集，属性有仓库编号、仓库名、地址等；三是“职工”实体集，属性有职工编号、姓名、性别等。,实体及其属性的描述,4,公司与仓库间存在“隶属”联系，每个公司管辖若干仓库，每个仓库只能属于一个公司管辖；仓库与职工之间存在聘用联系，每个仓
2、库可聘用多个职工，每个职工只能在一个仓库工作，仓库聘用职工有聘期和工资。,联系及其属性的描述,（1）试画出ER图，并在图上注明属性、联系的类型。 （2）将ER图转换成关系模式集，并指出每个关系模式的主键和外健。,5,仓库（仓库编号，仓库名，地址，公司编号）,公司（公司编号，公司名，地址）,职工（职工编号，姓名，性别，仓库编号，聘期，工资）,6,数据库设计实例(二),设某商业集团数据库中有3个实体集。一是“商店”实体集，属性有商店编号、商店名、地址等；二是“商品”实体集，属性有商品号、商品名、规格、单价等；三是“职工”实体集，属性有职工编号、姓名、性别、业绩等。 商店与商品间存在“销售”联系，每
3、个商店可销售多种商品，每种商品也可以放在多个商店销售，每个商店每销售一种商品，有月销售量；商店与职工间存在着“聘用”关系，每个商店有许多职工，每个职工只能在一个商店工作，商店聘用职工有聘期和月薪。（1）试画出ER 图，并在图上注明属性、联系的类型。（2）将ER图转换成关系模式集，并指出每个关系模式主键和外键。,7,商店（商店编号，商店名，地址）,商品（商品号，商品名，规格，单价）,职工（职工编号，姓名，性别，业绩，商店编号，聘期，月薪）,销售（商店编号，商品号，月销售量）,8,数据库设计实例(三),供应商、项目和零件三者之间具有多对多的联系。即一个供应商 可以供给若干项目多种零件，每个项目可以
4、使用不同供应商供应 的零件，每种零件可由不同供应商供给。 实体描述如下： 供应商： 供应商号、姓名、地址、电话号码、帐号。 项目：项目号、预算、开工日期。 零件：零件号、名称、规格、单价、描述。 （1）试画出ER 图，并在图上注明属性、联系的类型。 （2）将ER图转换成关系模式集，并指出每个关系模式主键和外键。,9,供应（项目号，供应商号，零件号，供应量）,供应商（供应商号,姓名,帐号,地址,电话号码）,零件（零件号, 名称,描述,规格,单价）,项目（项目号,预算,开工日期）,10,数据库设计思考题,一个图书借阅管理数据库要求提供下述服务： （1）可随时查询书库中现有书籍的品种、数量与存放位置
5、。所有各类书籍均可由书号唯一标识。 （2）可随时查询书籍借还情况，包括借书人单位、姓名、借书证号、借书日期和还书日期。规则约定：任何人可借多种书，任何一种书可为多个人所借，借书证号具有唯一性。 （3）当需要时，可以通过数据库中保存的出版社的电报编号、电话、邮编及地址等信息向相应出版社增购有关书籍。规则约定：一个出版社可出版多种书籍，同一本书仅为一个出版社出版，出版社名具有唯一性。根据上述需求分析，试完成下列设计：1. 构造满足需求的E-R图。2. 转换为等价的关系模式。,11,12,关系代数运算,关系模型中常用的操作如下： 1、交 2、并 3、差 4、笛卡儿积 5、投影 6、选择7、联接,13
6、,关系代数运算,8、自然连接 9、除法：设有关系R（X，Y）和S（Y），其中X、Y可以使单个属性或属性集，则除法运算定义为：,14,关系代数实例,设有关系模式： S(snum,sname,age,sex) SC(snum,cnum,grade) C(cnum,cname,teacher) 试用关系代数表达式表达每个查询语句。 1、检索学习课程号为C2的学生学号与成绩。,15,2、检索学习课程为C2的学生学号与姓名。3、检索选修课程名为Maths的学生学号与姓名。4、检索选修课程号为C2或C4的学生学号。,16,5、检索至少选修课程号为C2和C4的学生学号。6、检索不学课程号为C2的学生姓名与年
7、龄。,17,7、检索学习全部课程的学生姓名。8、检索所学课程包含学生S3所学课程的学生学号。,18,Take a break!,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的学生
8、学号。 5.检索至少选修课程号为C2和C4的学生学号。 6.检索不学课程号为C2的学生姓名与年龄。 7.检索学习全部课程的学生姓名。 8.检索所学课程包含学生S3所学课程的学生学号。,22,1、检索学习课程号为C2的学生学号与成绩。,Select snum,grade From sc Where cnum=c2;,23,2、检索学习课程为C2的学生学号与姓名。,Select snum,sname From s Where snum in(select snumfrom scwhere cnum=c2);,24,3、检索选修课程名为Maths的学生学号与姓名。,select snum,sname
9、 from s where snum in(select snumfrom scwhere cnum in(select cnumfrom cwhere 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 snumfrom scwhere cnum=c2) and snum in(select snumfrom scwhere
10、 cnum=c4);,27,6、检索不学课程号为C2的学生姓名与年龄。,select sname,age from s where snum not in(select snumfrom scwhere cnum=c2);,select sname,age from s where snum in (select snumfrom scwhere cnumc2);,28,7、检索学习全部课程的学生姓名。,select sname from s where not exists( select *from c where not exists(select *from scwhere sc.sn
11、um=s.snumand um=um);,select sname from s,sc where s.snum=sc.snum group by s.snun having count(cnum)=(select count(*)from c);,29,8、检索所学课程包含学生S3 所学课程的学生学号。,select distinct snum from sc x where not exists(select *from sc ywhere y.snum=S3 and not exists(select *from sc zwhere x.snum=z.snum and um=um),se
12、lect 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 scwhere snum=s3),30,思考题（一）,设某商业集团数据库有3个实体集。一是“商品”实体集，属性有商品号、商品名、规格、单价等；二是“商店”实体集，属性有商品号、商店名、地址等；三是“供应商”实体集，属性有供应商编号、供应商名、地址等。供应商与商品之间存在“供应”联系，每个供应商可供应多种商品每种商品可向多个供应商
13、订购，每个供应商应供应每种商品有个月供应量；商店与商品间存在“销售”联系，每个商店可销售多种商品，每种商品可在多个商店销售，每个商店销售每种商品有月计划数。 （1）试画出ER图，并在图上注明属性、联系的类型。 （2）将ER图转换成关系模式集，并指出每个关系模式的主键和外键。,31,思考题（二）,设有关系模式： S(snum,sname,age,sex) SC(snum,cnum,grade) C(cnum,cname,teacher) 试用关系代数表达式和SQL语言表达下列查询语句： 1、求解所有同学都选修的课程名。 2、求解至少选修了两门课程的学生名单。 3、求解王焱同学未选修的课程名。 4、求既没有选修“数据库”又没选修“计算机应用基础”的学生名单。,
