m2rrQMdq

1drop table if exists book;
2drop table if exists author;
3drop table if exists book_author;
4
5CREATE TABLE book (
6    id bigint NOT NULL,
7    name character varying(255) NOT NULL,
8    description character varying(255) NOT NULL
9);
10
11ALTER TABLE ONLY book ADD CONSTRAINT book_pkey PRIMARY KEY (id);
12
13
14CREATE TABLE author (
15    id bigint NOT NULL,
16    first_name character varying(255) NOT NULL,
17    last_name character varying(255) NOT NULL
18);
19
20ALTER TABLE ONLY author ADD CONSTRAINT author_pkey PRIMARY KEY (id);
21
22CREATE TABLE book_author (
23    book_id bigint NOT NULL,
24    author_id bigint NOT NULL
25);
26
27ALTER TABLE ONLY book_author ADD CONSTRAINT book_author_pkey PRIMARY KEY (book_id, author_id);
28
29-- ---------------
30-- ---------------
31
32insert into author(id, first_name, last_name) values (1, 'James', 'Joyce');
33insert into author(id, first_name, last_name) values (2, 'Herman', 'Melville');
34insert into author(id, first_name, last_name) values (3, 'William', 'Shakespeare');
35insert into author(id, first_name, last_name) values (4, 'Leo', 'Tolstoy');
36insert into author(id, first_name, last_name) values (5, 'Dante', 'Alighieri');
37insert into author(id, first_name, last_name) values (6, 'Fyodor', 'Dostoyevsky');
38
39insert into book(id, name, description) values(1, 'Ulysses', 'Fill me in later');
40insert into book(id, name, description) values(2, 'Moby Dick', 'Fill me in later');
41insert into book(id, name, description) values(3, 'Hamlet', 'Fill me in later');
42insert into book(id, name, description) values(4, 'War and Peace', 'Fill me in later');
43insert into book(id, name, description) values(5, 'The Divine Comedy', 'Fill me in later');
44insert into book(id, name, description) values(6, 'The Brothers Karamazov', 'Fill me in later');
45insert into book(id, name, description) values(7, 'Crime and Punishment', 'Fill me in later');
46insert into book(id, name, description) values(8, 'Anna Karenina', 'Fill me in later');
47insert into book(id, name, description) values(9, 'A Portrait of the Artist as a Young Man', 'Fill me in later');
48
49insert into book_author(book_id, author_id) values(1, 1);
50insert into book_author(book_id, author_id) values(2, 2);
51insert into book_author(book_id, author_id) values(3, 3);
52insert into book_author(book_id, author_id) values(4, 4);
53insert into book_author(book_id, author_id) values(5, 5);
54insert into book_author(book_id, author_id) values(6, 6);
55insert into book_author(book_id, author_id) values(7, 6);
56insert into book_author(book_id, author_id) values(8, 4);
57insert into book_author(book_id, author_id) values(9, 1);
58
59-- ---------------
60-- ---------------
61
62drop table if exists category;
63drop table if exists book_category;
64
65CREATE TABLE category (
66    id bigint NOT NULL,
67    name character varying(255) NOT NULL
68);
69
70ALTER TABLE ONLY category ADD CONSTRAINT category_pkey PRIMARY KEY (id);
71
72CREATE TABLE book_category (
73    book_id bigint NOT NULL,
74    category_id bigint NOT NULL
75);
76
77ALTER TABLE ONLY book_category ADD CONSTRAINT book_category_pkey PRIMARY KEY (book_id, category_id);
78
79-- ---------------
80-- ---------------
81
82insert into category(id, name) values(1, 'Fiction');
83insert into category(id, name) values(2, 'Romance');
84insert into category(id, name) values(3, 'History');
85insert into category(id, name) values(4, 'Biography');
86
87insert into book_category(book_id, category_id) values(1, 3);
88insert into book_category(book_id, category_id) values(1, 4);
89insert into book_category(book_id, category_id) values(2, 1);
90insert into book_category(book_id, category_id) values(3, 1);
91insert into book_category(book_id, category_id) values(3, 2);
92insert into book_category(book_id, category_id) values(4, 1);
93insert into book_category(book_id, category_id) values(5, 1);
94insert into book_category(book_id, category_id) values(6, 1);
95insert into book_category(book_id, category_id) values(7, 1);
96insert into book_category(book_id, category_id) values(8, 1);
97insert into book_category(book_id, category_id) values(8, 2);
98insert into book_category(book_id, category_id) values(9, 1);