Exercise_4.md

Exercise-IV

Consider the Banking database – CUSTOMER, BRANCH, ACCOUNT and TRANSACTION. An account can be a savings account or a current account. Customer can have both types of accounts. The transactions can be a deposit or a withdrawal.

a) Mention the constrainst neatly.

CREATE TABLE branch (
  bno number(10) primary key,
  add varchar(30)
);
  
CREATE TABLE account (
  accno number(10) primary key,
  balance number(10,2),
  type varchar(30)
);

CREATE TABLE customer (
  custno number(10),
  name varchar(30),
  phone number(10),
  bno number(10) references branch(bno) on delete cascade,
  accno number(4) references account(accno) on delete cascade,
  primary key(custno,bno,accno)
);

CREATE TABLE transaction (
  accno number(4) references account(accno) on delete cascade,
  amount number(10,2),
  transtype varchar(30),
  transid number(10) primary key
);

INSERT ALL
INTO branch VALUES (01, 'Bengaluru')
INTO branch VALUES (02, 'Kolkata')
INTO branch VALUES (03, 'Chennai')
INTO branch VALUES (04, 'Mumbai')
INTO branch VALUES (05, 'Delhi')
SELECT * FROM DUAL;

INSERT ALL 
INTO customer VALUES (101, 'Raj', 1234567890, 01, 56901)
INTO customer VALUES (101, 'Raj', 1234567890, 01, 66901)
INTO customer VALUES (102, 'Kumar', 0987654321, 02, 56902)
INTO customer VALUES (103, 'Kushal', 1222333444, 02, 56903)
INTO customer VALUES (104, 'Manoj', 1123567890, 03, 56904)
INTO customer VALUES (105, 'Ram', 1234567891, 04, 56905)
INTO customer VALUES (106, 'Kunal', 1234565890, 05, 56906)
INTO customer VALUES (106, 'Kunal', 1234565890, 05, 66906)
SELECT * FROM DUAL;

INSERT ALL
INTO account VALUES (56901,10000,'Savings')
INTO account VALUES (66901,15000,'Current')
INTO account VALUES (56902,12000,'Savings')
INTO account VALUES (56903,13000,'Savings')
INTO account VALUES (56904,14000,'Savings')
INTO account VALUES (56905,10600,'Savings')
INTO account VALUES (56906,70700,'Savings')
INTO account VALUES (66906,170000,'Current')
SELECT * FROM DUAL;

INSERT ALL
INTO transaction VALUES (56901,5000,'Deposit',30001)
INTO transaction VALUES (56901,3000,'Withdraw',30002)
INTO transaction VALUES (56901,2000,'Deposit',30003)
INTO transaction VALUES (56902,33000,'Withdraw',30004)
INTO transaction VALUES (56903,5400,'Deposit',30005)
INTO transaction VALUES (56904,54000,'Deposit',30006)
INTO transaction VALUES (56904,5000,'Withdraw',30007)
INTO transaction VALUES (56905,6600,'Withdraw',30008)
SELECT * FROM DUAL;

b) Design the ER diagram for the problem statement.

c) State the schema diagram for the ER diagram.

d) Create the tables, insert suitable tuples (min 6 each) and perform the following operations in SQL

1. Obtain the details of customers who have both Savings and Current Account.

SELECT custno, name, phone
FROM customer
JOIN account
ON customer.accno = account.accno
WHERE type = 'Savings'
INTERSECT 
SELECT custno, name, phone
FROM customer
JOIN account
ON customer.accno = account.accno
WHERE type = 'Current'

2. . Retrieve the details of branches and the number of accounts in each branch.

SELECT branch.bno, address, COUNT(customer.accno) as 'No. of Accounts'
FROM branch, customer
WHERE branch.bno = customer.bno
GROUP BY customer.bno;

3. Obtain the details of customers who have performed at least 3 transactions.

SELECT custno,name,phone, COUNT(transactions.transid) as 'No. of Transactions'
FROM customer, transactions
WHERE transactions.accno = customer.accno
GROUP BY transactions.accno
HAVING count(transactions.transid) >= 3;

4. List the details of branches where the number of accounts is less than the average number of accounts in all branches.

SELECT branch.bno, address, COUNT(customer.accno) as 'No. of Accounts'
FROM branch,customer
WHERE branch.bno = customer.bno
GROUP BY branch.bno
HAVING COUNT(customer.accno) <= (SELECT (COUNT(account.accno) / COUNT(branch.bno))
                               FROM account,branch);

e) Create the table, insert suitable tuples and perform the following operations using MongoDB

db.createCollection('Bank')
db.Bank.insertMany([
    {'CustId': 101, 'CustName': 'Raj', 'AccNo' : 56901, 'Balance' :12000, 'BranchNo': 01, 'AccountType':'Savings', 'Bname' : 'ABC'},
    {'CustId': 101, 'CustName': 'Raj', 'AccNo' : 66901, 'Balance' :12000, 'BranchNo': 01, 'AccountType':'Current', 'Bname' : 'ABC'},
    {'CustId': 102, 'CustName': 'Kumar', 'AccNo' : 56902, 'Balance' :112000, 'BranchNo': 01, 'AccountType':'Savings', 'Bname' : 'ABC'},
    {'CustId': 103, 'CustName': 'Kushal', 'AccNo' : 56903, 'Balance' :1000, 'BranchNo': 01, 'AccountType':'Savings', 'Bname' : 'ABC'},
    {'CustId': 103, 'CustName': 'Kushal', 'AccNo' : 66903, 'Balance' :22000, 'BranchNo': 02, 'AccountType':'Current', 'Bname' : 'XYZ'}
    ])

1. Find the branch name for a given Branch_ID.

db.Bank.findOne({'BranchNo' : 01}, {'Bname' : 1, '_id' : 0})

2. List the total number of accounts for each customer.

db.Bank.aggregate([{
        $group : {_id : { 'Customer ID' : '$CustId'},
            Number_of_Accounts : {$sum : 1}}
    }])

f)Using cursors demonstrate the process of copying the contents of one table to a new table.

1. Create BranchCopy table with same schema as branch Note : We add an always false condition 1=2 here to avoid copying rows from the supply table. We only copy the schema.

CREATE TABLE BranchCopy AS 
(SELECT * 
FROM branch 
WHERE 1=2)

2. Create the PL/SQL file - branch.sql using the command edit branch.sql

DECLARE
	cursor c1 is select * from branch;
    v_rec branch%rowtype;
BEGIN
    open c1;
    loop
    fetch c1 into vrec;
    exit when c1%notfound;
    insert into BranchCopy values (v_rec.bno, v_rec.address);
    end loop;
    close c1;
END;
/

3. Commands to execute the PL/SQL file -

set serveroutput on;
@branch.sql
plsql execution completed successfully
SELECT *
FROM BranchCopy;