z0wY3x5A

1CREATE TABLE IF NOT EXISTS books (
2  book_id INTEGER UNSIGNED PRIMARY KEY AUTO_INCREMENT,
3  author_id INTEGER UNSIGNED,
4  title VARCHAR(100) NOT NULL,
5  year INTEGER UNSIGNED NOT NULL DEFAULT 1900,
6  language VARCHAR(2) NOT NULL DEFAULT 'es' COMMENT 'ISO 639-1 Language',
7  cover_url VARCHAR(500),
8  price DOUBLE(6,2) NOT NULL DEFAULT 10.0,
9  sellable TINYINT(1) DEFAULT 1,
10  copies INTEGER NOT NULL DEFAULT 1,
11  description TEXT
12);
13
14CREATE TABLE IF NOT EXISTS author (
15  author_id INTEGER UNSIGNED PRIMARY KEY AUTO_INCREMENT,
16  name VARCHAR(100) NOT NULL,
17  nationality VARCHAR(3)
18);
19
20CREATE TABLE clients (
21  client_id INTEGER UNSIGNED PRIMARY KEY AUTO_INCREMENT,
22  `name` VARCHAR(50) NOT NULL,
23  email VARCHAR(100) NOT NULL UNIQUE,
24  birthdate DATETIME,
25  gender ENUM('M', 'F', 'ND') NOT NULL,
26  active TINYINT(1) NOT NULL DEFAULT 1,
27  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
28  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
29);
30
31CREATE TABLE IF NOT EXISTS operations (
32  operation_id INTEGER UNSIGNED PRIMARY KEY AUTO_INCREMENT,
33  book_id INTEGER UNSIGNED NOT NULL,
34  client_id INTEGER UNSIGNED NOT NULL,
35  `type` ENUM('prestado', 'devuelto', 'vendido') NOT NULL,
36  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
37  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
38  finished TINYINT(1) NOT NULL DEFAULT 0
39);
40
41INSERT INTO author(author_id, name, nationality)
42VALUES (NULL, 'Juan Rulfo', 'MEX');
43
44INSERT INTO author(name, nationality)
45VALUES ('Gabriel Garcia Marquez', 'COL');
46
47INSERT INTO author()
48VALUES (NULL, 'Juan Gabriel Vasquez', 'COL');
49
50INSERT INTO author(name, nationality)
51VALUES ('Julio Cortazar', 'ARG'),
52       ('Isabel Allende', 'CHI'),
53       ('Octavio Paz', 'MEX'),
54       ('Juan Carlos Onetti', 'URU')
55       ;
56
57INSERT INTO author(author_id, name)
58VALUES (16, 'Pablo Neruda');
59
60INSERT INTO `clients`(client_id, name, email, birthdate, gender, active, created_at)
61VALUES (1,'Maria Dolores Gomez','Maria Dolores.95983222J@random.names','1971-06-06','F',1,'2018-04-09 16:51:30'),
62       (2,'Adrian Fernandez','Adrian.55818851J@random.names','1970-04-09','M',1,'2018-04-09 16:51:30'),
63       (3,'Maria Luisa Marin','Maria Luisa.83726282A@random.names','1957-07-30','F',1,'2018-04-09 16:51:30'),
64       (4,'Pedro Sanchez','Pedro.78522059J@random.names','1992-01-31','M',1,'2018-04-09 16:51:30')
65       ;
66
67INSERT INTO books(title, author_id)
68VALUES ('El Laberinto de la Soledad', 6);
69
70INSERT INTO books(title, author_id, `year`)
71VALUES ('Vuelta al laberinto de la Soledad',
72  (SELECT author_id FROM author
73  WHERE name = 'Octavio Paz'
74  LIMIT 1
75  ), 1960
76);