db_script.sql

DROP TABLE TB_DOC;
DROP TABLE TB_TRANSACTION;
DROP TABLE TB_ACCOUNT;
DROP TABLE TB_ADDRESS;
DROP TABLE TB_USER;
DROP TABLE TB_BRANCH;

CREATE TABLE USER_SEQUENCES(

);


CREATE TABLE TB_USER (
    USER_ID varchar(32) PRIMARY KEY,
    USER_NAME varchar(100) NOT NULL,
    FULL_NAME varchar(255),
	PASSWORD varchar(100),
	ROLE varchar(50)
);


ALTER TABLE TB_USER ADD UNIQUE (USER_NAME);

CREATE TABLE TB_BRANCH(
	BRANCH_ID varchar(32) PRIMARY KEY,
	BRANCH_NO varchar(10),
	BRANCH_NAME varchar(200),
	BRANCH_ADDR varchar(300)
);

CREATE TABLE TB_ADDRESS (
	ADDR_ID varchar(32) PRIMARY KEY,
	POST_CODE varchar(10),
	ADDR1 varchar(255),
	ADDR2 varchar(255),
	ADDR3 varchar(255),
	ADDR4 varchar(255)
);


CREATE TABLE TB_DOC (
	DOC_ID varchar(32) PRIMARY KEY,
	DOC_NAME varchar(255),
	CONTENT_TYPE varchar(200),
	DATA blob,
	CREATE_DT TIMESTAMP,
	FK_USER_ID varchar(32)
);
ALTER TABLE TB_DOC ADD FOREIGN KEY (FK_USER_ID) REFERENCES TB_USER(USER_ID); 



CREATE TABLE TB_ACCOUNT (
    ACC_ID varchar(32) PRIMARY KEY,
    ACC_TYPE varchar(10),
    ACC_NO varchar(20),
    ACC_NAME varchar(255),
    ACC_SEQ varchar(10),
    ACC_CREATED_DATE TIMESTAMP,
    CURRENCY varchar(20),
    BALANCE number(11,2),
    FK_BRANCH_ID varchar(32),
    FK_ADDRESS_ID varchar(32),
    FK_USER_ID varchar(32)
);

ALTER TABLE TB_ACCOUNT ADD FOREIGN KEY (FK_BRANCH_ID) REFERENCES TB_BRANCH(BRANCH_ID);
ALTER TABLE TB_ACCOUNT ADD FOREIGN KEY (FK_ADDRESS_ID) REFERENCES TB_ADDRESS(ADDR_ID);
ALTER TABLE TB_ACCOUNT ADD FOREIGN KEY (FK_USER_ID) REFERENCES TB_USER(USER_ID);


CREATE TABLE TB_TRANSACTION (
	TXN_ID varchar(32) PRIMARY KEY,
	TXN_DATE timestamp,
	TXN_AMT int,
	TXN_NO varchar(200),
	VOC_NO varchar(200),
	PURPOSE varchar(200),
	TXN_DESC varchar(255),
	BAL_AFT number(11,2),
	FK_ACC_ID varchar(32)
);

ALTER TABLE TB_TRANSACTION ADD FOREIGN KEY (FK_ACC_ID) REFERENCES TB_ACCOUNT(ACC_ID);


-- user test data
INSERT INTO TB_USER (USER_ID, USER_NAME, FULL_NAME, PASSWORD, ROLE)
VALUES('userId1', 'USER1', 'User 1 full name', 'password', 'USER');

INSERT INTO TB_USER (USER_ID, USER_NAME, FULL_NAME, PASSWORD, ROLE)
VALUES('userId2', 'USER2', 'User 2 full name', 'password', 'USER');

INSERT INTO TB_USER (USER_ID, USER_NAME, FULL_NAME, PASSWORD, ROLE)
VALUES('userId3', 'USER3', 'User 3 full name', 'password', 'USER');

INSERT INTO TB_USER (USER_ID, USER_NAME, FULL_NAME, PASSWORD, ROLE)
VALUES('userId99', 'ADMIN', 'Administrator', 'password', 'ADMIN');


-- branch test data
INSERT INTO TB_BRANCH (BRANCH_ID, BRANCH_NO, BRANCH_NAME, BRANCH_ADDR)
VALUES ('branchId1', '7001', 'Woodlands Branch', '1 Woodlands St 12 #01-03 Singapore 738622');

INSERT INTO TB_BRANCH (BRANCH_ID, BRANCH_NO, BRANCH_NAME, BRANCH_ADDR)
VALUES ('branchId2', '7002', 'Raffles Branch', '1 Beach Road #05-01 Singapore 189673');

INSERT INTO TB_BRANCH (BRANCH_ID, BRANCH_NO, BRANCH_NAME, BRANCH_ADDR)
VALUES ('branchId3', '7003', 'Bugis Junction Branch', '‎200 Victoria Street, Singapore 628622');

INSERT INTO TB_BRANCH (BRANCH_ID, BRANCH_NO, BRANCH_NAME, BRANCH_ADDR)
VALUES ('branchId4', '7004', 'Jurong branch', '2 Jurong West Avenue #02-03 Singapore 558622');


-- address test data
INSERT INTO TB_ADDRESS(ADDR_ID, POST_CODE, ADDR1, ADDR2, ADDR3, ADDR4)
VALUES ('addressId1', '764504', 'Yishun', 'ave 1', '#01-02', 'Singapore');

INSERT INTO TB_ADDRESS(ADDR_ID, POST_CODE, ADDR1, ADDR2, ADDR3, ADDR4)
VALUES ('addressId2', '854623', 'Woodlands', 'ave 5', '#25-02', 'Singapore');

INSERT INTO TB_ADDRESS(ADDR_ID, POST_CODE, ADDR1, ADDR2, ADDR3, ADDR4)
VALUES ('addressId3', '985421', 'Raffles', 'street 82', '#75-32', 'Singapore');

INSERT INTO TB_ADDRESS(ADDR_ID, POST_CODE, ADDR1, ADDR2, ADDR3, ADDR4)
VALUES ('addressId4', '965723', 'Jurong', 'lorlong 1', '#01-79', 'Singapore');

INSERT INTO TB_ADDRESS(ADDR_ID, POST_CODE, ADDR1, ADDR2, ADDR3, ADDR4)
VALUES ('addressId5', '741898', 'Choa Chu Kang', 'ring road', '#10-111', 'Singapore');

INSERT INTO TB_ADDRESS(ADDR_ID, POST_CODE, ADDR1, ADDR2, ADDR3, ADDR4)
VALUES ('addressId6', '258934', 'Bishan', 'junction 8', '#05-02', 'Singapore');

INSERT INTO TB_ADDRESS(ADDR_ID, POST_CODE, ADDR1, ADDR2, ADDR3, ADDR4)
VALUES ('addressId7', '441723', 'Ang Mo Kio', 'ave 1', '#05-99', 'Singapore');

-- account test data
INSERT INTO TB_ACCOUNT(ACC_ID, ACC_TYPE, ACC_NO, ACC_NAME, ACC_SEQ, ACC_CREATED_DATE, CURRENCY, BALANCE, FK_BRANCH_ID, FK_ADDRESS_ID, FK_USER_ID)
VALUES ('accountId1', 'MAIN', '65001123456', 'Saving 01', '0', sysdate, 'SGD', 18001.22, 'branchId1', 'addressId7', 'userId1');

INSERT INTO TB_ACCOUNT(ACC_ID, ACC_TYPE, ACC_NO, ACC_NAME, ACC_SEQ, ACC_CREATED_DATE, CURRENCY, BALANCE, FK_BRANCH_ID, FK_ADDRESS_ID, FK_USER_ID)
VALUES ('accountId2', 'SUB', '6500290001', 'Saving 02', '1', sysdate, 'RMB', 50001.00, 'branchId1', 'addressId7', 'userId1');

INSERT INTO TB_ACCOUNT(ACC_ID, ACC_TYPE, ACC_NO, ACC_NAME, ACC_SEQ, ACC_CREATED_DATE, CURRENCY, BALANCE, FK_BRANCH_ID, FK_ADDRESS_ID, FK_USER_ID)
VALUES ('accountId3', 'SUB', '6500290002', 'Spending', '2', sysdate, 'RMB', 21001.00, 'branchId2', 'addressId7', 'userId1');

INSERT INTO TB_ACCOUNT(ACC_ID, ACC_TYPE, ACC_NO, ACC_NAME, ACC_SEQ, ACC_CREATED_DATE, CURRENCY, BALANCE, FK_BRANCH_ID, FK_ADDRESS_ID, FK_USER_ID)
VALUES ('accountId4', 'MAIN', '65001987121', 'Saving 01', '0', sysdate, 'SGD', 5001.00, 'branchId2', 'addressId6', 'userId2');

INSERT INTO TB_ACCOUNT(ACC_ID, ACC_TYPE, ACC_NO, ACC_NAME, ACC_SEQ, ACC_CREATED_DATE, CURRENCY, BALANCE, FK_BRANCH_ID, FK_ADDRESS_ID, FK_USER_ID)
VALUES ('accountId5', 'SUB', '6500191245', 'Draft acc', '1', sysdate, 'RMB', 95001.00, 'branchId2', 'addressId6', 'userId2');

INSERT INTO TB_ACCOUNT(ACC_ID, ACC_TYPE, ACC_NO, ACC_NAME, ACC_SEQ, ACC_CREATED_DATE, CURRENCY, BALANCE, FK_BRANCH_ID, FK_ADDRESS_ID, FK_USER_ID)
VALUES ('accountId6', 'SUB', '6500191246', 'Invest', '2', sysdate, 'SGD', 1001.00, 'branchId3', 'addressId6', 'userId2');

INSERT INTO TB_ACCOUNT(ACC_ID, ACC_TYPE, ACC_NO, ACC_NAME, ACC_SEQ, ACC_CREATED_DATE, CURRENCY, BALANCE, FK_BRANCH_ID, FK_ADDRESS_ID, FK_USER_ID)
VALUES ('accountId7', 'SUB', '6500191247', 'Joint', '3', sysdate, 'RMB', 28001.99, 'branchId2', 'addressId6', 'userId2');

INSERT INTO TB_ACCOUNT(ACC_ID, ACC_TYPE, ACC_NO, ACC_NAME, ACC_SEQ, ACC_CREATED_DATE, CURRENCY, BALANCE, FK_BRANCH_ID, FK_ADDRESS_ID, FK_USER_ID)
VALUES ('accountId8', 'MAIN', '123456789', 'Saving', '0', sysdate, 'SGD', 65001.00, 'branchId3', 'addressId5', 'userId3');


INSERT INTO TB_TRANSACTION(TXN_ID,TXN_DATE,TXN_AMT,TXN_NO,VOC_NO,PURPOSE,TXN_DESC,BAL_AFT,FK_ACC_ID)
VALUES ('txnId1', sysdate, -500, '52914', null, 'A', 'Rental', '18501.22', 'accountId1');

INSERT INTO TB_TRANSACTION(TXN_ID,TXN_DATE,TXN_AMT,TXN_NO,VOC_NO,PURPOSE,TXN_DESC,BAL_AFT,FK_ACC_ID)
VALUES ('txnId2', sysdate -1, -100, '52921', null, 'DA', 'Bill', '18601.22', 'accountId1');

INSERT INTO TB_TRANSACTION(TXN_ID,TXN_DATE,TXN_AMT,TXN_NO,VOC_NO,PURPOSE,TXN_DESC,BAL_AFT,FK_ACC_ID)
VALUES ('txnId3', sysdate -1, -200, '52914', null, 'AC', 'Food', '18801.22', 'accountId1');

INSERT INTO TB_TRANSACTION(TXN_ID,TXN_DATE,TXN_AMT,TXN_NO,VOC_NO,PURPOSE,TXN_DESC,BAL_AFT,FK_ACC_ID)
VALUES ('txnId4', sysdate, -300, '52914', null, 'P', 'Bus', '11501.22', 'accountId2');

INSERT INTO TB_TRANSACTION(TXN_ID,TXN_DATE,TXN_AMT,TXN_NO,VOC_NO,PURPOSE,TXN_DESC,BAL_AFT,FK_ACC_ID)
VALUES ('txnId5', sysdate, -100, '52914', null, 'X', 'Clothes', '16501.22', 'accountId2');

INSERT INTO TB_TRANSACTION(TXN_ID,TXN_DATE,TXN_AMT,TXN_NO,VOC_NO,PURPOSE,TXN_DESC,BAL_AFT,FK_ACC_ID)
VALUES ('txnId6', sysdate, -600, '52914', null, 'T', 'Furniture', '9501.22', 'accountId2');

INSERT INTO TB_TRANSACTION(TXN_ID,TXN_DATE,TXN_AMT,TXN_NO,VOC_NO,PURPOSE,TXN_DESC,BAL_AFT,FK_ACC_ID)
VALUES ('txnId7', sysdate, -1200, '52914', null, 'CA', 'Food', '5301.22', 'accountId3');

INSERT INTO TB_TRANSACTION(TXN_ID,TXN_DATE,TXN_AMT,TXN_NO,VOC_NO,PURPOSE,TXN_DESC,BAL_AFT,FK_ACC_ID)
VALUES ('txnId8', sysdate, -800, '52914', null, 'AD', 'Electronic', '1201.22', 'accountId3');

INSERT INTO TB_TRANSACTION(TXN_ID,TXN_DATE,TXN_AMT,TXN_NO,VOC_NO,PURPOSE,TXN_DESC,BAL_AFT,FK_ACC_ID)
VALUES ('txnId9', sysdate, -200, '52914', null, 'DA', 'Rental', '19601.22', 'accountId4');

INSERT INTO TB_TRANSACTION(TXN_ID,TXN_DATE,TXN_AMT,TXN_NO,VOC_NO,PURPOSE,TXN_DESC,BAL_AFT,FK_ACC_ID)
VALUES ('txnId10', sysdate, -300, '52914', null, 'AC', 'Rental', '10321.22', 'accountId4');

INSERT INTO TB_TRANSACTION(TXN_ID,TXN_DATE,TXN_AMT,TXN_NO,VOC_NO,PURPOSE,TXN_DESC,BAL_AFT,FK_ACC_ID)
VALUES ('txnId11', sysdate, 100, '52914', 'P02', 'X', 'Clothes', '98501.22', 'accountId2');

INSERT INTO TB_TRANSACTION(TXN_ID,TXN_DATE,TXN_AMT,TXN_NO,VOC_NO,PURPOSE,TXN_DESC,BAL_AFT,FK_ACC_ID)
VALUES ('txnId12', sysdate, 600, '52914', 'P01', 'T', 'Furniture', '63501.22', 'accountId2');

INSERT INTO TB_TRANSACTION(TXN_ID,TXN_DATE,TXN_AMT,TXN_NO,VOC_NO,PURPOSE,TXN_DESC,BAL_AFT,FK_ACC_ID)
VALUES ('txnId13', sysdate, 1200, '52914', 'V03', 'CA', 'Food', '4001.22', 'accountId3');

INSERT INTO TB_TRANSACTION(TXN_ID,TXN_DATE,TXN_AMT,TXN_NO,VOC_NO,PURPOSE,TXN_DESC,BAL_AFT,FK_ACC_ID)
VALUES ('txnId14', sysdate, 800, '52914', 'C03', 'AD', 'Electronic', '3001.22', 'accountId3');

INSERT INTO TB_TRANSACTION(TXN_ID,TXN_DATE,TXN_AMT,TXN_NO,VOC_NO,PURPOSE,TXN_DESC,BAL_AFT,FK_ACC_ID)
VALUES ('txnId15', sysdate, 200, '52914', 'E03', 'DA', 'Rental', '9546.22', 'accountId4');

INSERT INTO TB_TRANSACTION(TXN_ID,TXN_DATE,TXN_AMT,TXN_NO,VOC_NO,PURPOSE,TXN_DESC,BAL_AFT,FK_ACC_ID)
VALUES ('txnId16', sysdate, 300, '52914', 'V03', 'AC', 'Rental', '23120.22', 'accountId4');