· 7 years ago · Oct 03, 2018, 11:50 AM
1/*==============================================================*/
2/* DBMS name: Sybase SQL Anywhere 10 */
3/* Created on: 2018-10-03 13:32:55 */
4/*==============================================================*/
5
6
7if exists(select 1 from sys.sysforeignkey where role='FK_COPIES O_INCLUDES2_BOOKS') then
8 alter table "Copies of the book"
9 delete foreign key "FK_COPIES O_INCLUDES2_BOOKS"
10end if;
11
12if exists(select 1 from sys.sysforeignkey where role='FK_COPIES O_IS_THE READ') then
13 alter table "Copies of the book"
14 delete foreign key "FK_COPIES O_IS_THE READ"
15end if;
16
17if exists(select 1 from sys.sysforeignkey where role='FK_IS CONNE_HAS THE I_CLASSIFI') then
18 alter table "Is connected"
19 delete foreign key "FK_IS CONNE_HAS THE I_CLASSIFI"
20end if;
21
22if exists(select 1 from sys.sysforeignkey where role='FK_IS CONNE_INCLUDES_BOOKS') then
23 alter table "Is connected"
24 delete foreign key "FK_IS CONNE_INCLUDES_BOOKS"
25end if;
26
27if exists(
28 select 1 from sys.sysindex i, sys.systable t
29 where i.table_id=t.table_id
30 and i.index_name='Books_PK'
31 and t.table_name='Books'
32) then
33 drop index Books.Books_PK
34end if;
35
36if exists(
37 select 1 from sys.systable
38 where table_name='Books'
39 and table_type in ('BASE', 'GBL TEMP')
40) then
41 drop table Books
42end if;
43
44if exists(
45 select 1 from sys.sysindex i, sys.systable t
46 where i.table_id=t.table_id
47 and i.index_name='Classification directory_PK'
48 and t.table_name='Classification directory'
49) then
50 drop index "Classification directory"."Classification directory_PK"
51end if;
52
53if exists(
54 select 1 from sys.systable
55 where table_name='Classification directory'
56 and table_type in ('BASE', 'GBL TEMP')
57) then
58 drop table "Classification directory"
59end if;
60
61if exists(
62 select 1 from sys.sysindex i, sys.systable t
63 where i.table_id=t.table_id
64 and i.index_name='Is_FK'
65 and t.table_name='Copies of the book'
66) then
67 drop index "Copies of the book".Is_FK
68end if;
69
70if exists(
71 select 1 from sys.sysindex i, sys.systable t
72 where i.table_id=t.table_id
73 and i.index_name='Includes2_FK'
74 and t.table_name='Copies of the book'
75) then
76 drop index "Copies of the book".Includes2_FK
77end if;
78
79if exists(
80 select 1 from sys.sysindex i, sys.systable t
81 where i.table_id=t.table_id
82 and i.index_name='Copies of the book_PK'
83 and t.table_name='Copies of the book'
84) then
85 drop index "Copies of the book"."Copies of the book_PK"
86end if;
87
88if exists(
89 select 1 from sys.systable
90 where table_name='Copies of the book'
91 and table_type in ('BASE', 'GBL TEMP')
92) then
93 drop table "Copies of the book"
94end if;
95
96if exists(
97 select 1 from sys.sysindex i, sys.systable t
98 where i.table_id=t.table_id
99 and i.index_name='Has the information from_FK'
100 and t.table_name='Is connected'
101) then
102 drop index "Is connected"."Has the information from_FK"
103end if;
104
105if exists(
106 select 1 from sys.sysindex i, sys.systable t
107 where i.table_id=t.table_id
108 and i.index_name='Includes_FK'
109 and t.table_name='Is connected'
110) then
111 drop index "Is connected".Includes_FK
112end if;
113
114if exists(
115 select 1 from sys.sysindex i, sys.systable t
116 where i.table_id=t.table_id
117 and i.index_name='Is connected_PK'
118 and t.table_name='Is connected'
119) then
120 drop index "Is connected"."Is connected_PK"
121end if;
122
123if exists(
124 select 1 from sys.systable
125 where table_name='Is connected'
126 and table_type in ('BASE', 'GBL TEMP')
127) then
128 drop table "Is connected"
129end if;
130
131if exists(
132 select 1 from sys.sysindex i, sys.systable t
133 where i.table_id=t.table_id
134 and i.index_name='The readers_PK'
135 and t.table_name='The readers'
136) then
137 drop index "The readers"."The readers_PK"
138end if;
139
140if exists(
141 select 1 from sys.systable
142 where table_name='The readers'
143 and table_type in ('BASE', 'GBL TEMP')
144) then
145 drop table "The readers"
146end if;
147
148/*==============================================================*/
149/* Table: Books */
150/*==============================================================*/
151create table Books
152(
153 ISBN varchar(12) not null,
154 Name varchar(30) null,
155 Author varchar(15) null,
156 Publishing varchar(15) null,
157 City varchar(15) null,
158 Year varchar(15) null,
159 "Amount of pages" decimal null,
160 Attribute_8 numeric null,
161 constraint PK_BOOKS primary key (ISBN)
162);
163
164/*==============================================================*/
165/* Index: Books_PK */
166/*==============================================================*/
167create unique index Books_PK on Books (
168ISBN ASC
169);
170
171/*==============================================================*/
172/* Table: "Classification directory" */
173/*==============================================================*/
174create table "Classification directory"
175(
176 Code_knowledges varchar(10) not null,
177 Name_knowledges varchar(20) null,
178 constraint "PK_CLASSIFICATION DIRECTORY" primary key (Code_knowledges)
179);
180
181/*==============================================================*/
182/* Index: "Classification directory_PK" */
183/*==============================================================*/
184create unique index "Classification directory_PK" on "Classification directory" (
185Code_knowledges ASC
186);
187
188/*==============================================================*/
189/* Table: "Copies of the book" */
190/*==============================================================*/
191create table "Copies of the book"
192(
193 ISBN varchar(12) not null,
194 Num_inv varchar(10) not null,
195 Num_document varchar(10) null,
196 Is_or_not binary(1) null,
197 Capture_date date null,
198 Return_date date null,
199 constraint "PK_COPIES OF THE BOOK" primary key (ISBN, Num_inv)
200);
201
202/*==============================================================*/
203/* Index: "Copies of the book_PK" */
204/*==============================================================*/
205create unique index "Copies of the book_PK" on "Copies of the book" (
206ISBN ASC,
207Num_inv ASC
208);
209
210/*==============================================================*/
211/* Index: Includes2_FK */
212/*==============================================================*/
213create index Includes2_FK on "Copies of the book" (
214ISBN ASC
215);
216
217/*==============================================================*/
218/* Index: Is_FK */
219/*==============================================================*/
220create index Is_FK on "Copies of the book" (
221Num_document ASC
222);
223
224/*==============================================================*/
225/* Table: "Is connected" */
226/*==============================================================*/
227create table "Is connected"
228(
229 ISBN varchar(12) not null,
230 Code_knowledges varchar(10) not null,
231 constraint "PK_IS CONNECTED" primary key clustered (ISBN, Code_knowledges)
232);
233
234/*==============================================================*/
235/* Index: "Is connected_PK" */
236/*==============================================================*/
237create unique clustered index "Is connected_PK" on "Is connected" (
238ISBN ASC,
239Code_knowledges ASC
240);
241
242/*==============================================================*/
243/* Index: Includes_FK */
244/*==============================================================*/
245create index Includes_FK on "Is connected" (
246ISBN ASC
247);
248
249/*==============================================================*/
250/* Index: "Has the information from_FK" */
251/*==============================================================*/
252create index "Has the information from_FK" on "Is connected" (
253Code_knowledges ASC
254);
255
256/*==============================================================*/
257/* Table: "The readers" */
258/*==============================================================*/
259create table "The readers"
260(
261 Num_document varchar(10) not null,
262 Last_name varchar(20) null,
263 First_name varchar(10) null,
264 Birth_date date null,
265 Sex varchar(1) null,
266 Phone_home varchar(10) null,
267 Phone_office varchar(10) null,
268 constraint "PK_THE READERS" primary key (Num_document)
269);
270
271/*==============================================================*/
272/* Index: "The readers_PK" */
273/*==============================================================*/
274create unique index "The readers_PK" on "The readers" (
275Num_document ASC
276);
277
278alter table "Copies of the book"
279 add constraint "FK_COPIES O_INCLUDES2_BOOKS" foreign key (ISBN)
280 references Books (ISBN)
281 on update restrict
282 on delete restrict;
283
284alter table "Copies of the book"
285 add constraint "FK_COPIES O_IS_THE READ" foreign key (Num_document)
286 references "The readers" (Num_document)
287 on update restrict
288 on delete restrict;
289
290alter table "Is connected"
291 add constraint "FK_IS CONNE_HAS THE I_CLASSIFI" foreign key (Code_knowledges)
292 references "Classification directory" (Code_knowledges)
293 on update restrict
294 on delete restrict;
295
296alter table "Is connected"
297 add constraint "FK_IS CONNE_INCLUDES_BOOKS" foreign key (ISBN)
298 references Books (ISBN)
299 on update restrict
300 on delete restrict;