数据库综合设计实验Word格式.docx
《数据库综合设计实验Word格式.docx》由会员分享，可在线阅读，更多相关《数据库综合设计实验Word格式.docx（16页珍藏版）》请在冰豆网上搜索。
职工，仓库，供应商，订购单，订购单明细
隐含的实体：
经理，工资，城市，面积
（1）职工（职工号，仓库，经理，工资）
（2）仓库（仓库号，城市，面积）
（3）供应商（供应商号，供应商名，地址）
（4）订购单（职工号，供应商，订购单号，订购日期，金额）
（5）订购单明细（订购单号，序号，产品名称，单价，数量）
实体E-R图如下：
图2系统E-R图
从概念模型，得到数据库的逻辑模型如下：
表名
字段名
字段类型
关键字
约束
仓库
仓库号
6个字符
是
仓库号>
=’AA0000’AND仓库号<
=’ZZ9999’
城市
10个字符
UNIQUE，NOTNULL
面积
数值
面积>
=50
职工
NOTNULLFOREIGNKEYREFERENCES仓库（仓库号）
职工号
8个字符
PRIMARYKEY
经理
工资
货币
工资BETWEEN1000AND10000
供应商
供应商号
4个字符
substring（[供应商号],1,1）='
S'
andright
（[供应商号],3）>
'
000'
andright（[供应商号],3）<
999'
）
供应商名
16个字符
地址
30个字符
订购单
NOTNULL
订购单号
订购单号BETWEEN'
OR0000'
AND'
OR9999'
订购日期
日期
DEFAULT（GETUTCDATE（））
金额
DEFAULT（null）
订购单明细
[订购单号]>
='
and
[订购单号]<
序号
2位数字
产品名称
20个字符
单价
[单价]>
0
数量
整数
[数量]>
数据库的关系图
系统相关存储过程：
（1）查询工资范围在1000到3000元范围内的职工信息
CREATEPROCEDUREuspsalaryxy
@xsmallmoney,@ysmallmoney
AS
SELECT仓库号,所在城市,职工号,工资FROMsmwh.仓库JIONsmwh.职工ON仓库.仓库号=职工.仓库
WHERE工资BETWEEN@xand@y
EXECUTEuspsalaryxy@x=1000,@y=2000
（2）查询在某年某月某日之后签订的订购单的有关供应商信息
CREATEPROCEDUREuspAtdateOrder
@dtsmalldatetime
AS
select供应商号,供应商名,地址fromsmwh.供应商joinsmwh.订购单.订购单号on供应商.供应商号=订购单.供应商号
where订购日期>
@dt
orderby订购单号
execuspAtdateOrder'
2000.01.03'
（3）更新操作,将某仓库的订购单的订购日期统一改为一个指定日期（参数为仓库号和新的订购日期）
CREATEPROCEDUREuspUdOrder
@dtsmalldatetime,@sidchar（6）
UPDATE订购单SET订购日期=@dt
WHERE职工号LIKE@sid+'
%'
EXECUTEUSPDATETIMECURSOR'
2010-3-9'
'
aa0001'
系统相关视图：
（1）基于单个表按投影操作定义视图
CREATEVIEW仓库面积（城市,面积）
AS
SELECT城市,面积FROM仓库
（2）基于单个表按选择操作定义视图
CREATEVIEW高薪职工（职工号,经理,工资）
SELECT职工号,经理,工资FROM职工WHERE工资>
2000
（3）基于单个表按投影和选择操作定义视图
CREATEVIEW高薪职工（职工号,工资）
SELECT职工号,工资FROM职工WHERE工资>
1500
（4）基于多个表根据连接操作定义视图
CREATEVIEW订单汇总（订购单号,订购日期,职工号,供应商号,序号,产品名称,单价,数量,金额）
SELECT订购单.订购单号,订购日期,职工号,供应商号,序号,产品名称,单价,数量,金额FROM订购单
JOIN订购单明细ON订购单.订购单号=订购单明细.订购单号
（5）基于多个表根据嵌套查询定义视图
CREATEVIEW低薪仓库的职工asSELECT城市FROMsmwh.仓库WHERE仓库号=ANY（SELECT仓库FROMsmwh.职工WHERE工资<
3000）
（6）定义含有虚字段的视图
CREATEVIEW订单汇总计算（订购单号,订购日期,职工号,供应商号,序号,产品名称,单价,数量,金额）
SELECT订购单.订购单号,订购日期,职工号,供应商号,序号,产品名称,单价,数量,单价*数量AS单项金额FROM订购单
2.视图上的查询设计
3.视图上的插入，更新，和删除操作。
（设计错误语句，回答出错原因，给出改正方法）
（1）插入
错误语句：
INSERTINTO职工号（职工号,经理,工资）VALUES（'
'
JOHN,3000）
出错原因：
职工号为主键,不能为空
改正方法：
AA000302'
（2）更新
UPDATE职工信息SET面积=20WHERE仓库号='
AA0001'
违反用户定义完整性,面积>
50有约束
UPDATE职工信息SET面积=100WHERE仓库号='
（3）删除
DELETE职工WHERE经理='
AA000108'
违反了参照完整性,经理'
不存在
AA00001'
游标的使用：
DECLARE@eidchar（8）,@sidchar（4）,@oidchar（6）,@odatechar（10）,@osummoney,@odidchar
（2）,@pnamechar（20）,@ppricemoney,@pcountsmallint
DECLAREORDERDETAILS_CURSORFOR
SELECT职工号,供应商号,订购单号,订购日期,金额,序号,产品名称,单价,数量
FROM订购单JOIN订购单明细ON订购单.订购单号=订购单明细.订购单号
ORDERBY单价ASC
OPENORDERDETAILS_CURSOR
PRINT'
--------订购单详情--------'
职工号,供应商号,订购单号,订购日期,金额,序号,产品名称,单价,数量'
FETCHFROMORDERDETAILS_CURSORINTO@eid,@sid,@oid,@odate,@osum,@odid,@pname,@pprice,@pcount
WHILE@@FETCH_STATUS=0
BEGIN
PRINT@eid+'
+@sid+'
+@oid+'
+@odate+'
+@osum+'
+@odid+'
+@pname+'
+@pprice+'
+@pcount
END
CLOSEORDERDETAILS_CURSOR
DEALLOCATEORDERDETAILS_CURSOR
设置触发器：
1）为职工建立一个更新触发器,当修改工资值超过6%时发出警示信息
CREATETRIGGERreminder1
ONDBO.职工
FORUPDATE
DECLARE@newmoney,@oldmoney
SELECT@new=工资FROMINSERTED
SELECT@old=工资FROMDELETED
IF@new/@old>
1.06
RAISERROR（'
修改工资值超过6%取消修改'
16,10）
ROLLBACK
2）为订购单表建立一个插入和更新触发器,约束规则是：
当订购单金额小于3000时必须使用本地供应商（发出订购单的仓库和供应商在同一城市），如果不满主要求，则拒绝操作，并给出错误信息
CREATETRIGGERreminder2
ONDBO.订购单
FORINSERT，UPDATE
DECLARE@storeidchar（6）,@sidchar（20）,@saddrchar（20）,@citychar（4）
SELECT@storeid=Substring（职工号,1,6）,@SID=供应商号FROMINSERED
WHERE金额<
3000
IFNOT@storeidISNULL
SELECT@CITY=城市FROM仓库WHERE仓库号=@storeid
SELECT@saddr=地址FROM供应商WHERE供应商号=@sid
IF@saddrnotlike'
+@city+'
3000元以下订单请用本地供应商,取消订单修改'
（3）当“工资”发生更改时，向职工显示消息
CREATETRIGGERmanage
ONSMWH.职工
AFTERinsert,UPDATE,DELETE
注意人事部门'
16,1）
UPDATESMWH.职工set工资=5000where职工号='
AA000101'
3．系统详细设计
（1）数据维护
添加仓库信息：
仓库5条记录
职工10
供应商5
订购表20
订购表明细50
INSERTINTO仓库（仓库号,城市,面积）
VALUES（'
'
广州'
2500）
INSERTINTO职工（职工号,仓库,经理,工资）
5000）
INSERTINTO供应商（供应商号,供应商名,地址）
S001'
广州市莲香楼有限公司'
广州市城西'
INSERTINTO订购单（职工号,供应商号,订购单号,订购日期,金额）
OR0001'
2000）
INSERTINTO订购单明细（订购单号,序号,产品名称,单价,数量）
01'
双黄白莲月饼4个装'
100,50）
修改职工信息：
（将x城市仓库的职工工资提高10%）
UPDATE职工SET工资=工资*（1.1）
WHERE仓库IN（SELECT仓库号FROM仓库WHERE城市='
删除仓库信息：
DELETEFROM仓库WHERE仓库号='
AB0002'
（2）查询
用SELECT语句完成以下查询
（1）考虑不去掉重复值：
Select工资from职工
去掉重复值：
SelectDISTINCT工资from职工
（2）Select*from仓库
（3）当x=1100时SELECT职工号from职工where工资>
1100
（4）当y=1100时select仓库from职工where工资>
（5）当x=AA0001，y=AA0002,z=1300时
select职工号from职工where仓库='
and工资<
1300
UNION
select职工号from职工where仓库='
（6）当x=1600,y=1300
select*from职工where工资between1300and1600
（7）select*from供应商where供应商名like'
%公司'
（8）当x=广州时
select*from仓库where城市!
（9）select*from订购单where供应商号isnull
（10）select*from订购单where供应商号isnotnull
（11）select*from职工orderby工资
（12）select*from职工orderby仓库asc,工资desc
（13）当x=1000
select职工号，城市from仓库join职工On职工.仓库=仓库.仓库号where工资>
1000
（14）当x=200
select职工号，城市from仓库join职工On职工.仓库=仓库.仓库号where面积>
200
15）SELECT经理,职工号FROM职工WHERE职工号LikeSUBSTRING（职工号,1,6）+'
ORDERBY职工号
（16）SELECT*FROM仓库CROSSJOIN职工
（17）SELECT仓库号,城市,面积,职工号,经理,工资FROM仓库CROSSJOIN职工WHERE职工.职工号LIKE仓库.仓库号+'
ORDERbY仓库.仓库号
（18）select供应商名，供应商号，订购单号，订购单日期
From订购单join供应商on订购单.供应商号=供应商.供应商号
（19）select供应商名，供应商号，订购单号，订购单日期
From订购单leftjoin供应商on订购单.供应商号=供应商.供应商号
（20）select供应商名，供应商号，订购单号，订购单日期
From订购单rightjoin供应商on订购单.供应商号=供应商.供应商号
（21）select供应商名，供应商号，订购单号，订购单日期
From订购单fulljoin供应商on订购单.供应商号=供应商.供应商号
（22）当x=1100时
SELECT城市FROM仓库WHERE
仓库号=ANY（SELECT仓库FROM职工WHERE工资>
1100）
（23）SELECT*FROM仓库WHERENOT仓库号=ANY（SELECTDISTINCT仓库FROM职工WHERE工资<
（24）当x=AA000114时
SELECT*FROM职工WHERE工资=（SELECT工资FROM职工WHERE职工号='
AA000114'
（25）查询哪些城市的仓库向地址为x的供应商发出了订购单。
SELECT*FROM仓库WHERE仓库号=ANY（SELECTSUBSTRING（职工号,1,6）AS仓库号
FROM订购单WHERE供应商号=ANY（SELECT供应商号FROM供应商WHERE地址='
））
（26）SELECT供应商名FROM供应商wHERE地址='
广州天河'
AND供应商号IN（SELECT供应商号FROM订购单
WHERESUBSTRING（职工号,1,6）=（SELECT仓库号FROM仓库WHERE城市='
SELECT供应商名,城市
FROM供应商JOIN订购单ON供应商.供应商号=订购单.供应商号JOIN职工
ON订购单.职工号=职工.职工号JOIN仓库ON职工.仓库=仓库.仓库号
WHERE供应商.地址='
广州天河'
AND仓库.城市='
（27）SELECT*FROM职工
WHERE工资>
=ALL（SELECT工资FROM职工WHERESUBSTRING（职工号,1,6）='
（28）SELECT*FROM仓库WHERE仓库号IN（SELECT仓库FROM职工WHERE工资>
（29）SELECT职工号,MAX（金额）AS订单最高金额FROM订购单GROUPBY职工号
（30）SELECT*FROM仓库WHERENOT仓库号=ANY（SELECTDISTINCT仓库FROM职工WHERE职工号ISNOTNULL）
（31）SELECT*FROM仓库WHERE仓库号=ANY（SELECTDISTINCT仓库FROM职工WHERE职工号ISNOTNULL）
（32）SELECTCOUNT（城市）AS仓库数目FROM仓库
（33）SELECTSUM（工资）AS支付工资FROM职工
（34）SELECTSUM（工资）AS支付工资FROM职工WHERE城市IN（'
韶关'
（35）SELECTAVG（面积）AS平均面积FROM仓库WHERENOT仓库号=ANY（SELECT仓库FROM职工WHERE工资<
=1500）
（36）SELECTMAX（金额）FROM订购单WHERE职工号=ANY（SELECT职工号FROM职工WHERE工资>
1500）
（37）SELECT仓库,AVG（工资）AS平均工资FROM职工GROUPBY仓库
（38）SELECT仓库号,MAX（金额）AS最高金额,MIN（金额）AS最低金额,AVG（金额）AS平均金额
FROM订购单JOIN职工ON订购单.职工号=订购单.职工号JOIN仓库ON仓库.仓库号=职工.仓库GROUPBY仓库号
（39）SELECT订购单号,AVG（数量*单价）AS平均金额,SUM（数量*单价）AS金额总计
FROM订购单明细
GROUPBY订购单号
havingCOUNT（序号）>
=3
（40）SELECT*FROM职工ORDERBY仓库
COMPUTEAVG（工资）,SUM（工资）BY仓库
COMPUTEAVG（工资）,SUM（工资）
（41）SELECT*,单价*数量AS金额FROM订购单明细ORDERBY订购单号
COMPUTEAVG（单价*数量）,SUM（单价*数量）BY订购单号
COMPUTEAVG（单价*数量）,SUM（单价*数量）
（42）SELECT*FROM订购单COMPUTEAVG（金额）,SUM（金额）
（9）系统访问用用户：
smwh,
（10）数据库备份计划。
（文字说明）
数据库维护计划:
备份数据库：
db_warehouse,master,
每周备份一次,为不同数据库建立分立文件夹,设置为周五下午17:
00
日志文件:
每天备份,时间是19:
数据库,日志文件的备份路径是:
D:
历史数据:
备份到同上的路径中.
2、结论：
一、通过将各部件进行组装，生成的项目实现了预期的功能。
制作项目时经常遇到找不到图片与表单，比较麻烦。
1.制作表单的时候很多时候忽略了一部就造不出原先想象中的表单。
2.经常忘记保存已经修改的信息。
升级会员 