XxaapUXv

1create database postapo5265345687894
2
3
4DROP Table If EXISTS [Author] 
5go
6DROP Table If EXISTS [Published] 
7go
8DROP Table If EXISTS [Reader] 
9go
10DROP Table If EXISTS [Genre] 
11go
12DROP Table If EXISTS [Fines_rules] 
13go
14DROP Table If EXISTS [book_author] 
15go
16DROP Table If EXISTS [book_specimen] 
17go
18DROP Table If EXISTS [book] 
19go
20DROP Table If EXISTS [Book_genre] 
21go
22DROP Table If EXISTS [Lending_book] 
23go
24DROP Table If EXISTS [Fines] 
25go
26DROP Table If EXISTS [Fines_rules] 
27go
28
29Alter TABLE [Book_author] Drop Constraint if Exists [Book_author_fk0] 
30GO
31Alter TABLE [Book_author] Drop Constraint if Exists [Book_author_fk1] 
32GO
33Alter TABLE [Book_specimen] Drop Constraint if Exists [Book_specimen_fk2]
34GO
35Alter TABLE [Book_specimen] Drop Constraint if Exists [Book_specimen_fk3] 
36GO
37Alter TABLE [Book_genre] Drop Constraint if Exists [Book_genre_fk4]
38GO
39Alter TABLE [Book_genre] Drop Constraint if Exists [Book_genre_fk5] 
40GO
41Alter TABLE [Lending_book] Drop Constraint if Exists [Lending_book_fk6] 
42GO
43Alter TABLE [Lending_book] Drop Constraint if Exists [Lending_book_fk7] 
44GO
45Alter TABLE [Fines] Drop Constraint if Exists [Fines_fk8] 
46GO
47Alter TABLE [Fines] Drop Constraint if Exists [Fines_fk9] 
48GO
49
50create table Author (
51author_id integer not null primary key,
52first_name varchar(30) ,
53last_name varchar(30)
54);
55
56create table Book_author (
57author_id integer not null ,
58book_id integer not null ,
59primary key(author_id,book_id)
60);
61
62create table Book_specimen (
63book_id integer not null  primary key ,
64name_id integer,
65published_id integer,
66number_of_page integer,
67year_of_published datetime,
68quantiti_number integer
69);
70
71create table Published (
72published_id integer not null primary key,
73published_name varchar(30),
74published_adress varchar(30),
75telephone integer
76);
77
78create table Book (
79name_id integer not null primary key,
80book_name varchar(30)
81);
82
83create table Book_genre (
84name_id integer not null ,
85genre_id integer not null ,
86primary key(name_id,genre_id)
87);
88
89create table Genre (
90genre_id integer not null primary key,
91genre varchar(30)
92);
93
94create table Reader (
95reader_id_bilet integer  not null primary key,
96first_name varchar(30),
97last_name varchar(30),
98pasport_id integer,
99book_limit integer,
100adress varchar(30)
101);
102
103
104create table Lending_book (
105lending_id integer not null primary key,
106issue_date datetime,
107return_date datetime,
108real_return_date datetime,
109book_id integer,
110reader_id_bilet integer
111);
112
113create table Fines(
114lending_id integer not null ,
115fines_clause integer not null,
116primary key(lending_id,fines_clause)
117);
118
119create table Fines_rules(
120fines_clause integer not null primary key,
121book_damage_discription varchar(50),
122fines_summer integer
123);
124insert into Author values(1,'Kevin','Krust');
125insert into Author values(2,'Lin','Vin');
126insert into Author values(3,'Artur','Vogrin');
127insert into Author values(4,'Ivan','Klont');
128insert into Author values(5,'Jan','Vingan');
129insert into Author values(6,'Stepan','Kluev');
130insert into Author values(7,'Inga','Kupzov');
131insert into Author values(8,'Danil','Ivanov');
132insert into Author values(9,'Dima','Grunkov');
133insert into Author values(10,'Semen','Galah');
134
135
136insert into Book_specimen values(1,1,1,1,'03/12/1903',1);
137insert into Book_specimen values(2,2,2,2,'02/03/1998',6);
138insert into Book_specimen values(3,3,3,3,'01/01/1994',7);
139insert into Book_specimen values(4,4,4,3,'04/01/2000',8);
140insert into Book_specimen values(5,5,5,3,'01/03/2010',9);
141insert into Book_specimen values(6,6,6,3,'04/01/2010',11);
142insert into Book_specimen values(7,7,7,7,'01/01/1994',7);
143insert into Book_specimen values(8,8,8,12,'04/01/2000',8);
144insert into Book_specimen values(9,9,9,14,'01/03/2010',9);
145insert into Book_specimen values(10,10,10,15,'04/01/2010',11);
146
147
148insert into Published values(1,'Наука','California',5);
149insert into Published values(2,'Izdatel','Mosco',7);
150insert into Published values(3,'Pechat','Abydabi',6);
151insert into Published values(4,'List','Taxas',5);
152insert into Published values(5,'Bymaga','Globina',8);
153insert into Published values(6,'Наука','Com',9);
154insert into Published values(7,'Pechat','Abydabi',6);
155insert into Published values(8,'List','Taxas',5);
156insert into Published values(9,'Bymaga','Globina',8);
157insert into Published values(10,'Наука','Com',9);
158
159insert into Book values(1,'механика');
160insert into Book values(2,'sto lie pod vodoy');
161insert into Book values(3,'analogi v dele');
162insert into Book values(4,'keprtc');
163insert into Book values(5,'more');
164insert into Book values(6,'les');
165insert into Book values(7,'parys');
166insert into Book values(8,'azy');
167insert into Book values(9,'posledov');
168insert into Book values(10,'dymai');
169
170
171insert into Genre values(1,'физика');
172insert into Genre values(2,'матан');
173insert into Genre values(3,'история');
174insert into Genre values(4,'физика');
175insert into Genre values(5,'английский');
176insert into Genre values(6,'геометрия');
177insert into Genre values(7,'история');
178insert into Genre values(8,'китайский');
179insert into Genre values(9,'алгебра');
180insert into Genre values(10,'философия');
181
182
183insert into Reader values(1,'Ivanov','Ivan',1,5,'nikonova12');
184insert into Reader values(2,'Petron','Petr',2,5,'nosova1');
185insert into Reader values(3,'Sidorov','stepan',3,5,'globnova34');
186insert into Reader values(4,'Kulov','Ivan',4,5,'iznova45');
187insert into Reader values(5,'Semenov','Genrih',5,5,'lesorybova12');
188insert into Reader values(6,'Nikonov','Damir',6,5,'kpnechna2');
189insert into Reader values(7,'Stekov','Ivan',7,5,'yglova42');
190insert into Reader values(8,'Semenov','Evgeniy',8,5,'cenralna82');
191insert into Reader values(9,'Sеpanov','Ivan',9,9,'central ctreet20');
192insert into Reader values(10,'Zimin','Petr',9,9,'prolet56');
193
194
195
196insert into Lending_book  values(1,'01/01/1991','01/01/1991','01/02/1991',1,1);
197insert into Lending_book  values(2,'01/01/1992','01/01/1993','01/06/1993',2,2);
198insert into Lending_book  values(3,'01/01/1995','01/01/1996','01/02/1996',3,3);
199insert into Lending_book  values(4,'01/01/1997','01/01/1997','01/04/1997',4,4);
200insert into Lending_book  values(5,'01/01/1990','01/01/1991','01/02/1991',5,5);
201insert into lending_book  values(6,'01/01/2005','01/02/2005','01/03/2005',6,6);
202insert into Lending_book  values(7,'01/01/1990','01/01/1991','01/02/1991',7,7);
203insert into Lending_book  values(8,'01/01/1990','01/01/1991','01/05/1991',8,8);
204insert into lending_book  values(9,'01/01/2005','01/02/2005','01/03/2005',9,9);
205insert into lending_book  values(10,'01/01/1990','01/01/1991','01/04/1991',10,10);
206
207insert into Fines_rules values(1,'small damage',10);
208insert into Fines_rules values(2,'small damage',10);
209insert into Fines_rules values(3,'small damage',10);
210insert into Fines_rules values(4,'small damage',10);
211insert into Fines_rules values(5,'small damage',10);
212insert into Fines_rules values(6,'small damage',10);
213insert into Fines_rules values(7,'small damage',10);
214insert into Fines_rules values(8,'small damage',10);
215insert into Fines_rules values(9,'small damage',10);
216insert into Fines_rules values(10,'small damage',10);
217
218
219insert into Book_author values(1,1);
220insert into Book_author values(2,2);
221insert into Book_author values(3,3);
222insert into Book_author values(4,4);
223insert into Book_author values(5,5);
224insert into Book_author values(6,6);
225insert into Book_author values(7,7);
226insert into Book_author values(8,8);
227insert into Book_author values(9,9);
228insert into Book_author values(10,10);
229
230insert into Fines values(1,1);
231insert into Fines values(2,2);
232insert into Fines values(3,3);
233insert into Fines values(4,4);
234insert into Fines values(5,5);
235insert into Fines values(6,6);
236insert into Fines values(7,7);
237insert into Fines values(8,8);
238insert into Fines values(9,9);
239insert into Fines values(10,10);
240
241
242insert into Book_genre values(1,1);
243insert into Book_genre values(2,2);
244insert into Book_genre values(3,3);
245insert into Book_genre values(4,4);
246insert into Book_genre values(5,5);
247insert into Book_genre values(6,6);
248insert into Book_genre values(7,7);
249insert into Book_genre values(8,8);
250insert into Book_genre values(9,9);
251insert into Book_genre values(10,10);
252
253
254
255
256
257
258ALTER TABLE [Book_author] ADD CONSTRAINT [Book_author_fk0]
259	FOREIGN KEY ([author_id]) 
260	REFERENCES [Author] ([author_id])
261 ON DELETE CASCADE
262    ON UPDATE CASCADE
263GO
264
265ALTER TABLE [Book_author] ADD CONSTRAINT [Book_author_fk1]
266	FOREIGN KEY ([book_id]) 
267	REFERENCES [Book_specimen] ([book_id])
268 ON DELETE CASCADE
269    ON UPDATE CASCADE
270GO
271
272ALTER TABLE [Book_specimen] ADD CONSTRAINT [Book_specimen_fk2]
273	FOREIGN KEY ([name_id]) 
274	REFERENCES [Book] ([name_id])
275 ON DELETE CASCADE
276    ON UPDATE CASCADE
277GO
278
279ALTER TABLE [Book_specimen] ADD CONSTRAINT [Book_specimen_fk3]
280	FOREIGN KEY ([published_id]) 
281	REFERENCES [Published] ([published_id])
282 ON DELETE CASCADE
283    ON UPDATE CASCADE
284GO
285
286ALTER TABLE [Book_genre] ADD CONSTRAINT [Book_genre_fk4]
287	FOREIGN KEY ([name_id]) 
288	REFERENCES [Book] ([name_id])
289 ON DELETE CASCADE
290    ON UPDATE CASCADE
291GO
292ALTER TABLE [Book_genre] ADD CONSTRAINT [Book_genre_fk5]
293	FOREIGN KEY ([genre_id]) 
294	REFERENCES [Genre] ([genre_id])
295 ON DELETE CASCADE
296    ON UPDATE CASCADE
297GO
298
299ALTER TABLE [Lending_book] ADD CONSTRAINT [Lending_book_fk6]
300	FOREIGN KEY ([book_id]) 
301	REFERENCES [Book_specimen] ([book_id])
302 ON DELETE CASCADE
303    ON UPDATE CASCADE
304GO
305ALTER TABLE [Lending_book] ADD CONSTRAINT [Lending_book_fk7]
306	FOREIGN KEY ([reader_id_bilet]) 
307	REFERENCES [Reader] ([reader_id_bilet])
308 ON DELETE CASCADE
309    ON UPDATE CASCADE
310GO
311
312ALTER TABLE [Fines] ADD CONSTRAINT [Fines_fk8]
313	FOREIGN KEY ([lending_id]) 
314	REFERENCES [Lending_book] ([lending_id])
315 ON DELETE CASCADE
316    ON UPDATE CASCADE
317GO
318ALTER TABLE [Fines] ADD CONSTRAINT [Fines_fk9]
319	FOREIGN KEY ([fines_clause]) 
320	REFERENCES [Fines_rules] ([fines_clause])
321 ON DELETE CASCADE
322    ON UPDATE CASCADE
323GO
324
325