
CREATE TABLE customer 
(
    customer_name   varchar(80) PRIMARY KEY,
    customer_street varchar(80),
    customer_city   varchar(80)
)

CREATE TABLE branch (
    branch_name     varchar(80) PRIMARY KEY,
    branch_city     varchar(80),
    assets          numeric(12,2)
    CHECK (assets >= 0)
    
)


CREATE TABLE loan 
(
    loan_number     char(8) PRIMARY KEY,
    branch_name     varchar(80) REFERENCES branch (branch_name),
    amount          numeric(10,2) NOT NULL 
    CHECK (amount > 0)
)


CREATE TABLE borrower 
(
    customer_name   varchar(80) REFERENCES customer (customer_name),
    loan_number     char(8) REFERENCES loan (loan_number),
    
)

CREATE TABLE account 
(
    account_number  char(10) PRIMARY KEY,
    branch_name     varchar(80) REFERENCES branch (branch_name),
    balance         numeric(10,2) NOT NULL 
    CHECK (balance >= 0)
)

CREATE TABLE depositor 
(
    customer_name     varchar(80) REFERENCES customer (customer_name),
    account_number    char(10) REFERENCES account (account_number)
)



INSERT INTO customer (customer_name, customer_street, customer_city) 
    VALUES ('Adams', 'Spring St.', 'Pittsfield');
INSERT INTO customer (customer_name, customer_street, customer_city) 
    VALUES ('Brooks', 'Senator St.', 'Brooklyn');
INSERT INTO customer (customer_name, customer_street, customer_city) 
    VALUES ('Curry', 'North St.', 'Rye');   
INSERT INTO customer (customer_name, customer_street, customer_city) 
    VALUES ('Glen', 'Sand Hill St.', 'Woodside');  
INSERT INTO customer (customer_name, customer_street, customer_city) 
    VALUES ('Green', 'Walnut', 'Stamford');   
INSERT INTO customer (customer_name, customer_street, customer_city) 
    VALUES ('Hayes', 'Main', 'Harrison');
INSERT INTO customer (customer_name, customer_street, customer_city) 
    VALUES ('Johnson', 'Alma', 'Palo Alto');   
INSERT INTO customer (customer_name, customer_street, customer_city) 
    VALUES ('Jones', 'Main', 'Harrison');   
INSERT INTO customer (customer_name, customer_street, customer_city) 
    VALUES ('Lindsay', 'Park', 'Pittsfield');
INSERT INTO customer (customer_name, customer_street, customer_city) 
    VALUES ('Smith', 'North', 'Rye');   
INSERT INTO customer (customer_name, customer_street, customer_city) 
    VALUES ('Turner', 'Putnam', 'Stamford');
INSERT INTO customer (customer_name, customer_street, customer_city) 
    VALUES ('Williams', 'Nassau', 'Princeton');   





INSERT INTO branch (branch_name, branch_city, assets)
    VALUES ('Brighton', 'Brooklyn', 7100000.00);
INSERT INTO branch (branch_name, branch_city, assets)
    VALUES ('Downtown', 'Brooklyn', 9000000.00);
INSERT INTO branch (branch_name, branch_city, assets)
    VALUES ('Mianus', 'Horseneck', 400000.00);   
INSERT INTO branch (branch_name, branch_city, assets)
	VALUES ('North Town', 'Rye', 3700000.00);
INSERT INTO branch (branch_name, branch_city, assets)
	VALUES ('Perryridge', 'Horseneck', 1700000.00);
INSERT INTO branch (branch_name, branch_city, assets)
	VALUES ('Pawnal', 'Bennington', 300000.00);
INSERT INTO branch (branch_name, branch_city, assets)
	VALUES ('Redwood', 'Palo Alto', 2100000.00);
INSERT INTO branch (branch_name, branch_city, assets)
	VALUES ('Round Hill', 'Horseneck', 8000000.00);
    
    

INSERT INTO loan (loan_number, branch_name, amount)
    VALUES ('L-11', 'Round Hill', 900);
INSERT INTO loan (loan_number, branch_name, amount)
    VALUES ('L-14', 'Downtown', 1500);
INSERT INTO loan (loan_number, branch_name, amount)
    VALUES ('L-15', 'Perryridge', 1500); 
INSERT INTO loan (loan_number, branch_name, amount)
        VALUES ('L-16', 'Perryridge', 1300);
INSERT INTO loan (loan_number, branch_name, amount)
        VALUES ('L-17', 'Downtown', 1000);
INSERT INTO loan (loan_number, branch_name, amount)
    VALUES ('L-23', 'Redwood', 2000);
INSERT INTO loan (loan_number, branch_name, amount)
        VALUES ('L-93', 'Mianus', 500);
    


INSERT INTO borrower (customer_name, loan_number)
    VALUES ('Adams', 'L-16');
INSERT INTO borrower (customer_name, loan_number)
    VALUES ('Curry', 'L-93');
INSERT INTO borrower (customer_name, loan_number)
    VALUES ('Hayes', 'L-15');  
INSERT INTO borrower (customer_name, loan_number)
    VALUES ('Jackson', 'L-14');
INSERT INTO borrower (customer_name, loan_number)
    VALUES ('Jones', 'L-17');
INSERT INTO borrower (customer_name, loan_number)
    VALUES ('Smith', 'L-11');  
INSERT INTO borrower (customer_name, loan_number)
    VALUES ('Smith', 'L-23');
INSERT INTO borrower (customer_name, loan_number)
    VALUES ('Williams', 'L-17');

    


INSERT INTO account (account_number, branch_name, balance)
    VALUES ('A-101', 'Downtown', 500);
INSERT INTO account (account_number, branch_name, balance)
    VALUES ('A-102', 'Perryridge', 400);
INSERT INTO account (account_number, branch_name, balance)
    VALUES ('A-201', 'Brighton', 900);
INSERT INTO account (account_number, branch_name, balance)
    VALUES ('A-215', 'Mianus', 700);
INSERT INTO account (account_number, branch_name, balance)
    VALUES ('A-217', 'Brighton', 750);
INSERT INTO account (account_number, branch_name, balance)
    VALUES ('A-222', 'Redwood', 700);
INSERT INTO account (account_number, branch_name, balance)
    VALUES ('A-305', 'Round Hill', 350);





INSERT INTO depositor (customer_name, account_number)
    VALUES ('Hayes', 'A-102');
INSERT INTO depositor (customer_name, account_number)
    VALUES ('Johnson', 'A-101');
INSERT INTO depositor (customer_name, account_number)
    VALUES ('Johnson', 'A-201');
INSERT INTO depositor (customer_name, account_number)
    VALUES ('Jones', 'A-217');
INSERT INTO depositor (customer_name, account_number)
    VALUES ('Lindsay', 'A-222');
INSERT INTO depositor (customer_name, account_number)
    VALUES ('Smith', 'A-215');
INSERT INTO depositor (customer_name, account_number)
    VALUES ('Turner', 'A-305');