wyBM5s9s

1/*==============================================================*/
2/* DBMS name:      MySQL 5.0                                    */
3/* Created on:     4/18/2020 9:53:34 PM                         */
4/*==============================================================*/
5
6
7drop table if exists author;
8
9drop table if exists book;
10
11drop table if exists rent_order;
12
13drop table if exists student;
14
15drop table if exists writing;
16
17/*==============================================================*/
18/* Table: author                                                */
19/*==============================================================*/
20create table author
21(
22   a_ID                 varchar(64) not null,
23   name                 varchar(128) not null,
24   nationality          varchar(64) not null,
25   primary key (a_ID)
26);
27
28/*==============================================================*/
29/* Table: book                                                  */
30/*==============================================================*/
31create table book
32(
33   bar_code             varchar(256) not null,
34   b_name               varchar(128) not null,
35   b_number             varchar(128) not null,
36   b_type               varchar(256) not null,
37   primary key (b_number)
38);
39
40/*==============================================================*/
41/* Table: rent_order                                            */
42/*==============================================================*/
43create table rent_order
44(
45   stu_s_ID             varchar(64),
46   b_number             varchar(128),
47   s_ID                 varchar(64) not null,
48   b_ID                 varchar(64) not null,
49   rent_time            time not null,
50   return_time          time
51);
52
53/*==============================================================*/
54/* Table: student                                               */
55/*==============================================================*/
56create table student
57(
58   s_ID                 varchar(64) not null,
59   name                 varchar(128) not null,
60   status               varchar(16) not null,
61   primary key (s_ID)
62);
63
64/*==============================================================*/
65/* Table: writing                                               */
66/*==============================================================*/
67create table writing
68(
69   a_ID                 varchar(64) not null,
70   b_number             varchar(128) not null,
71   primary key (a_ID, b_number)
72);
73
74alter table rent_order add constraint FK_book_rent foreign key (b_number)
75      references book (b_number) on delete restrict on update restrict;
76
77alter table rent_order add constraint FK_student_rent foreign key (stu_s_ID)
78      references student (s_ID) on delete restrict on update restrict;
79
80alter table writing add constraint FK_author foreign key (b_number)
81      references book (b_number) on delete restrict on update restrict;
82
83alter table writing add constraint FK_book foreign key (a_ID)
84      references author (a_ID) on delete restrict on update restrict;