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

  1. 项目背景及需求分析

图书管理系统数据库设计

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操作的熟练度,包括创建表、数据操作以及复杂查询等,这些都是数据库管理的核心技能。

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

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

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值