SQLserver数据库课程设计范例

1、1概述1.1课题简介书店书目书种繁多，来源多样，购买者众多，图书信息、供应商信息、客户信息、销售 信息庞大，不易管理。因此，很有必要创建一个小型书店管理系统，以便于书店对图书的管 理。1.2设计目的应用对数据库系统原理的理论学习，通过上机实践的方式将理论知识与实践更好的结合 起来，巩固所学知识。数据库应用 课程实践：实践和巩固在课堂教学中学习有关知识，熟练掌握对于给定结 构的数据库的创建、基本操作、程序系统的建立和调试以及系统评价。数据库原理软件设计实践：实践和巩固在课堂教学中学习的关于关系数据库原理的有 关知识和数据库系统的建立方法，熟练掌握对于给定实际问题，为了建立一个关系数据库信息管理系

2、统，必须得经过系统调研、 需求分析、概念设计、逻辑设计、物理设计、系统调试、 维护以及系统评价的一般过程，为毕业设计打下基础。1.3设计内容运用基于E-R模型的数据库设计方法和关系规范化理论做指导完成从系统的分析到设 计直至系统的最终实现，开发小型书店管理系统，完成小型书店管理系统的全部功能。首先做好需求分析，并完成数据流图和数据字典。其次做概念分析，利用实体联系的方法将需求分析的用户需求抽象为信息结构，得到E-R 图。然后就是逻辑结构设计，将 E-R图转换为计算机系统所支持的逻辑模型25/236/30/20192需求分析2.1功能分析首先，建立一些基本表（尽可能满足3N）,对大部分基本信息组

3、合、存储；其次通过建立视图实现对冗余数据的有必要保留（查询并计算基本表属性得到新的作为视图属性）并实现对以下基本信息的显示。图书信息：图书名称、订购数量、订购时间、订购单价、金额、出版社名称、作者名称； 供应商名称等；供应商信息：供应商名称、地址、电话，联系人； 客户信息：客户编号、名称、年龄、性别、累计购书金额等； 销售信息：时间、销售名称、数量、销售单价、客户编号、客户名称、金额等。在此基础上进行以下目标查询，由于有些查询常用且较复杂，为了简化其应用，所以将它们定义为存储过程。查询当月书店销售金额、营业金额；（存储过程）查询某种图书库存数量；（存储过程） 查询当月销量最好的图书信息；（存储

4、过程）按供应商名称查询订购信息；（普通查询）查询购买次数超过 3次的客户信息。（普通查询）接着根据需要建立触发器、存储过程、索引，实现对数据库的优化。最后，进行过程 功能的验证。允许具有权限者对数据库进行数据更新和查询等合法操作。数据流图用流程图来描述处理过程与数据的关系 数据流图如下：图2,1数据流图数据字典用数据字典来描述数据数据字典如下：数据项：表2.1数据字典数据 项名含义 说明别 名数据 类型长度取值范围取值含义与其他数据 项的逻辑关系图书编号唯一标识 每本书char80000000099999999根据入库时间 先后编号有时与客 户编号作 为主码图书名称唯一标识 每种书书 名var

5、char30有时与供应 商组成主码作者名称每种书的 作者姓名char8出版社名 称每种书的 出版社名称char12供应商名 称唯一标识 供应商char12地址供应商的地址:varchar50电话供应商的电话char700000009999999联系人通过联系人 书店与供应 商进行沟通char8订购时间每种书的 订购时间datetime应与图书称 与供应商 名称作为的主码 直接关联订购单价从供应商处 进货每种书 的每本价格int客户编号唯一标识 客户char800000000099999999按购书先后 编号有时与图 书编号作 为主码客户名称客户姓名char8性别客户性别char2女或男:年龄客

6、户年龄int每年都要 增加一岁的销售时间每本书的销售时间datetime与图书编号和客户编号销售单价每本书的 销售价格int作为的主码 直接关联数据结构:数据结构名称含义说明组成每种书指具有相同书名的书的集 合的基本信息图书名称、作者名称、出版社名称每本书指图书编号唯一标识的个体的归属图书编号、图书名称:供应商供应商的基本信息供应商名称、地址、电话、联系人供应基表每种书在何处订购的具体情况图书名称、供应商名称、订购单价、订购时间客户客户的基本信息客户编号、客户名称、性别、年龄销售基表每本书被谁买的具体请况图书编号、客户编号、 销售单价、销售时间概念结构设计概念结构设计的方法设计概念结构通常有四

7、类方法：自顶向下，自底向上，逐渐扩张以及混合策略。本系统采用的是自底向上的方法。即首先定义全局的概念结构的框架，然后逐步细化。根据自顶向下地进行需求分析然后再自底向上地进行概念设计。概念结构设计的步骤第一步：抽象数据；第二步：设计局部 E-R图；第三步：把各分 E-R图综合成总体E-R图，消除各种冲突; 第四步：对总体 E-R图进行优化，消除冗余数据和冗余联系;抽象初步数据：（此处与优化后 E-R图得出的实体联系属性很有可能不同）实体：图书、供应商、客户 联系：图书订购关系、图书销售关系 图书与供应商的关系是多对一的关系；图书与客户的关系是多对一的关系。属性：图书的属性：图书编号、图书名称、出

8、版社名称、作者名称、供应商名称; 主码为图书编号供应商的属性：供应商名称、供应商地址、电话、联系人；主码为供应商名称客户的属性：客户编号、姓名、性别、年龄；主码为客户编号图书订购关系属性：供应商名称、图书编号、订购时间、订购单价；主码为供应商名称和图书编号图书销售关系属性：客户编号、图书编号、销售时间、销售单价； 主码为客户编号和图书编号分E-R图:供应商E-R图:初E-R图:图书编图书名出版社尔作者名供应商名称供应商图书编图书客户编图书编订购时间销售时订购单价图书订购图书销销售单供应商地供应商电话联系年龄客户性别客户编姓名优化后的总E-R图:1N供应商名称系人逻辑设计阶段逻辑设计阶段：将系统

9、 E-R模型转换为关系模式，并对给出的关系模式进行关系规范 化处理（尽量满足 3NF），得到系统全部的关系模式，并标明主键、外键此部分一律采用英 文命名。然后进行用户外模式的设计，针对中国用户，此处用中文命名。将E R模式转换为以下关系模式：1) book(#b name, writer_ name, press_ name);主键：#bname2. ) everybook (# bno, #bn ame);主键：#bno夕卜键：#bname3) supplier (#supplier_ name, address, telepho ne, con tact);主键：#supplier_nam

10、e4) customer (#c no, cn ame, sex, age);主键：#cno5) book_order(#b name, #supplier_ name, #order_time, order_price, order_co un t);主键：#bn ame, #supplier_ name, #order_time6) book_sell(#b no, #cno, sell_time, sell_price);主键：#bno发现这六个关系模式 3N都满足。因为元组中都是不可分割的数据，且所有非主属性完 全依赖其主码，且它的任何一个非主属性都不传递于任何主关键字。用户外模式设计

11、:基本信息视图的建立：1. 单本销售情况（图书编号，图书名称客户编号，客户名称，销售单价，销售时间）2. 同种销售情况（书名，销售数量，销售金额）；3. 客户信息（客户编号，客户名称、性别，年龄，累计购书金额）；4. 供应商信息（供应商名称，地址，电话，联系人）；5. 图书订购信息（图书名称，供应商名称，订购时间，订购数量，订购单价，订购金额, 出版社名称，作者名称）；物理设计阶段（存储结构与存取B、对物理结构进行物理设计阶段：为一个逻辑数据模型选取一个最适合应用环境的物理结构 方法），数据库的物理设计通常分为两步： A、确定数据库的物理结构; 时间和空间效率评价。建立小型书店管理系统 仓U建

12、数据库 book_shop_management-创建小型书店管理系统create database book_shop_ma nageme nt on primary (n ame =book_shop_ma nageme nt,file name=d:program filesmicrosoft sql servermssql.1mssqldatabook_shop_ma nageme nt.mdf,size =10mb,maxsize =un limited ,filegrowth =10 %)log on (n ame =book_shop_ma nageme nt_log,file

13、name=d:program filesmicrosoft sqlservermssql.1mssqldatabook_shop_ma nageme nt_log .ldf size =1mb,maxsize =5mb,filegrowth =3%)go2013-201303, . .sql s要|creat d&tafease fo0ok_Shop_managem已titan pr imauy (name- 1 bo k_3 ho p_rnanageme nt1 *f ilEnanE= 1 d: program f ilesHiicrosof t sql servermssql. 1mssq

14、l databoohop_inanagieifient*size=10mb,maxsize-unl iinitedf i 1 e gr owt h= 10 % )log on1 bo k_s hon t _ 1 g11f ilEname= 1 d: prograiM filesMicrosoft, sql servermssql. 1 mssql data toook_shop si Ee=linb, inaxsiz e=5irib.消息命令已戚功元成.建立表book-建立表bookcreate table book (#bname varchar (30) primary key ,writ

15、er_namechar ( 8),press_namechar (12)建立表 everybook-建立表 everybookcreate table everybook (#bno char ( 8) primarykey ,#bname varchar (30) not nullconstraintfk_everybook_bnameforeign key references book ( #bname ) 建立表 supplier-建立表 suppliercreate table supplier (key#supplier_name char (12) primary address

16、 varchar ( 50 )notnull,telephonechar ( 7)not null,con tactchar ( 8) not n ull)建立表 customer-建立表 customercreate table customer (#cno char ( 8) primary key cname char ( 8)not null,sex char (2),age int ,check ( sex in(男,女)con stra intck_customer_sex建立表 book_order-建立表 book_ordercreatetablebook_order(#bna

17、me varchar ( 30),#supplier_ namechar (12),#order_timedatetime ,order_cou ntint not n ull,order_priceint not n ull,con stra intpk_book_orderprimarykey(#bname , #supplier_name,#order_time )建立表book_sell-建立表 book sellcreatetable book_sell(#bnochar(8) primarykey#cnochar(8) notn ull,sell_timedatetime5sell

18、_price int)建立视图建立基本信息视图:建立单本销售情况视图-建立单本销售情况视图create view view_ 单本销售情况asselect book_sell. #bno as 图书编号,#bname as 图书名称book_sell . #cno as 客户编号，cname as 客户名称, sell_time as销售时间，sell_price as销售单价from book_sell , customer , everybookwhere everybook . #bno =book_sell . #bnoand customer . #cno =book_sell .

19、#cno建立同种销售情况视图-建立同种销售情况视图createview view_ 同种销售情况asselect图书名称 as书名，cou nt （图书编号）as销售数量, sum （销售单价）as销售金额from view_ 单本销售情况 group by图书名称建立客户信息视图-建立客户信息情况create view view_ 客户信息select customer . #cno as 客户编号, cname as 客户名称,sex as 性别，age as 年龄,(select sum ( sell_price ) from book_sell group by #cno ) as累计

20、购书金额from book_sell , customerwhere book_sell . #cno =customer . #cno建立供应商视图-建立供应商视图create view view_ 供应商信息asselect#supplier_nameas 供应商名称address as 地址,telephoneas 电话,con tactas联系人from supplier建立订购信息视图-建立订购信息视图createviewview._图书订购信息asselect book_order.#bname as图书名称,#supplier_ nameas供应商名称，#order_timeas

21、订购时间，order_priceas订购单价，order_cou ntas订购数量，(order_price* order_count) as订购金额press_ nameas岀版社名称，writer_ nameas作者名称from book , 1book_orderwhere book .#bn ame = book_order.#bname、亠 / -八 4、建立存储过程某种图书库存查询存储过程create procedure sp_某种图书库存查询-p1为要查询的图书名称p1 varchar ( 30 )asbeginselect 订购数-销售数量as库存from (selectsum

22、 (order_count) as 订购数,#bname as 书名from book_ordergroup by #bname ) as x , view_ 同种销售情况where x .书名=p1 and view_ 同种销售情况.书名=p1 endgo某段时间内的销售业绩查询存储过程- 建立本月业绩查询存储过程-由于不知其它信息，此处将销售金额与营业金额等同-营业金额=营业额=成本+利润create procedure sp_ 查询某段时间内的业绩-p1为起始时间，p2为结束时间p1 datetime , p2 datetimeas begin select sum （销售单价）as销售

23、金额from view_ 单本销售情况 where 销售时间=p1 and 销售时间=p2endgo某段时间内的销售最好图书信息查询存储过程- 建立某段时间内销售最好图书信息查询存储过程create procedure sp_查询某段时间内销售最好的图书信息 p1 datetime , p2 datetimeasbeginselect #bname as 图书名称, writer_nameas 作者名称,press_nameas 岀版社名称from （ select 图书名称as书名， cou nt （图书编号）as销售数量 from view_ 单本销售情况 where 销售时间=p1 an

24、d销售时间=p2group by 图书名称） as x , bookwhere #bname =书名and 销售数量=all（ selectcount （图书编号）from view_ 单本销售情况where 销售时间=p1and销售时间=p2group by图书名称）endgo建立触发器（另外5个表也建立相应曾删改触发器，不过此处从略，详情见附件源代码）建立一个customer的操作审核表，并建立相关触发器，当向customer中插入、更新或删除记录时，生成一条操作记录插入到该操作审核表中，内容包括客户编号、客户名称、操作方 式（插入/更新/删除）和操作时间-建一个操作审核表create t

25、able customer_operate（客户编号 char （ 8）not null, 客户名称 char （ 8）not null, 操作方式 char （ 4）not null,操作时间 datetimenotnull)-建立相关触发器create trigger trigger_customer_ inserton customerforin sertasbegindeclare p1 char (8), p2 char (8)set p1 =( select #cno from inserted )set p2 =( selectcnamefrom inserted)pri nt

26、in sert:select * from insertedinsertinto customer_operatevalues ( p1, p2,插入,getdate ()endgocreate trigger trigger_customer_deleteon customerfor deleteasbegindeclare p1 char (8), p2 char (8)set p1 =( select #cno from deleted )set p2 =( selectcnamefrom deleted )pri nt deleted:select * from deletedinse

27、rtinto customer_operatevalues ( p1, p2,删除,getdate ()endgocreate trigger trigger_customer_updateon customerfor updateasbegindeclare p1 char (8), p2 char (8)set p1 =( select #cno from deleted )set p2 =( selectcnamefrom deleted )pri ntin serted:select* from insertedpri ntdeleted:select* from deletedins

28、ertinto customer_operatevalues ( p1, p2,修改,getdate ()endgo建立索引（由于数据库比较小，没有必要建立索引）实现数据库（有附件源代码和该数据库源文件） 过程功能验证向基本表插入适量如下数据并显示基本表信息#bno#bn ame00000001何以笙箫默00000002何以笙箫默00000003原来你还在这里00000004烈火如歌00000005烈火如歌00000006烈火如歌00000007微微一笑很倾城everybook#bn ameWriter namePress n ame何以笙箫默顾漫晋江出版社原来你还在这里辛夷坞起点出版社烈火如

29、歌明晓溪晋江出版社微微一笑很倾城顾漫阅读出版社bookbook_sell#bno#c noSell_timeSell_price00000001000000012013-6-162100000003000000022013-6-251900000007000000022013-7-92000000004000000032013-7-92300000005000000042013-7-1918customer#c nocn amesexAge00000001吕鹏女1800000002孙鲁鲁女1800000003王肖雨女1900000004邵丽君女19Book_order#bn ame#suppl

30、ier_ nameOrder_timeOrder_cou ntOrder_price何以笙箫默大旗2013-6-10212原来你还在这里大旗2013-6-20110烈火如歌红方2013-7-2110烈火如歌长城2013-7-329微微一笑很倾城高原2013-7-3111Suppilier#suppileraddresstelepho necon tact大旗上海1573234王平红方上海1734272李景长城江苏1564323张艳丽高原郑州1242356殷风注：此处插入数据应当book在everybook之前插入数据（外键）截图得：hook2ni 3-201303 LOwyLTsql* 201

31、3-201303. nt.ioul sq； 插要 select * fromselectf roinbook orderselectf rambook sellselect * fromcustoiuier结果国消息select fromeverybookttbnamevyritefnamepressname1商虐謂默帧漫晉江出版社2明昵谟晋江岀版社3徽微一笑很脚顾漫阅读岀版社4原来你还在这里辛夷坞起点出版社ttbnamettsupplier_name ttorder_timeoider_priceordler_count1何谑萧默2013-06-20 00：0&.00.0002122埶火如欧

32、长城2013-07-03 OOtOQOQOOO293烈火如歌红方2013-07-02 00:00:00.0001w4锻锻_笑很倾城高原2013-07-03 00:00:00.0001115原来你还在遠里2013-07-02 00:0000.000110itbnotlcnosslLbmeselljprict2013-06 00:00:00000 212013-07-09 oo：oo：oaooa 191? 00000001000000012D0000003000000023OOOOOOtM000000034000000050000000420m7-Q9 00:00:0a000 232013.07-

33、13 00.00:00.000 18n2= J72rl, FU har ivr iw - ur W _ wittcnocname sexage100000001昌鹏鹏女19300000003昌鹏女19400000004吕鹏吉、19ttbnottbname100000001何以笙箫默230000000200000003何以笙箫默 原来你还400000004輕火如歌500000005烈火如敢GOOOOOOOB賀火如欣700000007徴徽一笑.ttsupplier_name addiesstelephone corttacl1挨城江苏1564323张艳丽2大旗上海1573

34、234 王平3高原郑州1242356 殿凤4红方上海1734272 李最消息国錯兰J區消息詰果视图显示基本信息 用显示视图基本信息select*fromview_单本销售情况select*fromview_供应商信息select*fromview_客户信息select*fromview_同种销售信息select*fromview_图书订购信息截图得：2013-201303 Luryl. sl* 2013-201303. ntsoul iql 幡要selectfromselectf roimse丄已ut.fromselectfromselectfrom叮单本销售情况 L供应商信首 念户信息L同种

35、销售情况 view_g书订购信息viewviewtin二结杲也消息图书编号團书名肺客尸编寻客户可销售时间销售单愉100000001何以笙箫默000000012013-06-25 OOtOO:OOLOOO21200000003原来你还破里00000002201309 OttOQOOLDOO19300000004烈火勺哪！00000003王肖雨2013-07-09 DOtOO:OQOOO23彳00000005鯉火勺瞰00000004邵丽君2013-07-19 00100:00.00018500000007徴徽一笑很倾城0DQ00DQ2孙鲁曾2013-079 OQOO: 00.00020供应翩名聃地

36、址电话联累人1| 江苏 1564323张艳丽2上海 15旳234壬平?高原郑州 12423564紅方上海 1734272李杲客戶輪号粤户名 性年.累计嗚书金额100000001呂鹏宝10 212 00000002孙鲁鲁女1039300000003王肖雨京192340000000召颐君女1916书名销售数消售金颠11何旦笔箫飮212烈火如歌2413徽徽一笑1204原来你还119團书名称供应商名.订购时间订嗚单.订购数.订购金.出版壮名称作罟名聃1大愤2013-06-20 000:00.00021224晋江出版社顾漫2烈火如荻2013 07-03 000:00.0002916晋江出版社明蹩溪3烈火

37、如歐红方201307-02 00.00:00.00011010曹江出版社明瞪溪4微微一笑高原2013070(3 0000:00.00011111圍读出版社顾漫5原来你还大眉20130702 皿皿000011010起点岀版社辛夷塩执行存储过程完成目标查询某种图书库存查询exec sp_某种图书库存查询烈火如歌本月业绩查询exec sp_ 查询某段时间内的业绩2013-7-1, 2013-7-31本月销售最好图书信息查询,2013-7-31exec sp_查询某段时间内销售最好的图书信息2013-7-1截图得:表 一 dbo. book_order / 20132D1303. - - IftKer

38、jS. sql* 2013H3D1303. . . nt_scral. sL芯矿臥棄种图书库存查询烈火如歌1exec ap査询某段时闾内的业轨-7-12013-7-311音好匚邛_查询某段时间內销售最奸的图书信息13-7-1/2013-7-31二结果d消息1库存-111111 1LBHWHTI11卡苜售金额G11的书名称二若“岀JK社名称 |明晓溪晋江出版社用普通查询完成目标查询（两次查询同时显示）订购超3次客户信息查询订购超次客户信息查询select* fromview_ 客户信息where客户编号=an y(select#c nofrom book_sellgroupby #cnohav ingcou nt(*)3)按供应商名称查询订购信息按供应商名称查询订购信息selectorder* from view_ 图书订购信息by供应商名称截图得:通结果由消和霍尸輪导客尸名 性 年 累计!书金顿團书名称供应商名订购时间订购单订购数订购金出版社名称作者名称1长城2013-06-20 00:00:00.000321&晋江出版社2冋笙箫猷2013-061000:00:00,00012224晋江出版社3凜来你还在遠團大赫2013-07-D2 00:00:00.00010110起点出牘壮4总