Database Management Systems (DBMS) Lab Program 1

CREATE TABLE person (

driverid VARCHAR(30),

name VARCHAR(30),

address VARCHAR(30),

PRIMARY KEY (driverid)

);



CREATE TABLE car (

regno VARCHAR(30),

model VARCHAR(30),

year NUMBER(4),

PRIMARY KEY (regno)

);



CREATE TABLE accident (

reportno NUMBER(10),

acccdate DATE,

location VARCHAR(30),

PRIMARY KEY (reportno)

);



CREATE TABLE owns (

driverid VARCHAR(30),

regno VARCHAR(30),

PRIMARY KEY(driverid,regno),

FOREIGN KEY(driverid) REFERENCES person(driverid),

FOREIGN KEY(regno) REFERENCES car(regno)

);



CREATE TABLE participated (

driverid VARCHAR(30),

regno VARCHAR(30),

reportno NUMBER(30),

damage NUMBER(10),

PRIMARY KEY(driverid,regno,reportno),

FOREIGN KEY(driverid) REFERENCES person(driverid),

FOREIGN KEY(regno) REFERENCES car(regno),

FOREIGN KEY(reportno) REFERENCES accident(reportno)

);



INSERT INTO person VALUES('1', 'a', 'x');

INSERT INTO person VALUES('2', 'b', 'x');

INSERT INTO person VALUES('3', 'c', 'x');

INSERT INTO person VALUES('4', 'd', 'x');

INSERT INTO person VALUES('5', 'e', 'x');

INSERT INTO person VALUES('6', 'f', 'x');



INSERT INTO car VALUES('r1', 'tata',1998);

INSERT INTO car VALUES('r2', 'uno',1998);

INSERT INTO car VALUES('r3', 'beetle',1998);

INSERT INTO car VALUES('r4', 'zen',1998);

INSERT INTO car VALUES('r5', 'maruti',1998);



INSERT INTO owns VALUES('1', 'r1');

INSERT INTO owns VALUES('2', 'r2');

INSERT INTO owns VALUES('3', 'r3');

INSERT INTO owns VALUES('4', 'r4');

INSERT INTO owns VALUES('5', 'r5');

INSERT INTO owns VALUES('1', 'r2');

INSERT INTO owns VALUES('2', 'r1');

INSERT INTO owns VALUES('3', 'r1');



INSERT INTO accident VALUES(1,'1-jan-2010','x');

INSERT INTO accident VALUES(2,'1-jan-2010','x');

INSERT INTO accident VALUES(3,'1-jan-2010','x');

INSERT INTO accident VALUES(4,'1-jan-2010','x');

INSERT INTO accident VALUES(5,'1-jan-2010','x');

INSERT INTO accident VALUES(6,'1-jan-2000','x');





INSERT INTO participated VALUES('1','r1',1,1000);

INSERT INTO participated VALUES('2','r1',1,1000);

INSERT INTO participated VALUES('3','r3',3,1000);

INSERT INTO participated VALUES('1','r2',2,1000);

INSERT INTO participated VALUES('2','r3',3,1000);

INSERT INTO participated VALUES('4','r4',4,1000);

INSERT INTO participated VALUES('6','r4',4,1000);

INSERT INTO participated VALUES('5','r3',6,1000);



DESC person;

DESC car;

DESC owns;

DESC accident;

DESC participated;



(III)

a.



UPDATE participated SET damage = 25000 WHERE regno LIKE 'r4' AND reportno=4;



b.

INSERT INTO accident VALUES(7,'1-jan-2000','x');

INSERT INTO participated VALUES('6','r5',7,1000);



(IV)



SELECT count(DISTINCT o.driverid)

FROM owns o,

(

SELECT DISTINCT p.driverid

FROM participated p,accident a

WHERE p.reportno=a.reportno AND a.acccdate LIKE '%2010%'

) X

WHERE o.driverid = X.driverid ;



(V)



SELECT count(*)

FROM car c,participated p

WHERE p.regno=c.regno AND c.model LIKE 'beetle';

Computer Science: 5th sem ADA Lab Program 12

Analysis and Design of Algorithms Lab.

Question 12.


//Find the minimum cost spanning tree of a given undirected graph using prim's algorithm.

#include <iostream>
#include <conio.h>

#define MAX 10

using namespace std;

void kruskal(int graph[MAX][MAX],int n,int source)
{
int *vis=new int[n];
for(int i=0;i<n;i++)
vis[i]=-1;

int edge=1,max=0;
vis[source]=0;

while(edge<n)
{
int min=99,u,v;

for(int i=0;i<n;i++)
{
for(int j=0;j<n;j++)
if(i!=j && graph[i][j]<min && (vis[i]==-1 || vis[j]==-1) && !(vis[i]==-1 && vis[j]==-1))
{
min=graph[i][j];
u=i;v=j;
}
}

vis[v]=u;
cout<<"\n"<<u+1<<"-"<<v+1<<"="<<min;
edge++;
max+=min;
graph[u][v]=99;
graph[v][u]=99;
}

cout<<"\nMax cost:"<<max;
}

void main()
{
cout<<"Enter the number of verticies:";
int n;cin>>n;

cout<<"Enter the source:";
int source;cin>>source;

cout<<"Enter the weighted matrix:\n";
int graph[MAX][MAX];

for(int i=0;i<n;i++)
for(int j=0;j<n;j++)
cin>>graph[i][j];

kruskal(graph,n,source);
getch();
}

Computer Science: 5th sem ADA Lab Program 11

Analysis and Design of Algorithms Lab.

Question 11.

//Implement Horsepool algorithm for string matcing
//Find the binomial Co-efficient using dynamic programing

#include<iostream>
#include <conio.h>
#include <string.h>
using namespace std;

void shiftTable(int *tab,int m,char *p,int n)
{
for(int i=0;i<m;i++)
tab[i]=n;
for(int i=0;i<n-1;i++)
{
tab[p[i]]=n-1-i;
}

}

int horsepool(char *arr,char *pattren)
{
int table[128];
int n=strlen(arr);
int m=strlen(pattren);
int k=0;
shiftTable(table,128,pattren,m);

for(int i=m-1;i<=n-1;)
{

for(k=0;arr[i-k]==pattren[m-1-k];k++);

if(k==m)
return i-m+1;

i+=table[arr[i]];
}

return -1;
}

int binomial(int n,int r)
{
if(n==0||r==0||n==r)
return 1;
else
return binomial(n-1,r-1)+binomial(n-1,r);
}

void main()
{
cout<<"Enter the string:";
char *str=new char[100];cin>>str;
cout<<"Enter the pattren:";
char *pat=new char[100];cin>>pat;
int index=horsepool(str,pat);
if(index==-1)
cout<<"\nString NOT found";
else
cout<<"\nString found at:"<<index;


cout<<"\n\nEnter the valut of n and r for binomial coefficient:";
int n,r;cin>>n>>r;
cout<<"Binomial coefficient of "<<n<<"C"<<r<<"="<<binomial(n,r);

getch();
}

Computer Science: 5th sem ADA Lab Program 14

Analysis and Design of Algorithms Lab.

Question 14.

//Implement N Queen's problem using backtracking.

#include <iostream>
#include <conio.h>

using namespace std;

bool isPossible(int *arr,int k)
{
for(int i=1;i<k;i++)
if(arr[k]==arr[i] || i-arr[i]==k-arr[k] || i+arr[i]==k+arr[k])
return false;
return true;
}
int queen(int n)
{
int x[10],count=0,k=1;
x[k]=0;

while(k!=0)
{
x[k]+=1;
while(x[k]<=n && !isPossible(x,k))
x[k]+=1;
if(x[k]<=n)
{
if(k==n)
{
count++;
cout<<"\nSolution"<<count;
for(int j=1;j<=n;j++)
{
cout<<"\n";
for(int k=1;k<=n;k++)
if(x[j]==k)
cout<<"q ";
else
cout<<"- ";
}
}
else
{
k++;
x[k]=0;
}
}
else
k--;
}

return 1;
}
void main()
{
cout<<"Enter the size of board:";int n;cin>>n;

int *arr=new int[n];
for(int i=0;i<n;i++)
arr[i]=-1;

queen(n);
getch();
}

Computer Science: 5th sem ADA Lab Program 10

Analysis and Design of Algorithms Lab.

Question 10.

//Find a subset of a given set s = {s1,s2,....sn} of n positive integers whose sum is equal to a given positive integer d.
//(they cannot repeat more than once)

#include <iostream>
#include <conio.h>

using namespace std;

void sumOfSubset(int curSum,int i,int d,int *arr,int *vis,int n)
//curSum=current sum untill this point
//i= the next number to add
//d is the integer to compare
//arr is the original array input in ascending order
//vis is the array to keep track of the visted index in the orginal array. 1=visited,0=not visited
//n is the size of the input

{
if(curSum==d)
{
//print the answer
for(int i=0;i<n;i++)
if(vis[i]>0)
cout<<arr[i]<<" ";
cout<<"\n";
}
if(i<n)
{

if(curSum<d)
{
if(curSum+arr[i]<=d)
{
vis[i]+=1;
sumOfSubset(curSum+arr[i],i+1,d,arr,vis,n);
vis[i]=0;
sumOfSubset(curSum,i+1,d,arr,vis,n);
}

else
{
vis[i]=0;
sumOfSubset(curSum,i+1,d,arr,vis,n);
}

}
}
}

void main()
{
cout<<"Enter the value of d:";int d;cin>>d;
cout<<"Enter the size of the array:";int n;cin>>n;
cout<<"Enter the Array:\n";
int *arr=new int[n];
int *vis=new int[n];
for(int i=0;i<n;i++)
{
cin>>arr[i];
vis[i]=0;
}
cout<<"\nAny subsets=\n";

sumOfSubset(0,0,d,arr,vis,n);

getch();
}

Database Management Systems (DBMS) Lab Program 5

Database Management Systems (DBMS) Lab Program



Question 5

CREATE TABLE branch(
name VARCHAR(30),
city VARCHAR(30),
assets NUMBER(10,3),
PRIMARY KEY(name)
);

CREATE TABLE account (
accno NUMBER(10),
branchname VARCHAR(30),
balance NUMBER(10,3),
PRIMARY KEY(accno),
FOREIGN KEY (branchname) REFERENCES branch(name) ON DELETE CASCADE,
CHECK(balance>0)
);

CREATE TABLE cust(
name VARCHAR(30),
street VARCHAR(30),
city VARCHAR(30),
PRIMARY KEY(name)
);

CREATE TABLE depositor (
custname VARCHAR(30),
accno NUMBER(10),
PRIMARY KEY(custname,accno),
FOREIGN KEY (custname) REFERENCES cust(name) ON DELETE CASCADE,
FOREIGN KEY (accno) REFERENCES account(accno) ON DELETE CASCADE
);

CREATE TABLE loan (
no NUMBER(10),
branchname VARCHAR(30),
amount NUMBER(10,3),
PRIMARY KEY(no),
FOREIGN KEY (branchname) REFERENCES branch(name) ON DELETE CASCADE
);

CREATE TABLE borrower (
custname varchar(30),
loanno NUMBER(10),
PRIMARY KEY (custname,loanno),
FOREIGN KEY (custname) REFERENCES cust(name) ON DELETE CASCADE,
FOREIGN KEY (loanno) REFERENCES loan(no) ON DELETE CASCADE
);


DESC  branch;
DESC account;
DESC depositor;
DESC customer;
DESC loan;
DESC borrower;


insert into branch values('abc','bang',1200000.01);
insert into branch values('def','che',2000000);
insert into branch values('abn','mum',330000);
insert into branch values('xyz','hyd',555555);
insert into branch values('mno','bang',9999999.2);

insert into  account values(1,'abc',25000);
insert into  account values(2,'def',12000.14);
insert into  account values(3,'def',1000);
insert into  account values(4,'abn',10000);
insert into  account values(5,'mno',600000.66);
insert into  account values(6,'xyz',50000);

insert into cust values('mik','ab','bang');
insert into cust values('muj','cd','bang');
insert into cust values('maj','ef','che');
insert into cust values('waj','xy','del');
insert into cust values('prad','lm','mum');
insert into cust values('now','op','hyd');

insert into depositor values('mik',2);
insert into depositor values('muj',1);
insert into depositor values('muj',5);
insert into depositor values('prad',4);
insert into depositor values('maj',3);
insert into depositor values('waj',6);
insert into depositor values('mik',3);

insert into loan values(1,'abc',5000);
insert into loan values(2,'def',1500);
insert into loan values(3,'abn',10000);
insert into loan values(4,'xyz',3500);
insert into loan values(5,'mno',20000);

insert into borrower values('mik',2);
insert into borrower values('muj',1);
insert into borrower values('prad',3);
insert into borrower values('maj',4);
insert into borrower values('waj',5);



SELECT * FROM branch;
SELECT * FROM account;
SELECT * FROM depositor;
SELECT * FROM customer;
SELECT * FROM loan;
SELECT * FROM borrower;




-----------------------------------------------------------------------------------------------------------
3 - at least 2 accounts at the main branch.
-----------------------------------------------------------------------------------------------------------
select d.custname,count(d.custname)
from account a,depositor d
where a.accno=d.accno AND a.branchname='def'
group by d.custname
having count(d.custname)>1;


-----------------------------------------------------------------------------------------------------------
4 - all the customers who have an account at ALL the banches located in a specified city
-----------------------------------------------------------------------------------------------------------
select cname from cust c where not exists
  (select bname from branch where city='bang' minus select bname from depositor d,account a
   where d.accno=a.accno and d.cname=c.cname)
 


-----------------------------------------------------------------------------------------------------------
5 - demonstrate how you delete a tuple in account relation at every branch locatedin a specified city.
-----------------------------------------------------------------------------------------------------------

DELETE FROM account
WHERE branchname IN
(SELECT name FROM branch WHERE city LIKE 'che');

Database Management Systems (DBMS) Lab Program 4

Database Management Systems (DBMS) Lab Program



Question 4

CREATE TABLE author (authorid NUMBER(10),
name VARCHAR(30) NOT NULL,
city VARCHAR(30),
country VARCHAR(30),
PRIMARY KEY(authorid)
);


CREATE TABLE publisher (
publisherid NUMBER(10),
name VARCHAR(30) NOT NULL,
city VARCHAR(30),
country VARCHAR(30),
PRIMARY KEY(publisherid)
);

CREATE TABLE category (
catagoryid NUMBER(10),
description VARCHAR(50),
PRIMARY KEY(catagoryid)
);

CREATE TABLE catalog(
bookid NUMBER(10),
title VARCHAR(30) NOT NULL,
authorid NUMBER(30),
publisherid NUMBER(30),
price NUMBER(10),
year NUMBER(4),
catagoryid NUMBER(10),
PRIMARY KEY(bookid),
CONSTRAINT ca1 FOREIGN KEY (authorid) REFERENCES author(authorid),
CONSTRAINT ca2 FOREIGN KEY (publisherid) REFERENCES publisher(publisherid),
CONSTRAINT ca3 FOREIGN KEY (catagoryid) REFERENCES category (catagoryid)
);

CREATE TABLE ord_details (
orderno NUMBER(10),
bookid NUMBER(10),
qty NUMBER(10) NOT NULL,
PRIMARY KEY(orderno,bookid),
CHECK(qty>0),
CONSTRAINT or1 FOREIGN KEY (bookid) REFERENCES catalog(bookid)
);


INSERT INTO author VALUES (1,'Adarsha','Bangalore','India');
INSERT INTO author VALUES (2,'Ashish','Bangalore','India');
INSERT INTO author VALUES (3,'Abhishek','Bangalore','India');
INSERT INTO author VALUES (4,'Aashray','Bangalore','India');
INSERT INTO author VALUES (5,'Anirudh','Bangalore','India');

INSERT INTO publisher VALUES(1,'abc','Bangalore','India');
INSERT INTO publisher VALUES(2,'bcd','Bangalore','India');
INSERT INTO publisher VALUES(3,'def','Bangalore','India');
INSERT INTO publisher VALUES(4,'ghi','Bangalore','India');
INSERT INTO publisher VALUES(5,'klm','Bangalore','India');


INSERT INTO category VALUES(1,'1st cata');
INSERT INTO category VALUES(2,'2nd cata');
INSERT INTO category VALUES(3,'3rd cata');
INSERT INTO category VALUES(4,'4th cata');
INSERT INTO category VALUES(5,'5th cata');

INSERT INTO catalog VALUES
(1,'Book1',1,1,495,2010,1);
INSERT INTO catalog VALUES
(2,'Book2',2,2,399,2005,2);
INSERT INTO catalog VALUES
(3,'Book3',3,3,395,2009,3);
INSERT INTO catalog VALUES
(4,'Book4',4,4,785,2008,4);
INSERT INTO catalog VALUES
(5,'Book5',5,5,625,2010,5);
INSERT INTO catalog VALUES
(6,'Book6',5,5,625,2010,5);
INSERT INTO catalog VALUES
(7,'Book7',5,4,425,1990,5);
INSERT INTO catalog VALUES
(8,'Book8',3,5,525,2010,5);
INSERT INTO catalog VALUES
(9,'Book9',2,4,325,1999,5);
INSERT INTO catalog VALUES
(10,'Book10',5,2,225,2000,5);

INSERT INTO ord_details VALUES(1,2,5);
INSERT INTO ord_details VALUES(2,2,4);
INSERT INTO ord_details VALUES(3,1,6);
INSERT INTO ord_details VALUES(3,2,2);
INSERT INTO ord_details VALUES(4,2,3);
INSERT INTO ord_details VALUES(5,3,4);
INSERT INTO ord_details VALUES(5,2,4);

commit;


DESC author;
DESC publisher;
DESC category;
DESC catalog;
DESC ord_details;


SELECT * FROM author;
SELECT * FROM publisher;
SELECT * FROM category;
SELECT * FROM catalog;
SELECT * FROM ord_details;

----------------------------------------------------------------------------------------------------------
3.

select * from author a
where a.authorid IN
(
select authorid
from catalog
group by authorid
having count(authorid)>1)

AND authorid IN (
select authorid
from catalog c
where year>2000 AND price>(select avg(price) from catalog)
group by authorid
)
----------------------------------------------------------------------------------------------------------
4.

select a.*

from
(
select bookid as b,sum(qty) as s
from ord_details
group by bookid
) X,
(select max(s) as s
from (select bookid as b,sum(qty) as s
from ord_details
group by bookid)) Y,author a
WHERE X.s=Y.s and a.authorid=X.b;
----------------------------------------------------------------------------------------------------------
OR
----------------------------------------------------------------------------------------------------------
select a.*

from
(
(select o.bookid as b,sum(o.qty),c.price,sum(o.qty)*c.price as s
from ord_details o,catalog c
where o.bookid=c.bookid
group by o.bookid,c.price)
) X,
(select max(s) as s
from ((select o.bookid as b,sum(o.qty),c.price,sum(o.qty)*c.price as s
from ord_details o,catalog c
where o.bookid=c.bookid
group by o.bookid,c.price))) Y,author a
WHERE X.s=Y.s and a.authorid=X.b;

----------------------------------------------------------------------------------------------------------
5.

UPDATE (
select c.*
from catalog c,publisher p
where c.publisherid=p.publisherid and p.name='ghi')
SET price=1.1*price;

Database Management Systems (DBMS) Lab Program 3

Database Management Systems (DBMS) Lab Program



Question 3
 

CREATE TABLE student (
regno VARCHAR(10) CONSTRAINT ss1 PRIMARY KEY,
name VARCHAR(10) CONSTRAINT ss2 NOT NULL,
major VARCHAR(10) CONSTRAINT ss3 NOT NULL,
bdate DATE
);

CREATE TABLE course (
courseno NUMBER(3) CONSTRAINT co1 PRIMARY KEY,
cname VARCHAR(10) CONSTRAINT co2 NOT NULL,
dept VARCHAR(10) CONSTRAINT co3 NOT NULL
);

CREATE TABLE text (
bookISBN NUMBER(10) CONSTRAINT t1 PRIMARY KEY,
book_title VARCHAR(50) CONSTRAINT t2 NOT NULL,
publisher VARCHAR(50),
author VARCHAR(50)
);

CREATE TABLE enroll (
regno VARCHAR(10) CONSTRAINT e11 REFERENCES student(regno) ON DELETE CASCADE,
courseno NUMBER(3) CONSTRAINT e12 REFERENCES course(courseno) ON DELETE CASCADE,
sem NUMBER(1) CONSTRAINT e13 NOT NULL,
marks NUMBER(5,2) CONSTRAINT e14 NOT NULL,
CONSTRAINT e15 PRIMARY KEY (regno,courseno)
);

CREATE TABLE book_adoption (
courseno NUMBER(3) CONSTRAINT b1 REFERENCES course(courseno) ON DELETE CASCADE,
sem NUMBER(1),
bookISBN NUMBER(10) CONSTRAINT b2 NOT NULL REFERENCES text(bookISBN) ON DELETE CASCADE,
PRIMARY KEY(courseno,sem)
);

DESC student;
DESC course;
DESC text
DESC enroll;
DESC book_adoption;

commit;


INSERT INTO student VALUES('ox03','Adarsha','CS','21-JUN-1990');
INSERT INTO student VALUES('ox07','Ashish','EC','18-MAR-1990');
INSERT INTO student VALUES('ox12','Bhavesh','IS','20-JUN-1990');
INSERT INTO student VALUES('ox02','Abhishek','CS','20-JUN-1989');
INSERT INTO student VALUES('ox01','Abhay','CV','2-JUN-1990');


INSERT INTO course VALUES(1,'Comp Sci','CS');
INSERT INTO course VALUES(2,'Electro','EC');
INSERT INTO course VALUES(3,'Info Sci','IS');
INSERT INTO course VALUES(4,'Civil','CV');
INSERT INTO course VALUES(5,'Biotech','BT');
INSERT INTO course VALUES(6,'abc','CS');

  

INSERT INTO text VALUES(1234,'Oper sys','Thiva','Tenanbom');
INSERT INTO text VALUES(4567,'FAFL','SIVA','Farajay');
INSERT INTO text VALUES(7890,'Genes','TGMC','Yan koa');
INSERT INTO text VALUES(1597,'Structures','IIT','Ramesh');
INSERT INTO text VALUES(3579,'VHDL','Oxford','Ashish');

INSERT INTO enroll VALUES('ox03',1,5,701);
INSERT INTO enroll VALUES('ox03',3,4,700);
INSERT INTO enroll VALUES('ox07',5,5,699);
INSERT INTO enroll VALUES('ox12',2,4,698);
INSERT INTO enroll VALUES('ox02',1,4,702);
INSERT INTO enroll VALUES('ox02',2,5,701);
INSERT INTO enroll VALUES('ox01',4,5,700);

INSERT INTO book_adoption VALUES(1,5,1234);
INSERT INTO book_adoption VALUES(2,4,3579);
INSERT INTO book_adoption VALUES(3,4,1234);
INSERT INTO book_adoption VALUES(4,5,1597);
INSERT INTO book_adoption VALUES(1,4,4567);
INSERT INTO book_adoption VALUES(2,5,4567);
INSERT INTO book_adoption VALUES(5,5,7890);
INSERT INTO book_adoption VALUES(6,5,4567);
INSERT INTO book_adoption VALUES(6,4,7410);
INSERT INTO book_adoption VALUES(6,6,1234);
INSERT INTO book_adoption VALUES(1,4,7410);

SELECT * FROM student;
SELECT * FROM course;
SELECT * FROM text;
SELECT * FROM enroll;
SELECT * FROM book_adoption;

commit;

------------------------------------------------------------------------------------------------------------
3.
INSERT INTO text VALUES(7410,'Computer Networks','Head First','Gimp');
INSERT INTO book_adoption VALUES(3,5,7410);
SELECT * FROM text;
SELECT * FROM book_adoption;
------------------------------------------------------------------------------------------------------------
4.

select distinct c.courseno,t.bookISBN,t.book_title
from course c,text t,book_adoption a
where c.courseno=a.courseno and a.bookISBN=t.bookISBN and c.dept LIKE 'CS' and c.courseno IN

(select x.courseno
from book_adoption x
group by x.courseno
having count(x.courseno)>2)


order by book_title;

SELECT * FROM course;
SELECT * FROM text;
SELECT * FROM book_adoption;

commit;
------------------------------------------------------------------------------------------------------------
5.

select DISTINCT c.dept
from course c,book_adoption a,text t
where c.courseno=a.courseno and t.bookISBN=a.bookISBN
and c.courseno NOT IN
(
select x.courseno
from book_adoption x
where x.bookISBN NOT IN (
select y.bookISBN
from text y
where y.publisher LIKE 'abc'));