数据库设计——以设计图书管理系统为例_设计一个图书管理系统数据库

• 目录

  1、需求分析（功能分析及数据）

  2、概要模型设计

  3、逻辑结构设计

  4、物理结构设计与数据库实施

  5、数据库运行与维护

  1.设计一个插入数据的问题，并用SQL实现

  2.设计一个修改数据的问题（能够用到like和排序），并用SQL实现

  3.设计一个删除数据的问题（能够用到关系代数的差），并用SQL实现

  4.设计一个查询数据的问题（能够用到关系代数的除），并用SQL实现

  5.设计一个查询数据的问题（能够用到外连接），并用SQL实现

   6.设计一个查询数据的问题（能够用到表的自身连接），并用SQL实现

  7.设计一个分组统计数据的问题（基于多表连接基础上），并用SQL实现

  8.设计一个数据安全的问题（能够用到自主存取控制的授权或回收权限），并用SQL实现

  ---

• 1、需求分析（功能分析及数据）

     图书馆作为一种信息集散地，图书、用户借阅得资料繁多，包含很多信息资源数据的管理。而大多数图书馆却反对数据的规范化整理，导致数据繁多不易查找、数据冗余等众多问题，现在亟需建立一个图书管理系统，让图书管理工作更加规范化。

        读者管理：该功能针对读者进行管理。

读者信息：对读者身份证、性别、年龄、姓名、联系方式进行管理。

读者借阅信息：对读者编号、可借阅图书数量、已借阅图书数量、违约次数等信息进行管理。

        图书管理：该功能针对图书进行管理。

图书信息：对图书的书名、作者、出版社、ISBN进行管理。

图书借阅信息：对图书编号、入库时间、出库时间、剩余库存等信息进行管理。

        图书流通管理：该功能针对图书流通进行管理。

图书借阅：对借阅订单编号、图书编号、读者编号、到期时间进行管理。

图书归还：对借阅订单编号、图书编号、读者编号、归还时间进行管理。

图书返款：对借阅订单编号、图书编号、读者编号、罚款金额进行管理。

        图书管理人员：该功能针对管理图书的工作人员进行管理。

图书管理人员：对人员编号、姓名、管理区域进行管理。

---

• 2、概要模型设计

• 3、逻辑结构设计

    （1）根据E-R图，给出的关系模式。

    （2）判断各个表属于第几范式。

（1）

1. 读者信息（身份证，姓名，性别，联系方式，年龄）
2. 读者借阅情况（读者编号，可借阅图书数量，已借阅图书数量，违约次数，身份证）
3. 图书信息（ISBN，书名，出版社，作者）
4. 图书借阅信息（图书编号，入库时间，出库时间，剩余库存，ISBN）
5. 图书借阅（借阅订单编号，到期时间，读者编号，图书编号）
6. 图书归还（借阅订单编号，归还时间，读者编号，图书编号）
7. 图书罚款（借阅订单编号，罚款金额，读者编号，图书编号）
8. 图书管理人员（人员编号，姓名，管理区域）

（2）

1. 读者信息表

<1>身份证→姓名，身份证→性别，身份证→联系方式，身份证→年龄

<2>（姓名，联系方式）→身份证，（姓名，联系方式）→性别，（姓名，联系方式）→年龄

候选码：（身份证），（姓名，联系方式）

这里将主码设置为身份证，则该表属于BCNF

1. 读者借阅情况表

<1>（读者编号）→可借阅图书数量，（读者编号）→已借阅图书数量，（读者编号）→ 违约次数，(读者编号）→身份证

<2>（身份证）→读者编号，（身份证）→可借阅图书数量，（身份证）→已借阅图书数           量，（身份证）→违约次数

候选码：（读者编号），（身份证）

这里将主码设置为读者编号，则该表属于BCNF

1. 图书信息表

<1>（ISBN）→作者，（ISBN）→书名，（ISBN）→出版社

候选码：（ISBN）

该表属于BCNF

1. 图书借阅信息表

<1>图书编号→入库时间，图书编号→出库时间，图书编号→剩余库存，图书编号→ISBN

候选码：（图书编号）

该表属于BCNF

1. 图书借阅表

<1>借阅订单编号→读者编号，借阅订单编号→图书编号，借阅订单编号→到期时间

候选码借阅订单编号

该表属于BCNF

1. 图书归还表

<1>借阅订单编号→归还时间，借阅订单编号→图书编号，借阅订单编号→读者编号

候选码：借阅订单编号

该表属于BCNF

1. 图书罚款表

<1>借阅订单编号→罚款金额，借阅订单编号→图书编号，借阅订单编号→读者编号

候选码：借阅订单编号

该表属于BCNF

1. 图书管理人员表

<1>人员编号→姓名，人员编号→管理区域

候选码：人员编号

该表属于BCNF

• 4、物理结构设计与数据库实施

用SQL实现数据库和所有数据表的创建，包括相应的完整性约束（主外键等）。

Create Database 图书管理系统
    ON
       (name=lib_data,filename='d:\database\lib_data.mdf')
    LOG ON
  (name=lib_log,filename='d:\database\lib_log.ldf')
 
create table 读者信息(
	身份证 int not null primary key,
	姓名 char(20) not null,
	性别 char(10) not null check(性别 in ('男','女')),
	联系方式 char(20) not null，
年龄 int not null
)
create table 读者借阅情况(
	读者编号 char(15) not null primary key,
	可借阅图书数量 int not null,
	已借阅图书数量 int not null,
	违约次数 int  default 0,
	身份证 int FOREIGN KEY REFERENCES 读者信息(身份证)
)
create table 图书信息(
	ISBN int not null primary key,
	出版社 char(20) not null,
	作者 char(20) not null,
	书名 char(20) not null
)
create table 图书借阅信息(
	图书编号 char(15) not null primary key,
	剩余库存 int not null,
	入库时间 char(15) not null,
	出库时间 char(15) not null,
	ISBN int FOREIGN KEY REFERENCES 图书信息(ISBN)
)
create table 图书借阅(
	借阅订单编号 char(15) not null primary key,
	到期时间 char(15) not null,
	图书编号 char(15) FOREIGN KEY REFERENCES 图书借阅信息(图书编号),
	读者编号 char(15) FOREIGN KEY REFERENCES 读者借阅情况(读者编号)
)
create table 图书归还(
	借阅订单编号 char(15) not null primary key,
	归还时间 char(15) not null,
	图书编号 char(15) FOREIGN KEY REFERENCES 图书借阅信息(图书编号),
	读者编号 char(15) FOREIGN KEY REFERENCES 读者借阅情况(读者编号)
)
create table 图书罚款(
	借阅订单编号 char(15) not null primary key,
	罚款金额 int not null default 0,
	图书编号 char(15) FOREIGN KEY REFERENCES 图书借阅信息(图书编号),
	读者编号 char(15) FOREIGN KEY REFERENCES 读者借阅情况(读者编号)
)
create table 图书管理人员(
	人员编号 int not null primary key,
	姓名 char(20) not null,
	管理区域 char(20),
	图书编号 char(15) FOREIGN KEY REFERENCES 图书借阅信息(图书编号),
	ISBN int FOREIGN KEY REFERENCES 图书信息(ISBN)
)

• 5、数据库运行与维护

• 1.设计一个插入数据的问题，并用SQL实现

问题：请插入一个身份证为‘1001’，性别‘男’，联系方式为‘1375642’，姓名为‘小明’的，年龄为‘21’的数据到读者信息表。

代码：

insert into dbo.读者信息
values ('1001','小明','男','1375642','21')

• 2.设计一个修改数据的问题（能够用到like和排序），并用SQL实现

问题：请将读者信息表中姓中带‘小’的读者，年龄都增加1岁，并按照年龄从小到大进行排序。

 代码：

update dbo.读者信息
SET 年龄 = 年龄 + 1
where 姓名 like '小%'
 
select *
from dbo.读者信息
order by 年龄

                        1.读者信息表（原）                                              2.读者信息表（修改后）

• 3.设计一个删除数据的问题（能够用到关系代数的差），并用SQL实现

问题：将没有借阅过图书的读者借阅情况删除。

代码：

delete from dbo.读者借阅情况
where not exists
(select * from 图书借阅 
where 读者借阅情况.读者编号 = 图书借阅.读者编号)

 1.读者借阅情况表（原）

 2.图书借阅表

 3.读者借阅情况表（删除后）

• 4.设计一个查询数据的问题（能够用到关系代数的除），并用SQL实现

问题：查询至少借阅了‘4001’，‘4002’两本书的读者编号。

代码：

select  distinct  读者编号  
from  图书借阅  L1  
where not exists(
select * from 图书借阅 L2 
where 图书编号 in ('4001','4002')  
and not exists(
select * from  图书借阅 L3 
where L1. 读者编号=L3.读者编号 and L2.图书编号=L3.图书编号))

•         5.设计一个查询数据的问题（能够用到外连接），并用SQL实现

问题：将有过违约的读者全部信息输出。

代码：

select *
from dbo.读者借阅情况,dbo.读者信息
where 读者借阅情况.身份证 = 读者信息.身份证
	  and 违约次数 <> 0 

•  6.设计一个查询数据的问题（能够用到表的自身连接），并用SQL实现

问题：查询ISBN为‘3001’书的倒数第二次入库时间。

代码：

select B.入库时间
from dbo.图书借阅信息 A,dbo.图书借阅信息 B
where A.ISBN = B.ISBN and A.入库时间 = B.出库时间 and A.ISBN ='3001'

• 7.设计一个分组统计数据的问题（基于多表连接基础上），并用SQL实现

问题：查询每一种图书编号，对应读者的违约次数总和。

代码：

select 图书编号,sum(违约次数) as 违约次数总和
from dbo.读者借阅情况,dbo.图书借阅
where 读者借阅情况.读者编号 = 图书借阅.读者编号
group by 图书编号

• 8.设计一个数据安全的问题（能够用到自主存取控制的授权或回收权限），并用SQL实现

问题：把查询读者信息表权限授给U1用户。

代码：

grant select
on 读者信息
to U1