数据库原理、技术及应用习题汇编

..作业6：〔数据库原理局部〕一、设某医院住院管理数据库包括病人、医生、科室、病房以及护士等5个实体集，其中"病人〞包括病历号、、性别、入院时间、病症等属性；"医生〞包括医生工号、、年龄、职称等属性；"科室〞包括科室编码、名称、办公地点、科室主任等属性；"病房〞包括病房号、位置、床位数等属性；"护士〞包括护士工号、、年龄等属性。医院住院管理的根本语义如下：①一个医生对应治疗多个病人，一个病人可有多个医生治疗；②一个科室有多个医生和护士，一个医生和护士只属于一个科室；③一个病人对应一个床位号，一个护士护理多个床位，一个床位有多个护士护理。试根据上述语义完成以下各项数据库设计任务：①设计满足上述要求的E-R图。〔8分〕②将该E-R图转换成关系模式，并指出每一个关系的主码和外码。〔8分〕③编写创立"医生〞关系模式对应的根本表的T-SQL语句。〔4分〕④假设符合1NF的医院数据库的关系模式如下所示，其根本语义为同一病人同一入院时间只有一个主治医生，试根据式理论将该关系模式分解成符合3NF的多个关系模式。〔8分〕医疗〔病历号，病人，病症，床位号，病房编号，病房位置，床位数，医生工号，医生，医生职称，科室编码，科室名称，科室地点，科室主任〕二、试设计某科技园区用电管理系统关系数据库，该系统根本信息需求如下：1〕园区有多幢建筑物〔即办公楼〕，建筑物根本信息包括：建筑物编码、建筑物名称、建筑物楼对应的经度纬度坐标、建筑物所在地址、最低楼层、最高楼层、楼层数〔计算得到〕等。2〕园区可以进驻多个企业单位〔用户〕，企业根本信息包括企业编码、企业名称、联系人、联系、进驻时间等。每个企业进驻园区后，其管理总部对应园区建筑物的某个楼层和房间号。3〕建筑物的各个楼层中安装有多个电表，电表根本信息包括：电表编码、电表名称、规格型号、供给商名称、安装位置〔包括经度纬度坐标、建筑物及楼层〕、电表所属的企业用户等。4〕园区物业管委会可以定期或随时采集各个电表的当前电表指示数据，存放在数据库中，同一电表同一天最多只保存一次数据采集记录。根据采集的数据和时间，系统可定期或实时计算出每个电表的实际耗电量，进而统计出每个企业的用电量。5〕为了更好节约和控制用电，每个企业单位每年都有一个分月度的用电方案。月末时，数据库应用系统可统计出每个企业的用电超标情况。试根据上述语义，完成以下各题：1.设计满足上述要求的数据库E－R图。〔6分〕2.根据E-R图，转换得到各个关系模式，并指出每一个关系模式的主码和外码。〔6分〕3．使用关系代数完成以下检索：〔6分〕①检索"信达〞〔企业名称〕公司2014年5月10日在"科技创新楼〞〔建筑物名称〕中的所有电表的采集数据。②检索哪些建筑物中安装有两个或两个以上不同单位的电表，列出这些建筑物的名称。4〕假设一个关系模式如下，试运用式理论和方法，从第一式开场将其逐步转换成符合3NF的关系模式。〔6分〕电能管理数据库=〔电表编码，电表名称，电表所在楼层，企业编码，企业名称，建筑物编码，建筑物名称，电表采集时间、电表当前指示数〕三、图书出版和销售管理的一个局部E—R图及其各个实体的属性如以下图1所示，其语义如下：一本书可以由多个作者编写，一个作者可以编写多本书；一本书只能在一个出版，但一个可以出版多本书；一个书店可以销售多本书，一本书可以在多个书店销售；书店销售容包括：销售订单号、销售日期和销售数量等信息；同一本书同一销售订单中只出现一次。①试将该E—R图中的各种联系〔包括联系的类型和联系的属性〕标注完整。〔7分〕图1.图书出版与销售管理的局部E—R图②将上述E—R图转换成关系模型，导出各个关系模式，注明各个关系模式的主码和外码；根据式理论，分析上述各个关系模式是否属于3NF。〔10分〕作业2：1.从产品表中检索哪些产品名称的前10个字符中包含en字符串。2.从产品表中检索哪些产品名称中出现两个er字符串。3.从产品表中检索哪些产品名称中只包含一个er字符串。4.从产品表中检索哪些产品名称中包含单引号字符。5.从客户表中检索哪些客户名称所包含的字符个数最多。6.根据产品表和供给商表，检索哪些产品是由美国的供给商提供的。7.从产品表中检索第一大类产品中〔即categoryid值为1〕哪些产品的单价大于其同类产品的平均单价。8.从产品表中检索第一大类产品中〔即categoryid值为1〕哪些产品的单价最低。9.从订单明细表〔Orderitems〕中计算10号产品的销售额和平均销售单价。〔提示：使用sum函数〕10.根据产品表和订单明细表，统计计算Tofu这个产品的销售额〔提示：使用sum函数和IN〕。​作业3：1.统计检索销售额最大的前30%的客户他们购置哪些产品的次数最多。2.统计检索哪些客户购置tofu这个产品的次数最多。3.统计计算销售额最大的前30%的客户中有哪些购置了tofu这个产品。4.分别从客户表和产品表中随机提取一个客户和产品，统计计算这个客户购置这个产品的销售量和销售额。5.单价最贵的前10个产品中2008年每个客户购置这些产品的次数分别是多少。6.统计计算销售额最大的前30%的客户他们的销售额占总销售额的百分比。7.利用自连接，统计检索哪些产品的单价是一样的。8.统计检索哪些产品的销售额与tofu这个产品的销售额相差最大。9.统计检索哪些产品的销售额与tofu这个产品的销售额相差在10%之。10.统计检索哪些产品2008年度每笔销售的销售单价都是大于本钱单价的1.1倍以上。11.统计检索哪些美国的客户从未买过美国供给商供给的产品。12.统计每类产品2008年每个月的销售额。在此根底上，统计检索哪几类产品有两个月以上的销售额在同月中排名前3位。13.统计计算销售额最大的30%客户购置tofu这个产品的数量占总tofu这个产品销量的百分比。14.统计2009年每一笔订单的利润和利润率。利润=销售量*〔销售单价-本钱单价〕，利润率=100*利润/销售额。15.统计检索销售额最大的前30%客户没有购置过哪些产品。​作业4：1.从2008年销售次数最多的前5个产品中随机提取1个产品，求这个产品的销售额在同类产品销售额中的排名名次。2.利用相关子查询，计算每个产品的销售额在同类产品销售额中的排名名次。3.随机提取一个客户，计算这个客户2009年度每个月销售额与上年同期销售额的增长率。4.在2008年1月1日至2009年12月31日之间随机产生一个日期区间，分别查询在这个日期区间哪些客户购置tofu这个产品的次数最多和最少。5.统计2008年哪些客户购置了tofu这个产品但没有购置longlifetofu这个产品。6.统计哪些产品同时出现在同一个订单中的比例最高？注意：除数为每个产品的销售次数。数据库原理与应用模拟卷〔作业5〕1．某公司需建立产品信息数据库，其语义如下：①一种产品可以使用多个不同种类的部件，也可以使用多个不同种类的零件：一种部件至少被用在一种产品中；一种部件可以由多个不同种类的零件组成；一种零件至少被用在一种产品或一种部件中；②对于一种具体的部件，要记录这种部件使用的各种零件的数量；③对于一种具体的产品，要记录这种产品使用的各种部件数量和各种零件数量，但部件所使用的零件数量不计入该产品的零件数量；④一个供给商可供给多种零件，但一种零件只能由一个供给商供给。图1.产品信息数据库E—R图在此E-R图中，实体的属性完整，试完成以下各题：①根据以上需求，在图1中写出各个实体〔型〕之间的联系及其联系的属性。〔7分〕②根据E-R图导出该数据库的各个关系模式，标出每个关系模式的主码和外码，并根据式理论，分析上述各个关系模式是否属于3NF。〔10分〕2．图书出版和销售管理的一个局部E—R图及其各个实体的属性如以下图1所示，其语义如下：一本书可以由多个作者编写，一个作者可以编写多本书；一本书只能在一个出版，但一个可以出版多本书；一个书店可以销售多本书，一本书可以在多个书店销售；书店销售容包括：销售订单号、销售日期和销售数量等信息；同一本书同一销售订单中只出现一次。①试将该E—R图中的各种联系〔包括联系的类型和联系的属性〕标注完整。〔7分〕图1.图书出版与销售管理的局部E—R图②将上述E—R图转换成关系模型，导出各个关系模式，注明各个关系模式的主码和外码；根据式理论，分析上述各个关系模式是否属于3NF。〔10分〕3．某数据库包含学生〔Students〕、课程〔Courses〕、选课〔StudCourses〕等3个关系模式，其根本数据如表1所示，试解答以下各题。表1.关系数据库实例〔a〕学生关系：Students〔b〕课程关系：Courses〔c〕选课关系：StudCourses学号SnoSname性别Gender所属专业Major课程号o课程名ame类别Type学号Sno课程号o成绩GradeS1雯女信息管理C1数据库1S1C180S2高波男计算机科学C2C程序设计2S1C276S3远男电子商务C3数据构造1S1C393S4训男信息管理C4Web技术2S2C280S5胡娟女计算机科学C5软件工程3S2C346S2C478S3C262S4C152S4C379S4C456S5C486①关系代数Π2,4(Students(Π1,2(StuCourses)÷Π1(σ3=1(Courses))))的计算结果为：〔6分〕②检索"信息管理〞专业中哪些学生〔要求列出〕每门课程的考试成绩都及格，写出其关系代数表达式。〔6分〕③某数据检索的关系代数表达式如下：((StudCourses))(StudCourses))Students)写出与该语句对应的T-SQL语句。〔6分〕二、程序阅读题〔阅读以下T-SQL语句，写出标记处各条SELECT语句运行的结果，此题共30分，每题6分，任选5小题〕数据表Test1、Test2的构造定义如下，其原始数据分别如表2和表3所示。其中Test1表中的F7和F8、Test2表2中的C1列需要自己计算得到。写出以下程序各个小题中SELECT语句的检索结果。CREATETABLEtest1(F1intidentityprimarykey,F2varchar(10),F3varchar(10),F4tinyint,F5datetimedefault(getdate()),F6intdefault(0),F7ASLEFT(f2,1)+RIGHT(f3,1)+CAST(f1asvarchar))GOCREATETABLEtest2(C1intidentity(200,2),C2varchar(20),C3int,C4int)GO表2.Test1原始数据F1F2F3F4F5F6F7F81froggreen22011-03-1500:00:00.000152catgrey32011-06-1600:00:00.000163birdgreen22011-09-1300:00:00.000174mousegreen12011-10-3000:00:00.000195foxred32011-11-1000:00:00.000206sheepred12011-12-3000:00:00.000187tigerred22012-01-1800:00:00.000168pigblack22012-02-1100:00:00.000159wolfgrey32012-03-1200:00:00.00017表3.Test2原始数据C1C2C3C4C0128C0237C0316C0425C0534C0643C0732C0823C0914C1045①第1小题SELECTf1,f3+SPACE(1)+f2AS'Animal',f4,Convert(varchar(10),f5,120)as'f5'FROMtest1WHEREDATEDIFF(month,f5,getdate())>=6andf3like'%e[dy]%'ORDERBYf3,f2DESCGO②)第2小题SELECTf2,f3,CASEWHENf4=1THEN'R'WHENf4=2THEN'S'WHENf4=3THEN'T'ELSE'X'ENDasf4,f6,f7FROMtest1WHEREf6BETWEEN16AND18GO③第3小题CREATEPROCEDUREproc1(v1varchar(10))ASBEGINdeclaresvarchar(500)sets='SELECTsum(c4*f6)asamtFROMtest2asaJOINtest1asbONa.c3=b.f1'+space(1)sets=s+'WHEREa.c3='+ltrim(str(v1,4))execute(s)ENDGOEXECUTEproc12GO④第4小题DeclaretTABLE(h1int,h2varchar(10),h3varchar(10),h4int)INSERTINTOtSELECTc1,c2,f2,c4*f6FROMtest2aJOINtest1bONb.f1=a.c3WHEREb.f3='green'SELECT*FROMt⑤第5小题CREATEFUNCTIONfn1(v1int)RETURNSvarchar(20)ASBEGINDeclaresvarchar(20),nint,s1varchar(10)SELECTn=count(*)fromtest2wherec3=v1SELECTs1=f3+space(1)+f2fromtest1wheref1=v1IFn>0BEGINSets=cast(nasvarchar)+space(1)+s1IFn>1sets=s+'s'ENDELSEIFn=0sets='no