TU_SIA_DDL

DROP TABLE banks cascade constraints;
DROP TABLE category cascade constraints;
DROP TABLE customers cascade constraints;
DROP TABLE products cascade constraints;
DROP TABLE transactions cascade constraints;

CREATE TABLE Banks (
 Bank_ID INTEGER NOT NULL PRIMARY KEY,
 Bank_Name VARCHAR (255) NOT NULL,
 Phone VARCHAR (25),
 Email VARCHAR (255),
 Address VARCHAR (255),
 City VARCHAR (255),
 Country VARCHAR (10)
)
tablespace TU_SIA_TBS_2;

INSERT INTO banks VALUES(1, 'Bank_1',485685, 'test_125@mail.com','Address1','Iasi','Romania');
INSERT INTO banks VALUES(2, 'Bank_2',485686, 'test_125@mail.com','Address2','Iasi','Romania');
INSERT INTO banks VALUES(3, 'Bank_3',485687, 'test_125@mail.com','Address3','Iasi','Romania');
INSERT INTO banks VALUES(4, 'Bank_4',485688, 'test_125@mail.com','Address4','Iasi','Romania');
INSERT INTO banks VALUES(5, 'Bank_5',485689, 'test_125@mail.com','Address5','Iasi','Romania');
INSERT INTO banks VALUES(6, 'Bank_6',485690, 'test_125@mail.com','Address6','Iasi','Romania');
INSERT INTO banks VALUES(7, 'Bank_7',485691, 'test_125@mail.com','Address7','Iasi','Romania');

CREATE TABLE Customers (
 Customer_ID INTEGER NOT NULL PRIMARY KEY,
 First_Name VARCHAR (50) NOT NULL,
 Last_name VARCHAR (50) NOT NULL,
 Email VARCHAR (255) NOT NULL,
 Phone VARCHAR (25),
 Active INTEGER NOT NULL,
 Bank_ID INTEGER NOT NULL,
 Branch_id INTEGER NOT NULL
) tablespace TU_SIA_TBS_1;

ALTER TABLE Customers add CONSTRAINT cust_bank_fk FOREIGN KEY (Branch_id) REFERENCES Banks (Bank_ID);
CREATE INDEX Customers_FK_IDX ON Customers(Branch_id) tablespace TU_SIA_TBS_IDX;
CREATE INDEX Customers_PK_FK_IDX ON Customers(Customer_ID, Branch_id) tablespace TU_SIA_TBS_IDX;

INSERT INTO Customers VALUES( 1,'Name_1','Last_1','TEST1@net.com',46984,1,1,1);
INSERT INTO Customers VALUES( 2,'Name_2','Last_2','TEST1@net.com',46984,0,2,2);
INSERT INTO Customers VALUES( 3,'Name_3','Last_3','TEST1@net.com',46984,1,3,3);
INSERT INTO Customers VALUES( 4,'Name_4','Last_4','TEST1@net.com',46984,1,4,4);
INSERT INTO Customers VALUES( 5,'Name_5','Last_5','TEST1@net.com',46984,1,5,5);
INSERT INTO Customers VALUES( 6,'Name_6','Last_6','TEST1@net.com',46984,1,6,6);
INSERT INTO Customers VALUES( 7,'Name_7','Last_7','TEST1@net.com',46984,1,7,7);

CREATE TABLE Category (
 Category_ID INTEGER NOT NULL primary key,
 Category_name VARCHAR (255) NOT NULL
)
tablespace TU_SIA_TBS_2;

INSERT INTO Category VALUES( 1,'Name_1');
INSERT INTO Category VALUES( 2,'Name_2');

CREATE TABLE Products (
 Product_ID INTEGER NOT NULL primary key,
 Product_name VARCHAR (255) NOT NULL,
 Category_ID INTEGER NOT NULL,
 Interest_rate NUMBER NOT NULL,
 Installements_numbers INTEGER NOT NULL
)
tablespace TU_SIA_TBS_2;

ALTER TABLE Products add CONSTRAINT prod_categ_fk FOREIGN KEY (Category_ID) REFERENCES Category (Category_ID);
CREATE INDEX Products_FK_IDX ON Products(Category_ID) tablespace TU_SIA_TBS_IDX;
CREATE INDEX Products_PK_FK_IDX ON Products(Product_ID, Category_ID) tablespace TU_SIA_TBS_IDX;

INSERT INTO Products VALUES ( 1,'Name_1',1,0.1,100     );
INSERT INTO Products VALUES ( 2,'Name_2',2,1.1,101     );
INSERT INTO Products VALUES ( 3,'Name_3',1,2.1,102     );
INSERT INTO Products VALUES ( 4,'Name_4',2,3.1,103     );
INSERT INTO Products VALUES ( 5,'Name_5',1,4.1,104     );
INSERT INTO Products VALUES ( 6,'Name_6',2,5.1,105     );
INSERT INTO Products VALUES ( 7,'Name_7',1,6.1,106     );

CREATE TABLE Transactions (
 Transaction_ID INTEGER NOT NULL primary key,
 Customer_ID INTEGER NOT NULL,
 Transaction_Date DATE NOT NULL,
 Year_month INTEGER NOT NULL,
 Installement_Date DATE NOT NULL,
 Bank_id INTEGER NOT NULL,
 Category_id INTEGER NOT NULL
)
tablespace TU_SIA_TBS_1;

ALTER TABLE Transactions MODIFY PARTITION BY LIST (Year_month) (PARTITION TP_FIRST VALUES (0));
ALTER TABLE Transactions SET PARTITIONING AUTOMATIC;

ALTER TABLE Transactions ADD CONSTRAINT trans_cust_fk FOREIGN KEY (Customer_ID) REFERENCES Customers (Customer_ID);
ALTER TABLE Transactions ADD CONSTRAINT trans_bank_fk FOREIGN KEY (Bank_id) REFERENCES Banks (Bank_id);
ALTER TABLE Transactions ADD CONSTRAINT trans_cat_fk FOREIGN KEY (Category_ID) REFERENCES Category (Category_ID);
CREATE INDEX Transactions_FK1_IDX ON Transactions(Customer_ID) tablespace TU_SIA_TBS_IDX;
CREATE INDEX Transactions_FK2_IDX ON Transactions(Bank_id) tablespace TU_SIA_TBS_IDX;
CREATE INDEX Transactions_FK3_IDX ON Transactions(Category_ID) tablespace TU_SIA_TBS_IDX;

INSERT INTO Transactions VALUES (1,1,TO_DATE('2020-02-10','YYYY-MM-DD'), 202002,TO_DATE('2020-02-10','YYYY-MM-DD'), 1,1);
INSERT INTO Transactions VALUES (2,2,TO_DATE('2020-02-10','YYYY-MM-DD'), 202002,TO_DATE('2020-02-10','YYYY-MM-DD'), 2,2);
INSERT INTO Transactions VALUES (3,1,TO_DATE('2020-02-10','YYYY-MM-DD'), 202002,TO_DATE('2020-02-10','YYYY-MM-DD'), 3,1);
INSERT INTO Transactions VALUES (4,2,TO_DATE('2020-02-10','YYYY-MM-DD'), 202002,TO_DATE('2020-02-10','YYYY-MM-DD'), 4,2);
INSERT INTO Transactions VALUES (5,1,TO_DATE('2020-02-10','YYYY-MM-DD'), 202002,TO_DATE('2020-02-10','YYYY-MM-DD'), 5,1);
INSERT INTO Transactions VALUES (6,2,TO_DATE('2020-02-10','YYYY-MM-DD'), 202002,TO_DATE('2020-02-10','YYYY-MM-DD'), 6,2);
INSERT INTO Transactions VALUES (7,1,TO_DATE('2020-02-10','YYYY-MM-DD'), 202002,TO_DATE('2020-02-10','YYYY-MM-DD'), 7,1);