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