DATABASE MANAGEMENT SYSTEMS LABORATORY 10CSL57 VTU prg 5

The Eduladder is a community of students, teachers, and programmers just interested to make you pass any exams. So we solve previous year question papers for you.
See Our team
Wondering how we keep quality?
Got unsolved questions?

Ask Questions


You are here:Open notes-->VTU-->DATABASE-MANAGEMENT-SYSTEMS-LABORATORY-10CSL57-VTU-prg-5

DATABASE MANAGEMENT SYSTEMS LABORATORY [10CSL57] VTU prg-5

5. 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
ii. Enter at least five tuples for each relation
iii. Find all the customers who have at least two accounts at the Main branch.
iv. Find all the customers who have an account at all the branches located in a specific city.
v. Demonstrate how you delete all account tuples at every branch located in a specific city.
vi. Generate suitable reports.
vii. Create suitable front end for querying and displaying the results.

5. 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 BBRANCH
(BRANCHNAME VARCHAR(30) PRIMARY KEY,
BRANCHCITY VARCHAR(30) NOT NULL,
ASSETS NUMBER(10,2) NOT NULL);
CREATE TABLE BBANKACCOUNT
(ACCNO NUMBER(5) PRIMARY KEY,
BRANCHNAME VARCHAR(30) NOT NULL,
BALANCE NUMBER(10,2),
FOREIGN KEY (BRANCHNAME) REFERENCES BBRANCH (BRANCHNAME));
CREATE TABLE BBANKCUSTOMER
(CUSTOMERNAME VARCHAR(30) PRIMARY KEY,
CUSTOMERSTREET VARCHAR(30) NOT NULL,
CUSTOMERCITY VARCHAR(30) NOT NULL);
CREATE TABLE DDEPOSITOR
(CUSTOMERNAME VARCHAR(30) NOT NULL,
ACCNO NUMBER(5) NOT NULL,
PRIMARY KEY (CUSTOMERNAME, ACCNO),
FOREIGN KEY (CUSTOMERNAME) REFERENCES BBANKCUSTOMER (CUSTOMERNAME),
FOREIGN KEY (ACCNO) REFERENCES BBANKACCOUNT (ACCNO) ON DELETE CASCADE);
CREATE TABLE LLOAN
(LOANNUMBER INTEGER PRIMARY KEY,
BRANCHNAME VARCHAR(30) NOT NULL,
AMOUNT NUMBER(10,2) NOT NULL,
FOREIGN KEY (BRANCHNAME) REFERENCES BBRANCH (BRANCHNAME));
CREATE TABLE BBORROWER
(CUSTOMERNAME VARCHAR(30) NOT NULL,
LOANNUMBER INTEGER NOT NULL,
PRIMARY KEY (CUSTOMERNAME, LOANNUMBER),
FOREIGN KEY (CUSTOMERNAME) REFERENCES BBANKCUSTOMER (CUSTOMERNAME),
FOREIGN KEY (LOANNUMBER) REFERENCES LLOAN (LOANNUMBER));

ii. Enter at least five tuples for each relation

INSERT INTO BBRANCH VALUES('CHAMRAJPET','BANGALORE',50000);
INSERT INTO BBRANCH VALUES('RESIDENCY ROAD','BANGALORE',10000);
INSERT INTO BBRANCH VALUES('M G ROAD','BANGALORE',100000);
INSERT INTO BBRANCH VALUES('CP','DELHI',100000);
INSERT INTO BBRANCH VALUES('JANTARMANTAR','DELHI',100000);
INSERT INTO BBANKACCOUNT VALUES(1,'CHAMRAJPET',2000);
INSERT INTO BBANKACCOUNT VALUES(2,'RESIDENCY ROAD',5000);
INSERT INTO BBANKACCOUNT VALUES(3,'M G ROAD',6000);
INSERT INTO BBANKACCOUNT VALUES(4,'CP',9999);
INSERT INTO BBANKACCOUNT VALUES(5,'JANTARMANTAR',999);
INSERT INTO BBANKACCOUNT VALUES(6,'M G ROAD',999);
INSERT INTO BBANKACCOUNT VALUES(8,'RESIDENCY ROAD',999);
INSERT INTO BBANKACCOUNT VALUES(9,'CP',10000);
INSERT INTO BBANKACCOUNT VALUES(10,'RESIDENCY ROAD',5000);
INSERT INTO BBANKACCOUNT VALUES(11,'JANTARMANTAR',9999);
INSERT INTO BBANKCUSTOMER VALUES('ANNE','BULL TEMPLE ROAD','BANGALORE');
INSERT INTO BBANKCUSTOMER VALUES('DANNY','BANNERGATTA ROAD','BANGALORE');
INSERT INTO BBANKCUSTOMER VALUES('TOM','J C ROAD','BANGALORE');
INSERT INTO BBANKCUSTOMER VALUES('NICK','CP','DELHI');
INSERT INTO BBANKCUSTOMER VALUES('ROVER','JANTARMANTAR','DELHI');
INSERT INTO DDEPOSITOR VALUES('ANNE',1);
INSERT INTO DDEPOSITOR VALUES('DANNY',2);
INSERT INTO DDEPOSITOR VALUES('TOM',3);
INSERT INTO DDEPOSITOR VALUES('NICK',4);
INSERT INTO DDEPOSITOR VALUES('ROVER',5);
INSERT INTO DDEPOSITOR VALUES('ANNE',6);
INSERT INTO DDEPOSITOR VALUES('ANNE',8);
INSERT INTO DDEPOSITOR VALUES('NICK',9);
INSERT INTO DDEPOSITOR VALUES('DANNY',10);
INSERT INTO DDEPOSITOR VALUES('NICK',11);
INSERT INTO LLOAN VALUES(1,'CHAMRAJPET',1000);
INSERT INTO LLOAN VALUES(2,'RESIDENCY ROAD',2000);
INSERT INTO LLOAN VALUES(3,'M G ROAD',3000);
INSERT INTO LLOAN VALUES(4,'CP',4000);
INSERT INTO LLOAN VALUES(5,'JANTARMANTAR',5000);
INSERT INTO BBORROWER VALUES('ANNE',1);
INSERT INTO BBORROWER VALUES('ANNE',2);
INSERT INTO BBORROWER VALUES('TOM',3);
INSERT INTO BBORROWER VALUES('NICK',4);
INSERT INTO BBORROWER VALUES('ROVER',5);

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

SELECT *
FROM BBANKCUSTOMER C
WHERE EXISTS
(SELECT DP.CUSTOMERNAME, COUNT (DP.CUSTOMERNAME)
FROM DDEPOSITOR DP,BBANKACCOUNT BA
WHERE DP.ACCNO=BA.ACCNO AND C.CUSTOMERNAME=DP.CUSTOMERNAME AND BA.BRANCHNAME='RESIDENCY ROAD'
GROUP BY DP.CUSTOMERNAME
HAVING COUNT(DP.CUSTOMERNAME)>=2);

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

SELECT *
FROM BBANKCUSTOMER BC
WHERE NOT EXISTS
(SELECT BRANCHNAME
FROM BBRANCH
WHERE BRANCHCITY='DELHI'
MINUS
SELECT BA.BRANCHNAME
FROM DDEPOSITOR D,BBANKACCOUNT BA
WHERE D.ACCNO=BA.ACCNO AND BC.CUSTOMERNAME=D.CUSTOMERNAME );

v. Demonstrate how you delete all account tuples at every branch located in a specific city.
DELETE FROM BBANKACCOUNT
WHERE BRANCHNAME IN
(SELECT BRANCHNAME
FROM BBRANCH
WHERE BRANCHCITY='BANGALORE');
vi. Generate suitable reports.
vii. Create suitable front end for querying and displaying the results.




Editors




Join eduladder!