Transcript Bank Schema

Bank Schema

create table Account
(AccNo
varchar(5) not null unique,
CustID
varchar(5) not null,
BranchID
varchar(5) not null,
balance
numeric
not null,
primary key(AccNo),
foreign key(CustID) references Customer(CustID),
foreign key(BranchID) references Branch(BranchID));
create table Branch
(BranchID
varchar(5) not null unique,
BranchName
varchar(15) not null,
BranchCity
varchar(15) not null,
Assets
numeric
,
primary key(BranchID));
create table Customer
(CustID
varchar(5)
CName
varchar(15)
Cstreet
varchar(15)
Ccity
varchar(15)
CBDate
Date,
primary key(customer_name));
not null unique,
not null,
,
,
create table Loan
(CustID
varchar(5) not null,
BranchID
varchar(5) not null,
amount
numeric
not null,
primary key(CustID, BranchID),
foreign key(CustID) references Customer(CustID),
foreign key(BranchID) references Branch(BranchID));
Queries






Find name of customer who
have account in “BCA”
Find tuple of customer that live
in “Bogor”
Find name of customer who
have loan greater than his/her
balance
Find the total balance of
customer who live in “Jakarta”
Who has the minimal loan ?
Who has the maximal balance ?