Thursday, April 4, 2019
Wednesday, April 3, 2019
DBMS 1 - LIBRARY DATABASE
1 - Consider the following schema for a Library Database: BOOK(Book_id, Title, Publisher_Name, Pub_Year) BOOK_AUTHORS(Book_id, Author_Name) PUBLISHER(Name, Address, Phone) BOOK_COPIES(Book_id, Branch_id, No-of_Copies) BOOK_LENDING(Book_id, Branch_id, Card_No, Date_Out, Due_Date) LIBRARY_BRANCH(Branch_id, Branch_Name, Address)
Write SQL queries to
- Retrieve details of all books in the library – id, title, name of publisher, authors, number of copies in each branch, etc.
- Get the particulars of borrowers who have borrowed more than 3 books, but from Jan 2017 to Jun 2017.
- Delete a book in BOOK table. Update the contents of other tables to reflect this data manipulation operation.
- Partition the BOOK table based on year of publication. Demonstrate its working with a simple query.
- Create a view of all books and its number of copies that are currently available in the Library.
ENTITY RELATIONSHIP DIAGRAM
CREATION OF TABLES
-- 1
CREATE TABLE PUBLISHER
(NAME VARCHAR(20) PRIMARY KEY,
PHONE INTEGER,
ADDRESS VARCHAR(20));
DESC PUBLISHER;
-- 2
CREATE TABLE BOOK
(BID INTEGER PRIMARY KEY,
TITLE VARCHAR(20),
PUBYEAR VARCHAR(20),
PUBNAME REFERENCES PUBLISHER(NAME) ON DELETE CASCADE);
DESC BOOK;
-- 3
CREATE TABLE BOOK_AUTHORS
(ANAME VARCHAR(20),
BID REFERENCES BOOK(BID) ON DELETE CASCADE,
PRIMARY KEY(BID, ANAME));
DESC BOOK_AUTHORS;
-- 4
CREATE TABLE LIBRARY_BRANCH
(BID INTEGER PRIMARY KEY,
BNAME VARCHAR(50),
ADDRESS VARCHAR(50));
DESC LIBRARY_BRANCH;
-- 5
CREATE TABLE BOOK_COPIES
(NOOFCOPIES INTEGER,
BID REFERENCES BOOK(BID) ON DELETE CASCADE,
BRANCHID REFERENCES LIBRARY_BRANCH(BRANCHID) ON DELETE CASCADE,
PRIMARY KEY (BID,BRANCHID));
DESC BOOK_COPIES;
-- 6
CREATE TABLE CARD
(CARDNO INTEGER PRIMARY KEY);
DESC CARD;
-- 7
CREATE TABLE BOOKLENDING
(DATEOUT DATE,
DUEDATE DATE,
BID REFERENCES BOOK(BID) ON DELETE CASCADE,
BRANCHID REFERENCES LIBRARY_BRANCH(BRANCHID) ON DELETE CASCADE,
CARDNO REFERENCES CARD(CARDNO) ON DELETE CASCADE,
PRIMARY KEY (BID,BRANCHID,CARDNO));
DESC BOOKLENDING;
INSERTION OF TABLES
-- 1
INSERT INTO PUBLISHER VALUES('MCGRAW-HILL',9191919191,'BANGALORE');
INSERT INTO PUBLISHER VALUES('PEARSON',8181818181,'NEWDELHI');
INSERT INTO PUBLISHER VALUES('RANDOM HOUSE',7171717171,'HYDERABAD');
INSERT INTO PUBLISHER VALUES('LIVRE',6161616161,'CHENNAI');
INSERT INTO PUBLISHER VALUES('PLANETA',5151515151,'BANGALORE');
SELECT * FROM PUBLISHER;
-- 2
INSERT INTO BOOK VALUES(1,'DBMS','JAN-2017','MCGRAW-HILL');
INSERT INTO BOOK VALUES(2,'ADBMS','JUN-2016','MCGRAW-HILL');
INSERT INTO BOOK VALUES(3,'CN','SEP-2016','PEARSON');
INSERT INTO BOOK VALUES(4,'CG','SEP-2015','PLANETA');
INSERT INTO BOOK VALUES(5,'OS','MAY-2016','PEARSON');
SELECT * FROM BOOK;
-- 3
INSERT INTO BOOK_AUTHORS VALUES('NAVATHE',1);
INSERT INTO BOOK_AUTHORS VALUES('NAVATHE',2);
INSERT INTO BOOK_AUTHORS VALUES('TANENBAUM',3);
INSERT INTO BOOK_AUTHORS VALUES('EDWARD ANGEL',4);
INSERT INTO BOOK_AUTHORS VALUES('GALVIN',5);
SELECT * FROM BOOK_AUTHORS;
-- 4
INSERT INTO LIBRARY_BRANCH VALUES(10,'RR NAGAR','BANGALORE');
INSERT INTO LIBRARY_BRANCH VALUES(11,'RNSIT','BANGALORE');
INSERT INTO LIBRARY_BRANCH VALUES(12,'RAJAJI NAGAR','BANGALORE');
INSERT INTO LIBRARY_BRANCH VALUES(13,'NITTE','MANGALORE');
INSERT INTO LIBRARY_BRANCH VALUES(14,'MANIPAL','UDUPI');
SELECT * FROM LIBRARY_BRANCH;
-- 5
INSERT INTO BOOK_COPIES VALUES(10,1,10);
INSERT INTO BOOK_COPIES VALUES(5,1,11);
INSERT INTO BOOK_COPIES VALUES(2,2,12);
INSERT INTO BOOK_COPIES VALUES(5,2,13);
INSERT INTO BOOK_COPIES VALUES(7,3,14);
INSERT INTO BOOK_COPIES VALUES(1,5,10);
INSERT INTO BOOK_COPIES VALUES(3,4,11);
SELECT * FROM BOOK_COPIES;
-- 6
INSERT INTO CARD VALUES(100);
INSERT INTO CARD VALUES(101);
INSERT INTO CARD VALUES(102);
INSERT INTO CARD VALUES(103);
INSERT INTO CARD VALUES(104);
SELECT * FROM CARD;
-- 7
INSERT INTO BOOKLENDING VALUES('01-JAN-17','01-JUN-17',1,10,101);
INSERT INTO BOOKLENDING VALUES('11-JAN-17','11-MAR-17',3,14,101);
INSERT INTO BOOKLENDING VALUES('21-FEB-17','21-APR-17',2,13,101);
INSERT INTO BOOKLENDING VALUES('15-MAR-17','15-JUL-17',4,11,101);
INSERT INTO BOOKLENDING VALUES('12-APR-17','12-MAY-17',1,11,104);
SELECT * FROM BOOKLENDING;
--QUERIES
-- QUERY 1
SELECT B.BID, B.TITLE, B.PUBNAME, A.ANAME,C.NOOFCOPIES,L.BRANCHID
FROM BOOK B, BOOK_AUTHORS A, BOOK_COPIES C, LIBRARY_BRANCH LIBRARY_BRANCH LIBRARY_BRANCH L
WHERE B.BID=A.BID
AND B.BID=C.BID
AND L.BRANCHID=C.BRANCHID;
SELECT CARDNO
FROM BOOKLENDING
WHERE DATEOUT BETWEEN '01-JAN-17' AND '01-JUN-17'
GROUP BY CARDNO
HAVING COUNT(*)>3;
-- QUERY 3
DELETE FROM BOOK
WHERE BID = 3;
-- QUERY 4
CREATE VIEW VPUBLICATION AS
SELECT PUBYEAR
FROM BOOK;
-- QUERY 5
CREATE VIEW VBOOKS AS
SELECTB.BID, B.TITLE, C.NOOFCOPIES
FROM
BOOK B, BOOK_COPIES C, LIBRARY_BRANCH L
WHEREB.BID=C.BID
AND C.BRANCHID=L.BRANCHID;
Monday, March 4, 2019
Sunday, March 3, 2019
CN 1 - POINT TO POINT NETWORK WITH DUPLEX LINKS
1. Implement three nodes point – to – point network with duplex links between them. Set the queue size, vary the bandwidth and find the number of packets dropped.
cn1.tcl - PROGRAM
# Simulation parameters setup
set val(stop) 6.0 ; # stopping time of the simulation
# Initialization
#Create a ns simulator
set ns [new Simulator]
#Open the NS trace file
set tracefile [open 1.tr w]
$ns trace-all $tracefile
#Open the NAM trace file
set namfile [open 1.nam w]
$ns namtrace-all $namfile
# Nodes Definition
#Create 3 nodes
set n1 [$ns node]
set n2 [$ns node]
set n3 [$ns node]
# Links Definition
#Createlinks between nodes
$ns duplex-link $n1 $n2 1000Kb 60ms DropTail
$ns queue-limit $n1 $n2 14
$ns duplex-link $n2 $n3 500Kb 60ms DropTail
$ns queue-limit $n2 $n3 4
$ns duplex-link-op $n1 $n2 queuePos 0.5
$ns duplex-link-op $n2 $n3 queuePos 0.2
# Agents Definition
#Setup a TCP connection
set tcp0 [new Agent/TCP]
$ns attach-agent $n1 $tcp0
set sink1 [new Agent/TCPSink]
$ns attach-agent $n3 $sink1
$ns connect $tcp0 $sink1
$tcp0 set packetSize_ 1500
# Applications Definition
#Setup a FTP Application over TCP connection
set ftp0 [new Application/FTP]
$ftp0 attach-agent $tcp0
$ns at 0.2 "$ftp0 start"
$ns at 5.0 "$ftp0 stop"
# Termination
#Define a 'finish' procedure
proc finish {} {
global ns tracefile namfile
$ns flush-trace
close $tracefile
close $namfile
exec nam 1.nam &
exit 0
}
$ns at $val(stop) "$ns nam-end-wireless $val(stop)"
$ns at $val(stop) "finish"
$ns at $val(stop) "puts \"done\" ; $ns halt"
$ns run
cn1.awk - PROGRAM
BEGIN {
count=0;
total=0;
}
{
event=$1;
if(event=="d") {
count++;
}
}
END {
printf("No of packets dropped : %d\n",count);
}
STEPS :
( click on image to zoom )
OUTPUT :
( click on image to zoom )
Subscribe to:
Posts (Atom)