rhLQTcZD

1drop database if exists films;
2create database films;
3use films;
4
5create table director(
6name varchar(30) not null,
7code int(7) zerofill auto_increment primary key,
8birthday date,
9natinality varchar(50))
10engine = InnoDB COMMENT 'Director'
11Default char set=utf8
12default collate=utf8_general_ci;
13
14create table actor(
15code int(7) zerofill auto_increment primary key,
16name varchar(30) not  null,
17lli1 varchar(30) not null,
18lli2 varchar(30))
19engine = InnoDB COMMENT 'Actor'
20Default char set=utf8
21default collate=utf8_general_ci;
22
23create table film(
24code int(7) zerofill auto_increment primary key,
25title varchar(100) not null unique,
26year year not null,
27nationality varchar(100) not null,
28language varchar(100) not null,
29color boolean default true,
30summary text,
31observation text,
32codeDirector int(7) zerofill not null,
33check (color = true),
34FOREIGN KEY (codeDirector) REFERENCES director (code) on update cascade on delete no action)
35engine = InnoDB COMMENT 'Film'
36Default char set=utf8
37default collate=utf8_general_ci;
38
39create table performs(
40character_name varchar(50),
41codeFilm int(7) zerofill not null,
42codeActor int(7) zerofill not null,
43FOREIGN KEY (codeFilm) REFERENCES film (code) on update cascade on delete no action,
44FOREIGN KEY (codeActor) REFERENCES actor (code) on update cascade on delete no action) 
45engine = InnoDB COMMENT 'Actor performs in a film'
46Default char set=utf8
47default collate=utf8_general_ci;
48
49drop database if exists  magazine;
50create database magazine;
51use magazine;
52
53create table magazine(
54issn varchar(40) primary key,
55number int(7) zerofill default '0',
56title varchar(100) not null,
57year year not null,
58check (number > 0))
59engine = InnoDB COMMENT 'Magazine'
60Default char set=utf8
61default collate=utf8_general_ci;
62
63create table report(
64code int(7) zerofill primary key,
65title varchar(100) not null unique key,
66init_page int(10) not null,
67last_page int(10) not null,
68issnMagazine varchar(40) not null,
69FOREIGN KEY (issnMagazine) REFERENCES magazine (issn) on update cascade on delete no action)
70engine = InnoDB COMMENT 'Report'
71Default char set=utf8
72default collate=utf8_general_ci;
73
74create table author(
75code int(7) zerofill primary key,
76name varchar(30) not  null,
77lli1 varchar(30) not null,
78lli2 varchar(30),
79email varchar(100))
80engine = InnoDB COMMENT 'Author'
81Default char set=utf8
82default collate=utf8_general_ci;
83
84create table writes(
85codeAuthor int(7) zerofill not null,
86codeReport int(7) zerofill not null,
87FOREIGN KEY (codeAuthor) REFERENCES author (code) on update cascade on delete no action,
88FOREIGN KEY (codeReport) REFERENCES report (code) on update cascade on delete no action)
89engine = InnoDB COMMENT 'Writes'
90Default char set=utf8
91default collate=utf8_general_ci;
92
93drop database if exists project;
94create database project;
95use project;
96
97create table centre(
98code int(7) zerofill primary key,
99name varchar(30) not  null)
100engine = InnoDB COMMENT 'Centre'
101Default char set=utf8
102default collate=utf8_general_ci;
103
104create table director(
105code int(7) zerofill primary key,
106name varchar(50) not null,
107sur1 varchar(50) not null,
108sur2 varchar(50))
109engine = InnoDB COMMENT 'Centre'
110Default char set=utf8
111default collate=utf8_general_ci;
112
113create table project(
114code int(7) zerofill primary key,
115name varchar(100) not null unique key,
116init_page int(10) not null,
117last_page int(10) not null,
118budget int(20) not null,
119codeDirector int(7) zerofill not null,
120codeCentre int(7) zerofill not null,
121FOREIGN KEY (codeDirector) REFERENCES director (code) on update cascade on delete no action,
122FOREIGN KEY (codeCentre) REFERENCES centre (code) on update cascade on delete no action,
123check (budget > 500))
124engine = InnoDB COMMENT 'Project'
125Default char set=utf8
126default collate=utf8_general_ci;
127
128create table city(
129code int(7) zerofill primary key,
130name varchar(100) not null,
131country varchar(50) not null,
132number_inhibitants int(255) not null)
133engine = InnoDB COMMENT 'City'
134Default char set=utf8
135default collate=utf8_general_ci;
136
137create table works(
138codeCity int(7) zerofill not null,
139codeProject int(7) zerofill not null,
140description text not null,
141foreign key (codeCity) REFERENCES city (code) on update cascade on delete no action,
142foreign key (codeProject) References project (code) on update cascade on delete no action,
143PRIMARY KEY(codeCity, codeProject))
144engine = InnoDB COMMENT 'Works'
145Default char set=utf8
146default collate=utf8_general_ci;
147
148drop database if exists car;
149create database car;
150use car;
151
152create table city(
153code int(7) zerofill primary key,
154name varchar(30) not  null)
155engine = InnoDB COMMENT 'City'
156Default char set=utf8
157default collate=utf8_general_ci;
158
159create table client(
160dni varchar(9) primary key,
161name varchar(50) not null,
162sur1 varchar(50) not null,
163sur2 varchar(50),
164address varchar(100),
165credit_card int(16) not null,
166type_credit_card  ENUM('Visa', 'Master Card', 'American Express')not null,
167phone varchar(12) not null,
168codeCity int(7) zerofill not null,
169foreign key (codeCity) references city (code) on update cascade on delete no action)
170engine = InnoDB COMMENT 'Client'
171Default char set=utf8
172default collate=utf8_general_ci;
173
174create table centre(
175code int(7) zerofill primary key,
176name varchar(100) not null,
177adress varchar(50) not null,
178phone int(12) not null)
179engine = InnoDB COMMENT 'City'
180Default char set=utf8
181default collate=utf8_general_ci;
182
183create table car(
184code int(7) zerofill primary key,
185groupCar varchar(100) not null,
186model varchar(25) not null,
187capacity int(5) not null,
188doors int(3) not null,
189year year not null,
190codeCentre int(7) zerofill not null,
191FOREIGN KEY (codeCentre) REFERENCES centre (code) on update cascade on delete no action,
192check (doors > 0))
193engine = InnoDB COMMENT 'Car'
194Default char set=utf8
195default collate=utf8_general_ci;
196
197create table rents(
198n_of_days int(255) not null,
199price int(255) not null,
200insurance varchar(25) not null,
201codeCar int(7) zerofill not null,
202dniClient varchar(9) not null,
203foreign key (codeCar) REFERENCES car (code) on update cascade on delete no action,
204foreign key (dniClient) REFERENCES client (dni) on update cascade on delete no action)
205engine = InnoDB COMMENT 'Rents'
206Default char set=utf8
207default collate=utf8_general_ci;
208
209drop database if exists loan;
210create database loan;
211use loan;
212
213create table client(
214DNI varchar(9) primary key,
215name varchar(25) not null,
216sur1 varchar(25) not null,
217sur2 varchar(25))
218engine = InnoDB COMMENT 'Client'
219Default char set=utf8
220default collate=utf8_general_ci;
221
222create table lender(
223DNI varchar(9) primary key,
224name varchar(25) not null,
225sur1 varchar(25) not null,
226sur2 varchar(25) default 'No second surname')
227engine = InnoDB COMMENT 'Lender'
228Default char set=utf8
229default collate=utf8_general_ci;
230
231create table loan(
232id int(7) zerofill auto_increment primary key,
233euros decimal(2) not null,
234init_date date not null,
235final_date date not null,
236percentage int(3) not null,
237finality text,
238dniClient varchar(9) not null,
239check(init_date<final_date),
240foreign key (dniClient) references client (dni) on update no action on delete no action)
241engine = InnoDB COMMENT 'loan'
242Default char set=utf8
243default collate=utf8_general_ci;
244
245create table provides(
246dniLender varchar(9) not null,
247idLoan int(7) zerofill not null,
248qta int(10),
249foreign key (dniLender) references Lender (dni) on update no action on delete no action,
250foreign key (idLoan) references loan (id) on update cascade on delete no action)
251engine = InnoDB COMMENT 'loan and lender'
252Default char set=utf8
253default collate=utf8_general_ci;
254
255drop database if exists airport;
256create database airport;
257use airport;
258
259create table city(
260code int(7) zerofill auto_increment primary key,
261name varchar(30) not  null)
262engine = InnoDB COMMENT 'City'
263Default char set=utf8
264default collate=utf8_general_ci;
265
266create table airport(
267code int(7) zerofill auto_increment primary key,
268name varchar(25) not null,
269codeCity int(7) zerofill not null,
270foreign key (codeCity) references city (code)
271)
272engine = InnoDB COMMENT 'airport'
273Default char set=utf8
274default collate=utf8_general_ci;
275
276create table airplane(
277code int(7) zerofill auto_increment primary key,
278name varchar(25) default 'airplane',
279model varchar(25) not null)
280engine = InnoDB COMMENT 'airplane'
281Default char set=utf8
282default collate=utf8_general_ci;
283
284create table flight(
285codeAirport int(7) zerofill not null,
286codeAirport2 int(7) zerofill not null,
287number int(7) zerofill auto_increment primary key,
288foreign key (codeAirport) references airport (code) on update cascade on delete no action,
289foreign key (codeAirport2) references airport(code) on update cascade on delete no action,
290check (codeAirport != codeAirport2)
291)
292engine = InnoDB COMMENT 'flight'
293Default char set=utf8
294default collate=utf8_general_ci;
295
296create table is_Asigned(
297number int(7) zerofill not null,
298code int(7) zerofill not null,
299date date,
300foreign key (number) references flight (number) on update cascade on delete no action,
301foreign key (code) references airplane (code) on update cascade on delete no action,
302primary key (number, date)
303)
304engine = InnoDB COMMENT 'is_Asigned'
305Default char set=utf8
306default collate=utf8_general_ci;
307
308drop database if exists selling;
309create database selling;
310use selling;
311
312create table type(
313code int(7) zerofill auto_increment primary key,
314name varchar(25) not null,
315minimum_stock int(255) not null,
316subType int(7) zerofill not null,
317foreign key (subType) references type (code) on update cascade on delete no action,
318check(minimum_stock > 0))
319engine = InnoDB COMMENT 'type'
320Default char set=utf8
321default collate=utf8_general_ci;
322
323create table piece(
324code int(7) zerofill auto_increment primary key,
325name varchar(25) not null,
326description text,
327codeType int(7) zerofill not null,
328foreign key (codeType) references type (code) on update cascade on delete no action)
329engine = InnoDB comment 'piece'
330default char set = utf8
331default collate utf8_general_ci;
332
333create table bar_code(
334barcode int(255) primary key,
335pieceCode int(7) zerofill not null,
336foreign key (pieceCode) references piece (code)
337)
338engine = InnoDB Comment 'bar_code'
339default char set=utf8
340default collate=utf8_general_ci;
341
342create table supplier(
343code int(7) zerofill auto_increment primary key,
344name varchar(25) not null,
345nif varchar(9) unique key
346)
347engine = InnoDB comment 'supplier'
348default char set=utf8
349default collate=utf8_general_ci;
350
351create table orders(
352data date not null,
353codeSupplier int(7) zerofill not null,
354number int(255) auto_increment primary key,
355foreign key (codeSupplier) references supplier (code) on update cascade on delete no action
356)
357engine = InnoDB comment 'orders'
358default char set=utf8
359default collate=utf8_general_ci;
360
361create table orderLine(
362codePiece int(7) zerofill not null,
363orderNumber int(255) not null,
364foreign key (codePiece) references piece (code) on update cascade on delete no action,
365foreign key (orderNumber) references orders (number) on update cascade on delete no action,
366constraint codeOrderLine primary key (codePiece, orderNumber)
367)
368engine = InnoDB comment 'orderLine'
369default char set=utf8
370default collate=utf8_general_ci;
371
372drop database if exists drives;
373create database drives;
374use drives;
375
376create table car(
377code int(7) zerofill auto_increment primary key
378)
379engine = InnoDB comment 'car'
380default char set=utf8
381default collate=utf8_general_ci;
382
383create table police(
384code int(7) primary key,
385name varchar(25) not null,
386sur1 varchar(25) not null,
387sur2 varchar(25) default 'null'
388)
389engine = InnoDB comment 'police'
390default char set=utf8
391default collate=utf8_general_ci;
392
393create table driver(
394dni varchar(9) primary key,
395name varchar(25) not null,
396sur1 varchar(25) not null,
397sur2 varchar(25) default 'null'
398)
399engine = InnoDB comment 'driver'
400default char set=utf8
401default collate=utf8_general_ci;
402
403create table drives(
404codeCar int(7) zerofill not null,
405dniDriver varchar(9) not null,
406data date not null,
407foreign key (codeCar) references car (code) on update cascade on delete no action,
408foreign key (dniDriver) references driver (dni) on update cascade on delete no action,
409primary key (codeCar, dniDriver)
410)
411engine = InnoDB comment 'drives'
412default char set=utf8
413default collate=utf8_general_ci;
414
415create table sanction(
416codeCar int(7) zerofill not null,
417dniDriver varchar(9) not null,
418codePolice int(7) not null,
419price decimal(2) not null,
420points int(3) not null,
421foreign key (codeCar) references drives (codeCar) on update cascade on delete no action,
422foreign key (dniDriver) references drives (dniDriver) on update cascade on delete no action,
423foreign key (codePolice) references police (code) on update cascade on delete no action,
424check( price > 5)
425)
426engine = InnoDB comment 'sanction'
427default char set=utf8
428default collate=utf8_general_ci;
429
430drop database if exists classroom;
431create database classroom;
432use classroom;
433
434create table supplier(
435code int(7) zerofill primary key,
436name varchar(25) not null,
437nif varchar(9) default 'No nif')
438engine = InnoDB comment 'supplier'
439default char set=utf8
440default collate=utf8_general_ci;
441
442create table motherboard(
443code int(7) zerofill primary key,
444name varchar(25))
445engine = InnoDB comment 'motherboard'
446default char set=utf8
447default collate=utf8_general_ci;
448
449create table memory(
450code int(7) zerofill primary key,
451name varchar(25))
452engine = InnoDB comment 'memory'
453default char set=utf8
454default collate=utf8_general_ci;
455
456create table type(
457code int(7) zerofill primary key,
458name varchar(25) not null,
459subType int(7) zerofill not null,
460foreign key (subType) references type (code) on update cascade on delete no action)
461engine = InnoDB comment 'type'
462default char set=utf8
463default collate=utf8_general_ci;
464
465create table classroom(
466code int(7) zerofill primary key,
467name varchar(25) not null)
468engine = InnoDB comment 'classroom'
469default char set=utf8
470default collate=utf8_general_ci;
471
472create table computer(
473code int(7) zerofill primary key,
474description text not null,
475megas varchar(255) not null,
476codeMemory int(7) zerofill not null,
477codeSupplier int(7) zerofill not null,
478codeMotherboard int(7) zerofill not null,
479foreign key (codeSupplier) references supplier (code) on update cascade on delete no action,
480foreign key (codeMotherboard) references motherboard (code) on update cascade on delete no action,
481foreign key (codeMemory) references memory (code) on update cascade on delete no action)
482engine = InnoDB comment 'computer'
483default char set=utf8
484default collate=utf8_general_ci;
485
486create table copy(
487serial_number varchar(20) primary key,
488purchased_date date not null,
489waranty_date date not null,
490check (waranty_date>purchased_date),
491codeClassroom int(7) zerofill not null,
492codeComputer int(7) zerofill not null,
493foreign key (codeClassroom) references classroom (code) on update cascade on delete no action,
494foreign key (codeComputer) references computer (code) on update cascade on delete no action)
495engine = InnoDB comment 'copy'
496default char set=utf8
497default collate=utf8_general_ci;
498
499create table peripheral(
500code int(7) zerofill primary key,
501name varchar(25) not null,
502codeType int(7) zerofill not null,
503foreign key (codeType) references type (code) on update cascade on delete no action)
504engine = InnoDB comment 'peripheral'
505default char set=utf8
506default collate=utf8_general_ci;
507
508create table copy_per(
509serial_number varchar(20) primary key,
510purchased_date date not null,
511waranty_date date not null,
512check (waranty_date>purchased_date),
513codePeripheral int(7) zerofill not null,
514serialCopy varchar(20) not null,
515foreign key (codePeripheral) references peripheral(code) on update cascade on delete no action,
516foreign key (serialCopy) references copy(serial_number) on update cascade on delete no action)
517engine = InnoDB comment 'copy_per'
518default char set=utf8
519default collate=utf8_general_ci;