2gPnre0K

1DROP DATABASE IF EXISTS cursoplatzi;
2
3CREATE DATABASE cursoplatzi;
4USE cursoplatzi;
5
6CREATE TABLE `authors` (
7  `author_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
8  `name` varchar(100) NOT NULL,
9  `nationality` varchar(100) DEFAULT NULL,
10  PRIMARY KEY (`author_id`),
11  UNIQUE KEY `uniq_author` (`name`)
12) ENGINE=InnoDB AUTO_INCREMENT=193 DEFAULT CHARSET=utf8;
13
14CREATE TABLE `books` (
15  `book_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
16  `author_id` int(10) unsigned DEFAULT NULL,
17  `title` varchar(100) NOT NULL,
18  `year` int(11) NOT NULL DEFAULT '1900',
19  `language` varchar(2) NOT NULL COMMENT 'ISO 639-1 Language code (2 chars)',
20  `cover_url` varchar(500) DEFAULT NULL,
21  `price` double(6,2) DEFAULT NULL,
22  `sellable` tinyint(1) NOT NULL DEFAULT '0',
23  `copies` int(11) NOT NULL DEFAULT '1',
24  `description` text,
25  PRIMARY KEY (`book_id`),
26  UNIQUE KEY `book_language` (`title`,`language`)
27) ENGINE=InnoDB AUTO_INCREMENT=199 DEFAULT CHARSET=utf8;
28
29CREATE TABLE `clients` (
30  `client_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
31  `name` varchar(50) DEFAULT NULL,
32  `email` varchar(100) NOT NULL,
33  `birthdate` date DEFAULT NULL,
34  `gender` enum('M','F') DEFAULT NULL,
35  `active` tinyint(1) NOT NULL DEFAULT '1',
36  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
37  PRIMARY KEY (`client_id`),
38  UNIQUE KEY `email` (`email`)
39) ENGINE=InnoDB AUTO_INCREMENT=101 DEFAULT CHARSET=utf8;
40
41
42
43CREATE TABLE `transactions` (
44  `transaction_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
45  `book_id` int(10) unsigned NOT NULL,
46  `client_id` int(10) unsigned NOT NULL,
47  `type` enum('lend','sell','return') NOT NULL,
48  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
49  `modified_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
50  `finished` tinyint(1) NOT NULL DEFAULT '0',
51  PRIMARY KEY (`transaction_id`)
52) ENGINE=InnoDB DEFAULT CHARSET=utf8;