--------------------------------------------------------------------------------
/*				                 Banking DDL           		  		          */
--------------------------------------------------------------------------------

    CREATE TABLE branch(
    branch_name VARCHAR(40),
    branch_city VARCHAR(25),
    assets NUMERIC(20,2) CHECK (assets > 0),
    CONSTRAINT branch_pkey PRIMARY KEY(branch_name)  
    );
    
CREATE TABLE customer(
    cust_ID VARCHAR(15),
    customer_name VARCHAR(30) NOT NULL,
    customer_street VARCHAR (40),
    customer_city VARCHAR(30),
    CONSTRAINT customer_ID_pkey PRIMARY KEY(cust_ID)
    );
CREATE TABLE loan(
    loan_number VARCHAR(20),
    branch_name VARCHAR(40),
    amount NUMERIC(20,2) DEFAULT 0.00 CHECK(amount>0),
    CONSTRAINT loan_number_pkey PRIMARY KEY(loan_number),
    CONSTRAINT loan_branch_name FOREIGN KEY(branch_name) REFERENCES branch(branch_name)
        ON DELETE SET NULL
        ON UPDATE CASCADE
    
);

CREATE TABLE borrower(
             cust_ID VARCHAR(15),
             loan_number VARCHAR(20),
             CONSTRAINT borrower_pkey PRIMARY KEY(cust_ID,loan_number),
    		 CONSTRAINT borrower_loan_number_fkey1 FOREIGN KEY (loan_number) REFERENCES loan(loan_number)
			    ON DELETE CASCADE
                ON UPDATE CASCADE,
             CONSTRAINT borrower_custID_fkey2 FOREIGN KEY (cust_ID) REFERENCES customer(cust_ID)
			    ON DELETE SET NULL
                ON UPDATE CASCADE
    );
    
CREATE TABLE account(
             account_number VARCHAR(20),
             branch_name VARCHAR(40),
             balance NUMERIC (20,2) DEFAULT 0.00,
             CONSTRAINT account_number_pkey PRIMARY KEY(account_number),
             CONSTRAINT account_branch_name_fkey1 FOREIGN KEY (branch_name) REFERENCES branch(branch_name)
                ON DELETE SET NULL
                ON UPDATE CASCADE
);

CREATE TABLE depositor(
             cust_ID VARCHAR(15),
             account_number VARCHAR(20),
             CONSTRAINT depositor_pkey PRIMARY KEY(cust_ID,account_number),
             CONSTRAINT deporitor_cust_ID_fkey FOREIGN KEY(cust_ID) REFERENCES customer(cust_ID)
                ON DELETE CASCADE
                ON UPDATE CASCADE
                );