b0G5NpaY

1/*==============================================================*/
2/* DBMS name:      MySQL 5.0                                    */
3/* Created on:     2020/4/19 17:27:44                           */
4/*==============================================================*/
5
6
7drop table if exists Book;
8
9drop table if exists Borrow;
10
11drop table if exists Student;
12
13/*==============================================================*/
14/* Table: Book                                                  */
15/*==============================================================*/
16create table Book
17(
18   id                   int not null,
19   title                char(256) not null,
20   author               char(256) not null,
21   isbn                 numeric(8,0) not null,
22   serial_number        numeric(8,0) not null,
23   type                 char(256),
24   primary key (id)
25);
26
27/*==============================================================*/
28/* Table: Borrow                                                */
29/*==============================================================*/
30create table Borrow
31(
32   id                   int not null,
33   s_id                 numeric(8,0) not null,
34   borrow_time          time not null,
35   return_time          time,
36   primary key (id, s_id)
37);
38
39/*==============================================================*/
40/* Table: Student                                               */
41/*==============================================================*/
42create table Student
43(
44   s_id                 numeric(8,0) not null,
45   grade                char(256) not null,
46   major                char(255) not null,
47   primary key (s_id)
48);
49
50alter table Borrow add constraint FK_Borrow foreign key (id)
51      references Book (id) on delete restrict on update restrict;
52
53alter table Borrow add constraint FK_Borrow2 foreign key (s_id)
54      references Student (s_id) on delete restrict on update restrict;