黄阿信的个人博客

项目地址：gitee

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

数据库设计

E-R图

五张表：

用户表（编号，姓名，性别，年龄）

 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

书籍表（编号，书名，库存）

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

书商表（编号，书商名，地址）

 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

借阅表（书籍编号，用户编号，借阅日期，归还日期，是否已归还）

 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

入库表（书商编号，书籍编号，入库数量）

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

存储过程

借阅：根据用户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 ;

归还：根据用户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 ;

入库：根据书商编号和书籍编号，将对应的书籍库存量进行修改

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功能，因此需要通过触发器限制某些属性的值的范围。

书籍数量必须是非负数

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 ;

书籍数量必须是非负数

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 ;

用户删除之前需要归还所有书籍

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 ;

书籍删除之前确保该书籍已归还完毕

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 ;

书记入库数量不能是负数

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 ;

不能随便修改入库记录中的入库数量

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	对书商表拥有所有权、入库表拥有插入权、选择权、书籍表有更新权

图书借阅管理系统

数据库设计