· 5 years ago · Apr 18, 2020, 02:18 PM
1/*==============================================================*/
2/* DBMS name: MySQL 5.0 */
3/* Created on: 4/18/2020 10:14:02 PM */
4/*==============================================================*/
5
6
7drop table if exists author;
8
9drop table if exists book;
10
11drop table if exists library;
12
13drop table if exists own;
14
15drop table if exists rent_order;
16
17drop table if exists student;
18
19drop table if exists writing;
20
21/*==============================================================*/
22/* Table: author */
23/*==============================================================*/
24create table author
25(
26 a_ID varchar(64) not null,
27 name varchar(1024) not null,
28 nationality varchar(64) not null,
29 primary key (a_ID)
30);
31
32/*==============================================================*/
33/* Table: book */
34/*==============================================================*/
35create table book
36(
37 bar_code varchar(256) not null,
38 b_name varchar(1024) not null,
39 b_number varchar(1024) not null,
40 b_type varchar(256) not null,
41 primary key (b_number)
42);
43
44/*==============================================================*/
45/* Table: library */
46/*==============================================================*/
47create table library
48(
49 address varchar(128) not null,
50 l_name varchar(1024) not null,
51 manager varchar(1024),
52 primary key (address)
53);
54
55/*==============================================================*/
56/* Table: own */
57/*==============================================================*/
58create table own
59(
60 b_number varchar(1024) not null,
61 address varchar(128) not null,
62 primary key (b_number, address)
63);
64
65/*==============================================================*/
66/* Table: rent_order */
67/*==============================================================*/
68create table rent_order
69(
70 stu_s_ID varchar(64),
71 b_number varchar(1024),
72 s_ID varchar(64) not null,
73 b_ID varchar(64) not null,
74 rent_time time not null,
75 return_time time
76);
77
78/*==============================================================*/
79/* Table: student */
80/*==============================================================*/
81create table student
82(
83 s_ID varchar(64) not null,
84 name varchar(1024) not null,
85 status varchar(16) not null,
86 primary key (s_ID)
87);
88
89/*==============================================================*/
90/* Table: writing */
91/*==============================================================*/
92create table writing
93(
94 a_ID varchar(64) not null,
95 b_number varchar(1024) not null,
96 primary key (a_ID, b_number)
97);
98
99alter table own add constraint FK_belong_library foreign key (b_number)
100 references book (b_number) on delete restrict on update restrict;
101
102alter table own add constraint FK_book2 foreign key (address)
103 references library (address) on delete restrict on update restrict;
104
105alter table rent_order add constraint FK_book_rent foreign key (b_number)
106 references book (b_number) on delete restrict on update restrict;
107
108alter table rent_order add constraint FK_student_rent foreign key (stu_s_ID)
109 references student (s_ID) on delete restrict on update restrict;
110
111alter table writing add constraint FK_author foreign key (b_number)
112 references book (b_number) on delete restrict on update restrict;
113
114alter table writing add constraint FK_book foreign key (a_ID)
115 references author (a_ID) on delete restrict on update restrict;