得分
四、应用题(本大题共1小题，共18分)1、设某生产零件的工厂有若干车间，每个车间加工若干种零件，每种零件在某个车间进行加工；车间的属性有车间名（唯一）、电话；零件的属性有零件号、零件名；零件由不同的原材料制成，不同的零件所用的原材料可以相同，也可能不同；制成的属性有制作时间；材料的属性有材料号和供应商。请画出相应的E-R图，并写出对应的关系模式。
（1）E-R图（标注联系类型）（10分）
（2）转换后的关系模式（要求达到第三范式，主键和外键可用符号标记或在关系模式后文字表达）（8分）
关系模式
1、现有一名为“HouseSelling”的数据库，在此数据库下有三张表，请根据要求，写出相应的SQL语句。（共6个小题，其中1-3每题4分，4-6每题5分，共27分）
（1）房源表（houses），“房源编号”为主键。
（2）销售员表（salesman），“销售员编号”为主键。
（3）销售表（sales），“销售员编号，房源编号”为主键，销售员编号和房源编号均为外键。
说明：下列数据表中“（）”部分为字段类型，SQL语句编写时请正确书写字段名称，若字段名称写错将被扣分。
houses表
房源编号
(char(12))
小区名称(char(20)
面积
(float)
总价
(decimal(10,2))
楼层
(int)
总楼层(int)
建成日期(date)
房间数
(int)
100-5-202
天华苑
55.90
1000000.00
2
15
2010.7.1
1
111-10-401
中诚阁
85.73
1700000.00
4
5
2013.12.2
2
100-2-801
诗远城
110.21
2298900.00
8
12
2015.9.1
3
233-1-302
博爱缘
79.27
1595000.00
3
6
2018.7.1
2
100-5-302
天华苑
55.90
1050000.00
2
15
2010.7.1
1
111-10-501
中诚阁
85.73
1760000.00
4
5
2013.12.2
2
100-2-1001
诗远城
110.21
2198900.00
8
12
2015.9.1
3
233-1-602
博爱缘
79.27
1545000.00
3
6
2018.7.1
2
sales表
销售员编号
(char(7))
房源编号
(char(12))
销售日期
(date)
10002
111-10-401
2014-5-3
12010
233-1-302
2018-10-2
12003
233-1-602
2018-9-10
10002
100-5-302
2011-1-2
salesman表
销售员编号
(char(7))
姓名
(char(10)
性别
(char(2)
电话
(char(11))
10002
孙华
男
13502100210
12003
张桂花
女
13502100211
12010
刘远航
男
13502100212
15079
王兴信
男
13502100213
- 查询所有在2015年及其以后建成的房源信息，包括房源编号、小区名称、面积、总价、房间数。
Select 房源编号,小区名称,面积,总价,房间数 from houses Where year(建成日期)>=2015
- 在salesman表中插入一条记录，姓名：王剑，男，编号为20001，手机号码为15120136490。
Insert into salesman values (‘20001‘,‘王剑‘,‘男‘,‘15120136490‘)
- 编号为“10002”销售员因为某些原因更换了手机号码，请在salesman表中将其手机号码更改为“18902016688”。
Update salesman set 电话=‘18902016688‘ where 销售员编号=‘10002‘
- 删除无销售业绩的销售员信息。
Delete from salesman where 销售员编号 not in (Select distinct 销售员编号 from sales)
- 查询女销售员售出的房源信息，包括姓名、房源编号、小区、面积和总价。
Select 姓名,房源编号,小区,面积,总价 from houses h,salesman sm,sales ss
Where h.房源编号=ss.房源编号 and sm.销售员编号=ss.销售员编号 and 性别=‘女‘
- 统计各小区房源数量，显示小区名称和房源数量。
Select 小区名称,count(*) AS 房源数量 from houses group by 小区名称
2、根据相应要求，编写相应T-SQL语句，完成相关功能。（共2小题，请 任选一题 作答，共5分）
请根据上一题“HouseSelling”材料完成完成下面两小题，任选一题完成即可。
- 建立统计销售员历史销售业绩的存储过程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‘
- 建立触发器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
