重庆科技学院数据库原理课程设计报告安光照2011

1、重庆科技学院课程设计报告 院（系）:_电气与信息过程学院 专业班级: 计科1102 学生姓名: 安光照 学 号: 设计地点（单位）_第一实验楼_ _ _ _ 设计题目:_ _校园小商品交易系统设计_ 完成日期： 2013年 6月 28日 指导教师评语: _ _成绩（五级记分制）:_ _ 指导教师（签字）: _ _摘要 现在的校园超市，越来越需要现代化，学生的要求提高，我们商品的种类增加，如果继续使用以前的数据库，那么不仅是效率无法提高，而且损失会大大增大。 校园小商品管理系统，分为四个板块，分为为：供应板块，库存板块，仓库工作板块，购物板块，不仅仅为商家，以及超市管理者提供了大大的便利，而且也

2、为普通的用户，顾客，提供了很大的便利！本系统采用SQL Server 2000作为数据库，使用ODBC作为数据源。在实现数据库部分功能时按照要求创建了触发器和存储过程。关键词： 校园小商品市场 管理 SQL Server 2000 触发器 存储过程重庆科技学院课程设计任务书设计题目：校园小商品交易系统设计学生姓名安光照课程名称数据库原理课程设计专业班级计科2011-02地 点I313和计算机自主学习中心起止时间2013.6.24-2013.6.28设计内容及要求内容： 学生根据本课程设计指导书中的题目，进行设计。（1）用户管理：包含三类用户：管理员、商品发布者、普通用户、访客。（2）向管理员提

3、供以下功能：自身密码修改，其他用户添加删除，用户信息修改、统计。商品信息添加、修改、删除、查找、统计。（3）向商品发布者提供以下功能：注册、登陆、注销、自身密码修改、自身信息修改。商品信息发布，自身商品信息统计。查找浏览其他商品。（4）向一般用户提供以下功能：商品浏览、查找、获知商家联系方式，定购商品。（5）向访客提供以下功能：商品浏览、查找、获知商家联系方式。要求： 按照数据库系统开发步骤进行数据库概念结构设计、逻辑结构设计、物理结构设计,使学生掌握数据库应用软件的开发流程，SQL语句的使用和存储过程的使用。设计参数至少5个表结构；5个存储过程；5个触发器；5个视图、2处使用事务处理、3处使

4、用游标过程报告格式规范进度要求19.1 讲课 阅读分析任务书 制订设计计划19.2-3 需求分析 概念结构设计19.4-5 概念结构设计准备文挡20.1-3逻辑结构设计物理结构设计20.4-5写文挡答辩交设计报告书参考资料1雷亮等数据库原理课程设计指导书2王珊、萨师煊数据库系统概述(第四版) 北京：高等教育出版社2006。3C.J.Date著孟小峰、王珊等译数据库系统导论(第8版) 北京：机械工业出版社.2007。4陈根才等. 数据库课程设计浙江浙江大学出版社2007其它说明.本表应在每次实施前一周由负责教师填写二份，院系审批后交院系办备案，一份由负责教师留用。.若填写内容较多可另纸附后。3.

5、一题多名学生共用的，在设计内容、参数、要求等方面应有所区别。教研室主任： 指导教师：裴仰军、游明英 2013年 06 月 15 日目录摘要II1 需求分析11.1 系统目标设计11.2 系统功能分析11.3 功能模块分析21.4 数据字典22 概念结构设计62.1 构成系统的实体型62.2 校园小商品E-R图73 逻辑结构设计83.1逻辑结构简介83.2 关系模型84物理模型设计104.1定义数据库104.2 创建表104.3 创建存储过程154.4 创建视图194.5 创建触发器214.6 创建游标234.7 创建用户，角色以及 授权25总结26致谢27参考文献281 需求分析1.1 系统目

6、标设计 信息时代不断发展，校园小商品系统也需要不断更新，现代化，才能实现双赢！1.2 系统功能分析 (1) 根据不同的用户角色，进行授权，使不同用户具有相应的权限，比如管理员可以管理该数据库的全部数据更新；供应商可以修改自己的商品的信息等等。 （2）通过供应关系的建立，将供应商与商品信息联系起来，使供应商可以随时更新自己所提供的商品数据。 （3）通过建立库存关系，将仓库与商品联系起来，使管理员以及工作人员可以较为方便的对商品的库存量进行查询，同时也比较方便的进行管理。 （4）通过建立仓库工作表，将工作人员与仓库联系起来，随机分配的机制，将工作人员分到各仓库，不仅方便了管理者对工作的分配，同时也

7、健全了责任机制，提高了工作人员的工作责任以及效率。 （5）通过建立顾客购物车，将顾客与商品表进行了链接，方便了顾客的查询，同时也提供了顾客对商品的定制等功能。1.3 功能模块分析 根据系统功能的基本要求，可对整个系统划分为几个模块校园小商品市场管理系统基本信息维护供应关系管理商品信息维护用户信息维护库存关系管理仓库信息维护商品信息更新供应商信息购物车模块管理数据库管理商品数量更新商品数量信息顾客购物管理各个信息更新密码权限管理图1.1 功能模块分析1.4 数据字典表1.1 管理者信息表名称数据类型说明MgID char(9)管理者ID，主键MgPsswordchar(6)管理者密码MgName

8、char(20)管理人员姓名MgSexchar(2)性别MgTelchar(15)电话表1.2 工作人员表名称数据类型说明UsIDchar(9)员工编号 关键字UsNamechar(20)员工姓名UsSexchar(2)员工性别UsTelchar(15)电话表1.3 普通用户表名称数据类型说明UssNamechar(20)用户登录名UssPasswordchar(6)用户密码表1.4 用户商品表名称数据类型说明UssNamechar(20)顾客姓名GdNumchar(9)商品编号GdCountint数量表1.5 访客表名称数据类型说明ViIDchar(9)访客登录ID 关键字Vikeychar

9、(6)登录密码表1.6 仓库表名称数据类型说明StNumchar(9)仓库编号 关键字StAreachar(5)仓库面积StTelchar(15)仓库电话表1.7 供应商表名称数据类型说明SuNumchar(9)供应商编号，主键SuPasswordchar(6)供应商密码SuNamechar(20)供应单位SuAdresschar(20)供应商地址SuTelchar(15)供应商电话SuBankchar(20)供应商账号表1.8 商品表名称数据类型说明GdNumchar(9)商品编号，主键GdNamechar(20)商品名称GdPriceint商品价格GdSpecchar(20)商品规格GdD

10、scribechar(20)商品描述表1.9 供应关系表名称数据类型说明SuNumchar(9)供应商编号，主键GdNumchar(9商品编号，主键SGFlowint供应数量表2.0 库存关系表名称数据类型说明StNumchar(9)仓库编号，主键GdNumchar(9)商品编号，主键SGLevelint库存量表2.0 库存关系表名称数据类型说明StNumchar(9)仓库编号，主键UsIDchar(9)工作人员编号，主键2 概念结构设计2.1 构成系统的实体型由校园小商品交易系统的数据流图和数据字典，抽取出系统的10个主要实体，包括：管理员实体，工作人员实体，供应商实体，商品实体，顾客实体，

11、访客实体，仓库实体，供应关系实体，库存关系实体，购物车实体，仓库工作表实体。管理员实体型属性：MgID, MgPsaaword, MgName, MgSex, MgTel工作人员实体型属性：UsID, UsName, UsSex, UsTel 顾客实体型属性：UssName, UssPassword 购物车实体型属性：UssName GdNum, GdCount访客实体型属性：ViID， Vikey 仓库实体型属性：StNum, StArea StTel供应商实体型属性： SuNum, SuPassword, SuName, SuAdress, SuTel, SuBank商品实体属性： GdN

12、um, GdName, GdPrice, GdSpec, GdDscribe 供应关系实体属性：SuNum, GdNum, SGFLow 库存关系实体属性：StNum, GdNum, SGLevel 仓库工作关系实体属性：StNum ,UsID 2.2 校园小商品E-R图图2.1 校园小商品管理系统E-R3 逻辑结构设计3.1逻辑结构简介逻辑结构设计就是把概念结构设计阶段设计好的基本E-R图转换为与选用DBMS产品所支持的数据模型相符合的逻辑结构。 设计逻辑结构一般分为3步进行：(1) 将概念结构转换为一般的关系、网状、层次模型；(2) 将转换来的关系、网状、层次模型向特定DBMS支持下的数据

13、模型转换；(3) 对数据模型进行优化。3.2 关系模型 将E-R图转换为关系模型实际上就是要奖实体型、实体的属性和实体型之间的联系转换为关系模式，这种转换一般遵循如下原则：一个实体型转换为一个关系模式,一个多对多的关系可以转化为一个关系模式，而一对多的关系可以融合在其他的相关的关系模式中。例如： (1) 商品与供应商是多对一的关系，所以将商品与供应商设计成以下关系模式： 商品（GdNum, GdName, GdPrice, GdSpec, GdDscribe ） 供应商（SuNum, SuPassword, SuName, SuAdress, SuTel, SuBank） 属于（GdNum,

14、GdName, GdPrice, GdSpec, GdDscribe SuNum, SuPassword, SuName, SuAdress, SuTel, SuBank ） (2)商品与仓库是多对一的关系，所以将商品与仓库设计成以下关系模式： 商品（GdNum, GdName, GdPrice, GdSpec, GdDscribe ） 仓库 （StNum, StArea StTel） 属于（GdNum, GdName, GdPrice, GdSpec, GdDscribe StNum, StArea StTel） （3）工作人员与仓库是多对一的关系，所以将工作人员与仓库设计以下关系模式： 工

15、作人员（UsID, UsName, UsSex, UsTel） 仓库 （StNum, StArea StTel） 属于（UsID, UsName, UsSex, UsTel StNum, StArea StTel） 4物理模型设计数据库物理设计是将逻辑设计影射到存储介质上，利用可用的硬件和软件功能尽可能快地对数据进行物理访问和维护。4.1定义数据库SQL Server 2000数据库文件分为3中类型：主数据文件、次数据文件和日志文件。通过SQL语言进行数据库创建，创建数据库的语句如下：Create database 工资管理系统;4.2 创建表创建管理员信息表create Table Mana

16、ger ( MgID char(9) primary key, /*管理人员ID，设置主键*/ MgPssword char(6), /*管理人员密码*/ MgName char(20) NOT NULL, /*管理人员姓名*/ MgSex char(2) check( MgSex in (男,女) ),/*管理人员性别*/ MgTel char(15) /*管理人员电话*/);创建工作人员表create Table Users( UsID char(9) primary key, /*普通工作人员ID，设置主键*/ UsName char(20) NOT NULL, /*普通工作人员姓名，不

17、能为空*/ UsSex char (2) check(UsSex in (男,女), /*普通工作人员性别*/ UsTel char(15)/*普通工作人员电话*/);创建普通顾客表 create Table Useres( UssName char(20) primary key, UssPassword char(6),);创建购物车表create Table UG( UssName char(20), GdNum char(9), GdCount int primary key(UssName,GdNum), /*设置主键*/ foreign key(UssName) reference

18、s Useres(UssName) /*在表级定义参照完整性*/on delete cascade /*当删除Supplier表中的元组时，级连删除SG1表中相应元组*/on update cascade, /*当更新Supplier表中的元组时，级连更新SG1表中相应元组*/ foreign key(GdNum) references Goods(GdNum) /*在表级定义参照完整性*/on delete no ACTION /*当删除Goods表中的元组造成了与SG1表不一致时拒绝删除*/on update cascade /*当更新Goods表中的元组时，级连更新SG1表中相应元组*/

19、);创建访客表create Table Visitor( ViID char(9) primary key,/*访客登陆名*/ Vikey char(6) NOT NULL,/*访客登陆密码*/);创建仓库表create Table Storehouse( StNum char(9) primary key,/*仓库编号*/ StArea char(5),/*仓库面积*/ StTel char(15) /*仓库电话*/);创建供应商表create Table Supplier( SuNum char(9) primary key, /*供应商编号*/ SuPassword char(6), /

20、*供应商密码*/ SuName char(20) NOT NULL, /*供应商姓名*/ SuAdress char(20), /*供应商地址*/ SuTel char(15), /*供应商电话*/ SuBank char(20) /*供应商银行账号*/);创建商品表create Table Goods( GdNum char(9) primary key, /*商品编号*/ GdName char(20), /*商品名称*/ GdPrice int , /*商品价格*/ GdSpec char(20), /* 产品规格*/ GdDscribe char(20) /*商品描述*/);创建供应关

21、系表create Table SG1( SuNum char(9), /*供应商编号*/ GdNum char(9), /*商品编号*/ SGFlow int check(SGFlow=0), /*供应量*/ primary key(SuNum,GdNum), /*设置主键*/ foreign key(SuNum) references Supplier(SuNum) /*在表级定义参照完整性*/ on delete cascade /*当删除Supplier表中的元组时，级连删除SG1表中相应元组*/on update cascade, /*当更新Supplier表中的元组时，级连更新SG1

22、表中相应元组*/ foreign key(GdNum) references Goods(GdNum) /*在表级定义参照完整性*/on delete no ACTION /*当删除Goods表中的元组造成了与SG1表不一致时拒绝删除*/on update cascade /*当更新Goods表中的元组时，级连更新SG1表中相应元组*/ );创建库存关系create Table SG2( StNum char(9), /*仓库编号*/ GdNum char(9), /*商品编号*/ SGLevel int check(SGLevel=0), /*各商品库存量*/ primary key (St

23、Num,GdNum), /*设置主键*/ foreign key (StNum) references Storehouse(StNum) /*在表级定义参照完整性*/on delete cascade /*当删除Storehouse表中的元组时，级连删除SG2表中相应元组*/on update cascade, /*当更新Sturehouse表中的元组时，级连更新SG2表中相应元组*/ foreign key (GdNum) references Goods(GdNum) /*在表级定义参照完整性*/on delete no ACTION /*当删除Goods表中的元组造成了与SG2表不一致

24、时拒绝删除*/on update cascade /*当更新Goods表中的元组时，级连更新SG2表中相应元组*/);创建仓库工作表create Table SU( StNum char(9), /*仓库编号*/ UsID char(9), /*普通用户编号*/ primary key (StNum,UsID), /*设置主键*/ foreign key (StNum) references Storehouse(StNum) /*在表级定义参照完整性*/on delete cascade /*当删除Storehouse表中的元组时，级连删除SG2表中相应元组*/on update casca

25、de, /*当更新Sturehouse表中的元组时，级连更新SG2表中相应元组*/ foreign key (UsID) references Users(UsID) /*在表级定义参照完整性*/on delete no ACTION /*当删除Goods表中的元组造成了与SU表不一致时拒绝删除*/on update cascade /*当更新Goods表中的元组时，级连更新SU表中相应元组*/);4.3 创建存储过程create procedure AddGoods(GdNum char(9), GdName char(20), GdPrice int, GdSpec char(20), G

26、dDscribe char(20)as insert into Goods(GdNum,GdName,GdPrice,GdSpec,GdDscribe) values (GdNum,GdName,GdPrice,GdSpec,GdDscribe)DROP procedure AddGoods/*创建查询存储过程*/create procedure selecting1as select *from Goods /*调用该存储过程*/ exec AddGoods , 面包,5,开瑞,营养就在其中 exec AddGoods ,方便面,2,统一,使生活加快节奏 exec AddGoods , 矿泉

27、水,1,康师傅,水中的营养更高 exec AddGoods , 雪糕,5,巧乐兹,炎热的夏日需要爽爽的凉意 exec AddGoods , 瑞士卷,3,旺旺,零食，让生活充满乐趣. exec AddGoods , 火腿肠,2,双汇,浓浓的火腿香! exec AddGoods , 啤酒,2,山城,cool！ exec AddGoods , 鸡腿,9,双汇,饿了就找我哦！ exec AddGoods , 瓜子,5,真心瓜子,闲暇时你会想起我. exec AddGoods , 饼干,4,奥利奥,我虽然贵，但是我很好吃！exec selecting1/*创建对供应商的插入存储过程*/create pr

28、ocedure AddSupplier( SuNum char(9), SuPassword char(6), SuName char(20), SuAdress char(20), SuTel char(15), SuBank char(20)as insert into Supplier(SuNum,SuPassword,SuName,SuAdress,SuTel,SuBank) values (SuNum,SuPassword,SuName,SuAdress,SuTel,SuBank)drop procedure AddSupplier/*创建查询存储过程*/create procedu

29、re selecting2as select *from Supplier/*调用该存储过程*/ exec AddSupplier ,双汇,重庆,23 exec AddSupplier ,奥利奥,杭州,73 exec AddSupplier ,真心瓜子,河北,26 exec AddSupplier ,山城,江苏,28 exec AddSupplier ,康师傅,广东,78 exec AddSupplier ,旺旺,重庆,15 exec AddSupplier ,巧乐兹,重庆,69 exec AddSupplier ,统一,上海,90 exec AddSupplier ,开瑞,重庆,52 exe

30、c AddSupplier ,双汇,四川,65 exec selecting2/*创建仓库插入存储程序*/create procedure AddStorehouse( StNum char(9), StArea char(5), StTel char(15)as insert into Storehouse(StNum,StArea,StTel) values (StNum,StArea,StTel)/*创建查询存储过程*/create procedure selecting3as select *from Storehouse/*调用查询过程*/ exec AddStorehouse 01

31、,120, exec AddStorehouse 02,50, exec AddStorehouse 03,80, exec selecting3 /*创建管理人员的插入存储器*/create procedure AddManager( MgID char(9), MgPssword char(6), MgName char(20), MgSex char(2), MgTel char(15)as insert into Manager(MgID,MgPssword,MgName,MgSex,MgTel) values (MgID,MgPssword,MgName,MgSex,MgTel)dr

32、op procedure AddManagercreate procedure selecting4as select *from Manager/*调用该存储过程*/ exec AddManager 001,An,男, exec AddManager 002,Qn,女, exec AddManager 003,Wn,男, exec selecting4/*创建普通用户的插入存储过程*/ create procedure AddUsers( UsID char(9), UsName char(20), UsSex char(2), UsTel char(15) ) as insert into

33、 Users(UsID,UsName,UsSex,UsTel) values (UsID,UsName,UsSex,UsTel)create procedure selecting5as select *from Users/*调用该存储过程*/ exec AddUsers 1000,Ao,男, exec AddUsers 1001,Bo,男, exec AddUsers 1002,Co,女, exec AddUsers 1003,Do,男, exec AddUsers 1004,Eo,女,4 exec selecting5 create procedure p_select_SG2stnum

34、 char(9)asselect gdname,SGLevelfrom goods join sg2 on goods.gdnum=sg2.gdnumwhere sg2.stnum=stnumgoexec p_select_SG2 01create procedure p_select_SG21gdnum char(9)asselect gdnum,SGLevelfrom storehouse join sg2 on storehouse.stnum= sg2.stnumwhere sg2.gdnum=gdnumgodrop procedure p_select_SG21exec p_sele

35、ct_SG21 /*供应表中插入信息*/ insert into SG1 (SuNum,GdNum,SGFlow) values (,1500) insert into SG1 (SuNum,GdNum,SGFlow) values (,1000) insert into SG1 (SuNum,GdNum,SGFlow) values (,1200) insert into SG1 (SuNum,GdNum,SGFlow) values (,150) insert into SG1 (SuNum,GdNum,SGFlow) values (,2000) insert into SG1 (SuN

36、um,GdNum,SGFlow) values (,800) insert into SG1 (SuNum,GdNum,SGFlow) values (,1800) insert into SG1 (SuNum,GdNum,SGFlow) values (,2500) insert into SG1 (SuNum,GdNum,SGFlow) values (,1500) insert into SG1 (SuNum,GdNum,SGFlow) values (,3000)select *from SG14.4 创建视图create view VGGasselect GdNum,GdName,G

37、dPrice,GdSpecfrom Goodswhere GdPrice=40select *from VUG2DROP view VUG2create view VSG2asselect GdName商品名称,Storehouse.StNum仓库编号,SGLevel库存数量from Goods,Storehouse,SG2where Goods.GdNum=SG2.GdNum AND SG2.StNum=Storehouse.StNum and SGLevel=1000select *from VSG2drop view VSG24.5 创建触发器create trigger TGoodso

38、n Goods for insert asif(select Gdprice from inserted)=0 begin print 价格为负，不允许插入rollback transaction endinsert into Goods values (0001,QLZ,-1,DFFD,QQQQ)select *from Goodsdrop trigger TGoodsCREATE TRIGGER TGoods2ON SG2FOR INSERTASBEGIN TRANSACTION UPDATE SG2 SET SGLevel=SGLevel+1 WHERE StNum IN (SELECT

39、 StNum from INSERTED)COMMIT TRANSACTIONGOcreate trigger insert_SG2on SG2for insertasif(select StNum from inserted) not in (select StNum from Storehouse)and( (select GdNum from inserted)not in (select GdNum from Goods)beginprint 用户不存在或商品不存在！rollback transaction enddrop trigger insert_SG2create trigge

40、r delete_Goodson Goods for deleteasbegindelete from SG2 where GdNum = (select gdnum from deleted ) rollback transaction enddrop trigger delete_Goodscreate trigger delete_Usereson Useres for deleteasbegindelete from UG where UssName=(select UssName from deleted) rollback transaction enddrop trigger d

41、elete_Userescreate trigger delete_Supplieron Supplier for deleteasbegindelete from SG1 where SuNum=(select SuNum from deleted)rollback transaction end4.6 创建游标declare GdNum char (9),GdName char(20),GdPrice int,GdSpec char(20),GdDscribe char(20)declare AA cursor for select GdNum,GdName,GdPrice,GdSpec,

42、GdDscribe from Goodsopen AAfetch next FROM AA into GdNum,GdName,GdPrice,GdSpec,GdDscribewhile FETCH_STATUS=0begin print GdNamefetch next FROM AA into GdNum,GdName,GdPrice,GdSpec,GdDscribeend close AAdeallocate AAdeclareMgID char(9),MgName char(20),MgSex char(2),MgTel char(15)declare BB cursor for se

43、lect MgID,MgName,MgSex,MgTel from Manageropen BBfetch next FROM BB into MgID,MgName,MgSex,MgTel while FETCH_STATUS=0begin print MgNamefetch next FROM BB into MgID,MgName,MgSex,MgTel end close BBdeclaresunum char (9),suname char(20),sutel char(15),subank char(20)declare CC cursor for select sunum,sun

44、ame,sutel,subank from Supplieropen CCfetch next FROM CC into sunum,suname,sutel,subankwhile FETCH_STATUS=0begin print suname print sutelfetch next FROM CC into sunum,suname,sutel,subankendclose CCdeallocate CCdeclareSuNum char (9),GdNum char(9),SGFlow int declare DD cursor for select SuNum,GdNum,SGF

45、low from SG1open DDfetch next FROM CC into SuNum,GdNum,SGFlowwhile FETCH_STATUS=0begin print SuNum print SGFlowfetch next FROM CC into SuNum,GdNum,SGFlowendclose DDdeallocate DD*/if exists(select gdnum from goods where gdname=面) /*查询判断*/ select gdname from goods where gdname=面elseprint 查无此商品gouse myDB /*指示SQL 等待3s后查询 goods supplier sg1*/gowaitfor delay 00:00:01select *from goodsselect *from supplierselect *from sg1go4.7 创建用户，角色