generate_computer_store.sql

-- Generated by Oracle SQL Developer Data Modeler 21.4.2.059.0838
--   at:        2022-05-23 10:43:18 CEST
--   site:      Oracle Database 12c
--   type:      Oracle Database 12c



-- predefined type, no DDL - MDSYS.SDO_GEOMETRY

-- predefined type, no DDL - XMLTYPE

CREATE TABLE addresses (
    address_id   INTEGER GENERATED ALWAYS AS IDENTITY,
    street       NVARCHAR2(100),
    city         NVARCHAR2(50),
    postal_code  CHAR(6 CHAR),
    phone_number VARCHAR2(11 CHAR)
);
ALTER TABLE addresses ADD CONSTRAINT adresses_pk PRIMARY KEY ( address_id );


CREATE TABLE cost_invoices (
    cost_invoice_id   INTEGER GENERATED ALWAYS AS IDENTITY NOT NULL,
    cost_invoice_nr   NVARCHAR2(30) NOT NULL,
    supplier_id       SMALLINT NOT NULL,
    total_net_price   NUMBER(12, 2),
    total_tax         NUMBER(12, 2),
    is_paid           NUMBER,
    cost_invoice_date DATE
);
ALTER TABLE cost_invoices ADD CONSTRAINT costinvoices_pk PRIMARY KEY ( cost_invoice_id );
ALTER TABLE cost_invoices ADD CONSTRAINT costinvoices_un UNIQUE ( cost_invoice_nr );


CREATE TABLE delivery_methods (
    delivery_method_id   SMALLINT GENERATED ALWAYS AS IDENTITY NOT NULL,
    delivery_method_name VARCHAR2(50)
);
ALTER TABLE delivery_methods ADD CONSTRAINT sales_pk PRIMARY KEY ( delivery_method_id );


CREATE TABLE employee_positions (
    position_id   SMALLINT GENERATED ALWAYS AS IDENTITY NOT NULL,
    position_name NVARCHAR2(40) NOT NULL
);
ALTER TABLE employee_positions ADD CONSTRAINT employeepositions_pk PRIMARY KEY ( position_id );
ALTER TABLE employee_positions ADD CONSTRAINT employeepositions_name_un UNIQUE ( position_name );


CREATE TABLE employees (
    employee_id         INTEGER GENERATED ALWAYS AS IDENTITY,
    employee_name       NVARCHAR2(20) NOT NULL,
    employee_surname    NVARCHAR2(30) NOT NULL,
    pesel               VARCHAR2(11),
    email               VARCHAR2(50 CHAR),
    address_id          INTEGER,
    contract_id         SMALLINT
);
ALTER TABLE employees ADD CONSTRAINT employees_pk PRIMARY KEY ( employee_id );


CREATE TABLE employees_contracts (
    contract_id INTEGER GENERATED ALWAYS AS IDENTITY,
    wages       NUMBER(8, 2),
    section_id  SMALLINT,
    position_id SMALLINT,
    hire_date   DATE,
    end_date    DATE
);
ALTER TABLE employees_contracts ADD CONSTRAINT employeecontracts_pk PRIMARY KEY ( contract_id );
ALTER TABLE employees_contracts ADD CONSTRAINT employees_contracts_posid_un UNIQUE ( position_id );
ALTER TABLE employees_contracts ADD CONSTRAINT emp_contracts_dates_check CHECK(hire_date + 183 <= end_date); --contract has to be signed for a period of not less then 6 months 


CREATE TABLE income_invoices (
    income_invoice_id   INTEGER GENERATED ALWAYS AS IDENTITY NOT NULL,
    income_invoice_no   NVARCHAR2(20) NOT NULL,
    transaction_id      INTEGER,
    wholesale_client_id INTEGER NOT NULL,
    income_invoice_date TIMESTAMP,
    payment_term_id     INTEGER,
    CONSTRAINT payment_term_UN UNIQUE (payment_term_id)
);

ALTER TABLE income_invoices ADD CONSTRAINT invoices_pk PRIMARY KEY ( income_invoice_id );
ALTER TABLE income_invoices ADD CONSTRAINT incomeinvoices_nr_un UNIQUE ( income_invoice_nr );
ALTER TABLE income_invoices
    ADD CONSTRAINT invoices_transactions_fk FOREIGN KEY ( transaction_id )
        REFERENCES transactions ( transaction_id );
ALTER TABLE income_invoices
    ADD CONSTRAINT invoices_terms_FK FOREIGN KEY (payment_term_id)
    REFERENCES payment_terms (payment_term_id);
ALTER TABLE income_invoices ADD CONSTRAINT payment_term_UN UNIQUE (payment_term_id);


CREATE TABLE invoice_products_lists (
    invoice_list_id       INTEGER GENERATED ALWAYS AS IDENTITY,
    income_invoice_id     INTEGER NOT NULL,
    product_id            INTEGER NOT NULL,
    purchased_product_qty SMALLINT
);

ALTER TABLE invoice_products_lists ADD CONSTRAINT invoice_products_lists_pk PRIMARY KEY ( invoice_list_id, income_invoice_id );
ALTER TABLE invoice_products_lists ADD CONSTRAINT invoice_products_lists_un UNIQUE ( income_invoice_id );


CREATE TABLE online_storehouse (
    product_id         INTEGER NOT NULL,
    online_product_qty INTEGER DEFAULT 0 NOT NULL,
    deficit_alert      SMALLINT
);

ALTER TABLE online_storehouse ADD CONSTRAINT onlinestorehouse_pk PRIMARY KEY ( product_id );


CREATE TABLE ordered_products_lists (
    order_id             INTEGER NOT NULL,
    product_id           INTEGER NOT NULL,
    stationary_store_qty SMALLINT,
    online_store_qty     SMALLINT
);

ALTER TABLE ordered_products_lists ADD CONSTRAINT orderedproductslist_pk PRIMARY KEY ( order_id, product_id );


CREATE TABLE orders (
    order_id        INTEGER GENERATED ALWAYS AS IDENTITY,
    order_nr        NVARCHAR2(20),
    supplier_id     SMALLINT NOT NULL,
    order_send_date DATE
);

ALTER TABLE orders ADD CONSTRAINT orders_pk PRIMARY KEY ( order_id );
ALTER TABLE orders ADD CONSTRAINT orders__un UNIQUE ( order_nr );


CREATE TABLE pay_scales (
    position_id SMALLINT GENERATED ALWAYS AS IDENTITY,
    min_wages   NUMBER(8, 2) NOT NULL,
    max_wages   NUMBER(8, 2) NOT NULL
);

ALTER TABLE pay_scales ADD CONSTRAINT payscales_pk PRIMARY KEY ( position_id );


CREATE TABLE payment_methods (
    payment_method_id   SMALLINT GENERATED ALWAYS AS IDENTITY NOT NULL,
    payment_method_name NVARCHAR2(50) NOT NULL
);

ALTER TABLE payment_methods ADD CONSTRAINT paymentmethods_pk PRIMARY KEY ( payment_method_id );
ALTER TABLE payment_methods ADD CONSTRAINT paymentmethods__un UNIQUE ( payment_method_name );


CREATE TABLE product_categories (
    category_id   SMALLINT GENERATED ALWAYS AS IDENTITY NOT NULL,
    category_name NVARCHAR2(30) NOT NULL
);

ALTER TABLE product_categories ADD CONSTRAINT productcategories_pk PRIMARY KEY ( category_id );
ALTER TABLE product_categories ADD CONSTRAINT productcategories__un UNIQUE ( category_name );


CREATE TABLE PRODUCTS 
    (
    product_id    INTEGER GENERATED ALWAYS AS IDENTITY NOT NULL,
    category_id   SMALLINT,
    product_name  VARCHAR2(200) NOT NULL,
    unit_price    NUMBER(8, 2) DEFAULT ON NULL 0 
    ) 
;

ALTER TABLE products ADD CONSTRAINT products_pk PRIMARY KEY ( product_id );
ALTER TABLE products ADD CONSTRAINT products_unit_price_check CHECK (unit_price >= 0);


CREATE TABLE receipt_products_lists (
    receipt_list_id       INTEGER GENERATED ALWAYS AS IDENTITY NOT NULL,
    receipt_id            INTEGER NOT NULL,
    product_id            INTEGER NOT NULL,
    purchased_product_qty SMALLINT
);

ALTER TABLE receipt_products_lists ADD CONSTRAINT purchasedproductslist_pk PRIMARY KEY ( receipt_list_id, receipt_id );
ALTER TABLE receipt_products_lists ADD CONSTRAINT receipt_products_lists__un UNIQUE ( receipt_id );
ALTER TABLE receipt_products_lists ADD CONSTRAINT receipt_purchased_qty_check CHECK (purchased_product_qty > 0);


CREATE TABLE receipts (
    receipt_id      INTEGER GENERATED ALWAYS AS IDENTITY NOT NULL,
    receipt_no      NVARCHAR2(20) NOT NULL,
    transaction_id  INTEGER,
    receipt_date    TIMESTAMP
);

ALTER TABLE receipts ADD CONSTRAINT receipts_pk PRIMARY KEY ( receipt_id );
ALTER TABLE receipts ADD CONSTRAINT receipts__un UNIQUE ( receipt_no );
ALTER TABLE receipts
    ADD CONSTRAINT receipts_transactions_fk FOREIGN KEY ( transaction_id )
        REFERENCES TRANSACTIONS ( transaction_id );


CREATE TABLE sections (
    section_id   SMALLINT GENERATED ALWAYS AS IDENTITY NOT NULL,
    section_name VARCHAR2(100 CHAR) NOT NULL
);
ALTER TABLE sections ADD CONSTRAINT sections_pk PRIMARY KEY ( section_id );
ALTER TABLE sections ADD CONSTRAINT sections_name_un UNIQUE ( section_name );


CREATE TABLE stationary_storehouse (
    product_id       INTEGER NOT NULL,
    product_quantity INTEGER DEFAULT 1 NOT NULL,
    deficit_alert    SMALLINT
);

ALTER TABLE stationary_storehouse ADD CONSTRAINT storehouse_pk PRIMARY KEY ( product_id );


CREATE TABLE supplied_products_lists (
    supply_id         INTEGER NOT NULL,
    product_id        INTEGER NOT NULL,
    product_net_price NUMBER(10, 2),
    tax               NUMBER(3, 2),
    quantity          SMALLINT
);

ALTER TABLE supplied_products_lists ADD CONSTRAINT suppliedproductlist_pk PRIMARY KEY ( supply_id, product_id );
ALTER TABLE supplied_products_lists ADD CONSTRAINT supp_prod_lists_supplies_un UNIQUE ( supply_id );


CREATE TABLE suppliers (
    supplier_id      SMALLINT GENERATED ALWAYS AS IDENTITY NOT NULL,
    supplier_name    NVARCHAR2(100) NOT NULL,
    address_id       INTEGER,
    nip              NVARCHAR2(10) NOT NULL
);

ALTER TABLE suppliers ADD CONSTRAINT suppliers_pkv2 PRIMARY KEY ( supplier_id );


CREATE TABLE supplies (
    supply_id        INTEGER GENERATED ALWAYS AS IDENTITY NOT NULL,
    order_nr         NVARCHAR2(20) NOT NULL,
    cost_invoice_id  INTEGER NOT NULL,
    delivery_date    DATE NOT NULL
);

ALTER TABLE supplies ADD CONSTRAINT suppliers_pk PRIMARY KEY ( supply_id );


CREATE TABLE transaction_statuses (
    status_id   SMALLINT GENERATED ALWAYS AS IDENTITY NOT NULL,
    status_name NVARCHAR2(30)
);

ALTER TABLE transaction_statuses ADD CONSTRAINT transactionstatus_pk PRIMARY KEY ( status_id );
ALTER TABLE transaction_statuses ADD CONSTRAINT transactionstatus__un UNIQUE ( status_name );


CREATE TABLE transactions (
    transaction_id     INTEGER GENERATED ALWAYS AS IDENTITY NOT NULL,
    employee_id        SMALLINT,
    payment_method_id  SMALLINT DEFAULT 1 NOT NULL,
    delivery_method_id SMALLINT NOT NULL,
    status_id          SMALLINT NOT NULL,
    start_time         TIMESTAMP,
    end_time           TIMESTAMP
);

ALTER TABLE transactions ADD CONSTRAINT transactions_pk PRIMARY KEY ( transaction_id );
ALTER TABLE transactions ADD CONSTRAINT transactions__un_invoice UNIQUE ( invoice_id );
ALTER TABLE transactions ADD CONSTRAINT transactions__un_receipt UNIQUE ( receipt_id );


CREATE TABLE wholesale_clients (
    wholesale_client_id   INTEGER GENERATED ALWAYS AS IDENTITY,
    wholesale_client_name NVARCHAR2(100) NOT NULL,
    loyalty_card_id       INTEGER,
    address_id            INTEGER,
    nip                   VARCHAR2(10 CHAR),
    regon                 VARCHAR2(9 BYTE),
    email                 VARCHAR2(50 CHAR)
);

ALTER TABLE wholesale_clients ADD CONSTRAINT clients_pk PRIMARY KEY ( wholesale_client_id );
ALTER TABLE wholesale_clients ADD CONSTRAINT wholesale_clients_regon_un UNIQUE ( regon );
ALTER TABLE wholesale_clients ADD CONSTRAINT wholesale_clients_nip_un UNIQUE ( nip );


CREATE TABLE payment_terms (
    payment_term_id     INTEGER GENERATED ALWAYS AS IDENTITY, 
    payment_term_name   VARCHAR(50),
    days_to_payment     INTEGER,
    CONSTRAINT PK_payment_term PRIMARY KEY (payment_term_id),
    CONSTRAINT UN_payment_term_name UNIQUE (payment_term_name)
);


CREATE TABLE clients_loyalty_cards (
    loyalty_card_id	    INTEGER GENERATED ALWAYS AS IDENTITY,
    loyalty_card_label  VARCHAR2 (15 CHAR),
    CONSTRAINT loyalty_card_PK PRIMARY KEY (loyalty_card_id)
);

ALTER TABLE wholesale_clients 
    ADD CONSTRAINT wholesale_clients_cards_fk FOREIGN KEY (loyalty_card_id)
    REFERENCES clients_loyalty_cards (loyalty_card_id);

ALTER TABLE wholesale_clients
    ADD CONSTRAINT clients_adresses_fk FOREIGN KEY ( address_id )
        REFERENCES addresses ( address_id );

ALTER TABLE cost_invoices
    ADD CONSTRAINT costinvoices_suppliers_fk FOREIGN KEY ( supplier_id )
        REFERENCES suppliers ( supplier_id );

ALTER TABLE employees_contracts
    ADD CONSTRAINT empcontracts_emppositions_fk FOREIGN KEY ( position_id )
        REFERENCES employee_positions ( position_id );

ALTER TABLE employees_contracts
    ADD CONSTRAINT employeecontracts_sections_fk FOREIGN KEY ( section_id )
        REFERENCES sections ( section_id );

ALTER TABLE employees
    ADD CONSTRAINT employees_adresses_fk FOREIGN KEY ( address_id )
        REFERENCES addresses ( address_id );

ALTER TABLE employees
    ADD CONSTRAINT employees_employeecontracts_fk FOREIGN KEY ( contract_id )
        REFERENCES employees_contracts ( contract_id );

ALTER TABLE income_invoices
    ADD CONSTRAINT ininvoices_wholesaleclients_fk FOREIGN KEY ( wholesale_client_id )
        REFERENCES wholesale_clients ( wholesale_client_id );

ALTER TABLE invoice_products_lists
    ADD CONSTRAINT invoice_lists_income__fk FOREIGN KEY ( income_invoice_id )
        REFERENCES income_invoices ( income_invoice_id );

ALTER TABLE invoice_products_lists
    ADD CONSTRAINT invoice_products_lists__fk FOREIGN KEY ( product_id )
        REFERENCES products ( product_id );

ALTER TABLE online_storehouse
    ADD CONSTRAINT onlinestorehouse_products_fk FOREIGN KEY ( product_id )
        REFERENCES products ( product_id );

ALTER TABLE ordered_products_lists
    ADD CONSTRAINT orderedprodlist_orders_fk FOREIGN KEY ( order_id )
        REFERENCES orders ( order_id );

ALTER TABLE ordered_products_lists
    ADD CONSTRAINT orderedprodlist_products_fk FOREIGN KEY ( product_id )
        REFERENCES products ( product_id );

ALTER TABLE orders
    ADD CONSTRAINT orders_suppliers_fk FOREIGN KEY ( supplier_id )
        REFERENCES suppliers ( supplier_id );

ALTER TABLE pay_scales
    ADD CONSTRAINT payscales_employeepositions_fk FOREIGN KEY ( position_id )
        REFERENCES employee_positions ( position_id );

ALTER TABLE products
    ADD CONSTRAINT products_productcategories_fk FOREIGN KEY ( category_id )
        REFERENCES product_categories ( category_id );

ALTER TABLE receipt_products_lists
    ADD CONSTRAINT purchasedlist_products_fk FOREIGN KEY ( product_id )
        REFERENCES products ( product_id );

ALTER TABLE receipt_products_lists
    ADD CONSTRAINT receipt_prod_lists_receipts_fk FOREIGN KEY ( receipt_id )
        REFERENCES receipts ( receipt_id );

ALTER TABLE stationary_storehouse
    ADD CONSTRAINT stationstorehouse_products_fk FOREIGN KEY ( product_id )
        REFERENCES products ( product_id );

ALTER TABLE supplied_products_lists
    ADD CONSTRAINT suppliedprodlist_products_fk FOREIGN KEY ( product_id )
        REFERENCES products ( product_id );

ALTER TABLE supplied_products_lists
    ADD CONSTRAINT suppliedprodlist_supplies_fk FOREIGN KEY ( supply_id )
        REFERENCES supplies ( supply_id );

ALTER TABLE suppliers
    ADD CONSTRAINT suppliers_adresses_fk FOREIGN KEY ( address_id )
        REFERENCES addresses ( address_id );

ALTER TABLE supplies
    ADD CONSTRAINT supplies_cost_invoices_fk FOREIGN KEY ( cost_invoice_id )
        REFERENCES cost_invoices ( cost_invoice_id );

ALTER TABLE transactions
    ADD CONSTRAINT trans_delivery_meth_fk FOREIGN KEY ( delivery_method_id )
        REFERENCES delivery_methods ( delivery_method_id );

ALTER TABLE transactions
    ADD CONSTRAINT transactions_employees_fk FOREIGN KEY ( employee_id )
        REFERENCES employees ( employee_id );

ALTER TABLE transactions
    ADD CONSTRAINT transactions_paymentmethods_fk FOREIGN KEY ( payment_method_id )
        REFERENCES payment_methods ( payment_method_id );

ALTER TABLE transactions
    ADD CONSTRAINT transactions_transtatus_fk FOREIGN KEY ( status_id )
        REFERENCES transaction_statuses ( status_id );


-- Oracle SQL Developer Data Modeler Summary Report: 
-- 
-- CREATE TABLE                            25
-- CREATE INDEX                             0
-- ALTER TABLE                             69
-- CREATE VIEW                              0
-- ALTER VIEW                               0
-- CREATE PACKAGE                           0
-- CREATE PACKAGE BODY                      0
-- CREATE PROCEDURE                         0
-- CREATE FUNCTION                          0
-- CREATE TRIGGER                           1
-- ALTER TRIGGER                            0
-- CREATE COLLECTION TYPE                   0
-- CREATE STRUCTURED TYPE                   0
-- CREATE STRUCTURED TYPE BODY              0
-- CREATE CLUSTER                           0
-- CREATE CONTEXT                           0
-- CREATE DATABASE                          0
-- CREATE DIMENSION                         0
-- CREATE DIRECTORY                         0
-- CREATE DISK GROUP                        0
-- CREATE ROLE                              0
-- CREATE ROLLBACK SEGMENT                  0
-- CREATE SEQUENCE                          1
-- CREATE MATERIALIZED VIEW                 0
-- CREATE MATERIALIZED VIEW LOG             0
-- CREATE SYNONYM                           0
-- CREATE TABLESPACE                        0
-- CREATE USER                              0
-- 
-- DROP TABLESPACE                          0
-- DROP DATABASE                            0
-- 
-- REDACTION POLICY                         0
-- TSDP POLICY                              0
-- 
-- ORDS DROP SCHEMA                         0
-- ORDS ENABLE SCHEMA                       0
-- ORDS ENABLE OBJECT                       0
-- 
-- ERRORS                                   4
-- WARNINGS                                 0