DDL.txt

DROP SCHEMA Bank CASCADE;
Create Schema Bank;
SET SEARCH_PATH TO Bank , PUBLIC;

CREATE TABLE Branch (
    IFSC_Code varchar(11) NOT NULL,  
    Branch_Name VARCHAR(30) NOT NULL,
    City VARCHAR(30)  NOT NULL,
    State VARCHAR(30)  NOT NULL,
    Manager_ID CHAR(10) NOT NULL, 
    PRIMARY KEY (IFSC_Code)
);    


CREATE TABLE ATM (
    ATM_No BIGINT NOT NULL, 
    ATM_Location VARCHAR(150) NOT NULL,
    IFSC_Code varchar(11) NOT NULL,  
    PRIMARY KEY (ATM_No),
    FOREIGN KEY (IFSC_Code) REFERENCES Branch(IFSC_Code)
    );    
 
 CREATE TABLE CUSTOMER (
	 CIF_No BIGINT NOT NULL,
    Name VARCHAR(20) NOT NULL,
    Date_Of_Birth DATE NOT NULL,
    GENDER CHAR(1) NOT NULL,
    Aadhar_No BIGINT NOT NULL,
    Pan_Card_No VARCHAR(20) NULL,
	 MARITAL_STATUS VARCHAR(10) NOT NULL,
    Nominee_CIF_NO BIGINT NOT NULL,  
    PRIMARY KEY (CIF_No)
);

CREATE TABLE EMPLOYEE (
    Employee_ID VARCHAR(10) NOT NULL,
    NAME VARCHAR(20) NOT NULL,
    dob DATE NOT NULL,
    GENDER CHAR(1) NOT NULL,
    MARITAL_STATUS VARCHAR(10) NOT NULL,
    Permanent_address Varchar(150) NOT NULL,
    SALARY BIGINT NOT NULL,
    Joining_Date DATE NOT NULL, 
	CIF_No BIGINT NOT NULL, 
	IFSC_Code VARCHAR(11) NOT NULL,
	PRIMARY KEY (Employee_ID),
	FOREIGN KEY(CIF_No) REFERENCES CUSTOMER(CIF_No),
	FOREIGN KEY (IFSC_Code) REFERENCES Branch(IFSC_Code)
);

CREATE TABLE EMP_DEPENDENT (
	Employee_ID VARCHAR(10) NOT NULL,
	DEPEN_NO INTEGER NOT NULL,
	NAME VARCHAR(40) NOT NULL,
	RELATIONSHIP VARCHAR(40) NOT NULL ,
	DOB DATE NOT NULL,
	PROFESSION VARCHAR(40) NOT NULL,
	INSTITUTE_PLACE_OF_WORK  VARCHAR(100) NOT NULL ,
	PRIMARY KEY (Employee_ID, DEPEN_NO),
	FOREIGN KEY(Employee_ID) REFERENCES EMPLOYEE(Employee_ID)
);

CREATE TABLE Employee_email (
    Employee_ID VARCHAR(10) NOT NULL,    
    Email_id VARCHAR(30) NOT NULL,
    PRIMARY KEY (Employee_ID,Email_ID),
    FOREIGN KEY(Employee_ID) REFERENCES EMPLOYEE(Employee_ID)
);
  
CREATE TABLE EMPLOYEE_PHONE_NO (
    Employee_ID VARCHAR(10) NOT NULL,    
    Phone_No BIGINT NOT NULL, 
    PRIMARY KEY (Employee_ID,Phone_No),
    FOREIGN KEY(Employee_ID) REFERENCES EMPLOYEE(Employee_ID)
);



CREATE TABLE CUSTOMER_PHONE_NO (
    CIF_No BIGINT NOT NULL,     
    Phone_No BIGINT NOT NULL, 
    PRIMARY KEY (CIF_No,Phone_No),
    FOREIGN KEY(CIF_No) REFERENCES CUSTOMER(CIF_No)
);


CREATE TABLE Customer_email (
    CIF_No BIGINT NOT NULL,     
    Email_id VARCHAR(30) NOT NULL,
    PRIMARY KEY (Email_ID,CIF_No),
    FOREIGN KEY(CIF_No) REFERENCES CUSTOMER(CIF_No)
);


CREATE TABLE Customer_Income_Source (
    CIF_No BIGINT NOT NULL,     
    ISource VARCHAR(30) NOT NULL, 
    Income BIGINT NOT NULL, 
    PRIMARY KEY (ISource,CIF_No),
    FOREIGN KEY(CIF_No) REFERENCES CUSTOMER(CIF_No)
);

CREATE TABLE Customer_Address (
    CIF_No BIGINT NOT NULL,     
    Address VARCHAR(150) NOT NULL,    
    PRIMARY KEY (CIF_No,Address),
    FOREIGN KEY(CIF_No) REFERENCES CUSTOMER(CIF_No)
);





CREATE TABLE Account_Type (
    Type_ID CHAR(2) NOT NULL,    
    Type_Name VARCHAR(30) NOT NULL,    
    Interest_Rate INTEGER NOT NULL,     
    PRIMARY KEY (Type_ID)
);

CREATE TABLE Account (
    Account_No BIGINT NOT NULL, 
    Account_Type_ID CHAR(2) NOT NULL,    
    Balance BIGINT NOT NULL,  
    Creation_Date DATE NOT NULL,
    CIF_No BIGINT NOT NULL, 
    IFSC_Code varchar(11) NOT NULL,    
    PRIMARY KEY (Account_No),
    FOREIGN KEY(CIF_No) REFERENCES CUSTOMER(CIF_No),
    FOREIGN KEY(Account_Type_ID) REFERENCES Account_Type(Type_ID),
    FOREIGN KEY (IFSC_Code) REFERENCES Branch(IFSC_Code)
);
 
CREATE TABLE CARD(
    Card_No BIGINT NOT NULL,     
    CType VARCHAR(15) NOT NULL,    
    Exp_Date DATE NOT NULL,
    Pin_No INTEGER NOT NULL,
	Account_No BIGINT NOT NULL, 
    Card_Limit BIGINT NOT NULL,
    PRIMARY KEY (Card_No),
	FOREIGN KEY (Account_No) REFERENCES ACCOUNT(Account_No)
); 

CREATE TABLE INSURANCE(
   POLICY_NO BIGINT PRIMARY KEY,
   INSURANCE_Type VARCHAR(50) NOT NULL,
   PREMIUM_START_DATE DATE,
   INSURANCE_AMOUNT BIGINT NOT NULL,
   DATE_OF_CLAIM DATE,
   AMT_ON_CLAIM FLOAT(24) NOT NULL,
   CIF_No BIGINT NOT NULL ,
   NOMINEE_CIF_No BIGINT NOT NULL ,
   IFSC_Code varchar(11) NOT NULL,
   STATUS VARCHAR(20) NOT NULL,
   FOREIGN KEY (IFSC_Code) REFERENCES Branch(IFSC_Code),
   FOREIGN KEY(CIF_No) REFERENCES CUSTOMER(CIF_No)
);

CREATE TABLE INSURANCE_INSTALLMENT(
	POLICY_NO BIGINT,
	INSTALLMENT_NO_INSURANCE INTEGER NOT NULL,
	AMOUNT FLOAT(24) NOT NULL,
	INSTALLMENT_DATE DATE NOT NULL,
	NEXT_INSTALLMENT_DATE DATE NOT NULL,
	PRIMARY KEY(INSTALLMENT_NO_INSURANCE, POLICY_NO) ,
	FOREIGN KEY(POLICY_NO) REFERENCES INSURANCE(POLICY_NO)
);

CREATE TABLE Loan_Type (
    Product_Code CHAR(2) NOT NULL,     
    Type_Name VARCHAR(15) NOT NULL,    
    Interest_Rate FLOAT(24) NOT NULL,
    PRIMARY KEY (Product_Code)
);


CREATE TABLE LOAN(
   LOAN_ACCOUNT_NO BIGINT NOT NULL,
   CIF_No BIGINT NOT NULL, 
   AMOUNT FLOAT(24) NOT NULL,
   Product_Code CHAR(2) NOT NULL,   
   LOAN_DISBURSEMENT_DATE DATE NOT NULL,
   TIME_SPAN SMALLINT NOT NULL,
   GUARANTEER_AADHAR_NO BIGINT NOT NULL,
   IFSC_Code varchar(11) NOT NULL,
   PRIMARY KEY (LOAN_ACCOUNT_NO),
   FOREIGN KEY (IFSC_Code) REFERENCES Branch(IFSC_Code),
   FOREIGN KEY(CIF_No) REFERENCES CUSTOMER(CIF_No),
   FOREIGN KEY(Product_Code) REFERENCES LOAN_TYPE(Product_Code)
);


CREATE TABLE ASSETS_Log(
    LOAN_ACCOUNT_NO BIGINT NOT NULL,
    Assets VARCHAR(20) NOT NULL,  
    PRIMARY KEY (LOAN_ACCOUNT_NO,Assets),
    FOREIGN KEY (LOAN_ACCOUNT_NO) REFERENCES LOAN(LOAN_ACCOUNT_NO)
);


CREATE TABLE Loan_Income_Source(
    LOAN_ACCOUNT_NO BIGINT NOT NULL,     
    Source VARCHAR(50) NOT NULL,    
    Income BIGINT NOT NULL,
	PRIMARY KEY (Source,LOAN_ACCOUNT_NO),
    FOREIGN KEY (LOAN_ACCOUNT_NO) REFERENCES LOAN(LOAN_ACCOUNT_NO)
);


CREATE TABLE Fixed_Deposit (
    FD_ID VARCHAR(20) NOT NULL,
    CIF_No BIGINT NOT NULL,  
    Creation_Date DATE NOT NULL,
    Mature_Date DATE NOT NULL,
    Deposit_Amount BIGINT NULL,
    Interest_Rate INTEGER NOT NULL,  
    IFSC_Code varchar(11) NOT NULL,
    PRIMARY KEY(FD_ID),
    FOREIGN KEY (IFSC_Code) REFERENCES Branch(IFSC_Code),
    FOREIGN KEY(CIF_No) REFERENCES CUSTOMER(CIF_No)
);


CREATE TABLE LOAN_Installment(
    Loan_Account_No BIGINT NOT NULL,
    Installment_No_Loan INTEGER NOT NULL,
    Repayment_Date DATE NOT NULL,
    Repayment_Due_Date DATE NOT NULL,
    Amount_Paid BIGINT NOT NULL,
    Penalty BIGINT ,
    STATUS VARCHAR(20) NOT NULL,
    PRIMARY KEY (Loan_Account_No,Installment_No_Loan),
    FOREIGN KEY(Loan_Account_No) REFERENCES LOAN(Loan_Account_No)
);

CREATE TABLE Transactions (
    TID BIGSERIAL NOT NULL,
    Account_No BIGINT NOT NULL,  
    Account_to BIGINT ,
    Amount FLOAT(24) NOT NULL,  
    BALANCE BIGINT NOT NULL,
    Time_Stamp TIMESTAMP NOT NULL,  
    DD_No BIGINT ,  
	FD_ID VARCHAR(20),
    Cheque_No BIGINT,  
    Card_No BIGINT ,
    No_Of_Notes BIGINT ,
    Loan_Account_No BIGINT,  
    Installment_No_Loan INTEGER ,  
    POLICY_NO INTEGER ,
    INSTALLMENT_NO_INSURANCE INTEGER ,
    IFSC_Code varchar(11) NOT NULL, 
    Narration Varchar(10) NOT NULL,  
    PRIMARY KEY (TID),
    FOREIGN KEY (IFSC_Code) REFERENCES Branch(IFSC_Code),
    FOREIGN KEY (Card_No) REFERENCES CARD(Card_No),
    FOREIGN KEY (Account_No) REFERENCES ACCOUNT(Account_No),
    FOREIGN KEY (Account_to) REFERENCES ACCOUNT(Account_No),
    FOREIGN KEY (Loan_Account_No, Installment_No_Loan) REFERENCES   		   LOAN_Installment(Loan_Account_No,Installment_No_Loan) ,
    FOREIGN KEY (Policy_no, Installment_No_Insurance) REFERENCES INSURANCE_INSTALLMENT(Policy_no,Installment_No_Insurance) 
      
); 

CREATE TABLE TransactsAt (
    TID BIGINT NOT NULL,     
    ATM_No BIGINT NOT NULL, 
    ReceiptNo BIGSERIAL NOT NULL,    
    PRIMARY KEY (TID,ATM_No),
    FOREIGN KEY (TID) REFERENCES Transactions(TID),  
    FOREIGN KEY (ATM_No) REFERENCES ATM(ATM_No) 
);