图书管理数据库系统设计

第1关：数据库表设计 - 图书表

#请在此添加实现代码
########## Begin ##########
#在library_db库中创建books表
use library_db;
create table books(
    bookId int(11) primary key not null,
    bookName varchar(255) not null,
    publicationDate datetime not null,
    publisher varchar(255) not null,
    bookrackId int(11) not null,
    roomId int(11) not null
);


########## End ##########

第2关：数据库表设计 - 读者表

#请在此添加实现代码
########## Begin ##########
#在library_db库中创建reader表
use library_db;
create table reader(
    borrowBookId int(11) primary key,
    name varchar(20) not null,
    age int(11) not null,
    sex varchar(2) not null,
    address varchar(255) not null
);


########## End ##########

 第3关：数据库表设计 - 关联表

#请在此添加实现代码
########## Begin ##########
#在library_db库中创建bookrack表
use library_db;
create table bookrack(
    bookrackId int(11) primary key,
    roomId int(11) not null,
    constraint FK_bookrack_bookrackId foreign key(bookrackId) references books(bookrackId),
    constraint FK_bookrack_roomId foreign key(roomId) references books(roomId)
);


#在library_db库中创建borrow表
create table borrow(
    borrowBookId int(11) primary key,
    bookId int(11) not null,
    borrowDate datetime not null,
    returnDate datetime not null,
    constraint FK_borrow_borrowBookId foreign key(borrowBookId) references reader(borrowBookId),
    constraint FK_borrow_bookId foreign key(bookId) references books(bookId)
);


########## End ##########