图书馆系统的后天SQLServer数据库设计

设计题目 图书馆系统的后台数据库设计

一．实验目的：

针对图书管理部门的实践调研，通过系统需求分析、数据库概念设计、逻辑设计到上机编程、调试和应用等全过程完成图书管理系统的后台数据库设计。

二．软硬件环境：

软件环境：Windows 11

SQL Server 2022

硬件环境：64 位操作系统 2.40GHz  256内存

三．实验设计简述：

图书管理系统主要实现图书管理员与学生之间书籍交易的功能。

其中包括图书馆内书籍的信息、学校在校学生的信息以及学生的借阅信息。

此系统功能分为面向学生和面向管理员两部分，其中学生可以进行借阅、续借、归还和查询书籍等操作;管理员可以完成书籍和学生的增加，删除和修改以及对学生，借阅、续借、归还的确认

四. 逻辑模型设计(按数据库设计方法和规范化理论得出符合3NF的逻辑模型, ER图设计,ER图转化为相应的关系模式,设计数据库的逻辑模型)

通过ER模型到关系模型的转化，可以得到如下关系模式:

(1）学生实体集转化为:关系student(SID, Sname,Sdept,Sclass,Ssex,Ste1)

(2)书籍实体集转化为:关系book (BID，Bname,TID,Bauthor, Bpub, Bdate, Bprice, Bquantity)

(3)书籍类别实体集转化为:关系 type(TID,Tname)

(4)借阅实体集转化为:关系s_bimform(SID,BID, btime, ctime, rtime)每个关系模式的主键码都用下划线标出。

对于从联系导出的关系s-bimform（借阅〉使用与之相联系的实体集的主健码作为自己的键码，必须符合外键码约束。

五. 物理模型设计（存储记录结构设计，物理文件的安排和建立索引）

（1）Student 表

列名	类型	长度	约束
SID	char	10	PRIMARY KEY
Sname	char	8
Sdept	char	20
Sclass	char	8
Ssex	char	2
Stel	char	12

(2)book表

列名	类型	长度	约束
BID	char	20	PRIMARY KEY
Bname	char	20
TID	char	20
Bauthor	char	8
Bpub	char	20
Bdate	date	-
Bprice	numeric	-
Bquantity	int	-

(3)type表

列名	类型	长度	约束
TID	char	20	PRIMARY KEY
Tname	char	20

(4)Borrowing

列名	类型	长度	约束
SID	char	10	NOT NULL
BID	char	20	NOT NULL
Btime	date
Ctime	date
Rtime	date

六．实现（数据库结构设计的程序代码，基本操作的程序代码，全部附上完整的带桌面的截图）

1.创建图书馆数据库

Create database Library

on

(

name=Library_dat,

filename='D:\Test\Library.mdf',

size=5,

filegrowth=1MB

)

log on

(

name=Library_log,

filename='D:\Test\Library.ldf',

size=2,

filegrowth=10%

)

2.建立Student表

create table student

(sid char( 10) primary key,

sname char(8),

sdept char(20),sclass char(8),ssex char(2),stel char(12))

3.建立type表

create table type

(tid char(20) primary key,tname char(20))

4.建立book表

create table book

(bid char(20)primary key,bname char(20),

tid char(20),bauthor char(8),bpub char(20),bdate date ,

bprice numeric,bquantity int ,

foreign key (tid) references type(tid))

5.建立Borrowing表

create table Borrowing(sid char(10),

bid char(20),btime date,ctime date,rtime date,

primary key (sid,bid),

foreign key (sid) references student(sid),foreign key (bid)references book(bid))

6.学生操作

当学生借书，续借，还书时，实际都只能由管理员进行操作，学生只有对所有表查询的权利。例如:

查询图书信息: sclect*

From book

Where<条件表达式>

查询本学生的借阅信息: select *

From Borrowing

Where sid=#sid;

7.图书管理员操作

(1)增加记录

(a)增加书籍类别记录

Inset

Into type(tid, tname)

Values(#tid,#tname)

(b）增加书籍记录

当买进新书时，图书管理员需要将书籍的信息录入书籍信息表，执行以下操作

Insert

Into book

Values(#bid,#bname,#tid,#bauthor,#bpub,#bdate,#bprice,#bquantity)

(c)增加学生记录

当有新学生在图书馆注册时，图书管理员需要将学生的信息录入学生信息表，执行以下操作

Insert

Into student

values(#sid,#sname,#sdept,ttsc lass,#issex,#stel)

(d)增加学生借阅记录

当学生借书时，管理员要增加学生借阅记录项，执行以下操作

Insert

Into Borrowing

Values(#sid,#bid,#btime)

(2)修改记录

(a）修改图书信息

当学生借书还书后，图书库存量都会发生变化，相应的操作如下

Update book

Set bquantity= # bquantity

Where bid=#bid

(b)修改学生信息

Update studentSet

stel=#stel

Where sid=#sid

(c)修改借阅信息

当学生续借时，应当更新续借时间。

Update Borrowing

Set ctimc=#ctime

Where sid=tsid and bid=bid

当学生还书时，应当跟新还书时间

Update Borrowing rtime=#rtime

Where sid=#sid and bid=#bid

(3)删除记录

(a)删除学生记录Delete

From studentWhere sid=#sid

(b)删除图书信息

DeleteFrom bookWhere bid-#bid

(c)删除借阅信息Delete

From Borrowing 

Where sid=#sid and bid=#bid

(d)删除书类型信息

Delete

From typeWhere tid=#tid

8.创建视图和存储过程

为了使用方便，为常用操作定义创建视图

(1）视图

当需要查询某一类别的书籍信息的时候，我们建立一个视图

Create view b_t

As

Select bid 图书ID,bname 书名,book.tid 类型ID,bauthor 作者,bpub 出版社,bdate 出版日期, 

bprice 价格, bquantity 库存量,tname 类型名

From book , type

Where book.tid=type.tid

为了使用方便,这里定义了一个视图，其功能是通过学生自己的学生id ，能够输出该学生的借书信息

Create view s_b

As

Select student.sid 学号,sname 姓名,book.bid 图书号,bname 书名,

btime 借书日期 From student,book,Borrowing

Where student.sid=Borrowing.sid and book.bid=Borrowing.bid;

9.实验数据示例：

向各个表格插入数据

insert into type

values('A001','文学类'),

('B002','理工类');



insert into book

values('1001','《活着》','A001','余华','《收获》','1992-12-06','42','100'),

('2001','《计算机网络》','B002','','《电子工业出版社》','2021-06-08','59.8','50');



insert into student

values('52230731','小邓','计算机系','23-1','女','18424921394'),

('52230777','小王','物联网系','23-2','男','1518387282');



insert into Borrowing

values('52230777','1001','2023-11-16',NULL,NULL),

('52230731','2001','2023-11-18',NULL,NULL);

通过学生自己的学生id ，能够输出该学生的借书信息

当学生借书时要更新库存量和借书信息

update Borrowing

set rtime='2023-11-16'

where sid in

(select sid from student

where student.sid='52230777');



update book

set bquantity=bquantity -1

where bid ='1001';

select * from book

用视图查询某一书名的书籍信息

select * from b_t

where 书名='《计算机网络》';

删除学号为52230777的学生的借阅信息并查看结果

delete from Borrowing

where sid='52230777';

select * from Borrowing;