【黄啊码】快毕业了？毕业设计没着落？这份计算机系的毕业设计你一定要收藏好

drop database  if  exists library;
CREATE DATABASE `library` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
USE library;
-- 书籍信息
CREATE TABLE book
(
    bookNo varchar(10) primary key,
    bookName varchar(20)not null ,
    importDate date not null ,
    inLibStatus enum('available','unavailable','locked') default 'available', 
    bookPrice float(2) not null ,
    frequency int not null ,
    category varchar(20) not null
);
 
-- 人员信息
CREATE TABLE admin
(
    adminno varchar(15) primary key,
    adminname varchar(20) not null,
    password varchar(10) not null,
    level enum('reader','normal', 'super')
);
 
-- 读者信息
CREATE TABLE reade
(
    readerNo varchar(15) primary key,
    readerName varchar(20) not null,
    password varchar(10) not null
);
 
-- 借阅信息
CREATE TABLE borrowBook
(
    serialNumber int primary key AUTO_INCREMENT,
    readerNo varchar(15) not null,
    bookNo varchar(10) not null,
    borrowDate date not null,
    foreign key (readerNo) references reader(readerNo),
    foreign key (bookNo) references book(bookNo)
);
 
-- 归还信息
CREATE TABLE returnBook
(
    serialNumber int primary key,
    readerNo varchar(15) not null,
    bookNo varchar(10) not null,
    returnDate date not null,
    foreign key (readerNo) references reader(readerNo),
    foreign key (bookNo) references book(bookNo),
    foreign key (serialNumber) references borrowBook(serialNumber)
);
 
-- 罚款信息
CREATE TABLE fine
(
    serialNumber int primary key,
    readerNo varchar(15) not null,
    bookNo varchar(10) not null,
    fine_money float(2) check (fine_money>=0),
    lossResult varchar(20) not null,
    foreign key (readerNo) references reader(readerNo),
    foreign key (bookNo) references book(bookNo)
);
 
-- 遗失信息
CREATE TABLE loss
(
    bookNo varchar(10) primary key,
    lossResult enum('normal','outdated','broken','others','lost'),
    recordDate date not null,
    foreign key (bookNo) references book(bookNo)
);
 
CREATE INDEX book_IND on book (bookNo);
CREATE INDEX reader_IND on reader (readerNo);
 
-- 初始化数据
INSERT INTO `admin` (`adminno`,`adminname`, `password`, `level`) VALUES ('20151000001', '吴彦祖', 'pwdpwd', 'reader'),('normal1', '管理员1', 'pwdpwd', 'normal'),('normal2', '管理员2', 'pwdpwd', 'normal'),('normal3', '管理员3', 'pwdpwd', 'normal'), ('super0', '超级管理员', 'superpwd', 'super'),('20151000002', '陈冠希', 'pwdpwd', 'reader'), ('20151000003', '彭于晏', 'pwdpwd', 'reader');
INSERT INTO `reader` (`readerNo`, `readerName`, `password`) VALUES ('20151000001', '吴彦祖', 'pwdpwd'), ('20151000002', '陈冠希', 'pwdpwd'), ('20181000003', '彭于晏', 'pwdpwd');
INSERT INTO `book` (`bookNo`, `bookName`, `importDate`, `inLibStatus`, `bookPrice`, `frequency`, `category`) VALUES ('book1', '老人与海','2020-11-28', 'available','40', '0', '英文'), ('book2', '童年','2020-11-23', 'available','43', '0', '英文'), ('book3', '巴黎圣母院','2020-11-23', 'available','44', '0', '法语'), ('book4', '红与黑','2020-11-28', 'available','40', '0', '英文'), ('book5', '悲惨世界','2020-11-23', 'available','43', '0', '法语'), ('book6', '约翰·克利斯朵夫','2020-11-23', 'available','52', '0', '日语'),('book7', '教父1','2020-11-28', 'available','40', '0', '英文'), ('book8', '教父2','2020-11-23', 'available','43', '0', '英文'), ('book9', '教父3','2020-11-23', 'available','44', '0', '英文');