作业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.2.3.4.5.6.7.8.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 这个产品但没有购买 longlife tofu这个产品。

6. 统计哪些产品同时出现在同一个订单中的比例最高？注意：除数为每个产品的销售次数。

数据库原理与应用模拟卷（作业5）1．某公司需建立产品信息数据库，其语义如下：①一种产品可以使用多个不同种类的部件，也可以使用多个不同种类的零件：一种部件至少被用在一种产品中；一种部件可以由多个不同种类的零件组成；一种零件至少被用在一种产品或一种部件中；②对于一种具体的部件，要记录这种部件使用的各种零件的数量；③对于一种具体的产品，要记录这种产品使用的各种部件数量和各种零件数量，但部件所使用的零件数量不计入该产品的零件数量；2④⑤⑥①①关系代数Π2,4分）数表达式。

（6分）③(Cno∏Snoσ语句，写出标记处各条SELECTCREATE TABLE test2 ( C1 int identity(200,2),C2 varchar(20), C3 int, C4 int )GO表2. Test1原始数据表3. Test2原始数据①第1小题SELECT f1,f3+SPACE(1)+f2 AS 'Animal',f4, Convert(varchar(10),f5,120) as 'f5' FROM test1 WHERE DATEDIFF(month,f5,getdate())>=6 and f3 like '%e[dy]%'ORDER BY f3,f2 DESCGO②)第2小题SELECT f2,f3,CASEWHEN f4=1 THEN 'R'WHEN f4=2 THEN 'S'WHEN f4=3 THEN 'T'ELSE 'X' END as f4,f6,f7 FROM test1 WHERE f6 BETWEEN 16 AND 18GO③第3小题CREATE PROCEDURE proc1 (@v1 varchar(10))ASBEGINdeclare @s varchar(500)set @s='SELECT sum(c4*f6) as amt FROM test2 as a JOIN test1 as b ON a.c3=b.f1'+space(1) set @s=@s+'WHERE a.c3='+ltrim(str(@v1,4))execute(@s)ENDGOEXECUTE proc1 2GO④第4小题Declare @t TABLE ( h1 int, h2 varchar(10), h3 varchar(10), h4 int) INSERT INTO @t SELECT c1,c2,f2,c4*f6 FROM test2 aJOIN test1 b ON b.f1=a.c3 WHERE b.f3='green'SELECT * FROM @t⑤第5小题CREATE FUNCTION fn1 (@v1 int)RETURNS varchar(20) ASBEGINDeclare @s varchar(20),@n int,@s1 varchar(10)SELECT @n=count(*) from test2 where c3=@v1SELECT @s1=f3+space(1)+f2 from test1 where f1=@v1IF @n>0BEGINSet @s=cast(@n as varchar)+space(1)+@s1IF @n>1 set @s=@s+'s'ENDELSE IF @n=0 set @s='no '+@s1RETURN(@s)ENDGOSELECT f1,dbo.fn1(f1) as f0 FROM test1 ORDER BY f1GO⑥第6小题ALTER TABLE test1 ADD f8 intGODECLARE c1 cursor dynamic FOR SELECT f4 FROM test1 ORDER BY f4 open c1declare @x1 int,@x2 int,@n intset @n=1set @x2=0fetch next FROM c1 into @x1WHILE @@fetch_status=0BEGINif @x2<>@x1beginset @x2=@x1set @n=1endelse set @n=@n+1UPDATE test1 set f8=@n WHERE current of c1fetch next FROM c1 into @x1ENDDEALLOCATE c1SELECT f2,f4,f8 FROM test1 ORDER BY f4程序设计题1. 计算客户2009年度的销售额，求出销售额排名在11~20位之间的客户信息及其销售额和排名。