数据库课程设计之电脑销售信息管理系统

1、数据库设计题目：学生姓名：电脑销售信息管理系统王雨丽学生学号：20091020117学生姓名：王玉玲学生学号：20091020118专业班级：信息与计算科学（1）班学院名称：数学与计量经济学院09级指导老师：姚兰2012年 2 月 23日目录1 绪论1.1 设计要求 - 4 -1.2 开发平台 - 4 -1.3 成员分工 - 4 -王雨丽：需求分析，逻辑机构设计，数据库实施 - 4 -2 需求分析 - 5 -2.1功能需求 - 5 -2.3 用户视图 - 8 -2.4初始数据库大小 - 8 -2.5数据库增长速度 - 8 -2.6记录查找的类型和平均数量 - 8 -2.7网络和共享访问需求 -

2、 8 -2.8性能要求 - 8 -2.9 安全性 - 8 -2.10 备份和恢复 - 9 -数据库设计完成时， 要对新数据库进行备份， 在日常应用与管理数据库时， 若有操作或 系统等的错误出现，会为数据库提供最原始的数据。 - 9 - - 9 -若数据库在使用过程出现事物内部故障、 系统故障、 介质故障或是计算机病毒侵入， 要对数据库进行恢复，一般方法为数据转储和登记日志文件。3 概念结构设计 - 10 -3.1 局部 E-R 图 - 10 -3.2 整体 E-R 图 - 12-4 逻辑结构设计 - 13-4.1将E-R图转化为关系模型 -13 -4.2关系模型的优化 -14-4.3基表设计

3、-14-4.4视图设计 -17 -建立采购单商品金额大于 10000 的采购单信息 - 17 -4.5索引设计 -17-4.6授权设计 -17-4.7存储过程设计 -17-1 利用存储过程，添加一条营业员的信息 - 17 -4.8触发器设计 -17-5 数据库实施 - 18 -5.1 数据库 - 18 -5.2基本表- 18 -5.3视图- 22-CREATE VIEW xianshiqi -22-AS- 22 -SELECT gID,gPRICE- 22-FROM goods- 22 -where gID=xianshiqi - 22 -建立采购单商品金额大于 10000 的采购单信息： -

4、 22 -5.4索引- 22- -23-5.5角色及授权 -23-5.6存储过程 -24-1 利用存储过程，添加一条营业员的信息 -24 -5.7触发器-24 -6数据库维护计划 -26 -7总结-25-1 绪论1.1 设计要求本系统名为“电脑销售信息管理系统” ，设计本系统时，要展示实体和实体 之间的联系，要全面考虑各实体之间的关系。建立数据库时要考虑数据模型的三要素： 数据结构、数据操作、完整性约束。 数据库设计过程中， 为使数据库满足现实需要及数据库稳定， 要严格按照数 据库设计步骤，一步一步，不得跨越。1.2 开发平台前台应用程序： C 语言后台数据库： Structured Quer

5、y Language1.3 成员分工王雨丽：需求分析，逻辑机构设计，数据库实施 王玉玲：概念结构设计，逻辑结构设计，数据库实施2需求分析2.1功能需求对商品、供应商、仓库、营业员、门店的有关信息进行管理。包括以下功能:1. 信息增删修改2. 信息查询3. 信息统计2.2数据需求表1.1数据项数据项含义说明别名数据类型长度取值范围取值含义giD商品编号CHAR6000000-999999编号gNAME名称VARCHAR10Not null名称gKINDS类别VARCHAR6Not null商品类别gUNIT单位INTNot null单位gPRICE单价INT0单品价格pID供应商编号CHAR60

6、00000-999999编号pNAME名称VARCHAR10Not null名称pACC账号CHAR1919位数字帐号pADD地址VARCHAR8Not null地址sID仓库编号CHAR6000000-999999编号sADD地址VARCHAR8Not null地址sLEAD负责人VARCHAR4Not null名称shID门店编号CHAR6000000-999999编号shNAME名称VARCHAR10Not null名称shADD地址VARCHAR8Not null地址aID营业员编号CHAR6000000-999999编号aNAME姓名VARCHAR4Not null名称aFEAT业绩

7、INT=0每天卖出商品的总价mID管理员编号CHAR6000000-999999编号mNAME姓名VARCHAR4Not null名称mFEAT业绩INT=0所管理门店每天卖 出商品的 总价shopperlD采购员编号CHAR6000000-999999编号shopperNA ME姓名VARCHAR4Not null名称shopperFE AT业绩INT=0母天采购 商品的总 价buy采购单号CHAR6000000-999999编号gID商品号CHAR6000000-999999编号参照商品编号blD采购员编号CHAR6000000-999999编号参照 采购员编 号bQUA数量INT=0采购

8、某种商品的数量bTIAME日期DATETIME00-00-00当天日期 格式如： 年-月-日stockID进货单号CHAR6000000-999999编号sID仓库编号CHAR6000000-999999编号参照 仓库编号shID门店编号CHAR6000000-999999编号参照 门店编号stockQUA数量INT=0进货某种 商品的数量stockTIME日期DATETIME00-00-00当天日期 格式如： 年-月-日sendID配送单号CHAR6000000-999999编号shID门店编号CHAR6000000-999999编号参照 门店编号sen dNAME顾客姓名VARCHAR10

9、Not null名称sen dQUA数量INT=0配送数量sen dTIME日期DATETIME00-00-00当天日期 格式如： 年-月-日sellQUA销售单号CHAR6000000-999999编号alD营业员编号CHAR6000000-999999编号参照 营业员编 号giD商品编号CHAR6000000-999999编号参照商品编号sellQUA数量INT=0某种商品 销售数量sellTIME日期DATETIME00-00-00当天日期 格式如： 年-月-日giD商品编号CHAR6000000-999999编号参照商品编号sID仓库编号CHAR6000000-999999编号参照 仓

10、库编号storageQU A库存量INT=0某种商品 采购的数 量-此种商 品进货拿 走的数量storageTIM E日期DATETIME00-00-00当天日期 格式如： 年-月-日safetystorag eQUA安全库存量INT=0某个仓库 的额定库 存量表1.2数据结构数据结构含义说明组成goods商品商品编号，名称，类别，单位，单价provider供应商供应商编号，名称，账号，地址store仓库仓库编号，地址，负责人shop门店门店编号，名称，地址shopper采购员采购员编号，姓名，业绩man ager管理员管理员编号，姓名，业绩assista nt营业员营业员编号，姓名，业绩bu

11、y采购采购单号，数量，日期，商品号，采购员号stock进货进货单号，数量，日期，仓库编号，门店编号send配送配送单号，数量，日期，门店编号，顾客姓名sell销售销售单号，数量，日期，营业员编号，商品编号storage存储库存量，日期，安全库存量，商品编号，仓库编号2.3用户视图表1.3用户视图表角色权限营业员? 查询商品的情况? 查询商品存储情况? 对销售数据进行查询更新统计采购员? 对供应商数据进行查询更新? 对采购数据进行查询更新统计顾客? 查询商品信息管理员? 维护（插入，更新和删除）营业员，采购员数据 ? 查询所有的数据2.4初始数据库大小初步设定，初始数据库大小为 OMB- 210

12、MB2.5数据库增长速度数据库增长速度初步设定为10MB2.6记录查找的类型和平均数量查找类型为一般的属性查找，初定为平均每次查找20个表，每次添加1000个数据2.7网络和共享访问需求数据库访问时，设定同一时间段内，管理者访问量不超过100人，一般工作人员访问量不超过1000人，顾客访问量不超过30000人。2.8性能要求实现信息管理自动化，提高销售事务工作效率，减少工作人员工作量，实现 自动统计功能，一共有效参考数据作为门店决策依据，实现管理自动化，信息化。2.9安全性系统应该有较高的安全性，不同级别的操作员有不同的安全级别。保证数据 不外泄，保证数据的可靠性，供管理员进行正确的决策，提高

13、运营效率。2.10 备份和恢复 数据库设计完成时，要对新数据库进行备份，在日常应用与管理数据库时， 若有操作或系统等的错误出现，会为数据库提供最原始的数据。若数据库在使用过程出现事物内部故障、 系统故障、 介质故障或是计算机病 毒侵入，要对数据库进行恢复，一般方法为数据转储和登记日志文件。3.1局部E-R图3概念结构设计分E-R图：仓库管理子系统:门店管理子系统:负责人地址库存量m数量n日期安全库存量存储进货业绩名称名称日期n姓名姓名地址1n1供应门店属于管理业绩地址m单价类别单位1采购数量日期销售配送m属于数量姓名日期数量nn日期业绩编号编号编号编号编号编号编号帐号单号单号单号单号3.2整体

14、E-R图名称制造商营业员管理员采购员- -|供应商仓库顾客商品4逻辑结构设计4.1将E-R图转化为关系模型实体供应商（供应商编号，名称，账号，地址）provider（pID,pNAME,pACC,pADD）商品（商品编号，名称，类别，单位，单价）goods（glD,gNAME,gKINDS,gUNIT,gPRICE）仓库（仓库编号，地址，负责人）store（sID,sADD,sLEAD）门店（门店编号，采购员，营业员，管理员，名称，地址）shop（shlD,shopper,assista nt,ma nager,shNAMJE shADD）采购员（采购员编号，姓名，业绩）shopper（sho

15、pperlD,shopperNAME,shopperFEAT）管理员（管理员编号，姓名，业绩）man ager（mlD,mNAME,mFEAT）营业员（营业员编号，姓名，业绩）assista nt（aJD,aNAME,aFEAT）实体间的联系采购（采购单号，商品号，采购员编号，数量，日期）buy（bD,glD,shopperlD，bQUA,bTIAME）进货（进货单号，仓库编号，门店编号，数量，日期）stock（stocklD,slD,shlD,stockQUA,stockTIME）配送（配送单号，门店编号，顾客姓名，数量，日期）sen d（sendID,shID,se ndNAME,e nd

16、QUA,se ndTIME）销售（销售单号，营业员编号，商品编号，数量，日期）sell(sellID,alD,glD,sellQUA,sellTIME)存储(商品编号，仓库编号，库存量，日期，安全库存量)storage(glD,slD,storageQUA,storageTIME,safetystorageQUA)关系模型的优化4.2关系模型的优化provider1(plD,pNAME)provider2(pNAME,pACC)provider3(pNAME,pADD) goods1(gJD,gKINDS,gUNIT,gPRICE)goods2(gID,gNAME)store(sID,sADD

17、,sLEAD)shop1(shNAME,shopper,assista nt,ma nager,shADD) shop2(shlD,shNAME) shopper1(shopperNAME,shopperFEAT) shopper2(shopperlD,shopperNAME)man ager1_(mNAME,mFEAT)man ager2(mID,mNAME)assista nta1(aNAME,aFEAT)assista nta2(aJD,aNAME)buy(bID,gID,shopperID，bQUA,bTIAME) stock(stocklD,slD,shlD,stockQUA,sto

18、ckTIME)sen d(sen dlD.shlD.se ndNAME,e ndQUA,se ndTIME) sell(seJUD,aID,gID,sellQUA,sellTIME) storagel(glD.slD.storageTIME) storage2(sID,storageQUA,safetystorageQUA)4.3基表设计表4.1字段名字段说明数据类型数据长度主/外 码可否 为空是否 多值备注glD商品编号CHAR6PK否否gNAME名称VARCHAR10否否gKINDS类别VARCHAR6否1否gUNIT单位INT否否gPRICE单价INT否否表4.2字段名字段说明数据类型数

19、据长度主/外码可否 为空是否 多值备注pID供应商编号CHAR6PK否否pNAME名称VARCHAR10否否pACC账号CHAR19否：否pADD地址VARCHAR8否否表4.3字段名字段说明数据类型数据长度主/外码可否 为空是否 多值备注sID仓库编号CHAR6PK否否sADD地址VARCHAR8否I否sLEAD负责人VARCHAR4否否表4.4字段名字段说明数据类型数据长度主/外码可否 为空是否 多值备注shlD门店编号CHAR6PK否否shNAME名称VARCHAR10否I否shADD地址VARCHAR8否否表4.5字段名字段说明数据类型数据长度主/外 码可否 为空是否 多值备注aID营

20、业员编号CHAR6PK否否aNAME姓名VARCHAR4否否aFEAT业绩INT否否表4.6字段名字段说明数据类型数据长度主/外 码可否 为空是否 多值备注mID管理员编号CHAR6PK否否mNAME姓名VARCHAR4否否mFEAT业绩INT否否表4.7字段名字段说明数据类型数据长度主/外码可否为空是否 多值备注shopperlD采购员编号CHAR6PK否否shopperNAME姓名VARCHAR4否否shopperFEAT业绩INT否否表4.8字段名字段说明数据类型数据长度主/外码可否 为空是否 多值备注buy米购单号CHAR6PK否否gID商品号CHAR6FK否否bID采购员编号CHAR

21、6FK否否bQUA数量INT否1否bTIAME日期DATETIME否否表4.9字段名字段说明数据类型数据长度主/外码可否 为空是否 多值备注stockID进货单号CHAR6PK否否sID仓库编号CHAR6FK否否shID门店编号CHAR6FK否否stockQUA数量INT否否stockTIME日期DATETIME否否表 4.10字段名字段说明数据类型数据长度主/外 码可否 为空是否 多值备注sendID配送单号CHAR6PK否否shID门店编号CHAR6FK否否sen dNAME顾客姓名VARCHAR10否否sen dQUA数量INT否：否sen dTIME日期DATETIME否否表 4.11

22、字段名字段说明数据类型数据长度主/外码可否为空是否 多值备注sellQUA销售单号CHAR6PK否否aID营业员编号CHAR6FK否否gID商品编号CHAR6FK否否sellQUA数量INT否否sellTIME日期CHAR6否否表 4.12字段名字段说明数据类型数据长度主/外码可否为空是否 多值备注sellTIME日期DATETIMEPK否否gID商品编号CHAR6FK否否sID仓库编号CHAR6FK否否storageQUA库存量INT否1否safetystorageQUA安全库存量INT否否4.4 视图设计 建立商品类别为显示器的视图，并标出价格 建立营业员业绩小于 1000 的营业员信息

23、建立采购单商品金额大于 10000 的采购单信息4.5 索引设计为商品goods建立索引为采购 buy 按照商品名称升序，数量降序建立唯一索引为存储storage按库存量降序建立唯一索引4.6 授权设计把对assistant表的所有权限授予给r1把商品的情况的查询 ,删除，存储情况的查询，删除权限授予给营业员r2把供应商数据的查询更新，采购数据的查询更新插入的权限授予采购员 r3 把商品表的查询的权限授予所有的用户4.7 存储过程设计1 利用存储过程，添加一条营业员的信息2 利用存储过程计算采购员编号为 010101 的采购金额之和4.8 触发器设计当删除一种电脑时， 说明该系统不再出售该种电

24、脑， 那么该电脑所对应的存储信 息也要删除当查询一种电脑是否存在于仓库中时，弹出信息。5 数据库实施5.1 数据库CREATE SCHEMAcomputerAUTHORIZATION MANAGER5.2 基本表建立商品表：CREATE TABLE goods(gID CHAR(6) PRIMARY KEY, gNAME VARCHAR(10), gKINDS VARCHAR(6), gUNIT INT, gPRICE INT, pID CHAR(6),FOREIGN KEY (pID) REFERENCES provider(pID);建立供应商表：CREATE TABLE provider

25、 (pID CHAR(6) PRIMARY KEY, pNAME VARCHAR(10), pACC CHAR(19), pADD VARCHAR(8)建立仓库表：CREATE TABLE store(sID CHAR(6) PRIMARY KEY,sADD VARCHAR(8),sLEAD VARCHAR(4)建立营业员表：CREATE TABLE assistant(aID CHAR(6) PRIMARY KEY, aNAME VARCHAR(4), aFEAT INT)建立门店表：CREATE TABLE shop(shID CHAR(6) PRIMARY KEY, shNAME VAR

26、CHAR(10), shADD VARCHAR(8)建立管理员表：CREATE TABLE manager(mID CHAR(6) PRIMARY KEY, mNAME VARCHAR(4), mFEAT INT)建立采购员表：CREATE TABLE shopper(shopperID CHAR(6) PRIMARY KEY, shopperNAME VARCHAR(4), shopperFEAT INT)实体之间的联系： 采购表：CREATE TABLE Buy(bID CHAR(6),gID CHAR(6),shopperID CHAR(6),bQUA CHAR(10),bTIME da

27、teTIME,PRIMARY KEY(bID,gID,shopperID)FOREIGN KEY (gID) REFERENCES goods(gID),FOREIGN KEY (shopperID) REFERENCES shopper(shopperID) )进货表：CREATE TABLE Stock(stockID CHAR(6),sID CHAR(6),shID CHAR(6),stockQUA CHAR(10),stockTIME DATETIME,PRIMARY KEY(stockID,sID,shID)FOREIGN KEY (sID) REFERENCES store(sID

28、),FOREIGN KEY (shID) REFERENCES shop(shID)销售表：CREATE TABLE Sell(sellID CHAR(6),aID CHAR(6), gID CHAR(6), sellQUA CHAR(10), sellTIME DATETIME, PRIMARY KEY(sellID,aID,gID)FOREIGN KEY (aID) REFERENCES assistant(aID), FOREIGN KEY (gID) REFERENCES goods(gID) )配送表：CREATE TABLE Send (sendID CHAR(6), shID C

29、HAR(6), sendNAME VARCHAR(10), sendQUA CHAR(10) sendTIME DATETIME, PRIMARY KEY(sendID,shID) FOREIGN KEY (shID) REFERENCES shop(shID) )存储表：CREATE TABLE Storage (sD CHAR(6), gID CHAR(6), storageQUA INT, storageTIME DATETIME, safetystorageQUA CHAR(10), PRIMARY KEY(sID,gID) FOREIGN KEY (sID) REFERENCES s

30、tore(sID),FOREIGN KEY (gID) REFERENCES goods(gID)5.3 视图建立商品类别为显示器的视图，并标出价格CREATE VIEW xianshiqiASSELECT gID,gPRICEFROM goods where gID=xianshiqi建立营业员业绩小于 1000 的营业员信息CREATE VIEW yejiASSELECT *FROM assistant where aFEAT=100005.4 索引为商品goods建立索引Create unique index goods_name on goods(gNAME)为采购 buy 按照商品名

31、称升序，数量降序建立唯一索引Create unique index storageQUA on storage(storageQUA desc)为存储storage按库存量降序建立唯一索引Create unique index buyQUA on buy(buyQUA desc)5.5 角色及授权1把对assistant表的所有权限授予给r1sp_addlogin manager,password001 sp_addrole r1Grant all privilegesOn assistantTo r12. 把商品的情况的查询 ,删除，存储情况的查询，删除权限授予给营业员sp_addrole

32、r2grant select,deleteOn goodsTo r2grant select,deleteOn storageTo r23. 把供应商数据的查询更新，采购数据的查询更新插入的权限授予采购员 sp_addrole r3Grant selectOn goodsTo r3Grant selectOn storageTo r34. 把商品表的查询的权限授予所有的用户r2r3Grant selectOn goodsTo public5.6 存储过程1 利用存储过程，添加一条营业员的信息Create procedure in_assistant(aID char(6)aNAME char(

33、4)aFEAT int)AsInsert into usersValues(aID,aNAME,aFEAT)Exec in_assistant 100005, 刘乐 ,02 利用存储过程计算采购员编号为 010101 的采购金额之和Create procedure in_out(shopperID char(6),shopperFEAT int,shopper_tot int output)As select shopper_tot=sum(shopperFEAT)From shopperWhere shopperID=shopperIDExec in_out 010101,shopper_t

34、ot output5.7 触发器1.goodsDelete所属表： goods功 能：当删除一种电脑时，说明该系统不再出售该种电脑，那么该电脑所 对应的存储信息也要删除源代码：CREATE TRIGGER goodsDelete ON goodsFOR DELETEASbegindeclare t intselect t=CgoodsID from deleteddelete from storage where goodsID=tend2.storage_update所属表： storage功能: 当查询一种电脑是否存在于仓库中时，弹出信息源代码Create trigger storage_updateO