数据库课程设计---库存物资管理系统后台数据库

大型数据库课程设计设计报告题目库存物资管理系统后台数据库学号学生姓名指导教师提交时间20131122目录库存物资管理系统后台数据库1第1章进行需求分析，编写数据字典111系统功能要求设计112模块设计113数据字典2第2章面向对象分析和设计4第3章逻辑结构设计531类和对象向关系模式转换6第4章数据库物理结构设计741存取方法设计742存储结构设计743物理设计7第5章数据完整性设计1151主键及索引11表名1152完整性约束的说明1153CHECK约束1254DEFAULT默认值1255触发器设计12第6章数据库视图的设计16第7章存储过程设计19总结28参考文献28库存物资管理系统后台数据库第1章进行需求分析，编写数据字典11系统功能要求设计1数据表以及数据量1）有一个存放商品的仓库，每天都有商品出库和入库。2）商品有1000多种，每种商品都有名称、生产厂家、型号、规格等。3）出入库时必须填写出入库单据，单据包括商品名称、生产厂家、型号、规格、数量、日期、时间、入库单位（或出库单位）名称、送货（或提货）人姓名。2设计要求1进行需求分析，编写数据字典。2设计ER图。3设计出入库单据的录入。4实现按商品名称、出入库日期的查询。5实现分别按日、月和年对出入库商品数量的统计。3主要功能1商品管理增加商品、修改商品、删除商品、浏览商品2库存管理通过触发器实现商品的入库，同时完成对库存表的更新通过触发器实现商品的出库，同时完成对库存表的更新实现按商品名称查询库存数量、入库数量和出库数量实现按入库日期查询入库数量、出库数量实现分别按日、月和年对入库商品数量的统计实现分别按日、月和年对入库商品数量的统计12模块设计库存物资管理大体可以分为3大块，如下图所示首先是商品入库模块，该模块主要是描述把采购回来的商品，分类别的放置到指定的仓库中去，然后是商品出库模块，该模块主要描述从指定的仓库中拿出商品，最后是商品库存模块，这个模块主要是记录商品的库存数量。13数据字典表商品表仓库表库存表入库表出库表商品表字段名数据类型是否可以为空ISPKISFK商品编号CHAR5NOTNULLYESNO商品名称VARCHAR50NOTNULLNONO生产厂家VARCHAR50NOTNULLNONO型号VARCHAR50NOTNULLNONO规格VARCHAR20NOTNULLNONO仓库表字段名数据类型是否可以为空ISPKISFK仓库号CHAR10NOTNULLYESNO仓库名称VARCHAR50NOTNULLNONO库存表字段名数据类型是否可以为空ISPKISFK商品编号CHAR5NOTNULLYESYES仓库号CHAR10NOTNULLYESYES商品入库模块库存物资管理商品出库模块商品库存模块库存数量INTNOTNULLNONO入库表字段名数据类型是否可以为空ISPKISFK入库号CHAR10NOTNULLYESNO商品编号CHAR5NOTNULLNOYES仓库号CHAR10NOTNULLNOYES入库数量INTNOTNULLNONO入库日期SMALLDATETIMENOTNULLNONO入库单位名称VARCHAR50NULLNONO送货人姓名VARCHAR50NOTNULLNONO性别CHAR2NULLNONO出库表字段名数据类型是否可以为空ISPKISFK出库号CHAR10NOTNULLYESNO商品编号CHAR5NOTNULLNOYES仓库号CHAR10NOTNULLNOYES出库数量INTNOTNULLNONO出库日期SMALLDATETIMENOTNULLNONO出库单位名称VARCHAR50NULLNONO提货人姓名VARCHAR50NOTNULLNONO性别CHAR2NULLNONO第2章面向对象分析和设计类和对象设计如下商品商品编号CHAR商品名称VARCHAR生产厂家VARCHAR规格VARCHAR型号VARCHAR添加删除查询修改仓库仓库号CHAR仓库名称VARCHAR添加修改删除查询库存商品编号商品仓库号仓库库存数量INT查询修改添加删除出库出库号INT仓库号仓库商品编号商品商品名称商品出库数量INT出库日期SMALLDATETIME出库单位名称单位送货人姓名人性别CHAR添加修改删除查询入库入库号INT仓库号仓库商品编号商品商品名称商品入库数量INT入库日期SMALLDATETIME入库单位名称单位送货人姓名人性别CHAR添加删除查询修改第3章逻辑结构设计分ER图库存关系NM入库关系NM出库关系NM商品型号规格生产厂家商品名称商品编号仓库仓库号仓库名称商品库存仓库商品仓库入库商品仓库出库总ER图NNNMMM31类和对象向关系模式转换关系模式如下商品（商品编号，商品名称，生产厂家，型号，规格）商品编号为主键仓库（仓库号，仓库名称）仓库号为主键库存（商品编号，仓库号，库存数量）（商品编号，仓库号）为主键，同时也分别为外键入库（入库号，商品编号，仓库号，入库数量，入库日期，入库时间，入库单位名称，送货人姓名，性别）（入库号）为主键，（商品编号，仓库号）为外键。出库（出库号，商品编号，仓库号，出库数量，出库日期，出库时间，出库单位名称，提货人姓名，性别）（出库号）为主键，（商品编号，仓库号）为外键。商品型号规格仓库仓库号仓库名称商品编号商品名称生产厂家出库入库入库日期入库数量入库号送货人姓名出库日期出库数量出库号提货人姓名入库库存数量性别入库单位名称出库单位名称性别第4章数据库物理结构设计41存取方法设计对于库存物资管理系统来说，为了提高某些属性如商品编号，仓库号、入库号，入库日期，入库数量等的查询速度，可以选择聚簇存取的方法，即把这些属性上具有相同值的元组集中放在连续的物理块上。这样在查询某种商品就会大大提高查询速度。因此，该系统中选择聚簇存取方法。42存储结构设计库存物资管理系统是一个大型复杂的计算机网络信息系统，采用基于浏览器/服务器（B/S），客户端/服务器（C/S）混合的应用体系结构来建设库存物资管理系统。数据库管理系统采用MICROSOFT公司推出的SQLSERVER2005或以上版本，并用SQL进行数据库的建立和数据库中数据的维护和查询。43物理设计实现该设计的环境为WINDOWSXPPROFESSIONALMSSQLSERVER2005或以上版本。1、创建MATERIAL数据库CREATEDATABASEMATERIALONPRIMARYNAMEMATERIAL,FILENAMECPROGRAFILESMICROSOFTSQLSERVERMSSQL2MSSQLDATAMATERIALMDF,SIZE3MB,MAXSIZEUNLIMITED,FILEGROWTH1MBLOGONNAMEMATERIAL_LOG,FILENAMECPROGRAMFILESMICROSOFTSQLSERVERMSSQL2MSSQLDATAMATERIAL_LOGLDF,SIZE1MB,FILEGROWTH102、创建商品表CREATETABLE商品表商品编号CHAR5NOTNULLPRIMARYKEY,商品名称VARCHAR50NOTNULL,生产厂家VARCHAR50NOTNULL,型号VARCHAR20NOTNULL,规格VARCHAR20NOTNULL3、创建仓库表CREATETABLE仓库表仓库号CHAR10NOTNULLPRIMARYKEY,仓库名称VARCHAR50NOTNULL,4、创建库存表CREATETABLE库存表商品编号CHAR5NOTNULL,仓库号CHAR10NOTNULL,库存数量INTNOTNULL,PRIMARYKEY商品编号,仓库号,FOREIGNKEY商品编号REFERENCES商品表商品编号,FOREIGNKEY仓库号REFERENCES仓库表仓库号5、创建入库表CREATETABLE入库表入库号CHAR10NOTNULLPRIMARYKEY,商品编号CHAR5NOTNULL,仓库号CHAR10NOTNULL,FOREIGNKEY商品编号REFERENCES商品表商品编号,FOREIGNKEY仓库号REFERENCES仓库表仓库号,入库数量INTNOTNULL,入库日期SMALLDATETIMENOTNULL,入库单位名称VARCHAR50NULL,送货人姓名VARCHAR10NOTNULL,性别CHAR2DEFAULT男CHECK性别IN男女6、创建出库表CREATETABLE出库表出库号CHAR10NOTNULLPRIMARYKEY,商品编号CHAR5NOTNULL,仓库号CHAR10NOTNULL,FOREIGNKEY商品编号REFERENCES商品表商品编号,FOREIGNKEY仓库号REFERENCES仓库表仓库号,出库数量INTNOTNULL,出库日期SMALLDATETIMENOTNULL,出库单位名称VARCHAR50NULL,提货人姓名VARCHAR10NOTNULL,性别CHAR2DEFAULT男CHECK性别IN男女第5章数据完整性设计51主键及索引表名主键建立索引商品表商品编号CREATECLUSTEREDINDEX商品表ON商品表商品编号仓库表仓库号CREATECLUSTEREDINDEX仓库表ON仓库表仓库号入库表入库号CREATECLUSTEREDINDEX入库表ON入库表入库号出库表出库号CREATECLUSTEREDINDEX出库表ON出库表出库号52完整性约束的说明1、商品表的商品编号1000111000之间（便于查询的使用），商品名称不能取空；该仓库的商品型号统一为DA0001DA1000格式。1）USEMATERIAL/定义商品表唯一性的约束/GOALTERTABLE商品表ADDUNIQUE商品名称,生产厂家,型号,规格2）USEMATERIAL/定义型号的完整性约束条件商品型号统一为DA0001DA1000格式/GOALTERTABLE商品表ADDCONSTRAINTCS1CHECK型号LIKEDA0109090953CHECK约束在商品表中将型号进行CHECK约束CHECK型号LIKEDA01090909在入库表和出库表中将性别进行CHECK约束CHECK性别IN男女54DEFAULT默认值在入库表和出库表中性别默认值设为男。DEFAULT男55触发器设计1、通过触发器实现商品的入库，同时完成对库存表的更新（1）入库USEMATERIALGOIFEXISTSSELECTFROMSYSOBJECTSWHERENAMERUKUINSERTDROPPROCEDURERUKUINSERTGOCREATEPROCEDURERUKUINSERTRUKUHAOCHAR10,SHANGPINBIANHAOCHAR5,CANGKUHAOCHAR10,RUKUSHULIANGINT,RUKURIQISMALLDATETIME,RUKUDANWEIVARCHAR50,SONGHUORENNAMEVARCHAR50,SEXCHAR2ASIFEXISTSSELECTFROM入库表WHERE入库号RUKUHAOPRINT该商品已经存在，不能再次插入ELSEBEGININSERTINTO入库表VALUESRUKUHAO,SHANGPINBIANHAO,CANGKUHAO,RUKUSHULIANG,RUKURIQI,RUKUDANWEI,SONGHUORENNAME,SEXPRINT插入成功END（2）更新USEMATERIALGOCREATETRIGGERRKB_INTOON入库表AFTERUPDATEASBEGINDECLAREACHAR5,BCHAR10,DINTSELECTA商品编号FROMINSERTEDSELECTB仓库号FROMINSERTEDSELECTD入库数量FROMINSERTEDUPDATE库存表SET库存数量库存数量DWHERE商品编号AAND仓库号BEND2、通过触发器实现商品的出库，同时完成对库存表的更新（1）出库USEMATERIALGOIFEXISTSSELECTFROMSYSOBJECTSWHERENAMECHUKUINSERTDROPPROCEDURECHUKUINSERTGOCREATEPROCEDURECHUKUINSERTCHUKUHAOCHAR10,SHANGPINBIANHAOCHAR5,CANGKUHAOCHAR10,CHUKUSHULIANGINT,CHUKURIQISMALLDATETIME,CHUKUDANWEIVARCHAR50,TIHUORENNAMEVARCHAR50,SEXCHAR2ASIFEXISTSSELECTFROM出库表WHERE出库号CHUKUHAOPRINT该商品已经存在，不能再次插入ELSEBEGININSERTINTO出库表VALUESCHUKUHAO,SHANGPINBIANHAO,CANGKUHAO,CHUKUSHULIANG,CHUKURIQI,CHUKUDANWEI,TIHUORENNAME,SEXPRINT插入成功END（2）更新USEMATERIALGOCREATETRIGGERCKB_INTOON出库表AFTERUPDATEASBEGINDECLAREAACHAR10,BBCHAR5,DDINTSELECTAA仓库号FROMDELETEDSELECTBB商品编号FROMDELETEDSELECTDD出库数量FROMINSERTEDUPDATE库存表SET库存数量库存数量DDWHERE商品编号BBAND仓库号AAEND第6章数据库视图的设计1、建立商品表视图USEMATERIALGOCREATEVIEW商品表视图ASSELECTFROM商品表2、创建商品库存视图来浏览库存中相同编号商品的信息，其中包括商品编号、商品名称和库存数量。USEMATERIALGOCREATEVIEW商品库存视图商品编号,商品名称,库存数量ASSELECT库存表商品编号,商品表商品名称,SUM库存表库存数量FROM商品表,库存表WHERE商品表商品编号库存表商品编号GROUPBY库存表商品编号,商品表商品名称3、创建商品入库视图USEMATERIALGOCREATEVIEW商品入库视图ASSELECT商品表商品编号,商品名称,生产厂家,型号,规格,入库号,仓库号,入库数量,入库日期FROM商品表,入库表WHERE商品表商品编号入库表商品编号4、创建商品出库视图USEMATERIALGOCREATEVIEW商品出库视图ASSELECT商品表商品编号,商品名称,生产厂家,型号,规格,出库号,仓库号,出库数量,出库日期FROM商品表,出库表WHERE商品表商品编号出库表商品编号5、创建商品入库仓库视图USEMATERIALGOCREATEVIEW商品入库仓库视图ASSELECT商品表商品编号,商品名称,入库表仓库号,仓库名称FROM商品表,入库表,仓库表WHERE商品表商品编号入库表商品编号AND入库表仓库号仓库表仓库号6、创建商品出库仓库视图USEMATERIALGOCREATEVIEW商品出库仓库视图ASSELECT商品表商品编号,商品名称,出库表仓库号,仓库名称FROM商品表,出库表,仓库表WHERE商品表商品编号出库表商品编号AND出库表仓库号仓库表仓库号第7章存储过程设计1、实现商品表的插入USEMATERIALGOIFEXISTSSELECTFROMSYSOBJECTSWHERENAMESHANGPININSERTDROPPROCEDURESHANGPININSERTGOCREATEPROCEDURESHANGPININSERTBIANHAOCHAR5,NAMEVARCHAR50,CHANGJIAVARCHAR50,XINGHAOVARCHAR50,GUIGEVARCHAR20ASIFEXISTSSELECTFROM商品表WHERE商品编号BIANHAOPRINT该商品已经存在，不能再次插入ELSEBEGININSERTINTO商品表VALUESBIANHAO,NAME,CHANGJIA,XINGHAO,GUIGEPRINT插入成功ENDEXECSHANGPININSERT11111,毛巾,纺织有限公司,DA0111,3070CM2、实现商品表的删除USEMATERIALGOIFEXISTSSELECTFROMSYSOBJECTSWHERENAMESHANGPINDELETEDROPPROCEDURESHANGPINDELETEGOCREATEPROCEDURESHANGPINDELETEBIANHAOCHAR5ASIFEXISTSSELECT商品编号FROM商品表WHERE商品编号BIANHAOBEGINDELETEFROM商品表WHERE商品编号BIANHAOPRINT删除成功，该商品已被删除ENDELSEPRINT该商品不存在,不能删除EXECSHANGPINDELETE122003、按商品名称查询库存数量USEMATERIALGOIFEXISTSSELECTFROMSYSOBJECTSWHERENAMESHANGPIN_INFO1DROPPROCEDURESHANGPIN_INFO1GOCREATEPROCEDURESHANGPIN_INFO1SHANGPINNAMEVARCHAR50ASSELECTA商品编号,商品名称,B库存数量FROM商品表AJOIN库存表BONA商品编号B商品编号WHERE商品名称SHANGPINNAMEEXECSHANGPIN_INFO1LG冰箱4、按商品名称查询入库数量USEMATERIALGOIFEXISTSSELECTFROMSYSOBJECTSWHERENAMERUKU_INFO10DROPPROCEDURERUKU_INFO10GOCREATEPROCEDURERUKU_INFO10SHANGPINNAMEVARCHAR50ASSELECT商品表商品编号,商品名称,入库数量FROM商品表JOIN入库表ON商品表商品编号入库表商品编号WHERE商品名称SHANGPINNAMEEXECRUKU_INFO10ACER宏基笔记本电脑5、按商品名称查询出库数量USEMATERIALGOIFEXISTSSELECTFROMSYSOBJECTSWHERENAMECHUKU_INFO11DROPPROCEDURECHUKU_INFO11GOCREATEPROCEDURECHUKU_INFO11SHANGPINNAMEVARCHAR50ASSELECT商品表商品编号,商品名称,出库数量FROM商品表JOIN出库表ON商品表商品编号出库表商品编号WHERE商品名称SHANGPINNAMEEXECCHUKU_INFO11MSI微星电脑主板6、按入库日期查询入库数量USEMATERIALGOIFEXISTSSELECTFROMSYSOBJECTSWHERENAMERUKU_INFO2DROPPROCEDURERUKU_INFO2GOCREATEPROCEDURERUKU_INFO2RUKUDATESMALLDATETIMEASSELECT商品表商品编号,商品名称,入库日期,入库数量FROM商品表JOIN入库表ON商品表商品编号入库表商品编号WHERE入库日期RUKUDATEEXECRUKU_INFO22012350007、按出库日期查询出库数量USEMATERIALGOIFEXISTSSELECTFROMSYSOBJECTSWHERENAMECHUKU_INFO3DROPPROCEDURECHUKU_INFO3GOCREATEPROCEDURECHUKU_INFO3CHUKUDATESMALLDATETIMEASSELECT商品表商品编号,商品名称,出库日期,出库数量FROM商品表JOIN出库表ON商品表商品编号出库表商品编号WHERE出库日期CHUKUDATEEXECCHUKU_INFO32010/2/16000008、按年对入库商品数量的统计USEMATERIAL/按年对入库商品数量的统计/GOIFEXISTSSELECTFROMSYSOBJECTSWHERENAMERUKU_INFO4DROPPROCEDURERUKU_INFO4GOCREATEPROCEDURERUKU_INFO4START_DATESMALLDATETIME,END_DATESMALLDATETIMEASSELECT商品名称,SUM入库数量AS总入库数量FROM商品表JOIN入库表ON商品表商品编号入库表商品编号WHERE入库日期BETWEENSTART_DATEANDEND_DATEGROUPBY商品名称EXECRUKU_INFO4201011,201012319、按年对出库商品数量的统计USEMATERIAL/按年对出库商品数量的统计/GOIFEXISTSSELECTFROMSYSOBJECTSWHERENAMECHUKU_INFO5DROPPROCEDURECHUKU_INFO5GOCREATEPROCEDURECHUKU_INFO5START_DATESMALLDATETIME,END_DATESMALLDATETIMEASSELECT商品名称,SUM出库数量AS出总库数量FROM商品表JOIN出库表ON商品表商品编号出库表商品编号WHERE出库日期BETWEENSTART_DATEANDEND_DATEGROUPBY商品名称EXECCHUKU_INFO5201211,2012123110、按月对入库商品数量的统计USEMATERIAL/按月对入库商品数量的统计/GOIFEXISTSSELECTFROMSYSOBJECTSWHERENAMERUKU_INFO6DROPPROCEDURERUKU_INFO6GOCREATEPROCEDURERUKU_INFO6START_DATESMALLDATETIME,END_DATESMALLDATETIMEASSELECT商品名称,SUM入库数量AS总入库数量FROM商品表JOIN入库表ON商品表商品编号入库表商品编号WHERE入库日期BETWEENSTART_DATEANDEND_DATEGROUPBY商品名称EXECRUKU_INFO6201011,201013111、按月对出出库商品数量的统计USEMATERIAL/按月对出库商品数量的统计/GOIFEXISTSSELECTFROMSYSOBJECTSWHERENAMECHUKU_INFO7DROPPROCEDURECHUKU_INFO7GOCREATEPROCEDURECHUKU_INFO7START_DATESMALLDATETIME,END_DATESMALLDATETIMEASSELECT商品名称,SUM出库数量AS出总库数量FROM商品表JOIN出库表ON商品表商品编号出库表商品编号WHERE出库日期BETWEENSTART_DATEANDEND_DATEGROUPBY商品名称EXECCHUKU_INFO7201271,201273112、按日对入库商品数量的统计USEMATERIAL/按日对入库商品数量的统计/GOIFEXISTSSELECTFROMSYSOBJECTSWHERENAMERUKU_INFO8DROPPROCEDURERUKU_INFO8GOCREATEPROCEDURERUKU_INFO8START_DATESMALLDATETIME,END_DATESMALLDATETIMEASSELECT商品名称,SUM入库数量AS总入库数量FROM商品表JOIN入库表ON商品表商品编号入库表商品编号WHERE入库日期BETWEENSTART_DATEANDEND_DATEGROUPBY商品名称13、按日对出库商品数量的统计USEMATERIAL/按日对出库商品数量的统计/GOIFEXISTSSELECTFROMSYSOBJECTSWHERENAMECHUKU_INFO9DROPPROCEDURECHUKU_INFO9GOCREATEPROCEDURECHUKU_INFO9START_DATESMALLDATETIME,END_DATESMALLDATETIMEASSEL