《数据库系统应用大作业》课程设计报告商品管理系统数据库设计.doc

数据库系统应用大作业课程设计报告题 目： 商品管理系统数据库设计 院 （系）： 计算机与电子系 专业班级： 计算机科学与技术0804班 学生姓名： 学 号： _指导教师： 20 11 年 12 月 30 日至20 12 年 1 月 13 日 数据库管理系统 课程设计任务书一、设计（调查报告/论文）题目 商品管理系统数据库设计二、设计（调查报告/论文）主要内容 内容： 我本次做的课程设计题目是关于商品管理数据库系统，共设计了八个基本表，包括了商品信息表、供货厂商表、售货员表、工作表、会员表、商品交易表。商品信息表：主要标示了商品的各种属性，如商品编号、商品名称、商品单价、会员价等。售货员表：主要为其定义了售货员的工号、姓名、性别和年龄等属性。工作表：工作表即与售货员工作有关的各种属性，如售货员的工号、姓名、他们各自工作时间及月工资。会员表：商场都会有会员，这对商场的经营是有利的，因为会员所购买的商品较一般顾客便宜，这也是一种促销手段。这里我为会员设置了会员编号（即会员卡号）、会员姓名及会员办理时间三个属性。交易表：任何交易都是有记录的，这里交易也必须有其必备属性，所以我为交易设置了多个属性：商品编号、商品名称、商品交易时间、商品交易价格（因一般顾客和会员购买同一商品的价格可能不一样，这里就以“交易价格”来统称顾客售卖商品的价格）、售货员的工号、以及会员卡号（如果是会员，这里显示就是会员卡号，若是一般顾客，则显示“0”作为区分）基本功能与要求：1. 创建E-R图实体数5个，关系模式5个；2. 创建一个数据库，创建5个以上的表；3. 至少有3个插入语句，至少有3个修改语句，至少有3个删除语句；4. 10个以上的条件查询，5个以上的排序查询，3个以上的分组查询；5. 3个以上的连接查询，3个以上的计算查询，至少有5个视图；6. 报告的汉字字数不少于2000字。 三、原始资料1.数据库管理系统课程设计指导书2. 数据库系统设计课件四、要求的设计（调查/论文）成果1.课程设计报告2.课程设计作品五、进程安排序号课程设计内容学时分配备注1选题、需求分析1天2数据库设计2天3数据库表及相关约束、视图实现2天4数据库的存储过程、触发器实现2天5数据库后台功能测试2天6验收答辩、撰写课程设计报告1天合计10天六、主要参考资料1 顾兵.数据库技术与应用（SQL Server）.北京：清华大学出版社，2010.2 马晓梅.SQL Server实验指导.第3版.北京：清华大学出版社，2009.3 范立南等.SQL Server 2005实用教程.北京：清华大学出版社，2009.4 李丹.SQL Server 2005数据库管理与开发.北京：机械工业出版社，2010.指导教师（签名）： 20 年 月 日目 录1课程设计目的12课程设计题目描述和要求22.1课程设计题目描述22.2课程设计要求23、课程设计报告内容33.1 概念设计33.2 逻辑设计43.2.1关系模式43.2.2系统数据库表结构43.2.3系统数据库数据表63.3 物理设计93.3.1创建数据库93.3.2创建基本表103.3.3插入记录语句153.3.4修改数据语句163.3.5删除数据语句173.3.6查询语句183.3.6.1.条件查询183.3.6.2.排序查询213.3.6.3.连接查询243.3.7创建视图273.4 系统测试314、总结33参考文献341 课程设计目的随着现代化事业的快速发展，计算机成了现代社会不可缺少的信息存储和交互的重要工具，而数据库已经起到了极其关键的作用。计算机的运用现正在现代化国家的各类商店商场中普及。由于它能够准确记载和查阅有关商场经营活动的大量数据，帮助商场经营者掌握和分析营销情况，及时做出正确决策，并且有利于商店内部的财务、工资、人员、库存、销售情况等管理，因而大大提高了现代商场的管理水平，使交易更加便捷。开发此数据库管理系统最直接的目的就是为商场信息管理提供一种更加高效、实用的管理手段，为商场商品信息的存储、统计、分析、交流提供一种更加快捷、安全的信息平台。而其最终目的是通过对此商场管理信息系统运用可以减少大量的人工操作，以及在人工操作中由于人为因素而引起的数据错误，保证商品信息数据的安全性和完整性，使商场管理人员能够轻松，正确无误地完成各项工作。当然，这次课程设计还有一个重要的目的，就是巩固数据库应用有关知识，提高自己的动手能力和数据库综合运用的能力。2 课程设计题目描述和要求2.1 课程设计题目描述我本次做的课程设计题目是关于商品管理数据库系统，共设计了八个基本表，包括了商品信息表、供货厂商表、售货员表、工作表、会员表、商品交易表。商品信息表：主要标示了商品的各种属性，如商品编号、商品名称、商品单价、会员价等。售货员表：主要为其定义了售货员的工号、姓名、性别和年龄等属性。工作表：工作表即与售货员工作有关的各种属性，如售货员的工号、姓名、他们各自工作时间及月工资。会员表：商场都会有会员，这对商场的经营是有利的，因为会员所购买的商品较一般顾客便宜，这也是一种促销手段。这里我为会员设置了会员编号（即会员卡号）、会员姓名及会员办理时间三个属性。交易表：任何交易都是有记录的，这里交易也必须有其必备属性，所以我为交易设置了多个属性：商品编号、商品名称、商品交易时间、商品交易价格（因一般顾客和会员购买同一商品的价格可能不一样，这里就以“交易价格”来统称顾客售卖商品的价格）、售货员的工号、以及会员卡号（如果是会员，这里显示就是会员卡号，若是一般顾客，则显示“0”作为区分）2.2 课程设计要求本次课程设计的最低要求是所设计的E-R图实体数5个，关系模式5个，创建一个数据库，创建5个以上的表，至少有3个插入语句，至少有3个修改语句，至少有3个删除语句，10个以上的条件查询，5个以上的排序查询，3个以上的分组查询，3个以上的连接查询，3个以上的计算查询，至少有5个视图。对报告的汉字要求是不少于2000字。3 课程设计报告内容3.1 概念设计说明：为使我所设计的E-R图更加工整美观，我用Office自带组建Visio绘制，然后导出为PDF格式的文件，截图如下，以便于读者阅读。图3-1 E-R图3.2 逻辑设计3.2.1 关系模式商品（商品编号，商品名称，单价，会员价）售货员（工号，姓名，性别，年龄）工作（工号，姓名，工作时间，工资）会员（会员编号，会员姓名，办理时间）交易（商品编号，商品名称，交易时间，售出价格，工号，会员编号）3.2.2 系统数据库表结构表3-1 数据库表索引（Index）表名中文名Goods商品表Salesclerk售货员表Job工作表Member会员表Trade交易表表3-2 商品信息表(GoodsInfo)字段名字段类型长度主/外键字段值约束对应中文名GoodsIDChar4PNot null商品编号GoodsNameVarchar50Not null商品名称PriceFloatNot null单价MemberPriceFloatNot null会员价表3-3销售员表(SalesClerk)字段名字段类型长度主/外键字段值约束对应中文名ClerkIDchar3PNot null工号ClerkNameVarchar8Not null姓名ClerkSexChar2Not null性别ClerkAgeChar2Not null年龄表3-4 工作表(Job)字段名字段类型长度主/外键字段值约束对应中文名ClerkIDchar3PNot null工号ClerkNameVarchar8Not null姓名WorkTimeVarchar11Not null工作时间ClerkPayIntNot null工资表3-5 会员表(Member)字段名字段类型长度主/外键字段值约束对应中文名MemberIDChar11PNot null会员卡号MemberNameVarchar8Not null姓名RegDateVarchar20Not null办理时间表3-6交易表(Trade)字段名字段类型长度主/外键字段值约束对应中文名TradeTimeVarchar15PNot null交易时间GoodsIDChar4Not null商品编号GoodsNameVarchar50Not null商品名称ClerkIDChar3Not null工号SalePriceMoneyNot null出售价格MemberIDChar11Not null会员卡号3.2.3 系统数据库数据表表3-7 商品信息表GoodsIDGoodsNamePriceMemberPriceGoodsDateLastProviderName0001百事可乐2.502.302010.11.0112个月百事公司0002美年达2.402.202010.11.0112个月百事公司0003康师傅红烧牛肉面2.001.802010.12.016个月康师傅公司0004康师傅麻辣排骨面2.402.002010.12.016个月康师傅公司0005北京方便面0.600.502010.12.016个月南街村公司0006伊利盒装纯牛奶2.502.202011.01.013个月伊利公司0007蒙牛盒装纯牛奶2.001.802011.01.014个月蒙牛公司0008王中王火腿肠1.501.502011.01.013个月双汇公司0009汉堡火腿1.201.102011.01.014个月双汇公司0010青岛啤酒5.004.802010.10.016个月青岛啤酒公司0011雪花啤酒3.002.802010.10.016个月华润雪花啤酒公司0012心相印卷纸2.502.402010.10.013年恒安纸业0013乐百氏矿泉水0.900.902010.12.013个月乐百氏公司0014农夫山泉1.201.102010.12.012个月农夫山泉公司表3-8 销售员表ClerkIDClerkNameClerkSexClerkAge001李勇男27002张力男25003张向东男24004王芳女21005李晨女22006杨磊男26007赵青青女22008许强男19009朱小燕女20010郭婷婷女18011付杨男29012田妮女19013李雷男28014韩梅梅女27015周鑫男22表3-9 工作表ClerkIDClerkNameWorkTimeClerkPay001李勇8:00-12:002200002张力8:00-12:002000003张向东8:00-12:002500004王芳8:00-12:002500005李晨8:00-12:002400006杨磊12:00-18:002800007赵青青12:00-18:003000008许强12:00-18:002600009朱小燕12:00-18:002000010郭婷婷12:00-18:002600011付杨18:00-22:002500012田妮18:00-22:002500013李雷18:00-22:002800014韩梅梅18:00-22:002800015周鑫18:00-22:002400表3-10 会员表MemberIDMemberNameRegDate20081183001李宇春2008.03.1020081183002陈冠希2008.05.1920081183003曾轶可2009.01.0320081183004黄英2009.03.1820081183005李霄云2009.04.0320081183006江映蓉2009.05.2320081183007郁可唯2009.06.1520081183008刘惜君2009.07.1420081183009陈翔2010.05.1020081183010李炜2010.07.3020081183011刘心2010.08.2220081183012武艺2010.08.2920081183013谭杰希2010.09.1220081183014李刚2010.10.16表3-11 交易表:TradeTimeGoodsIDGoodsNameClerkIDSalePriceMemberID2011.01.010001百事可乐0012.30200811830102011.01.010002美年达0022.20200811830012011.01.010010青岛啤酒0034.80200811830042011.01.010010青岛啤酒0044.80200811830042011.01.010001百事可乐0052.30200811830042011.01.010002美年达0062.40000000000002011.01.010011雪花啤酒0073.00000000000002011.01.010004康师傅麻辣排骨面0082.00200811830072011.01.010005北京方便面0090.50200811830082011.01.010006伊利盒装纯牛奶0102.20200811830092011.01.020007蒙牛盒装纯牛奶0112.00000000000002011.01.020008王中王火腿肠0121.50200811830112011.01.020009汉堡火腿0131.10200811830122011.01.020010青岛啤酒0144.80200811830132011.01.020011雪花啤酒0153.00000000000002011.01.020012心相印卷纸0102.40200811830012011.01.020013乐百氏矿泉水0110.90200811830022011.01.020014农夫山泉0111.10200811830022011.01.020011雪花啤酒0023.00000000000002011.01.020001百事可乐0032.30200811830093.3 物理设计3.3.1 创建数据库create database goodson(name=stu,filename='D:SQLgoods_data.mdf',size=3MB,maxsize=500MB,filegrowth=10%)log on(name=stu_log,filename='D:SQLgoods_log.ldf',size=3MB,maxsize=500MB,filegrowth=10%)Go3.3.2 创建基本表use goodsgo3.3.2.1创建商品表create table goods(GoodsID char(4) Primary key,GoodsName varchar(50) NOT NULL,GoodsPrice float NOT NULL,MemberPrice float NOT NULL,GoodsDate char(10) NOT NULL,GoodsLast varchar(10) NOT NULL,ProviderName varchar(20) NOT NULL)Go3.3.2.2创建售货员表create table salesclerk(ClerkID char(3) Primary key,ClerkName varchar(8) NOT NULL,ClerkSex char(2) NOT NULL,ClerkAge char(2) NOT NULL)go3.3.2.3创建工作表create table job(ClerkID char(3) Primary key,ClerkName varchar(8) NOT NULL,WorkTime varchar(11) NOT NULL,ClerkPay int NOT NULL)go3.3.2.4创建会员表create table member(MemberID char(11) Primary key,MemberName varchar(8) NOT NULL,RegDate Datetime NOT NULL)go3.3.2.5创建交易表create table trade(TradeTime varchar(15) NOT NULL,goodsID char(4) NOT NULL,goodsName varchar(50) NOT NULL,ClerkID char(3) NOT NULL,SalePrice money NOT NULL,MemberID char(11) NOT NULL )go3.3.2.6插入表数据insert into goodsselect * from OPENROWSET('MICROSOFT.JET.OLEDB.4.0' ,'Excel 8.0;IMEX=1;HDR=YES;DATABASE=C:Documents and SettingsAdministrator桌面1.xls' ,Sheet1$)图3-2 商品信息insert into salesclerkselect * from OPENROWSET('MICROSOFT.JET.OLEDB.4.0' ,'Excel 8.0;IMEX=1;HDR=YES;DATABASE=C:Documents and SettingsAdministrator桌面3.xls' ,Sheet1$)图3-3 售货员信息insert into jobselect * from OPENROWSET('MICROSOFT.JET.OLEDB.4.0' ,'Excel 8.0;IMEX=1;HDR=YES;DATABASE=C:Documents and SettingsAdministrator桌面4.xls' ,Sheet1$)图3-4 工作信息insert into memberselect * from OPENROWSET('MICROSOFT.JET.OLEDB.4.0' ,'Excel 8.0;IMEX=1;HDR=YES;DATABASE=C:Documents and SettingsAdministrator桌面5.xls' ,Sheet1$)图3-5 会员信息insert into tradeselect * from OPENROWSET('MICROSOFT.JET.OLEDB.4.0' ,'Excel 8.0;IMEX=1;HDR=YES;DATABASE=C:Documents and SettingsAdministrator桌面6.xls' ,Sheet1$)图3-6 交易信息3.3.3 插入记录语句use goodsgoinsert into goods values('0016','福满多红烧牛肉面','2','1.9','2011.12.01','8个月','今麦郎公司')select * from goods图3-7 插入记录实例1use goodsgoinsert into jobvalues('016','张洋','18:00-22:00','3000')select * from job图3-8 插入记录实例2use goodsgoinsert into salesclerkvalues('016','张洋','男','24')select * from salesclerk图3-9 插入记录实例3use goodsgoinsert into supplyvalues('0016','福满多红烧牛肉面','L123','今麦郎公司','2010.01.01')select * from supply图3-10 插入记录实例43.3.4 修改数据语句use goodsgoupdate salesclerkset ClerkAge=23 where ClerkName='赵青青'select * from salesclerk where ClerkName='赵青青'图3-11 修改数据实例1use goodsgoupdate providerset ProviderPN='010-89578224' where ProviderName='华润雪花啤酒公司'select * from provider where ProviderName='华润雪花啤酒公司'图3-12 修改数据实例2use goodsgoupdate supplyset GoodsName='福满多麻辣牛肉面' where GoodsID='0016'select * from supply where GoodsID='0016'图3-13 修改数据实例3use goodsgoupdate goodsset GoodsName='福满多麻辣牛肉面' where GoodsID='0016'select * from goods where GoodsID='0016'图3-14 修改数据实例43.3.5 删除数据语句use goodsgodelete goodswhere GoodsName='乐百氏矿泉水'use goodsgodelete supplywhere GoodsName='乐百氏矿泉水'use goodsgodelete providerwhere ProviderName='乐百氏公司'3.3.6 查询语句3.3.6.1.条件查询use goodsgoselect * from goodswhere Price<2图3-15 条件查询实例1use goodsgoselect * from goodswhere Price<=2 and Price>=1图3-16 条件查询实例2use goodsgoselect * from goodswhere ProviderName='双汇公司'图3-17 条件查询实例3use goodsgoselect * from salesclerkwhere ClerkSex='男'图3-18 条件查询实例4use goodsgoselect * from salesclerkwhere ClerkSex='男' and ClerkAge>='25'图3-19 条件查询实例5use goodsgoselect * from jobwhere WorkTime='18:00-22:00'图3-20 条件查询实例6use goodsgoselect ClerkName as 售货员姓名,ClerkPay as 工资 from jobwhere ClerkPay>=2500图3-21 条件查询实例7use goodsgoselect TradeTime as 交易时间, GoodsName as 商品名城, SalePrice as 出售价格, MemberID as 会员编号 from tradewhere TradeTime='2011.01.01' and MemberID!=0图3-22 条件查询实例8use goodsgoselect * from providerwhere substring(ProviderADD,1,3)='内蒙古'图3-23 条件查询实例9use goodsgoselect * from memberwhere substring(RegDate,1,4)='2008'图3-24 条件查询实例103.3.6.2.排序查询use goodsgoselect GoodsName as 商品名称, Price as 单价from goodsorder by Price asc图3-25 排序查询实例1use goodsgoselect ClerkName as 职员姓名, ClerkPay as 工资from joborder by ClerkPay asc图3-26 排序查询实例2use goodsgoselect ClerkName as 职员姓名, ClerkAge as 职员年龄from salesclerkorder by ClerkAge desc图3-27 排序查询实例3use goodsgoselect ClerkID as 职员编号, ClerkName as 职员姓名, ClerkSex as 职员性别, ClerkAge as 年龄 from salesclerkorder by ClerkName asc图3-28 排序查询实例4use goodsgoselect ClerkID as 职员编号, TradeTime as 交易时间, GoodsName as 商品名称, SalePrice as 交易价格from tradeorder by ClerkID asc图3-29 排序查询实例53.3.6.3.连接查询use goodsgoselect distinct goods.GoodsName as 商品名称,goods.ProviderName as 公司名称,SupplyDate as 上架时间from supply inner join goodson goods.ProviderName=supply.ProviderName图3-30 连接查询实例1use goodsgoselect salesclerk.ClerkID as 职员编号, salesclerk.ClerkName as 职员姓名, ClerkPay as 工资, WorkTime as 工作时间from salesclerk inner join jobon salesclerk.ClerkID=job.ClerkIDand salesclerk.ClerkName=job.ClerkName图3-31 连接查询实例2use goodsgoselect member.MemberID as 会员卡号, member.MemberName as 会员姓名, trade.GoodsName as 商品名称, trade.SalePrice as 交易价格, trade.TradeTime as 交易时间from member inner join tradeonmember.MemberID=trade.MemberID图3-32 连接查询实例3use goodsgoselect goods.GoodsID as 商品编号, goods.GoodsName as 商品名称,from goods inner join locationon goods.GoodsID=location.GoodsID图3-33 连接查询实例43.3.7 创建视图create view goods_viewasselect GoodsID as 商品编号, GoodsName as 商品名称, Price as 单价, ProviderName as 生产商from goodsselect * from goods_view图3-34 创建视图实例1use goodsgocreate view member_tradeasselect member.MemberID as 会员卡号, member.MemberName as 会员姓名, trade.GoodsName as 商品名称, trade.SalePrice as 交易价格, trade.TradeTime as 交易时间from member inner join tradeon member.MemberID=trade.MemberIDselect * from member_trade图3-35 创建视图实例2use goodsgocreate view goods_locasselect goods.GoodsID as 商品编号, goods.GoodsName as 商品名称,from goods inner join locationon goods.GoodsID=location.GoodsIDselect * from goods_loc图3-36 创建视图实例3use goodsgocreate view goods_supplyasselect distinct goods.GoodsName as 商品名称, goods.ProviderName as 公司名称,SupplyDate as 上架时间from supply inner join goodson goods.ProviderName=supply.ProviderNameselect * from supply图3-37 创建视图实例4use goodsgocreate view Goods_tradeasselect distinct trade.GoodsName as 商品名称,trade.MemberID as 顾客编号,salesclerk.ClerkID as 职员编号,salesclerk.ClerkName as 职员姓名from trade inner join goods on goods.GoodsID=trade.GoodsIDinner join salesclerk on salesclerk.ClerkID=trade.ClerkIDselect * from Goods_trade图3-38 创建视图实例53.4 系统测试（1）select * from goods预期结果：显示所有商品信息实际结果：图3-39 测试结果1与预期结果一致，测试成