数据库创建图书馆管理系统

CREATE DATABASE library
CREATE TABLE book(
bId VARCHAR(10) NOT NULL COMMENT ‘图书编号,必填项’,PRIMARY KEY(bId),
bName VARCHAR(10) NOT NULL COMMENT ‘图书书名,必填’,
author VARCHAR(10) COMMENT ‘作者姓名’,
pubComp VARCHAR(10) COMMENT ‘出版社’,
pubDate DATE COMMENT ‘出版日期’,
bCount INT COMMENT ‘现存数量’,
price DOUBLE COMMENT ‘单价’
)COMMENT ‘图书信息表’

CREATE TABLE reader(
rId VARCHAR(10) NOT NULL COMMENT ‘读者编号，主键，必填项’,PRIMARY KEY(rId),
rName VARCHAR(10) NOT NULL COMMENT ‘读者姓名，必填’,
lendNum INT COMMENT ‘已借书数量’,
rAddress VARCHAR(10) COMMENT ‘联系地址’
)COMMENT ‘读者信息表’

CREATE TABLE borrow(
rId VARCHAR(10) NOT NULL COMMENT ‘读者编号，必填’,
bId VARCHAR(10) NOT NULL COMMENT ‘图书编号，必填’,
lendDate TIMESTAMP COMMENT ‘借阅日期，默认为当前日期，必填’,
PRIMARY KEY(rId,bId,lendDate),
willDate DATE COMMENT ‘应归还日期’,
returnDate DATE COMMENT ‘归还日期’
)COMMENT ‘图书借阅表’

CREATE TABLE penalty(
rId VARCHAR(10) NOT NULL COMMENT ‘读者编号，必填’,
bId VARCHAR(10) NOT NULL COMMENT ‘图书编号，必填’,
pDate DATE NOT NULL COMMENT ‘罚款日期，必填’,
PRIMARY KEY(rId,bId,pDate),
pType ENUM(‘1’,‘2’,‘3’) COMMENT ‘罚款类型，1-延期，2-损坏，3-丢失’,
amount DOUBLE COMMENT ‘罚款金额’
)COMMENT ‘罚款记录表’