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'));

Database Management Systems (DBMS) Lab Program 2

Database Management Systems (DBMS) Lab Program

Question 2

 

 

CREATE TABLE customer (
cust# NUMBER(10) CONSTRAINT cc1 PRIMARY KEY,
cname VARCHAR(20) CONSTRAINT cc2 NOT NULL,
city VARCHAR(30)
);

CREATE TABLE ord (
order# NUMBER(10) CONSTRAINT o1 PRIMARY KEY,
odate DATE CONSTRAINT o2 NOT NULL,
cust# NUMBER(10) CONSTRAINT o3 REFERENCES customer(cust#),
ord_amt NUMBER(10,3) CONSTRAINT o4 CHECK(ord_amt>0)
);

CREATE TABLE item (
item# NUMBER(10) CONSTRAINT i1 PRIMARY KEY,
unitPrice NUMBER(10,3) CONSTRAINT i2 CHECK(unitPrice>0)
);

CREATE TABLE order_item (
order# NUMBER(10) CONSTRAINT oi1 REFERENCES ord(order#) ON DELETE CASCADE,
item# NUMBER(10) CONSTRAINT oi2 REFERENCES item(item#) ON DELETE CASCADE,
qty NUMBER(6) CONSTRAINT oi3 CHECK(qty>0),
CONSTRAINT oi4 PRIMARY KEY(order#,item#)
);

CREATE TABLE warehouse (
warehouse# NUMBER(10) CONSTRAINT w1 PRIMARY KEY,
city VARCHAR(30)
);

CREATE TABLE shippment (
ORDER# NUMBER(10) CONSTRAINT s1 REFERENCES ord(order#) ON DELETE CASCADE,
warehouse# NUMBER(10) CONSTRAINT s2 REFERENCES warehouse(warehouse#) ON DELETE CASCADE,
ship_date DATE CONSTRAINT s3 NOT NULL,
CONSTRAINT s4 PRIMARY KEY(warehouse#,order#)
);

commit;

INSERT INTO customer  VALUES(1,'Adarsha','Bangalore');
INSERT INTO customer  VALUES(2,'Ashish','Bangalore');
INSERT INTO customer  VALUES(3,'Bhavesh','Bangalore');
INSERT INTO customer  VALUES(4,'Anirudh','Bangalore');
INSERT INTO customer  VALUES(5,'Abhishek','Bangalore');


INSERT INTO ord  VALUES(1,'1-Jan-1990',1,2500);
INSERT INTO ord  VALUES(2,'2-Jan-1990',1,4500);
INSERT INTO ord  VALUES(3,'3-Jan-1990',2,6955);
INSERT INTO ord  VALUES(4,'4-Jan-1990',4,1778);
INSERT INTO ord  VALUES(5,'5-Jan-1990',5,2345);


INSERT INTO item  VALUES(1,89);
INSERT INTO item  VALUES(2,87);
INSERT INTO item  VALUES(3,45);
INSERT INTO item  VALUES(4,47);
INSERT INTO item  VALUES(5,56);


INSERT INTO order_item  VALUES(1,2,5);
INSERT INTO order_item  VALUES(1,5,4);
INSERT INTO order_item  VALUES(2,5,3);
INSERT INTO order_item  VALUES(3,1,4);
INSERT INTO order_item  VALUES(4,2,8);
INSERT INTO order_item  VALUES(5,3,2);

INSERT INTO warehouse  VALUES(1,'Bangalore');
INSERT INTO warehouse  VALUES(2,'Bagalkot');
INSERT INTO warehouse  VALUES(3,'Bijapur');
INSERT INTO warehouse  VALUES(4,'Bidar');
INSERT INTO warehouse  VALUES(5,'Bannerghatta');


INSERT INTO shippment  VALUES(1,2,'2-Jan-1990');
INSERT INTO shippment  VALUES(1,4,'2-Jan-1990');
INSERT INTO shippment  VALUES(2,1,'3-Jan-1990');
INSERT INTO shippment  VALUES(3,5,'4-Jan-1990');
INSERT INTO shippment  VALUES(4,2,'5-Jan-1990');
INSERT INTO shippment  VALUES(5,4,'6-Jan-1990');



SELECT * FROM customer;
SELECT * FROM ord;
SELECT * FROM item;
SELECT * FROM order_item;
SELECT * FROM warehouse;
SELECT * FROM shippment;

DESC customer;
DESC ord;

 
DESC item;
DESC order_item;
DESC warehouse;
DESC shippment;

commit;
-----------------------------------------------------------------------------------------------------------
a:
SELECT * FROM customer;
SELECT * FROM ord;

SELECT c.cname as customer_name ,count(o.order#) as No_of_Orders ,avg(ord_amt) as average_amount
FROM customer c,ord o
WHERE c.cust#=o.cust#
GROUP BY c.cust#, c.cname;
------------------------------------------------------------------------------------------------------------
b:
SELECT order#
FROM

(SELECT count(warehouse#) as tot1
FROM warehouse w
WHERE w.city LIKE ‘Bidar’) A,
(SELECT count(s.order#) as tot2
FROM shippment s
WHERE s.warehouse# IN
(SELECT w.warehouse#
FROM warehouse w
WHERE w.city LIKE ‘Bidar’)
GROPU BY s.order#) B
WHERE A.tot1=B.tot2;
------------------------------------------------------------------------------------------------------------
c:
DELETE FROM item WHERE item#=2;

Computer Science: 5th sem ADA Lab Program 13

Analysis and Design of Algorithms Lab.

Question 13.

//Implement floyd's algorithm for the all-pairs-shortest-path problem.
//Compute the transitive closure of a given directed graph using warshall's
#include <iostream>
#include <conio.h>
#define MAX 10

using namespace std;

void disp(int arr[MAX][MAX],int n)
{
for(int i=0;i<n;i++)
{
cout<<"\n";
for(int j=0;j<n;j++)
cout<<arr[i][j]<<" ";
}
}
void inp(int arr[MAX][MAX],int n)
{
for(int i=0;i<n;i++)
for(int j=0;j<n;j++)
cin>>arr[i][j];
}

inline int min(int a,int b)
{
return a<b?a:b;
}

void floyd(int graph[MAX][MAX],int n)
{
for(int k=0;k<n;k++)
for(int i=0;i<n;i++)
for(int j=0;j<n;j++)
graph[i][j]=min(graph[i][j],graph[i][k]+graph[k][j]);
}

void warshal(int graph[MAX][MAX],int n)
{
for(int k=0;k<n;k++)
for(int i=0;i<n;i++)
for(int j=0;j<n;j++)
graph[i][j]=graph[i][j] || (graph[i][k] && graph[k][j]);
}

void main()
{
cout<<"\nWarshal's:\nEnter the size for matrix:";
int n;cin>>n;
int graph[MAX][MAX];
inp(graph,n);
warshal(graph,n);
cout<<"\nWarshal's output:\n";
disp(graph,n);

cout<<"\nFloyd's:\nEnter the size for matrix:";
cin>>n;
int weighted_graph[MAX][MAX];
inp(weighted_graph,n);
floyd(weighted_graph,n);
cout<<"\nFloyd's's output:\n";
disp(weighted_graph,n);

getch();
}

Computer Science: 5th sem ADA Lab Program 9

Analysis and Design of Algorithms Lab.

Question 9.

//print all the nodes reachable from a given starting node in a diagraph using BFS(Breadth first search)

///Check whether a given graph is connected or not using DFS(Depth first search)

#include<iostream>

#include<conio.h>

#define MAX 10

using namespace std;

void BFS(int a[MAX][MAX],int n,int v,int *vis,int &count)

{

if(vis[v]==-1)

{

vis[v]=++count;

int *q=new int[n];

int q_ponter=-1;

for(int i=0;i<n;i++)

q[i]=-1;

for(int i=0;i<n;i++)

{

if(a[v][i]!=0 && vis[i]==-1)

{

vis[i]=++count;

q[++q_ponter]=i;

}

}

for(int i=0;i<=q_ponter;i++)

BFS(a,n,q[i],vis,count);

}

}

void bfs(int a[MAX][MAX],int n)

{

int *vis=new int[n];

for(int i=0;i<n;i++)

vis[i]=-1;

int count=0;

for(int i=0;i<n;i++)

{

BFS(a,n,i,vis,count);

}

for(int i=0;i<n;i++)

cout<<vis[i]<<" ";

}

void DFS(int a[MAX][MAX],int n,int v,int *vis)

{

if(vis[v]==0)

{

vis[v]=1;

for(int i=0;i<n;i++)

if(a[v][i]!=99)

DFS(a,n,i,vis);

}

}

bool dfs(int a[MAX][MAX],int n)

{

int *vis=new int[n];

for(int i=0;i<n;i++)

vis[i]=-1;



DFS(a,n,0,vis);

for(int i=0;i<n;i++)

if(vis[i]==-1)

return false;

return true;

}

void main()

{

int n;

cout<<"Enter the size of the array:";cin>>n;

cout<<"Enter the matrix:\n\n";

int arr[MAX][MAX];

for(int i=0;i<n;i++)

for(int j=0;j<n;j++)

cin>>arr[i][j];



cout<<"\nThe order of BFS traversal according to the vertici numbers:\n";

bfs(arr,n);

if(dfs(arr,n))

cout<<"\nThe graph is connected";

else

cout<<"\nThe graph is not connected";

getch();

}

Computer Science: 5th sem ADA Lab Program 8

Analysis and Design of Algorithms Lab.

Question 8.

//Find the mimimum cost spanning tree of a given undirected graph using Kruskal's algorithm

#include <iostream>

#include <conio.h>

#define MAX 10

using namespace std;

void kruskal(int graph[MAX][MAX],int n)

{

int *vis=new int[n];

for(int i=0;i<n;i++)

vis[i]=-1;

int edge=1,max=0;

while(edge<n)

{

int min=999,a,b,u,v;

for(int i=0;i<n;i++)

{

for(int j=0;j<n;j++)

{

if(graph[i][j]<min && i!=j)

{

min=graph[i][j];

a=u=i;b=v=j;

}

}

}



while(vis[a]!=-1)

a=vis[a];

while(vis[b]!=-1)

b=vis[b];



if(a!=b)

{

vis[b]=a;

cout<<"\n"<<u<<"-"<<v<<"="<<min;

edge++;

max+=min;

}

graph[u][v]=999;

graph[v][u]=999;

}

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

}

void main()

{

cout<<"Enter the number of verticies:";

int n;cin>>n;

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);

getch();

}

Computer Science: 5th sem ADA Lab Program 7

Analysis and Design of Algorithms Lab.

Question 7.

//Sort a given set of elements using quick sort method and determine the time required to sort the elements

#include <iostream>

#include <conio.h>

#include <time.h>

using namespace std;

void swap(int &a,int &b)

{

int tmp=a;a=b;b=tmp;

}

int partition(int *arr,int start,int end)

{

int pivot=arr[start];

int i=start,j=end+1;

do {



do {

i++;

}while(arr[i]<pivot);

do {

j--;

}while(arr[j]>pivot);

swap(arr[i],arr[j]);

}while(i<j);

swap(arr[i],arr[j]);

swap(arr[start],arr[j]);

return j;

}

void quickSort(int *arr,int start,int end)

{

if(start<end)

{

int p=partition(arr,start,end);

cout<<"\n";

for(int i=start;i<=end;i++)

cout<<arr[i]<<" ";

quickSort(arr,start,p-1);

quickSort(arr,p+1,end);

}

}

void main()

{

int n;

cout<<"Enter the size of the array:";cin>>n;

cout<<"Enter the array:\n\n";

int *arr=new int[n+1];

for(int i=0;i<n;i++)

cin>>arr[i];



cout<<"\nQuick Sorting...\n\n";

int start=clock();

quickSort(arr,0,n-1);

int time1=(clock()-start)/CLK_TCK;

cout<<"\nTime taken:"<<time1<<"\n\n";

cout<<"\n";

for(int i=0;i<n;i++)

cout<<arr[i]<<" ";

getch();

}

Computer Science: 5th sem ADA Lab Program 6

Analysis and Design of Algorithms Lab.

Question 6.

//From a given vertex in a weighted connected graph find shortest paths to other vertices using dijikstra's algorithm

#include <iostream>

#include <conio.h>

#define MAX 10

using namespace std;

void dijkstra(int graph[MAX][MAX], int n,int source)

{

int *vis=new int[n];

int *weight=new int[n];

for(int i=0;i<n;i++)

{

vis[i]=0;

weight[i]=graph[source][i];

}

int done=1;

vis[source]=1;

int min_pointer=source;

while(done<=n)

{

int min=99;

for(int i=0;i<n;i++)

{

//identify the minimum weighted vertex in the current vertex(not visited earlier).

if(weight[i]<min && vis[i]==0)

{

min=weight[i];

min_pointer=i;//store the minimum weighted vertex

}

}



vis[min_pointer]=1;

done++;

for(int i=0;i<n;i++)

{

if(weight[min_pointer]+graph[min_pointer][i]<weight[i] && vis[i]==0)

weight[i]=weight[min_pointer]+graph[min_pointer][i];

}

}

cout<<"\nThe weights are:\n\n";



for(int i=0;i<n;i++)

{

cout<<source<<" - "<< i <<" = "<< weight[i]<<" \n";

}

}

void main()

{

cout<<"Enter the number of verticies:";

int n;cin>>n;

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];

cout<<"Enter the source vertex:";

int source;cin>>source;

dijkstra(graph,n,source);

getch();

}