4.
Consider the following database for a banking enterprise
BRANCH (branch-name:string, branch-city:string,
assets:real)
ACCOUNT (accno:int, branch-name:string, balance:real)
DEPOSITOR (customer-name:string, accno:int)
CUSTOMER (customer-name:string, customer-street:string,
customer-city:string)
LOAN (loan-number:int, branch-name:string, amount:real)
BORROWER (customer-name:string, loan-number:int)
i. Create the above tables by properly specifying the primary
keys and the foreign keys.
create table branch(
branch_name varchar(20) primary key,
branch_city varchar(20),
assets real);
create table account(
accno int primary key,
branch_name varchar(20),
balance real,
FOREIGN KEY(branch_name) REFERENCES branch(branch_name));
create table loan(
loan_number int primary key,
branch_name varchar(20),
amount real,
FOREIGN KEY(branch_name) REFERENCES branch(branch_name));
create table customer(
cust_name varchar(20) primary key,
cust_street varchar(20),
cust_city varchar(20));
create table depositor(
cust_name varchar(20),
accno int,
FOREIGN KEY (cust_name) REFERENCES customer (cust_name),
FOREIGN KEY (accno) REFERENCES account (accno));
create table borrower(
cust_name varchar(20),
loan_number int,
foreign key(cust_name) references customer (cust_name),
foreign key(loan_number) references loan (loan_number));
ii. Enter at least five tuples for each relation
insert into branch values
('basavangudi', 'BANGALORE', 50000);
1 row created.
insert into branch values
('RESIDENCY ROAD', 'BANGALORE', 10000);
1 row created.
insert into branch values
('somwarpet', 'kodagu', 100000);
1 row created.
SQL> insert into branch values
('MGROAD', 'kushalnagar', 100000);
1 row created.
SQL> insert into branch values
('shrinagar', 'Delhi', 100000);
insert into account values
(1,'basavangudi',2000)
1 row created.
SQL> insert into account values
(2,'somwarpet',5000);
1 row created.
insert into account values
(3,'MGROAD',6000);
SQL> insert into account values
(4,'RESIDENCY ROAD',9999);
1 row created.
SQL> insert into account values
(5,'shrinagar',999);
1 row created.
SQL> insert into account values
(7,'MGROAD',10000);
1 row created.
SQL> insert into account values
(8,'shrinagar',5000);
1 row created.
insert into customer values
('sachin','Bull temple road','banglore');
1 row created.
SQL> insert into customer values
('vilas','Bannergatta road','banglore');
1 row created.
SQL> insert into customer values
('advith','N R colony','banglore');
1 row created.
SQL> insert into customer values
('ashik','cp','Delhi');
1 row created.
SQL> insert into customer values
('yathish','jk','Delhi');
1 row created.
SQL> insert into depositor values
('sachin',1);
1 row created.
SQL> insert into depositor values
('vilas',2);
1 row created.
SQL> insert into depositor values
('advith',4);
1 row created.
SQL> insert into depositor values
('ashik',8);
1 row created.
SQL> insert into depositor values
('yathish',5);
1 row created.
SQL> insert into depositor values
('yathish',3);
1 row created.
insert into depositor values
('vilas',2);
insert into loan values
(1,'basavangudi',1000);
1 row created.
SQL> insert into loan values
(2,'shrinagar',2000);
1 row created.
SQL> insert into loan values
(3,'somwarpet',3000);
1 row created.
SQL> insert into loan values
(4,'MGROAD',4000);
1 row created.
SQL> insert into loan values
(5,'RESIDENCY
ROAD',5000);
1 row created.
insert into borrower values
('vilas',1);
1 row created.
SQL> insert into borrower values
('yathish',2);
1 row created.
SQL> insert into borrower values
('ashik',3);
1 row created.
SQL> insert into borrower values
('advith',4);
1 row created.
SQL> insert into borrower values
('sachin',5);
1 row created.
iii. Find all the customers who have at least two accounts at the
Main branch.
SELECT * FROM Customer C
WHERE EXISTS (
SELECT D.cust_name, COUNT(D.cust_name)
FROM Depositor D, Account A
WHERE
D.accno = A.accno AND
C.cust_name = D.cust_name AND
A.branch_name = 'somwarpet'
GROUP BY D.cust_name
HAVING COUNT(D.cust_name) >= 2);
iv. Find all the customers who have an account at all the
branches located in a specific city.
SELECT d.cust_name
FROM account a,branch b,depositor d
WHERE b.branch_name=a.branch_name AND
a.accno=d.accno AND
b.branch_city='kushalnagar'
GROUP BY d.cust_name
HAVING COUNT(distinct b.branch_name)=(
SELECT COUNT(branch_name)
FROM branch
WHERE branch_city='kushalnagar');
v. Demonstrate how you delete all account tuples at
every branch located in a specific city.
DELETE FROM Account
WHERE Branch_name IN (
SELECT Branch_name
FROM Branch
WHERE branch_city = 'BANGALORE');
vi. Generate suitable reports. vii. Create suitable front
end for querying and displaying the results.
