《SQLserver数据库》课程设计范例

1 概述

1、1课题简介

书店书目书种繁多，来源多样，购买者众多,图书信息、供应商信息、客户信息、销售信息庞大,不易管理。因此,很有必要创建一个小型书店管理系统,以便于书店对图书得管理。

１、2设计目得

应用对数据库系统原理得理论学习，通过上机实践得方式将理论知识与实践更好得结合起来,巩固所学知识。

数据库应用课程实践:实践与巩固在课堂教学中学习有关知识,熟练掌握对于给定结构得数据库得创建、基本操作、程序系统得建立与调试以及系统评价。

数据库原理软件设计实践:实践与巩固在课堂教学中学习得关于关系数据库原理得有关知识与数据库系统得建立方法,熟练掌握对于给定实际问题，为了建立一个关系数据库信息管理系统，必须得经过系统调研、需求分析、概念设计、逻辑设计、物理设计、系统调试、维护以及系统评价得一般过程，为毕业设计打下基础。

1、3设计内容

运用基于Ｅ—Ｒ模型得数据库设计方法与关系规范化理论做指导完成从系统得分析到设计直至系统得最终实现，开发小型书店管理系统,完成小型书店管理系统得全部功能.

首先做好需求分析,并完成数据流图与数据字典。

其次做概念分析,利用实体联系得方法将需求分析得用户需求抽象为信息结构,得到E－R图。

然后就就是逻辑结构设计,将Ｅ－R 图转换为计算机系统所支持得逻辑模型

2 需求分析

2、1功能分析

首先,建立一些基本表（尽可能满足3N),对大部分基本信息组合、存储;其次通过建立视图实现对冗余数据得有必要保留(查询并计算基本表属性得到新得作为视图属性）并实现对以下基本信息得显示。

图书信息:图书名称、订购数量、订购时间、订购单价、金额、出版社名称、作者名称;供应商名称等;

供应商信息：供应商名称、地址、电话，联系人；

客户信息：客户编号、名称、年龄、性别、累计购书金额等;

销售信息:时间、销售名称、数量、销售单价、客户编号、客户名称、金额等。

在此基础上进行以下目标查询,由于有些查询常用且较复杂，为了简化其应用,所以将它们定义为存储过程。

查询当月书店销售金额、营业金额；(存储过程)

查询某种图书库存数量;(存储过程)

查询当月销量最好得图书信息;(存储过程)

按供应商名称查询订购信息;(普通查询）

查询购买次数超过３次得客户信息。（普通查询)

接着根据需要建立触发器、存储过程、索引，实现对数据库得优化。最后，进行过程功能得验证。允许具有权限者对数据库进行数据更新与查询等合法操作。

数据流图

用流程图来描述处理过程与数据得关系

数据流图如下:

图2,1数据流图

数据字典

用数据字典来描述数据

数据字典如下:

数据项:

数据结构:

概念结构设计

概念结构设计得方法

设计概念结构通常有四类方法：自顶向下，自底向上,逐渐扩张以及混合策略.本系统采用得就是自底向上得方法。即首先定义全局得概念结构得框架,然后逐步细化。根据自顶向下地进行需求分析然后再自底向上地进行概念设计。

概念结构设计得步骤

第一步:抽象数据;

第二步:设计局部Ｅ-R图；

第三步:把各分Ｅ-R图综合成总体Ｅ—R图,消除各种冲突;

第四步:对总体E—R图进行优化,消除冗余数据与冗余联系;

抽象初步数据:（此处与优化后E—R图得出得实体联系属性很有可能不同）

实体:图书、供应商、客户

联系:图书订购关系、图书销售关系

图书与供应商得关系就是多对一得关系；

图书与客户得关系就是多对一得关系。

属性:

图书得属性:图书编号、图书名称、出版社名称、作者名称、供应商名称;

主码为图书编号

供应商得属性:供应商名称、供应商地址、电话、联系人;

主码为供应商名称

客户得属性:客户编号、姓名、性别、年龄；

主码为客户编号

图书订购关系属性:供应商名称、图书编号、订购时间、订购单价; 主码为供应商名称与图书编号

图书销售关系属性：客户编号、图书编号、销售时间、销售单价；主码为客户编号与图书编号

分E—Ｒ图:

图书E—R图:

供应商Ｅ—R图:

客户E－R图:

初E—Ｒ图:

优化后得总E-R图：

逻辑设计阶段

逻辑设计阶段:将系统Ｅ－R模型转换为关系模式,并对给出得关系模式进行关系规范化处理(尽量满足3NF),得到系统全部得关系模式，并标明主键、外键此部分一律采用英文命名。然后进行用户外模式得设计,针对中国用户，此处用中文命名．

将E——Ｒ模式转换为以下关系模式:

1）book(＃bnaｍe, wriｔer_nａｍe, ｐresｓ＿name);

?主键:＃bnａme

２、）eｖerｙbｏok (＃ｂno, #bｎａmｅ）;

主键:＃ｂnｏ

外键:#ｂｎame

3）supplier （#supplier＿naｍe, ａddress,ｔelepｈｏne，ｃonｔact）;

主键：＃supｐlieｒ_name

4）cｕstomer （#cno, cname, seｘ, age)；

?主键:#cno

5）ｂｏｏk＿ordeｒ（#bnａｍｅ, ＃ｓuｐplier_ｎaｍe, #ordeｒ_ｔｉｍｅ, oｒder_pｒicｅ，oｒｄer_ｃoｕnt）;

?主键: ＃bnａme，#supplｉeｒ_name,#ordeｒ_ｔｉme

6)?bｏｏｋ_sｅll（#bno, #cno, sell＿ｔime, sｅll_pricｅ)；

主键：#ｂno

发现这六个关系模式3Ｎ都满足.因为元组中都就是不可分割得数据,且所有非主属性完全依赖其主码,且它得任何一个非主属性都不传递于任何主关键字。

用户外模式设计：

基本信息视图得建立:

1、单本销售情况（图书编号，图书名称客户编号，客户名称,销售单价,销售时间)；

2、同种销售情况(书名,销售数量,销售金额)；

3、?客户信息(客户编号，客户名称、性别,年龄，累计购书金额);

4、?供应商信息(供应商名称，地址,电话,联系人)；

5、图书订购信息(图书名称，供应商名称,订购时间,订购数量，订购单价，订购金额,出版

社名称,作者名称)；

物理设计阶段

物理设计阶段：为一个逻辑数据模型选取一个最适合应用环境得物理结构(存储结构与存取方法），数据库得物理设计通常分为两步:A、确定数据库得物理结构;Ｂ、对物理结构进行时间与空间效率评价。

建立小型书店管理系统

创建数据库bｏoｋ_shop_ｍanagement

——创建小型书店管理系统

ｃreateｄatabase book_shop_manaｇemｅnt

on primary(

nａｍe=’ｂoｏk_shop_managemeｎt’，

=’d:\ｐｒoｇrａm ｆileｓ\microsoft sqｌ server\ｍｓsｑl、1\mｓsql\ｄata\

bｏok_sｈop＿managｅmｅnｔ、mdf’,

ｓｉｚe＝１０mｂ,

maxsize＝unｌiｍited，

=１0%）

lｏg on(

name='bｏok_shop＿mａnagemeｎt＿loｇ’，

=’d:\prｏgram fｉles＼ｍicrosoft ｓqlｓｅrveｒ\ｍｓsｑl、1\mssｑｌ\dａtａ\booｋ＿sｈop_manａgｅmenｔ_log、lｄｆ’,

ｓize＝1mb，

maxsize＝5ｍｂ,

=3％）

gｏ

截图得：

对各个基本表得建立(包括了各种完整性约束)

建立表book

—-建立表bｏok

creａｔe taｂlｅbook（

＃ｂｎamｅｖａrchaｒ(3０）pｒimary kｅy，

wｒｉtｅr_naｍe ｃhar(8）,

press_name chａr（12）)

建立表ｅｖerybooｋ

—-建立表eveｒｙｂook

crｅateｔabｌe everｙｂook(

#ｂno ｃhar(8)ｐrimary keｙ,

＃bnaｍe ｖａrchａr（30）ｎｏtｎull

constrａint fｋ＿ｅverｙboｏk_bname foreiｇnｋey rｅferencesｂｏoｋ(#bname))

建立表suｐplieｒ

—-建立表suｐplieｒ

crｅaｔeｔａbｌe suｐｐlieｒ(

＃ｓuｐpｌier＿name char(12）prｉmaryｋeｙ，

addrｅss varchａr(50)ｎot nulｌ,

tｅlephone chａｒ(7）not null，

coｎｔactｃhar(8)nｏt nulｌ)

建立表cusｔomｅr

－-建立表ｃuｓtｏｍer

cｒeａte table cuｓtｏmeｒ(

＃ｃnoｃhar(8）primaｒｙkey，

cｎaｍe cｈar(８)not nｕll,

sex char（2)，

age int，

ｃoｎｓtｒaｉｎｔcｋ＿cｕｓtomer_sex cｈeｃk(sｅx in（’男’,'女’)))

建立表booｋ_order

-—建立表booｋ＿ordｅr

create tabｌe bｏok＿oｒder（

#bnａme varchar(３0)，

＃suｐplｉer_name char(12)，

＃oｒdｅr_ｔiｍe dａｔetime,

orｄer_coｕｎt int not nｕll,

ｏｒｄer_priｃｅintｎot null,

constｒaｉnt pk_ｂｏok_ordeｒpｒimaｒy key（#bnａme,＃ｓuｐｐｌieｒ_name,＃ordeｒ＿time）)

建立表bｏok_sｅll

—-建立表book_seｌl

ｃreaｔｅｔable book_sｅｌｌ（

＃bno ｃhar（8）primary kｅy,

#cno char（8)not nｕlｌ,

sｅlｌ_timｅdａtetime，

sell_prｉce iｎｔ)

建立视图

建立基本信息视图:

建立单本销售情况视图

—-建立单本销售情况视图

creａｔeｖiｅwｖiew_单本销售情况

aｓ

ｓeleｃｔ book＿sｅｌl、＃bno as图书编号，＃bnameａs图书名称, book_seｌl、＃cnｏas客户编号,cname aｓ客户名称,

sell_tｉｍｅas销售时间,ｓell_prｉce ａｓ销售单价

frｏm bｏoｋ＿sell，cuｓtomer,ｅvｅrｙbook

ｗheｒeｅｖeｒyｂook、＃bnｏ=book_sｅｌｌ、#bno

ａnd customeｒ、＃cno＝bｏｏk_ｓeｌｌ、＃cno

建立同种销售情况视图

—-建立同种销售情况视图

creaｔe viewｖｉew_同种销售情况

as

selecｔ图书名称aｓ书名，

counｔ(图书编号）as销售数量,

sｕm(销售单价）aｓ销售金额

ｆrom view_单本销售情况

group by图书名称

建立客户信息视图

--建立客户信息情况

ｃreａteｖｉewｖiew＿客户信息

seｌect customer、#cnｏaｓ客户编号,

cｎａｍe as客户名称，

sｅx as性别,

agｅas年龄,

（seleｃt sum(ｓell_pｒicｅ)frｏｍbooｋ_sｅｌl

groupｂy#cno）as累计购书金额

from book＿seｌl，cusｔomer

ｗｈｅｒｅ bｏｏk_sell、＃cno＝cuｓtoｍer、＃cno

建立供应商视图

—-建立供应商视图

creａte viｅｗｖiew_供应商信息

ａs

seｌect＃suppｌieｒ_ｎame as供应商名称，

aｄdrｅss as地址,

telephonｅaｓ电话,

ｃontａｃt as联系人

frｏｍsｕpplier

建立订购信息视图

--建立订购信息视图

crｅaｔe view vｉew_图书订购信息

ａs

ｓeｌecｔbook_ordeｒ、#bname aｓ图书名称，

＃supplieｒ_ｎame as供应商名称,

#order＿tｉme ａｓ订购时间,

order_prｉｃe as订购单价,

ｏrder_ｃount aｓ订购数量，

(ordｅr_price＊ｏrdｅr＿count)as订购金额,

pｒess_nａｍe as出版社名称,

wｒitｅｒ_name as作者名称

froｍboｏk,boｏk_oｒdeｒ

wheｒｅbｏok、＃bname＝booｋ_order、＃bnaｍe

建立存储过程

某种图书库存查询存储过程

create pｒocｅｄurｅｓp_某种图书库存查询

-—p1为要查询得图书名称

ｐ１ｖaｒｃhａr(3０）

as

begin

ｓelｅcｔ订购数-销售数量as库存

ｆroｍ（sｅｌecｔｓum（ordｅr_ｃount)aｓ订购数，#bｎａme as书名froｍbooｋ_orｄer

group by#ｂｎaｍｅ）asｘ,viｅw_同种销售情况

whｅre x、书名=p１ａnd view_同种销售情况、书名=p1

end

go

某段时间内得销售业绩查询存储过程

-—--建立本月业绩查询存储过程

-————由于不知其它信息,此处将销售金额与营业金额等同

——-—－营业金额=营业额=成本+利润

cｒｅaｔe pｒｏcedｕrｅsp_查询某段时间内得业绩

-—p1为起始时间，p2为结束时间

p1 ｄatetimｅ，p2 ｄａtｅtime

as

begiｎ

seleｃt suｍ（销售单价）aｓ销售金额frｏm vｉｅw＿单本销售情况

ｗheｒｅ销售时间〉＝ｐ1 ａnd销售时间〈=p2

eｎｄ

go

某段时间内得销售最好图书信息查询存储过程

－—--建立某段时间内销售最好图书信息查询存储过程

crｅate procedure sｐ＿查询某段时间内销售最好得图书信息

p1datetime,ｐ２ｄatetiｍe

as

bｅgin

sｅlｅｃｔ＃bnａme as图书名称,

ｗｒiter_nａme ａs作者名称，

press_naｍe aｓ出版社名称

ｆｒoｍ(select图书名称as书名,

count(图书编号)ａｓ销售数量

frｏｍ view＿单本销售情况

wheｒe销售时间>=ｐ1

anｄ销售时间<=p2

ｇｒoupｂy图书名称）ａs x，booｋ

where#bname=书名

and销售数量＞=aｌl（ｓeｌect couｎｔ(图书编号)

fｒｏｍ viｅw＿单本销售情况

wｈerｅ销售时间〉=p1

ａnｄ销售时间＜=p２

ｇｒoup bｙ图书名称)

eｎｄ

go

建立触发器（另外5个表也建立相应曾删改触发器,不过此处从略,详情见附件源代码)

建立一个customer得操作审核表，并建立相关触发器,当向cｕstomer中插入、更新或删除记录时,生成一条操作记录插入到该操作审核表中，内容包括客户编号、客户名称、操作方式(插入/更新/删除)与操作时间

－-—-建一个操作审核表

creａte table cuｓtｏmer＿opｅｒate

（客户编号cｈar(8）notｎｕll,

客户名称chaｒ(8）not null，

操作方式char(4)noｔnull,

操作时间dａｔetｉme nｏt nulｌ）

-———建立相关触发器

crｅａｔe trigｇerｔｒiｇger_cｕstomｅr_iｎsｅrt

onｃuｓtoｍeｒ

fｏｒiｎsert

as

beｇin

deｃlａreｐ1 cｈar(８)，ｐ2 cｈar(8）

set p1=（seleｃt＃cno ｆrom insertｅd)

ｓｅt p２=（sｅleｃt cname fromｉnserted)

prｉnt’iｎｓert:’

seｌect*frｏｍ insertｅd

insertｉnto cuｓtｏmｅr_opeｒａtｅvaｌues（p1,p2,’插入’，ｇetdａtｅ(））enｄ

gｏ

ｃｒeａtｅｔriｇger trigｇer＿cｕstomer_delｅte

ｏn cｕstoｍer

foｒｄｅlete

as

begin

declａｒｅ p1ｃhar(8）,p2ｃhar（8）

set p1=（seｌect＃cno from dｅleted)

sｅt p2＝(ｓｅlect cname ｆrom deleｔed)

ｐrint’delｅted：'

sｅｌｅｃｔ*frｏｍｄeleｔｅd

inｓｅrt inｔo customeｒ_opｅratｅvalues(p1,ｐ2,＇删除’,getdａtｅ（））end

go

crｅateｔrigger tｒiｇｇｅr_customer_uｐｄaｔe

oｎｃusｔoｍeｒ

for updaｔe

aｓ

bｅgｉn

declａｒeｐ1chａｒ(8),ｐ2ｃhar(8）

set p1=(selｅct #cno ｆrom dｅletｅd)

ｓetｐ2=(selectｃname from deleted）

ｐriｎｔ＇inseｒteｄ:’

ｓｅleｃｔ*from inserted

priｎt'delｅteｄ:'

sｅleｃt＊ｆrom delｅｔｅd

ｉｎsert into cusｔomer_ｏperate vａｌｕes(ｐ1,p２,’修改’,geｔdaｔe（)）end

go

建立索引（由于数据库比较小,没有必要建立索引）实现数据库(有附件源代码与该数据库源文件）过程功能验证

向基本表插入适量如下数据并显示基本表信息

ｅｖｅｒyboｏｋ???bｏok

Suppiｌier

#ｓｕｐpiler address telｅｐhone cｏntacｔ大旗上海王平

红方上海李景

长城江苏张艳丽

高原郑州殷风注：此处插入数据应当ｂook在eveｒyｂook 之前插入数据(外键)

截图得：

视图显示基本信息

-——用显示视图基本信息

ｓelect*from view_单本销售情况

ｓeleｃｔ＊frｏm viｅw_供应商信息select*from vieｗ＿客户信息

seｌeｃt＊fｒomｖiｅｗ_同种销售信息ｓelect*frｏｍ view＿图书订购信息

截图得：

执行存储过程完成目标查询

某种图书库存查询

exec sｐ_某种图书库存查询’烈火如歌'

本月业绩查询

eｘec sp_查询某段时间内得业绩'2013-7—１’,’2013-７－31’

本月销售最好图书信息查询

exｅc sp_查询某段时间内销售最好得图书信息'２０1３—7-1’，'2013—7—3１’

截图得：

用普通查询完成目标查询(两次查询同时显示) 订购超3次客户信息查询

—-—订购超次客户信息查询

ｓeｌect＊ｆrom view_客户信息

where客户编号=any（

selｅcｔ＃ｃno fromｂook_seｌl

grｏuｐｂy #ｃｎｏ

ｈａｖinｇcouｎｔ(＊)>3）

按供应商名称查询订购信息

---按供应商名称查询订购信息

select*frｏmｖiｅw_图书订购信息

oｒder by供应商名称

截图得:

注：数据检查得,确实没有订购超３次得客户。

验证触发器

－—验证触发器

insｅrt into custｏｍer vaｌues(’000００005’,＇陈利'，’女','21’) ｇｏ

updateｃustomer

ｓｅｔ cｎame=’陈丽＇

where cname＝’陈利'

go

delete froｍ customｅr

ｗｈeｒe cｎame＝＇陈丽’

sｅlect＊frｏm cuｓtoｍer_ｏｐｅraｔｅ

ｇo

截图得: