数据库设计与SQL查询.docx
- 文档编号:3554627
- 上传时间:2022-11-23
- 格式:DOCX
- 页数:16
- 大小:68.73KB
数据库设计与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)转化后的科室关系模式、病房关系模式、医生关系模式各1分,有一个属性不正确均不给分,病人关系模式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)转化后的工厂关系模式、产品关系模式、生产关系模式各1分,有一个属性不正确均不给分,职工关系模式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
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- 数据库 设计 SQL 查询