-- -- PostgreSQL database dump -- -- Dumped from database version 14.7 -- Dumped by pg_dump version 15.2 -- Started on 2023-04-24 12:28:56 EDT 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; -- -- TOC entry 4 (class 2615 OID 2200) -- Name: public; Type: SCHEMA; Schema: -; Owner: - -- -- *not* creating schema, since initdb creates it -- -- TOC entry 834 (class 1247 OID 16396) -- Name: customer_type; Type: TYPE; Schema: public; Owner: - -- CREATE TYPE public.customer_type AS ENUM ( 'gold', 'silver', 'bronze' ); -- -- TOC entry 849 (class 1247 OID 16433) -- Name: inventory_item; Type: TYPE; Schema: public; Owner: - -- CREATE TYPE public.inventory_item AS ( name text, supplier_id integer, price numeric, in_stock boolean ); -- -- TOC entry 840 (class 1247 OID 16415) -- Name: order_type; Type: TYPE; Schema: public; Owner: - -- CREATE TYPE public.order_type AS ENUM ( 'online', 'offline' ); -- -- TOC entry 846 (class 1247 OID 16430) -- Name: product_position; Type: TYPE; Schema: public; Owner: - -- CREATE TYPE public.product_position AS ( x_coord numeric(10,2), y_coord numeric(10,2), z_coord numeric(10,2) ); SET default_table_access_method = heap; -- -- TOC entry 219 (class 1259 OID 16459) -- Name: categories; Type: TABLE; Schema: public; Owner: - -- CREATE TABLE public.categories ( category_id integer NOT NULL, category_name character varying(100) ); -- -- TOC entry 3641 (class 0 OID 0) -- Dependencies: 219 -- Name: TABLE categories; Type: COMMENT; Schema: public; Owner: - -- COMMENT ON TABLE public.categories IS 'Table storing category information'; -- -- TOC entry 3642 (class 0 OID 0) -- Dependencies: 219 -- Name: COLUMN categories.category_id; Type: COMMENT; Schema: public; Owner: - -- COMMENT ON COLUMN public.categories.category_id IS 'Unique identifier for a category'; -- -- TOC entry 3643 (class 0 OID 0) -- Dependencies: 219 -- Name: COLUMN categories.category_name; Type: COMMENT; Schema: public; Owner: - -- COMMENT ON COLUMN public.categories.category_name IS 'Name of the category'; -- -- TOC entry 218 (class 1259 OID 16458) -- Name: categories_category_id_seq; Type: SEQUENCE; Schema: public; Owner: - -- CREATE SEQUENCE public.categories_category_id_seq AS integer START WITH 1 INCREMENT BY 1 NO MINVALUE NO MAXVALUE CACHE 1; -- -- TOC entry 3644 (class 0 OID 0) -- Dependencies: 218 -- Name: categories_category_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: - -- ALTER SEQUENCE public.categories_category_id_seq OWNED BY public.categories.category_id; -- -- TOC entry 210 (class 1259 OID 16404) -- Name: customers; Type: TABLE; Schema: public; Owner: - -- CREATE TABLE public.customers ( customer_id integer NOT NULL, first_name character varying(50), last_name character varying(50), email character varying(100), phone_number character varying(20), address character varying(200), birth_date date, total_amount numeric(10,0), is_active character(1), type public.customer_type, created_at timestamp without time zone DEFAULT CURRENT_TIMESTAMP, updated_at timestamp without time zone, CONSTRAINT active_check CHECK ((is_active = ANY (ARRAY['Y'::bpchar, 'N'::bpchar]))) ); -- -- TOC entry 3645 (class 0 OID 0) -- Dependencies: 210 -- Name: TABLE customers; Type: COMMENT; Schema: public; Owner: - -- COMMENT ON TABLE public.customers IS 'Table storing customer information'; -- -- TOC entry 3646 (class 0 OID 0) -- Dependencies: 210 -- Name: COLUMN customers.customer_id; Type: COMMENT; Schema: public; Owner: - -- COMMENT ON COLUMN public.customers.customer_id IS 'Unique identifier for a customer'; -- -- TOC entry 3647 (class 0 OID 0) -- Dependencies: 210 -- Name: COLUMN customers.first_name; Type: COMMENT; Schema: public; Owner: - -- COMMENT ON COLUMN public.customers.first_name IS 'First name of the customer'; -- -- TOC entry 3648 (class 0 OID 0) -- Dependencies: 210 -- Name: COLUMN customers.last_name; Type: COMMENT; Schema: public; Owner: - -- COMMENT ON COLUMN public.customers.last_name IS 'Last name of the customer'; -- -- TOC entry 3649 (class 0 OID 0) -- Dependencies: 210 -- Name: COLUMN customers.email; Type: COMMENT; Schema: public; Owner: - -- COMMENT ON COLUMN public.customers.email IS 'Email address of the customer'; -- -- TOC entry 3650 (class 0 OID 0) -- Dependencies: 210 -- Name: COLUMN customers.phone_number; Type: COMMENT; Schema: public; Owner: - -- COMMENT ON COLUMN public.customers.phone_number IS 'Phone number of the customer'; -- -- TOC entry 3651 (class 0 OID 0) -- Dependencies: 210 -- Name: COLUMN customers.address; Type: COMMENT; Schema: public; Owner: - -- COMMENT ON COLUMN public.customers.address IS 'Address of the customer'; -- -- TOC entry 3652 (class 0 OID 0) -- Dependencies: 210 -- Name: COLUMN customers.birth_date; Type: COMMENT; Schema: public; Owner: - -- COMMENT ON COLUMN public.customers.birth_date IS 'Date of birth of the customer'; -- -- TOC entry 3653 (class 0 OID 0) -- Dependencies: 210 -- Name: COLUMN customers.total_amount; Type: COMMENT; Schema: public; Owner: - -- COMMENT ON COLUMN public.customers.total_amount IS 'Total amount of purchases made by the customer'; -- -- TOC entry 3654 (class 0 OID 0) -- Dependencies: 210 -- Name: COLUMN customers.is_active; Type: COMMENT; Schema: public; Owner: - -- COMMENT ON COLUMN public.customers.is_active IS 'Indicates whether the customer is active or not'; -- -- TOC entry 3655 (class 0 OID 0) -- Dependencies: 210 -- Name: COLUMN customers.created_at; Type: COMMENT; Schema: public; Owner: - -- COMMENT ON COLUMN public.customers.created_at IS 'Date and time when the record was created'; -- -- TOC entry 3656 (class 0 OID 0) -- Dependencies: 210 -- Name: COLUMN customers.updated_at; Type: COMMENT; Schema: public; Owner: - -- COMMENT ON COLUMN public.customers.updated_at IS 'Date and time when the record was last updated'; -- -- TOC entry 209 (class 1259 OID 16403) -- Name: customers_customer_id_seq; Type: SEQUENCE; Schema: public; Owner: - -- CREATE SEQUENCE public.customers_customer_id_seq AS integer START WITH 1 INCREMENT BY 1 NO MINVALUE NO MAXVALUE CACHE 1; -- -- TOC entry 3657 (class 0 OID 0) -- Dependencies: 209 -- Name: customers_customer_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: - -- ALTER SEQUENCE public.customers_customer_id_seq OWNED BY public.customers.customer_id; -- -- TOC entry 217 (class 1259 OID 16443) -- Name: order_items; Type: TABLE; Schema: public; Owner: - -- CREATE TABLE public.order_items ( order_identifier integer NOT NULL, product_id integer NOT NULL, quantity integer ); -- -- TOC entry 3658 (class 0 OID 0) -- Dependencies: 217 -- Name: TABLE order_items; Type: COMMENT; Schema: public; Owner: - -- COMMENT ON TABLE public.order_items IS 'Table storing order item information'; -- -- TOC entry 3659 (class 0 OID 0) -- Dependencies: 217 -- Name: COLUMN order_items.order_identifier; Type: COMMENT; Schema: public; Owner: - -- COMMENT ON COLUMN public.order_items.order_identifier IS 'Identifier of the order'; -- -- TOC entry 3660 (class 0 OID 0) -- Dependencies: 217 -- Name: COLUMN order_items.product_id; Type: COMMENT; Schema: public; Owner: - -- COMMENT ON COLUMN public.order_items.product_id IS 'Identifier of the product in the order'; -- -- TOC entry 3661 (class 0 OID 0) -- Dependencies: 217 -- Name: COLUMN order_items.quantity; Type: COMMENT; Schema: public; Owner: - -- COMMENT ON COLUMN public.order_items.quantity IS 'Quantity of the product in the order'; -- -- TOC entry 212 (class 1259 OID 16420) -- Name: orders; Type: TABLE; Schema: public; Owner: - -- CREATE TABLE public.orders ( order_id integer NOT NULL, customer_id integer, order_date timestamp without time zone DEFAULT CURRENT_TIMESTAMP, total_amount numeric(10,2), type public.order_type, CONSTRAINT order_amount_check CHECK ((total_amount > (0)::numeric)) ); -- -- TOC entry 3662 (class 0 OID 0) -- Dependencies: 212 -- Name: TABLE orders; Type: COMMENT; Schema: public; Owner: - -- COMMENT ON TABLE public.orders IS 'Table storing order information'; -- -- TOC entry 3663 (class 0 OID 0) -- Dependencies: 212 -- Name: COLUMN orders.order_id; Type: COMMENT; Schema: public; Owner: - -- COMMENT ON COLUMN public.orders.order_id IS 'Unique identifier for an order'; -- -- TOC entry 3664 (class 0 OID 0) -- Dependencies: 212 -- Name: COLUMN orders.customer_id; Type: COMMENT; Schema: public; Owner: - -- COMMENT ON COLUMN public.orders.customer_id IS 'Identifier of the customer who placed the order'; -- -- TOC entry 3665 (class 0 OID 0) -- Dependencies: 212 -- Name: COLUMN orders.order_date; Type: COMMENT; Schema: public; Owner: - -- COMMENT ON COLUMN public.orders.order_date IS 'Date and time when the order was placed'; -- -- TOC entry 3666 (class 0 OID 0) -- Dependencies: 212 -- Name: COLUMN orders.total_amount; Type: COMMENT; Schema: public; Owner: - -- COMMENT ON COLUMN public.orders.total_amount IS 'Total amount of the order'; -- -- TOC entry 211 (class 1259 OID 16419) -- Name: orders_order_id_seq; Type: SEQUENCE; Schema: public; Owner: - -- CREATE SEQUENCE public.orders_order_id_seq AS integer START WITH 1 INCREMENT BY 1 NO MINVALUE NO MAXVALUE CACHE 1; -- -- TOC entry 3667 (class 0 OID 0) -- Dependencies: 211 -- Name: orders_order_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: - -- ALTER SEQUENCE public.orders_order_id_seq OWNED BY public.orders.order_id; -- -- TOC entry 221 (class 1259 OID 16468) -- Name: product_categories; Type: TABLE; Schema: public; Owner: - -- CREATE TABLE public.product_categories ( product_id integer NOT NULL, category_id integer NOT NULL ); -- -- TOC entry 3668 (class 0 OID 0) -- Dependencies: 221 -- Name: TABLE product_categories; Type: COMMENT; Schema: public; Owner: - -- COMMENT ON TABLE public.product_categories IS 'Table storing product category information'; -- -- TOC entry 3669 (class 0 OID 0) -- Dependencies: 221 -- Name: COLUMN product_categories.product_id; Type: COMMENT; Schema: public; Owner: - -- COMMENT ON COLUMN public.product_categories.product_id IS 'Identifier of the product'; -- -- TOC entry 3670 (class 0 OID 0) -- Dependencies: 221 -- Name: COLUMN product_categories.category_id; Type: COMMENT; Schema: public; Owner: - -- COMMENT ON COLUMN public.product_categories.category_id IS 'Identifier of the category'; -- -- TOC entry 216 (class 1259 OID 16435) -- Name: products; Type: TABLE; Schema: public; Owner: - -- CREATE TABLE public.products ( product_id integer NOT NULL, product_name character varying(100), description character varying(200), price numeric(10,2), pos public.product_position, inventory public.inventory_item ); -- -- TOC entry 3671 (class 0 OID 0) -- Dependencies: 216 -- Name: TABLE products; Type: COMMENT; Schema: public; Owner: - -- COMMENT ON TABLE public.products IS 'Table storing product information'; -- -- TOC entry 3672 (class 0 OID 0) -- Dependencies: 216 -- Name: COLUMN products.product_id; Type: COMMENT; Schema: public; Owner: - -- COMMENT ON COLUMN public.products.product_id IS 'Unique identifier for a product'; -- -- TOC entry 3673 (class 0 OID 0) -- Dependencies: 216 -- Name: COLUMN products.product_name; Type: COMMENT; Schema: public; Owner: - -- COMMENT ON COLUMN public.products.product_name IS 'Name of the product'; -- -- TOC entry 3674 (class 0 OID 0) -- Dependencies: 216 -- Name: COLUMN products.description; Type: COMMENT; Schema: public; Owner: - -- COMMENT ON COLUMN public.products.description IS 'Description of the product'; -- -- TOC entry 3675 (class 0 OID 0) -- Dependencies: 216 -- Name: COLUMN products.price; Type: COMMENT; Schema: public; Owner: - -- COMMENT ON COLUMN public.products.price IS 'Price of the product'; -- -- TOC entry 215 (class 1259 OID 16434) -- Name: products_product_id_seq; Type: SEQUENCE; Schema: public; Owner: - -- CREATE SEQUENCE public.products_product_id_seq AS integer START WITH 1 INCREMENT BY 1 NO MINVALUE NO MAXVALUE CACHE 1; -- -- TOC entry 3676 (class 0 OID 0) -- Dependencies: 215 -- Name: products_product_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: - -- ALTER SEQUENCE public.products_product_id_seq OWNED BY public.products.product_id; -- -- TOC entry 220 (class 1259 OID 16465) -- Name: super_categories; Type: TABLE; Schema: public; Owner: - -- CREATE TABLE public.super_categories ( category_id integer, category_name character varying(100) ); -- -- TOC entry 3476 (class 2604 OID 16462) -- Name: categories category_id; Type: DEFAULT; Schema: public; Owner: - -- ALTER TABLE ONLY public.categories ALTER COLUMN category_id SET DEFAULT nextval('public.categories_category_id_seq'::regclass); -- -- TOC entry 3471 (class 2604 OID 16407) -- Name: customers customer_id; Type: DEFAULT; Schema: public; Owner: - -- ALTER TABLE ONLY public.customers ALTER COLUMN customer_id SET DEFAULT nextval('public.customers_customer_id_seq'::regclass); -- -- TOC entry 3473 (class 2604 OID 16423) -- Name: orders order_id; Type: DEFAULT; Schema: public; Owner: - -- ALTER TABLE ONLY public.orders ALTER COLUMN order_id SET DEFAULT nextval('public.orders_order_id_seq'::regclass); -- -- TOC entry 3475 (class 2604 OID 16438) -- Name: products product_id; Type: DEFAULT; Schema: public; Owner: - -- ALTER TABLE ONLY public.products ALTER COLUMN product_id SET DEFAULT nextval('public.products_product_id_seq'::regclass); -- -- TOC entry 3490 (class 2606 OID 16464) -- Name: categories categories_pkey; Type: CONSTRAINT; Schema: public; Owner: - -- ALTER TABLE ONLY public.categories ADD CONSTRAINT categories_pkey PRIMARY KEY (category_id); -- -- TOC entry 3480 (class 2606 OID 16413) -- Name: customers customers_email_key; Type: CONSTRAINT; Schema: public; Owner: - -- ALTER TABLE ONLY public.customers ADD CONSTRAINT customers_email_key UNIQUE (email); -- -- TOC entry 3482 (class 2606 OID 16411) -- Name: customers customers_pkey; Type: CONSTRAINT; Schema: public; Owner: - -- ALTER TABLE ONLY public.customers ADD CONSTRAINT customers_pkey PRIMARY KEY (customer_id); -- -- TOC entry 3484 (class 2606 OID 16427) -- Name: orders orders_pkey; Type: CONSTRAINT; Schema: public; Owner: - -- ALTER TABLE ONLY public.orders ADD CONSTRAINT orders_pkey PRIMARY KEY (order_id); -- -- TOC entry 3488 (class 2606 OID 16447) -- Name: order_items pk_order_items; Type: CONSTRAINT; Schema: public; Owner: - -- ALTER TABLE ONLY public.order_items ADD CONSTRAINT pk_order_items PRIMARY KEY (order_identifier, product_id); -- -- TOC entry 3492 (class 2606 OID 16472) -- Name: product_categories pk_product_categories; Type: CONSTRAINT; Schema: public; Owner: - -- ALTER TABLE ONLY public.product_categories ADD CONSTRAINT pk_product_categories PRIMARY KEY (product_id, category_id); -- -- TOC entry 3486 (class 2606 OID 16442) -- Name: products products_pkey; Type: CONSTRAINT; Schema: public; Owner: - -- ALTER TABLE ONLY public.products ADD CONSTRAINT products_pkey PRIMARY KEY (product_id); -- -- TOC entry 3493 (class 2606 OID 16448) -- Name: order_items fk_order; Type: FK CONSTRAINT; Schema: public; Owner: - -- ALTER TABLE ONLY public.order_items ADD CONSTRAINT fk_order FOREIGN KEY (order_identifier) REFERENCES public.orders(order_id); -- -- TOC entry 3494 (class 2606 OID 16453) -- Name: order_items fk_product; Type: FK CONSTRAINT; Schema: public; Owner: - -- ALTER TABLE ONLY public.order_items ADD CONSTRAINT fk_product FOREIGN KEY (product_id) REFERENCES public.products(product_id); -- -- TOC entry 3495 (class 2606 OID 16478) -- Name: product_categories fk_product_category_category; Type: FK CONSTRAINT; Schema: public; Owner: - -- ALTER TABLE ONLY public.product_categories ADD CONSTRAINT fk_product_category_category FOREIGN KEY (category_id) REFERENCES public.categories(category_id); -- -- TOC entry 3496 (class 2606 OID 16473) -- Name: product_categories fk_product_category_product; Type: FK CONSTRAINT; Schema: public; Owner: - -- ALTER TABLE ONLY public.product_categories ADD CONSTRAINT fk_product_category_product FOREIGN KEY (product_id) REFERENCES public.products(product_id); -- Completed on 2023-04-24 12:28:56 EDT -- -- PostgreSQL database dump complete --