Notes

create database BANK;

create table BANK.branch(
branch_name varchar(40) unique,
branch_city varchar(40) not null,
assets int(10),
constraint c_1 Primary key (branch_name)
);

create table BANK.customer(
customer_name varchar(40) not null,
customer_street varchar(40),
customer_city varchar(40) not null,
constraint c_2 Primary key (customer_name)
);

create table BANK.loan(
loan_number int(5),
branch_name varchar(40) unique,
amount int(10),
constraint c_3 Primary key (loan_number)
#Foreign key (branch_name) references BANK.branch(branch_name)
);

create table BANK.borrower(
customer_name varchar(40),
loan_number int(5),
constraint c_1000 Primary key (customer_name, loan_number) # it is composite key
#Foreign key (customer_name) references BANK.customer(customer_name),
#Foreign key (loan_number) references BANK.loan(loan_number)
);

create table BANK.account(
account_number int(5) not null,
branch_name varchar(40) unique,
balance int(10),
constraint c_4 Primary key (account_number)
#Foreign key (branch_name) references BANK.branch(branch_name)
);

create table BANK.depositor(
customer_name varchar(40),
account_number int(5)
#Foreign key (customer_name) references BANK.customer(customer_name),
#Foreign key (account_number) references BANK.branch(account_number)
);

INSERT INTO BANK.branch(branch_name, branch_city, assets) values
('HBL Nust H-12 Islamabad', 'Islamabad', 10000), # updated branch_city
('HBL Gulshan Area', 'Karachi', 5000);

INSERT INTO BANK.customer(customer_name, customer_street, customer_city) values
('Abdul Basit Ibrahim', 'Attar 1 Hostel Nust H-12 Islamabad','Haripur'), #updated customer_city
('Cristiano Ronaldo', 'Street 2 House # 7', 'Turin');

INSERT INTO BANK.loan(loan_number, branch_name, amount) values
(10 , 'HBL Nust H-12 Islamabad',2000), #updated amount
(7, 'HBL Turin', 2500);

INSERT INTO BANK.borrower(customer_name, loan_number) values
('Abdul Basit Ibrahim', 10),
('Cristiano Ronaldo', 7);

INSERT INTO BANK.account(account_number, branch_name, balance) values
(10, 'HBL Nust H-12 Islamabad', 500000 ),
(7, 'HBL Turin', 100000);

INSERT INTO BANK.depositor(customer_name, account_number) values
('Abdul Basit Ibrahim', 10),
('Cristiano Ronaldo', 7);

Update BANK.branch
set branch_city = 'Rawalpindi'
where branch_city = 'Islamabad';

Update BANK.customer
set customer_city = 'Mardan'
where customer_city = 'Haripur';

Update BANK.loan
set amount = 9999
where amount = 20000;

delete from BANK.loan
where loan_number = 10;


#TASK 3 a.

delete from BANK.loan;

Alter table BANK.loan
Add constraint c_5
Foreign key (branch_name) references BANK.branch(branch_name);

delete from BANK.borrower;

Alter table BANK.borrower
Add constraint c_6
Foreign key (customer_name) references BANK.customer(customer_name);

Alter table BANK.borrower
Add constraint c_7
Foreign key (loan_number) references BANK.loan(loan_number);

delete from BANK.account;

Alter table BANK.account
Add constraint c_8
Foreign key (branch_name) references BANK.branch(branch_name);

delete from BANK.depositor;

Alter table BANK.depositor
Add constraint c_9
Foreign key (customer_name) references BANK.customer(customer_name);

Alter table BANK.depositor
Add constraint c_10
Foreign key (account_number) references BANK.account(account_number);

# TASK 4 b.

Alter table BANK.depositor
add column deposit_date int(10);