SQLserver数据库课程设计范例

1、 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 数据字典 数据项名长数据含义 别 与其他数据项辑的逻 系有时与客 户编号作为主

5、码有时与供应商组成主码 应与图书称与供应商 名称为的作 码直接关联有时与图 书编号作为主码 取值范围 取值含义 度 类型 说明 名唯一标识 00000000 根据入库时间先后编号 图书编号 8 char 99999999每本书书唯一标识 varchar 30 名 每种书图书名称作者名称出版社名称供应商名称地址电话联系人订购时间订购单价客户编号客户名称性别年龄 每种书的char 8 作者姓名 每种书的 12 char 出版社名称 唯一标识 char 12 供应商 供应商的地址 50 varchar 0000000 7char 供应商的电话 9999999 通过联系人 char 8 书店与供应 商

6、进行沟通每种书的 datetime 订购时间 从供应商处 int 进货每种书 的每本价格唯一标识 000000000 按购书先后 8char 编号 客户 99999999 8 char 客户姓名 女或男 2 char 客户性别 每年都要 客户年龄 int 每本书的 增加一岁的与图书编销售时间 销售时间 datetime 号编客户和销售单价 每本书的 int 号主作为的 销售价格 码 直接关联 数据结构： 数据结构名称每种书每本书客户编号 指具有相同书名的书的集 合的基本信息指图书编号唯一标识的个体的归属 含义说明 姓名 图书名称、作者名称、出版社名称 图书编号、图书名称 组成性别供应商供应基表

7、供应商的基本信息 每种书在何处订购的具体情况 供应商名称、地址、电话、联系人图书名称、供应商名称、订购单价、订购时间客户 销售基表客户的基本信息 每本书被谁买的具体请况 客户 客户编号、客户名称、性别、年龄图书编号、客户编号、销售单价、销售时间 概念结构设计 概念结构设计的方法自顶向下，自底向上，逐渐扩张以及混合策略。本系统设计概念结构通常有四类方法：根据自顶向即首先定义全局的概念结构的框架，然后逐步细化。采用的是自底向上的方法。 下地进行需求分析然后再自底向上地进行概念设计。概念结构设计的步骤 第一步：抽象数据； E-R图；第二步：设计局部 图，消除各种冲突；E-R图综合成总体E-R第三步：

8、把各分 图进行优化，消除冗余数据和冗余联系；第四步：对总体E-R 抽象初步数据：（此处与优化后E-R图得出的实体联系属性很有可能不同） 实体：图书、供应商、客户 联系：图书订购关系、图书销售关系 图书与供应商的关系是多对一的关系； 图书与客户的关系是多对一的关系。 属性： 图书的属性：图书编号、图书名称、出版社名称、作者名称、供应商名称； 主码为图书编号 供应商的属性：供应商名称、供应商地址、电话、联系人； 主码为供应商名称 客户的属性：客户编号、姓名、性别、年龄； 主码为客户编号 图书订购关系属性：供应商名称、图书编号、订购时间、订购单价； 主码为供应商名称和图书编号 图书销售关系属性：客户

9、编号、图书编号、销售时间、销售单价； 主码为客户编号和图书编号 分E-R图： 图书E-R图： 图书编号图书名称出版社名称图书作者名称供应商名称 供应商E-R图： 供应商名称供应商地址供应商电话联系人 E-R客户图： 年龄 初E-R图： 图书编号图书名称出版社名称作者名称图书供应商名称图书编号订购时间订购单价1供应商名称供应商年龄供应商地址电话 NN图书订购图书销售联系人供应商名客户编图书编销售时销售单客客户编性姓名 优化后的总E-R图： 图书编号图书名称图书名称作者名称出版社名称1M每种书每本书归属N1销售时间图书编号图书名称订购时间订购基表销售基表销售单价客户编号供应商名称订购单价MN订购数

10、量联系人年龄客户编号供应商名称性别地址客户名称 客户供应商电话 逻辑设计阶段 逻辑设计阶段：将系统E-R模型转换为关系模式，并对给出的关系模式进行关系规范化处理（尽量满足3NF），得到系统全部的关系模式，并标明主键、外键此部分一律采用英文命名。然后进行用户外模式的设计，针对中国用户，此处用中文命名。 将ER模式转换为以下关系模式： 1) book(#bname, writer_name, press_name); 主键：#bname 2.) everybook (# bno, #bname) ; 主键：#bno 外键：#bname 3) supplier (#supplier_name, ad

11、dress, 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都满足。因为元组中都是不可分割的数据，且所有

12、非主属性完全依赖其主码，且它的任何一个非主属性都不传递于任何主关键字。 用户外模式设计： 基本信息视图的建立： 1. 单本销售情况（图书编号，图书名称客户编号，客户名称，销售单价，销售时间）； 2. 同种销售情况（书名，销售数量，销售金额）； 3. 客户信息（客户编号，客户名称、性别，年龄，累计购书金额）； 4. 供应商信息（供应商名称，地址，电话，联系人）； 5. 图书订购信息（图书名称，供应商名称，订购时间，订购数量，订购单价，订购金额，出版社名称，作者名称）； 物理设计阶段 物理设计阶段：为一个逻辑数据模型选取一个最适合应用环境的物理结构（存储结构与存取方法），数据库的物理设计通常分为两

13、步：A、确定数据库的物理结构；B、对物理结构进行时间和空间效率评价。 建立小型书店管理系统 创建数据库 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

14、( name=book_shop_management_log, filename=d:program filesmicrosoft sql servermssql.1mssqldatabook_shop_management_log.ldf, size=1mb, maxsize=5mb, filegrowth=3%) go 截图得： 对各个基本表的建立（包括了各种完整性约束） 建立表 book -建立表book create table book( #bname varchar(30) primary key, writer_name char(8), press_name char(12)

15、 建立表 everybook -建立表everybook create table everybook( #bno char(8) primary key, #bname varchar(30) not null constraint fk_everybook_bname foreign key references book(#bname) 建立表 supplier -建立表supplier create table supplier( #supplier_name char(12) primary key, address varchar(50)not null, telephone ch

16、ar(7)not null, null) ) notcontact char(8建立表 customer -建立表customer create table customer( #cno char(8) primary key, cname char(8)not null, sex char(2), age int, )女, ck_customer_sex check (sex in(男constraint建立表 book_order -建立表book_order create table book_order( #bname varchar(30), #supplier_name char(

17、12), #order_time datetime, order_count int not null, order_price int not null, ),#order_time#bname,#supplier_namekeyconstraint pk_book_order primary (建立表 book_sell -建立表book_sell create table book_sell( #bno char(8) primary key, #cno char(8) not null, sell_time datetime, sell_price int) 建立视图 建立基本信息视图

18、： 建立单本销售情况视图 -建立单本销售情况视图 create view view_单本销售情况 as select book_sell.#bno as 图书编号,#bname as 图书名称, book_sell.#cno as 客户编号,cname as 客户名称, sell_time as 销售时间,sell_price as 销售单价 from book_sell,customer,everybook where everybook.#bno=book_sell.#bno and customer.#cno=book_sell.#cno 建立同种销售情况视图 -建立同种销售情况视图 c

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

20、,customer where book_sell.#cno=customer.#cno 建立供应商视图 -建立供应商视图 create view view_供应商信息 as select #supplier_name as 供应商名称, address as 地址, telephone as 电话, contact as 联系人 supplierfrom建立订购信息视图 -建立订购信息视图 create view view_图书订购信息 as select book_order.#bname as 图书名称, #supplier_name as 供应商名称, #order_time as 订

21、购时间, order_price as 订购单价, order_count as 订购数量, (order_price*order_count) as 订购金额, press_name as 出版社名称, writer_name as 作者名称 from book,book_order #bnamebook_order. book.#bname=where建立存储过程 某种图书库存查询存储过程 create procedure sp_某种图书库存查询 -p1为要查询的图书名称 p1 varchar(30) as begin select 订购数-销售数量as 库存 from (select s

22、um(order_count) as 订购数,#bname as 书名 from book_order group by #bname) as x,view_同种销售情况 where x.书名=p1 and view_同种销售情况.书名=p1 end go某段时间内的销售业绩查询存储过程 -建立本月业绩查询存储过程 -由于不知其它信息，此处将销售金额与营业金额等同 -营业金额=营业额=成本+利润 create procedure sp_查询某段时间内的业绩 -p1为起始时间,p2为结束时间 p1 datetime,p2 datetime as begin select sum(销售单价) as 销售金额from view_单本销售情况 where 销售时间=p1 and 销售时间=p1 and 销售时间=all(select count(图书编号) from view_单本销售情况 where 销售时间=p1 and 销售时间3having count按供应商名称查询订购信息 -按供应商名称查询订购信息 select * from view_图书订购信息 ord