数据库设计与SQL查询.docx
《数据库设计与SQL查询.docx》由会员分享，可在线阅读，更多相关《数据库设计与SQL查询.docx（16页珍藏版）》请在冰豆网上搜索。
数据库设计与SQL查询
数据库设计与SQL查询
一、数据库设计
假设某商业集团数据库中有一关系模式R如下：
R（商店编号，商品编号，数量，部门编号，负责人）
如果规定：
（1）每个商店的每种商品只在一个部门销售；
（2）每个商店的每个部门只有一个负责人；
（3）每个商店的每种商品只有一个库存数量。
试回答下列问题：
（1）根据上述规定，写出关系模式R的基本函数依赖；（3分）
（2）找出关系模式R的候选码；（1分）
（3）试问关系模式R最高已经达到第几范式？
为什么？
（2分）
（4）如果R不属于3NF，请将R分解成3NF模式集。
（4分）
参考答案：
（1）有三个函数依赖：
（商店编号，商品编号）→部门编号（1分）
（商店编号，部门编号）→负责人（1分）
（商店编号，商品编号）→数量（1分）
（2）R的候选码是（商店编号，商品编号）（1分）
（3）因为R中存在着非主属性“负责人”对候选码（商店编号、商品编号）的传递函数依赖（1分），所以R属于2NF，R不属于3NF（1分）。
（4）将R分解成：
R1（商店编号，商品编号，数量，部门编号）（2分）
R2（商店编号，部门编号，负责人）（2分）
二、数据库应用
1.某医院病房管理系统中，包括四个实体型，分别为：
科室：
科名，科地址，科电话
病房：
病房号，病房地址
医生：
工作证号，姓名，职称，年龄
病人：
病历号，姓名，性别
且存在如下语义约束：
①一个科室有多个病房、多个医生，一个病房只能属于一个科室，一个医生只属于一个科室；
②一个医生可负责多个病人的诊治，一个病人的主管医生只有一个；
③一个病房可入住多个病人，一个病人只能入住在一个病房。
注意：
不同科室可能有相同的病房号。
完成如下设计：
（1）画出该医院病房管理系统的E－R图；（8分）
（2）将该E-R图转换为关系模型；（5分）
（要求：
1:
1和1:
n的联系进行合并）
（3）指出转换结果中每个关系模式的主码和外码。
（7分）
参考答案：
（1）本题的E-R图如下图所示。
（2）转化后的关系模式如下：
科室（科名，科地址，科电话）
病房（病房号，病房地址，科名）
医生（工作证号，姓名，职称，年龄，科名）
病人（病历号，姓名，性别，主管医生，病房号，科名）
（3）每个关系模式的主码、外码如下：
科室：
主码是科名；
病房：
主码是病房号，外码是科名；
医生：
主码是工作证号，外码是科名；
病人：
主码是病历号，外码是科名、病房号。
评分标准：
（1）四个联系各1分，名称一定要表达联系含义，联系类型错误不给分；四个实体型各1分，属性漏写、错写不给分。
（2）转化后的科室关系模式、病房关系模式、医生关系模式各１分，有一个属性不正确均不给分，病人关系模式2分，漏写、错写一个属性扣1分扣完2分为止。
（3）科室：
主码是科名（1分）；
病房：
主码是科名十病房号（1分），外码是科名（1分）；
医生：
主码是工作证号（1分），外码是科名（1分）；
病人：
主码是病历号（1分），外码是科名十病房号（1分）。
2.某企业集团有若干工厂，每个工厂生产多种产品，且每一种产品可以在多个工厂生产，每个工厂按照固定的计划数量生产产品；每个工厂聘用多名职工，且每名职工只能在一个工厂工作，工厂聘用职工有聘期和工资。
工厂的属性有工厂编号、厂名、地址，产品的属性有产品编号、产品名、规格，职工的属性有职工号、姓名。
（1）根据上述语义画出E-R图；（5分）
（2）将该E-R模型转换为关系模型；（5分）
（要求：
1:
1和1:
n的联系进行合并）
（3）指出转换结果中每个关系模式的主码和外码。
（5分）
参考答案：
（1）本题的E-R图如下图所示。
（2）转化后的关系模式如下：
工厂（工厂编号，厂名，地址）
产品（产品编号，产品名，规格）
职工（职工号，姓名，工产编号，聘期，工资）
生产（工产编号，产品编号，计划数量）
（3）每个关系模式的主码、外码如下：
工厂：
主码是工产编号；
产品：
主码是产品编号；
职工：
职工号，外码是工厂编号；
生产：
主码是（工产编号，产品编号），
外码是工产编号、产品编号。
评分标准：
（1）三个实体型工厂、产品、职工各1分，属性漏写、错写不给分；两个联系各1分，名称一定要表达联系含义，联系类型错误不给分，属性漏写不给分。
（2）转化后的工厂关系模式、产品关系模式、生产关系模式各１分，有一个属性不正确均不给分，职工关系模式2分。
（3）工厂：
主码是工产编号（0.5分），无外码（0.5分）；
产品：
主码是产品编号（0.5分），无外码（0.5分）；
职工：
主码职工号（0.5分），外码是工厂编号（0.5分）；
生产：
主码是（工产编号，产品编号）（1分），
外码是工产编号（0.5分）、产品编号（0.5分）。
三、SQL查询
1.SQL基本查询语句格式
SELECT[DISTINCT]<属性表（列名序列）>FROM关系名表（R1[<别名1>,R1[<别名1>,…]）
WHERE<行条件表达式>
[GROUPBY<分组属性表（分组列名序列）>[HAVING<分组条件表达式>]]
[ORDERBY<排序列名>[ASC/DESC]…]
注意：
（1）GROUPBY子句对WHERE子句所选取的行（记录）进行分组，HAVING子句选取满足条件的分组，故HAVING子句一定要放GROUPBY在子句的后面；
（2）使用GROUPBY<分组列名>子句时，该列名必须出现在SELECT后的<列名序列>中；
（3）ORDERBY子句必须是SELECT查询语句的最后一个子句；
（4）当聚合函数的值与其他属性的值相关时，需使用GROUPBY分组子句，而使用GROUPBY分组子句的先决条件是有AVG、COUNT、MAN、MIN、SUM等聚合函数；当一个聚合函数和一个GROUPBY子句一起使用时，聚合函数的作用范围就称为每个分组的所有记录。
（5）为简化SQL语句输入，允许在SELECET查询语句中使用表的别名，以缩写表名；
（6）为改变查询结果中现实的列名，可在SELECT语句的列名后使用“AS标题名”，这样在显示时便以该标题名来显示新的列名。
（7）相关子查询：
内层的条件涉及到外层属性的子查询称为相关子查询，参见（P76，李春葆）
2.SQL运算符与谓词
（1）谓词IN（集合成员运算符）表示“在集合中”
使用IN谓词将一个值与其他几个值进行比较。
例如：
SELECTNAMEFROMSTAFFWHEREDEPTIN（20,15）
此示例相当于：
SELECTNAMEFROMSTAFFWHEREDEPT=20ORDEPT=15
当子查询返回一组值时，可使用IN和NOTIN运算符。
例如，下列查询列出负责项目MA2100和OP2012的雇员的姓：
SELECTLASTNAMEFROMEMPLOYEEWHEREEMPNOIN（SELECTRESPEMPFROMPROJECTWHEREPROJNO='MA2100'ORPROJNO='OP2012'）
计算一次子查询，并将结果列表直接代入外层查询。
例如，上面的子查询选择雇员编号10和330，对外层查询进行计算，就好象WHERE子句如下：
WHEREEMPNOIN（10,330）
子查询返回的值列表可包含零个、一个或多个值。
带IN的子查询
（集合1）IN（集合2）
（集合1）NOTIN（集合2）
集合1与集合2可以是一个SELECT子查询或值的集合，但他们的结构必须相同。
对于第一个查询，如何集合1中的每个元素都在集合2中，其逻辑值为真，否则为假；对于第二个查询，如果集合1中的某个元素不在集合2中，逻辑值为真，否则为假。
思考：
如何采用IN和NOTIN表示一个集合是另一个集合的真子集？
（2）EXISTS谓词，表示“存在于集合中”
可使用子查询来测试满足某个条件的行的存在性。
在此情况下，谓词EXISTS或NOTEXISTS将子查询链接到外层查询。
当用EXISTS谓词将子查询链接到外层查询时，该子查询不返回值。
相反，如果子查询的回答集包含一个或更多个行，则EXISTS谓词为真；如果回答集不包含任何行，则EXISTS谓词为假。
通常将EXISTS谓词与相关子查询一起使用。
下面示例列出当前在项目（PROJECT）表中没有项的部门：
SELECTDEPTNO,DEPTNAMEFROMDEPARTMENTXWHERENOTEXISTS（SELECT*FROMPROJECTWHEREDEPTNO=X.DEPTNO）ORDERBYDEPTNO
可通过在外层查询的WHERE子句中使用AND和OR将EXISTS和NOTEXISTS谓词与其他谓词连接起来。
带EXISTS的子查询
带EXISTS的查询语句通常用来测试一个集合是否存在，如
EXISTS（集合）
NOTEXISTS（集合）
对于第一个查询，如果查询结果为非空，表示集合中至少存在一个元素，其逻辑值为真，否则为假；对于第二个查询，当集合为空时，其逻辑值为真，否则为假。
（3）使用BETWEEN谓词
使用BETWEEN谓词将一个值与某个范围内的值进行比较。
范围两边的值是包括在内的，并考虑BETWEEN谓词中用于比较的两个表达式。
下一示例寻找收入在$10,000和$20,000之间的雇员的姓名：
SELECTLASTNAMEFROMEMPLOYEEWHERESALARYBETWEEN10000AND20000
这相当于：
SELECTLASTNAMEFROMEMPLOYEEWHERESALARY>=10000ANDSALARY<=20000
下一个示例寻找收入少于$10,000或超过$20,000的雇员的姓名：
SELECTLASTNAMEFROMEMPLOYEEWHERESALARYNOTBETWEEN10000AND20000
（4）使用LIKE谓词
使用LIKE谓词搜索具有某些模式的字符串。
通过百分号和下划线指定模式。
下划线字符（_）表示任何单个字符，百分号（%）表示零或多个字符的字符串。
任何其他表示本身的字符。
下列示例选择以字母/'S/'开头长度为7个字母的雇员名：
SELECTNAMEFROMSTAFFWHERENAMELIKE/'S______/'
下一个示例选择不以字母/'S/'开头的雇员名：
SELECTNAMEFROMSTAFFWHERENAMENOTLIKE/'S%/'
（5）定量谓词
定量谓词将一个值和值的集合进行比较。
如果全查询返回多个值，则必须通过附加后缀ALL、ANY或SOME来修改谓词中的比较运算符。
这些后缀确定如何在外层谓词中处理返回的这组值。
使用>比较运算符作为示例（下面的注释也适用于其他运算符）：
表达式>ALL（全查询）
如果该表达式大于由全查询返回的每个单值，则该谓词为真。
如果全查询未返回值，则该谓词为真。
如果指定的关系至少对一个值为假，则结果为假。
注意：
<>ALL定量谓词相当于NOTIN谓词。
下列示例使用子查询和>ALL比较来寻找收入超过所有经理的所有雇员的姓名和职业：
SELECTLASTNAME,JOBFROMEMPLOYEEWHERESALARY>ALL（SELECTSALARYFROMEMPLOYEEWHEREJOB=/'MANAGER/'）
表达式>ANY（全查询）
如果表达式至少大于由全查询返回的值之一，则该谓词为真。
如果全查询未返回值，则该谓词为假。
注意：
=ANY定量运算符相当于IN谓词。
表达式>SOME（全查询）
（6）集合运算符
UNION集合成员运算符，表示“集合并”
INTERSECT集合成员运算符，表示“集合交”
EXCEPT（MINUS）集合成员运算符，表示“集合差”
（7）使用谓词LIKE进行字符串的匹配运算
语法格式：
[NOT]LIKE‘匹配字符串’[ESCAPE‘转义符’]
（A）使用ESCAPE关键字定义转义符。
在模式中，当转义符置于通配符之前时，该通配符就解释为普通字符。
例如，要搜索在任意位置包含字符串5%的字符串，请使用：
WHEREColumnALIKE'%5/%%'ESCAPE'/'
（B）ESCAPE'escape_character'
允许在字符串中搜索通配符而不是将其作为通配符使用。
escape_character是放在通配符前表示此特殊用途的字符。
SELECT*
FROMfinances
WHEREdescriptionLIKE'gs_'ESCAPE'S'
GO
意思就是：
比如，我们要搜索一个字符串"g_",如果直接like"g_"，那么"_"的作用就是通配符，而不是字符，结果，我们会查到比如"ga","gb","gc",而不是我们需要的"g_".
用LIKE'gs_'ESCAPE'S''s'表示特殊用法标志
（C）
createtablea（namevarchar（10））
go
insertintoaselect'11"'
unionallselect'113'
unionallselect'123'
go
select*fromaWHEREnameLIKE'%/3'ESCAPE'/'--指定用'/'符号来说明跟在其后面的通配符字符为普能字符。
（第二个%是字符不是通配符来的）
go
droptablea
结果为：
name
----------
113
123
3.SQL的聚合函数（数值型统计函数）
COUNT（*）统计满足指定条件的元组（记录）个数
COUNT（列名）统计满足条件的列值的个数
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为学生关系，sno为学号，sname为学生姓名，sage为学生年龄，ssex为学生性别，class为班级；C为课程关系，cno为课程号，cname为课程名称，cteacher为任课教师编号；Sc为学生选课关系，sno为学号，degree为学生的课程成绩；T为学生关系，sno为学号，sname为学生姓名，ssex为教师性别，prof为职称，depart为教师所在院系。
分别使用关系代数表达式和SQL语言完成下列查询：
（1）查询选修课程最多的学生姓名
Selectdistinctsnamefromswheresnoin（selectsnofromscgroupbysnohavingcount（*）>=ALL（selectcount（*）fromscgroupbysno））
（2）查询选修人数多于5人的某课程的任课教师姓名
方法1：
selecttnamefromtwheretnoin（selecttnofromcwherecnoin（selectcnofromscgroupbycnohavingcount（*）>5））
方法2：
selecttnamefromtwheretnoin（selecttnofromc,scwhereo=ogroupbyohavingcount（*）>5）
（4）查询选修“王萍”所授课程的学生中，成绩最高的学生学号及其成绩
方法1：
selectsno,degreefromscx
wheredegree=（selectmax（degree）fromscwhereoin
（selectofromcwherec.tnoin（selectt.tnofromtwheret.tname='王萍'）））
ANDX.cnoin
（selectofromcwherec.tnoin（selectt.tnofromtwheret.tname='王萍'））
方法2：
集合交集的方法
selectsno,degreefromscx
wheredegree=（selectmax（degree）fromscwhereoin
（selectofromcwherec.tnoin（selectt.tnofromtwheret.tname='王萍'）））
intersect
selectsno,degreefromscxwhereoin
（selectofromcwherec.tnoin（selectt.tnofromtwheret.tname='王萍'））
（5）查询选修“3-105”号课程的学生中成绩最高的学生学号
方法1：
selectsnofromscwherecno='3-105'anddegree=（selectMAX（degree）fromscwherecno='3-105'）
方法2：
selectsnofromscwherecno='3-105'anddegree>=all（selectdegreefromscwherecno='3-105'）
【注意，这里一定是大于或等于否则无法得到正确的结果】
方法3：
显示学生学号和姓名
selectsc.sno,snamefromsc,swheresc.sno=s.snoandcno='3-105'anddegree>=all（selectdegreefromscwherecno='3-105'）
注意：
这里一定要在主条件中添加cno='3-105'条件子句，否则无法保证该分数对应的课程编号一定是3-105
（6）查询没有选修过任何课程的学生姓名【哈工大考博真题】
方法1：
selectdistincts.snamefromswheres.snonotin（selectsc.snofromsc）
方法2：
select*fromsxwherenotexists（select*fromscwheresc.sno=x.sno）
（7）查询学生“陆君”没有选修过的课程编号
方法1：
selectcnofromcwherecnonotin（selectofromsc,swheresc.sno=s.snoands.sname='陆君'）
方法2：
selectcnofromcwherenotexists（select*fromsc,swheresc.sno=s.snoando=oands.sname='陆君'）
（8）查询不同课程成绩相同的学生的学号、课程号、学生成绩
selectdistinctA.sno,B.snofromscA,scBwhereA.degree=B.degreeandA.cno!
=B.cno
（9）查询95031班年龄最大的三名学生的姓名和出生日期，并按年龄降序排序
selecttop3sname,sbirthday,sagefromswhereclass='95031'orderbysageDESC
注意：
使用分组子句groupbysage时，属性名sage必须在select中出现
selectsname,sbirthday,sagefromswhereclass='95031'orderbysageDESClimit3
（11）查询未担任任何一门课程的教师姓名及其所在院系
selecttname,departfromtwherenotexists（select*fromcwheret.tno=c.tno）
或selecttname,departfromtwheretnonotin（selecttnofromc）
（12）查询没有选修过“王萍”老师讲授的任何一门课程的学生姓名
selectdistincts.snamefroms,sc
wheres.snonotin（selectdistinctsc.snofromsc,t,cwhereo=oandc.tno=t.tnoandt.tname='王萍'）
（13）查询任何一门课程成绩在70分以上的姓名、课程名称和分数【哈工大考博真题】
方法：
任何一门课程成绩在70分以上，即该生的所有成绩均在70分以上，也就是说该生的最低成绩大于70分
selectdistincts.sno,s.sname,o,sc.degree
froms,sc
wheresc.sno=s.snogroupbysc.snohavingmin（degree）>70
（10）查询所有课程成绩小于60分的学生学号和姓名
方法：
所有课程成绩小于60分的学生，即该生的最高小于60分
selects.sno,s.snamefromsc,swheres.sno=sc.snogroupbysc.snohavingmax（degree）<60
（12）把“王萍”老师教的课的成绩都更改为此课程的平均成绩
方法1：
updatescsetdegree=（selectavg（degree）fromSC,t,cwhereo=oandc.tno=t.tnoandt.tname='王萍'）
wherecnoin（selectofromsc,c,twhereo=oandc.tno=t.tnoandt.tname='王萍'）
方法2：
updatescsetdegree=（selectavg（x.degree）fromSCxwhereo=sc.c
升级会员 