数据库综合设计试验

1、cheng综合实验报告课程名 称 数据库原理与实验名称_ 数摒库综合设计实验学生学院专业班级一学 号学生姓名指导教师20年 月曰cheng实验报告一、实验方案：（1）实验软件坏境：Windows XP中文版,SQL SERVER2000中文版（2）项目名称：订货数据管理系统（3）完成方式：由课程实验分多次完成，在最后一次实验提交。（4）提交内容：数据库数据文件，口志文件，综合实验报告。二、实验结果与数据1. 系统功能1. 系统功能本系统的工作坏境是可在订货数据管理系统局域网内操作，供商家查询订货及查看供应 记录。为订货一一供应的运用提供一个方便快捷的工具，方便商家寻找货源，提高生产资料 购买效

2、率。（1）基础数据维护。管理员可以进行仓库信息，职工信息的数据添加，删除，修改，管理 员可以进行订购单数据的添加，删除，修改。（2）新订购单管理。管理员可以订购新的货物形成新的订购单，对新订购单验收并编号。（3）订购单明细管理。管理员可以进行订购单明细管理。非管理员可以查看自已订购单明 细信息。（4）订购单查询。全体人员可使用订购单查询功能。查询可按职工号，供应商号，订购单 号等订货数据管理系统基础数库维护供应商信息管仓库信息管职工信息管理硕认订购单新增订购单管理系统维护负责职工用户管理更改口令图1系统功能模快层次结构图2. 数据库设计：根据上面对系统做的功能分析、系统设计，规划出本系统中使用

3、的数据库实体，得出数 据库概念模型系统使用中涉及的实体包扌舌：职工，仓库，供应商，订购单，订购单明细隐含的实体：经理，工资，城市，面积（1）职工（职工号，仓库，经理，工资）（2）仓库（仓库号，城市，面枳）供应商（供应商号，供应商名，地址）（4）订购单（职工号，供应商，订购单号，订购口期，金额）（5）订购单明细（订购单号，序号，产品名称，单价，数量）实体E-R图如下:图2系统E-R图从概念模型，得到数据库的逻辑模型如下:表名字段Z字段类型关键字约束仓库仓库号6个字符是仓库号=?AA0000,AND 仓库号=NZ999y城市10个字符UNIQUE , NOT NULL面枳数值面积=50职工仓库是N

4、OT NULL FOREIGN KEY REFERENCES 仓库（仓库号）职工号8个字符是PRIMARY KEY经理工资货币工资 BETWEEN 1000 AND 10000供应商供应商号4个字符是PRIMARY KEYsubstiiiig(供应商号丄1) = S and right(供应商号,3) 000 and light(供应商号,3)1.06BEGINRAISERRORC修改工资值超过6%取消修改16,10)ROLLBACKEND2) 为订购单表建立一个插入和更新触发器,约束规则是：当订购单金额小于3000 时必须使用本地供应商(发出订购单的仓库和供应商在同一城市)，如果不满主 要求

5、，则拒绝操作，并给出错误信息CREATE TRIGGER renunder2ON DBO.订购单FOR INSERT, UPDATEASDECLARE storeid char(6),sid chai(20),saddi chai(20), citvchai(4)SELECT storeid=Substrmg(职工号丄6),SID=供应商号 FROM INSERED WHERE 金额 v 3000IF NOT storeid IS NULLBEGINSELECT CITY=城市 FROM 仓库 WHERE 仓库号=storeidSELECT saddi-地址FROM 供应商 WHERE供应商号

6、=耐IF saddi not like *%+city+%BEGINRAISERROR(3000元以下订单请用本地供应商，取消订单修改;16,10)ROLLBACKENDEND(3) 当工资”发生更改时，向职工显示消息CREATE TRIGGER manageON SMWH.职工AFTER mseil,UPDATE,DELETEASRAISERROR（*注意人事部门；16,1）UPDATE SMWH.职工 set 工资=5000 wheie 职工号=AA0001013. 系统详细设计（1）数据维护5条记录1052050添加仓库信息:仓库职工供应商订购表订购表明细INSERT INTO仓库（仓库

7、号,城市,面积）VALUES C AAOOOr,广州,2500）INSERT INTO职工（职工号，仓库，经理，工资）VALUES （ AA000101, AA0001, AA000101, 5000）INSERT INTO供应商（供应商号,供应商名，地址）VALUES （S001,广州市莲香楼有限公司，广州市城西）INSERT INTO订购单（职工号，供应商号，订购单号，订购日期，金额）VALUES （AA000101, S001, 0R0001, 2000）INSERT INTO订购单明细（订购单号，序号，产品名称，单价，数量）VALUES （OR0001, 01,双黄白莲月饼 4 个装1

8、00 , 50）修改职工信息：（将x城市仓库的职工工资提高10%）UPDATE职工SET工资=工资*（1.1）WHERE仓库IN （SELECT仓库号FROM 仓库 WHERE城市=广州? 删除仓库信息：DELETE FROM 仓库 WHERE仓库号=AB0002，查询用SELECT语句完成以下查询(1)考虑不去掉重复值：Select工资fiom职工去掉重复值：Select DISTINCT工资fiom职工(2) Select * fiom 仓库(3) 当 x=1100 时 SELECT 职工号 fiom 职工 where 工资1100(4) 当 y=1100 时 select 仓库 fiom

9、 职工 where 工资 1100(5) 当 x=AA0001, y=AA0002,z=1300 时select 职工号 from 职工 where 仓库=AAOOOland 工资1300UNIONselect职工号fiom职工where仓库=and工资1300(6) 当 x=1600,尸 1300select * from 职工 where 工资 between 1300 and 1600(7) select * fiom供应商wheie供应商名like %公司,当x=广州时select * from 仓库 where城市!=广州,(9) select * fiom 订购单 wheie 供应

10、商号 is null(10) select * fiom 订购单 where 供应商号 is not null(11) select * fiom 职工 order by 工资(12) select * fiom 职工 older by 仓库 asc,工资 desc(13) 当 x=1000select职工号，城市fiom仓库join职工On职工.仓库=仓库.仓库号where 工资 1000(14) 当 x=200select职工号，城市fiom仓库join职工On职工.仓库=仓库.仓库号where 面积 20015)SELECT 经理，职工号 FROM 职工 WHERE 职工号 LikeSU

11、BSTRING(职工号,1,6)十% ORDER BY 职工号(16) SELECT * FROM 仓库 CROSS JOIN 职工(17) SELECT仓库号，城市，面积，职工号，经理，工资FROM仓库CROSSJOIN职工WHERE职工.职工号LIKE仓库.仓库号十:ORDER bY仓库.仓库号(18) select供应商名，供应商号，订购单号，订购单日期From订购单join供应商on订购单.供应商号=供应商.供应商号(19) select供应商名，供应商号，订购单号，订购单日期From订购单left jom供应商on订购单.供应商号=供应商.供应商号(20) select供应商名，供应

12、商号，订购单号，订购单日期From订购单门ght j om供应商on订购单.供应商号=供应商.供应商号(21) select供应商名，供应商号，订购单号，订购单日期From订购单fiill join供应商on订购单.供应商号=供应商.供应商号(22) 当 x=1100 时SELECT 城市 FROM 仓库 WHERE仓库号=ANY(SELECT仓库FROM 职工 WHERE工资1100)(23) SELECT * FROM 仓库 WHERE NOT 仓库号=ANY(SELECTDISTINCT 仓库 FROM 职工 WHERE 工资1100)(24) 当 x=AA000114 时SELECT

13、* FROM 职工 WHERE 工资=(SELECT 工资 FROM 职工WHERE 职工号AAOOOIM)(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

14、仓库号 FROM 仓库 WHERE 城市=广州?)SELECT供应商名，城市FROM供应商JOIN订购单ON供应商.供应商号=订购单.供应商号JOIN 职工ON订购单.职工号=职工.职工号JOIN仓库ON职工.仓库=仓库.仓库号WHERE供应商.地址=广州天河AND仓库.城市=广州，（27）SELECT * FROM 职工WHERE 工资=ALL（SELECT 工资 FROM 职工 WHERE SUBSTRING（职 工号,1,6）=0001）（28）SELECT * FROM 仓库 WHERE 仓库号 DESELECT 仓库 FROM 职工 WHERE 工资 =ALL（SELECT 工资 F

15、ROM 职工 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）SELECTCOUNT（城市）AS仓库数目FROM 仓库（33）SE

16、LECT SUM（工资）AS支付工资FROM 职工（34）SELECT SUM（工资）AS支付工资FROM 职工 WHERE城市IN C韶 关；广州）（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最高金额，MI

17、N（金额）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 IT购单明细ORDER BY订购单 号COMPUTE AVG（单价*数量）,SUM（单价*数量）BY订购单号COMPUTE AVG（单价*数量）,SUM（单价*数量）（42）SELECT * FROM 订购单 COMPUTE