DATA BASE

CREATE TABLE USERS(USERNAME VARCHAR2(8) PRIMARY KEY,PASSWORD VARCHAR2(8));

CREATE TABLE USER_ACCOUNT_TYPES(
ACC_TYPE_CODE VARChar2(2) CONSTRAINT USERACCTYPE REFERENCES ACCOUNT_TYPES(ACC_TYPE_CODE),
ACC_TYPE_DESC Varchar2(20),
MIN_AMT Number(5),
INTEREST_RATE Number(4,2),
MIN_PERIOD Number(2));

CREATE TABLE FUNDS_TRANSFERS(FUNDS_TRANS_CODE VARCHAR2(10) PRIMARY KEY,
ORIGIN_ACC_NO VARCHAR2(8),
DEST_ACC_NO VARCHAR2(8),
DEST_BANK VARCHAR2(20),
DEST_BRANCH VARCHAR2(20),
TRANS_AMT NUMBER(10,2),
FREQUENCY VARCHAR2(1),
INSTALMENTS NUMBER(9),
TRANS_DATE DATE);

CREATE TABLE BILL_PAYMENTS(
BILL_PAY_CODE VARCHAR2(10),
ACCOUNT_NO VARCHAR2(8),
BILL_NO VARCHAR2(10),
BILL_DATE Date,
BILL_FROM Date,
BILL_TO Date,
PAYEE Varchar2(30),
BILL_AMOUNT Number(10,2),
BILL_DESC Varchar2(30));

create table bill_pay_code_maxno(bill_pay_code_maxno number);


CREATE TABLE TRANS_CODE_GEN(TRANS_CODE_GEN NUMBER(8));

CREATE TABLE TAX_PAYMENTS(TAX_PAY_CODE VARCHAR2(10),
ORIGIN_ACC_NO VARCHAR2(8),
TAX_PAYER_ID VARCHAR2(10),
TAX_PAY_DATE Date,
TAX_FROM Date,
TAX_TO Date,
INCOME Number(10,2),
TAX_AMOUNT Number(8,2),
TAX_AUTHORITY VARCHAR2(30));

CREATE TABLE DRAFT_CHEQUE(ACCOUNT_NO VARCHAR2(8),
DD_OR_CHEQ VARCHAR2(1),
PAYEE VARCHAR2(30),
DRAWN_ON DATE,
DD_CHEQ_NO VARCHAR2(10),
AMOUNT NUMBER(10,2),
DELIVERY VARCHAR2(1),
PAYEE_DOOR_NO VARCHAR2(10),
PAYEE_STREET VARCHAR2(20),
PAYEE_AREA VARCHAR2(20),
PAYEE_CITY VARCHAR2(20),
PAYEE_PIN VARCHAR2(6),
STATUS VARCHAR2(1));

CREATE TABLE NOMINEE(ACCOUNT_NO VARCHAR2(8),
NOM_NAME VARCHAR2(25),
NOM_ADDR VARCHAR2(40),
NOM_AGE NUMBER(3),
RELATIONSHIP VARCHAR2(10));

CREATE TABLE ACCOUNTS(ACCOUNT_NO VARCHAR2(8) PRIMARY KEY,
ACC_TYPE_CODE VARCHAR2(2) CONSTRAINT ACCTYPECODE REFERENCES ACCOUNT_TYPES(ACC_TYPE_CODE),
ACC_CAT VARCHAR2(1),
OPERATION_MODE VARCHAR2(1),
OPEN_DATE DATE,
BALANCE_AMT NUMBER(10,2),
INT_NAME VARCHAR2(25),
INT_ACC_NO VARCHAR2(8),
BRANCH VARCHAR2(20),
KNOW_APPLICANTS NUMBER(2));

CREATE TABLE ACC_NO_GEN(ACC_NO_GEN NUMBER(6));
CREATE TABLE INCREASE(ACCNO NUMBER(6))

Drop TABLE CUSTOMERS;
CREATE TABLE CUSTOMERS(
Cust_code VARChar2(7) PRIMARY KEY,
APPLICANT_NO Number(1),
ACCOUNT_NO VARChar2(8),
ACCOUNT_NO2 VARChar2(8),
CUST_FNAME Varchar2(25),
CUST_MNAME Varchar2(25),
CUST_LNAME Varchar2(25),
HOUSE_NO Varchar2(10),
STREET1 Varchar2(20),
STREET2 Varchar2(20),
AREA Varchar2(20),
CITY Varchar2(20),
PIN VARChar2(6),
STATE_CD VARChar2(2),
RES_PHONE VARChar2(13),
CELL_PHONE VARChar2(10),
EMAIL Varchar2(30),
NO_YEARS_ADDRESS Number(2),
PROFESSION Varchar2(20),
ORGANIZATION Varchar2(20),
WORKING_SINCE Date,
DESIGNATION Varchar2(20),
OFF_DOOR_NO Varchar2(10),
OFF_STREET Varchar2(20),
OFF_AREA Varchar2(20),
OFF_CITY Varchar2(20),
OFF_PIN VARChar2(6),
OFF_STATE_CD VARChar2(2),
OFF_PHONE VARChar2(13),
PAN_GIRN VARChar2(10),
GENDER VARChar2(1),
BIRTH_DATE Date,
MAR_STATUS VARChar2(1),
REL_FIRST_APP Varchar2(10),
EDUCATION Varchar2(20),
MONTHLY_INCOME Number(6),
GUARDIAN_NAME Varchar2(25));

CREATE TABLE CHEQUE_REORDER(CHEQ_RO_CODE VARCHAR2(9),
ACCOUNT_NO VARCHAR2(8),
REQUEST_DATE DATE);

CREATE TABLE LOAN_TYPES(LOAN_TYPE VARCHAR2(3) PRIMARY KEY,
LOAN_DESC VARCHAR2(20),
INTEREST_RATE NUMBER(4,2));

CREATE TABLE LOANISSUE(LOAN_CODE VARCHAR2(8) PRIMARY KEY,
LOAN_TYPE VARCHAR2(3),
ACCOUNT_NO VARCHAR2(8),
LOAN_AMT NUMBER(6),
ISSUE_DATE DATE,
DESCP VARCHAR2(20),
PERIOD NUMBER(3));

CREATE TABLE LOANS(LOAN_CODE VARCHAR2(8) CONSTRAINT LCODE REFERENCES LOANISSUE(LOAN_CODE),
LOAN_TYPE VARCHAR2(3),
ACCOUNT_NO VARCHAR2(8),
APPLIED_DATE DATE,
LOAN_PERIOD NUMBER(3),
LOAN_AMT NUMBER(6),
EMI NUMBER(6),
DOC_DETAILS VARCHAR2(40),
STATUS VARCHAR2(1),
NO_OF_INSTAL NUMBER(3),
LAST_PAID DATE);

CREATE TABLE TRANSACTIONS(ACCOUNT_NO VARCHAR2(8) CONSTRAINT ACC REFERENCES ACCOUNTS(ACCOUNT_NO),
TRANS_TYPE VARCHAR2(1),
TRANS_DESC VARCHAR2(30),
AMOUNT NUMBER(10,2),
TRANS_DATE Date,
TRANS_MODE VARCHAR2(1));

create table credit_cards(CREDIT_CARD_NO VARCHAR2(10) primary key,
ACCOUNT_NO VARCHAR2(8),
ISSUE_DATE DATE,
EXPIRY_DATE DATE,
MAX_AMT NUMBER(6),
STATUS VARCHAR2(1));

CREATE TABLE TAX_PAYMENTS(TAX_PAY_CODE VARCHAR2(10),
ORIGIN_ACC_NO VARCHAR2(8),
TAX_PAYER_ID VARCHAR2(10),
TAX_PAY_DATE Date,
TAX_FROM Date,
TAX_TO Date,
INCOME Number(10,2),
TAX_AMOUNT Number(8,2),
TAX_AUTHORITY VARCHAR2(30));

CREATE TABLE CARD_TRANSACTIONS(
CREDIT_CARD_NO VARChar2(10),
DESCRIPTION Varchar2(50),
CREDIT_AMT Number(10,2),
CREDIT_DATE Date,
PAYTO Varchar2(30));

/