Data Warehouse Project

1   ‐‐Create Customer table 2   CREATE TABLE "CUSTOMER" ( 3    "CUSTOMERID" BIGINT PRIMARY KEY, 4    "CUSTOMERNAME" VARCHAR(1000) NOT NULL, 5    "DATEOFBIRTH" DATETIME NOT NULL, 6    "GENDER" VARCHAR(1000) NOT NULL, 7    "FICOSCORE" BIGINT NOT NULL 8   ); 9   

10   ‐‐Create Table VENDOR 11   CREATE TABLE "VENDOR" ( 12    "VENDORID" BIGINT PRIMARY KEY, 13    "VENDORNAME" VARCHAR(1000 ) NOT NULL, 14    "VENDORTYPE" VARCHAR(1000 ) NOT NULL, 15    "VENDORADDRESS" VARCHAR(1000 ) NOT NULL, 16    "VENDORPHONE" VARCHAR(1000 ) NOT NULL 17   ); 18    19   ‐‐Create Table Account 20   CREATE TABLE "ACCOUNT" ( 21    "ACCOUNTID" BIGINT PRIMARY KEY, 22    "ACCOUNTTYPE" VARCHAR(1000 ) NOT NULL, 23    "INTERESTRATE" DECIMAL(12, 2) NOT NULL, 24    "CREDITLIMIT" BIGINT NOT NULL, 25    "MINIMUMPAYMENT" BIGINT NOT NULL, 26    "AMOUNTDUE" BIGINT NOT NULL, 27    "LASTPAYMENTAMOUNT" BIGINT NOT NULL, 28    "DATEACCOUNTOPENED" DATETIME NOT NULL, 29    "DATEACCOUNTCLOSED" DATETIME NOT NULL, 30    "CUSTOMERID" BIGINT NOT NULL, 31    "VENDORID" BIGINT NOT NULL 32   ); 33   ALTER TABLE "ACCOUNT" ADD CONSTRAINT "FK_ACCOUNT__CUSTOMERID" FOREIGN KEY ("CUSTOMERID")

REFERENCES "CUSTOMER" ("CUSTOMERID"); 34    35   ALTER TABLE "ACCOUNT" ADD CONSTRAINT "FK_ACCOUNT__VENDORID" FOREIGN KEY ("VENDORID") REFERENCES

"VENDOR" ("VENDORID"); 36    37   ‐‐Create Table TRANSACTION 38   CREATE TABLE "TRANSACTION" ( 39    "TRANSACTIONID" BIGINT PRIMARY KEY, 40    "DATE" DATETIME NOT NULL, 41    "TIME" VARCHAR(30) NOT NULL, 42    "AMOUNT" BIGINT NOT NULL, 43    "AUTHORIZATIONCODE" VARCHAR(1000 ) NOT NULL, 44    "ACCOUNTID" BIGINT NOT NULL 45   ); 46    47   ALTER TABLE "TRANSACTION" ADD CONSTRAINT "FK_TRANSACTION__ACCOUNTID" FOREIGN KEY ("ACCOUNTID")

REFERENCES "ACCOUNT" ("ACCOUNTID"); 48    49    50   ‐‐Insert into Customer Table 51   insert into CUSTOMER values(1,'David','12‐Aug‐1989','Male',69); 52   insert into CUSTOMER values(2,'Joe','12‐Mar‐1989','Female',45); 53   insert into CUSTOMER values(3,'John','13‐Aug‐1989','Male',80); 54   insert into CUSTOMER values(4,'Smith','15‐Aug‐1989','Male',90); 55   insert into CUSTOMER values(5,'Harry','12‐Jun‐1989','Male',69); 56    57   ‐‐Insert into VENDOR Table 58   insert into VENDOR values(101,'David Smith','HDFC','12‐Havelock, UK','12456789'); 59   insert into VENDOR values(201,'Martin Smith','ICICI','24‐ Birhana, UK','12456799'); 60   insert into VENDOR values(301,'LO Jack','SBI','13‐South wales, UK','12456779'); 61   insert into VENDOR values(401,'Sithia','RBS','134‐South wales, UK','12456709');

62   insert into VENDOR values(501,'Fresar','RBI','136‐South wales, UK','12789789'); 63    64   ‐‐Insert into Account Table 65   insert into Account

values(1,'Saving',12,300000,12000,2000,3000,'12‐Mar‐2001','13‐Mar‐2017',1,101); 66   insert into Account

values(2,'Current',13,350000,14000,7000,8000,'14‐Jun‐2001','16‐Jun‐2017',2,201); 67   insert into Account

values(3,'Saving',7.5,200000,15000,6000,9000,'12‐May‐2001','12‐May‐2017',3,301); 68   insert into Account

values(4,'Current',6,280000,16000,5000,6000,'13‐Aug‐2001','18‐Mar‐2017',4,401); 69   insert into Account

values(5,'Saving',12,370000,17000,4000,5000,'12‐Dec‐2001','16‐Dec‐2017',5,501); 70    71   ‐‐Insert into Transaction Table 72   insert into Transaction values(1,'12‐Mar‐2001',to_dsinterval('0 07:39:33'),2000,'ARR001',1); 73   insert into Transaction values(2,'14‐Mar‐2001',to_dsinterval('0 07:40:33'),3000,'ARR002',2); 74   insert into Transaction values(3,'15‐Mar‐2001',to_dsinterval('0 07:50:33'),4000,'ARR003',3); 75   insert into Transaction values(4,'16‐Mar‐2001',to_dsinterval('0 07:46:33'),5000,'ARR004',4); 76   insert into Transaction values(5,'17‐Mar‐2001',to_dsinterval('0 07:29:33'),6000,'ARR005',5); 77