数据库综合设计实验(共14页).doc
精选优质文档-倾情为你奉上综合实验报告 课程名称_ 数据库原理与应用 实验名称_ 数据库综合设计实验学生学院_ _专业班级_ _ 学 号_ _ 学生姓名_ _ _ 指导教师_ _20 年 月 日专心-专注-专业实验报告一、 实验方案：(1) 实验软件环境：Windows XP中文版，SQL SERVER2000中文版(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 ， NOT NULL 面积数值面积>=50职工仓库是NOT NULL FOREIGN KEY REFERENCES 仓库(仓库号)职工号8个字符是PRIMARY KEY经理工资货币工资 BETWEEN 1000 AND 10000供应商供应商号4个字符是PRIMARY KEYsubstring(供应商号,1,1) = 'S' and right(供应商号,3) > '000' and right(供应商号,3) < '999')供应商名16个字符地址30个字符订购单职工号8个字符是NOT NULL供应商号16个字符订购单号6个字符是PRIMARY KEY订购单号 BETWEEN 'OR0000' AND 'OR9999'订购日期日期DEFAULT (GETUTCDATE()金额货币DEFAULT (null)订购单明细订购单号6个字符是订购单号 >= 'OR0000' and 订购单号 <= 'OR9999')序号2位数字是产品名称20个字符单价货币单价 > 0数量整数数量 > 0数据库的关系图系统相关存储过程：(1)查询工资范围在1000到3000元范围内的职工信息CREATE PROCEDURE uspsalaryxyx smallmoney,y smallmoneyAS SELECT 仓库号,所在城市,职工号,工资 FROM smwh.仓库 JION smwh.职工 ON 仓库.仓库号=职工.仓库WHERE 工资 BETWEEN x and yEXECUTE uspsalaryxy x=1000 , y=2000（2）查询在某年某月某日之后签订的订购单的有关供应商信息CREATE PROCEDURE uspAtdateOrderdt smalldatetimeASselect 供应商号,供应商名,地址 from smwh.供应商 join smwh.订购单.订购单号 on 供应商.供应商号=订购单.供应商号where 订购日期>dtorder by 订购单号exec uspAtdateOrder '2000.01.03'(3)更新操作,将某仓库的订购单的订购日期统一改为一个指定日期（参数为仓库号和新的订购日期）CREATE PROCEDURE uspUdOrderdt smalldatetime,sid char(6) ASUPDATE 订购单 SET 订购日期=dtWHERE 职工号 LIKE sid+'%'EXECUTE USPDATETIMECURSOR '2010-3-9','aa0001'系统相关视图： (1) 基于单个表按投影操作定义视图 CREATE VIEW 仓库面积(城市, 面积) AS SELECT 城市, 面积 FROM 仓库(2) 基于单个表按选择操作定义视图 CREATE VIEW 高薪职工(职工号, 经理,工资) AS SELECT 职工号, 经理,工资 FROM 职工 WHERE 工资>2000 (3) 基于单个表按投影和选择操作定义视图 CREATE VIEW 高薪职工(职工号, 工资) AS SELECT 职工号, 工资 FROM 职工 WHERE 工资>1500 (4) 基于多个表根据连接操作定义视图 CREATE VIEW 订单汇总(订购单号,订购日期,职工号,供应商号,序号,产品名称,单价,数量,金额) AS SELECT 订购单.订购单号,订购日期,职工号,供应商号,序号,产品名称,单价,数量,金额 FROM 订购单 JOIN 订购单明细 ON 订购单.订购单号=订购单明细.订购单号(5) 基于多个表根据嵌套查询定义视图 CREATE VIEW 低薪仓库的职工 as SELECT 城市 FROM smwh.仓库 WHERE 仓库号=ANY(SELECT 仓库 FROM smwh.职工 WHERE 工资<3000)(6) 定义含有虚字段的视图 CREATE VIEW 订单汇总计算(订购单号,订购日期,职工号,供应商号,序号,产品名称,单价,数量,金额) AS SELECT 订购单.订购单号,订购日期,职工号,供应商号,序号,产品名称,单价,数量, 单价*数量 AS 单项金额 FROM 订购单 JOIN 订购单明细 ON 订购单.订购单号=订购单明细.订购单号2. 视图上的查询设计 SELECT 城市, 面积 FROM 仓库3. 视图上的插入，更新，和删除操作。(设计错误语句，回答出错原因，给出改正方法)(1) 插入 错误语句：INSERT INTO 职工号(职工号,经理,工资)VALUES('',JOHN,3000) 出错原因：职工号为主键,不能为空 改正方法：INSERT INTO 职工号(职工号,经理,工资)VALUES('AA',JOHN,3000)(2) 更新 错误语句：UPDATE 职工信息 SET 面积=20 WHERE 仓库号='AA0001' 出错原因：违反用户定义完整性,面积>50有约束 改正方法：UPDATE 职工信息 SET 面积=100 WHERE 仓库号='AA0001'(3) 删除 错误语句：DELETE 职工 WHERE 经理='AA' 出错原因：违反了参照完整性,经理'AA'不存在 改正方法：DELETE 职工 WHERE 经理='AA00001'游标的使用：DECLARE eid char(8),sid char(4),oid char(6),odate char(10),osum money,odid char(2),pname char(20),pprice money,pcount smallintDECLARE ORDERDETAILS_CURSOR FOR SELECT 职工号,供应商号,订购单号,订购日期,金额,序号,产品名称,单价,数量FROM 订购单 JOIN 订购单明细 ON 订购单.订购单号=订购单明细.订购单号ORDER BY 单价 ASCOPEN ORDERDETAILS_CURSOR PRINT '-订购单详情-'PRINT '职工号,供应商号,订购单号,订购日期,金额,序号,产品名称,单价,数量'FETCH FROM ORDERDETAILS_CURSOR INTO eid,sid,oid,odate,osum,odid,pname,pprice,pcount WHILE FETCH _STATUS=0BEGINPRINT eid+''+sid+''+oid+''+odate+''+osum+''+odid+''+pname+''+pprice+''+pcountFETCH FROM ORDERDETAILS_CURSOR INTO eid,sid,oid,odate,osum,odid,pname,pprice,pcount ENDCLOSE ORDERDETAILS_CURSORDEALLOCATE ORDERDETAILS_CURSOR设置触发器：1)为职工建立一个更新触发器,当修改工资值超过6%时发出警示信息CREATE TRIGGER reminder1ON DBO.职工FOR UPDATEASDECLARE new money,old moneySELECT new=工资 FROM INSERTEDSELECT old=工资 FROM DELETEDIF new/old>1.06BEGINRAISERROR('修改工资值超过6% 取消修改',16,10)ROLLBACKEND2）为订购单表建立一个插入和更新触发器,约束规则是：当订购单金额小于3000时必须使用本地供应商（发出订购单的仓库和供应商在同一城市），如果不满主要求，则拒绝操作，并给出错误信息CREATE TRIGGER reminder2 ON DBO.订购单FOR INSERT，UPDATEASDECLARE storeid char(6),sid char(20),saddr char(20), citychar(4)SELECT storeid=Substring(职工号,1,6),SID=供应商号 FROM INSEREDWHERE 金额 < 3000IF NOT storeid IS NULLBEGINSELECT CITY=城市 FROM 仓库 WHERE 仓库号=storeidSELECT saddr=地址 FROM 供应商 WHERE 供应商号=sidIF saddr not like '%'+city+'%'BEGINRAISERROR('3000元以下订单请用本地供应商,取消订单修改',16,10)ROLLBACKENDEND(3)当“工资”发生更改时，向职工显示消息CREATE TRIGGER manage ON SMWH.职工AFTER insert,UPDATE,DELETEAS RAISERROR('注意人事部门',16,1)UPDATE SMWH.职工 set 工资=5000 where 职工号='AA'3系统详细设计（1）数据维护 添加仓库信息： 仓库5条记录 职工10 供应商 5 订购表 20 订购表明细 50INSERT INTO 仓库(仓库号, 城市, 面积)VALUES ('AA0001', '广州', 2500)INSERT INTO 职工(职工号, 仓库, 经理, 工资)VALUES ('AA', 'AA0001','AA',5000)INSERT INTO 供应商(供应商号, 供应商名, 地址)VALUES ('S001', '广州市莲香楼有限公司','广州市城西')INSERT INTO 订购单 (职工号, 供应商号, 订购单号, 订购日期, 金额)VALUES ('AA', 'S001', 'OR0001','',2000)INSERT INTO 订购单明细 (订购单号, 序号, 产品名称, 单价, 数量)VALUES ('OR0001', '01', '双黄白莲月饼4个装', 100 , 50)修改职工信息：（将x城市仓库的职工工资提高10%）UPDATE 职工 SET 工资 = 工资*(1.1) WHERE 仓库 IN (SELECT 仓库号 FROM 仓库 WHERE 城市='广州')删除仓库信息： DELETE FROM 仓库 WHERE 仓库号='AB0002'(2) 查询用SELECT 语句完成以下查询(1) 考虑不去掉重复值：Select 工资 from 职工去掉重复值：Select DISTINCT 工资 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 工资<1300UNION select 职工号 from 职工 where 仓库=''and 工资<1300(6)当x=1600,y=1300select * from 职工 where 工资 between 1300 and 1600(7)select * from 供应商 where 供应商名 like '%公司'(8)当x=广州时select * from 仓库 where 城市 != '广州'(9)select * from 订购单 where 供应商号 is null(10)select * from 订购单 where 供应商号 is not null (11)select * from 职工 order by 工资(12)select * from 职工 order by 仓库 asc ,工资 desc(13)当x=1000select 职工号，城市 from 仓库 join 职工 On 职工.仓库=仓库.仓库号 where 工资>1000(14) 当x=200select 职工号，城市 from 仓库 join 职工 On 职工.仓库=仓库.仓库号 where 面积>20015)SELECT 经理,职工号 FROM 职工WHERE 职工号 Like SUBSTRING(职工号,1,6) + '%' ORDER BY 职工号 (16)SELECT * FROM 仓库 CROSS JOIN 职工(17)SELECT 仓库号,城市,面积,职工号,经理,工资 FROM 仓库 CROSS JOIN 职工WHERE 职工.职工号 LIKE 仓库.仓库号+'%'ORDER bY 仓库.仓库号(18)select 供应商名，供应商号，订购单号，订购单日期 From 订购单 join 供应商 on 订购单.供应商号=供应商.供应商号(19) select 供应商名，供应商号，订购单号，订购单日期 From订购单 left join供应商on 订购单.供应商号=供应商.供应商号(20) select 供应商名，供应商号，订购单号，订购单日期 From订购单 right join供应商on 订购单.供应商号=供应商.供应商号(21) select 供应商名，供应商号，订购单号，订购单日期 From订购单full join供应商on 订购单.供应商号=供应商.供应商号(22)当x=1100时SELECT 城市 FROM 仓库 WHERE 仓库号=ANY(SELECT 仓库 FROM 职工 WHERE 工资>1100)(23)SELECT * FROM 仓库 WHERE NOT 仓库号=ANY(SELECT DISTINCT 仓库 FROM 职工 WHERE 工资<1100)(24)当x=AA时SELECT * FROM 职工WHERE 工资=(SELECT 工资 FROM 职工 WHERE 职工号='AA')(25) 查询哪些城市的仓库向地址为x的供应商发出了订购单。SELECT * FROM 仓库 WHERE 仓库号=ANY(SELECT SUBSTRING(职工号,1,6) AS 仓库号 FROM 订购单 WHERE 供应商号=ANY(SELECT 供应商号 FROM 供应商 WHERE 地址 ='广州市城西')(26) SELECT 供应商名 FROM 供应商wHERE 地址='广州天河 ' AND 供应商号 IN (SELECT 供应商号 FROM 订购单WHERE SUBSTRING(职工号,1,6)=(SELECT 仓库号 FROM 仓库 WHERE 城市='广州')SELECT 供应商名,城市FROM 供应商 JOIN 订购单 ON 供应商.供应商号=订购单.供应商号 JOIN 职工ON 订购单.职工号=职工.职工号 JOIN 仓库 ON 职工.仓库=仓库.仓库号WHERE 供应商.地址='广州天河' AND 仓库.城市='广州'(27) SELECT * FROM 职工WHERE 工资>=ALL(SELECT 工资 FROM 职工 WHERE SUBSTRING(职工号,1,6)='AA0001')(28)SELECT * FROM 仓库WHERE 仓库号 IN(SELECT 仓库 FROM 职工 WHERE 工资>=ALL(SELECT 工资 FROM 职工 WHERE SUBSTRING(职工号,1,6)='AA0001')(29)SELECT 职工号,MAX(金额) AS 订单最高金额 FROM 订购单GROUP BY 职工号(30) SELECT * FROM 仓库 WHERE NOT 仓库号=ANY(SELECT DISTINCT 仓库 FROM 职工 WHERE 职工号 IS NOT NULL)(31)SELECT * FROM 仓库 WHERE 仓库号=ANY(SELECT DISTINCT 仓库 FROM 职工 WHERE 职工号 IS NOT NULL)(32)SELECT COUNT(城市) AS 仓库数目 FROM 仓库(33)SELECT SUM(工资) AS 支付工资 FROM 职工(34)SELECT SUM(工资) AS 支付工资 FROM 职工 WHERE 城市 IN ('韶关','广州')(35)SELECT AVG(面积) AS 平均面积 FROM 仓库 WHERE NOT 仓库号=ANY(SELECT 仓库 FROM 职工 WHERE 工资<=1500)(36)SELECT MAX(金额) FROM 订购单 WHERE 职工号=ANY(SELECT 职工号 FROM 职工 WHERE 工资>1500)(37)SELECT 仓库,AVG(工资) AS 平均工资 FROM 职工 GROUP BY 仓库(38)SELECT 仓库号,MAX(金额) AS 最高金额, MIN(金额) AS 最低金额, AVG(金额) AS 平均金额 FROM 订购单 JOIN 职工 ON 订购单.职工号=订购单.职工号 JOIN 仓库 ON 仓库.仓库号 =职工.仓库 GROUP BY 仓库号(39)SELECT 订购单号,AVG(数量*单价) AS 平均金额, SUM(数量*单价) AS 金额总计 FROM 订购单明细 GROUP BY 订购单号 having COUNT(序号)>=3(40) SELECT * FROM 职工 ORDER BY 仓库COMPUTE AVG(工资),SUM(工资) BY 仓库COMPUTE AVG(工资),SUM(工资)(41)SELECT * ,单价*数量 AS 金额 FROM 订购单明细ORDER BY 订购单号COMPUTE AVG(单价*数量),SUM(单价*数量) BY 订购单号COMPUTE AVG(单价*数量),SUM(单价*数量)(42)SELECT * FROM 订购单COMPUTE AVG(金额),SUM(金额)(9) 系统访问用用户：smwh,（10）数据库备份计划。(文字说明) 数据库维护计划: 备份数据库：db_warehouse, master, 每周备份一次,为不同数据库建立分立文件夹,设置为周五下午17:00 日志文件:每天备份,时间是19:00 数据库,日志文件的备份路径是: D: 历史数据:备份到同上的路径中.二、 结论：一、 通过将各部件进行组装，生成的项目实现了预期的功能。制作项目时经常遇到找不到图片与表单，比较麻烦。1. 制作表单的时候很多时候忽略了一部就造不出原先想象中的表单。2. 经常忘记保存已经修改的信息。
