《《SQLserver数据库》课程设计范例讲解学习.docx》由会员分享，可在线阅读，更多相关《《SQLserver数据库》课程设计范例讲解学习.docx（28页珍藏版）》请在三一文库上搜索。
1、SQLs e r v er 数 据课程设计范例1 概述1.1 课题简介书店书目书种繁多，来源多样，购买者众多，图书信息、供应商信息、客户信息、销售信息庞大，不易管理。因此，很有必要创建一个小型书店管理系统，以便于书店对图书的管理。1.2 设计目的应用对数据库系统原理的理论学习，通过上机实践的方式将理论知识与实践更好的结合起来，巩固所学知识。数据库应用课程实践：实践和巩固在课堂教学中学习有关知识，熟练掌握对于给定结构的数据库的创建、基本操作、程序系统的建立和调试以及系统评价。数据库原理软件设计实践：实践和巩固在课堂教学中学习的关于关系数据库原理的有关知识和数据库系统的建立方法，熟练掌握对于给定实
2、际问题，为了建立一个关系数据库信息管理系统，必须得经过系统调研、需求分析、概念设计、逻辑设计、物理设计、系统调试、维护以及系统评价的一般过程，为毕业 设计打下基础。1.3 设计内容运用基于 E-R 模型的数据库设计方法和关系规范化理论做指导完成从系统的分析到设计直至系统的最终实现，开发小型书店管理系统，完成小型书店管理系统的全部功能。首先做好需求分析，并完成数据流图和数据字典。其次做概念分析，利用实体联系的方法将需求分析的用户需求抽象为信息结构，得到E-R 图。然后就是逻辑结构设计，将E-R 图转换为计算机系统所支持的逻辑模型2 需求分析2.1 功能分析首先，建立一些基本表（尽可能满足 3N）
3、,对大部分基本信息组合、存储；其次通过建立视图实现对冗余数据的有必要保留（查询并计算基本表属性得到新的作为视图属性）并实现对以下基本信息的显示。图书信息：图书名称、订购数量、订购时间、订购单价、金额、出版社名称、作者名称；供应商名称等；供应商信息：供应商名称、地址、电话，联系人；客户信息：客户编号、名称、年龄、性别、累计购书金额等；销售信息：时间、销售名称、数量、销售单价、客户编号、客户名称、金额等。在此基础上进行以下目标查询，由于有些查询常用且较复杂，为了简化其应用，所以将它们定义为存储过程。查询当月书店销售金额、营业金额；（存储过程）查询某种图书库存数量；（存储过程）查询当月销量最好的图书
4、信息；（存储过程）按供应商名称查询订购信息；（普通查询）查询购买次数超过3次的客户信息。（普通查询）接着根据需要建立触发器、存储过程、索引，实现对数据库的优化。最后，进行过程功能的验证。允许具有权限者对数据库进行数据更新和查询等合 法操作。数据流图用流程图来描述处理过程与数据的关系数据流图如下：图2,1数据流图数据字典用数据字典来描述数据数据字典如下：数据项:表2.1数据字典数据 项名含义 说明别 名数据长 度取值范围取值含义与其他 数据项 的逻辑 关系图书编 号唯一标识 每本书char80000000099999999根据入库时 问 先后编t有时与 客 户编号 作 为主码图书名 称唯一标识
5、每种书书 名varchar30有时与 供应商 组成主 码作者名 称每种书的 作者姓名char8出版社 名称每种书的 出版社名称char12供应商 名称唯一标识 供应商char12地址供应商的地 址varchar50电话供应商的电 话char700000009999999联系人通过联系人 书店与供应 商进行沟通char8订购时 问每种书的 订购时间datetime应与图 书称订购单 价从供应商处 进货每种书 的每本价格int与供应 商 名称 作为的 主码 直接关 联客户编 号唯一标识 客户char800000000099999999按购书先后 编号有时与 图 书编号 作 为主码客户名 称客户姓名
6、char8性别客户性别char2女或男年龄客户年龄int每年都要 增加一岁的销售时 问每本书的 销售时间datetime与图书 编号 和客户 编号 作为的 主码 直接关 联销售单 价每本书的 销售价格int数据结构:数据结构名 称含义说明组成每种书指具有相同书名的书的集 合的基本信息图书名称、作者名称、出版社 名称每本书指图书编号唯一标识的个体的 归属图书编号、图书名称供应商供应商的基本信息供应商名称、地址、电话、联 系人供应基表每种书在何处订购的具体情况图书名称、供应商名称、 订购单价、订购时间客户客户的基本信息客户编号、客户名称、性别、 年龄销售基表每本书被谁买的具体请况图书编号、客户编号
7、、销售单价、销售时间概念结构设计概念结构设计的方法设计概念结构通常有四类方法：自顶向下，自底向上，逐渐扩张以及混合 策略。本系统采用的是自底向上的方法。即首先定义全局的概念结构的框架， 然后逐步细化。根据自顶向下地进行需求分析然后再自底向上地进行概念设 计。概念结构设计的步骤第一步：抽象数据；第二步：设计局部E-R图；第三步：把各分E-R图综合成总体E-R图，消除各种冲突；第四步：对总体E-R图进行优化，消除冗余数据和冗余联系；抽象初步数据：（此处与优化后E-R图得由的实体联系属性很有可能不同）实体：图书、供应商、客户联系：图书订购关系、图书销售关系图书与供应商的关系是多对一的关系； 图书与客
8、户的关系是多对一的关系属性:图书的属性：图书编号、图书名称、出版社名称、作者名称、供应商名称;主码为图书编号供应商的属性：供应商名称、供应商地址、电话、联系人；主码为供应商名称客户的属性：客户编号、姓名、性别、年龄；主码为客户编号图书订购关系属性：供应商名称、图书编号、订购时间、订购单价；主码为供应冏名称和图书编号图书销售关系属性：客户编号、图书编号、销售时间、销售单价；主码为客户编号和图书编号分E-R图:图书E-R图:供应商E-R图:客户E-R图:初E-R图:图书编号（图书名莎版社名窗作者名掷（供应商多称优化后的总E-R图:c 书（每本书1售基工Qg户编工）Q售单打/N户后） u年J客户3书
9、名g） Q乍者名石）Q版社可分归属每种书N购m供应商逻辑设计阶段逻辑设计阶段：将系统E-R模型转换为关系模式，并对给出的关系模式进行关系规范化处理（尽量满足 3NF）,得到系统全部的关系模式，并标明主键、外键此部分一律采用英文命名。然后进行用户外模式的设计，针对中国用户,此处用中文命名。E R模式转换为以下关系模式:1)book(#bname, writer_name, press_name);主键： #bname2) ) everybook (# bno, #bname) ;主键：#bno外键：#bname3) supplier (#supplier_name, address, telep
10、hone, contact) ;主键：#supplier_name4) customer (#cno, cname, sex, age);主键： #cno5) book_order(#bname, #supplier_name, #order_time, order_price, order_count);主键： #bname, #supplier_name, #order_time6) book_sell(#bno, #cno, sell_time, sell_price);主键： #bno发现这六个关系模式3N 都满足。因为元组中都是不可分割的数据，且所有非主属性完全依赖其主码，且它的任何
11、一个非主属性都不传递于任何主关键字。用户外模式设计：基本信息视图的建立：1. 单本销售情况(图书编号，图书名称客户编号，客户名称，销售单价，销售时间)；2. 同种销售情况(书名，销售数量，销售金额)；3. 客户信息(客户编号，客户名称、性别，年龄，累计购书金额)；供应商信息(供应商名称，地址，电话，联系人)；4.5. 图书订购信息(图书名称，供应商名称，订购时间，订购数量，订购单价，订购金额，出版社名称，作者名称)；物理设计阶段物理设计阶段：为一个逻辑数据模型选取一个最适合应用环境的物理结构(存储结构与存取方法)，数据库的物理设计通常分为两步：A、确定数据库的物理结构；B、对物理结构进行时间和
12、空间效率评价。建立小型书店管理系统创建数据库book_shop_management- 创建小型书店管理系统create database book_shop_management on primary (name =book_shop_management ,filename =d:program filesmicrosoft sql servermssql.1mssqldata book_shop_management.mdf , size = 10mb , maxsize =unlimited , filegrowth = 10 %)log on (name =book_shop_man
13、agement_log,filename =d:program filesmicrosoft sqlservermssql.1mssqldatabook_shop_management_log.ldf,size = 1mb,maxsize =5mb,filegrowth =3%) go截图得：2013-201303. . . =七三。I 随要|cireate dotcdoasc Joook uh口p inetneigeincnton priniary (naine= LooRshopanagenient, f i lenane= 1 : prccjrani fi 1 pflXmtcTrosof
14、t. flFxrvrmHQl . 1 TrmsrildHt boo lt_s ho f_iftatiag eme nt, nd slae-ioirio.fiiawsise utLlirtilted,1 ilerouth-10-j)log qh(name = too 口 k一审 tio p_ii(ianagenie ntlog1 fti Lename=l proguani f I lesinLcrosatt sql seuvernissqL .shopsimax aire-S J： 国消息命令已成功完成.对各个基本表的建立(包括了各种完整性约束)建立表book-建立表bookcreate tab
15、le book (#bname varchar (30) primary key ,writer_name char (8),press_name char (12)建立表everybook- -建立表 everybookcreate table everybook (- bno char ( 8) primarykey ,- bname varchar (30) not nullconstraint fk_everybook_bname foreign key references book (#bname )建立表supplier- -建立表 suppliercreate table su
16、pplier (#supplier_name char (12) primary key ,address varchar ( 50 )not null,telephone char (7)not null,contact char ( 8) not null)建立表 customer- - 建立表 customercreate table customer(- cno char ( 8) primarykey ,cname char ( 8)not null,sex char ( 2),age int ,constraint ck_customer_sex check ( sex in(建立
17、表 book_order- - 建立表book_ordercreatetable book_order(- bnamevarchar ( 30 ),- supplier_name char ( 12 ),#order_timedatetime,order_countint notnull,order_priceint notnull,constraint pk_book_order primary key ( #bname , #supplier_name , #order_time )建立表 book_sell- - 建立表book_sellcreatetable book_sell(- b
18、no char ( 8) primary key- cno char ( 8) not null, sell_time datetime , sell_price int)建立视图 建立基本信息视图：建立单本销售情况视图- - 建立单本销售情况视图create view view_ 单本销售情况asselect book_sell . #bno as 图书编号, #bname asbook_sell . #cno as 客户编号, cname as 客户名称男 , 女 )图书名称sell_time as 销售时间, sell_price as 销售单价 from book_sell , cus
19、tomer , everybook where everybook . #bno =book_sell . #bno and customer . #cno =book_sell . #cno建立同种销售情况视图- - 建立同种销售情况视图create view view_ 同种销售情况asselect 图书名称as 书名 ,count ( 图书编号) as 销售数量,sum( 销售单价) as 销售金额from view_ 单本销售情况group by 图书名称建立客户信息视图- - 建立客户信息情况create view view_ 客户信息select customer . #cno a
20、s 客户编号,cname as 客户名称,sex as 性别 ,age as 年龄 ,( select sum ( sell_price ) from book_sell groupby #cno ) as 累计购书金额from book_sell , customerwhere book_sell . #cno =customer . #cno建立供应商视图- - 建立供应商视图create view view_ 供应商信息asselect #supplier_name as 供应商名称,addressas 地址,telephone as 电话 , contactas 联系人from sup
21、plier建立订购信息视图- - 建立订购信息视图create view view_ 图书订购信息asselect book_order. #bname as图书名称,#supplier_nameas 供应商名称,#order_timeas订购时间,order_priceas订购单价,order_countas订购数量,( order_price* order_count ) as订购金额press_nameas出版社名称,writer_nameas作者名称from book ,book_orderwhere book . #bname =book_order . #bname建立存储过程某种
22、图书库存查询存储过程create procedure sp_ 某种图书库存查询-p1 为要查询的图书名称p1 varchar ( 30 )asbeginselect 订购数 - 销售数量as 库存书名from ( select sum ( order_count ) as 订购数 , #bname as from book_ordergroup by #bname ) as x , view_ 同种销售情况where x . 书名 =p1 and view_ 同种销售情况. 书名 =p1endgo某段时间内的销售业绩查询存储过程 建立本月业绩查询存储过程 由于不知其它信息，此处将销售金额与营业
23、金额等同 营业金额=营业额 =成本 +利润create procedure sp_ 查询某段时间内的业绩-p1 为起始时间,p2 为结束时间p1 datetime, p2 datetimeasbeginselect sum ( 销售单价) as 销售金额from view_ 单本销售情况where 销售时间= p1 and 销售时间= p1and 销售时间=all( select count ( 图书编号)from view_ 单本销售情况where 销售时间= p1and 销售时间= p2group by 图书名称)endgo建立触发器(另外5 个表也建立相应曾删改触发器，不过此处从略，详情
24、见附件源代码)建立一个customer的操作审核表，并建立相关触发器，当向customer中插入、更新或删除记录时，生成一条操作记录插入到该操作审核表中，内容包括客户编号、客户名称、操作方式(插入/更新/删除)和操作时间 建一个操作审核表createtable customer_operate( 客户编号char ( 8)not null,客户名称char ( 8)notnull,操作方式char ( 4 )notnull,操作时间datetime not null) 建立相关触发器create trigger trigger_customer_insert on customerfor in
25、sert asbegindeclare p1 char ( 8), p2 char ( 8)set p1 =( select #cno from inserted )set p2 =( select cname from inserted )print insert:select * from insertedinsert into customer_operate values ( p1, p2, 插入 , getdate () endgocreate trigger trigger_customer_deleteon customerfor deleteasbegindeclare p1
26、char ( 8), p2 char ( 8)set p1 =( select #cno from deleted )set p2 =( select cname from deleted )print deleted:select * from deletedinsert into customer_operate values ( p1, p2, 删除 , getdate () endgocreate trigger trigger_customer_updateon customerfor updateasbegindeclare p1 char ( 8), p2 char ( 8)se
27、t p1 =( select #cno from deleted )set p2 =( select cname from deleted )printinserted:select* from insertedprintdeleted:select* from deletedinsert into customer_operate values ( p1, p2, 修改 , getdate () end go建立索引（由于数据库比较小，没有必要建立索引） 实现数据库（有附件源代码和该数据库源文件）过程功能验证#bnameWriter_namePress_name何以笙箫默顾漫晋江京版原来你还
28、在 这里辛夷坞起点出版烈火如歌明晓溪晋江出版微微T很顾漫阅读出版1book#bno#bname00000001何以笙箫默00000002何以笙箫默00000003原来你还在这里00000004烈火如歌00000005烈火如歌00000006烈火如歌00000007微微一笑很倾城everybook向基本表插入适量如下数据并显示基本表信息#bno#cnoSell_timeSell_price00000001000000012013-6-162100000003000000022013-6-251900000007000000022013-7-92000000004000000032013-7-92
29、300000005000000042013-7-1918book_sellcustomer#cnocnamesexAge00000001吕鹏女1800000002孙鲁鲁女1800000003王肖雨女1900000004邵丽君女19Book_order#bname#supplier_nameOrder_timeOrder_countOrder_price何以笙箫默大旗2013-6-10212原来你还在这里大旗2013-6-20110烈火如歌红方2013-7-2110烈火如歌长城2013-7-329微微一笑很倾城高原2013-7-3111Suppilier#suppileraddresstelep
30、honecontact大旗上海1573234王平红方上海1734272李景长城江苏1564323张艳丽高原郑州1242356殷风注：此处插入数据应当 book在everybook 之前插入数据（外键）截图得:2013-201303. .LQueryl. sql*2013-201303. . . nt=s.oul. zql摘要selectf combookselectrambook orderselect selectfrom frombock seiLcustomerselectfroiDeverybook_l结果上消息ttbnainewffita_ra piesname1L何以堇箫默顾漫晋江
31、出版社,2烈火的歌明瞪溪普江出版社2撒撒一笑很领城藤漫阅读出醺社4原来你还在这里辛夷鸠起点出版社ttbnanne#suppli5r_niarnBttorifer_tinneofder_pice order_co用普通查询完成目标查询(两次查询同时显示)订购超3次客户信息查询-订购超次客户信息查询select * from view_ 客户信息where 客户编号=any(select #cno from book_sellgroup by #cnohaving count (*) 3)按供应商名称查询订购信息-按供应商名称查询订购信息select * from view_ 图书订购信息order by供应商名称截图得:信息A.2013-201303. .LQuerrT. sl* 2013-201303. . .nt.sotl. sqlselect * from view,jjhere 客尸编号=sr. 71select #cuo fton tiook selljr口叫 Jay cnosaving coan-(T) 3一技供应商名称查询订购值息select丫工点班国书订购信息一 一 一 一 一 一口结果、消息客户籍名客F名伸 年里计购苍金融国书名物期商名盯购时间订购单tT败数一订购自出版社名称作者名航1