DBMS Lab 3 [My SQL]

Exercise:  3

A  bank  has  many  branches  and  a  large  number  of  customers.  Bank  is  identified  by  its  code. Other  details  like  name,  address  and  phone  for  each bank  are  also  stored.  Each  branch  is identified  by  its  bank.  Branch  has  name,  address  and  phone.  A  customer  can  open  different kinds  of  accounts  with  the  branches.  An  account  can belong  to  more  than  one  customer.Customers  are  identified  by  their  SSN,  name,  address  and  phone  number.  Age  is  used  as  a factor to check whether customer is a major. There  are different types of loans, each identified by a loan number. A customer can take more than onetype of loan and a loan can be given to more than one customer.  Loans have a duration  and interest rate. Make  suitable assumptions and use them in showing maximum and minimum cardinality ratios.

ER DIAGRAM:

Relational schema:

Tables

mysql> create table bank(code varchar(10) primary key,name varchar(20),address varchar(20),phone int(11));

Query OK, 0 rows affected (0.09 sec)

mysql> select * from bank;

mysql> create table branch(id int primary key,name varchar(10),address varchar(20),phone int(12),b_code varchar(10), foreign key(b_code) references bank(code));

Query OK, 0 rows affected (0.08 sec)

mysql> select * from branch;

mysql> create table account(ac_no int primary key,ac_type varchar(10), balance int, bid int, foreign key(bid) references branch(id));

Query OK, 0 rows affected (0.09 sec)

mysql> select * from account;

mysql> create table customers(ssn int primary key,name varchar(20), address varchar(20),phone int,age int);

Query OK, 0 rows affected (0.09 sec)

mysql> select * from customers;

           

mysql> create table cust_ac(acc_no int, ssn int, foreign key(acc_no) references account(ac_no), foreign key(ssn) references customers(ssn));

Query OK, 0 rows affected (0.14 sec)

mysql> select * from cust_ac;

create table loan(lno int primary key,duration int,interest float,amount int,bid int(11),foreign key(bid) references branch(id));

Query OK, 0 rows affected (0.13 sec)

mysql> select * from loan;

mysql> create table cust_loan(lno int,ssn int, foreign key(lno) references loan(lno), foreign key(ssn) references customers(ssn));

Query OK, 0 rows affected (0.13 sec)

mysql> select * from cust_loan;

                       

Queries:

a)      List the details of customers who have joint account and also have at least one loan.

mysql> select c.* from customers c,account a,cust_loan cl,cust_ac ca where cl.ssn=c.ssn and c.ssn=ca.ssn and a.ac_type="joint" and ca.acc_no=a.ac_no;

b)      List the details of the branch which has given maximum loan.

mysql> select * from branch where id=(select bid from loan where amount=(select max(amount) from loan));

c)      List the details of saving accounts opened in the SBI branches located at Bangalore.

mysql> select * from account where ac_type="sb" and bid=(select id from branch where address like "%bangalore" and b_code=(select code from bank where name="sbi"));.

d)     List the name of branch along with its bank name and total amount of loan given by it.

mysql> select b.name,br.name,sum(l.amount) from bank b,branch br,loan l where br.b_code=b.code and l.bid=br.id group by l.bid;

e) Retrieve the names of customers who have accounts in all the branches located in a specific city.

mysql> select c.name from customers c,cust_ac ac,account a,branch br where c.ssn=ac.ssn and a.ac_no=ac.acc_no and a.bid=br.id and br.address="bangalore";

Posted in: II_DBMS_lab