[VTU DBMS PGM 5] Bank Database (involving the tables 'BRANCH', 'ACCOUNT', 'DEPOSITOR', 'CUSTOMER', 'LOAN', 'BORROWER')

Question:

Consider the following database for a banking enterprise 

• BRANCH(branchname: string, branchcity: string, assets: real) 
• ACCOUNT(accno: int, branchname: string, balance: real) 
• DEPOSITOR(customername: string, accno: int) 
• CUSTOMER(customername: string, customerstreet: string, customercity: string) 
• LOAN(loannumber: int, branchname: string, amount: real) 
• BORROWER(customername: string, loannumber: int)

Queries:

1. Create the above tables by properly specifying the primary keys and the  foreign keys 

CREATE TABLE BRANCH (
branch_name varchar(20), 
branch_city varchar(20), 
assets real, 
constraint bpk PRIMARY KEY(branch_name));

CREATE TABLE CUSTOMER(
customer_name varchar(20), 
customer_street varchar(20), 
customer_city varchar(20), 
constraint cpk PRIMARY KEY(customer_name));

CREATE TABLE ACCOUNT (
accno int, 
branch_name varchar(20), 
balance real, 
constraint apk PRIMARY KEY(accno), 
constraint afk FOREIGN KEY(branch_name) references BRANCH(branch_name)); 

CREATE TABLE DEPOSITOR (
customer_name varchar(20), 
Accno int, 
constraint dpk PRIMARY KEY(customer_name, accno), 
constraint dfk1 FOREIGN KEY(customer_name) references CUSTOMER(customer_name), 
constraint dfk2 FOREIGN KEY(accno) references ACCOUNT(accno) On Delete Cascade);

CREATE TABLE LOAN (
loan_number int, 
branch_name varchar(20), 
amount real, 
constraint lpk PRIMARY KEY(loan_number), 
constraint lfk FOREIGN KEY(branch_name) references BRANCH(branch_name)); 

CREATE TABLE BORROWER (
customer_name varchar(20), 
Loan_number int, 
constraint bpk PRIMARY KEY(customer_name, Loan_number), constraint bfk1 FOREIGN KEY(customer_name) references CUSTOMER(customer_name), 
constraint bfk2 FOREIGN KEY(Loan_number) references LOAN(Loan_number));

2. Enter at least five tuples for each relation 

INSERT INTO BRANCH VALUES('&branch_name','&branch_city','&assets'); 

INSERT INTO CUSTOMER VALUES('&customer_name','&customer_street','&customer_city');

INSERT INTO ACCOUNT VALUES('&accno', '&branch_name', '&balance');

INSERT INTO DEPOSITOR VALUES('&customer_name', '&accno'); 

INSERT INTO LOAN VALUES('&loan_number', '&branch_name', '&amount')

INSERT INTO BORROWER VALUES('&customer_name', '&loan_number');


3. Find all the customers who have at least two accounts at the Main branch. 

SELECT D.customer_name 
FROM DEPOSITOR D, ACCOUNT A 
WHERE A.accno = D.accno AND 
A.branch_name= 'rt nagar'  
GROUP BY D.customer_name 
 HAVING COUNT(*) >= 2; 

4. Find all the customers who have an account at all the branches located in a specific city.

SELECT DISTINCT D.customer_name 
FROM DEPOSITOR D 
 WHERE NOT EXISTS (
( SELECT branch_name 
 FROM BRANCH 
 WHERE branch_city='mangaluru') 
MINUS 
(SELECT A.branch_name 
FROM ACCOUNT A, DEPOSITOR K 
WHERE A.accno = K.accno AND D.customer_name=K.customer_name)); 

5. 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 = 'mysore');

6. Generate suitable reports.

ttitle 'LOAN DETAILS'
btitle 'BANK DATABASE' 
column cname heading 'CUSTOMER NAME’ 
column lnum heading 'LOAN NUMBER' 
column bname heading 'BRANCH NAME'
column amt heading 'LOAN AMOUNT' 
break on bname skip 2 
compute count of lnum on bname 
set linesize 100 
set pagesize 50 

select c.cname, b.lnum, l.bname, l.amt 
from customers c, loan l, borrower b 
where c.cname=b.cname and 
l.lnum=b.lnum 
order by l.bname;

btitle off 
ttitle off 
clear breaks 
clear columns 

Author : Unknown

He is a simple passionate tech freak who himself is an engineering student at Canara Engineering college. He likes App Development, Web designing, Blogging, Youtubing, Debugging and also is a CodeGeek!

Sharing is sexy!

Related Articles