图书馆表设计
一图书馆系统需求
二：图书馆数据库设计
通过对图书管理系统分析，有图书（book），图书类别（bookcatagory），书库（BookRoom）,管理员（bookAdministrator），读者（Reader）,
借阅证（borrowcard）,借阅等级，借阅（Borrow）,罚款单（Ticket）9个实体，表如下：
1：bookRoom表
Sql代码
CREATE TABLE`bookromm` (
`id` int NOT NULL AUTO_INCREMENT COMMENT '书库编号',
`name` varchar(255) CHARACTER SET utf8COLLATE utf8_czech_ci NOT NULL COMMENT '书库名称',
`address` varchar(255) CHARACTER SET utf8COLLATE utf8_czech_ci DEFAULT NULL COMMENT '书库地址',
`phone` varchar(255) CHARACTER SET utf8COLLATE utf8_danish_ci DEFAULT NULL COMMENT '联系电话',
PRIMARY KEY (`id`)
) ENGINE=InnoDBDEFAULT CHARSET=utf8mb3 COLLATE=utf8_czech_ci;
2：bookcatagory表
Sql代码
CREATE TABLE`bookcatagragory` (
`id` int NOT NULL AUTO_INCREMENT COMMENT '图书分类编号',
`name` varchar(20) NOT NULL COMMENT '图书分类名称',
`brid` int NOT NULL COMMENT '书库编号',
`demo` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `brid` (`brid`),
CONSTRAINT `brid` FOREIGN KEY (`brid`)REFERENCES `bookromm` (`id`)
) ENGINE=InnoDBDEFAULT CHARSET=utf8mb3;
3：book表
Sql代码
CREATE TABLE`book` (
`ISBN` varchar(20) COLLATE utf8_czech_ci NOTNULL COMMENT '图书ISBN号',
`catagroyid` int DEFAULT NULL COMMENT '图书分类编号',
`name` varchar(100) COLLATE utf8_czech_ci NOTNULL COMMENT '图书名称',
`publishDate` datetime DEFAULT NULL COMMENT '出版日期',
`price` decimal(10,2) DEFAULT NULL COMMENT '价格',
`StrongDate` datetime DEFAULT NULL COMMENT '入库日期',
`stockNuber` int DEFAULT NULL COMMENT '入库数量',
`inNumber` int DEFAULT NULL COMMENT '库存数量',
PRIMARY KEY (`ISBN`),
KEY `catagroyid` (`catagroyid`),
CONSTRAINT `catagroyid` FOREIGN KEY(`catagroyid`) REFERENCES `bookcatagragory` (`id`)
) ENGINE=InnoDBDEFAULT CHARSET=utf8mb3 COLLATE=utf8_czech_ci;
4：bookAdministrator
Sql代码
CREATE TABLE`bookadmin` (
`id` int NOT NULL COMMENT '工作证号码',
`name` varchar(255) COLLATE utf8_czech_ciDEFAULT NULL COMMENT '姓名',
`phone` varchar(11) COLLATE utf8_czech_ciDEFAULT NULL,
`brid` int DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `b_id` (`brid`),
CONSTRAINT `b_id` FOREIGN KEY (`brid`) REFERENCES`bookromm` (`id`)
) ENGINE=InnoDBDEFAULT CHARSET=utf8mb3 COLLATE=utf8_czech_ci;
5：Reader
Sql代码
CREATE TABLE`reader` (
`id` varchar(20) COLLATE utf8_czech_ci NOTNULL,
`name` varchar(25) COLLATE utf8_czech_ci NOTNULL COMMENT '读者姓名',
`sexx` varchar(25) COLLATE utf8_czech_ciDEFAULT NULL COMMENT '性别',
`phone` varchar(25) COLLATE utf8_czech_ciDEFAULT NULL COMMENT '电话',
`photo` varchar(25) COLLATE utf8_czech_ciDEFAULT NULL COMMENT '照片',
PRIMARY KEY (`id`)
) ENGINE=InnoDBDEFAULT CHARSET=utf8mb3 COLLATE=utf8_czech_ci;
7:borrowcard表
CREATE TABLE`borrowcard` (
`id` varchar(20) COLLATE utf8_czech_ci NOTNULL COMMENT '借阅卡号',
`rid` varchar(20) COLLATE utf8_czech_ciDEFAULT NULL COMMENT '读者编号',
`quantity` varchar(255) COLLATE utf8_czech_ciDEFAULT NULL,
`blid` int DEFAULT NULL COMMENT '借阅等级',
PRIMARY KEY (`id`),
KEY `blid` (`blid`),
CONSTRAINT `borrowcard_ibfk_1` FOREIGN KEY(`blid`) REFERENCES `borrowlever` (`id`)
) ENGINE=InnoDBDEFAULT CHARSET=utf8mb3 COLLATE=utf8_czech_ci;
8：Borrow
CREATE TABLE`borrow` (
`id` int NOT NULL,
`bcid` int DEFAULT NULL COMMENT '借阅证编号',
`ISBN` varchar(20) COLLATE utf8_czech_ciDEFAULT NULL,
`BorrowDate` datetime DEFAULT NULL COMMENT '借阅天数',
`explainDate` datetime DEFAULT NULL COMMENT '到期日期',
`dueDate` datetime DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `bcid` (`bcid`),
KEY `ISBN` (`ISBN`),
CONSTRAINT `borrow_ibfk_1` FOREIGN KEY(`bcid`) REFERENCES `bookcatagragory` (`id`),
CONSTRAINT `borrow_ibfk_2` FOREIGN KEY(`ISBN`) REFERENCES `book` (`ISBN`)
) ENGINE=InnoDBDEFAULT CHARSET=utf8mb3 COLLATE=utf8_czech_ci;
8：Ticket表
CREATE TABLE`ticket` (
`id` varchar(20) COLLATE utf8_czech_ci NOTNULL COMMENT '罚款单编号',
`bcid` int DEFAULT NULL COMMENT '借阅证编号',
`ISNB` varchar(255) COLLATE utf8_czech_ciDEFAULT NULL COMMENT '图书ISBN',
`fIneMoney` varchar(255) COLLATEutf8_czech_ci DEFAULT NULL COMMENT '罚款金额',
`finedate` datetime DEFAULT NULL COMMENT '罚款日期',
PRIMARY KEY (`id`)
) ENGINE=InnoDBDEFAULT CHARSET=utf8mb3 COLLATE=utf8_czech_ci;
