Shop DB

https://ibb.co/Xbt0ySj

-- tables
-- Table: Client
CREATE TABLE Client (
    ID INTEGER  NOT NULL,
    Name VARCHAR2(30)  NOT NULL,
    Surname VARCHAR2(40)  NOT NULL,
    Discount INTEGER  NOT NULL,
    CONSTRAINT Client_pk PRIMARY KEY (ID)
) ;

-- Table: Order
CREATE TABLE ClientOrder (
    ID INTEGER  NOT NULL,
    CreatedDate DATE  NOT NULL,
    Status_ID INTEGER  NOT NULL,
    Client_ID INTEGER  NOT NULL,
    CONSTRAINT Order_pk PRIMARY KEY (ID)
) ;

-- Table: Order_Product
CREATE TABLE Order_Product (
    ID INTEGER  NOT NULL,
    Price NUMBER(4,2)  NOT NULL,
    Amount INTEGER  NOT NULL,
    Order_ID INTEGER  NOT NULL,
    Product_ID INTEGER  NOT NULL,
    CONSTRAINT Order_Product_pk PRIMARY KEY (ID)
) ;

-- Table: Product
CREATE TABLE Product (
    ID INTEGER  NOT NULL,
    Name VARCHAR2(30)  NOT NULL,
    CurrentAvailability INTEGER  NOT NULL,
    CONSTRAINT Product_pk PRIMARY KEY (ID)
) ;

-- Table: Status
CREATE TABLE Status (
    ID INTEGER  NOT NULL,
    Name VARCHAR2(30)  NOT NULL,
    CONSTRAINT Status_pk PRIMARY KEY (ID)
) ;

-- foreign keys
-- Reference: Order_Client (table: Order)
ALTER TABLE ClientOrder ADD CONSTRAINT Order_Client
    FOREIGN KEY (Client_ID)
    REFERENCES Client (ID);

-- Reference: Order_Product_Order (table: Order_Product)
ALTER TABLE Order_Product ADD CONSTRAINT Order_Product_Order
    FOREIGN KEY (Order_ID)
    REFERENCES ClientOrder (ID);

-- Reference: Order_Product_Product (table: Order_Product)
ALTER TABLE Order_Product ADD CONSTRAINT Order_Product_Product
    FOREIGN KEY (Product_ID)
    REFERENCES Product (ID);

-- Reference: Order_Status (table: Order)
ALTER TABLE ClientOrder ADD CONSTRAINT Order_Status
    FOREIGN KEY (Status_ID)
    REFERENCES Status (ID);


--data

--Table: Client

INSERT INTO Client (ID, Name, Surname, Discount) VALUES (1, 'John', 'Doe', 10);
INSERT INTO Client (ID, Name, Surname, Discount) VALUES (2, 'Jane', 'Smith', 5);
INSERT INTO Client (ID, Name, Surname, Discount) VALUES (3, 'Michael', 'Johnson', 15);
INSERT INTO Client (ID, Name, Surname, Discount) VALUES (4, 'Emily', 'Williams', 0);
INSERT INTO Client (ID, Name, Surname, Discount) VALUES (5, 'David', 'Brown', 20);

--Table: Status

INSERT INTO Status (ID, Name) VALUES (1, 'Pending');
INSERT INTO Status (ID, Name) VALUES (2, 'Processing');
INSERT INTO Status (ID, Name) VALUES (3, 'Completed');

--Table: Order

INSERT INTO ClientOrder (ID, CreatedDate, Status_ID, Client_ID) VALUES (1, TO_DATE('2023-05-01', 'YYYY-MM-DD'), 1, 1);
INSERT INTO ClientOrder (ID, CreatedDate, Status_ID, Client_ID) VALUES (2, TO_DATE('2023-05-03', 'YYYY-MM-DD'), 2, 2);
INSERT INTO ClientOrder (ID, CreatedDate, Status_ID, Client_ID) VALUES (3, TO_DATE('2023-05-05', 'YYYY-MM-DD'), 1, 3);
INSERT INTO ClientOrder (ID, CreatedDate, Status_ID, Client_ID) VALUES (4, TO_DATE('2023-05-08', 'YYYY-MM-DD'), 3, 4);
INSERT INTO ClientOrder (ID, CreatedDate, Status_ID, Client_ID) VALUES (5, TO_DATE('2023-05-10', 'YYYY-MM-DD'), 2, 1);

--Table: Product

INSERT INTO Product (ID, Name, CurrentAvailability) VALUES (1, 'T-Shirt', 50);
INSERT INTO Product (ID, Name, CurrentAvailability) VALUES (2, 'Jeans', 30);
INSERT INTO Product (ID, Name, CurrentAvailability) VALUES (3, 'Dress', 20);
INSERT INTO Product (ID, Name, CurrentAvailability) VALUES (4, 'Jacket', 15);
INSERT INTO Product (ID, Name, CurrentAvailability) VALUES (5, 'Shoes', 25);
INSERT INTO Product (ID, Name, CurrentAvailability) VALUES (6, 'Sweater', 10);
INSERT INTO Product (ID, Name, CurrentAvailability) VALUES (7, 'Shorts', 20);
INSERT INTO Product (ID, Name, CurrentAvailability) VALUES (8, 'Skirt', 15);
INSERT INTO Product (ID, Name, CurrentAvailability) VALUES (9, 'Blouse', 12);
INSERT INTO Product (ID, Name, CurrentAvailability) VALUES (10, 'Pants', 18);

--Table: Order_Product

INSERT INTO Order_Product (ID, Price, Amount, Order_ID, Product_ID) VALUES (1, 29.99, 2, 1, 3);
INSERT INTO Order_Product (ID, Price, Amount, Order_ID, Product_ID) VALUES (2, 19.99, 1, 2, 1);
INSERT INTO Order_Product (ID, Price, Amount, Order_ID, Product_ID) VALUES (3, 39.99, 1, 2, 4);
INSERT INTO Order_Product (ID, Price, Amount, Order_ID, Product_ID) VALUES (4, 24.99, 3, 3, 2);
INSERT INTO Order_Product (ID, Price, Amount, Order_ID, Product_ID) VALUES (5, 34.99, 2, 4, 5);
INSERT INTO Order_Product (ID, Price, Amount, Order_ID, Product_ID) VALUES (6, 49.99, 1, 4, 3);
INSERT INTO Order_Product (ID, Price, Amount, Order_ID, Product_ID) VALUES (7, 14.99, 2, 5, 6);
INSERT INTO Order_Product (ID, Price, Amount, Order_ID, Product_ID) VALUES (8, 29.99, 1, 5, 9);
INSERT INTO Order_Product (ID, Price, Amount, Order_ID, Product_ID) VALUES (9, 19.99, 1, 5, 7);
INSERT INTO Order_Product (ID, Price, Amount, Order_ID, Product_ID) VALUES (10, 39.99, 1, 5, 10);
INSERT INTO Order_Product (ID, Price, Amount, Order_ID, Product_ID) VALUES (11, 59.99, 2, 1, 4);
INSERT INTO Order_Product (ID, Price, Amount, Order_ID, Product_ID) VALUES (12, 34.99, 1, 1, 6);