数据库原理与实践期末项目_学生可以直接通过借阅终端来查阅书籍信息,同时也可以查阅自己的借阅信息。 (2)当-CSDN博客

项目背景及需求分析

图书管理系统数据库设计

1.1项目背景

在知识快速发展的当下，图书馆作为知识传播的重要场所，传统图书管理模式弊端渐显。人工操作效率低、易出错，难以适应规模和读者数量增长。

信息技术发展促使图书管理走向数字化。设计精良的图书管理系统数据库，能整合业务流程，方便工作人员管理，为读者提供便捷服务。

1.2需求分析

学生可以直接通过借阅终端来查阅书籍信息，同时也可以查阅自己的借阅信息。

当学生需要借阅书籍时，通过账号密码登陆借阅系统，借阅系统处理学生的借阅，同时修改图书馆保存的图书信息，修改被借阅的书籍是否还有剩余，同时更新学生个人的借阅信息。

学生借阅图书之前需要将自己的个人信息注册，登陆时对照学生信息。

学生直接归还图书，根据图书编码修改借阅信息。

管理员登陆管理系统后，可以修改图书信息，增加或者删除图书信息。

管理员可以注销学生信息。

管理员功能：登录管理系统后，能修改图书信息（增删、编辑），还可注销学生信息。

2、概念结构设计

2.1抽象出系统实体

学生（Student）：包含学号、姓名、性别、年龄、专业、年级和诚信级等属性，用于唯一标识和管理学生个人信息，在借书、超期处罚等业务中作为关键关联对象。

图书（Book）：通过编号、名称、作者、出版社、在架状态、分类和登记日期等属性，对每本图书进行详细描述，是借书业务的核心对象之一。

图书分类（Book Sort）：以类型编号和名称对图书进行分类，为图书管理和检索提供分类依据，与图书表通过分类编号建立关联。

借书记录（Borrow Record）：由学生编号、图书编号、借书时间和预期归还时间构成，记录学生借书的具体情况，反映学生与图书之间的借阅关系。

超期处罚（Overdue Penalty）：涉及学生编号、图书编号、超期天数和处罚金额，用于记录学生借书超期的相关处罚信息，与学生表和图书表相关联。

管理员（Manager）：通过编号、姓名、年龄和电话等属性，对管理图书馆系统的管理员进行信息记录，负责系统的各项管理操作。

2.2局部E-R图

描述一个图书馆管理系统，其中学生借阅图书，管理员负责管理图书和学生的借阅活动。管理实体则是一个抽象的概念，用来表示这些操作和关系。图2-1流程图展示了学生借阅图书和管理员管理图书的基本流程关系。

图示描述已自动生成

图2-1

2.3全局E-R图

展示一个图书馆管理系统的数据库结构，图2-2描述了各个实体之间的关系以及它们的属性。学生可以借阅图书、归还图书、可能受到处罚，这些操作都在数据库中记录相关信息。管理员负责管理这些操作。图书类别用于对图书进行分类管理。

图示描述已自动生成

图2-2

3、逻辑结构设计

3.1简单说明

图书馆管理系统包含 6 张表，涵盖学生、图书、借书、处罚及管理员等信息管理。

student 表存储学生学号、姓名、性别等基本信息，诚信级可用于管理借阅权限。book 表记录图书编号、名称、作者等详情，通过 book_sort 关联 book_sort 表以实现分类管理。book_sort 表明确了图书分类编号与名称。

borrow 表记录学生借书行为，通过 student_id 和 book_id 分别关联学生与图书表，记录借书与预期归还时间。ticket 表针对借书超期情况，记录超期天数与处罚金额，同样通过 student_id 和 book_id 关联相关信息。manager 表管理管理员信息，用于系统管理操作。

这些表相互关联，构建起完整的图书馆管理数据体系，为实现借阅、超期管理等功能提供了数据支持。

3.2关系模式

根据E-R图，设计出第三范式的关系模式

Student(stu_id, stu_name, stu_sex, stu_age, stu_pro, stu_grade, stu_integrity)

Book (book_id, book_name, book_author, book_pub, book_num, book_sort, book_record)

BookSort (sort_id, sort_name)

Borrow (student_id, book_id, borrow_date, expect_return_date)

Ticket TICKET(student_id, book_id, over_date, ticket_fee)

Manager (manager_id, manager_name, manager_age, manager_phone)

3.3表结构

Student表的表结构

列名	数据类型	长度	小数位数	是否允许NULL值	说明
stu_id	int	N/A	N/A	否	唯一学号
stu_name	varchar	255		否	学生姓名
stu_sex	char	1		否	学生性别
stu_age stu_pro stu_grade stu_integrity	int varchar varchar int	N/A 255 255 N/A		否否否否	学生年龄学生专业学生年级诚信默认1

Book表的表结构

列名	数据类型	长度	小数位数	是否允许NULL值	说明
book_id	int	N/A		否	唯一序号
book_name	varchar	255		否	图书名称
book_author book_pub book_num book_sort book_record	Varchar Varchar Int Varchar datetime	255 255 N/A 255 N/A		否否否否否	图书作者出版社是否上架图书分类登记日期

Book_Sort表的表结构

列名	数据类型	长度	小数位数	是否允许NULL值	说明
sort_id	char	10		否	类型编号
sort_name	varchar	255		否	类型名称

Borrow表的表结构

列名	数据类型	长度	小数位数	是否允许NULL值	说明
student_id	varchar	255		否	学生编号
book_id	varchar	255		否	书籍编号
borrow_date expect_return_date	Datetime datetime	N/A 10	2	否否	借书时间预还时间

Ticket表的表结构

列名	数据类型	长度	小数位数	是否允许NULL值	说明
student_id	varchar	255		否	学生编号
book_id	varchar	255		否	书籍编号
over_date ticket_fee	Int float	N/A N/A		否否	超期天数处罚金额

Manager表的表结构

列名	数据类型	长度	小数位数	是否允许NULL值	说明
manager_id	varchar	255		否	管理员编号
manager_name	varchar	255		否	管理员姓名
manager_age manager_phone	Varchar varchar	3 20		否否	管理员年龄管理员电话

4、创建数据库及SQL相关操作

4.1 创建数据库

建立数据库，数据库名称自取，设置字符集为UTF-8,排序规则为utf8_general_ci。

代码：

CREATE DATABASE library_system

CHARACTER SET utf8

COLLATE utf8_general_ci;

截图结果：

文本描述已自动生成

4.1.1 创建表

根据需要，在你们创建的数据库中创建你们的每个数据表。

表一

代码：

CREATE TABLE Student (

stu_id VARCHAR(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci PRIMARY KEY,

stu_name VARCHAR(50) NOT NULL,

stu_sex CHAR(1) NOT NULL,

stu_age INT NOT NULL,

stu_pro VARCHAR(50) NOT NULL,

stu_grade VARCHAR(10) NOT NULL,

stu_integrity INT NOT NULL DEFAULT 1

);

结果截图：

表二

代码：

CREATE TABLE Book (

book_id VARCHAR(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci PRIMARY KEY,

book_name VARCHAR(100) NOT NULL,

book_author VARCHAR(50) NOT NULL,

book_pub VARCHAR(50) NOT NULL,

book_num INT NOT NULL,

book_sort VARCHAR(10) NOT NULL,

book_record DATETIME

);

结果截图：

文本中度可信度描述已自动生成

表三

代码：

CREATE TABLE BookSort (

sort_id CHAR(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci PRIMARY KEY,

sort_name VARCHAR(50) NOT NULL

);

结果截图：

表四

代码：

CREATE TABLE Borrow (

student_id VARCHAR(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,

book_id VARCHAR(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,

borrow_date DATETIME,

expect_return_date DATETIME,

PRIMARY KEY (student_id, book_id),

FOREIGN KEY (student_id) REFERENCES Student(stu_id),

FOREIGN KEY (book_id) REFERENCES Book(book_id)

);

结果截图：

文本, 信件描述已自动生成

表五

代码：

CREATE TABLE Ticket (

student_id VARCHAR(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,

book_id VARCHAR(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,

over_date INT,

ticket_fee DECIMAL(10, 2),

PRIMARY KEY (student_id, book_id),

FOREIGN KEY (student_id) REFERENCES Student(stu_id),

FOREIGN KEY (book_id) REFERENCES Book(book_id)

);

结果截图：

文本, 信件描述已自动生成

表六

代码：

CREATE TABLE Manager (

manager_id VARCHAR(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci PRIMARY KEY,

manager_name VARCHAR(50) NOT NULL,

manager_age INT NOT NULL,

manager_phone VARCHAR(15) NOT NULL

);

结果截图：

文本中度可信度描述已自动生成

4.1.2 添加记录

向每个表插入10条记录。

Student表一

代码：

INSERT INTO Student (stu_id, stu_name, stu_sex, stu_age, stu_pro, stu_grade, stu_integrity)

VALUES

('STU001', '林晓妍', '女', 20, '英语', '2023 级', 1),

('STU002', '陈宇轩', '男', 21, '会计学', '2022 级', 1),

('STU003', '苏悦', '女', 20, '法学', '2023 级', 1),

('STU004', '刘泽恺', '男', 22, '临床医学', '2021 级', 1),

('STU005', '叶诗涵', '女', 20, '教育学', '2023 级', 1),

('STU006', '张逸飞', '男', 21, '土木工程', '2022 级', 1),

('STU007', '沈梦琪', '女', 22, '环境科学', '2021 级', 1),

('STU008', '王梓豪', '男', 20, '美术学', '2023 级', 1),

('STU009', '李婉晴', '女', 21, '音乐表演', '2022 级', 1),

('STU010', '赵睿', '男', 22, '体育教育', '2021 级', 1);

结果截图：

一些文字和图片的手机截图描述已自动生成

Book表二

代码：

INSERT INTO Book (book_id, book_name, book_author, book_pub, book_num, book_sort, book_record)

VALUES

('BK001', '《简·爱》', '夏洛蒂·勃朗特', '上海译文出版社', 1, '文学名著', '2019-05-12'),

('BK002', '《经济学原理》', 'N·格里高利·曼昆', '北京大学出版社', 1, '经济管理', '2020-03-28'),

('BK003', '《中国法制史》', '曾宪义', '北京大学出版社', 1, '法律', '2021-07-15'),

('BK004', '《人体解剖学图谱》', '王兴海', '人民卫生出版社', 1, '医学', '2022-02-20'),

('BK005', '《教育心理学》', '陈琦、刘儒德', '高等教育出版社', 1, '教育', '2020-11-09'),

('BK006', '《建筑力学》', '张克纯', '中国建筑工业出版社', 1, '建筑工程', '2021-09-30'),

('BK007', '《环境生态学》', '程胜高', '化学工业出版社', 1, '环境科学', '2022-04-18'),

('BK008', '《素描基础教程》', '王磊', '人民美术出版社', 1, '艺术', '2020-08-25'),

('BK009', '《声乐教程》', '徐朗', '上海音乐出版社', 1, '艺术', '2021-06-11'),

('BK010', '《运动生理学》', '邓树勋', '高等教育出版社', 1, '体育', '2022-05-07');

结果截图：

一些文字和图片描述已自动生成

BookSort表三

代码：

INSERT INTO BookSort (sort_id, sort_name)

VALUES

('L001', '文学名著'),

('E001', '经济管理'),

('L002', '法律'),

('M001', '医学'),

('E002', '教育'),

('A001', '建筑工程'),

('E003', '环境科学'),

('A002', '艺术'),

('S001', '体育'),

('T001', '工具类');

结果截图：

表格低可信度描述已自动生成

Borrow表四

代码：

INSERT INTO Borrow (student_id, book_id, borrow_date, expect_return_date)

VALUES

('STU001', 'BK001', '2023-11-01', '2023-11-30'),

('STU002', 'BK002', '2023-11-02', '2023-12-02'),

('STU003', 'BK003', '2023-11-03', '2023-12-03'),

('STU004', 'BK004', '2023-11-04', '2023-12-04'),

('STU005', 'BK005', '2023-11-05', '2023-12-05'),

('STU006', 'BK006', '2023-11-06', '2023-12-06'),

('STU007', 'BK007', '2023-11-07', '2023-12-07'),

('STU008', 'BK008', '2023-11-08', '2023-12-08'),

('STU009', 'BK009', '2023-11-09', '2023-12-09'),

('STU010', 'BK010', '2023-11-10', '2023-12-10');

结果截图：

表格描述已自动生成

Ticket表五

代码：

INSERT INTO Ticket (student_id, book_id, over_date, ticket_fee)

VALUES

('STU001', 'BK001', 3, 15.00),

('STU002', 'BK002', 2, 10.00),

('STU003', 'BK003', 4, 20.00),

('STU004', 'BK004', 1, 5.00),

('STU005', 'BK005', 5, 25.00),

('STU006', 'BK006', 3, 15.00),

('STU007', 'BK007', 2, 10.00),

('STU008', 'BK008', 4, 20.00),

('STU009', 'BK009', 1, 5.00),

('STU010', 'BK010', 3, 15.00);

结果截图：

表格描述已自动生成

Manager表六

代码：

INSERT INTO Manager (manager_id, manager_name, manager_age, manager_phone)

VALUES

('MG001', '吴迪', 32, '13912345678'),

('MG002', '田甜', 29, '13898765432'),

('MG003', '宋阳', 35, '13678901234'),

('MG004', '刘悦', 28, '13711122233'),

('MG005', '张峰', 31, '13555666777'),

('MG006', '周敏', 30, '13477788899'),

('MG007', '孙杰', 33, '13299988877'),

('MG008', '赵琳', 27, '13188877766'),

('MG009', '陈辉', 34, '13066655544'),

('MG010', '杨雪', 36, '18955544433');

结果截图：

文本描述已自动生成

4.1.3 修改记录

表一

代码：

UPDATE Student

SET stu_sex = '男',

stu_grade = '2022 级'

WHERE stu_id = 'STU003';

结果截图：

文本描述已自动生成

4.2 删除记录

删除book表中的一条记录

代码：

DELETE FROM Book

WHERE book_id = 'B003';

结果截图：

文本低可信度描述已自动生成

4.3查询问题及查询结果

4.3.1 选择列

自行设计查询单表中的几列，并用别名代替。

代码：

SELECT

stu_name AS 学生姓名,

stu_pro AS 所学专业,

stu_grade AS 所在年级

FROM

Student;

结果截图：

文字图案中度可信度描述已自动生成

4.3.2函数使用

计算满足条件的平均值，最大值，最小值。

代码：

SELECT

AVG(stu_age) AS 平均年龄,

MAX(stu_age) AS 最大年龄,

MIN(stu_age) AS 最小年龄

FROM

Student;

结果截图：

4.3.3 LIKE子句

用LIKE子句实现模糊查询

说明实现的功能：查询姓名中包含 “李” 字的学生信息

代码：

SELECT *

FROM Student

WHERE stu_name LIKE '%李%';

结果截图：

表格描述已自动生成

4.3.4 条件查询

用给定条件，查询结果

说明实现的功能：查询在 Book 表中查找图书分类为 “文学名著” 的所有图书信息。

代码：

SELECT *

FROM Book

WHERE book_sort = '文学名著';

结果截图：

图片包含表格描述已自动生成

4.4.5多表查询

至少牵扯两个表，根据给定条件，查询结果

说明实现的功能：查询所有有超期罚款记录的学生姓名、所借图书名称及罚款金额

代码：

SELECT

s.stu_name,

b.book_name,

t.ticket_fee

FROM

Student s

JOIN

Ticket t ON s.stu_id = t.student_id

JOIN

Book b ON t.book_id = b.book_id;

结果截图：

一些文字和图片的手机截图描述已自动生成

4.4.6子查询

单行子查询举例

说明实现的功能：查询比 “林晓妍” 年龄大的学生信息

代码：

SELECT *

FROM Student

WHERE stu_age > (

SELECT stu_age

FROM Student

WHERE stu_name = '林晓妍'

);

结果截图：

表格描述已自动生成

多行子查询举例（用in，any,all任意一个均可）

说明实现的功能：查询借过 “文学名著” 分类图书的学生信息

代码：

SELECT *

FROM Student

WHERE stu_id IN (

SELECT student_id

FROM Borrow

WHERE book_id IN (

SELECT book_id

FROM Book

WHERE book_sort = '文学名著'

)

);

结果截图：

表格描述已自动生成

4.5建立视图

建立一个用户user01，给user01账户授予建立视图的权限。

代码：CREATE USER 'user01'@'localhost' IDENTIFIED BY 'password';

结果截图：

4.5.1 定义视图

自定义一个视图，要求实现多表查询。

代码：

文本描述已自动生成

查询自定义的视图代码：SELECT * FROM student_book_borrow_view;

结果截图：

一些文字和图案中度可信度描述已自动生成

4.5.2利用视图插入数据

创建简单视图代码：

CREATE OR REPLACE VIEW StudentView AS

SELECT stu_id, stu_name, stu_sex, stu_age, stu_pro, stu_grade, stu_integrity

FROM Student;

利用视图向表中插入一条记录代码：

INSERT INTO StudentView (stu_id, stu_name, stu_sex, stu_age, stu_pro, stu_grade, stu_integrity)

VALUES ('STU011', '王小明', '男', 19, '计算机科学', '2023 级', 1);

查询插入的记录代码：

SELECT * FROM StudentView WHERE stu_id = 'STU011';

查询结果截图：

图片包含表格描述已自动生成

删除插入的结果代码：

DELETE FROM Student WHERE stu_id = 'STU011';

删除结果截图：

5、建立存储过程及函数

5.1创建存储过程

创建一个存储过程，该存储过程实现输入某一个参数可以返回对应行上另外一个参数的值（如给定学号，返回选修课程信息等）。

代码：

DELIMITER $$

CREATE PROCEDURE GetBorrowedBooksByStudentId(IN stu_id VARCHAR(10))

BEGIN

SELECT

br.book_id,

bk.book_name,

br.borrow_date,

br.expect_return_date

FROM

Borrow br

JOIN

Book bk ON br.book_id = bk.book_id

WHERE

br.student_id = stu_id;

END $$

DELIMITER ;

执行存储过程代码：

CALL GetBorrowedBooksByStudentId('STU001');

结果截图：

图片包含表格描述已自动生成

5.2存储过程应用

创建一个函数过程，实现统计功能（统计个数或平均值等）。

代码：

DELIMITER $$

CREATE FUNCTION GetAverageStudentAge()

RETURNS DECIMAL(5,2)

READS SQL DATA

BEGIN

DECLARE avg_age DECIMAL(5,2);

SELECT AVG(stu_age) INTO avg_age

FROM Student;

RETURN avg_age;

END $$

DELIMITER ;

执行函数过程代码：

SELECT GetAverageStudentAge() AS AverageAge;

结果截图：

文本中度可信度描述已自动生成

6、建立触发器

在数据库的Student表中，定义一个触发器，当一个记录被删除时，把学生的主键添加到delStudent表中。

创建空表代码：

CREATE TABLE delStudent (

stu_id VARCHAR(10) PRIMARY KEY

);

创建触发器代码：

DELIMITER $$

CREATE TRIGGER after_student_delete

AFTER DELETE ON Student

FOR EACH ROW

BEGIN

INSERT INTO delStudent (stu_id)

VALUES (OLD.stu_id);

END $$

DELIMITER ;

删除数据代码：

DELETE FROM Borrow WHERE student_id = 'STU001';

DELETE FROM Ticket WHERE student_id = 'STU001';

DELETE FROM Student WHERE stu_id = 'STU001';

查看delStudent 表数据：

SELECT * FROM delStudent;

结果截图：

图表描述已自动生成

7、项目设计总结

在图书管理系统数据库设计项目中，我深刻体会到需求分析的重要性。通过详细分析，我们能够明确系统的功能和用户需求，从而在设计数据库时准确地定义实体和关系，确保支持所有必要功能。使用E-R图让我更直观地理解实体、属性及其关系，有助于在实际设计前理清思路，避免遗漏。

设计符合第三范式的关系模式是关键，它帮助我理解如何通过规范化减少数据冗余并提高数据一致性。在实施过程中，我提升了SQL操作的熟练度，包括创建表、数据操作以及复杂查询等，这些都是数据库管理的核心技能。

视图和存储过程的应用让我看到简化复杂查询和提升系统性能的可能性，尤其是在处理大型数据库时。触发器的使用则展示了自动化操作和维护数据完整性的有效方法，通过实践，我掌握了其创建与应用。

在整个项目中，我也提升了调试和故障排除能力，学会更高效地解决问题。这些经验不仅加深了我对数据库设计和管理的理解，还为我未来处理更复杂的项目奠定了坚实基础。

数据库原理与实践期末项目

1.1项目背景

1.2需求分析

2、 概念结构设计

2.1抽象出系统实体

2.2局部E-R图

2.3全局E-R图

3、 逻辑结构设计

3.1简单说明

3.2关系模式

3.3表结构

4、 创建数据库及SQL相关操作

4.1 创建数据库

4.1.1 创建表

4.1.2 添加记录

4.1.3 修改记录

4.2 删除记录

4.3查询问题及查询结果

4.3.1 选择列

4.3.2函数使用

4.3.3 LIKE子句

4.3.4 条件查询

4.4.5多表查询

4.4.6子查询

4.5建立视图

4.5.1 定义视图

4.5.2利用视图插入数据

5、建立存储过程及函数

5.1创建存储过程

5.2存储过程应用

6、建立触发器

7、项目设计总结

2、概念结构设计

3、逻辑结构设计

4、创建数据库及SQL相关操作