-- Generated by Oracle SQL Developer Data Modeler 19.4.0.350.1424
--   at:        2020-01-19 20:44:49 CET
--   site:      Oracle Database 11g
--   type:      Oracle Database 11g



CREATE TABLE adress (
    id_adress      NUMBER NOT NULL,
    city           VARCHAR2(15),
    street         VARCHAR2(15),
    street_number  NUMBER,
    state          VARCHAR2(15)
);

ALTER TABLE adress ADD CONSTRAINT adress_pk PRIMARY KEY ( id_adress );

CREATE TABLE bucket (
    id_bucket     NUMBER NOT NULL,
    count_item    NUMBER,
    item_id_item  NUMBER NOT NULL
);

ALTER TABLE bucket ADD CONSTRAINT bucket_pk PRIMARY KEY ( id_bucket );

CREATE TABLE client (
    id_clien          NUMBER NOT NULL,
    name              VARCHAR2(15),
    surname           VARCHAR2(15),
    email             VARCHAR2(15),
    phone             NUMBER,
    adress_id_adress  NUMBER NOT NULL
);

ALTER TABLE client ADD CONSTRAINT client_pk PRIMARY KEY ( id_clien );

CREATE TABLE complaint (
    id_complaint      NUMBER NOT NULL,
    date_complaint    DATE NOT NULL,
    complaint_status  VARCHAR2(15)
);

ALTER TABLE complaint ADD CONSTRAINT complaint_pk PRIMARY KEY ( id_complaint );

CREATE TABLE delivery (
    id_delivery    NUMBER NOT NULL,
    name_delivery  VARCHAR2(15),
    cost_delivery  NUMBER
);

ALTER TABLE delivery ADD CONSTRAINT delivery_pk PRIMARY KEY ( id_delivery );

CREATE TABLE item (
    id_item    NUMBER NOT NULL,
    item_name  VARCHAR2(20),
    item_type  VARCHAR2(10)
);

ALTER TABLE item ADD CONSTRAINT item_pk PRIMARY KEY ( id_item );

CREATE TABLE item_spec (
    id_spec       NUMBER NOT NULL,
    color         VARCHAR2(10),
    "Size"        VARCHAR2(5),
    price         NUMBER,
    item_id_item  NUMBER NOT NULL
);

ALTER TABLE item_spec ADD CONSTRAINT item_spec_pk PRIMARY KEY ( id_spec );

CREATE TABLE order_item (
    id_order                NUMBER NOT NULL,
    date_order              DATE,
    date_send               DATE,
    pay_status              VARCHAR2(10),
    pay_method              VARCHAR2(10),
    order_status            VARCHAR2(10),
    order_cost              NUMBER,
    client_id_clien         NUMBER NOT NULL,
    bucket_id_bucket        NUMBER NOT NULL,
    delivery_id_delivery    NUMBER NOT NULL,
    complaint_id_complaint  NUMBER NOT NULL
);

CREATE UNIQUE INDEX order_item__idx ON
    order_item (
        bucket_id_bucket
    ASC );

CREATE UNIQUE INDEX order_item__idxv1 ON
    order_item (
        complaint_id_complaint
    ASC );

ALTER TABLE order_item ADD CONSTRAINT order_item_pk PRIMARY KEY ( id_order );

CREATE TABLE photo (
    id_photo      NUMBER NOT NULL,
    photo         VARCHAR2(20),
    item_id_item  NUMBER NOT NULL
);

ALTER TABLE photo ADD CONSTRAINT photo_pk PRIMARY KEY ( id_photo );

CREATE TABLE "User" (
    id_user          NUMBER NOT NULL,
    login            VARCHAR2(15),
    password         VARCHAR2(15),
    client_id_clien  NUMBER NOT NULL
);

CREATE UNIQUE INDEX user__idx ON
    "User" (
        client_id_clien
    ASC );

ALTER TABLE "User" ADD CONSTRAINT user_pk PRIMARY KEY ( id_user );

ALTER TABLE bucket
    ADD CONSTRAINT bucket_item_fk FOREIGN KEY ( item_id_item )
        REFERENCES item ( id_item );

ALTER TABLE client
    ADD CONSTRAINT client_adress_fk FOREIGN KEY ( adress_id_adress )
        REFERENCES adress ( id_adress );

ALTER TABLE item_spec
    ADD CONSTRAINT item_spec_item_fk FOREIGN KEY ( item_id_item )
        REFERENCES item ( id_item );

ALTER TABLE order_item
    ADD CONSTRAINT order_item_bucket_fk FOREIGN KEY ( bucket_id_bucket )
        REFERENCES bucket ( id_bucket );

ALTER TABLE order_item
    ADD CONSTRAINT order_item_client_fk FOREIGN KEY ( client_id_clien )
        REFERENCES client ( id_clien );

ALTER TABLE order_item
    ADD CONSTRAINT order_item_complaint_fk FOREIGN KEY ( complaint_id_complaint )
        REFERENCES complaint ( id_complaint );

ALTER TABLE order_item
    ADD CONSTRAINT order_item_delivery_fk FOREIGN KEY ( delivery_id_delivery )
        REFERENCES delivery ( id_delivery );

ALTER TABLE photo
    ADD CONSTRAINT photo_item_fk FOREIGN KEY ( item_id_item )
        REFERENCES item ( id_item );

ALTER TABLE "User"
    ADD CONSTRAINT user_client_fk FOREIGN KEY ( client_id_clien )
        REFERENCES client ( id_clien );



-- Oracle SQL Developer Data Modeler Summary Report:
--
-- CREATE TABLE                            10
-- CREATE INDEX                             3
-- ALTER TABLE                             19
-- CREATE VIEW                              0
-- ALTER VIEW                               0
-- CREATE PACKAGE                           0
-- CREATE PACKAGE BODY                      0
-- CREATE PROCEDURE                         0
-- CREATE FUNCTION                          0
-- CREATE TRIGGER                           0
-- 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                          0
-- 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
--
-- ORDS DROP SCHEMA                         0
-- ORDS ENABLE SCHEMA                       0
-- ORDS ENABLE OBJECT                       0
--
-- ERRORS                                   0
-- WARNINGS                                 0