图书管理系统（数据库课程设计）

胜利的飞机

已于 2024-06-13 18:52:40 修改

阅读量298

点赞数 6

文章标签：课程设计

于 2024-05-21 21:52:08 首次发布

本文链接：https://blog.csdn.net/sbtouge/article/details/139103111

版权

一、项目背景

随着信息技术的飞速发展和知识经济时代的到来，信息管理已成为各行各业提高工作效率和服务质量的关键因素。在教育机构、公共图书馆、学校图书馆和个人藏书管理等领域，高效、准确地管理和利用图书馆资源显得尤为重要。传统的图书馆管理方式，如人工记录借阅信息、图书分类等，不仅费时费力，而且容易出错，难以满足现代信息管理的需要。因此，开发一套全面便捷的图书管理系统，对于优化图书资源配置、提高图书借阅效率、促进知识共享具有重要意义。

二、用户需求

用户登录：系统应提供用户登录界面，支持管理员通过用户名和密码进行身份验证。

图书管理：管理员能够根据图书编号、图书名称等信息查询、添加、修改或删除图书的基本信息。

读者管理：管理员根据账号、姓名查询读者基本信息，添加、修改、删除读者信息。

图书借阅：展示所有正在借阅图书的信息。

图书归还：展示所有已归还图书的信息。

三、系统设计

图书管理模块

图书录入：允许管理员添加新书信息到系统中，包括书名、作者、出版社、书号、分类号、出版日期、价格、库存数量等。
图书编辑：提供修改现有图书信息的功能，如更新图书状态（在馆/外借/丢失）、增减库存等。
图书删除：允许管理员从系统中移除不再馆藏或已报废的图书记录。
图书查询：支持多种条件查询图书，如按书名、作者、分类等快速查找图书信息。

读者管理模块

读者注册：新读者信息录入，包括姓名、联系方式、账号等。
读者信息维护：允许修改读者个人信息。
借阅权限管理：设定并调整读者的借书数量限制、借阅期限等规则。

借阅与归还处理模块

借书功能：读者可通过系统查询图书状态，发起借书请求，系统自动检测借阅权限并更新图书与读者的借阅记录。
续借处理：允许读者申请续借，延长借阅期限。
归还处理：接收图书归还，系统自动更新图书状态为可借，并计算归还日期与是否产生超期罚款。
预约管理：读者可预约未在馆的图书，系统自动排队并在图书可借时通知读者。

管理员系统

编辑用户资料：修改用户的基本信息和账户状态，如更改用户角色、更新联系方式等。
重置密码：为忘记密码的用户提供密码重置服务，或在必要时由管理员直接操作。
权限分配：细化权限管理，为用户或角色分配访问特定功能、模块或数据的权限。

逻辑结构设计

出版社（出版社名，电话，地址，邮编，电子邮箱，成立时间）

主码：出版社名

图书（书号，书名，作者，出版年份，分类，语种，出版社名）

主码：书号

外码：出版社名

副本（位置，条形码编号，新旧程度，借阅状态，借书日期，还书日期，书号，读者账号）

主码：条形码编号

外码：书号，读者账号

读者（读者账号，姓名，密码，职业，性别，管理员账号）

主码：读者账号

外码：管理员账号

借阅（读者账号，条形码编号，借书日期，还书日期）

主码：读者账号，条形码编号

管理员（管理员账号，密码，权限，联系方式）

主码：管理员账号

E-R图

物理结构设计

四、数据库的建立

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for admin
-- ----------------------------
DROP TABLE IF EXISTS `admin`;
CREATE TABLE `admin`  (
  `AdminAccount` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_as_ci NOT NULL COMMENT '账号/主码',
  `Password_admin` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_as_ci NOT NULL COMMENT '密码',
  `Privilege` enum('超级管理员','普通管理员') CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_as_ci NOT NULL COMMENT '权限',
  `Contact` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_as_ci NULL DEFAULT NULL COMMENT '联系方式',
  PRIMARY KEY (`AdminAccount`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_as_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Table structure for book
-- ----------------------------
DROP TABLE IF EXISTS `book`;
CREATE TABLE `book`  (
  `ISBN` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_as_ci NOT NULL COMMENT '书号/主码',
  `BookName` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_as_ci NOT NULL COMMENT '书名',
  `Class` enum('NULL','健康与生活','科学与技术','文学与艺术','教育与自我提升','历史与社会科学','自然与环境','旅行与探索','经济与商业','其他') CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_as_ci NULL DEFAULT NULL COMMENT '分类',
  `Language` enum('中文','英文','日文','俄文') CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_as_ci NULL DEFAULT NULL COMMENT '语种',
  `Writer` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_as_ci NULL DEFAULT NULL COMMENT '作者',
  `PublishYear` year NULL DEFAULT NULL COMMENT '出版年份',
  `PublisherName` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_as_ci NULL DEFAULT NULL COMMENT '出版社名/外码',
  `shelfcode` enum('A','B','C','D','E','F','G','H','I') CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_as_ci NULL DEFAULT NULL COMMENT '书架编号',
  `supplier_name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_as_ci NULL DEFAULT NULL COMMENT '供货商名称',
  PRIMARY KEY (`ISBN`) USING BTREE,
  INDEX `PublisherName_fk`(`PublisherName` ASC) USING BTREE,
  INDEX `supplier_name_fk`(`supplier_name` ASC) USING BTREE,
  INDEX `shelfcode_fk`(`shelfcode` ASC) USING BTREE,
  CONSTRAINT `PublisherName_fk` FOREIGN KEY (`PublisherName`) REFERENCES `publishers` (`PublisherName`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  CONSTRAINT `shelfcode_fk` FOREIGN KEY (`shelfcode`) REFERENCES `bookshelf` (`shelfcode`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  CONSTRAINT `supplier_name_fk` FOREIGN KEY (`supplier_name`) REFERENCES `supplier` (`supplier_name`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_as_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Table structure for bookcopies
-- ----------------------------
DROP TABLE IF EXISTS `bookcopies`;
CREATE TABLE `bookcopies`  (
  `BarcodeNumber` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_as_ci NOT NULL COMMENT '条形码编号/主码',
  `ISBN` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_as_ci NOT NULL COMMENT '书号/外码',
  `Condition` enum('可借出','已借出','保养中') CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_as_ci NOT NULL COMMENT '新旧程度',
  `BorrowStatus` enum('崭新出厂','略有磨损','久经沙场','破损不堪','战痕累累') CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_as_ci NOT NULL COMMENT '借阅状态',
  PRIMARY KEY (`BarcodeNumber`) USING BTREE,
  INDEX `ISBN_fk_bc`(`ISBN` ASC) USING BTREE,
  CONSTRAINT `ISBN_fk_bc` FOREIGN KEY (`ISBN`) REFERENCES `book` (`ISBN`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_as_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Table structure for bookshelf
-- ----------------------------
DROP TABLE IF EXISTS `bookshelf`;
CREATE TABLE `bookshelf`  (
  `shelfcode` enum('A','B','C','D','E','F') CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_as_ci NOT NULL,
  `location` enum('一号馆','二号馆','三号馆') CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_as_ci NULL DEFAULT NULL,
  PRIMARY KEY (`shelfcode`) USING BTREE,
  INDEX `location`(`location` ASC) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_as_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Table structure for borrow_record
-- ----------------------------
DROP TABLE IF EXISTS `borrow_record`;
CREATE TABLE `borrow_record`  (
  `BarcodeNumber` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_as_ci NOT NULL,
  `ReaderAccount` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_as_ci NOT NULL,
  `borrorwdate` datetime(6) NOT NULL,
  `returndate` datetime(6) NULL DEFAULT NULL,
  INDEX `borrow_record_bookcopies_BarcodeNumber_fk`(`BarcodeNumber` ASC) USING BTREE,
  INDEX `ReaderAccount_fk`(`ReaderAccount` ASC) USING BTREE,
  CONSTRAINT `borrow_record_bookcopies_BarcodeNumber_fk` FOREIGN KEY (`BarcodeNumber`) REFERENCES `bookcopies` (`BarcodeNumber`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  CONSTRAINT `borrow_record_readers_ReaderAccount_fk` FOREIGN KEY (`ReaderAccount`) REFERENCES `readers` (`ReaderAccount`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_as_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Table structure for publishers
-- ----------------------------
DROP TABLE IF EXISTS `publishers`;
CREATE TABLE `publishers`  (
  `PublisherName` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_as_ci NOT NULL COMMENT '出版社名/主码',
  `PhoneNumber` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_as_ci NULL DEFAULT NULL COMMENT '电话',
  `Address` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_as_ci NOT NULL COMMENT '地址',
  `PostalCode` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_as_ci NOT NULL COMMENT '邮编',
  `EmailAddress` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_as_ci NULL DEFAULT NULL COMMENT '电子邮箱',
  `EstablishmentDate` year NOT NULL COMMENT '成立时间',
  PRIMARY KEY (`PublisherName`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_as_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Table structure for readers
-- ----------------------------
DROP TABLE IF EXISTS `readers`;
CREATE TABLE `readers`  (
  `ReaderAccount` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_as_ci NOT NULL COMMENT '读者账号/主码',
  `Name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_as_ci NOT NULL COMMENT '姓名',
  `Password_readers` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_as_ci NOT NULL COMMENT '密码',
  `Occupation` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_as_ci NULL DEFAULT NULL COMMENT '职业',
  `Gender` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_as_ci NULL DEFAULT NULL COMMENT '性别',
  `AdminAccount` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_as_ci NOT NULL COMMENT '管理员账号/外码',
  PRIMARY KEY (`ReaderAccount`) USING BTREE,
  INDEX `AdminAccount_fk`(`AdminAccount` ASC) USING BTREE,
  CONSTRAINT `AdminAccount_fk` FOREIGN KEY (`AdminAccount`) REFERENCES `admin` (`AdminAccount`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_as_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Table structure for supplier
-- ----------------------------
DROP TABLE IF EXISTS `supplier`;
CREATE TABLE `supplier`  (
  `supplier_name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_as_ci NOT NULL,
  `contact information` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_as_ci NULL DEFAULT NULL,
  PRIMARY KEY (`supplier_name`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_as_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- View structure for bookinfo
-- ----------------------------
DROP VIEW IF EXISTS `bookinfo`;
CREATE ALGORITHM = UNDEFINED SQL SECURITY DEFINER VIEW `bookinfo` AS select `book`.`BookName` AS `BookName`,`bookcopies`.`BarcodeNumber` AS `BarcodeNumber`,`book`.`Class` AS `Class`,`book`.`Language` AS `Language`,`bookcopies`.`Condition` AS `Condition`,`bookcopies`.`BorrowStatus` AS `BorrowStatus`,`book`.`ISBN` AS `ISBN` from (`bookcopies` join `book`) where (`bookcopies`.`ISBN` = `book`.`ISBN`);

-- ----------------------------
-- View structure for 借阅记录总览视图
-- ----------------------------
DROP VIEW IF EXISTS `借阅记录总览视图`;
CREATE ALGORITHM = UNDEFINED SQL SECURITY DEFINER VIEW `借阅记录总览视图` AS select `borrow_record`.`ReaderAccount` AS `ReaderAccount`,`borrow_record`.`BarcodeNumber` AS `BarcodeNumber`,`book`.`BookName` AS `BookName`,`bookcopies`.`ISBN` AS `ISBN`,`borrow_record`.`borrorwdate` AS `borrorwdate`,`borrow_record`.`returndate` AS `returndate` from ((`bookcopies` join `borrow_record` on((`bookcopies`.`BarcodeNumber` = `borrow_record`.`BarcodeNumber`))) join `book` on((`bookcopies`.`ISBN` = `book`.`ISBN`)));

-- ----------------------------
-- View structure for 图书数据总览视图
-- ----------------------------
DROP VIEW IF EXISTS `图书数据总览视图`;
CREATE ALGORITHM = UNDEFINED SQL SECURITY DEFINER VIEW `图书数据总览视图` AS select `book`.`BookName` AS `BookName`,`book`.`ISBN` AS `ISBN`,`book`.`Language` AS `Language`,`book`.`Class` AS `Class`,`book`.`Writer` AS `Writer`,`book`.`shelfcode` AS `shelfcode`,-(-(count(`bookcopies`.`BarcodeNumber`))) AS `copy_counts`,`book`.`PublisherName` AS `PublisherName`,`book`.`PublishYear` AS `PublishYear`,`book`.`supplier_name` AS `supplier_name` from (`book` join `bookcopies` on((`book`.`ISBN` = `bookcopies`.`ISBN`))) group by `book`.`BookName`,`book`.`ISBN`;

-- ----------------------------
-- View structure for 读者和管理员联合表
-- ----------------------------
DROP VIEW IF EXISTS `读者和管理员联合表`;
CREATE ALGORITHM = UNDEFINED SQL SECURITY DEFINER VIEW `读者和管理员联合表` AS select `readers`.`Name` AS `Name`,`readers`.`ReaderAccount` AS `ReaderAccount`,`readers`.`Password_readers` AS `Password_readers`,`readers`.`Gender` AS `Gender`,`readers`.`Occupation` AS `Occupation`,`admin`.`AdminAccount` AS `AdminAccount`,`admin`.`Password_admin` AS `Password_admin`,`admin`.`Privilege` AS `Privilege`,`admin`.`Contact` AS `Contact` from (`admin` join `readers` on((`admin`.`AdminAccount` = `readers`.`AdminAccount`)));

-- ----------------------------
-- Procedure structure for borrow
-- ----------------------------
DROP PROCEDURE IF EXISTS `borrow`;
delimiter ;;
CREATE PROCEDURE `borrow`(IN account varchar(50), IN sBarcodeNumber varchar(50))
begin
    update bookcopies set `Condition`='已借出' where BarcodeNumber=sBarcodeNumber and `Condition`='可借出';
    insert into borrow_record(borrorwdate,ReaderAccount,BarcodeNumber) values(now(),account,sBarcodeNumber);
end
;;
delimiter ;

-- ----------------------------
-- Procedure structure for _return
-- ----------------------------
DROP PROCEDURE IF EXISTS `_return`;
delimiter ;;
CREATE PROCEDURE `_return`(IN account varchar(50), IN sBarcodeNumber varchar(50))
begin
    update bookcopies set `Condition`='可借出'
    where BarcodeNumber=sBarcodeNumber and `Condition`='已借出' and BarcodeNumber in (select BarcodeNumber from borrow_record
        where ReaderAccount=account);
    update borrow_record set returndate=now() where ReaderAccount=account and BarcodeNumber=sBarcodeNumber;
end
;;
delimiter ;

-- ----------------------------
-- Triggers structure for table borrow_record
-- ----------------------------
DROP TRIGGER IF EXISTS `before_insert_borrow_record`;
delimiter ;;
CREATE TRIGGER `before_insert_borrow_record` BEFORE INSERT ON `borrow_record` FOR EACH ROW BEGIN
    UPDATE `bookcopies`
    SET `Condition` = '已借出'
    WHERE `BarcodeNumber` = NEW.BarcodeNumber AND `Condition` = '可借出';
END
;;
delimiter ;

-- ----------------------------
-- Triggers structure for table borrow_record
-- ----------------------------
DROP TRIGGER IF EXISTS `before_insert_borrow_record_time`;
delimiter ;;
CREATE TRIGGER `before_insert_borrow_record_time` BEFORE INSERT ON `borrow_record` FOR EACH ROW BEGIN
    SET NEW.borrorwdate = NOW();
END
;;
delimiter ;

SET FOREIGN_KEY_CHECKS = 1;

五、功能实现

查看借阅情况

还书

借书

胜利的飞机

关注

6
点赞
踩
3

收藏

觉得还不错? 一键收藏
0
评论
图书管理系统（数据库课程设计）

在教育机构、公共图书馆、学校图书馆和个人藏书管理等领域，高效、准确地管理和利用图书馆资源显得尤为重要。传统的图书馆管理方式，如人工记录借阅信息、图书分类等，不仅费时费力，而且容易出错，难以满足现代信息管理的需要。因此，开发一套全面便捷的图书管理系统，对于优化图书资源配置、提高图书借阅效率、促进知识共享具有重要意义。图书管理：管理员能够根据图书编号、图书名称等信息查询、添加、修改或删除图书的基本信息。读者管理：管理员根据账号、姓名查询读者基本信息，添加、修改、删除读者信息。使用MySQL作为后台数据库。
复制链接

扫一扫