设计题目 图书馆系统的后台数据库设计
一.实验目的:
针对图书管理部门的实践调研,通过系统需求分析、数据库概念设计、逻辑设计到上机编程、调试和应用等全过程完成图书管理系统的后台数据库设计。
二.软硬件环境:
软件环境: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;