T17VuDwM

1/*==============================================================*/
2/* DBMS name:      Sybase SQL Anywhere 12                       */
3/* Created on:     2020/4/20 17:04:18                           */
4/*==============================================================*/
5
6
7if exists(select 1 from sys.sysforeignkey where role='FK_BOOKS_REFERENCE_LIBRARY') then
8    alter table Books
9       delete foreign key FK_BOOKS_REFERENCE_LIBRARY
10end if;
11
12if exists(select 1 from sys.sysforeignkey where role='FK_BORROW_REFERENCE_STUDENT') then
13    alter table Borrow
14       delete foreign key FK_BORROW_REFERENCE_STUDENT
15end if;
16
17if exists(select 1 from sys.sysforeignkey where role='FK_BORROW_REFERENCE_BOOKS') then
18    alter table Borrow
19       delete foreign key FK_BORROW_REFERENCE_BOOKS
20end if;
21
22drop table if exists Books;
23
24drop table if exists Borrow;
25
26drop table if exists Library;
27
28drop table if exists Student;
29
30/*==============================================================*/
31/* Table: Books                                                 */
32/*==============================================================*/
33create table Books 
34(
35   Barcode              image                          not null,
36   LibraryID            int                            null,
37   CollectionNumber     int                            null,
38   BookName             varchar(30)                    null,
39   Author               varchar(20)                    null,
40   BookNumber           int                            null,
41   IPC                  varchar(5)                     null,
42   Stocks               int                            null,
43   constraint PK_BOOKS primary key clustered (Barcode)
44);
45
46/*==============================================================*/
47/* Table: Borrow                                                */
48/*==============================================================*/
49create table Borrow 
50(
51   StudentID            varchr(20)                     null,
52   Barcode              image                          null
53);
54
55/*==============================================================*/
56/* Table: Library                                               */
57/*==============================================================*/
58create table Library 
59(
60   ID                   int                            not null,
61   Location             varchar(50)                    null,
62   PhoneNumber          varchar(20)                    null,
63   constraint PK_LIBRARY primary key clustered (ID)
64);
65
66/*==============================================================*/
67/* Table: Student                                               */
68/*==============================================================*/
69create table Student 
70(
71   ID                   varchar(20)                    not null,
72   constraint PK_STUDENT primary key clustered (ID)
73);
74
75alter table Books
76   add constraint FK_BOOKS_REFERENCE_LIBRARY foreign key (LibraryID)
77      references Library (ID)
78      on update restrict
79      on delete restrict;
80
81alter table Borrow
82   add constraint FK_BORROW_REFERENCE_STUDENT foreign key (StudentID)
83      references Student (ID)
84      on update restrict
85      on delete restrict;
86
87alter table Borrow
88   add constraint FK_BORROW_REFERENCE_BOOKS foreign key (Barcode)
89      references Books (Barcode)
90      on update restrict
91      on delete restrict;