数据库综合设计实验.docx
《数据库综合设计实验.docx》由会员分享,可在线阅读,更多相关《数据库综合设计实验.docx(14页珍藏版)》请在冰豆网上搜索。
数据库综合设计实验
综合实验报告
课程名称_数据库原理与应用
实验名称_数据库综合设计实验
学生学院_________
专业班级___
学号__
学生姓名____________
指导教师________________
20年月日
实验报告
一、实验方案:
(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:
历史数据:
备份到同上的路径中.
二、结论:
一、通过将各部件进行组装,生成的项目实现了预期的功能。
制作项目时经常遇到找不到图片与表单,比较麻烦。
1.制作表单的时候很多时候忽略了一部就造不出原先想象中的表单。
2.经常忘记保存已经修改的信息。