База данных и схема

=> CREATE DATABASE bookstore;
CREATE DATABASE
=> \c bookstore
You are now connected to database "bookstore" as user "student".
=> CREATE SCHEMA bookstore;
CREATE SCHEMA
=> ALTER DATABASE bookstore SET search_path = bookstore, public;
ALTER DATABASE
=> \c bookstore
You are now connected to database "bookstore" as user "student".
=> SHOW search_path;
    search_path    
-------------------
 bookstore, public
(1 row)

Авторы

=> CREATE TABLE authors(
    author_id serial PRIMARY KEY,
    last_name text,
    first_name text,
    surname text
);
CREATE TABLE
=> COPY authors(author_id, last_name, first_name, surname) FROM stdin;
1	Пушкин	Александр	Сергеевич
2	Тургенев	Иван	Сергеевич
3	Стругацкий	Борис	Натанович
4	Стругацкий	Аркадий	Натанович
5	Толстой	Лев	Николаевич
6	Свифт	Джонатан	\N
\.
COPY 6
=> SELECT pg_catalog.setval('authors_author_id_seq', 6, true);
 setval 
--------
      6
(1 row)

Книги

=> CREATE TABLE books(
    book_id serial PRIMARY KEY,
    title text
);
CREATE TABLE
=> COPY books (book_id, title) FROM stdin;
1	Сказка о царе Салтане
2	Муму
3	Трудно быть богом
4	Война и мир
5	Путешествия в некоторые удаленные страны мира в четырех частях: сочинение Лемюэля Гулливера, сначала хирурга, а затем капитана нескольких кораблей
6	Хрестоматия
\.
COPY 6
=> SELECT pg_catalog.setval('books_book_id_seq', 7, true);
 setval 
--------
      7
(1 row)

Авторство

=> CREATE TABLE authorship(
    book_id integer NOT NULL REFERENCES books,
    author_id integer NOT NULL REFERENCES authors,
    seq_num integer,
    UNIQUE (book_id,author_id)
);
CREATE TABLE
=> COPY authorship(book_id, author_id, seq_num) FROM stdin;
1	1	1
2	2	1
3	3	2
3	4	1
4	5	1
5	6	1
6	1	1
6	5	2
6	2	3
\.
COPY 9

Операции

=> CREATE TABLE operations(
    operation_id serial PRIMARY KEY,
    book_id integer NOT NULL REFERENCES books,
    qty_change integer,
    date_created date DEFAULT current_date
);
CREATE TABLE
=> COPY operations (operation_id, book_id, qty_change) FROM stdin;
1	1	10
2	1	10
3	1	-1
\.
COPY 3
=> SELECT pg_catalog.setval('operations_operation_id_seq', 3, true);
 setval 
--------
      3
(1 row)