Team8-Phase2-1.sql

CREATE TABLE BANK (
    BANK_ID NUMBER NOT NULL,
    BRANCH_NAME VARCHAR2(50) NOT NULL,
    STREET_ADDRESS VARCHAR2(70),
    CITY VARCHAR2(30),
    STATE VARCHAR2(10),
    ZIP_CODE NUMBER,
    UNIQUE(BRANCH_NAME),
    PRIMARY KEY (BANK_ID)
);

CREATE TABLE ACCOUNT(
	ACCOUNT_NUMBER VARCHAR2(20) NOT NULL,
	BALANCE NUMBER(12),
	PASSWORD CHAR(64) NOT NULL,
	H_ID NUMBER NOT NULL,
	PRIMARY KEY(ACCOUNT_NUMBER)
);

CREATE TABLE TRANSACTION (
    TXN_ID NUMBER(10) NOT NULL,
	TXN_DATE DATE NOT NULL,
    METHOD VARCHAR2(10) NOT NULL,
    -- 0: 출금, 1: 입금
	TRANSFER_DIRECTION NUMBER(1) NOT NULL,
    AMOUNT NUMBER(12) NOT NULL,
    CNTR_NAME VARCHAR2(50) NOT NULL,
    CNTR_CTRY CHAR(3) NOT NULL,
    CNTR_ACC_NO CHAR(12) NOT NULL,
    ACCOUNT_NUMBER VARCHAR2(20) NOT NULL,
    STATUS CHAR(1) NOT NULL,
    PRIMARY KEY (TXN_ID),
    FOREIGN KEY (ACCOUNT_NUMBER) REFERENCES ACCOUNT (ACCOUNT_NUMBER)
);

CREATE TABLE DNG_TXN (
    TXN_ID NUMBER(10) NOT NULL,
    SCORE NUMBER(3) NOT NULL,
    REASON VARCHAR2(50) NOT NULL,
    -- 0: On Judging(심사 중), 1: Permitted(거래허가됨), 2: Rejected(거래거부됨), 3: Reported(금융당국에 보고됨)
    STATUS CHAR(1) NOT NULL,
	PRIMARY KEY(TXN_ID),
    FOREIGN KEY (TXN_ID) REFERENCES TRANSACTION (TXN_ID)
);

CREATE TABLE HOLDER(
	H_ID Number NOT NULL,
    NAME VARCHAR2(50) NOT NULL,
    SEX VARCHAR2(10) NOT NULL,
	ADDRESS VARCHAR2(150),
	NATIONALITY VARCHAR2(20) NOT NULL,
	PHONE_NUMBER VARCHAR2(20) NOT NULL,
	PRIMARY KEY(H_ID),
	UNIQUE(PHONE_NUMBER)
);

CREATE TABLE INITIATION (
    TXN_ID NUMBER(10) NOT NULL,
    BANK_ID NUMBER NOT NULL,
    H_ID NUMBER NOT NULL,
    FOREIGN KEY (TXN_ID) REFERENCES TRANSACTION (TXN_ID),
    FOREIGN KEY (BANK_ID) REFERENCES BANK (BANK_ID),
    FOREIGN KEY (H_ID) REFERENCES HOLDER (H_ID),
    PRIMARY KEY (TXN_ID)
);


CREATE TABLE DNG_HOLDER(
	H_ID NUMBER NOT NULL,
	SCORE NUMBER(3) NOT NULL,
	PRIMARY KEY(H_ID)
);

ALTER TABLE DNG_HOLDER ADD FOREIGN KEY (H_ID) 
REFERENCES HOLDER(H_ID);

ALTER TABLE ACCOUNT ADD FOREIGN KEY (H_ID) 
REFERENCES HOLDER(H_ID);


CREATE TABLE DNG_PERS (
    DNG_ID      NUMBER          NOT NULL,
    NAME        VARCHAR2(50)    NOT NULL,
    REASON      VARCHAR2(100),
    BANK_ID     NUMBER,
    PRIMARY KEY (DNG_ID)
);

CREATE TABLE DNG_ACCT (
    ACCT_NO     VARCHAR2(20)     NOT NULL,
    REASON      VARCHAR2(100),
    BANK_ID     NUMBER,
    PRIMARY KEY (ACCT_NO)
);

CREATE TABLE COUNTRY_CREDIT (
    NAME        VARCHAR2(20)     NOT NULL,
    COUNTRY_CODE CHAR(3),
    CREDIT      Number,
    BANK_ID     NUMBER,
    PRIMARY KEY (NAME)
);

ALTER TABLE DNG_PERS ADD FOREIGN KEY (BANK_ID) REFERENCES BANK(BANK_ID);
ALTER TABLE DNG_ACCT ADD FOREIGN KEY (BANK_ID) REFERENCES BANK(BANK_ID);
ALTER TABLE COUNTRY_CREDIT ADD FOREIGN KEY (BANK_ID) REFERENCES BANK(BANK_ID);