fbUkkYiA

1DROP TABLE IF EXISTS book_author;
2DROP TABLE IF EXISTS author;
3DROP TABLE IF EXISTS book;
4DROP TABLE IF EXISTS editorial;
5DROP TABLE IF EXISTS country_language;
6DROP TABLE IF EXISTS language;
7DROP TABLE IF EXISTS country;
8
9CREATE TABLE country
10(
11    id                  int not null auto_increment,
12    name                character varying(255) not null,
13    original_name       character varying(255) not null,
14    iso_code            character varying(2) not null,
15
16    PRIMARY KEY         (id)
17);
18
19CREATE TABLE language
20(
21    id                  int not null auto_increment,
22    name                character varying(255) not null,
23    original_name       character varying(255) not null,
24    iso_code            character varying(2) not null,
25
26    PRIMARY KEY         (id)
27);
28
29CREATE TABLE country_language
30(
31    country_id          int not null,
32    language_id         int not null,
33
34    PRIMARY KEY         (country_id, language_id),
35
36    FOREIGN KEY         (country_id) REFERENCES country (id),
37    FOREIGN KEY         (language_id) REFERENCES language (id)
38);
39
40CREATE TABLE editorial
41(
42    id                  int not null auto_increment,
43    name                character varying(255) not null,
44    country_id          int not null,
45
46    PRIMARY KEY         (id),
47    UNIQUE KEY          (name),
48    FOREIGN KEY         (country_id) REFERENCES country (id)
49);
50
51CREATE TABLE book
52(
53    id                  int not null auto_increment,
54    name                character varying(255) not null,
55    description         text default null,
56    isbn                character varying(255) not null,
57    pages               int not null,
58    editorial_id        int not null,
59
60    PRIMARY KEY         (id),
61
62    UNIQUE KEY          (isbn),
63
64    FOREIGN KEY         (editorial_id) REFERENCES editorial (id)
65);
66
67CREATE TABLE author
68(
69    id                  int not null auto_increment,
70    first_name          character varying(255) not null,
71    last_name           character varying(255) not null,
72    country_id          int not null,
73    language_id         int not null,
74
75    PRIMARY KEY         (id),
76
77    FOREIGN KEY         (country_id) REFERENCES country (id),
78    FOREIGN KEY         (language_id) REFERENCES language (id)
79);
80
81CREATE TABLE book_author
82(
83    book_id             int not null,
84    author_id           int not null,
85
86    PRIMARY KEY         (book_id, author_id),
87
88    FOREIGN KEY         (book_id) REFERENCES book (id),
89    FOREIGN KEY         (author_id) REFERENCES author (id)
90);