CREATE TABLE `book` (
`bid` varchar(20) NOT NULL COMMENT '图书编号',
`bName` varchar(50) NOT NULL COMMENT '图书书名',
`author` varchar(50) DEFAULT NULL COMMENT '作者姓名',
`pubComp` varchar(50) DEFAULT NULL COMMENT '出版社',
`pubDate` date DEFAULT NULL COMMENT '出版日期',
`bCount` int(11) DEFAULT NULL COMMENT '现存数量',
`price` double DEFAULT NULL COMMENT '单价',
PRIMARY KEY (`bid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
CREATE TABLE `borrow` (
`rid` varchar(20) NOT NULL COMMENT '读者编号',
`nif` varchar(50) NOT NULL COMMENT '图书编号',
`lendDate` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '借阅日期',
`willDate` datetime DEFAULT NULL COMMENT '应归还日期',
`returnDate` datetime DEFAULT NULL COMMENT '实际归还日期',
PRIMARY KEY (`rid`,`nif`,`lendDate`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
CREATE TABLE `penalty` (
`rid` varchar(20) NOT NULL COMMENT '读者编号',
`bid` varchar(20) NOT NULL COMMENT '图书编号',
`pDate` datetime NOT NULL COMMENT '罚款日期',
`pType` int(11) NOT NULL COMMENT '罚款类型',
`amount` float DEFAULT NULL COMMENT '罚款金额',
PRIMARY KEY (`rid`,`bid`,`pDate`),
KEY `fk_type` (`pType`),
CONSTRAINT `fk_type` FOREIGN KEY (`pType`) REFERENCES `type` (`pType`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
CREATE TABLE `reader` (
`rid` varchar(20) NOT NULL COMMENT '读者编号',
`rName` varchar(50) NOT NULL COMMENT '读者姓名',
`lendNum` int(11) DEFAULT NULL COMMENT '已借书数量',
`rAddress` varchar(128) DEFAULT NULL COMMENT '联系地址',
PRIMARY KEY (`rid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
CREATE TABLE `type` (
`pType` int(11) NOT NULL COMMENT '罚款类型',
`class` char(10) NOT NULL COMMENT '类型',
PRIMARY KEY (`pType`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;