Created
October 20, 2019 09:19
-
-
Save geoextra/d300e63851aabbfbc23e5ae46c795a5c to your computer and use it in GitHub Desktop.
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| SET statement_timeout = 0; | |
| SET lock_timeout = 0; | |
| SET idle_in_transaction_session_timeout = 0; | |
| SET client_encoding = 'UTF8'; | |
| SET standard_conforming_strings = on; | |
| SELECT pg_catalog.set_config('search_path', '', false); | |
| SET check_function_bodies = false; | |
| SET xmloption = content; | |
| SET client_min_messages = warning; | |
| SET row_security = off; | |
| CREATE EXTENSION IF NOT EXISTS isn WITH SCHEMA public; | |
| COMMENT ON EXTENSION isn IS 'data types for international product numbering standards'; | |
| CREATE EXTENSION IF NOT EXISTS "uuid-ossp" WITH SCHEMA public; | |
| COMMENT ON EXTENSION "uuid-ossp" IS 'generate universally unique identifiers (UUIDs)'; | |
| SET default_tablespace = ''; | |
| SET default_with_oids = false; | |
| CREATE TABLE public.author ( | |
| id integer NOT NULL, | |
| firstname character varying(100), | |
| lastname character varying(100) NOT NULL | |
| ); | |
| ALTER TABLE public.author OWNER TO postgres; | |
| CREATE SEQUENCE public.author_id_seq | |
| AS integer | |
| START WITH 1 | |
| INCREMENT BY 1 | |
| NO MINVALUE | |
| NO MAXVALUE | |
| CACHE 1; | |
| ALTER TABLE public.author_id_seq OWNER TO postgres; | |
| ALTER SEQUENCE public.author_id_seq OWNED BY public.author.id; | |
| CREATE TABLE public.book ( | |
| isbn public.isbn NOT NULL, | |
| title character varying(100) NOT NULL, | |
| description text, | |
| year integer NOT NULL, | |
| genre character varying(15) NOT NULL, | |
| price money, | |
| author integer NOT NULL | |
| ); | |
| ALTER TABLE public.book OWNER TO postgres; | |
| CREATE TABLE public.copy ( | |
| id integer NOT NULL, | |
| book public.isbn NOT NULL | |
| ); | |
| ALTER TABLE public.copy OWNER TO postgres; | |
| CREATE SEQUENCE public.copy_signiture_seq | |
| AS integer | |
| START WITH 1 | |
| INCREMENT BY 1 | |
| NO MINVALUE | |
| NO MAXVALUE | |
| CACHE 1; | |
| ALTER TABLE public.copy_signiture_seq OWNER TO postgres; | |
| ALTER SEQUENCE public.copy_signiture_seq OWNED BY public.copy.id; | |
| CREATE TABLE public.customer ( | |
| id integer NOT NULL, | |
| first_name character varying(255), | |
| last_name character varying(255), | |
| date_of_birth date, | |
| e_mail character varying(255), | |
| phone_number integer | |
| ); | |
| ALTER TABLE public.customer OWNER TO postgres; | |
| CREATE SEQUENCE public.customer_id_seq | |
| AS integer | |
| START WITH 1 | |
| INCREMENT BY 1 | |
| NO MINVALUE | |
| NO MAXVALUE | |
| CACHE 1; | |
| ALTER TABLE public.customer_id_seq OWNER TO postgres; | |
| ALTER SEQUENCE public.customer_id_seq OWNED BY public.customer.id; | |
| CREATE TABLE public.lending ( | |
| id integer NOT NULL, | |
| lending_date date NOT NULL, | |
| return_date date, | |
| customer integer, | |
| copy integer | |
| ); | |
| ALTER TABLE public.lending OWNER TO postgres; | |
| CREATE SEQUENCE public.lending_id_seq | |
| AS integer | |
| START WITH 1 | |
| INCREMENT BY 1 | |
| NO MINVALUE | |
| NO MAXVALUE | |
| CACHE 1; | |
| ALTER TABLE public.lending_id_seq OWNER TO postgres; | |
| ALTER SEQUENCE public.lending_id_seq OWNED BY public.lending.id; | |
| CREATE VIEW public."openLendings" AS | |
| SELECT customer.id, | |
| customer.last_name, | |
| date_part('day'::text, (now() - (lending.lending_date)::timestamp with time zone)) AS dayslended, | |
| ((21)::double precision - date_part('day'::text, (now() - (lending.lending_date)::timestamp with time zone))) AS daysleft, | |
| book.isbn, | |
| book.title | |
| FROM (((public.lending | |
| LEFT JOIN public.customer ON ((lending.customer = customer.id))) | |
| LEFT JOIN public.copy ON ((lending.copy = copy.id))) | |
| LEFT JOIN public.book ON ((copy.book OPERATOR(public.=) book.isbn))) | |
| WHERE (lending.return_date IS NULL); | |
| ALTER TABLE public."openLendings" OWNER TO postgres; | |
| CREATE TABLE public.rating ( | |
| id integer NOT NULL, | |
| comment text, | |
| rating integer, | |
| customer integer, | |
| book public.isbn, | |
| CONSTRAINT checkbetween1and10 CHECK (((rating < 10) AND (rating > 0))) | |
| ); | |
| ALTER TABLE public.rating OWNER TO postgres; | |
| CREATE SEQUENCE public.rating_id_seq | |
| AS integer | |
| START WITH 1 | |
| INCREMENT BY 1 | |
| NO MINVALUE | |
| NO MAXVALUE | |
| CACHE 1; | |
| ALTER TABLE public.rating_id_seq OWNER TO postgres; | |
| ALTER SEQUENCE public.rating_id_seq OWNED BY public.rating.id; | |
| CREATE VIEW public."viewAverageRating" AS | |
| SELECT | |
| NULL::public.isbn AS isbn, | |
| NULL::character varying(100) AS title, | |
| NULL::bigint AS counts, | |
| NULL::numeric AS average; | |
| ALTER TABLE public."viewAverageRating" OWNER TO postgres; | |
| COMMENT ON VIEW public."viewAverageRating" IS 'Shows the title and isbn of all book which have at least one rating and show the average rating. The books will be ordered by their average rating.'; | |
| ALTER TABLE ONLY public.author ALTER COLUMN id SET DEFAULT nextval('public.author_id_seq'::regclass); | |
| ALTER TABLE ONLY public.copy ALTER COLUMN id SET DEFAULT nextval('public.copy_signiture_seq'::regclass); | |
| ALTER TABLE ONLY public.customer ALTER COLUMN id SET DEFAULT nextval('public.customer_id_seq'::regclass); | |
| ALTER TABLE ONLY public.lending ALTER COLUMN id SET DEFAULT nextval('public.lending_id_seq'::regclass); | |
| ALTER TABLE ONLY public.rating ALTER COLUMN id SET DEFAULT nextval('public.rating_id_seq'::regclass); | |
| COPY public.author (id, firstname, lastname) FROM stdin; | |
| 1 Herman Melville | |
| 2 Astrid Lindgren | |
| 5 J.K Rowling | |
| 6 Stephen Hawking | |
| 7 Albert Camus | |
| 8 Johann Wolfgang von Goethe | |
| 9 Ton van Reen | |
| 11 Aldous Huxley | |
| \. | |
| COPY public.book (isbn, title, description, year, genre, price, author) FROM stdin; | |
| 0-393-09670-X Moby-Dick An Authoritative Text, Reviews and Letters by Melville, Analogues and Sources, Criticism. 1967 Novel $9.99 1 | |
| 963-627-173-9 Pippi Langkous Pippi ziet er wat raar uit met haar stijve vlechten, vreemde jurken en veel te grote schoenen. Maar voor haar buurkinderen is zij de sterkste, de stoutste en vooral de leukste kameraad. 1945 Child book \N 2 | |
| 0-545-01022-5 Harry Potter and relics of death Seventh and final novel of the Harry Potter series 2007 Fantasy \N 5 | |
| 0-7475-3269-9 Harry Potter and the Philosopher's Stone First novel of the Harry Potter series 1997 Fantasy $14.99 5 | |
| 81-7992-591-9 The theory of everything A book on Gravity, Black holes, matter-anti matter, light, quantum mechanics, time and space. 2004 Science \N 6 | |
| 0-679-72020-0 The stranger Through the story of an ordinary man unwittingly drawn into a senseless murder on an Algerian beach, Camus explored what he termed "the nakedness of man faced with the absurd." 1942 Fiction \N 7 | |
| 0-86547-076-6 Italian journey Goethe describes his travels through eighteenth-century Italy and the unusual characters and incidents he encountered along the way 1788 Diary \N 8 | |
| 90-269-5629-0 Het winterjaar About a boy who is confronted with the illness and death of his father 1986 Novel \N 9 | |
| \. | |
| COPY public.copy (id, book) FROM stdin; | |
| 1 0-393-09670-X | |
| 2 0-393-09670-X | |
| 3 81-7992-591-9 | |
| 4 0-679-72020-0 | |
| 5 81-7992-591-9 | |
| 6 0-7475-3269-9 | |
| 7 0-7475-3269-9 | |
| 8 0-7475-3269-9 | |
| 9 0-545-01022-5 | |
| 10 0-86547-076-6 | |
| \. | |
| COPY public.customer (id, first_name, last_name, date_of_birth, e_mail, phone_number) FROM stdin; | |
| 1 Freddy Mercury 1996-03-17 [email protected] 1234579 | |
| 2 Thomas Edison 1847-02-11 [email protected] 880022112 | |
| 4 Timothy Jefferson 1985-09-25 [email protected] 563247210 | |
| 3 Steppen Genius 1997-02-25 [email protected] 621761033 | |
| 5 Nick McNick 1998-12-17 [email protected] 887462133 | |
| 6 Jonathon Miles 1998-11-17 [email protected] 660500210 | |
| 7 Milo Miles 1998-11-17 [email protected] 660500211 | |
| 8 Tinker Miles 1998-11-17 [email protected] 660500212 | |
| \. | |
| COPY public.lending (id, lending_date, return_date, customer, copy) FROM stdin; | |
| 3 2019-10-01 \N 1 2 | |
| 4 2019-10-04 2019-10-15 1 1 | |
| 1 2019-08-26 2019-09-16 1 3 | |
| 2 2019-09-02 2019-09-23 6 4 | |
| 5 2019-10-02 \N 2 5 | |
| 6 2019-10-10 \N 3 6 | |
| 7 2019-09-27 \N 4 7 | |
| 8 2019-09-30 \N 8 9 | |
| 9 2019-09-30 2019-10-16 7 10 | |
| 10 2019-10-07 \N 5 10 | |
| 11 2019-10-15 \N 6 1 | |
| 12 2019-10-19 \N 6 2 | |
| \. | |
| COPY public.rating (id, comment, rating, customer, book) FROM stdin; | |
| 1 Nice but too much water 7 1 0-393-09670-X | |
| 2 Got better at the end 8 1 0-393-09670-X | |
| 3 A bit dark with the black holes 6 2 81-7992-591-9 | |
| 4 Still haven't received my Hogwarts letter 9 5 0-7475-3269-9 | |
| 5 A bit unrealistic with those braids to be honest 4 3 963-627-173-9 | |
| 7 Good literary work, writing style is improvable 8 7 963-627-173-9 | |
| 8 Typical Dutch literature 3 6 90-269-5629-0 | |
| 9 The Battle of Hogwarts is still heartbreaking 8 8 0-545-01022-5 | |
| 10 My French is not so good 5 6 0-679-72020-0 | |
| 11 Strange story with these sticks that can do anything 6 5 0-7475-3269-9 | |
| 6 A German in Italy? Suspicious... 6 4 0-86547-076-6 | |
| \. | |
| SELECT pg_catalog.setval('public.author_id_seq', 12, true); | |
| SELECT pg_catalog.setval('public.copy_signiture_seq', 2, true); | |
| SELECT pg_catalog.setval('public.customer_id_seq', 1, true); | |
| SELECT pg_catalog.setval('public.lending_id_seq', 4, true); | |
| SELECT pg_catalog.setval('public.rating_id_seq', 2, true); | |
| ALTER TABLE ONLY public.author | |
| ADD CONSTRAINT author_pkey PRIMARY KEY (id); | |
| ALTER TABLE ONLY public.copy | |
| ADD CONSTRAINT copy_pkey PRIMARY KEY (id); | |
| ALTER TABLE ONLY public.customer | |
| ADD CONSTRAINT customer_pkey PRIMARY KEY (id); | |
| ALTER TABLE ONLY public.book | |
| ADD CONSTRAINT isbn_pkey PRIMARY KEY (isbn); | |
| ALTER TABLE ONLY public.lending | |
| ADD CONSTRAINT lending_pkey PRIMARY KEY (id); | |
| ALTER TABLE ONLY public.rating | |
| ADD CONSTRAINT rating_pkey PRIMARY KEY (id); | |
| CREATE INDEX author_lastname_firstname_idx ON public.author USING btree (lastname, firstname); | |
| CREATE INDEX author_lastname_idx ON public.author USING btree (lastname); | |
| CREATE INDEX book_genre_idx ON public.book USING btree (genre); | |
| CREATE INDEX book_title_idx ON public.book USING btree (title); | |
| CREATE INDEX book_year_idx ON public.book USING btree (year); | |
| CREATE INDEX customer_last_name_first_name_idx ON public.customer USING btree (last_name, first_name); | |
| CREATE INDEX customer_last_name_idx ON public.customer USING btree (last_name); | |
| CREATE INDEX fki_lending_copy_fkey ON public.lending USING btree (copy); | |
| CREATE INDEX lending_lending_date_idx ON public.lending USING btree (lending_date); | |
| CREATE INDEX lending_lending_date_idx1 ON public.lending USING btree (lending_date DESC); | |
| CREATE INDEX lending_return_date_idx ON public.lending USING btree (return_date); | |
| CREATE INDEX lending_return_date_idx1 ON public.lending USING btree (return_date DESC); | |
| CREATE INDEX rating_book_idx ON public.rating USING btree (book); | |
| CREATE INDEX rating_customer_idx ON public.rating USING btree (customer); | |
| CREATE INDEX rating_rating_idx ON public.rating USING btree (rating DESC); | |
| CREATE INDEX rating_rating_idx1 ON public.rating USING btree (rating); | |
| CREATE OR REPLACE VIEW public."viewAverageRating" WITH (security_barrier='false') AS | |
| SELECT b.isbn, | |
| b.title, | |
| count(rating.rating) AS counts, | |
| round(avg(rating.rating), 1) AS average | |
| FROM (public.rating | |
| LEFT JOIN public.book b ON ((rating.book OPERATOR(public.=) b.isbn))) | |
| GROUP BY b.isbn | |
| ORDER BY (avg(rating.rating)) DESC; | |
| ALTER TABLE ONLY public.book | |
| ADD CONSTRAINT book_author_fkey FOREIGN KEY (author) REFERENCES public.author(id) ON DELETE CASCADE NOT VALID; | |
| ALTER TABLE ONLY public.copy | |
| ADD CONSTRAINT copy_book_fkey FOREIGN KEY (book) REFERENCES public.book(isbn) ON DELETE CASCADE NOT VALID; | |
| ALTER TABLE ONLY public.lending | |
| ADD CONSTRAINT lending_copy_fkey FOREIGN KEY (copy) REFERENCES public.copy(id) NOT VALID; | |
| ALTER TABLE ONLY public.lending | |
| ADD CONSTRAINT lending_customer_fkey FOREIGN KEY (customer) REFERENCES public.customer(id) NOT VALID; | |
| ALTER TABLE ONLY public.rating | |
| ADD CONSTRAINT rating_book_fkey FOREIGN KEY (book) REFERENCES public.book(isbn) ON DELETE CASCADE NOT VALID; | |
| ALTER TABLE ONLY public.rating | |
| ADD CONSTRAINT rating_customer_fkey FOREIGN KEY (customer) REFERENCES public.customer(id) ON DELETE CASCADE NOT VALID; | |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment