dX0xqK5b

1/*==============================================================*/
2/* DBMS name:      MySQL 5.0                                    */
3/* Created on:     2020/4/20 19:10:51                           */
4/*==============================================================*/
5
6drop table if exists Book;
7
8drop table if exists Borrow;
9
10drop table if exists Ret;
11
12drop table if exists Student;
13
14/*==============================================================*/
15/* Table: Book                                                  */
16/*==============================================================*/
17create table Book
18(
19   B_ID                 int not null,
20   Title                varchar(1024) not null,
21   Author               varchar(1024) not null,
22   ISBN                 char(13) not null,
23   TypeID               varchar(10) not null,
24   primary key (B_ID)
25);
26
27/*==============================================================*/
28/* Table: Student                                               */
29/*==============================================================*/
30create table Student
31(
32   S_ID                 int not null,
33   Name                 varchar(1024) not null,
34   age                  int,
35   primary key (S_ID)
36);
37
38/*==============================================================*/
39/* Table: Borrow                                                */
40/*==============================================================*/
41create table Borrow
42(
43   B_ID                 int not null,
44   S_ID                 int not null,
45   Time                 datetime not null,
46   primary key (B_ID, S_ID, Time)
47);
48
49/*==============================================================*/
50/* Table: "Return"                                              */
51/*==============================================================*/
52create table Ret
53(
54   S_ID                 int not null,
55   B_ID                 int not null,
56   Time                 datetime not null,
57   primary key (S_ID, B_ID, Time)
58);
59
60alter table Borrow add constraint FK_Borrow foreign key (S_ID)
61      references Student (S_ID) on delete restrict on update restrict;
62
63alter table Borrow add constraint FK_Borrow2 foreign key (B_ID)
64      references Book (B_ID) on delete restrict on update restrict;
65
66alter table Ret add constraint FK_Return foreign key (B_ID)
67      references Book (B_ID) on delete restrict on update restrict;
68
69alter table Ret add constraint FK_Return2 foreign key (S_ID)
70      references Student (S_ID) on delete restrict on update restrict;