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

}

Computer Science: 5th sem ADA Lab Program 5

Analysis and Design of Algorithms Lab.

Question 5.

//Implement 0/1 Knapsack problem using dynamic programing

//NOTE: Increase the 'MAX' value to give higher inputs.

#define MAX 10

#include <iostream>

#include <conio.h>

using namespace std;

int sack[MAX][MAX];

inline int bigger(int a,int b)

{

return a>b?a:b;

}

void knapSack(int capacity,int n,int *w,int *v)

{

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

{

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

{

if(i==0 || j==0)

sack[i][j]=0;

else

{

if(j-w[i]>=0)

sack[i][j]=bigger(sack[i-1][j],v[i]+sack[i-1][j-w[i]]);

else

sack[i][j]=sack[i-1][j];

}

}

}

}

void main()

{

cout<<"Enter the capacity of knapsack:";int cap;cin>>cap;

cout<<"Enter the No of items:";int n;cin>>n;

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

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

cout<<"Enter the weight & value of the articles:\nW V\n\n";

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

{

cin>>weight[i]>>value[i];

}

cout<<"\nThe knapsack solution:\n";

knapSack(cap,n,weight,value);

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

{

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

cout<<sack[i][j]<<"\t";

cout<<"\n";

}

getch();

}