· 8 years ago · Mar 14, 2018, 11:18 PM
1-- MySQL Script generated by MySQL Workbench
2-- Wed Mar 14 20:12:09 2018
3-- Model: New Model Version: 1.0
4-- MySQL Workbench Forward Engineering
5
6SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
7SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
8SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES';
9
10-- -----------------------------------------------------
11-- Schema theowl
12-- -----------------------------------------------------
13
14-- -----------------------------------------------------
15-- Schema theowl
16-- -----------------------------------------------------
17CREATE SCHEMA IF NOT EXISTS `theowl` DEFAULT CHARACTER SET utf8 ;
18-- -----------------------------------------------------
19-- Schema teste
20-- -----------------------------------------------------
21USE `theowl` ;
22
23-- -----------------------------------------------------
24-- Table `theowl`.`bookscad`
25-- -----------------------------------------------------
26CREATE TABLE IF NOT EXISTS `theowl`.`bookscad` (
27 `idbooks` INT NOT NULL AUTO_INCREMENT,
28 `bc_tittle` VARCHAR(30) NOT NULL,
29 `bc_subtittle` VARCHAR(50) NULL,
30 PRIMARY KEY (`idbooks`))
31ENGINE = InnoDB;
32
33
34-- -----------------------------------------------------
35-- Table `theowl`.`publishers`
36-- -----------------------------------------------------
37CREATE TABLE IF NOT EXISTS `theowl`.`publishers` (
38 `idpublishers` INT NOT NULL AUTO_INCREMENT,
39 `pb_name` VARCHAR(30) NOT NULL,
40 `publisherscol` VARCHAR(45) NULL,
41 PRIMARY KEY (`idpublishers`),
42 UNIQUE INDEX `pb_name_UNIQUE` (`pb_name` ASC))
43ENGINE = InnoDB;
44
45
46-- -----------------------------------------------------
47-- Table `theowl`.`booksvar`
48-- -----------------------------------------------------
49CREATE TABLE IF NOT EXISTS `theowl`.`booksvar` (
50 `idbooksvar` INT NOT NULL AUTO_INCREMENT,
51 `bv_isbn13` VARCHAR(13) NULL,
52 `bv_year` INT NOT NULL,
53 `bv_page` INT NOT NULL,
54 `bv_volume` INT NULL,
55 `bv_weight` FLOAT NOT NULL,
56 `bv_translate` VARCHAR(15) NULL,
57 `bv_cover` VARCHAR(100) NULL,
58 `fk_bookscad` INT NOT NULL,
59 `fk_publisher` INT NOT NULL,
60 PRIMARY KEY (`idbooksvar`),
61 INDEX `fk_idbooks_bc_idx` (`fk_bookscad` ASC),
62 INDEX `fk_publisher_idx` (`fk_publisher` ASC),
63 CONSTRAINT `fk_bookscad`
64 FOREIGN KEY (`fk_bookscad`)
65 REFERENCES `theowl`.`bookscad` (`idbooks`)
66 ON DELETE NO ACTION
67 ON UPDATE NO ACTION,
68 CONSTRAINT `fk_publisher`
69 FOREIGN KEY (`fk_publisher`)
70 REFERENCES `theowl`.`publishers` (`idpublishers`)
71 ON DELETE NO ACTION
72 ON UPDATE NO ACTION)
73ENGINE = InnoDB;
74
75
76-- -----------------------------------------------------
77-- Table `theowl`.`authorbk`
78-- -----------------------------------------------------
79CREATE TABLE IF NOT EXISTS `theowl`.`authorbk` (
80 `idauthorbk` INT NOT NULL AUTO_INCREMENT,
81 `at_name` VARCHAR(12) NOT NULL,
82 `at_lastname` VARCHAR(12) NOT NULL,
83 PRIMARY KEY (`idauthorbk`))
84ENGINE = InnoDB;
85
86
87-- -----------------------------------------------------
88-- Table `theowl`.`languagebk`
89-- -----------------------------------------------------
90CREATE TABLE IF NOT EXISTS `theowl`.`languagebk` (
91 `idlanguagebk` INT NOT NULL AUTO_INCREMENT,
92 `lg_desc` VARCHAR(20) NOT NULL,
93 PRIMARY KEY (`idlanguagebk`),
94 UNIQUE INDEX `lg_desc_UNIQUE` (`lg_desc` ASC))
95ENGINE = InnoDB;
96
97
98-- -----------------------------------------------------
99-- Table `theowl`.`languagebk_has_booksvar`
100-- -----------------------------------------------------
101CREATE TABLE IF NOT EXISTS `theowl`.`languagebk_has_booksvar` (
102 `languagebk_idlanguagebk` INT NOT NULL,
103 `booksvar_idbooksvar` INT NOT NULL,
104 PRIMARY KEY (`languagebk_idlanguagebk`, `booksvar_idbooksvar`),
105 INDEX `fk_languagebk_has_booksvar_booksvar1_idx` (`booksvar_idbooksvar` ASC),
106 INDEX `fk_languagebk_has_booksvar_languagebk1_idx` (`languagebk_idlanguagebk` ASC),
107 CONSTRAINT `fk_languagebk_has_booksvar_languagebk1`
108 FOREIGN KEY (`languagebk_idlanguagebk`)
109 REFERENCES `theowl`.`languagebk` (`idlanguagebk`)
110 ON DELETE NO ACTION
111 ON UPDATE NO ACTION,
112 CONSTRAINT `fk_languagebk_has_booksvar_booksvar1`
113 FOREIGN KEY (`booksvar_idbooksvar`)
114 REFERENCES `theowl`.`booksvar` (`idbooksvar`)
115 ON DELETE NO ACTION
116 ON UPDATE NO ACTION)
117ENGINE = InnoDB;
118
119
120-- -----------------------------------------------------
121-- Table `theowl`.`authorbk_has_bookscad`
122-- -----------------------------------------------------
123CREATE TABLE IF NOT EXISTS `theowl`.`authorbk_has_bookscad` (
124 `authorbk_idauthorbk` INT NOT NULL,
125 `bookscad_idbooks` INT NOT NULL,
126 PRIMARY KEY (`authorbk_idauthorbk`, `bookscad_idbooks`),
127 INDEX `fk_authorbk_has_bookscad_bookscad1_idx` (`bookscad_idbooks` ASC),
128 INDEX `fk_authorbk_has_bookscad_authorbk1_idx` (`authorbk_idauthorbk` ASC),
129 CONSTRAINT `fk_authorbk_has_bookscad_authorbk1`
130 FOREIGN KEY (`authorbk_idauthorbk`)
131 REFERENCES `theowl`.`authorbk` (`idauthorbk`)
132 ON DELETE NO ACTION
133 ON UPDATE NO ACTION,
134 CONSTRAINT `fk_authorbk_has_bookscad_bookscad1`
135 FOREIGN KEY (`bookscad_idbooks`)
136 REFERENCES `theowl`.`bookscad` (`idbooks`)
137 ON DELETE NO ACTION
138 ON UPDATE NO ACTION)
139ENGINE = InnoDB;
140
141
142-- -----------------------------------------------------
143-- Table `theowl`.`edtionbk`
144-- -----------------------------------------------------
145CREATE TABLE IF NOT EXISTS `theowl`.`edtionbk` (
146 `idedtionbk` INT NOT NULL AUTO_INCREMENT,
147 `ed_desc` INT NULL,
148 `ed_binding` INT NULL,
149 `ed_vol` INT NULL,
150 `ed_reprint` INT NULL,
151 `ed_special` TINYINT NULL,
152 `ed_collectible` TINYINT NULL,
153 `ed_limited` TINYINT NULL,
154 PRIMARY KEY (`idedtionbk`))
155ENGINE = InnoDB;
156
157
158-- -----------------------------------------------------
159-- Table `theowl`.`edtionbk_has_booksvar`
160-- -----------------------------------------------------
161CREATE TABLE IF NOT EXISTS `theowl`.`edtionbk_has_booksvar` (
162 `edtionbk_idedtionbk` INT NOT NULL,
163 `booksvar_idbooksvar` INT NOT NULL,
164 PRIMARY KEY (`edtionbk_idedtionbk`, `booksvar_idbooksvar`),
165 INDEX `fk_editionbk_has_booksvar_booksvar1_idx` (`booksvar_idbooksvar` ASC),
166 INDEX `fk_editionbk_has_booksvar_editionbk1_idx` (`edtionbk_idedtionbk` ASC),
167 CONSTRAINT `fk_edtionbk_has_booksvar_edtionbk1`
168 FOREIGN KEY (`edtionbk_idedtionbk`)
169 REFERENCES `theowl`.`edtionbk` (`idedtionbk`)
170 ON DELETE NO ACTION
171 ON UPDATE NO ACTION,
172 CONSTRAINT `fk_edtionbk_has_booksvar_booksvar1`
173 FOREIGN KEY (`booksvar_idbooksvar`)
174 REFERENCES `theowl`.`booksvar` (`idbooksvar`)
175 ON DELETE NO ACTION
176 ON UPDATE NO ACTION)
177ENGINE = InnoDB;
178
179USE `theowl` ;
180
181-- -----------------------------------------------------
182-- procedure bc_ins
183-- -----------------------------------------------------
184
185DELIMITER $$
186USE `theowl`$$
187CREATE DEFINER=`root`@`localhost` PROCEDURE `bc_ins`(
188pd_tittle VARCHAR(30),
189pd_subtittle VARCHAR(50)
190)
191BEGIN
192
193insert bookscad (bc_tittle, bc_subtittle) values (pd_tittle, pd_subtittle);
194select last_insert_id() as pd_return;
195END$$
196
197DELIMITER ;
198
199SET SQL_MODE=@OLD_SQL_MODE;
200SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
201SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;
202
203select * from bookscad;
204select * from authorbk;
205select * from booksvar;
206select * from languagebk;
207select * from edtionbk;
208select * from publishers;
209
210select * from authorbk_has_bookscad;
211select * from edtionbk_has_booksvar;
212select * from languagebk_has_booksvar;
213
214
215insert into languagebk (idlanguagebk, lg_desc) values (1, 'Português');
216insert into languagebk (idlanguagebk, lg_desc) values (2, 'Inglês');
217insert into languagebk (idlanguagebk, lg_desc) values (3, 'Espanhol');
218
219insert into bookscad (bc_tittle, bc_subtittle) values ('O mundo de Sofia','Romance da história da filosofia');
220
221insert into authorbk (authorbk.at_name, authorbk.at_lastname) values ('Jostein', 'Gaarder');
222
223insert into publishers (publishers.pb_name) values ('Companhia das Letras');
224
225 insert into authorbk_has_bookscad (authorbk_idauthorbk, bookscad_idbooks) values (1,1);
226
227insert into booksvar (bv_isbn13, bv_year, bv_page, bv_volume, bv_weight, bv_translate, bv_cover, fk_bookscad, fk_publisher)
228 values ('9788571644755',1995,555,0,600,'','https://statics.livrariacultura.net.br/products/capas_lg/545/64545.jpg',1,1);
229
230 insert into languagebk_has_booksvar (languagebk_idlanguagebk, booksvar_idbooksvar) values (1,1);
231
232insert into edtionbk (ed_desc, ed_binding, ed_vol, ed_reprint, ed_special, ed_collectible, ed_limited)
233 values (1,1,0,2,0,0,0);
234
235 insert into edtionbk_has_booksvar (edtionbk_idedtionbk, booksvar_idbooksvar) values (1,1);
236
237
238
239update bookscad set bc_tittle = 'Schachmatt', bc_subtittle = 'Das große Jostein-Gaarder-Lesebuch' where idbooks = 2;
240
241
242select * from bookscad;
243select * from authorbk;
244select * from booksvar;
245select * from languagebk;
246select * from edtionbk;
247select * from publishers;
248
249select * from authorbk_has_bookscad;
250select * from edtionbk_has_booksvar;
251select * from languagebk_has_booksvar;
252
253
254insert into languagebk (idlanguagebk, lg_desc) values (1, 'Português');
255insert into languagebk (idlanguagebk, lg_desc) values (2, 'Inglês');
256insert into languagebk (idlanguagebk, lg_desc) values (3, 'Espanhol');
257insert into languagebk (idlanguagebk, lg_desc) values (4, 'Alemão');
258
259-- insert into bookscad (bc_tittle, bc_subtittle) values ('Schachmatt','Das große Jostein-Gaarder-Lesebuch');
260
261-- insert into authorbk (authorbk.at_name, authorbk.at_lastname) values ('Jostein', 'Gaarder');
262
263insert into publishers (publishers.pb_name) values ('Carl Hanser Verlag');
264
265 insert into authorbk_has_bookscad (authorbk_idauthorbk, bookscad_idbooks) values (1,2);
266
267insert into booksvar (bv_isbn13, bv_year, bv_page, bv_volume, bv_weight, bv_translate, bv_cover, fk_bookscad, fk_publisher)
268 values ('9783446207875',2006,370,0,626,'Gabriele Haefs','https://images-na.ssl-images-amazon.com/images/I/51De8HXJkjL._SX324_BO1,204,203,200_.jpg',2,2);
269
270 insert into languagebk_has_booksvar (languagebk_idlanguagebk, booksvar_idbooksvar) values (3,2);
271
272insert into edtionbk (ed_desc, ed_binding, ed_vol, ed_reprint, ed_special, ed_collectible, ed_limited)
273 values (2,2,0,0,0,0,0);
274
275 insert into edtionbk_has_booksvar (edtionbk_idedtionbk, booksvar_idbooksvar) values (2,2);
276
277
278-- INSERT DO 3 LIVRO--
279
280insert into bookscad (bc_tittle, bc_subtittle) values ('Um Dia','Vinte anos. Duas Pessoas');
281insert into authorbk (authorbk.at_name, authorbk.at_lastname) values ('Nicholas', 'Sparks');
282insert into publishers (publishers.pb_name) values ('Novo Conceito');
283
284
285
286insert into booksvar (bv_isbn13, bv_year, bv_page, bv_volume, bv_weight, bv_translate, bv_cover, fk_bookscad, fk_publisher)
287 values ('9788563219022',2010,288,0,430,'Claudio Carina','https://images.livrariasaraiva.com.br/imagemnet/imagem.aspx/?pro_id=3448189&qld=90&l=430&a=-1',3,3);
288
289 insert into authorbk_has_bookscad (authorbk_idauthorbk, bookscad_idbooks) values (2,3);
290 insert into languagebk_has_booksvar (languagebk_idlanguagebk, booksvar_idbooksvar) values (3,2);
291
292insert into edtionbk (ed_desc, ed_binding, ed_vol, ed_reprint, ed_special, ed_collectible, ed_limited)
293 values (2,2,0,0,0,0,0);
294
295 insert into edtionbk_has_booksvar (edtionbk_idedtionbk, booksvar_idbooksvar) values (2,2);
296
297 insert into languagebk_has_booksvar (languagebk_idlanguagebk, booksvar_idbooksvar) values (1,1);
298
299insert into edtionbk (ed_desc, ed_binding, ed_vol, ed_reprint, ed_special, ed_collectible, ed_limited)
300 values (1,1,0,2,0,0,0);
301
302 insert into edtionbk_has_booksvar (edtionbk_idedtionbk, booksvar_idbooksvar) values (1,1);