数据库综合设计实验.doc

综合实验报告 课程名称_ 数据库原理与应用 实验名称_ 数据库综合设计实验学生学院_ _专业班级_ _ 学 号_ _ 学生姓名_ _ _ 指导教师_ _20 年 月 日14实验报告1、 实验方案：(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 仓库号=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) = OR0000 and 订购单号 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 工资50有约束 改正方法：UPDATE 职工信息 SET 面积=100 WHERE 仓库号=AA0001(3) 删除 错误语句：DELETE 职工 WHERE 经理=AA000108 出错原因：违反了参照完整性,经理AA000108不存在 改正方法：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/old1.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 金额 1100（4)当y=1100时 select仓库 from 职工 where 工资1100(5)当x=AA0001，y=AA0002,z=1300时select 职工号 from 职工 where 仓库=AA0001and 工资1300UNION select 职工号 from 职工 where 仓库=and 工资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 工资=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)(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