--
-- PostgreSQL database dump
--

--
-- TOC entry 3 (OID 2200)
-- Name: public; Type: ACL; Schema: -
--

REVOKE ALL ON SCHEMA public FROM PUBLIC;
GRANT ALL ON SCHEMA public TO PUBLIC;


SET search_path = public, pg_catalog;

--
-- TOC entry 4 (OID 17146)
-- Name: salesrep; Type: TABLE; Schema: public
--

CREATE TABLE salesrep (
    id serial NOT NULL,
    name character varying(30) NOT NULL,
    year_salary double precision NOT NULL,
    date_empl date NOT NULL
);


--
-- TOC entry 5 (OID 17151)
-- Name: customers; Type: TABLE; Schema: public
--

CREATE TABLE customers (
    id serial NOT NULL,
    name character varying(35) NOT NULL,
    default_served_by integer,
    country character varying(20),
    city character varying(30)
);


--
-- TOC entry 6 (OID 17156)
-- Name: warehouses; Type: TABLE; Schema: public
--

CREATE TABLE warehouses (
    id serial NOT NULL,
    name character varying(30) NOT NULL,
    country character varying(20) NOT NULL,
    city character varying(30) NOT NULL
);


--
-- TOC entry 7 (OID 17159)
-- Name: products; Type: TABLE; Schema: public
--

CREATE TABLE products (
    ref character varying(15) NOT NULL,
    name character varying(20) NOT NULL,
    price double precision,
    wh_stored integer
);


--
-- TOC entry 8 (OID 17161)
-- Name: locations; Type: TABLE; Schema: public
--

CREATE TABLE locations (
    country character varying(20) NOT NULL,
    city character varying(30) NOT NULL,
    shortcut character varying(50) NOT NULL
);


--
-- TOC entry 9 (OID 17165)
-- Name: orders; Type: TABLE; Schema: public
--

CREATE TABLE orders (
    id serial NOT NULL,
    customer integer NOT NULL,
    creation_date date DEFAULT now() NOT NULL,
    delivery_before date,
    delivery_date date
);


--
-- TOC entry 10 (OID 17169)
-- Name: order_contents; Type: TABLE; Schema: public
--

CREATE TABLE order_contents (
    order_id integer NOT NULL,
    product_ref character varying(15) NOT NULL,
    quantity integer DEFAULT 1 NOT NULL,
    discount double precision DEFAULT 0 NOT NULL
);


--
-- TOC entry 11 (OID 1060475)
-- Name: Zbug; Type: TABLE; Schema: public
--

CREATE TABLE "Zbug" (
    id integer
);


--
-- TOC entry 12 (OID 1060728)
-- Name: test_last_id; Type: TABLE; Schema: public
--

CREATE TABLE test_last_id (
    id serial NOT NULL,
    name character varying(20)
);


--
-- TOC entry 13 (OID 1077129)
-- Name: roles; Type: TABLE; Schema: public
--

CREATE TABLE roles (
    id serial NOT NULL,
    role character varying(30) NOT NULL
);


--
-- TOC entry 14 (OID 1077134)
-- Name: sales_orga; Type: TABLE; Schema: public
--

CREATE TABLE sales_orga (
    id_salesrep integer NOT NULL,
    id_role integer NOT NULL,
    note text
);


--
-- Data for TOC entry 28 (OID 17146)
-- Name: salesrep; Type: TABLE DATA; Schema: public
--

INSERT INTO salesrep VALUES (1, 'Chris Johnson', 20, '2002-10-03');
INSERT INTO salesrep VALUES (2, 'Marc Swayn', 25, '2003-05-08');
INSERT INTO salesrep VALUES (3, 'John Tremor', 23, '2003-09-18');
INSERT INTO salesrep VALUES (4, 'Tom Gregor', 28, '2001-12-12');


--
-- Data for TOC entry 29 (OID 17151)
-- Name: customers; Type: TABLE DATA; Schema: public
--

INSERT INTO customers VALUES (10, 'Vladimir Zirkov', 4, NULL, NULL);
INSERT INTO customers VALUES (4, 'Mark Lawrencep', NULL, 'SP', 'MDR');
INSERT INTO customers VALUES (2, 'Ed Lamton', 4, 'SP', 'MDR');
INSERT INTO customers VALUES (3, 'Lew Bonito', 1, 'FR', 'TLS');
INSERT INTO customers VALUES (9, 'Greg Popoff', 2, 'SP', 'MDR');


--
-- Data for TOC entry 30 (OID 17156)
-- Name: warehouses; Type: TABLE DATA; Schema: public
--



--
-- Data for TOC entry 31 (OID 17159)
-- Name: products; Type: TABLE DATA; Schema: public
--

INSERT INTO products VALUES ('SC1', 'Screen 17"', 180, NULL);
INSERT INTO products VALUES ('SC2', 'Screen 19"', 220, NULL);
INSERT INTO products VALUES ('SC3', 'Flat Screen 15"', 200, NULL);
INSERT INTO products VALUES ('SC4', 'Flat Screen 19"', 700, NULL);
INSERT INTO products VALUES ('MOUSE1', 'USB Mouse', 40, NULL);
INSERT INTO products VALUES ('MOUSE2', 'Wheel Mouse', 42, NULL);


--
-- Data for TOC entry 32 (OID 17161)
-- Name: locations; Type: TABLE DATA; Schema: public
--

INSERT INTO locations VALUES ('FR', 'TLS', 'Toulouse, France');
INSERT INTO locations VALUES ('SP', 'MDR', 'Madrid, Spain');


--
-- Data for TOC entry 33 (OID 17165)
-- Name: orders; Type: TABLE DATA; Schema: public
--

INSERT INTO orders VALUES (2, 3, '2004-02-02', NULL, NULL);
INSERT INTO orders VALUES (3, 4, '2004-02-02', NULL, NULL);
INSERT INTO orders VALUES (4, 4, '2004-02-02', NULL, NULL);
INSERT INTO orders VALUES (5, 3, '2004-02-02', NULL, NULL);
INSERT INTO orders VALUES (6, 3, '2004-01-01', '2004-03-31', NULL);
INSERT INTO orders VALUES (7, 9, '2004-02-02', NULL, NULL);
INSERT INTO orders VALUES (1, 3, '2003-06-28', NULL, '2004-02-01');


--
-- Data for TOC entry 34 (OID 17169)
-- Name: order_contents; Type: TABLE DATA; Schema: public
--

INSERT INTO order_contents VALUES (5, 'SC2', 1, 0);
INSERT INTO order_contents VALUES (5, 'SC1', 2, 0);
INSERT INTO order_contents VALUES (6, 'SC2', 3, 10);
INSERT INTO order_contents VALUES (6, 'SC1', 2, 5);
INSERT INTO order_contents VALUES (7, 'MOUSE2', 6, 0);
INSERT INTO order_contents VALUES (3, 'SC3', 1, 0);
INSERT INTO order_contents VALUES (3, 'MOUSE1', 1, 2);
INSERT INTO order_contents VALUES (1, 'SC1', 1, 2);
INSERT INTO order_contents VALUES (2, 'MOUSE1', 1, 0);
INSERT INTO order_contents VALUES (2, 'SC4', 1, 0);
INSERT INTO order_contents VALUES (6, 'SC4', 1, 0);


--
-- Data for TOC entry 35 (OID 1060475)
-- Name: Zbug; Type: TABLE DATA; Schema: public
--



--
-- Data for TOC entry 36 (OID 1060728)
-- Name: test_last_id; Type: TABLE DATA; Schema: public
--

INSERT INTO test_last_id VALUES (1, 'Romeo');


--
-- Data for TOC entry 37 (OID 1077129)
-- Name: roles; Type: TABLE DATA; Schema: public
--

INSERT INTO roles VALUES (1, 'Manager');
INSERT INTO roles VALUES (2, 'Superviser');
INSERT INTO roles VALUES (3, 'The boss');


--
-- Data for TOC entry 38 (OID 1077134)
-- Name: sales_orga; Type: TABLE DATA; Schema: public
--

INSERT INTO sales_orga VALUES (4, 3, NULL);
INSERT INTO sales_orga VALUES (1, 1, NULL);
INSERT INTO sales_orga VALUES (1, 2, NULL);


--
-- TOC entry 21 (OID 17183)
-- Name: salesrep_pkey; Type: CONSTRAINT; Schema: public
--

ALTER TABLE ONLY salesrep
    ADD CONSTRAINT salesrep_pkey PRIMARY KEY (id);


--
-- TOC entry 22 (OID 17185)
-- Name: customers_pkey; Type: CONSTRAINT; Schema: public
--

ALTER TABLE ONLY customers
    ADD CONSTRAINT customers_pkey PRIMARY KEY (id);


--
-- TOC entry 23 (OID 17191)
-- Name: warehouses_pkey; Type: CONSTRAINT; Schema: public
--

ALTER TABLE ONLY warehouses
    ADD CONSTRAINT warehouses_pkey PRIMARY KEY (id);


--
-- TOC entry 24 (OID 17193)
-- Name: products_pkey; Type: CONSTRAINT; Schema: public
--

ALTER TABLE ONLY products
    ADD CONSTRAINT products_pkey PRIMARY KEY (ref);


--
-- TOC entry 25 (OID 17199)
-- Name: locations_pkey; Type: CONSTRAINT; Schema: public
--

ALTER TABLE ONLY locations
    ADD CONSTRAINT locations_pkey PRIMARY KEY (country, city);


--
-- TOC entry 26 (OID 17209)
-- Name: orders_pkey; Type: CONSTRAINT; Schema: public
--

ALTER TABLE ONLY orders
    ADD CONSTRAINT orders_pkey PRIMARY KEY (id);


--
-- TOC entry 27 (OID 1077132)
-- Name: roles_pkey; Type: CONSTRAINT; Schema: public
--

ALTER TABLE ONLY roles
    ADD CONSTRAINT roles_pkey PRIMARY KEY (id);


--
-- TOC entry 39 (OID 17187)
-- Name: $1; Type: FK CONSTRAINT; Schema: public
--

ALTER TABLE ONLY customers
    ADD CONSTRAINT "$1" FOREIGN KEY (default_served_by) REFERENCES salesrep(id) ON UPDATE CASCADE ON DELETE CASCADE;


--
-- TOC entry 42 (OID 17195)
-- Name: $1; Type: FK CONSTRAINT; Schema: public
--

ALTER TABLE ONLY products
    ADD CONSTRAINT "$1" FOREIGN KEY (wh_stored) REFERENCES warehouses(id) ON UPDATE CASCADE;


--
-- TOC entry 40 (OID 17201)
-- Name: customers_locations_fk; Type: FK CONSTRAINT; Schema: public
--

ALTER TABLE ONLY customers
    ADD CONSTRAINT customers_locations_fk FOREIGN KEY (country, city) REFERENCES locations(country, city);


--
-- TOC entry 41 (OID 17205)
-- Name: warehouses_locations_fk; Type: FK CONSTRAINT; Schema: public
--

ALTER TABLE ONLY warehouses
    ADD CONSTRAINT warehouses_locations_fk FOREIGN KEY (country, city) REFERENCES locations(country, city);


--
-- TOC entry 43 (OID 17211)
-- Name: $1; Type: FK CONSTRAINT; Schema: public
--

ALTER TABLE ONLY orders
    ADD CONSTRAINT "$1" FOREIGN KEY (customer) REFERENCES customers(id) ON UPDATE CASCADE;


--
-- TOC entry 44 (OID 17215)
-- Name: $1; Type: FK CONSTRAINT; Schema: public
--

ALTER TABLE ONLY order_contents
    ADD CONSTRAINT "$1" FOREIGN KEY (order_id) REFERENCES orders(id) ON UPDATE CASCADE ON DELETE CASCADE;


--
-- TOC entry 45 (OID 17219)
-- Name: $2; Type: FK CONSTRAINT; Schema: public
--

ALTER TABLE ONLY order_contents
    ADD CONSTRAINT "$2" FOREIGN KEY (product_ref) REFERENCES products(ref) ON UPDATE CASCADE;


--
-- TOC entry 46 (OID 1077139)
-- Name: $1; Type: FK CONSTRAINT; Schema: public
--

ALTER TABLE ONLY sales_orga
    ADD CONSTRAINT "$1" FOREIGN KEY (id_salesrep) REFERENCES salesrep(id);


--
-- TOC entry 47 (OID 1077143)
-- Name: $2; Type: FK CONSTRAINT; Schema: public
--

ALTER TABLE ONLY sales_orga
    ADD CONSTRAINT "$2" FOREIGN KEY (id_role) REFERENCES roles(id);


--
-- TOC entry 15 (OID 17144)
-- Name: salesrep_id_seq; Type: SEQUENCE SET; Schema: public
--

SELECT pg_catalog.setval('salesrep_id_seq', 4, true);


--
-- TOC entry 16 (OID 17149)
-- Name: customers_id_seq; Type: SEQUENCE SET; Schema: public
--

SELECT pg_catalog.setval('customers_id_seq', 21, true);


--
-- TOC entry 17 (OID 17154)
-- Name: warehouses_id_seq; Type: SEQUENCE SET; Schema: public
--

SELECT pg_catalog.setval('warehouses_id_seq', 1, false);


--
-- TOC entry 18 (OID 17163)
-- Name: orders_id_seq; Type: SEQUENCE SET; Schema: public
--

SELECT pg_catalog.setval('orders_id_seq', 7, true);


--
-- TOC entry 19 (OID 1060726)
-- Name: test_last_id_id_seq; Type: SEQUENCE SET; Schema: public
--

SELECT pg_catalog.setval('test_last_id_id_seq', 1, true);


--
-- TOC entry 20 (OID 1077127)
-- Name: roles_id_seq; Type: SEQUENCE SET; Schema: public
--

SELECT pg_catalog.setval('roles_id_seq', 3, true);


--
-- TOC entry 2 (OID 2200)
-- Name: SCHEMA public; Type: COMMENT; Schema: -
--

COMMENT ON SCHEMA public IS 'Standard public schema';