要么改变世界,要么适应世界

图书借阅管理系统

2020-07-03 16:49:00
125
目录

项目地址:gitee

一次数据库课程设计,系统界面用C语言,后台数据用MySQL(如果不知道如何用C语言连接MySQL,参照我的上一篇文章)。

数据库设计

  • E-R图

  • 五张表:

    1. 用户表(编号,姓名,性别,年龄)

       CREATE TABLE `users` (
         `PNO` int(11) NOT NULL,
         `PNAME` varchar(20) NOT NULL,
         `GENDER` enum('男','女') NOT NULL,
         `AGE` int(20) DEFAULT '18',
         PRIMARY KEY (`PNO`)
       ) ENGINE=InnoDB DEFAULT CHARSET=utf8
      
    2. 书籍表(编号,书名,库存)

      CREATE TABLE `books` (
         `BNO` int(11) NOT NULL,
         `BNAME` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
         `BINVENTORY` int(11) NOT NULL DEFAULT '0',
         PRIMARY KEY (`BNO`)
       ) ENGINE=InnoDB DEFAULT CHARSET=utf8
      
    3. 书商表(编号,书商名,地址)

       CREATE TABLE `booksellers` (
         `SNO` int(11) NOT NULL,
         `SNAME` varchar(20) DEFAULT NULL,
         `ADDR` varchar(50) DEFAULT NULL,
         PRIMARY KEY (`SNO`)
       ) ENGINE=InnoDB DEFAULT CHARSET=utf8
      
    4. 借阅表(书籍编号用户编号,借阅日期,归还日期,是否已归还)

       CREATE TABLE `borrowings` (
         `BNO` int(11) DEFAULT NULL,
         `PNO` int(11) DEFAULT NULL,
         `BDATE` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
         `RDATE` timestamp NULL DEFAULT '0000-00-00 00:00:00',
         `ISRETURN` tinyint(1) DEFAULT '0',
         KEY `borrowings_ibfk_1` (`PNO`),
         KEY `borrowings_ibfk_2` (`BNO`),
         CONSTRAINT `borrowings_ibfk_1` FOREIGN KEY (`PNO`) REFERENCES `users` (`PNO`) ON DELETE SET NULL ON UPDATE CASCADE,
         CONSTRAINT `borrowings_ibfk_2` FOREIGN KEY (`BNO`) REFERENCES `books` (`BNO`) ON DELETE SET NULL ON UPDATE CASCADE
       ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
      
    5. 入库表(书商编号书籍编号,入库数量)

      CREATE TABLE `warehousings` (
         `SNO` int(11) DEFAULT NULL,
         `BNO` int(11) DEFAULT NULL,
         `NUM` int(11) DEFAULT NULL,
         KEY `SNO` (`SNO`),
         KEY `BNO` (`BNO`),
         CONSTRAINT `warehousings_ibfk_1` FOREIGN KEY (`SNO`) REFERENCES `booksellers` (`SNO`) ON UPDATE CASCADE,
         CONSTRAINT `warehousings_ibfk_2` FOREIGN KEY (`BNO`) REFERENCES `books` (`BNO`) ON DELETE SET NULL ON UPDATE CASCADE
       ) ENGINE=InnoDB DEFAULT CHARSET=utf8
      
  • 存储过程

    1. 借阅:根据用户ID和书籍ID,形成借阅关系

      DELIMITER $$
      
      USE `bookbrdb`$$
      
      DROP PROCEDURE IF EXISTS `TRY_BORROW`$$
      
      CREATE DEFINER=`root`@`localhost` PROCEDURE `TRY_BORROW`(IN PNO INT, IN BNO INT)
      BEGIN
          DECLARE EXIT HANDLER FOR SQLEXCEPTION 
          BEGIN
              ROLLBACK ;
              SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '借阅失败,库存不足或者编号错误!' ;
          END ;
          START TRANSACTION ;
          UPDATE 
              books 
          SET
              BINVENTORY = (BINVENTORY - 1) 
          WHERE books.`BNO` = BNO ;
          INSERT INTO borrowings (BNO, PNO, BDATE, ISRETURN) 
          VALUES
              (BNO, PNO, NOW(), 0) ;
          COMMIT ;
      END$$
      
      DELIMITER ;
      
    2. 归还:根据用户ID和书籍ID,修改记录

      DELIMITER $$
      
      USE `bookbrdb`$$
      
      DROP PROCEDURE IF EXISTS `TRY_RETURN`$$
      
      CREATE DEFINER=`root`@`localhost` PROCEDURE `TRY_RETURN`(IN mPNO INT, IN mBNO INT)
      BEGIN
          DECLARE EXIT HANDLER FOR SQLEXCEPTION 
          BEGIN
              ROLLBACK ;
              SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '归还失败' ;
          END ;
          START TRANSACTION ;
          UPDATE 
              books,
              borrowings 
          SET
              books.`BINVENTORY` = (books.`BINVENTORY` + 1) 
          WHERE books.`BNO` = mBNO 
              AND borrowings.`BNO` = mBNO 
              AND borrowings.`PNO` = mPNO 
              AND borrowings.`ISRETURN` = 0 ;
          UPDATE 
              borrowings 
          SET
              borrowings.`ISRETURN` = 1,
              borrowings.`RDATE` = NOW() 
          WHERE borrowings.`BNO` = mBNO 
              AND borrowings.`PNO` = mPNO 
              AND borrowings.`ISRETURN` = 0 
          LIMIT 1 ;
          COMMIT ;
      END$$
      
      DELIMITER ;
      
    3. 入库:根据书商编号和书籍编号,将对应的书籍库存量进行修改

      DELIMITER $$
      
      USE `bookbrdb`$$
      
      DROP PROCEDURE IF EXISTS `PUT_IN`$$
      
      CREATE DEFINER=`root`@`localhost` PROCEDURE `PUT_IN`(IN mbno INT, IN msno INT, IN mnum INT)
      BEGIN
          DECLARE EXIT HANDLER FOR SQLEXCEPTION 
          BEGIN
              ROLLBACK ;
              SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '入库失败,编号错误或者入库数量错误' ;
          END ;
          START TRANSACTION ;
          INSERT INTO warehousings (BNO, SNO, NUM) 
          VALUES
              (mbno, msno, mnum) ;
          UPDATE 
              books 
          SET
              BINVENTORY = (BINVENTORY + mnum) 
          WHERE BNO = mbno ;
          COMMIT ;
      END$$
      
      DELIMITER ;
      
  • 触发器

    ​ 由于MySQL5.6不支持check功能,因此需要通过触发器限制某些属性的值的范围。

    1. 书籍数量必须是非负数

      DELIMITER $$
      
      USE `bookbrdb`$$
      
      DROP TRIGGER /*!50032 IF EXISTS */ `BOOK_INVENTORY_INSERT`$$
      
      CREATE
          /*!50017 DEFINER = 'root'@'localhost' */
          TRIGGER `BOOK_INVENTORY_INSERT` BEFORE INSERT ON `books` 
          FOR EACH ROW BEGIN
      IF NEW.BINVENTORY < 0 
      THEN
      SIGNAL SQLSTATE 'HY000' SET MESSAGE_TEXT = '您输入的库存量不符合现实意义:';
      END IF;
      END;
      $$
      
      DELIMITER ;
      
      DELIMITER $$
      
      USE `bookbrdb`$$
      
      DROP TRIGGER /*!50032 IF EXISTS */ `BOOK_INVENTORY_UPDATE`$$
      
      CREATE
          /*!50017 DEFINER = 'root'@'localhost' */
          TRIGGER `BOOK_INVENTORY_UPDATE` BEFORE UPDATE ON `books` 
          FOR EACH ROW 
      BEGIN
          IF NEW.BINVENTORY < 0 
          THEN SIGNAL SQLSTATE 'HY000' SET MESSAGE_TEXT = '新的的库存量不符合现实意义:' ;
          END IF ;
      END;
      $$
      
      DELIMITER ;
      
    2. 书籍数量必须是非负数

      DELIMITER $$
      
      USE `bookbrdb`$$
      
      DROP TRIGGER /*!50032 IF EXISTS */ `USER_AGE_INSERT`$$
      
      CREATE
          /*!50017 DEFINER = 'root'@'localhost' */
          TRIGGER `USER_AGE_INSERT` BEFORE INSERT ON `users` 
          FOR EACH ROW 
      BEGIN
          IF NEW.AGE < 0 OR NEW.AGE > 200 
          THEN SIGNAL SQLSTATE 'HY000' SET MESSAGE_TEXT = '户年龄不符合现实意义:' ;
          END IF ;
      END;
      $$
      
      DELIMITER ;
      
      DELIMITER $$
      
      USE `bookbrdb`$$
      
      DROP TRIGGER /*!50032 IF EXISTS */ `USER_AGE_UPDATE`$$
      
      CREATE
          /*!50017 DEFINER = 'root'@'localhost' */
          TRIGGER `USER_AGE_UPDATE` BEFORE UPDATE ON `users` 
          FOR EACH ROW BEGIN
          IF NEW.AGE < 0 
          THEN SIGNAL SQLSTATE 'HY000' SET MESSAGE_TEXT = '新的用户年龄不符合现实意义:' ;
          END IF ;
          END;
      $$
      
      DELIMITER ;
      
    3. 用户删除之前需要归还所有书籍

      DELIMITER $$
      
      USE `bookbrdb`$$
      
      DROP TRIGGER /*!50032 IF EXISTS */ `PEOPLE_DELETE`$$
      
      CREATE
          /*!50017 DEFINER = 'root'@'localhost' */
          TRIGGER `PEOPLE_DELETE` BEFORE DELETE ON `users` 
          FOR EACH ROW 
      BEGIN
          IF EXISTS 
          (SELECT 
              * 
          FROM
              borrowings 
          WHERE old.PNO = borrowings.`PNO` 
              AND borrowings.`ISRETURN` = 0) 
          THEN SIGNAL SQLSTATE 'HY000' SET MESSAGE_TEXT = '该用户仍有未归还的书籍,无法删除' ;
          END IF ;
      END;
      $$
      
      DELIMITER ;
      
    4. 书籍删除之前确保该书籍已归还完毕

      DELIMITER $$
      
      USE `bookbrdb`$$
      
      DROP TRIGGER /*!50032 IF EXISTS */ `BOOK_DELETE`$$
      
      CREATE
          /*!50017 DEFINER = 'root'@'localhost' */
          TRIGGER `BOOK_DELETE` BEFORE DELETE ON `books` 
          FOR EACH ROW BEGIN
      IF EXISTS (
      SELECT * FROM
      borrowings
      WHERE old.BNO = borrowings.`BNO` AND borrowings.`ISRETURN` = 0
      )
      THEN
      SIGNAL SQLSTATE 'HY000' SET MESSAGE_TEXT = '该书籍仍有未归还的记录,无法删除';
      END IF;
          END;
      $$
      
      DELIMITER ;
      
    5. 书记入库数量不能是负数

      DELIMITER $$
      
      USE `bookbrdb`$$
      
      DROP TRIGGER /*!50032 IF EXISTS */ `PUT_IN_STORAGE`$$
      
      CREATE
          /*!50017 DEFINER = 'root'@'localhost' */
          TRIGGER `PUT_IN_STORAGE` BEFORE INSERT ON `warehousings` 
          FOR EACH ROW BEGIN
          IF new.NUM < 0 THEN
          SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '入库量不能是负数';
          END IF;
          END;
      $$
      
      DELIMITER ;
      
    6. 不能随便修改入库记录中的入库数量

      DELIMITER $$
      
      USE `bookbrdb`$$
      
      DROP TRIGGER /*!50032 IF EXISTS */ `WAREHOUSINGS_UPDATE`$$
      
      CREATE
          /*!50017 DEFINER = 'root'@'localhost' */
          TRIGGER `WAREHOUSINGS_UPDATE` BEFORE UPDATE ON `warehousings` 
          FOR EACH ROW 
      BEGIN
          IF new.NUM != old.NUM 
          THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '入库量不能随意修改' ;
          END IF ;
      END;
      $$
      
      DELIMITER ;
      
  • 用户权限设置

    用户 权限
    root 对整个数据库bookbrdb拥有所有权
    bookAdmin 对书籍表拥有所有权、借阅表有插入、选择、更新权
    userAdmin 对用户表拥有所有权
    booksellerAdmin 对书商表拥有所有权、入库表拥有插入权、选择权、书籍表有更新权
历史评论
开始评论