m6ETrcwK

1/*==============================================================*/
2/* 会川慧516413990003                                           */
3/*==============================================================*/
4
5if exists(select 1 from sys.sysforeignkey where role='FK_BORROW_R_BOOK_BORR_BOOK') then
6    alter table Borrow_record
7       delete foreign key FK_BORROW_R_BOOK_BORR_BOOK
8end if;
9
10if exists(select 1 from sys.sysforeignkey where role='FK_BORROW_R_BORROWER__BORROWER') then
11    alter table Borrow_record
12       delete foreign key FK_BORROW_R_BORROWER__BORROWER
13end if;
14
15drop index if exists Book.Book_PK;
16
17drop table if exists Book;
18
19drop index if exists Borrow_record.borrower_borrowRecord_FK;
20
21drop index if exists Borrow_record.book_borrowRecord_FK;
22
23drop index if exists Borrow_record.Borrow_record_PK;
24
25drop table if exists Borrow_record;
26
27drop index if exists Borrower.Borrower_PK;
28
29drop table if exists Borrower;
30
31/*==============================================================*/
32/* Table: Book                                                  */
33/*==============================================================*/
34create or replace table Book 
35(
36   bookId               integer                        not null,
37   title                varchar(1024)                  not null,
38   author               varchar(1024)                  null,
39   publisher            varchar(1024)                  null,
40   publishedDate        timestamp                      null,
41   ibsn                 varchar(1024)                  not null,
42   shelf_id             varchar(1024)                  not null,
43   constraint PK_BOOK primary key clustered (bookId)
44);
45
46/*==============================================================*/
47/* Index: Book_PK                                               */
48/*==============================================================*/
49create unique clustered index Book_PK on Book (
50bookId ASC
51);
52
53/*==============================================================*/
54/* Table: Borrow_record                                         */
55/*==============================================================*/
56create or replace table Borrow_record 
57(
58   borrowRecordId       integer                        not null,
59   borrower_id          integer                        null,
60   bookId               integer                        null,
61   borrowedDate         timestamp                      not null,
62   returnDue            timestamp                      not null,
63   returnedDate         timestamp                      not null,
64   constraint PK_BORROW_RECORD primary key clustered (borrowRecordId)
65);
66
67/*==============================================================*/
68/* Index: Borrow_record_PK                                      */
69/*==============================================================*/
70create unique clustered index Borrow_record_PK on Borrow_record (
71borrowRecordId ASC
72);
73
74/*==============================================================*/
75/* Index: book_borrowRecord_FK                                  */
76/*==============================================================*/
77create index book_borrowRecord_FK on Borrow_record (
78bookId ASC
79);
80
81/*==============================================================*/
82/* Index: borrower_borrowRecord_FK                              */
83/*==============================================================*/
84create index borrower_borrowRecord_FK on Borrow_record (
85borrower_id ASC
86);
87
88/*==============================================================*/
89/* Table: Borrower                                              */
90/*==============================================================*/
91create or replace table Borrower 
92(
93   borrower_id          integer                        not null,
94   name                 varchar(1024)                  not null,
95   identity_type        varchar(1024)                  not null,
96   phoneNumber          varchar(1024)                  not null,
97   constraint PK_BORROWER primary key clustered (borrower_id)
98);
99
100/*==============================================================*/
101/* Index: Borrower_PK                                           */
102/*==============================================================*/
103create unique clustered index Borrower_PK on Borrower (
104borrower_id ASC
105);
106
107alter table Borrow_record
108   add constraint FK_BORROW_R_BOOK_BORR_BOOK foreign key (bookId)
109      references Book (bookId)
110      on update restrict
111      on delete restrict;
112
113alter table Borrow_record
114   add constraint FK_BORROW_R_BORROWER__BORROWER foreign key (borrower_id)
115      references Borrower (borrower_id)
116      on update restrict
117      on delete restrict;