数据库设计与SQL查询.docx

1、数据库设计与SQL查询数据库设计与SQL查询一、数据库设计假设某商业集团数据库中有一关系模式R如下：R (商店编号，商品编号，数量，部门编号，负责人) 如果规定：（1）每个商店的每种商品只在一个部门销售； （2）每个商店的每个部门只有一个负责人； （3）每个商店的每种商品只有一个库存数量。 试回答下列问题： （1）根据上述规定，写出关系模式R的基本函数依赖； （3分）（2）找出关系模式R的候选码； （1分）（3）试问关系模式R最高已经达到第几范式？为什么？ （2分）（4）如果R不属于3NF，请将R分解成3NF模式集。 （4分）参考答案：（1）有三个函数依赖： （商店编号，商品编号） 部门编号

2、（1分）（商店编号，部门编号） 负责人 （1分）（商店编号，商品编号） 数量 （1分）（2）R的候选码是 (商店编号，商品编号) （1分）（3）因为R中存在着非主属性“负责人”对候选码 (商店编号、商品编号)的传递函数依赖（1分），所以R属于2NF，R不属于3NF（1分）。 （4）将R分解成： R1 (商店编号，商品编号，数量，部门编号) （2分）R2 (商店编号，部门编号，负责人) （2分）二、数据库应用1. 某医院病房管理系统中，包括四个实体型，分别为：科室：科名，科地址，科电话病房：病房号，病房地址医生：工作证号，姓名，职称，年龄病人：病历号，姓名，性别 且存在如下语义约束： 一个科室有

3、多个病房、多个医生，一个病房只能属于一个科室，一个医生只属于一个科室； 一个医生可负责多个病人的诊治，一个病人的主管医生只有一个； 一个病房可入住多个病人，一个病人只能入住在一个病房。注意：不同科室可能有相同的病房号。 完成如下设计：（1）画出该医院病房管理系统的ER图；（8分）（2）将该E-R图转换为关系模型；（5分）（要求：1:1和1:n的联系进行合并）（3）指出转换结果中每个关系模式的主码和外码。（7分）参考答案：（1）本题的E-R图如下图所示。 （2）转化后的关系模式如下： 科室（科名，科地址，科电话） 病房（病房号，病房地址，科名） 医生（工作证号，姓名，职称，年龄，科名） 病人（病

4、历号，姓名，性别，主管医生，病房号，科名） （3）每个关系模式的主码、外码如下： 科室：主码是科名； 病房：主码是病房号，外码是科名； 医生：主码是工作证号，外码是科名； 病人：主码是病历号，外码是科名、病房号。评分标准：（1）四个联系各1分，名称一定要表达联系含义，联系类型错误不给分；四个实体型各1分，属性漏写、错写不给分。（2）转化后的科室关系模式、病房关系模式、医生关系模式各分，有一个属性不正确均不给分，病人关系模式2分，漏写、错写一个属性扣1分扣完2分为止。（3）科室：主码是科名（1分）； 病房：主码是科名十病房号（1分），外码是科名（1分）； 医生：主码是工作证号（1分），外码是科名

5、（1分）； 病人：主码是病历号（1分），外码是科名十病房号（1分）。2. 某企业集团有若干工厂，每个工厂生产多种产品，且每一种产品可以在多个工厂生产，每个工厂按照固定的计划数量生产产品；每个工厂聘用多名职工，且每名职工只能在一个工厂工作，工厂聘用职工有聘期和工资。工厂的属性有工厂编号、厂名、地址，产品的属性有产品编号、产品名、规格，职工的属性有职工号、姓名。（1）根据上述语义画出E-R图；（5分）（2）将该E-R模型转换为关系模型；（5分）（要求：1:1和1:n的联系进行合并）（3）指出转换结果中每个关系模式的主码和外码。（5分）参考答案： （1）本题的E-R图如下图所示。 （2）转化后的关系

6、模式如下： 工厂（工厂编号，厂名，地址） 产品（产品编号，产品名，规格） 职工（职工号，姓名，工产编号，聘期，工资） 生产（工产编号，产品编号，计划数量） （3）每个关系模式的主码、外码如下： 工厂：主码是工产编号； 产品：主码是产品编号； 职工：职工号，外码是工厂编号； 生产：主码是（工产编号，产品编号）， 外码是工产编号、产品编号。评分标准：（1）三个实体型工厂、产品、职工各1分，属性漏写、错写不给分；两个联系各1分，名称一定要表达联系含义，联系类型错误不给分，属性漏写不给分。（2）转化后的工厂关系模式、产品关系模式、生产关系模式各分，有一个属性不正确均不给分，职工关系模式2分。（3）工厂

7、：主码是工产编号（0.5分），无外码（0.5分）； 产品：主码是产品编号（0.5分），无外码（0.5分）； 职工：主码职工号（0.5分），外码是工厂编号（0.5分）； 生产：主码是（工产编号，产品编号）（1分）， 外码是工产编号（0.5分）、产品编号（0.5分）。三、SQL查询1. SQL基本查询语句格式SELECT DISTINCT FROM 关系名表（R1, R1, ）WHERE GROUP BY HAVING ORDER BY ASC/DESC 注意：（1）GROUP BY子句对WHERE子句所选取的行（记录）进行分组，HAVING子句选取满足条件的分组，故HAVING子句一定要放GRO

8、UP BY在子句的后面；（2）使用GROUP BY 子句时，该列名必须出现在SELECT后的中；（3）ORDER BY子句必须是SELECT查询语句的最后一个子句；（4）当聚合函数的值与其他属性的值相关时，需使用GROUP BY分组子句，而使用GROUP BY分组子句的先决条件是有AVG、COUNT、MAN、MIN、SUM等聚合函数；当一个聚合函数和一个GROUP BY子句一起使用时，聚合函数的作用范围就称为每个分组的所有记录。（5）为简化SQL语句输入，允许在SELECET查询语句中使用表的别名，以缩写表名；（6）为改变查询结果中现实的列名，可在SELECT语句的列名后使用“AS 标题名”，

9、这样在显示时便以该标题名来显示新的列名。（7）相关子查询：内层的条件涉及到外层属性的子查询称为相关子查询，参见（P76，李春葆）2. SQL运算符与谓词（1）谓词IN（集合成员运算符）表示“在集合中”使用 IN 谓词将一个值与其他几个值进行比较。例如：SELECT NAME FROM STAFF WHERE DEPT IN (20, 15)此示例相当于：SELECT NAME FROM STAFF WHERE DEPT = 20 OR DEPT = 15当子查询返回一组值时，可使用 IN 和 NOT IN 运算符。例如，下列查询列出负责项目 MA2100 和 OP2012 的雇员的姓：SELE

10、CT LASTNAME FROM EMPLOYEE WHERE EMPNO IN (SELECT RESPEMP FROM PROJECT WHERE PROJNO=MA2100 OR PROJNO=OP2012)计算一次子查询，并将结果列表直接代入外层查询。例如，上面的子查询选择雇员编号 10 和 330，对外层查询进行计算，就好象 WHERE 子句如下：WHERE EMPNO IN (10, 330)子查询返回的值列表可包含零个、一个或多个值。带IN的子查询(集合1) IN (集合2)(集合1) NOT IN (集合2)集合1与集合2可以是一个SELECT子查询或值的集合，但他们的结构必须

11、相同。对于第一个查询，如何集合1中的每个元素都在集合2中，其逻辑值为真，否则为假；对于第二个查询，如果集合1中的某个元素不在集合2中，逻辑值为真，否则为假。思考：如何采用IN和NOT IN表示一个集合是另一个集合的真子集？（2）EXISTS谓词，表示“存在于集合中”可使用子查询来测试满足某个条件的行的存在性。在此情况下，谓词 EXISTS 或 NOT EXISTS 将子查询链接到外层查询。当用 EXISTS 谓词将子查询链接到外层查询时，该子查询不返回值。相反，如果子查询的回答集包含一个或更多个行，则 EXISTS 谓词为真；如果回答集不包含任何行，则 EXISTS 谓词为假。通常将 EXIS

12、TS 谓词与相关子查询一起使用。下面示例列出当前在项目(PROJECT) 表中没有项的部门：SELECT DEPTNO, DEPTNAME FROM DEPARTMENT X WHERE NOT EXISTS (SELECT *FROM PROJECT WHERE DEPTNO = X.DEPTNO) ORDER BY DEPTNO可通过在外层查询的 WHERE 子句中使用 AND 和 OR 将 EXISTS 和 NOT EXISTS 谓词与其他谓词连接起来。带EXISTS的子查询带EXISTS的查询语句通常用来测试一个集合是否存在，如EXISTS (集合)NOT EXISTS (集合)对于第

13、一个查询，如果查询结果为非空，表示集合中至少存在一个元素，其逻辑值为真，否则为假；对于第二个查询，当集合为空时，其逻辑值为真，否则为假。（3）使用 BETWEEN 谓词使用 BETWEEN 谓词将一个值与某个范围内的值进行比较。范围两边的值是包括在内的，并考虑 BETWEEN 谓词中用于比较的两个表达式。下一示例寻找收入在 $10,000 和 $20,000 之间的雇员的姓名：SELECT LASTNAME FROM EMPLOYEE WHERE SALARY BETWEEN 10000 AND 20000这相当于：SELECT LASTNAME FROM EMPLOYEE WHERE SAL

14、ARY = 10000 AND SALARY 比较运算符作为示例（下面的注释也适用于其他运算符）：表达式 ALL （全查询）如果该表达式大于由全查询返回的每个单值，则该谓词为真。如果全查询未返回值，则该谓词为真。如果指定的关系至少对一个值为假，则结果为假。注意：ALL 定量谓词相当于 NOT IN 谓词。下列示例使用子查询和 ALL 比较来寻找收入超过所有经理的所有雇员的姓名和职业：SELECT LASTNAME, JOB FROM EMPLOYEE WHERE SALARY ALL (SELECT SALARY FROM EMPLOYEE WHERE JOB=/MANAGER/)表达式 AN

15、Y （全查询）如果表达式至少大于由全查询返回的值之一，则该谓词为真。如果全查询未返回值，则该谓词为假。注意：=ANY 定量运算符相当于 IN 谓词。表达式 SOME（全查询）（6）集合运算符UNION 集合成员运算符，表示“集合并”INTERSECT 集合成员运算符，表示“集合交”EXCEPT（MINUS） 集合成员运算符，表示“集合差”（7）使用谓词LIKE进行字符串的匹配运算语法格式：NOT LIKE 匹配字符串 ESCAPE 转义符（A）使用ESCAPE关键字定义转义符。在模式中，当转义符置于通配符之前时，该通配符就解释为普通字符。例如，要搜索在任意位置包含字符串 5% 的字符串，请使用

16、： WHERE ColumnA LIKE %5/% ESCAPE / （B）ESCAPE escape_character 允许在字符串中搜索通配符而不是将其作为通配符使用。escape_character 是放在通配符前表示此特殊用途的字符。 SELECT * FROM finances WHERE description LIKE gs_ ESCAPE S GO 意思就是：比如，我们要搜索一个字符串 g_ ,如果直接 like g_，那么 _的作用就是通配符，而不是字符，结果，我们会查到比如 ga,gb,gc,而不是我们需要的 g_. 用LIKE gs_ ESCAPE S s表示特殊用法标

17、志 （C）create table a (name varchar(10) go insert into a select 11 union all select 113 union all select 123 go select * from a WHERE name LIKE %/3 ESCAPE / -指定用/符号来说明跟在其后面的通配符字符为普能字符。(第二个%是字符不是通配符来的) go drop table a 结果为： name - 113 123 3. SQL的聚合函数（数值型统计函数）COUNT(*) 统计满足指定条件的元组（记录）个数COUNT(列名) 统计满足条件的列值

18、的个数MAX(列名) 求某列的最大属性值MIN(列名) 求某列的最小属性值SUM(列名) 对满足条件的数值类型的列求和AVG(列名) 对满足条件的数值类型的列求平均值注意：（1）这些聚合函数不能嵌套使用，行为MIN(AVG()的写法是非法的，即是不允许的。（2）使用DISTINCT选项可保证查询结果集中不存在重复元组（记录）。四、SQL查询实例已知关系模式：S（sno，sname， ssex，sage，sbirthday，class）；C(cno，cname，tno)，SC(sno，cno，degree)，T（tno，tname，tsex，tbirthday，prof，depart）。其中，S

19、为学生关系，sno为学号，sname为学生姓名，sage为学生年龄，ssex为学生性别，class为班级；C为课程关系，cno为课程号，cname为课程名称，cteacher为任课教师编号；Sc为学生选课关系，sno为学号，degree为学生的课程成绩；T为学生关系，sno为学号，sname为学生姓名，ssex为教师性别，prof为职称，depart为教师所在院系。分别使用关系代数表达式和SQL语言完成下列查询：（1）查询选修课程最多的学生姓名Select distinct sname from s where sno in (select sno from sc group by sno h

20、aving count(*)=ALL(select count(*) from sc group by sno)（2）查询选修人数多于5人的某课程的任课教师姓名方法1：select tname from t where tno in (select tno from c where cno in (select cno from sc group by cno having count(*)5)方法2：select tname from t where tno in (select tno from c,sc where o=o group by o having count(*)5)（4）查

21、询选修“王萍”所授课程的学生中，成绩最高的学生学号及其成绩方法1：select sno,degree from sc xwhere degree=(select max(degree) from sc where o in(select o from c where c.tno in (select t.tno from t where t.tname=王萍)AND X.cno in(select o from c where c.tno in (select t.tno from t where t.tname=王萍)方法2：集合交集的方法select sno,degree from sc

22、xwhere degree=(select max(degree) from sc where o in(select o from c where c.tno in (select t.tno from t where t.tname=王萍)intersectselect sno,degree from sc x where o in(select o from c where c.tno in (select t.tno from t where t.tname=王萍)（5）查询选修“3-105”号课程的学生中成绩最高的学生学号方法1：select sno from sc where cn

23、o=3-105 and degree=(select MAX(degree) from sc where cno=3-105)方法2：select sno from sc where cno=3-105 and degree=all(select degree from sc where cno=3-105)【注意，这里一定是大于或等于否则无法得到正确的结果】方法3：显示学生学号和姓名select sc.sno,sname from sc,s where sc.sno=s.sno and cno=3-105 and degree=all(select degree from sc where

24、cno=3-105)注意：这里一定要在主条件中添加cno=3-105条件子句，否则无法保证该分数对应的课程编号一定是3-105（6）查询没有选修过任何课程的学生姓名【哈工大考博真题】方法1：select distinct s.sname from s where s.sno not in (select sc.sno from sc)方法2：select * from s x where not exists (select * from sc where sc.sno=x.sno)（7）查询学生“陆君”没有选修过的课程编号方法1：select cno from c where cno not

25、 in (select o from sc,s where sc.sno=s.sno and s.sname=陆君)方法2：select cno from c where not exists (select * from sc,s where sc.sno=s.sno and o=o and s.sname=陆君)（8）查询不同课程成绩相同的学生的学号、课程号、学生成绩select distinct A.sno,B.sno from sc A,sc B where A.degree=B.degree and A.cno!=B.cno（9）查询95031班年龄最大的三名学生的姓名和出生日期，并

26、按年龄降序排序select top 3 sname,sbirthday,sage from s where class=95031 order by sage DESC注意：使用分组子句group by sage时，属性名sage必须在select中出现select sname,sbirthday,sage from s where class=95031 order by sage DESC limit 3（11）查询未担任任何一门课程的教师姓名及其所在院系select tname,depart from t where not exists (select * from c where t

27、.tno=c.tno)或select tname,depart from t where tno not in (select tno from c)（12）查询没有选修过“王萍”老师讲授的任何一门课程的学生姓名select distinct s.sname from s,sc where s.sno not in (select distinct sc.sno from sc,t,c where o=o and c.tno=t.tno and t.tname=王萍)（13）查询任何一门课程成绩在70分以上的姓名、课程名称和分数【哈工大考博真题】方法：任何一门课程成绩在70分以上，即该生的所有

28、成绩均在70分以上，也就是说该生的最低成绩大于70分select distinct s.sno,s.sname,o,sc.degreefrom s,scwhere sc.sno=s.sno group by sc.sno having min(degree)70（10）查询所有课程成绩小于60分的学生学号和姓名方法：所有课程成绩小于60分的学生，即该生的最高小于60分select s.sno,s.sname from sc,s where s.sno=sc.sno group by sc.sno having max(degree)60（12）把“王萍”老师教的课的成绩都更改为此课程的平均成绩方法1：update sc set degree=(select avg(degree) from SC,t,c where o=o and c.tno=t.tno and t.tname=王萍)where cno in (select o from sc,c,t where o=o and c.tno=t.tno and t.tname=王萍)方法2：update sc set degree=(select avg(x.degree) from SC x where o=sc.c