Логички и физички дизајн: kreiranje.sql

-- DROP SCHEMA project;

CREATE SCHEMA project AUTHORIZATION db_202122z_va_prj_pcpicco_owner;

CREATE TABLE project."Configuration" (
        config_id int4 NOT NULL,
        "name" varchar(64) NOT NULL,
        price int4 NOT NULL,
        "type" varchar(64) NOT NULL,
        CONSTRAINT configuration_pk PRIMARY KEY (config_id)
);

CREATE TABLE project."User" (
        user_id int4 NOT NULL,
        "Password" varchar(64) NOT NULL,
        CONSTRAINT user_pk PRIMARY KEY (user_id)
);

CREATE TABLE project.store (
        store_id varchar(64) NOT NULL,
        locations varchar(64) NOT NULL,
        contact varchar(64) NOT NULL,
        store_name varchar NULL,
        CONSTRAINT store_pk PRIMARY KEY (store_id)
);


CREATE TABLE project."admin" (
        admin_id int4 NOT NULL,
        CONSTRAINT admin_pk PRIMARY KEY (admin_id),
        CONSTRAINT admin_fk FOREIGN KEY (admin_id) REFERENCES project."User"(user_id)
);


CREATE TABLE project.client (
        client_id int4 NOT NULL,
        address varchar(64) NOT NULL,
        "name" varchar(64) NOT NULL,
        "e-mail" varchar(64) NOT NULL,
        CONSTRAINT client_pk PRIMARY KEY (client_id),
        CONSTRAINT client_fk FOREIGN KEY (client_id) REFERENCES project."User"(user_id)
);

CREATE TABLE project.distributor (
        distributor_id int4 NOT NULL,
        "name" varchar(64) NOT NULL,
        surname varchar(64) NOT NULL,
        contact_number int4 NOT NULL,
        CONSTRAINT distributor_pk PRIMARY KEY (distributor_id),
        CONSTRAINT distributor_fk FOREIGN KEY (distributor_id) REFERENCES project."User"(user_id)
);


CREATE TABLE project.manages_configuration (
        config_id int4 NOT NULL,
        admin_id int4 NOT NULL,
        CONSTRAINT manages_configuration_pk PRIMARY KEY (config_id, admin_id),
        CONSTRAINT manages_configuration_fk FOREIGN KEY (admin_id) REFERENCES project.admin(admin_id),
        CONSTRAINT manages_configuration_fk_1 FOREIGN KEY (config_id) REFERENCES project."Configuration"(config_id)
);

CREATE TABLE project.product (
        prod_id int4 NOT NULL,
        availability bool NOT NULL,
        "name" varchar(64) NOT NULL,
        description varchar(64) NOT NULL,
        price int4 NOT NULL,
        "type" varchar(64) NOT NULL,
        quantity int4 NOT NULL,
        store_name varchar(64) NOT NULL,
        CONSTRAINT product_pk PRIMARY KEY (prod_id),
        CONSTRAINT product_fk FOREIGN KEY (store_name) REFERENCES project.store(store_id)
);

CREATE TABLE project.seller_admin (
        admin_id int4 NOT NULL,
        seller_id int4 NOT NULL,
        store_name varchar(64) NOT NULL,
        "name" varchar(64) NOT NULL,
        surname varchar(64) NOT NULL,
        "e-mail" varchar(64) NOT NULL,
        CONSTRAINT seller_admin_pk PRIMARY KEY (seller_id),
        CONSTRAINT seller_admin_fk FOREIGN KEY (admin_id) REFERENCES project.admin(admin_id),
        CONSTRAINT seller_admin_fk_1 FOREIGN KEY (seller_id) REFERENCES project."User"(user_id),
        CONSTRAINT seller_admin_fk_2 FOREIGN KEY (store_name) REFERENCES project.store(store_id)
);


CREATE TABLE project.updates (
        seller_id int4 NOT NULL,
        product_id int4 NOT NULL,
        time_stamp date NOT NULL,
        CONSTRAINT updates_pk PRIMARY KEY (seller_id, product_id),
        CONSTRAINT updates_fk FOREIGN KEY (seller_id) REFERENCES project.seller_admin(seller_id),
        CONSTRAINT updates_fk_1 FOREIGN KEY (product_id) REFERENCES project.product(prod_id)
);

CREATE TABLE project.view_product (
        client_id int4 NOT NULL,
        prod_id int4 NOT NULL,
        time_stamp date NOT NULL,
        CONSTRAINT view_product_pk PRIMARY KEY (prod_id, client_id),
        CONSTRAINT view_product_fk FOREIGN KEY (client_id) REFERENCES project.client(client_id),
        CONSTRAINT view_product_fk_1 FOREIGN KEY (prod_id) REFERENCES project.product(prod_id)
);


CREATE TABLE project.wishlist (
        wishlist_id int4 NOT NULL,
        client_id int4 NOT NULL,
        CONSTRAINT wishlist_pk PRIMARY KEY (wishlist_id, client_id),
        CONSTRAINT wishlist_fk FOREIGN KEY (client_id) REFERENCES project.client(client_id)
);

CREATE TABLE project.cart (
        cart_id int4 NOT NULL,
        client_id int4 NOT NULL,
        CONSTRAINT cart_pk PRIMARY KEY (cart_id),
        CONSTRAINT cart_fk FOREIGN KEY (client_id) REFERENCES project.client(client_id)
);

CREATE TABLE project.config_contains_product (
        config_id int4 NOT NULL,
        prod_id int4 NOT NULL,
        num_products_config int4 NOT NULL,
        CONSTRAINT config_contains_product_pk PRIMARY KEY (config_id, prod_id),
        CONSTRAINT config_contains_product_fk FOREIGN KEY (config_id) REFERENCES project."Configuration"(config_id),
        CONSTRAINT config_contains_product_fk_1 FOREIGN KEY (prod_id) REFERENCES project.product(prod_id)
);

CREATE TABLE project.config_to_wishlist (
        config_id int4 NOT NULL,
        wishlist_id int4 NOT NULL,
        CONSTRAINT config_to_wishlist_pk PRIMARY KEY (config_id, wishlist_id),
        CONSTRAINT config_to_wishlist_fk FOREIGN KEY (wishlist_id, wishlist_id) REFERENCES project.wishlist(wishlist_id, client_id),
        CONSTRAINT config_to_wishlist_fk_1 FOREIGN KEY (config_id) REFERENCES project."Configuration"(config_id)
);


CREATE TABLE project.contains_config (
        config_id int4 NOT NULL,
        cart_id int4 NOT NULL,
        CONSTRAINT contains_config_pk PRIMARY KEY (cart_id, config_id),
        CONSTRAINT contains_config_fk FOREIGN KEY (config_id) REFERENCES project."Configuration"(config_id),
        CONSTRAINT contains_config_fk_1 FOREIGN KEY (cart_id) REFERENCES project.cart(cart_id)
);


CREATE TABLE project.contains_product (
        prod_id int4 NOT NULL,
        cart_id int4 NOT NULL,
        quantity int4 NOT NULL,
        CONSTRAINT contains_product_pk PRIMARY KEY (prod_id, cart_id),
        CONSTRAINT contains_product_fk FOREIGN KEY (prod_id) REFERENCES project.product(prod_id),
        CONSTRAINT contains_product_fk_1 FOREIGN KEY (cart_id) REFERENCES project.cart(cart_id)
);


CREATE TABLE project.delivery (
        delivery_id int4 NOT NULL,
        delivery_address varchar(64) NOT NULL,
        "date" date NOT NULL,
        status varchar(64) NOT NULL,
        client_id int4 NOT NULL,
        distributor_id int4 NOT NULL,
        CONSTRAINT delivery_pk PRIMARY KEY (delivery_id),
        CONSTRAINT delivery_fk FOREIGN KEY (distributor_id) REFERENCES project.distributor(distributor_id),
        CONSTRAINT delivery_fk_1 FOREIGN KEY (client_id) REFERENCES project.client(client_id)
);


CREATE TABLE project."order" (
        order_id int4 NOT NULL,
        order_status varchar(64) NOT NULL,
        total_price int4 NOT NULL,
        client_id int4 NOT NULL,
        delivery_id int4 NULL,
        CONSTRAINT order_pk PRIMARY KEY (order_id),
        CONSTRAINT order_fk FOREIGN KEY (client_id) REFERENCES project.client(client_id),
        CONSTRAINT order_fk1 FOREIGN KEY (delivery_id) REFERENCES project.delivery(delivery_id)
);


CREATE TABLE project.orders_configuration (
        config_id int4 NOT NULL,
        order_id int4 NOT NULL,
        CONSTRAINT orders_configuration_pk PRIMARY KEY (config_id, order_id),
        CONSTRAINT orders_configuration_fk FOREIGN KEY (config_id) REFERENCES project."Configuration"(config_id),
        CONSTRAINT orders_configuration_fk_1 FOREIGN KEY (order_id) REFERENCES project."order"(order_id)
);


CREATE TABLE project.payment (
        payment_id int4 NOT NULL,
        creditcard_number int4 NOT NULL,
        order_id int4 NOT NULL,
        CONSTRAINT payment_pk PRIMARY KEY (payment_id),
        CONSTRAINT payment_fk FOREIGN KEY (order_id) REFERENCES project."order"(order_id)
);


CREATE TABLE project.price_history (
        price_history_id int4 NOT NULL,
        dates date NOT NULL,
        prod_id int4 NOT NULL,
        "Product_price" int4 NOT NULL,
        CONSTRAINT price_history_pk PRIMARY KEY (price_history_id),
        CONSTRAINT price_history_fk FOREIGN KEY (prod_id) REFERENCES project.product(prod_id)
);


CREATE TABLE project.product_to_wishlist (
        prod_id int4 NOT NULL,
        wishlist_id int4 NOT NULL,
        CONSTRAINT product_to_wishlist_pk PRIMARY KEY (prod_id, wishlist_id),
        CONSTRAINT product_to_wishlist_fk FOREIGN KEY (prod_id) REFERENCES project.product(prod_id),
        CONSTRAINT product_to_wishlist_fk_1 FOREIGN KEY (wishlist_id, wishlist_id) REFERENCES project.wishlist(wishlist_id, client_id)
);


CREATE TABLE project.checkout (
        cart_id int4 NOT NULL,
        order_id int4 NOT NULL,
        CONSTRAINT checkout_pk PRIMARY KEY (cart_id, order_id),
        CONSTRAINT checkout_fk FOREIGN KEY (order_id) REFERENCES project."order"(order_id),
        CONSTRAINT checkout_fk_1 FOREIGN KEY (cart_id) REFERENCES project.cart(cart_id)
);

CREATE TABLE project.current_price (
        price_history_id int4 NOT NULL,
        order_id int4 NOT NULL,
        num_products int4 NOT NULL,
        CONSTRAINT current_price_pk PRIMARY KEY (order_id, price_history_id),
        CONSTRAINT current_price_fk FOREIGN KEY (price_history_id) REFERENCES project.price_history(price_history_id),
        CONSTRAINT current_price_fk_1 FOREIGN KEY (order_id) REFERENCES project."order"(order_id)
);