得分

四、应用题(本大题共1小题，共18分)

1、设某生产零件的工厂有若干车间，每个车间加工若干种零件，每种零件在某个车间进行加工；车间的属性有车间名（唯一）、电话；零件的属性有零件号、零件名；零件由不同的原材料制成，不同的零件所用的原材料可以相同，也可能不同；制成的属性有制作时间；材料的属性有材料号和供应商。请画出相应的E-R图，并写出对应的关系模式。

（1）E-R图（标注联系类型）（10分）

（2）转换后的关系模式（要求达到第三范式，主键和外键可用符号标记或在关系模式后文字表达）（8分）

关系模式

1. 查询所有在2015年及其以后建成的房源信息，包括房源编号、小区名称、面积、总价、房间数。

Select 房源编号,小区名称,面积,总价,房间数 from houses Where year(建成日期)>=2015

1. 在salesman表中插入一条记录，姓名：王剑，男，编号为20001，手机号码为15120136490。

Insert into salesman values (‘20001‘,‘王剑‘,‘男‘,‘15120136490‘)

1. 编号为“10002”销售员因为某些原因更换了手机号码，请在salesman表中将其手机号码更改为“18902016688”。

Update salesman set 电话=‘18902016688‘ where 销售员编号=‘10002‘

1. 删除无销售业绩的销售员信息。

Delete from salesman where 销售员编号 not in (Select distinct 销售员编号 from sales)

1. 查询女销售员售出的房源信息，包括姓名、房源编号、小区、面积和总价。

Select 姓名,房源编号,小区,面积,总价 from houses h,salesman sm,sales ss

Where h.房源编号=ss.房源编号 and sm.销售员编号=ss.销售员编号 and 性别=‘女‘

1. 统计各小区房源数量，显示小区名称和房源数量。

Select 小区名称,count(*) AS 房源数量 from houses group by 小区名称

2、根据相应要求，编写相应T-SQL语句，完成相关功能。（共2小题，请 任选一题 作答，共5分）

请根据上一题“HouseSelling”材料完成完成下面两小题，任选一题完成即可。

1. 建立统计销售员历史销售业绩的存储过程p_sales_smno（销售员编号为输入参数），显示销售员姓名、房源编号、销售日期、总价信息，并调用执行（查询销售员号为10002的历史销售业绩）。

存储过程：

Create proc p_sales_snmo @smno char(7) AS

Select 姓名,房源编号,销售日期,总价 from houses h,salesman sm, sales ss

Where h.房源编号=ss.房源编号 and sm.销售员编号=ss.销售员编号

And 销售员编号=@smno

调用执行：

execute p_sales_smno ‘10002‘

1. 建立触发器t_house_update，在更新房源信息时，总价需在100万至300万之间，否则，提示“价格超出范围”，并撤销相关操作。

Create trigger t_house_update on houses After Update AS

If not exists(select * from inserted where 总价 between 1000000 and 3000000)

Begin

Rooback;

Print ‘总价超出范围‘;

End