数据库综合设计实验.docx
《数据库综合设计实验.docx》由会员分享，可在线阅读，更多相关《数据库综合设计实验.docx（16页珍藏版）》请在冰豆网上搜索。
数据库综合设计实验
综合实验报告
课程名称_数据库原理与应用
实验名称_数据库综合设计实验
学生学院_________
专业班级___
学号__
学生姓名____________
指导教师________________
20年月日
实验报告
1、实验方案：
（1）实验软件环境：
WindowsXP中文版，SQLSERVER2000中文版
（2）项目名称：
订货数据管理系统
（3）完成方式：
由课程实验分多次完成，在最后一次实验提交。
（4）提交内容：
数据库数据文件，日志文件，综合实验报告。
二、实验结果与数据
1．系统功能
1．系统功能
本系统的工作环境是可在订货数据管理系统局域网内操作，供商家查询订货及查看供应记录。
为订货——供应的运用提供一个方便快捷的工具，方便商家寻找货源，提高生产资料购买效率。
（
（1）基础数据维护。
管理员可以进行仓库信息，职工信息的数据添加，删除，修改，管理员可以进行订购单数据的添加，删除，修改。
（2）新订购单管理。
管理员可以订购新的货物形成新的订购单，对新订购单验收并编号。
（3）订购单明细管理。
管理员可以进行订购单明细管理。
非管理员可以查看自已订购单明细信息。
（4）订购单查询。
全体人员可使用订购单查询功能。
查询可按职工号，供应商号，订购单号等
图1系统功能模快层次结构图
2．数据库设计：
根据上面对系统做的功能分析、系统设计,规划出本系统中使用的数据库实体,得出数据库概念模型
系统使用中涉及的实体包括：
职工，仓库，供应商，订购单，订购单明细
隐含的实体：
经理，工资，城市，面积
（1）职工（职工号，仓库，经理，工资）
（2）仓库（仓库号，城市，面积）
（3）供应商（供应商号，供应商名，地址）
（4）订购单（职工号，供应商，订购单号，订购日期，金额）
（5）订购单明细（订购单号，序号，产品名称，单价，数量）
实体E-R图如下：
图2系统E-R图
从概念模型，得到数据库的逻辑模型如下：
表名
字段名
字段类型
关键字
约束
仓库
仓库号
6个字符
是
仓库号>=’AA0000’AND仓库号<=’ZZ9999’
城市
10个字符
UNIQUE，NOTNULL
面积
数值
面积>=50
职工
仓库
是
NOTNULLFOREIGNKEYREFERENCES仓库（仓库号）
职工号
8个字符
是
PRIMARYKEY
经理
工资
货币
工资BETWEEN1000AND10000
供应商
供应商号
4个字符
是
PRIMARYKEY
substring（[供应商号],1,1）='S'andright
（[供应商号],3）>'000'andright（[供应商号],3）<'999'）
供应商名
16个字符
地址
30个字符
订购单
职工号
8个字符
是
NOTNULL
供应商号
16个字符
订购单号
6个字符
是
PRIMARYKEY
订购单号BETWEEN'OR0000'AND'OR9999'
订购日期
日期
DEFAULT（GETUTCDATE（））
金额
货币
DEFAULT（null）
订购单明细
订购单号
6个字符
是
[订购单号]>='OR0000'and
[订购单号]<='OR9999'）
序号
2位数字
是
产品名称
20个字符
单价
货币
[单价]>0
数量
整数
[数量]>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）
AS
UPDATE订购单SET订购日期=@dt
WHERE职工号LIKE@sid+'%'
EXECUTEUSPDATETIMECURSOR'2010-3-9','aa0001'
系统相关视图：
（1）基于单个表按投影操作定义视图
CREATEVIEW仓库面积（城市,面积）
AS
SELECT城市,面积FROM仓库
（2）基于单个表按选择操作定义视图
CREATEVIEW高薪职工（职工号,经理,工资）
AS
SELECT职工号,经理,工资FROM职工WHERE工资>2000
（3）基于单个表按投影和选择操作定义视图
CREATEVIEW高薪职工（职工号,工资）
AS
SELECT职工号,工资FROM职工WHERE工资>1500
（4）基于多个表根据连接操作定义视图
CREATEVIEW订单汇总（订购单号,订购日期,职工号,供应商号,序号,产品名称,单价,数量,金额）
AS
SELECT订购单.订购单号,订购日期,职工号,供应商号,序号,产品名称,单价,数量,金额FROM订购单
JOIN订购单明细ON订购单.订购单号=订购单明细.订购单号
（5）基于多个表根据嵌套查询定义视图
CREATEVIEW低薪仓库的职工asSELECT城市FROMsmwh.仓库WHERE仓库号=ANY（SELECT仓库FROMsmwh.职工WHERE工资<3000）
（6）定义含有虚字段的视图
CREATEVIEW订单汇总计算（订购单号,订购日期,职工号,供应商号,序号,产品名称,单价,数量,金额）
AS
SELECT订购单.订购单号,订购日期,职工号,供应商号,序号,产品名称,单价,数量,单价*数量AS单项金额FROM订购单
JOIN订购单明细ON订购单.订购单号=订购单明细.订购单号
2.视图上的查询设计
SELECT城市,面积FROM仓库
3.视图上的插入，更新，和删除操作。
（设计错误语句，回答出错原因，给出改正方法）
（1）插入
错误语句：
INSERTINTO职工号（职工号,经理,工资）VALUES（'',JOHN,3000）
出错原因：
职工号为主键,不能为空
改正方法：
INSERTINTO职工号（职工号,经理,工资）VALUES（'AA000302',JOHN,3000）
（2）更新
错误语句：
UPDATE职工信息SET面积=20WHERE仓库号='AA0001'
出错原因：
违反用户定义完整性,面积>50有约束
改正方法：
UPDATE职工信息SET面积=100WHERE仓库号='AA0001'
（3）删除
错误语句：
DELETE职工WHERE经理='AA000108'
出错原因：
违反了参照完整性,经理'AA000108'不存在
改正方法：
DELETE职工WHERE经理='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'--------订购单详情--------'
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
FETCHFROMORDERDETAILS_CURSORINTO@eid,@sid,@oid,@odate,@osum,@odid,@pname,@pprice,@pcount
END
CLOSEORDERDETAILS_CURSOR
DEALLOCATEORDERDETAILS_CURSOR
设置触发器：
1）为职工建立一个更新触发器,当修改工资值超过6%时发出警示信息
CREATETRIGGERreminder1
ONDBO.职工
FORUPDATE
AS
DECLARE@newmoney,@oldmoney
SELECT@new=工资FROMINSERTED
SELECT@old=工资FROMDELETED
IF@new/@old>1.06
BEGIN
RAISERROR（'修改工资值超过6%取消修改',16,10）
ROLLBACK
END
2）为订购单表建立一个插入和更新触发器,约束规则是：
当订购单金额小于3000时必须使用本地供应商（发出订购单的仓库和供应商在同一城市），如果不满主要求，则拒绝操作，并给出错误信息
CREATETRIGGERreminder2
ONDBO.订购单
FORINSERT，UPDATE
AS
DECLARE@storeidchar（6）,@sidchar（20）,@saddrchar（20）,@citychar（4）
SELECT@storeid=Substring（职工号,1,6）,@SID=供应商号FROMINSERED
WHERE金额<3000
IFNOT@storeidISNULL
BEGIN
SELECT@CITY=城市FROM仓库WHERE仓库号=@storeid
SELECT@saddr=地址FROM供应商WHERE供应商号=@sid
IF@saddrnotlike'%'+@city+'%'
BEGIN
RAISERROR（'3000元以下订单请用本地供应商,取消订单修改',16,10）
ROLLBACK
END
END
（3）当“工资”发生更改时，向职工显示消息
CREATETRIGGERmanage
ONSMWH.职工
AFTERinsert,UPDATE,DELETE
AS
RAISERROR（'注意人事部门',16,1）
UPDATESMWH.职工set工资=5000where职工号='AA000101'
3．系统详细设计
（1）数据维护
添加仓库信息：
仓库5条记录
职工10
供应商5
订购表20
订购表明细50
INSERTINTO仓库（仓库号,城市,面积）
VALUES（'AA0001','广州',2500）
INSERTINTO职工（职工号,仓库,经理,工资）
VALUES（'AA000101','AA0001','AA000101',5000）
INSERTINTO供应商（供应商号,供应商名,地址）
VALUES（'S001','广州市莲香楼有限公司','广州市城西'）
INSERTINTO订购单（职工号,供应商号,订购单号,订购日期,金额）
VALUES（'AA000101','S001','OR0001','',2000）
INSERTINTO订购单明细（订购单号,序号,产品名称,单价,数量）
VALUES（'OR0001','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工资>1100
（5）当x=AA0001，y=AA0002,z=1300时
select职工号from职工where仓库='AA0001'and工资<1300
UNION
select职工号from职工where仓库=''and工资<1300
（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工资<1100）
（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）='AA0001'）
（28）SELECT*FROM仓库WHERE仓库号IN（SELECT仓库FROM职工WHERE工资>=ALL（SELECT工资FROM职工WHERESUBSTRING（职工号,1,6）='AA0001'））
（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:
00
数据库,日志文件的备份路径是:
D:
历史数据:
备份到同上的路径中.
2、结论：
一、通过将各部件进行组装，生成的项目实现了预期的功能。
制作项目时经常遇到找不到图片与表单，比较麻烦。
1.制作表单的时候很多时候忽略了一部就造不出原先想象中的表单。
2.经常忘记保存已经修改的信息。
升级会员 