ADYKktLK

1ALTER TABLE ONLY in_category DROP CONSTRAINT in_category_isbn_fkey;
2ALTER TABLE ONLY in_category DROP CONSTRAINT in_category_category_id_fkey;
3ALTER TABLE ONLY copies DROP CONSTRAINT copies_isbn_fkey;
4ALTER TABLE ONLY authorship DROP CONSTRAINT authorship_isbn_fkey;
5ALTER TABLE ONLY authorship DROP CONSTRAINT authorship_author_id_fkey;
6ALTER TABLE ONLY in_category DROP CONSTRAINT in_category_pkey;
7ALTER TABLE ONLY copies DROP CONSTRAINT copies_pkey;
8ALTER TABLE ONLY categories DROP CONSTRAINT categories_pkey;
9ALTER TABLE ONLY books DROP CONSTRAINT books_pkey;
10ALTER TABLE ONLY authorship DROP CONSTRAINT authorship_pkey;
11ALTER TABLE ONLY authors DROP CONSTRAINT authors_pkey;
12ALTER TABLE copies ALTER COLUMN copy_id DROP DEFAULT;
13ALTER TABLE categories ALTER COLUMN category_id DROP DEFAULT;
14
15DROP TABLE IF EXISTS in_category CASCADE;
16DROP TABLE IF EXISTS copies CASCADE;
17DROP TABLE IF EXISTS categories CASCADE;
18DROP TABLE IF EXISTS books CASCADE;
19DROP TABLE IF EXISTS authorship CASCADE;
20DROP TABLE IF EXISTS authors CASCADE;
21
22--
23
24CREATE TABLE authors (
25    author_id text NOT NULL,
26    name text NOT NULL
27);
28
29
30CREATE TABLE authorship (
31    isbn text NOT NULL,
32    author_id text NOT NULL
33);
34
35
36CREATE TABLE books (
37    isbn text NOT NULL,
38    publisher text NOT NULL,
39    title text NOT NULL,
40    publication_year integer,
41    CONSTRAINT books_publication_year_check CHECK (((publication_year > 1800) AND (publication_year < 2018))),
42    CONSTRAINT books_publisher_check CHECK ((length(publisher) > 0))
43);
44
45CREATE TABLE categories (
46    category_id integer NOT NULL,
47    category_name text NOT NULL
48);
49
50CREATE TABLE copies (
51    copy_id integer NOT NULL,
52    isbn text NOT NULL
53);
54
55CREATE TABLE in_category (
56    isbn text NOT NULL,
57    category_id integer NOT NULL
58);
59
60CREATE SEQUENCE categories_category_id_seq
61    START WITH 1
62    INCREMENT BY 1
63    NO MINVALUE
64    NO MAXVALUE
65    CACHE 1;
66
67ALTER SEQUENCE categories_category_id_seq OWNED BY categories.category_id;
68
69CREATE SEQUENCE copies_copy_id_seq
70    START WITH 1
71    INCREMENT BY 1
72    NO MINVALUE
73    NO MAXVALUE
74    CACHE 1;
75
76ALTER SEQUENCE copies_copy_id_seq OWNED BY copies.copy_id;
77
78
79ALTER TABLE ONLY categories ALTER COLUMN category_id SET DEFAULT nextval('categories_category_id_seq'::regclass);
80ALTER TABLE ONLY copies ALTER COLUMN copy_id SET DEFAULT nextval('copies_copy_id_seq'::regclass);
81
82\copy authors (author_id, name) FROM './2213.dat';
83\copy authorship (isbn, author_id) FROM './2214.dat';
84\copy books (isbn, publisher, title, publication_year) FROM './2215.dat';
85\copy categories (category_id, category_name) FROM './2216.dat';
86\copy copies (copy_id, isbn) FROM './2218.dat';
87\copy in_category (isbn, category_id) FROM './2220.dat';
88
89--
90
91SELECT pg_catalog.setval('categories_category_id_seq', 8, true);
92SELECT pg_catalog.setval('copies_copy_id_seq', 48739, true);
93
94--
95
96ALTER TABLE ONLY authors
97    ADD CONSTRAINT authors_pkey PRIMARY KEY (author_id);
98
99ALTER TABLE ONLY authorship
100    ADD CONSTRAINT authorship_pkey PRIMARY KEY (isbn, author_id);
101
102ALTER TABLE ONLY books
103    ADD CONSTRAINT books_pkey PRIMARY KEY (isbn);
104
105ALTER TABLE ONLY categories
106    ADD CONSTRAINT categories_pkey PRIMARY KEY (category_id);
107
108ALTER TABLE ONLY copies
109    ADD CONSTRAINT copies_pkey PRIMARY KEY (copy_id);
110
111ALTER TABLE ONLY in_category
112    ADD CONSTRAINT in_category_pkey PRIMARY KEY (isbn);
113
114ALTER TABLE ONLY authorship
115    ADD CONSTRAINT authorship_author_id_fkey FOREIGN KEY (author_id) REFERENCES authors(author_id) ON UPDATE CASCADE ON DELETE RESTRICT;
116
117ALTER TABLE ONLY authorship
118    ADD CONSTRAINT authorship_isbn_fkey FOREIGN KEY (isbn) REFERENCES books(isbn) ON UPDATE CASCADE ON DELETE CASCADE;
119
120ALTER TABLE ONLY copies
121    ADD CONSTRAINT copies_isbn_fkey FOREIGN KEY (isbn) REFERENCES books(isbn) ON UPDATE CASCADE ON DELETE CASCADE;
122
123ALTER TABLE ONLY in_category
124    ADD CONSTRAINT in_category_category_id_fkey FOREIGN KEY (category_id) REFERENCES categories(category_id) ON UPDATE CASCADE ON DELETE RESTRICT;
125
126ALTER TABLE ONLY in_category
127    ADD CONSTRAINT in_category_isbn_fkey FOREIGN KEY (isbn) REFERENCES books(isbn) ON UPDATE CASCADE ON DELETE CASCADE;