We are building EduLadder(ELADR) - Protocol

The Eladr Protocol is a decentralized, security and efficiency enhanced Web3 noSQL database powered by IPFS as the data storage layer https://ipfs.io/, and the Cardano block chain as the rewards token platform, https://cardano.org/. It provides a JSON based, IPFS layer 2 solution for data indexing and retrieval in an 'append only' file system built with open source Node.js API libraries.

Eladr tokens are designed to incentifised community members as a proof of contribution. Using that they can access diffrent infrastructure built on top of eladr


Using this You can,Buy courses,Reward others and exchange for real money.


WHITE PAPER Buy Now

Real Problems! Real Experts!

Join Our Telegram Channel !


The Eduladder is a community of students, teachers, and programmers. We help you to solve your academic and programming questions fast.
In eduladder you can Ask,Answer,Listen,Earn and Download Questions and Question papers.
Watch related videos of your favorite subject.
Connect with students from different parts of the world.
Apply or Post Jobs, Courses ,Internships and Volunteering opportunity. For FREE
See Our team
Wondering how we keep quality?
Got unsolved questions? Ask Questions
ELADR beta version launched

We launched Anonymous immutable internet on eladr protocol

For any question or query please joinOur Telegram Channel !


Try BETA
Youtube Videohttps://www.youtube.com/watch?v=ySLPZu3Jxro

Our Github Repo
FrontEnd BackEnd

We are looking for some great and cool people to work with us. Please sent your resume to admin@eduladder.com


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




You might like this video:Watch more here

Watch more videos from this user Here

Learn how to upload a video over here