SQLserver数据库课程设计范例.docx
《SQLserver数据库课程设计范例.docx》由会员分享，可在线阅读，更多相关《SQLserver数据库课程设计范例.docx（23页珍藏版）》请在冰豆网上搜索。
SQLserver数据库课程设计范例
Lastrevisionon21December2020
SQLserver数据库课程设计范例
1概述
课题简介
书店书目书种繁多，来源多样，购买者众多，图书信息、供应商信息、客户信息、销售信息庞大，不易管理。
因此，很有必要创建一个小型书店管理系统，以便于书店对图书的管理。
设计目的
应用对数据库系统原理的理论学习，通过上机实践的方式将理论知识与实践更好的结合起来，巩固所学知识。
数据库应用课程实践：
实践和巩固在课堂教学中学习有关知识，熟练掌握对于给定结构的数据库的创建、基本操作、程序系统的建立和调试以及系统评价。
数据库原理软件设计实践：
实践和巩固在课堂教学中学习的关于关系数据库原理的有关知识和数据库系统的建立方法，熟练掌握对于给定实际问题，为了建立一个关系数据库信息管理系统，必须得经过系统调研、需求分析、概念设计、逻辑设计、物理设计、系统调试、维护以及系统评价的一般过程，为毕业设计打下基础。
设计内容
运用基于E-R模型的数据库设计方法和关系规范化理论做指导完成从系统的分析到设计直至系统的最终实现，开发小型书店管理系统，完成小型书店管理系统的全部功能。
首先做好需求分析，并完成数据流图和数据字典。
其次做概念分析，利用实体联系的方法将需求分析的用户需求抽象为信息结构，得到E-R图。
然后就是逻辑结构设计，将E-R图转换为计算机系统所支持的逻辑模型
2需求分析
功能分析
首先，建立一些基本表（尽可能满足3N），对大部分基本信息组合、存储；其次通过建立视图实现对冗余数据的有必要保留（查询并计算基本表属性得到新的作为视图属性）并实现对以下基本信息的显示。
图书信息：
图书名称、订购数量、订购时间、订购单价、金额、出版社名称、作者名称；供应商名称等；
供应商信息：
供应商名称、地址、电话，联系人；
客户信息：
客户编号、名称、年龄、性别、累计购书金额等；
销售信息：
时间、销售名称、数量、销售单价、客户编号、客户名称、金额等。
在此基础上进行以下目标查询，由于有些查询常用且较复杂，为了简化其应用，所以将它们定义为存储过程。
查询当月书店销售金额、营业金额；（存储过程）
查询某种图书库存数量；（存储过程）
查询当月销量最好的图书信息；（存储过程）
按供应商名称查询订购信息；（普通查询）
查询购买次数超过3次的客户信息。
（普通查询）
接着根据需要建立触发器、存储过程、索引，实现对数据库的优化。
最后，进行过程功能的验证。
允许具有权限者对数据库进行数据更新和查询等合法操作。
数据流图
用流程图来描述处理过程与数据的关系
数据流图如下：
图2,1数据流图
数据字典
用数据字典来描述数据
数据字典如下：
数据项：
表数据字典
数据
项名
含义
说明
别名
数据
类型
长度
取值范围
取值含义
与其他数据项
的逻辑关系
图书编号
唯一标识
每本书
char
8
00000000~
根据入库时间
先后编号
有时与客
户编号作
为主码
图书名称
唯一标识
每种书
书名
varchar
30
有时与供应商
组成主码
作者名称
每种书的
作者姓名
char
8
出版社名称
每种书的
出版社名称
char
12
供应商名称
唯一标识
供应商
char
12
地址
供应商的地址
varchar
50
电话
供应商的电话
char
7
0000000~
9999999
联系人
通过联系人
书店与供应
商进行沟通
char
8
订购时间
每种书的
订购时间
datetime
应与图书称
与供应商
名称
作为的主码
直接关联
订购单价
从供应商处
进货每种书
的每本价格
int
客户编号
唯一标识
客户
char
8
000000000~
按购书先后
编号
有时与图
书编号作
为主码
客户名称
客户姓名
char
8
性别
客户性别
char
2
女或男
年龄
客户年龄
int
每年都要
增加一岁的
销售时间
每本书的
销售时间
datetime
与图书编号
和客户编号
作为的主码
直接关联
销售单价
每本书的
销售价格
int
数据结构：
数据结构名称
含义说明
组成
每种书
指具有相同书名的书的集
合的基本信息
图书名称、作者名称、出版社名称
每本书
指图书编号唯一标识的个体的归属
图书编号、图书名称
供应商
供应商的基本信息
供应商名称、地址、电话、联系人
供应基表
每种书在何处订购的具体情况
图书名称、供应商名称、
订购单价、订购时间
客户
客户的基本信息
客户编号、客户名称、性别、年龄
销售基表
每本书被谁买的具体请况
图书编号、客户编号、
销售单价、销售时间
概念结构设计
概念结构设计的方法
设计概念结构通常有四类方法：
自顶向下，自底向上，逐渐扩张以及混合策略。
本系统采用的是自底向上的方法。
即首先定义全局的概念结构的框架，然后逐步细化。
根据自顶向下地进行需求分析然后再自底向上地进行概念设计。
概念结构设计的步骤
第一步：
抽象数据；
第二步：
设计局部E-R图；
第三步：
把各分E-R图综合成总体E-R图，消除各种冲突；
第四步：
对总体E-R图进行优化，消除冗余数据和冗余联系；
抽象初步数据：
（此处与优化后E-R图得出的实体联系属性很有可能不同）
实体：
图书、供应商、客户
联系：
图书订购关系、图书销售关系
图书与供应商的关系是多对一的关系；
图书与客户的关系是多对一的关系。
属性：
图书的属性：
图书编号、图书名称、出版社名称、作者名称、供应商名称；
主码为图书编号
供应商的属性：
供应商名称、供应商地址、电话、联系人；
主码为供应商名称
客户的属性：
客户编号、姓名、性别、年龄；
主码为客户编号
图书订购关系属性：
供应商名称、图书编号、订购时间、订购单价；
主码为供应商名称和图书编号
图书销售关系属性：
客户编号、图书编号、销售时间、销售单价；
主码为客户编号和图书编号
分E-R图：
图书E-R图：
供应商E-R图：
客户E-R图：
初E-R图：
优化后的总E-R图：
逻辑设计阶段
逻辑设计阶段：
将系统E-R模型转换为关系模式，并对给出的关系模式进行关系规范化处理（尽量满足3NF），得到系统全部的关系模式，并标明主键、外键此部分一律采用英文命名。
然后进行用户外模式的设计，针对中国用户，此处用中文命名。
将E——R模式转换为以下关系模式：
1）book（#bname,writer_name,press_name）;
主键：
#bname
2.）everybook（#bno,#bname）;
主键：
#bno
外键：
#bname
3）supplier（#supplier_name,address,telephone,contact）;
主键：
#supplier_name
4）customer（#cno,cname,sex,age）;
主键：
#cno
5）book_order（#bname,#supplier_name,#order_time,order_price,order_count）;
主键：
#bname,#supplier_name,#order_time
6）book_sell（#bno,#cno,sell_time,sell_price）;
主键：
#bno
发现这六个关系模式3N都满足。
因为元组中都是不可分割的数据，且所有非主属性完全依赖其主码，且它的任何一个非主属性都不传递于任何主关键字。
用户外模式设计：
基本信息视图的建立：
1.单本销售情况（图书编号，图书名称客户编号，客户名称，销售单价，销售时间）；
2.同种销售情况（书名，销售数量，销售金额）；
3.客户信息（客户编号，客户名称、性别，年龄，累计购书金额）；
4.供应商信息（供应商名称，地址，电话，联系人）；
5.图书订购信息（图书名称，供应商名称，订购时间，订购数量，订购单价，订购金额，出版社名称，作者名称）；
物理设计阶段
物理设计阶段：
为一个逻辑数据模型选取一个最适合应用环境的物理结构（存储结构与存取方法），数据库的物理设计通常分为两步：
A、确定数据库的物理结构；B、对物理结构进行时间和空间效率评价。
建立小型书店管理系统
创建数据库book_shop_management
--创建小型书店管理系统
createdatabasebook_shop_management
onprimary（
name='book_shop_management',
filename='d:
\programfiles\microsoftsqlserver\\mssql\data\
',
size=10mb,
maxsize=unlimited,
filegrowth=10%）
logon（
name='book_shop_management_log',
filename='d:
\programfiles\microsoftsqlserver\\mssql\data\',
size=1mb,
maxsize=5mb,
filegrowth=3%）
go
截图得：
对各个基本表的建立（包括了各种完整性约束）
建立表book
--建立表book
createtablebook（
#bnamevarchar（30）primarykey,
writer_namechar（8）,
press_namechar（12））
建立表everybook
--建立表everybook
createtableeverybook（
#bnochar（8）primarykey,
#bnamevarchar（30）notnull
constraintfk_everybook_bnameforeignkeyreferencesbook（#bname））
建立表supplier
--建立表supplier
createtablesupplier（
#supplier_namechar（12）primarykey,
addressvarchar（50）notnull,
telephonechar（7）notnull,
contactchar（8）notnull）
建立表customer
--建立表customer
createtablecustomer（
#cnochar（8）primarykey,
cnamechar（8）notnull,
sexchar
（2）,
ageint,
constraintck_customer_sexcheck（sexin（'男','女'）））
建立表book_order
--建立表book_order
createtablebook_order（
#bnamevarchar（30）,
#supplier_namechar（12）,
#order_timedatetime,
order_countintnotnull,
order_priceintnotnull,
constraintpk_book_orderprimarykey（#bname,#supplier_name,#order_time））
建立表book_sell
--建立表book_sell
createtablebook_sell（
#bnochar（8）primarykey,
#cnochar（8）notnull,
sell_timedatetime,
sell_priceint）
建立视图
建立基本信息视图：
建立单本销售情况视图
--建立单本销售情况视图
createviewview_单本销售情况
as
selectbook_sell.#bnoas图书编号,#bnameas图书名称,
book_sell.#cnoas客户编号,cnameas客户名称,
sell_timeas销售时间,sell_priceas销售单价
frombook_sell,customer,everybook
whereeverybook.#bno=book_sell.#bno
andcustomer.#cno=book_sell.#cno
建立同种销售情况视图
--建立同种销售情况视图
createviewview_同种销售情况
as
select图书名称as书名,
count（图书编号）as销售数量,
sum（销售单价）as销售金额
fromview_单本销售情况
groupby图书名称
建立客户信息视图
--建立客户信息情况
createviewview_客户信息
selectcustomer.#cnoas客户编号,
cnameas客户名称,
sexas性别,
ageas年龄,
（selectsum（sell_price）frombook_sell
groupby#cno）as累计购书金额
frombook_sell,customer
wherebook_sell.#cno=customer.#cno
建立供应商视图
--建立供应商视图
createviewview_供应商信息
as
select#supplier_nameas供应商名称,
addressas地址,
telephoneas电话,
contactas联系人
fromsupplier
建立订购信息视图
--建立订购信息视图
createviewview_图书订购信息
as
selectbook_order.#bnameas图书名称,
#supplier_nameas供应商名称,
#order_timeas订购时间,
order_priceas订购单价,
order_countas订购数量,
（order_price*order_count）as订购金额,
press_nameas出版社名称,
writer_nameas作者名称
frombook,book_order
wherebook.#bname=book_order.#bname
建立存储过程
某种图书库存查询存储过程
createproceduresp_某种图书库存查询
--@p1为要查询的图书名称
@p1varchar（30）
as
begin
select订购数-销售数量as库存
from（selectsum（order_count）as订购数,#bnameas书名
frombook_order
groupby#bname）asx,view_同种销售情况
wherex.书名=@p1andview_同种销售情况.书名=@p1
end
go
某段时间内的销售业绩查询存储过程
----建立本月业绩查询存储过程
-----由于不知其它信息，此处将销售金额与营业金额等同
-----营业金额=营业额=成本+利润
createproceduresp_查询某段时间内的业绩
--@p1为起始时间,@p2为结束时间
@p1datetime,@p2datetime
as
begin
selectsum（销售单价）as销售金额fromview_单本销售情况
where销售时间>=@p1and销售时间<=@p2
end
go
某段时间内的销售最好图书信息查询存储过程
----建立某段时间内销售最好图书信息查询存储过程
createproceduresp_查询某段时间内销售最好的图书信息
@p1datetime,@p2datetime
as
begin
select#bnameas图书名称,
writer_nameas作者名称,
press_nameas出版社名称
from（select图书名称as书名,
count（图书编号）as销售数量
fromview_单本销售情况
where销售时间>=@p1
and销售时间<=@p2
groupby图书名称）asx,book
where#bname=书名
and销售数量>=all（selectcount（图书编号）
fromview_单本销售情况
where销售时间>=@p1
and销售时间<=@p2
groupby图书名称）
end
go
建立触发器（另外5个表也建立相应曾删改触发器，不过此处从略，详情见附件源代码）
建立一个customer的操作审核表，并建立相关触发器，当向customer中插入、更新或删除记录时，生成一条操作记录插入到该操作审核表中，内容包括客户编号、客户名称、操作方式（插入/更新/删除）和操作时间
----建一个操作审核表
createtablecustomer_operate
（客户编号char（8）notnull,
客户名称char（8）notnull,
操作方式char（4）notnull,
操作时间datetimenotnull）
----建立相关触发器
createtriggertrigger_customer_insert
oncustomer
forinsert
as
begin
declare@p1char（8）,@p2char（8）
set@p1=（select#cnofrominserted）
set@p2=（selectcnamefrominserted）
print'insert:
'
select*frominserted
insertintocustomer_operatevalues（@p1,@p2,'插入',getdate（））
end
go
createtriggertrigger_customer_delete
oncustomer
fordelete
as
begin
declare@p1char（8）,@p2char（8）
set@p1=（select#cnofromdeleted）
set@p2=（selectcnamefromdeleted）
print'deleted:
'
select*fromdeleted
insertintocustomer_operatevalues（@p1,@p2,'删除',getdate（））
end
go
createtriggertrigger_customer_update
oncustomer
forupdate
as
begin
declare@p1char（8）,@p2char（8）
set@p1=（select#cnofromdeleted）
set@p2=（selectcnamefromdeleted）
print'inserted:
'
select*frominserted
print'deleted:
'
select*fromdeleted
insertintocustomer_operatevalues（@p1,@p2,'修改',getdate（））
end
go
建立索引（由于数据库比较小，没有必要建立索引）
实现数据库（有附件源代码和该数据库源文件）
过程功能验证
向基本表插入适量如下数据并显示基本表信息
everybookbook
#bno
#bname
Writer_name
Press_name
何以笙箫默
顾漫
晋江出版社
原来你还在这里
辛夷坞
起点出版社
烈火如歌
明晓溪
晋江出版社
微微一笑很倾城
顾漫
阅读出版社
#bname
00000001
何以笙箫默
00000002
何以笙箫默
00000003
原来你还在这里
00000004
烈火如歌
00000005
烈火如歌
00000006
烈火如歌
00000007
微微一笑很倾城
book_sell
#bno
#cno
Sell_time
Sell_price
00000001
00000001
2013-6-16
21
00000003
00000002
2013-6-25
19
00000007
00000002
2013-7-9
20
00000004
00000003
2013-7-9
23
00000005
00000004
2013-7-19
18
customer
#cno
cname
sex
Age
00000001
吕鹏
女
18
00000002
孙鲁鲁
女
18
00000003
王肖雨
女
19
00000004
邵丽君
女
19
Book_order
#bname
#supplier_name
Order_time
Order_count
Order_price
何以笙箫默
大旗
2013-6-10
2
12
原来你还在这里
大旗
2013-6-20
1
10
烈火如歌
红方
2013-7-2
1
10
烈火如歌
长城
2013-7-3
2
9
微微一笑很倾城
高原
2013-7-3
1
11
Suppilier
#suppiler
address
telephone
contact
大旗
上海
1573234
王平
红方
上海
1734272
李景
长城
江苏
1564323
张艳丽
高原
郑州
1242356
殷风
注：
此处插入数据应当book在everybook之前插入数据（外键）
截图得：
视图显示基本信息
---用显示视图基本信息
select*fromview_单本销售情况
select*fromview_供应商信息
select*fromview_客户信息
select*fromview_同种销售信息
select*fromview_图书订购信息
截图得：
执行存储过程完成目标查询
某种图书库存查询
execsp_某种图书库存查询'烈火如歌'
本月业绩查询
execsp_查询某段时间内的业绩'2013-7-1','2013-7-31'
本月销售最好图书信息查询
升级会员 