DISTRIBUTED DATABASE
AIM:
To develop and write SQL queries for a distributed database of BookStore at four
sites S1, S2, S3 and S4. The Bookstores are divided into four sites by their ZIP codes.
Distributed Database Design
Description:
A distributed database is a database in which storage devices are not all attached to a
common processing unit such as the CPU, controlled by a distributed database management
system. (together sometimes called a distributed database system). It may be stored in multiple
computers, located in the same physical location; or may be dispersed over a network of
interconnected computers. Unlike parallel systems, in which the processors are tightly coupled
and constitute a single database system, a distributed database system consists of loosely-coupled
sites that share no physical components.
There are two principal approaches to store a relation r in a distributed database system:
A) Replication
B) Fragmentation/Partitioning
A) Replication: In replication, the system maintains several identical replicas of the same relation
r in different sites.
Data is more available in this scheme.
Parallelism is increased when read request is served.
Increases overhead on update operations as each site containing the replica
needed to be updated in order to maintain consistency.
Multi-datacenter replication provides geographical diversity:
B) Fragmentation: The relation r is fragmented into several relations r1, r2, r3....rn in such a way
that the actual relation could be reconstructed from the fragments and then the fragments are
scattered to different locations. There are basically two schemes of fragmentation:
Horizontal fragmentation - splits the relation by assigning each tuple of r to one or
more fragments.
Vertical fragmentation - splits the relation by decomposing the schema R of
relation r.
Implementing distributed databases using SQL Server 2005
Linked servers provide SQL Server the ability to access data from remote data sources. Using
these mechanisms, we can issue queries, perform data modifications and execute remote
procedures. We can use the T-SQL function OPENROWSET to query a remote data source
without a linked server.
DATE:
EX.NO:
Steps:
1. Create Horizontal Partition:
First partition a table horizontally. In designing a partitioning scheme, it must be clear what
data belongs to each member table. The original table is replaced with several smaller
member tables. Each member table has the same number of columns as the original table,
and each column has the same attributes as the corresponding column in the original table,
such as data type, size, and collation. By using a distributed partitioned view, each member
table is on a separate member server. For the greatest location transparency, the name of the
member databases should be the same on each member server, although this is not
required.For
example: Server1.CustomerDB, Server2.CustomerDB, Server3.CustomerDB.
1.1 Creating Member Tables
Design the member tables so that each table stores a horizontal slice of the original table based
on a range of key values. The ranges are based on the data values in a partitioning column. The
range of values in each member table is enforced by a CHECK constraint on the partitioning
column, and ranges cannot overlap.The CHECK constraint for this table is the following:
-- On Server1:
CREATE TABLE Customers_33
(CustomerID INTEGER PRIMARY KEY
CHECK (CustomerID BETWEEN 1 AND 32999),
... -- Additional column definitions)
Similarly create a member table on the other server instances.
2. Create Linked Server:
The servers that can be referenced in queries are called linked servers. A linked server is any
data source that can be accessed using OLE DB
– It can be another SQL Server or
– A different database server (such as Oracle) or
– A simpler data source, such as a file (Excel, Access)
Create a linked server to another SQL Server instance using the T-SQL procedure
sp_addlinkedserver. The syntax of sp_addlinkedserver is
EXECsp_addlinkedserver
[ @server= ] 'server'
[ ,[ @srvproduct= ] 'product_name'
[ , [ @provider= ] 'provider_name'
[ , [ @datasrc= ] 'data_source'
[ , [ @location= ] 'location' ]
[ , [ @provstr= ] 'provider_string'
[ , [ @catalog= ] 'catalog' ]
And each parameter is described as
Parameter Description
server Local name used for the linked server.
product_name Product name of the OLE DB data source. For SQL Server instances, the
product_name is 'SQL Server'.
provider_name
This is the unique programmatic identifier for the OLE DB provider. When not
specified, the provider name is the SQL Server data source. The explicit
provider_name for SQL Server is SQLNCLI (for Microsoft SQL Native Client
OLE DB Provider).
data_source This is the data source as interpreted by the OLE DB provider.
location The location interpreted by the OLE DB provider.
provider_string The connection string specific to the OLE DB provider.
catalog This varies from the database systems.
3. Add linked server definitions on each member server that contains the connection information
required to run distributed queries on the other member servers. This gives a distributed
partitioned view access to data on the other servers.
4. Defining Distributed Partition Views:
After you create the member tables, you define a distributed partitioned view on each member
server, with each view having the same name. This enables queries that reference the distributed
partitioned view name to run on one or more of the member servers. The system operates as if a
copy of the original table is on each member server, but each server has only a member table and
a distributed partitioned view.
Create the following distributed partitioned view:
CREATE VIEW Customers AS select statement
To execute queries on the remote instance, Distributed queries referring to linked server name is
written by using the following syntax
[linked_server_name].[catalog].[schema].[object_name]
Create a distributed partitioned view on other server instances also.
Queries:
1. Insert and Display details in each table.
insert into <tablename> values(list of values);
select *from <tablename>;
SELECT * FROM OPENQUERY (Server1, 'SELECT * FROM bookstore.dbo.Books_1')
2. Find the total number of books in stock where price is between $15 and $55.
select sum(totalstock) 'Total Books' from BooksView where price between 25 and 100
3. Update the book price of book No=1234 from $45 to $55 at site S3.
update openquery(Server2,'select price from bookstore.dbo.Books_1 where ISBN=45') set
price=100
(1 row(s) affected)
4. Find total number of book at site S2.
select *from openquery(cs03c025,'select sum(totalstock) from bookstore.dbo.Books')
Create three databases names S1,S2,S3,S4.
Create the following tables in all of them
USE [S1]
GO
/****** Object: Table [dbo].[Books] Script Date: 3/8/2014 4:10:30 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Books](
[ISBN] [uniqueidentifier] NOT NULL,
[PrimaryAuthor] [varchar](50) NULL,
[Topic] [varchar](50) NULL,
[TotalStock] [int] NULL,
[Price] [decimal](18, 0) NULL,
CONSTRAINT [PK_Books] PRIMARY KEY CLUSTERED
(
[ISBN] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY
= OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
USE [S4]
GO
/****** Object: Table [dbo].[BookStore] Script Date: 3/8/2014 3:07:23 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[BookStore](
[StoreNo] [uniqueidentifier] NOT NULL,
[City] [varchar](50) NULL,
[State] [varchar](50) NULL,
[Zip] [varchar](50) NULL,
[InventoryValue] [decimal](18, 0) NULL,
CONSTRAINT [PK_BookStore] PRIMARY KEY CLUSTERED
(
[StoreNo] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY
= OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
USE [S4]
GO
drop table [Stock]
/****** Object: Table [dbo].[Stock] Script Date: 3/8/2014 3:07:50 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Stock](
[StoreNo] [uniqueidentifier] NOT NULL,
[ISBN] [uniqueidentifier] NOT NULL,
[Qty] [int] NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[Stock] WITH CHECK ADD CONSTRAINT [FK_Stock_BookStore]
FOREIGN KEY([StoreNo])
REFERENCES [dbo].[BookStore] ([StoreNo])
GO
ALTER TABLE [dbo].[Stock] CHECK CONSTRAINT [FK_Stock_BookStore]
GO
--Insert and Display details in each table
insert into s1.dbo.Books values(newid(),'Sujatha','Einthir manithan',1,12);
insert into s2.dbo.Books values(newid(),'Sujatha','Sample',1,30);
insert into s3.dbo.Books values(newid(),'Sujatha','Lalgudi',10,40);
insert into s4.dbo.Books values(newid(),'Sujatha','Kanini',5,300);
select * from s1.dbo.Books
select * from s2.dbo.Books
select * from s3.dbo.Books
select * from s4.dbo.Books
insert into s1.dbo.BookStore values(newid(),'Chicago','IL','60661',10000);
insert into s2.dbo.BookStore values(newid(),'Boston','MA','2233',30000);
insert into s3.dbo.BookStore values(newid(),'Albany','NY','1134',60000);
insert into s4.dbo.BookStore values(newid(),'LasVegas','CA','5677',80000);
select * from s1.dbo.BookStore
select * from s2.dbo.BookStore
select * from s3.dbo.BookStore
select * from s4.dbo.BookStore
insert into s1.dbo.stock values((select StoreNo from s1.dbo.BookStore where City='Chicago'),
(select top 1 ISBN from s1.dbo.Books bs where bs.Topic='Einthir manithan'),12);
insert into s2.dbo.stock values((select StoreNo from s1.dbo.BookStore where City='Boston'),
(select top 1 ISBN from s1.dbo.Books where topic='Sample'),12);
insert into s3.dbo.stock values((select StoreNo from s1.dbo.BookStore where City='Albany'),
(select top 1 ISBN from s1.dbo.Books where topic='Lalgudi'),12);
insert into s4.dbo.stock values((select StoreNo from s1.dbo.BookStore where City='LasVegas'),
(select top 1 ISBN from s1.dbo.Books where topic='Kanini'),12);
--details from books
select * from s1.dbo.Books union
select * from s2.dbo.Books union
select * from s3.dbo.Books union
select * from s4.dbo.Books
--details from bookstore
select * from s1.dbo.BookStore union
select * from s2.dbo.BookStore union
select * from s3.dbo.BookStore union
select * from s4.dbo.BookStore
--Details from stock
select * from s1.dbo.Stock union
select * from s2.dbo.Stock union
select * from s3.dbo.Stock union
select * from s4.dbo.Stock
--Find the total number of books in stock where price is between $15 and $55
Create view BooksView as
(
select * from s1.dbo.Books union
select * from s2.dbo.Books union
select * from s3.dbo.Books union
select * from s4.dbo.Books
)
select * from BooksView
Create view BookStoreView as
(
select * from s1.dbo.BookStore union
select * from s2.dbo.BookStore union
select * from s3.dbo.BookStore union
select * from s4.dbo.BookStore
)
select * from BookStoreView
Create view StockView as
(
select * from s1.dbo.Stock union
select * from s2.dbo.Stock union
select * from s3.dbo.Stock union
select * from s4.dbo.Stock
)
--Find the total number of books in stock where price is between $15 and $55
select sum(sv.Qty) from BooksView bv
inner join StockView sv on sv.ISBN=bv.ISBN
inner join BookStoreView bvs on bvs.StoreNo=sv.StoreNo
where bv.Price between 15 and 55
group by bv.ISBN
--Update the bookprice of bookNo=1234 from $45 to $55 at site S3.
update s3.dbo.books set price=55 where topic='Einthir manithan'
--Find total number of book at site S2.
select sum(sv.Qty) from s2.dbo.books bv
inner join s2.dbo.Stock sv on sv.ISBN=bv.ISBN
inner join s2.dbo.books bvs on bvs.StoreNo=sv.StoreNo
group by bv.ISBN
Result:
Thus the Distributed Database has been developed and executed successfully.
DEADLOCK DETECTION ALGORITHM FOR DISTRIBUTED DATABASE USING
WAIT- FOR GRAPH
Aim:
To implement Deadlock Detection Algorithm for Distributed Database using Wait-for
Graph to check for Deadlock.
Description:
There are five transactions T1, T2, T3, T4 and T5 with
T1 initiated at site S1 and spawning an agent at site S2
T2 initiated at site S3 and spawning an agent at site S1
T3 initiated at site S1 and spawning an agent at site S3
T4 initiated at site S2 and spawning an agent at site S3
T5 initiated at site S3
Wait For Graph:
DATE:
EX.NO:
CODINGS:
Connected to:
Oracle9i Release 9.0.1.1.1 - Production
JServer Release 9.0.1.1.1 - Production
SQL> create table dd1(trans varchar(20),loc varchar2(10),wait varchar2(10),site
varchar2(10));
Table created.
SQL> insert into dd1 values('t1','x1','x8','s1');
1 row created.
SQL> insert into dd1 values('t1','x6','x2','s2');
1 row created.
SQL> insert into dd1 values('t2','x4','x7','s2');
1 row created.
SQL> insert into dd1 values('t2','x5',' ','s3');
1 row created.
SQL> insert into dd1 values('t3','x2','x7','s1');
1 row created.
SQL> insert into dd1 values('t4','x7',' ','s2');
1 row created.
SQL> insert into dd1 values('t4','x8','x5','s3');
1 row created.
SQL> insert into dd1 values('t5','x3','x7','s3');
1 row created.
SQL> select * from dd1;
TRANS LOC WAIT SITE
-------------------- ---------- ---------- ----------
t1 x1 x8 s1
t1 x6 x2 s2
t2 x4 x7 s2
t2 x5 s3
t3 x2 x7 s1
t4 x7 s2
t4 x8 x5 s3
t5 x3 x7 s3
8 rows selected.
SQL> ed dd1;
SQL> set serveroutput on;
SQL> @dd1;
42 /
SELECT trans, lock1, wait
*
ERROR at line 3:
ORA-06550: line 3, column 15:
PL/SQL: ORA-00904: invalid column name
ORA-06550: line 3, column 1:
PL/SQL: SQL Statement ignored
ORA-06550: line 5, column 34:
PLS-00302: component 'LOCK1' must be declared
ORA-06550: line 5, column 1:
PL/SQL: Item ignored
ORA-06550: line 2, column 9:
PLS-00341: declaration of cursor 'C1' is incomplete or malformed
ORA-06550: line 14, column 4:
PL/SQL: Item ignored
ORA-06550: line 19, column 15:
PLS-00320: the declaration of the type of this expression is incomplete or
malformed
ORA-06550: line 19, column 1:
PL/SQL: SQL Statement ignored
ORA-06550: line 23, column 10:
PLS-00320: the declaration of the type of this expression is incomplete or
malformed
ORA-06550: line 23, column 1:
PL/SQL: Statement ignored
ORA-06550: line 26, column 9:
PLS-00320: the declaration of the type of this expression is incomplete or
malformed
ORA-06550: line 26, column 1:
PL/SQL: Statement ignored
ORA-06550: line 29, column 10:
PLS-00320: the declaration of the type of this expression is incomplete or
SQL> ed dd1;
SQL> @dd1;
42 /
ll(c) := ss.lock1;
*
ERROR at line 23:
ORA-06550: line 23, column 13:
PLS-00302: component 'LOCK1' must be declared
ORA-06550: line 23, column 1:
PL/SQL: Statement ignored
SQL> ed dd1;
SQL> @dd1;
42 /
TRANS Lock wait
t1 x1 x8
t1 x6 x2
t2 x4 x7
t2 x5
t3 x2 x7
t4 x7
t4 x8 x5
t5 x3 x7
x5<-x5deadlock occured
x2<-x2deadlock occured
x7<-x7deadlock occured
x7<-x7deadlock occured
x7<-x7deadlock occured
x8<-x8deadlock occured
PL/SQL procedure successfully completed.
SQL> ed dd2;
SQL> @dd2;
37 /
TRANS Lock wait
t1 x1 x8
t3 x2 x7
PL/SQL procedure successfully completed.
SQL> ed dd3;
SQL> ed dd3;
SQL> @dd3;
40 /
SELECT trans, lock1, wait
*
ERROR at line 3:
ORA-06550: line 3, column 15:
PL/SQL: ORA-00904: invalid column name
ORA-06550: line 3, column 1:
PL/SQL: SQL Statement ignored
ORA-06550: line 2, column 9:
PLS-00341: declaration of cursor 'C1' is incomplete or malformed
ORA-06550: line 14, column 4:
PL/SQL: Item ignored
ORA-06550: line 19, column 15:
PLS-00320: the declaration of the type of this expression is incomplete or
malformed
ORA-06550: line 19, column 1:
PL/SQL: SQL Statement ignored
ORA-06550: line 21, column 22:
PLS-00320: the declaration of the type of this expression is incomplete or
malformed
ORA-06550: line 21, column 1:
PL/SQL: Statement ignored
ORA-06550: line 24, column 10:
PLS-00320: the declaration of the type of this expression is incomplete or
malformed
ORA-06550: line 24, column 1:
PL/SQL: Statement ignored
ORA-06550: line 27, column 10:
PLS-00320: the declaration of the type of this expression is incomplete or
malformed
ORA-06550: line 27, column 1:
PL/SQL: Statement ignored
ORA-06550: line 36, column 5:
PLS-00201: identifier 'E' mu
SQL> ed dd3;
SQL> @dd3;
41 /
SELECT trans, lock1, wait
*
ERROR at line 3:
ORA-06550: line 3, column 15:
PL/SQL: ORA-00904: invalid column name
ORA-06550: line 3, column 1:
PL/SQL: SQL Statement ignored
ORA-06550: line 2, column 9:
PLS-00341: declaration of cursor 'C1' is incomplete or malformed
ORA-06550: line 15, column 4:
PL/SQL: Item ignored
ORA-06550: line 20, column 15:
PLS-00320: the declaration of the type of this expression is incomplete or
malformed
ORA-06550: line 20, column 1:
PL/SQL: SQL Statement ignored
ORA-06550: line 22, column 22:
PLS-00320: the declaration of the type of this expression is incomplete or
malformed
ORA-06550: line 22, column 1:
PL/SQL: Statement ignored
ORA-06550: line 25, column 10:
PLS-00320: the declaration of the type of this expression is incomplete or
malformed
ORA-06550: line 25, column 1:
PL/SQL: Statement ignored
ORA-06550: line 28, column 10:
PLS-00320: the declaration of the type of this expression is incomplete or
malformed
ORA-06550: line 28, column 1:
PL/SQL: Statement ignored
SQL> ed dd3;
SQL> @dd3;
41 /
TRANS Lock wait
t1 x6 x2
t2 x4 x7
t4 x7
x7<-x7deadlock occured
no deadlock
PL/SQL procedure successfully completed.
SQL> ed dd4;
SQL> @dd4;
37 /
SELECT trans, loc, wait
*
ERROR at line 3:
ORA-06550: line 3, column 15:
PL/SQL: ORA-00904: invalid column name
ORA-06550: line 3, column 1:
PL/SQL: SQL Statement ignored
ORA-06550: line 2, column 9:
PLS-00341: declaration of cursor 'C1' is incomplete or malformed
ORA-06550: line 14, column 4:
PL/SQL: Item ignored
ORA-06550: line 19, column 15:
PLS-00320: the declaration of the type of this expression is incomplete or
malformed
ORA-06550: line 19, column 1:
PL/SQL: SQL Statement ignored
ORA-06550: line 21, column 22:
PLS-00320: the declaration of the type of this expression is incomplete or
malformed
ORA-06550: line 21, column 1:
PL/SQL: Statement ignored
ORA-06550: line 24, column 10:
PLS-00320: the declaration of the type of this expression is incomplete or
malformed
ORA-06550: line 24, column 1:
PL/SQL: Statement ignored
ORA-06550: line 27, column 10:
PLS-00320: the declaration of the type of this expression is incomplete or
malformed
ORA-06550: line 27, column 1:
PL/SQL: Statement ignored
SQL> ed dd4;
SQL> @dd4;
37 /
TRANS Lock wait
t2 x5
t4 x8 x5
t5 x3 x7
x5<-x5deadlock occured
PL/SQL procedure successfully completed.
SQL> ed dd1;
SQL> @dd1;
42 /
TRANS Lock wait
t1 x1 x8
t1 x6 x2
t2 x4 x7
t2 x5
t3 x2 x7
t4 x7
t4 x8 x5
t5 x3 x7
x5<-x5deadlock occured
x2<-x2deadlock occured
x7<-x7deadlock occured
x7<-x7deadlock occured
x7<-x7deadlock occured
x8<-x8deadlock occured
PL/SQL procedure successfully completed.
SQL>
DD1 :
declare
cursor c1 is
SELECT trans, loc, wait
FROM dd1;
type c_list is varray(20) of dd1.loc%type;
ll c_list:=c_list();
type c_list1 is varray(20) of dd1.wait%type;
l2 c_list1:=c_list1();
type c_list2 is varray(20) of dd1.trans%type;
t c_list:=c_list();
c integer := 0;
d integer :=0;
f integer :=0;
ss c1%rowtype;
begin
open c1;
dbms_output.put_line('TRANS '||' '||'Lock'||' '||'wait');
loop
fetch c1 into ss;
exit when c1%notfound;
c := c+1;
ll.extend;
ll(c) := ss.loc;
f := f+1;
t.extend;
t(f) := ss.trans;
d :=d+1;
l2.extend;
l2(d) := ss.wait;
dbms_output.put_line(ss.trans||' '||ss.loc||' '||ss.wait);
end loop;
for i in 1 .. c loop
for j in 1 .. d loop
if ( ll(i) = l2(j)) then
if(ll(i) != '-')then
dbms_output.put_line(ll(i)||'<-'||l2(j)||'deadlock occured');
end if;
end if;
end loop;
end loop;
end;
DD2:
declare
cursor c1 is
SELECT trans, loc, wait
FROM dd1
WHERE Site='s1';
type c_list is varray(10) of dd1.loc%type;
ll c_list:=c_list();
type c_list1 is varray(10) of dd1.wait%type;
l2 c_list1:=c_list1();
type c_list2 is varray(20) of dd1.trans%type;
t c_list:=c_list();
c integer := 0;
d integer :=0;
ss c1%rowtype;
begin
open c1;
dbms_output.put_line('TRANS '||' '||'Lock'||' '||'wait');
loop
fetch c1 into ss;
exit when c1%notfound;
dbms_output.put_line(ss.trans||' '||ss.loc||' '||ss.wait);
c := c+1;
ll.extend;
ll(c) := ss.loc;
d :=d+1;
l2.extend;
l2(d) := ss.wait;
end loop;
for i in 1 .. c loop
for j in 1 .. d loop
if ( ll(i) = l2(j)) then
dbms_output.put_line(ll(i)||'<-'||l2(j)||'deadlock occured');
end if;
end loop;
end loop;
end;
DD3:
declare
cursor c1 is
SELECT trans, loc, wait
FROM dd1
WHERE Site='s2';
type c_list is varray(10) of dd1.loc%type;
ll c_list:=c_list();
type c_list1 is varray(10) of dd1.wait%type;
l2 c_list1:=c_list1();
type c_list2 is varray(20) of dd1.trans%type;
t c_list:=c_list();
c integer := 0;
d integer :=0;
e integer :=0;
ss c1%rowtype;
begin
open c1;
dbms_output.put_line('TRANS '||' '||'Lock'||' '||'wait');
loop
fetch c1 into ss;
exit when c1%notfound;
dbms_output.put_line(ss.trans||' '||ss.loc||' '||ss.wait);
c := c+1;
ll.extend;
ll(c) := ss.loc;
d :=d+1;
l2.extend;
l2(d) := ss.wait;
end loop;
for i in 1 .. c loop
for j in 1 .. d loop
if ( ll(i) = l2(j)) then
dbms_output.put_line(ll(i)||'<-'||l2(j)||'deadlock occured');
end if;
end loop;
end loop;
if (e = 0) then
dbms_output.put_line('no deadlock ');
end if;
end;
DD4:
declare
cursor c1 is
SELECT trans, loc, wait
FROM dd1
WHERE Site='s3';
type c_list is varray(10) of dd1.loc%type;
ll c_list:=c_list();
type c_list1 is varray(10) of dd1.wait%type;
l2 c_list1:=c_list1();
type c_list2 is varray(20) of dd1.trans%type;
t c_list:=c_list();
c integer := 0;
d integer :=0;
ss c1%rowtype;
begin
open c1;
dbms_output.put_line('TRANS '||' '||'Lock'||' '||'wait');
loop
fetch c1 into ss;
exit when c1%notfound;
dbms_output.put_line(ss.trans||' '||ss.loc||' '||ss.wait);
c := c+1;
ll.extend;
ll(c) := ss.loc;
d :=d+1;
l2.extend;
l2(d) := ss.wait;
end loop;
for i in 1 .. c loop
for j in 1 .. d loop
if ( ll(i) = l2(j)) then
dbms_output.put_line(ll(i)||'<-'||l2(j)||'deadlock occured');
end if;
end loop;
Result:
Thus the Deadlock Detection Algorithm Distributed Database has been developed and
executed successfully.
OBJECT ORIENTED DATABASE – EXTENDED ENTITY RELATIONSHIP (EER)
Aim:
To design an Enhanced Entity Relationship model for University database and to write
Object Query Language (OQL) to manage the database.
SQL Query:
Creation and insertion use University go
------------------------------------------------------------------------------
----------Design and create the tables needed
-------------------------------
---------------------------------------------------------------------------
create table department
(
DeptID int identity(1,1) PRIMARY KEY,
DeptName varchar(10)
)
go
create table dbo.Roles
(
RoleID int not null identity(1,1) PRIMARY KEY,
RoleName varchar(30) not null
)
go
create table dbo.Gender
(
GenderID int not null identity(1,1) PRIMARY KEY,
Gender varchar(10) not null
)
go
create table dbo.StatusTable
(
ID int NOT NULL PRIMARY KEY,
[Status] varchar(10)
)
go
create table dbo.Users
(
UserID int NOT NULL identity(1,1) PRIMARY KEY,
UserName varchar(30),
[Address] varchar(100),
DATE:
EX.NO:
Phone varchar(20),
DeptID int FOREIGN KEY REFERENCES department(DeptID),
Gender int FOREIGN KEY REFERENCES Gender(GenderID),
RoleId int FOREIGN KEY REFERENCES Roles(RoleID),
SubRoleID int FOREIGN KEY REFERENCES SubRoles(SubRoleID),
StatusId int FOREIGN KEY REFERENCES StatusTable(ID)
)
go
create table dbo.SubRoles
(
SubRoleID int identity(1,1) PRIMARY KEY,
RoleID int FOREIGN KEY REFERENCES Roles(RoleID),
SubRoleName varchar(30)
)
go
----------------------------------------------------------------------
--------Insert Values into the tables----------------------------
-------------------------------------------------------------------
insert into dbo.department
values('CSE')
go
insert into dbo.department
values('IT')
go
insert into dbo.department
values('ECE')
go
insert into dbo.Roles
values('Employee')
go
insert into dbo.Roles
values('Student')
go
insert into dbo.Gender
values('Male')
go
insert into dbo.Gender
values('Female')
go
insert into dbo.StatusTable
values(1,'Active')
go
insert into dbo.StatusTable
values(2,'InActive')
go
insert into dbo.SubRoles
values(1,'Faculty')
go
insert into dbo.SubRoles
values(1,'Technicians')
go
insert into dbo.SubRoles
values(1,'Project associates')
go
insert into dbo.SubRoles
values(2,'Full Time')
go
insert into dbo.SubRoles
values(2,'Part Time')
go
insert into dbo.SubRoles
values(2,'Teaching Assistant')
go
insert into users values
('kalpana','2-Richie street, chennai',9840000000,1,2,2,2,1)
--ii. Display the Employee details
--a. Display General Employee details :
select
Username as [EmployeeName],
[Address] as [Address],
Phone as [Contact],
d.DeptName as [Department],
g.Gender as [Gender],
s.SubRoleName as [ROle],
st.Status as [Status]
from users u
inner join department d on d.DeptID = u.DeptID
inner join Gender g on g.GenderID = u.Gender
inner join SubRoles s on s.SubRoleID = u.SubRoleID
inner join StatusTable st on st.ID = u.StatusID
where u.RoleID = 1 --(Employee Role is filtered)
--b. Display Female Employee Details
select
Username as [EmployeeName],
[Address] as [Address],
Phone as [Contact],
d.DeptName as [Department],
g.Gender as [Gender],
s.SubRoleName as [ROle],
st.Status as [Status]
from users u
inner join department d on d.DeptID = u.DeptID
inner join Gender g on g.GenderID = u.Gender
inner join SubRoles s on s.SubRoleID = u.SubRoleID
inner join StatusTable st on st.ID = u.StatusID
where u.RoleID = 1 and u.Gender = 2 --(Employee Role and female gender
- filtered)
--iii. Display Student Details.
--a.Select General Student Details
select
Username as [EmployeeName],
[Address] as [Address],
Phone as [Contact],
d.DeptName as [Department],
g.Gender as [Gender],
s.SubRoleName as [ROle],
st.Status as [Status]
from users u
inner join department d on d.DeptID = u.DeptID
inner join Gender g on g.GenderID = u.Gender
inner join SubRoles s on s.SubRoleID = u.SubRoleID
inner join StatusTable st on st.ID = u.StatusID
where u.RoleID = 2 --(Student Role is filtered)
--b.Display only full time students
select
Username as [EmployeeName],
[Address] as [Address],
Phone as [Contact],
d.DeptName as [Department],
g.Gender as [Gender],
s.SubRoleName as [ROle],
st.Status as [Status]
from users u
inner join department d on d.DeptID = u.DeptID
inner join Gender g on g.GenderID = u.Gender
inner join SubRoles s on s.SubRoleID = u.SubRoleID
inner join StatusTable st on st.ID = u.StatusID
where u.RoleID = 2 and u.SubRoleID = 4--(Student Role and Full-time
SubRole - filtered)
iv Modify User details v Delete User details
--iv Modify Person details
--a.Update Phone number of a user
Update Users
set Phone = 9840202000
where userid = 1
--b.Update all the part time students as full time students and all
the full time students
--as part time students
Update Users
set SubRoleID = Case when SubRoleID = 4 then 5
when SubRoleID = 5 then 4
where SubRoleID in (4,5) --This condition avoids the control to check
in all the rows
v. Delete Person Details
a)Delete an User from the table
Delete from Users
where UserID = 2
b)Delete users if their status is inactive
Delete from users
where StatusID = 2
Result:
Thus the Object Oriented Database for Extended Entity Relationship (EER)
has been developed and executed successfully.
PARALLEL DATABASE UNIVERSITY COUNSELLING FOR
ENGINEERING COLLEGES
AIM:
To implement University Counselling for Engineering Colleges using Parallel Database.
Description:
A variety of hardware architectures allow multiple computers to share access to data, software,
or peripheral devices. A Parallel Database is designed to take advantage of such architectures by
running multiple instances which "share" a single physical database. In appropriate applications, a
parallel server can allow access to a single database by users on multiple machines, with increased
performance.
SQL Query:
The College details, Department details and Vacancy Details are maintained in 3
different sites. By using the information from these 3 sites, seats can be allocated to a student
using Parallel Query Processing.
1.CREATE TABLE hr.admin_emp_dept
PARALLEL COMPRESS
AS SELECT * FROM hr.employees
WHERE department_id = 10;
In this case, the PARALLEL clause tells the database to select an optimum number of parallel
execution servers when creating the table.
DATE:
EX.NO:
parallel query with intra- and inter-operation parallelism, consider a more complex query:
2.SELECT /*+ PARALLEL(employees 4) PARALLEL(departments 4)
USE_HASH(employees) ORDERED */
MAX(salary), AVG(salary) FROM employees, departments
WHERE employees.department_id = departments.department_id
GROUP BY employees.department_id;
RESULT:
Thus the Object Parallel Database fo r University Counselling for Engineering colleges
has been developed and executed successfully.
PARALLEL JOIN AND PARALLEL SORT
ALGORITHM
AIM:
To implement parallel join and parallel sort algorithms to get marks marks from
different colleges and publish 10 ranks for each discipline.
Description:
parallel join and parallel sort:
A parallel join is a method that combines rows from two tables using multi-threading
for sorting and match merging to create final output, the goal of which is to reduce the
total time required to complete the task. The Parallel Join facility can handle multiple
character columns, numeric columns or combinations of character and numeric columns
that are joined between pairs of tables. Numeric columns do not need to be of the same
width to act as a join key, but character columns must be of the same width in order to be a
join key.
Parallel Sort-Merge Method:
The parallel sort-merge join method first performs a parallel sort to order the data,
and then merges the sorted tables in parallel. During the merge, the facility concurrently
joins multiple rows from one table with the corresponding rows in the other table.
SQL Query:
The first parallel join example is a basic SQL query that creates a pair-wise join of
two Server tables, table1 and table2.
1.CREATE TABLE junk as
SELECT *
from path1.table1 a,
path1.table2 b
where a.i = b.i;
DATE:
EX.NO:
2.creates a table which is the result of union of two parallel joins and this example shows
how the sort merges are used for the joins.
CREATE TABLE junk as
SELECT *
from path1.table1 a, path1.table2 b
where a.i = b.i
UNION
SELECT *
from path1.dansjunk3
c, path1.dansjunk4 d
where c.i = d.i;
3. The Parallel Join Facility also includes enhancements for data summarization by
using GROUP BY technique. The following example shows the combined use of both
the parallel join and parallel GROUP BY methods.
CREATE TABLE junk as
SELECT a.c,
b.d, sum(b.e)
from
path1.table1a,
path1.table2b
Where
a.i =b.i
GROUP BY a.d, b.d;
RESULT:
Thus the Object Parallel Database fo r University Counselling for Engineering colleges
has been developed and executed successfully.
ACTIVE DATABASE – IMPLEMENTATION OF TRIGGERS & ASSERTIONS FOR
BANK DATABASE
Aim:
To create Triggers and Assertions for Bank Database handling deposits and loan and
for Admission Database handling seat allocation and vacancy position.
Description:
An active database is a database that includes an event-driven architecture which can respond
to conditions both inside and outside the database. Possible uses include security monitoring,
alerting, statistics gathering and authorization.
Most modern relational databases include active database features in the form of database
triggers.
Typically this behavior is described by event-condition-action
(ECA) rules.
ECA rules comprise three components: event E, condition C, and action A.
The event describes an external happening to which the rule may be able to respond.
The condition examines the context in which the event has taken place.
The action describes the task to be carried out by the rule if the relevant event has taken place
and the condition has evaluated to true. In sum, if the specified event E occurs and if the
condition C is true then the specified action A is executed.
Triggers:
A trigger is a PL/SQL block or a PL/SQL procedure that executes implicitly whenever a
particular event takes place. It can either be:
1. Application trigger: Fires whenever an event occurs with a particular application.
2. Database Trigger: Fires whenever a data event (such as DML) occurs on a schema or database.
Guidelines to Designing Triggers:
o Use triggers to guarantee that when a specific operation is performed, related actions are
performed.
o Only use database triggers for centralized, global operations that should be fired for the
triggering statement, regardless of which user or application issues the statement.
o Do not define triggers to duplicate or replace the functionality already built into the oracle
database. For example do not define trigger to implement integrity rules that can be done by
using declarative constraints.
Elements in a Trigger:
• Trigger timing
o For table: BEFORE, AFTER
o For view: INSTEAD OF
• Trigger event: INSERT, UPDATE, OR DELETE
• Table name: On table, view
• Trigger Type: Row or statement
• When clause: Restricting condition
DATE:
EX.NO:
• Trigger body: PL/SQL block
“Before triggers” execute the trigger body before the triggering DML event on a table.
These are frequently used to determine whether that triggering statement should be allowed to
complete. This situation enables you to eliminate unnecessary processing of the triggering
statement and it eventual rollback in cases where an exception is raised in the triggering action.
“After triggers” are used when the triggering statement is to be completed before the triggering
action and to perform a different action on the same triggering statement if a BEFORE trigger is
already present.
“Instead of Triggers” are used to provide a transparent way of modifying views that cannot be
modified directly through SQL DML statements because the view is not inherently modifiable.
You can write INSERT, UPDATE, and DELETE statements against the view. The INSTEAD
OF trigger works invisibly in the background performing the action coded in the trigger body
directly on the underlying tables.
Triggering user events:
INSERT
UPDATE
DELETE
Trigger Components:
Statement: The trigger body executes once for the triggering event. This is the default. A
statement trigger fires once, even if no rows are affected at all.
Row: The trigger body executes once for each row affected by the triggering event.
A row trigger is not executed if the triggering event affects no rows.
Trigger Body:
The trigger body is a PL/SQL block or a call to a procedure.
Syntax:
CREATE [OR REPLACE] TRIGGER trigger_name
Timing
Event1 [OR event2 OR event3]
ON table_name
Trigger_body
Assertions
An assertion is a predicate expressing a condition we wish the database to always satisfy.
Domain constraints, functional dependency and referential integrity are special forms of
assertion.
Where a constraint cannot be expressed in these forms, we use an assertion, e.g.
Ensuring the sum of loan amounts for each branch is less than the sum of all account
balances at the branch.
Ensuring every loan customer keeps a minimum of $1000 in an account.
An assertion in DQL-92 takes the form,
CREATE ASSERTION <assertion_name> CHECK predicate
Problem Statement:
Create triggers and assertions for Bank database handling deposits and loan and admission
database handling seat allocation and vacancy position. Design the above relational database
schema and implement the following triggers and assertions.
a. When a deposit is made by a customer, create a trigger for updating customers account and
bank account
Create or replace trigger t1 after insert on dep09
for each row
begin
if :new.damt>0 then
update cust09 set cbal=cbal+:new.damt where :new.ano=cust09.ano;
update bank09 set asset=asset+:new.damt where :new.brid=bank09.brid;
end if;
end;
b. When a loan is issued to the customer, create a trigger for updating customer’s loan account
and bank account.
Create or replace trigger t2 after insert on loan09
for each row
begin
if :new.lamt>0 then
update cust09 set cbal=cbal+:new.lamt where :new.ano=cust09.ano;
update bank09 set asset=asset-:new.lamt where :new.brid=bank09.brid;
dbms_output.put_line('customer and bank account updated');
end if;
end;
c. Create assertion for bank database so that the total loan amount does not exceed the total
balance in the bank.
create or replace assertion check (select sum(amt) from jloan where
jloan.name=jbranch.name)>=(select sum(amt) from jaccount where
jaccount.name=jbranch.name)
d. When an admission is made, create a trigger for updating the seat allocation details and
vacancy position.
Create or replace trigger t3 after insert on ad09
for each row
declare
a number(5):=0;
begin
select vp into a from vp09 where dept=:new.dept;
if a>0 then
update sa09 set dsa=dsa+1 where :new.dept=sa09.dept;
update vp09 set vp=vp-1 where :new.dept=vp09.dept;
end if;
dbms_output.put_line('Seat Allocation and Vacancy position updated');
end;
Create the following tables
Customer Table
USE [Bank]
GO
/****** Object: Table [dbo].[Customer] Script Date: 01/01/2014 18:27:59 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Customer](
[CNO] [int] NOT NULL,
[FirstName] [varchar](50) NULL,
[LastName] [varchar](50) NULL,
[ZipCode] [nvarchar](50) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
Data to be inserted
CNO FirstName LastName ZipCode
1 Shyam Sundar 6120
2 Somes Thani 60661
3 Aswin Arun 6677
4 Shankar S 7667
CustomerAccount
USE [Bank]
GO
/****** Object: Table [dbo].[CustomerAccount] Script Date: 01/01/2014 18:29:22 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[CustomerAccount](
[AccountNo] [int] NOT NULL,
[Location] [varchar](50) NULL,
[CNO] [int] NOT NULL,
[Amount] [decimal](18, 0) NULL,
[ApprovedLoanAmount] [decimal](18, 0) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
AccountNo Location CNO Amount ApprovedLoanAmount
1000 Chennai 1 1000 500
CustomerBankAccount
USE [Bank]
GO
/****** Object: Table [dbo].[CustomerBankAccount] Script Date: 01/01/2014 18:29:36
******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[CustomerBankAccount](
[CBNO] [int] NOT NULL,
[BankLocation] [nchar](10) NOT NULL,
[BankName] [varchar](50) NULL,
[Amount] [decimal](18, 0) NOT NULL,
[CNO] [int] NULL,
[Active] [int] NULL,
[LoanAmount] [decimal](18, 0) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
CBNO BankLocation BankName Amount CNO Active LoanAmount
2001 Chennai LLFC 1000 1 NULL 500
2002 Chennai LLFC 0 2 NULL NULL
2003 Chennai LLFC 0 3 NULL NULL
CustomerLoanAccount
USE [Bank]
GO
/****** Object: Table [dbo].[CustomerLoanAccount] Script Date: 01/01/2014 18:29:43
******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[CustomerLoanAccount](
[CLNO] [int] NOT NULL,
[LoanName] [varchar](50) NOT NULL,
[Location] [varchar](50) NOT NULL,
[Amount] [decimal](18, 0) NOT NULL,
[CNO] [int] NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
CLNO LoanName Location Amount CNO
3001 0 Chennai 500 1
3002 0 Chennai 0 2
3003 0 Chennai 0 3
Bandmaster
USE [Bank]
GO
/****** Object: Table [dbo].[BankMaster] Script Date: 01/01/2014 18:29:57 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[BankMaster](
[BankID] [int] NOT NULL,
[Name] [varchar](50) NOT NULL,
[Allocated] [int] NOT NULL,
[Vacancy] [int] NOT NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
BankID Name Allocated Vacancy
1 LLFC 4 196
The scenarios are explained using the following triggers
When a deposit is made by a customer, create a trigger for updating
customers account and bank account
USE [Bank]
GO
/****** Object: Trigger [dbo].[CustomerAccountTrigger] Script Date: 01/01/2014 18:21:42
******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[CustomerAccountTrigger]
ON [dbo].[CustomerAccount]
AFTER INSERT,UPDATE
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
Declare @amount int
Declare @cno int
select @amount=amount,@cno=CNO from inserted
SET NOCOUNT ON;
update CustomerBankAccount set Amount=@amount where CNO=@cno
END
Create assertion for bank database so that the total loan amount does not
exceed
the total balance in the bank.
When a loan is issued to the customer, create a trigger for updating
customer’s loan account and bank account.
USE [Bank]
GO
/****** Object: Trigger [dbo].[CustomerLoanTrigger] Script Date: 01/01/2014 18:22:13
******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[CustomerLoanTrigger]
ON [dbo].[CustomerAccount]
AFTER INSERT,UPDATE
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
Declare @loanamount as int
Declare @cno as int
Declare @balance as decimal(18,0)
select @loanamount=ApprovedLoanAmount,@cno=CNO,@balance=Amount from
inserted
select @balance=Amount from CustomerBankAccount where CNO=@cno
if(@loanamount>@balance)
Begin
RAISERROR ('Loan Amount should not exceed the balance!',10, 1)
ROLLBACK
end
SET NOCOUNT ON;
update CustomerBankAccount set loanamount=@loanamount where CNO=@cno
update CustomerLoanAccount set Amount=@loanamount where CNO=@cno
END
When an admission is made, create a trigger for updating the seat allocation
details and vacancy position.
USE [Bank]
GO
/****** Object: Trigger [dbo].[BankMasterTrigger] Script Date: 01/01/2014 18:21:09
******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[BankMasterTrigger]
ON [dbo].[Customer]
AFTER INSERT,UPDATE
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
Declare @count as int
select @count=COUNT(1) from inserted
SET NOCOUNT ON;
if(@count>0)
Begin
update BankMaster set Allocated=Allocated+1,Vacancy=Vacancy-1
End
END
RESULT:
Thus the Active Database fo r Implementation of Triggers & Assertions for Bank
database has been developed and executed successfully.
DEDUCTIVE DATABASE
AIM:
To construct a knowlegde database for kinship domain(family relations) with
facts.Extract the following a relations using rules.Parent, Sibling, Brother,Sister, Child,
Daughter, Son, Spouse ,Wife, Husband, Grandparent, Grandchild, Cousin, Aunt and Uncle.
PROLOG:
Prolog stands for programming logic, Prolog is a high-level programming language based on
formal logic. Unlike traditional programing language that are based on performing sequences of
commands,Prolog is based on defining and then solving logical formulas. Prolog is sometimes
called a declarative language or a rule-based language because its programs consists of a list of
facts and rules. Prolog is used widely for artificial intelligence applications, particularly expert
systems.
PROCEDURE:
Start>>All programs>>SWI-Prolog>>Prolog
DATE:
EX.NO:
File>>New
Type the filename and select “save”
Type the coding in the window and Save it
Coding:
male(kasthuriraja).
male(dhanush).
male(selva).
male(yatra).
male(linga).
female(vijaya).
female(aishwarya).
female(geethanjali).
female(anjali).
parent(kasthuriraja,dhanush).
parent(vijaya,dhanush).
parent(kasthuriraja,selva).
parent(vijaya,selva).
parent(dhanush,linga).
parent(aishwarya,linga).
parent(dhanush,yatra).
parent(aishwarya,yatra).
parent(selva,anjali).
parent(geethanjali,anjali).
father(F,X):-male(F),parent(F,X).
mother(M,X):-female(M),parent(M,X).
sibling(X,Y):-father(Z,X),father(Z,Y).
child(C,P):-parent(P,C).
brother(B,X):-male(B),sibling(B,X).
sister(S,X):-female(S),sibling(S,X).
daughter(D,X):-female(D),parent(X,D).
son(S,X):-male(S),parent(X,S).
spouse(X,Y):-child(Z,X),child(Z,Y).
wife(W,X):-female(W),male(X),spouse(W,X).
husband(H,X):-male(H),female(X),spouse(H,X).
grandfather(GP,GC):-male(GP),parent(GP,X),parent(X,GC).
grandmother(GP,GC):-female(GP),parent(GP,X),parent(X,GC).
grandchild(GC,GP):-grandmother(GP,GC).
grandchild(GC,GP):-grandfather(GP,GC).
aunt(A,X):-female(A),father(Z,X),brother(Z,A).
aunt(A,X):-female(A),mother(Z,X),sister(Z,A).
uncle(U,X):-male(U),father(Z,X),brother(Z,U).
uncle(U,X):-male(U),mother(Z,X),sister(Z,U).
uncle(U,X):-male(U),father(Z,X),sister(S,Z),husband(U,S).
cousin(X,Y):-parent(Z,X),parent(P,Y),sibling(Z,P).
File>Save As
Enter the filename with extension .pl
Compile the code
Compile>>Compile buffer
Execute with the commands
; at the end of statement indicates to display next valid data. . at the end of statements will end
the display of futher data even if available.
To Edit existing file “File>>Edit>>select the name of the file to be edited”.Make the
modification and use “File>>Save As” to save the file and Click “File>>Reload Modified Files”
to load the currently saved document and check the commands.
RESULT:
Thus the knowledge database for kinship domain with facts has been created and the
rules have been executed.
STUDY AND WORKING OF WEKA TOOL
AIM:
To study and work with WEKA tool classification and clustering algorithms using training
dataset and test dataset.
Description:
Form the different clusters and classify the given data into different classes with the help
of WEKA tool.
RID Age Income Student Credit_rating Class:buys_computer
1 youth high no fair no
2 youth high no excellent no
3 middle_aged high no fair yes
4 senior medium no fair yes
5 senior low yes fair yes
6 senior low yes excellent no
7 middle_aged low yes excellent yes
8 youth medium no fair no
9 youth low yes fair yes
10 senior medium yes fair yes
11 youth medium yes excellent yes
12 middle_aged medium no excellent yes
13 middle_aged high yes fair yes
14 senior medium no excellent no
DATE:
EX.NO:
Snapshots:
Result
Thus the weka experiment has been studied, clustering and classification is done and executed
successfully.
QUERY PROCESSING – IMPLEMENTATION OF AN EFFICIENT QUERY
OPTIMIZER
AIM:
To implement Query Optimizer with Relational Algebraic expression construction and
execution plan generation for choosing an efficient execution strategy for processing the
given query
Description:
Creation of Employee Database
SQL> Create table Empdet ( Empname varchar(20), empid varchar(10), city varchar(20),
Country varchar(20), Branch varchar(30), Experience number(10));
SQL> Select * from Empdet;
EMPNAME EMPID CITY COUNTRY BRANCH EXPERIENCE
Ansar Ali 1420001 San Francisco America San
Francisco
10
Divya 1420002 Perth Australia Perth 4
Amrutha 1420003 New Delhi India New Delhi 8
Sharuk 1455007 London UK London 2
Amir khan 1456032 Chennai India Chennai 6
Anushka 1465603 Barclona America Barclona 1
Priyanka 1450007 London UK London 3
a)Select empid, empname fom employee where experience>5
SQL> Select empid, empname from Empdet where Experience >5;
EMPID EMPNAME
1420001 Ansar Ali
1420003 Amrutha
1456032 Amir khan
DATE:
EX.NO:
b)Find all managers working at London Branch
SQL> Select * from Empdet where Branch='London';
EMPNAME EMPID CITY COUNTRY BRANCH EXPERIENCE
Sharuk 1455007 London UK London 2
Priyanka 1450007 London UK London 3
Result
Thus the Query Processing fo r Implementation of an Efficient Query Optimizer is done and
executed successfully.
DESIGNING XML SCHEMA FOR COMPANY DATABASE
Aim:
To design a XML Schema and to implement the queries using XQuery and XPath.
Description:
An XML Schema
Describes the structure of an XML document.
1. XSD - The <schema> Element
The <schema> element is the root element of every XML Schema.
The <schema> Element
The <schema> element is the root element of every XML Schema:
<?xml version="1.0"?>
<xs:schema>
...
...
</xs:schema>
A simple element is an XML element that can contain only text. It cannot contain any other
elements or attributes.
2. Defining a Simple Element
The syntax for defining a simple element is:
<xs:element name="xxx" type="yyy"/>
where xxx is the name of the element and yyy is the data type of the element.
XML Schema has a lot of built-in data types. The most common types are:
xs:string
xs:decimal
xs:integer
xs:boolean
xs:date
xs:time
3. XSD Attributes
All attributes are declared as simple types.
What is an Attribute?
Simple elements cannot have attributes. If an element has attributes, it is considered to be of a
complex type. But the attribute itself is always declared as a simple type.
How to Define an Attribute?
The syntax for defining an attribute is:
<xs:attribute name="xxx" type="yyy"/>
where xxx is the name of the attribute and yyy specifies the data type of the attribute.
XML Schema has a lot of built-in data types. The most common types are:
xs:string
xs:decimal
xs:integer
DATE:
EX.NO:
xs:boolean
xs:date
xs:time
XPath
XPath is a language for finding information in an XML document.
1. Nodes
In XPath, there are seven kinds of nodes: element, attribute, text, namespace, processinginstruction,
comment, and document nodes.
XML documents are treated as trees of nodes. The topmost element of the tree is called the root
element.
XPath uses path expressions to select nodes in an XML document. The node is selected by
following a path or steps. The most useful path expressions are listed below:
Expression Description
nodename Selects all nodes with the name "nodename"
/ Selects from the root node
// Selects nodes in the document from the current node that match the
selection no matter where they are
. Selects the current node
.. Selects the parent of the current node
@ Selects attributes
XQuery
XQuery is to XML what SQL is to database tables.
XQuery was designed to query XML data.
XQuery uses functions to extract data from XML documents.
1.Path Expressions
XQuery uses path expressions to navigate through elements in an XML document.
The following path expression is used to select all the title elements in the "books.xml" file:
doc("books.xml")/bookstore/book/title
2.Predicates
XQuery uses predicates to limit the extracted data from XML documents.
The following predicate is used to select all the book elements under the bookstore element that
have a price element with a value that is less than 30:
doc("books.xml")/bookstore/book[price<30]
The expression above will select all the title elements under the book elements that are under the
bookstore element that have a price element with a value that is higher than 30.
The following FLWOR expression will select exactly the same as the path expression above:
for $x in doc("books.xml")/bookstore/book
where $x/price>30
return $x/title
The result will be:
<title lang="en">XQuery Kick Start</title>
<title lang="en">Learning XML</title>
With FLWOR you can sort the result:
for $x in doc("books.xml")/bookstore/book
where $x/price>30
order by $x/title
return $x/title
FLWOR is an acronym for "For, Let, Where, Order by, Return".
The for clause selects all book elements under the bookstore element into a variable called $x.
The where clause selects only book elements with a price element with a value greater than 30.
The order by clause defines the sort-order. Will be sort by the title element.
The return clause specifies what should be returned. Here it returns the title elements.
Implement a storage structure for storing XML database in Oracle 9i
Oracle XML DB provides you with the ability to fine tune how XML documents will be stored
and processed in Oracle9i database
Steps:
1.Design an XML Schema for the given company database.
2. Write XML file to store Department, Employee and Project details.
3. Write the queries using Xquery and Xpath and execute it using XQuery Engine.
4. Implement a storage structure for storing XML database in Oracle 9i.
Problem Statement:
Design XML Schema for the given company database
Department ( deptName, deptNo, deptManagerSSN, deptManagerStartDate,deptLocation )
Employee ( empName, empSSN, empSex, empSalary, empBirthDate,empDeptNo,
empSupervisorSSN, empAddress, empWorksOn)
Project ( projName, projNo, projLocation, projDeptNo, projWorker )
//project.xml
<?xml version="1.0"?>
<projects>
<project>
<projName>Web Mining</projName>
<projNo>111</projNo>
<projLoc>Chennai</projLoc>
<projDeptNo>1</projDeptNo>
<projWorkers>
<projWorker>
<name>dhanu</name>
<name>jeyaraman</name>
</projWorker>
</projWorkers>
</project>
<project>
<projName>Cloud Computing</projName>
<projNo>112</projNo>
<projLoc>Chennai</projLoc>
<projDeptNo>1</projDeptNo>
<projWorkers>
<projWorker>
<name>arthi</name>
<name>jeyaraman</name>
</projWorker>
</projWorkers>
</project>
<project>
<projName>BusinessProcess</projName>
<projNo>221</projNo>
<projLoc>Chennai</projLoc>
<projDeptNo>2</projDeptNo>
<projWorkers>
<projWorker>
<name>dhanushya</name>
<name>J</name>
</projWorker>
</projWorkers>
</project>
<project>
<projName>KnowledgeProcess</projName>
<projNo>222</projNo>
<projLoc>Chennai</projLoc>
<projDeptNo>2</projDeptNo>
<projWorkers>
<projWorker>
<name>dhanu</name>
</projWorker>
</projWorkers>
</project>
</projects>
//department.xml
<?xml version="1.0"?>
<departments>
<department>
<deptName>Research</deptName>
<deptNo>1</deptNo>
<deptMgrSSN>11</deptMgrSSN>
<deptMgrStartDate>1/1/2000</deptMgrStartDate>
<deptLocation>Chennai</deptLocation>
</department>
<department>
<deptName>Outsourcing</deptName>
<deptNo>2</deptNo>
<deptMgrSSN>22</deptMgrSSN>
<deptMgrStartDate>1/1/2001</deptMgrStartDate>
<deptLocation>Hyderabad</deptLocation>
</department>
</departments>
//employee.xml
<?xml version="1.0"?>
<employees>
<employee>
<empName>arthi</empName>
<empSSN>11</empSSN>
<empSex>Female</empSex>
<empSalary>900000</empSalary>
<empBirthDate>1-3-89</empBirthDate>
<empDeptNo>1</empDeptNo>
<empAddress>kknagarchennai</empAddress>
<empWorksOn>Web Mining</empWorksOn>
</employee>
<employee>
<empName>maliga</empName>
<empSSN>12</empSSN>
<empSex>Female</empSex>
<empSalary>300000</empSalary>
<empBirthDate>2-3-89</empBirthDate>
<empDeptNo>1</empDeptNo>
<empSupSSN>11</empSupSSN>
<empAddress>annanagarchennai</empAddress>
<empWorksOn>Cloud Computing</empWorksOn>
</employee>
<employee>
<empName>sindhu</empName>
<empSSN>13</empSSN>
<empSex>male</empSex>
<empSalary>300000</empSalary>
<empBirthDate>4-9-89</empBirthDate>
<empDeptNo>1</empDeptNo>
<empSupSSN>11</empSupSSN>
<empAddress>annanagarchennai</empAddress>
<empWorksOn>Cloud Computing</empWorksOn>
</employee>
<employee>
<empName>gg</empName>
<empSSN>14</empSSN>
<empSex>male</empSex>
<empSalary>300000</empSalary>
<empBirthDate>6-9-88</empBirthDate>
<empDeptNo>1</empDeptNo>
<empSupSSN>11</empSupSSN>
<empAddress>annanagarchennai</empAddress>
<empWorksOn>Web Mining</empWorksOn>
</employee>
<employee>
<empName>sruthi</empName>
<empSSN>22</empSSN>
<empSex>Female</empSex>
<empSalary>900000</empSalary>
<empBirthDate>3-3-89</empBirthDate>
<empDeptNo>2</empDeptNo>
<empAddress>T nagarchennai</empAddress>
<empWorksOn>BusinessProcess</empWorksOn>
</employee>
<employee>
<empName>dhanuhsya</empName>
<empSSN>23</empSSN>
<empSex>male</empSex>
<empSalary>300000</empSalary>
<empBirthDate>7-9-1992</empBirthDate>
<empDeptNo>2</empDeptNo>
<empSupSSN>22</empSupSSN>
<empAddress>T nagarchennai</empAddress>
<empWorksOn>BusinessProcess</empWorksOn>
</employee>
<employee>
<empName>dhanush</empName>
<empSSN>24</empSSN>
<empSex>Female</empSex>
<empSalary>400000</empSalary>
<empBirthDate>3-3-90</empBirthDate>
<empDeptNo>2</empDeptNo>
<empSupSSN>22</empSupSSN>
<empAddress>kknagarchennai</empAddress>
<empWorksOn>KnowledgeProcess</empWorksOn>
</employee>
<employee>
<empName>dhanu</empName>
<empSSN>25</empSSN>
<empSex>Female</empSex>
<empSalary>300000</empSalary>
<empBirthDate>3-5-90</empBirthDate>
<empDeptNo>2</empDeptNo>
<empSupSSN>22</empSupSSN>
<empAddress>annanagarchennai</empAddress>
<empWorksOn>KnowledgeProcess</empWorksOn>
</employee>
</employees>
Query 1:
Retrieve the department name, manager name, and manager salary for every department
let $d1:=doc("D:/department.xml")
let $d2:=doc("D:/employee.xml")
for $p1 in $d1/departments/department
for $p2 in $d2/employees/employee
where $p1/deptMgrSSN=$p2/empSSN
return<Result><dept>{$p1/deptName}</dept><mgrName>{$p2/empName}
</mgrName><mgrSal>{$p2/empSalary}</mgrSal></Result>
OUTPUT FOR QUERY1:
<Result>
<dept>
<deptName>Research</deptName>
</dept>
<mgrName>
<empName>arthi</empName>
</mgrName>
<mgrSal>
<empSalary>900000</empSalary>
</mgrSal>
</Result>
<Result>
<dept>
<deptName>Outsourcing</deptName>
</dept>
<mgrName>
<empName>sruthi</empName>
</mgrName>
<mgrSal>
<empSalary>900000</empSalary>
</mgrSal>
</Result>
Query 2:
Retrieve the employee name, supervisor name and employee salary for each employee who
works in the Research Department.
let $d1:=doc(“D:/employee.xml")
let $d2:=doc("D:/department.xml")
let $r:=$d2/departments/department[deptName="Research"]
let $sup:=$d1/employees/employee[empSSN=$r/deptMgrSSN]
for $p1 in $d1/employees/employee
where $p1/empDeptNo=$r/deptNo
return<Result><eName>{$p1/empName}</eName><supName>{$sup/empName}
</supName><empSal>{$p1/empSalary}</empSal></Result>
OUTPUT FOR QUERY2:
<Result>
<eName>
<empName>arthi</empName>
</eName>
<supName>
<empName>arthi</empName>
</supName>
<empSal>
<empSalary>900000</empSalary>
</empSal>
</Result>
<Result>
<eName>
<empName>maliga</empName>
</eName>
<supName>
<empName>arthi</empName>
</supName>
<empSal>
<empSalary>300000</empSalary>
</empSal>
</Result>
<Result>
<eName>
<empName>sindhu</empName>
</eName>
<supName>
<empName>arthi</empName>
</supName>
<empSal>
<empSalary>300000</empSalary>
</empSal>
</Result>
<Result>
<eName>
<empName>gg</empName>
</eName>
<supName>
<empName>arthi</empName>
</supName>
<empSal>
<empSalary>300000</empSalary>
</empSal>
</Result>
Query 3:
Retrieve the project name, controlling department name, number of employees and total hours
worked per week on the project for each project.
let $d1:=doc("D:/department.xml")
let $d2:=doc("D:/project.xml")
for $p1 in $d2/projects/project
let $dep:=$d1/departments/department[deptNo=$p1/projDeptNo]
return<Result><projName>{$p1/projName}</projName><depName>{$dep/deptName}
</depName><workers>{count($p1/projWorkers/projWorker/name)}</workers></Result>
OUTPUT FOR QUERY3:
<projName>
<projName>Web Mining</projName>
</projName>
<depName>
<deptName>Research</deptName>
</depName>
<workers>2</workers>
</Result>
<Result>
<projName>
<projName>Cloud Computing</projName>
</projName>
<depName>
<deptName>Research</deptName>
</depName>
<workers>2</workers>
</Result>
<Result>
<projName>
<projName>BusinessProcess</projName>
</projName>
<depName>
<deptName>Outsourcing</deptName>
</depName>
<workers>2</workers>
</Result>
<Result>
<projName>
<projName>KnowledgeProcess</projName>
</projName>
<depName>
<deptName>Outsourcing</deptName>
</depName>
<workers>1</workers>
</Result>
Query 4:
Retrieve the project name, controlling department name, number of employees and total hours
worked per week on the project for each project with more than one employee working on it.
let $d1:=doc("D:/department.xml")
let $d2:=doc("D:/project.xml")
for $p1 in $d2/projects/project
let $dep:=$d1/departments/department[deptNo=$p1/projDeptNo]
where count($p1/projWorkers/projWorker/name)>1
return<Result><projName>{$p1/projName}</projName><depName>{$dep/deptName}
</depName><workers>{count($p1/projWorkers/projWorker/name)}</workers></Result>
OUTPUT FOR QUERY4:
<Result>
<projName>
<projName>Web Mining</projName>
</projName>
<depName>
<deptName>Research</deptName>
</depName>
<workers>2</workers>
</Result>
<Result>
<projName>
<projName>Cloud Computing</projName>
</projName>
<depName>
<deptName>Research</deptName>
</depName>
<workers>2</workers>
</Result>
<Result>
<projName>
<projName>BusinessProcess</projName>
</projName>
<depName>
<deptName>Outsourcing</deptName>
</depName>
<workers>2</workers>
</Result>
Query 5:
Implement a storage structure for storing XML database
create table dept(deptno number(1),department xmltype);
insert into dept(deptno,department)values(1,XMLTYPE('
<dept>
<deptname>mech</deptname>
<deptmgrssn>1</deptmgrssn>
<deptmgrsd>17/10/2012</deptmgrsd>
<deptloc>chennai</deptloc>
</dept>')
);
select * from temproject;
DEPTNO
----------
PROJECT
--------------------------------------------------------------------------------
1
<project>
<name>ASp.net</name>
<no>1</no>
<location>chennai</location>
2
<project>
DEPTNO
----------
PROJECT
--------------------------------------------------------------------------------
<name>java</name>
<no>5</no>
<location>chennai</location>
<worker>
xQuery.html
<html>
<body>
<h1>Bookstore</h1>
<ul>
{
for $x in doc("books.xml")/bookstore/book
order by $x/title
return <li>{data($x/title)}. Category: {data($x/@category)}</li>
}
</ul>
</body>
</html>
PROCEDURE:
1. Type all project,employee and project and store it in their separate .xml files(eg: store it
in location D:\
2. Double click Basex.jar in BaseX folder
3. Goto Editor tabnew
4. Click file1 taband type/copy the query (copy it from Lab ex document)
5. Click save button give name as query1.xq
6. Then press run(green color button) result can be viewed if successful
7. Then add +tab which is nearer to query1.xq, type the next query and save as query2.xq.
8. Continue for as much query
Result
Thus the design a XML Schema and to implement the queries using XQuery and XPath.
is done and executed successfully.
AIM:
To develop and write SQL queries for a distributed database of BookStore at four
sites S1, S2, S3 and S4. The Bookstores are divided into four sites by their ZIP codes.
Distributed Database Design
Description:
A distributed database is a database in which storage devices are not all attached to a
common processing unit such as the CPU, controlled by a distributed database management
system. (together sometimes called a distributed database system). It may be stored in multiple
computers, located in the same physical location; or may be dispersed over a network of
interconnected computers. Unlike parallel systems, in which the processors are tightly coupled
and constitute a single database system, a distributed database system consists of loosely-coupled
sites that share no physical components.
There are two principal approaches to store a relation r in a distributed database system:
A) Replication
B) Fragmentation/Partitioning
A) Replication: In replication, the system maintains several identical replicas of the same relation
r in different sites.
Data is more available in this scheme.
Parallelism is increased when read request is served.
Increases overhead on update operations as each site containing the replica
needed to be updated in order to maintain consistency.
Multi-datacenter replication provides geographical diversity:
B) Fragmentation: The relation r is fragmented into several relations r1, r2, r3....rn in such a way
that the actual relation could be reconstructed from the fragments and then the fragments are
scattered to different locations. There are basically two schemes of fragmentation:
Horizontal fragmentation - splits the relation by assigning each tuple of r to one or
more fragments.
Vertical fragmentation - splits the relation by decomposing the schema R of
relation r.
Implementing distributed databases using SQL Server 2005
Linked servers provide SQL Server the ability to access data from remote data sources. Using
these mechanisms, we can issue queries, perform data modifications and execute remote
procedures. We can use the T-SQL function OPENROWSET to query a remote data source
without a linked server.
DATE:
EX.NO:
Steps:
1. Create Horizontal Partition:
First partition a table horizontally. In designing a partitioning scheme, it must be clear what
data belongs to each member table. The original table is replaced with several smaller
member tables. Each member table has the same number of columns as the original table,
and each column has the same attributes as the corresponding column in the original table,
such as data type, size, and collation. By using a distributed partitioned view, each member
table is on a separate member server. For the greatest location transparency, the name of the
member databases should be the same on each member server, although this is not
required.For
example: Server1.CustomerDB, Server2.CustomerDB, Server3.CustomerDB.
1.1 Creating Member Tables
Design the member tables so that each table stores a horizontal slice of the original table based
on a range of key values. The ranges are based on the data values in a partitioning column. The
range of values in each member table is enforced by a CHECK constraint on the partitioning
column, and ranges cannot overlap.The CHECK constraint for this table is the following:
-- On Server1:
CREATE TABLE Customers_33
(CustomerID INTEGER PRIMARY KEY
CHECK (CustomerID BETWEEN 1 AND 32999),
... -- Additional column definitions)
Similarly create a member table on the other server instances.
2. Create Linked Server:
The servers that can be referenced in queries are called linked servers. A linked server is any
data source that can be accessed using OLE DB
– It can be another SQL Server or
– A different database server (such as Oracle) or
– A simpler data source, such as a file (Excel, Access)
Create a linked server to another SQL Server instance using the T-SQL procedure
sp_addlinkedserver. The syntax of sp_addlinkedserver is
EXECsp_addlinkedserver
[ @server= ] 'server'
[ ,[ @srvproduct= ] 'product_name'
[ , [ @provider= ] 'provider_name'
[ , [ @datasrc= ] 'data_source'
[ , [ @location= ] 'location' ]
[ , [ @provstr= ] 'provider_string'
[ , [ @catalog= ] 'catalog' ]
And each parameter is described as
Parameter Description
server Local name used for the linked server.
product_name Product name of the OLE DB data source. For SQL Server instances, the
product_name is 'SQL Server'.
provider_name
This is the unique programmatic identifier for the OLE DB provider. When not
specified, the provider name is the SQL Server data source. The explicit
provider_name for SQL Server is SQLNCLI (for Microsoft SQL Native Client
OLE DB Provider).
data_source This is the data source as interpreted by the OLE DB provider.
location The location interpreted by the OLE DB provider.
provider_string The connection string specific to the OLE DB provider.
catalog This varies from the database systems.
3. Add linked server definitions on each member server that contains the connection information
required to run distributed queries on the other member servers. This gives a distributed
partitioned view access to data on the other servers.
4. Defining Distributed Partition Views:
After you create the member tables, you define a distributed partitioned view on each member
server, with each view having the same name. This enables queries that reference the distributed
partitioned view name to run on one or more of the member servers. The system operates as if a
copy of the original table is on each member server, but each server has only a member table and
a distributed partitioned view.
Create the following distributed partitioned view:
CREATE VIEW Customers AS select statement
To execute queries on the remote instance, Distributed queries referring to linked server name is
written by using the following syntax
[linked_server_name].[catalog].[schema].[object_name]
Create a distributed partitioned view on other server instances also.
Queries:
1. Insert and Display details in each table.
insert into <tablename> values(list of values);
select *from <tablename>;
SELECT * FROM OPENQUERY (Server1, 'SELECT * FROM bookstore.dbo.Books_1')
2. Find the total number of books in stock where price is between $15 and $55.
select sum(totalstock) 'Total Books' from BooksView where price between 25 and 100
3. Update the book price of book No=1234 from $45 to $55 at site S3.
update openquery(Server2,'select price from bookstore.dbo.Books_1 where ISBN=45') set
price=100
(1 row(s) affected)
4. Find total number of book at site S2.
select *from openquery(cs03c025,'select sum(totalstock) from bookstore.dbo.Books')
Create three databases names S1,S2,S3,S4.
Create the following tables in all of them
USE [S1]
GO
/****** Object: Table [dbo].[Books] Script Date: 3/8/2014 4:10:30 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Books](
[ISBN] [uniqueidentifier] NOT NULL,
[PrimaryAuthor] [varchar](50) NULL,
[Topic] [varchar](50) NULL,
[TotalStock] [int] NULL,
[Price] [decimal](18, 0) NULL,
CONSTRAINT [PK_Books] PRIMARY KEY CLUSTERED
(
[ISBN] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY
= OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
USE [S4]
GO
/****** Object: Table [dbo].[BookStore] Script Date: 3/8/2014 3:07:23 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[BookStore](
[StoreNo] [uniqueidentifier] NOT NULL,
[City] [varchar](50) NULL,
[State] [varchar](50) NULL,
[Zip] [varchar](50) NULL,
[InventoryValue] [decimal](18, 0) NULL,
CONSTRAINT [PK_BookStore] PRIMARY KEY CLUSTERED
(
[StoreNo] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY
= OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
USE [S4]
GO
drop table [Stock]
/****** Object: Table [dbo].[Stock] Script Date: 3/8/2014 3:07:50 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Stock](
[StoreNo] [uniqueidentifier] NOT NULL,
[ISBN] [uniqueidentifier] NOT NULL,
[Qty] [int] NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[Stock] WITH CHECK ADD CONSTRAINT [FK_Stock_BookStore]
FOREIGN KEY([StoreNo])
REFERENCES [dbo].[BookStore] ([StoreNo])
GO
ALTER TABLE [dbo].[Stock] CHECK CONSTRAINT [FK_Stock_BookStore]
GO
--Insert and Display details in each table
insert into s1.dbo.Books values(newid(),'Sujatha','Einthir manithan',1,12);
insert into s2.dbo.Books values(newid(),'Sujatha','Sample',1,30);
insert into s3.dbo.Books values(newid(),'Sujatha','Lalgudi',10,40);
insert into s4.dbo.Books values(newid(),'Sujatha','Kanini',5,300);
select * from s1.dbo.Books
select * from s2.dbo.Books
select * from s3.dbo.Books
select * from s4.dbo.Books
insert into s1.dbo.BookStore values(newid(),'Chicago','IL','60661',10000);
insert into s2.dbo.BookStore values(newid(),'Boston','MA','2233',30000);
insert into s3.dbo.BookStore values(newid(),'Albany','NY','1134',60000);
insert into s4.dbo.BookStore values(newid(),'LasVegas','CA','5677',80000);
select * from s1.dbo.BookStore
select * from s2.dbo.BookStore
select * from s3.dbo.BookStore
select * from s4.dbo.BookStore
insert into s1.dbo.stock values((select StoreNo from s1.dbo.BookStore where City='Chicago'),
(select top 1 ISBN from s1.dbo.Books bs where bs.Topic='Einthir manithan'),12);
insert into s2.dbo.stock values((select StoreNo from s1.dbo.BookStore where City='Boston'),
(select top 1 ISBN from s1.dbo.Books where topic='Sample'),12);
insert into s3.dbo.stock values((select StoreNo from s1.dbo.BookStore where City='Albany'),
(select top 1 ISBN from s1.dbo.Books where topic='Lalgudi'),12);
insert into s4.dbo.stock values((select StoreNo from s1.dbo.BookStore where City='LasVegas'),
(select top 1 ISBN from s1.dbo.Books where topic='Kanini'),12);
--details from books
select * from s1.dbo.Books union
select * from s2.dbo.Books union
select * from s3.dbo.Books union
select * from s4.dbo.Books
--details from bookstore
select * from s1.dbo.BookStore union
select * from s2.dbo.BookStore union
select * from s3.dbo.BookStore union
select * from s4.dbo.BookStore
--Details from stock
select * from s1.dbo.Stock union
select * from s2.dbo.Stock union
select * from s3.dbo.Stock union
select * from s4.dbo.Stock
--Find the total number of books in stock where price is between $15 and $55
Create view BooksView as
(
select * from s1.dbo.Books union
select * from s2.dbo.Books union
select * from s3.dbo.Books union
select * from s4.dbo.Books
)
select * from BooksView
Create view BookStoreView as
(
select * from s1.dbo.BookStore union
select * from s2.dbo.BookStore union
select * from s3.dbo.BookStore union
select * from s4.dbo.BookStore
)
select * from BookStoreView
Create view StockView as
(
select * from s1.dbo.Stock union
select * from s2.dbo.Stock union
select * from s3.dbo.Stock union
select * from s4.dbo.Stock
)
--Find the total number of books in stock where price is between $15 and $55
select sum(sv.Qty) from BooksView bv
inner join StockView sv on sv.ISBN=bv.ISBN
inner join BookStoreView bvs on bvs.StoreNo=sv.StoreNo
where bv.Price between 15 and 55
group by bv.ISBN
--Update the bookprice of bookNo=1234 from $45 to $55 at site S3.
update s3.dbo.books set price=55 where topic='Einthir manithan'
--Find total number of book at site S2.
select sum(sv.Qty) from s2.dbo.books bv
inner join s2.dbo.Stock sv on sv.ISBN=bv.ISBN
inner join s2.dbo.books bvs on bvs.StoreNo=sv.StoreNo
group by bv.ISBN
Result:
Thus the Distributed Database has been developed and executed successfully.
DEADLOCK DETECTION ALGORITHM FOR DISTRIBUTED DATABASE USING
WAIT- FOR GRAPH
Aim:
To implement Deadlock Detection Algorithm for Distributed Database using Wait-for
Graph to check for Deadlock.
Description:
There are five transactions T1, T2, T3, T4 and T5 with
T1 initiated at site S1 and spawning an agent at site S2
T2 initiated at site S3 and spawning an agent at site S1
T3 initiated at site S1 and spawning an agent at site S3
T4 initiated at site S2 and spawning an agent at site S3
T5 initiated at site S3
Wait For Graph:
DATE:
EX.NO:
CODINGS:
Connected to:
Oracle9i Release 9.0.1.1.1 - Production
JServer Release 9.0.1.1.1 - Production
SQL> create table dd1(trans varchar(20),loc varchar2(10),wait varchar2(10),site
varchar2(10));
Table created.
SQL> insert into dd1 values('t1','x1','x8','s1');
1 row created.
SQL> insert into dd1 values('t1','x6','x2','s2');
1 row created.
SQL> insert into dd1 values('t2','x4','x7','s2');
1 row created.
SQL> insert into dd1 values('t2','x5',' ','s3');
1 row created.
SQL> insert into dd1 values('t3','x2','x7','s1');
1 row created.
SQL> insert into dd1 values('t4','x7',' ','s2');
1 row created.
SQL> insert into dd1 values('t4','x8','x5','s3');
1 row created.
SQL> insert into dd1 values('t5','x3','x7','s3');
1 row created.
SQL> select * from dd1;
TRANS LOC WAIT SITE
-------------------- ---------- ---------- ----------
t1 x1 x8 s1
t1 x6 x2 s2
t2 x4 x7 s2
t2 x5 s3
t3 x2 x7 s1
t4 x7 s2
t4 x8 x5 s3
t5 x3 x7 s3
8 rows selected.
SQL> ed dd1;
SQL> set serveroutput on;
SQL> @dd1;
42 /
SELECT trans, lock1, wait
*
ERROR at line 3:
ORA-06550: line 3, column 15:
PL/SQL: ORA-00904: invalid column name
ORA-06550: line 3, column 1:
PL/SQL: SQL Statement ignored
ORA-06550: line 5, column 34:
PLS-00302: component 'LOCK1' must be declared
ORA-06550: line 5, column 1:
PL/SQL: Item ignored
ORA-06550: line 2, column 9:
PLS-00341: declaration of cursor 'C1' is incomplete or malformed
ORA-06550: line 14, column 4:
PL/SQL: Item ignored
ORA-06550: line 19, column 15:
PLS-00320: the declaration of the type of this expression is incomplete or
malformed
ORA-06550: line 19, column 1:
PL/SQL: SQL Statement ignored
ORA-06550: line 23, column 10:
PLS-00320: the declaration of the type of this expression is incomplete or
malformed
ORA-06550: line 23, column 1:
PL/SQL: Statement ignored
ORA-06550: line 26, column 9:
PLS-00320: the declaration of the type of this expression is incomplete or
malformed
ORA-06550: line 26, column 1:
PL/SQL: Statement ignored
ORA-06550: line 29, column 10:
PLS-00320: the declaration of the type of this expression is incomplete or
SQL> ed dd1;
SQL> @dd1;
42 /
ll(c) := ss.lock1;
*
ERROR at line 23:
ORA-06550: line 23, column 13:
PLS-00302: component 'LOCK1' must be declared
ORA-06550: line 23, column 1:
PL/SQL: Statement ignored
SQL> ed dd1;
SQL> @dd1;
42 /
TRANS Lock wait
t1 x1 x8
t1 x6 x2
t2 x4 x7
t2 x5
t3 x2 x7
t4 x7
t4 x8 x5
t5 x3 x7
x5<-x5deadlock occured
x2<-x2deadlock occured
x7<-x7deadlock occured
x7<-x7deadlock occured
x7<-x7deadlock occured
x8<-x8deadlock occured
PL/SQL procedure successfully completed.
SQL> ed dd2;
SQL> @dd2;
37 /
TRANS Lock wait
t1 x1 x8
t3 x2 x7
PL/SQL procedure successfully completed.
SQL> ed dd3;
SQL> ed dd3;
SQL> @dd3;
40 /
SELECT trans, lock1, wait
*
ERROR at line 3:
ORA-06550: line 3, column 15:
PL/SQL: ORA-00904: invalid column name
ORA-06550: line 3, column 1:
PL/SQL: SQL Statement ignored
ORA-06550: line 2, column 9:
PLS-00341: declaration of cursor 'C1' is incomplete or malformed
ORA-06550: line 14, column 4:
PL/SQL: Item ignored
ORA-06550: line 19, column 15:
PLS-00320: the declaration of the type of this expression is incomplete or
malformed
ORA-06550: line 19, column 1:
PL/SQL: SQL Statement ignored
ORA-06550: line 21, column 22:
PLS-00320: the declaration of the type of this expression is incomplete or
malformed
ORA-06550: line 21, column 1:
PL/SQL: Statement ignored
ORA-06550: line 24, column 10:
PLS-00320: the declaration of the type of this expression is incomplete or
malformed
ORA-06550: line 24, column 1:
PL/SQL: Statement ignored
ORA-06550: line 27, column 10:
PLS-00320: the declaration of the type of this expression is incomplete or
malformed
ORA-06550: line 27, column 1:
PL/SQL: Statement ignored
ORA-06550: line 36, column 5:
PLS-00201: identifier 'E' mu
SQL> ed dd3;
SQL> @dd3;
41 /
SELECT trans, lock1, wait
*
ERROR at line 3:
ORA-06550: line 3, column 15:
PL/SQL: ORA-00904: invalid column name
ORA-06550: line 3, column 1:
PL/SQL: SQL Statement ignored
ORA-06550: line 2, column 9:
PLS-00341: declaration of cursor 'C1' is incomplete or malformed
ORA-06550: line 15, column 4:
PL/SQL: Item ignored
ORA-06550: line 20, column 15:
PLS-00320: the declaration of the type of this expression is incomplete or
malformed
ORA-06550: line 20, column 1:
PL/SQL: SQL Statement ignored
ORA-06550: line 22, column 22:
PLS-00320: the declaration of the type of this expression is incomplete or
malformed
ORA-06550: line 22, column 1:
PL/SQL: Statement ignored
ORA-06550: line 25, column 10:
PLS-00320: the declaration of the type of this expression is incomplete or
malformed
ORA-06550: line 25, column 1:
PL/SQL: Statement ignored
ORA-06550: line 28, column 10:
PLS-00320: the declaration of the type of this expression is incomplete or
malformed
ORA-06550: line 28, column 1:
PL/SQL: Statement ignored
SQL> ed dd3;
SQL> @dd3;
41 /
TRANS Lock wait
t1 x6 x2
t2 x4 x7
t4 x7
x7<-x7deadlock occured
no deadlock
PL/SQL procedure successfully completed.
SQL> ed dd4;
SQL> @dd4;
37 /
SELECT trans, loc, wait
*
ERROR at line 3:
ORA-06550: line 3, column 15:
PL/SQL: ORA-00904: invalid column name
ORA-06550: line 3, column 1:
PL/SQL: SQL Statement ignored
ORA-06550: line 2, column 9:
PLS-00341: declaration of cursor 'C1' is incomplete or malformed
ORA-06550: line 14, column 4:
PL/SQL: Item ignored
ORA-06550: line 19, column 15:
PLS-00320: the declaration of the type of this expression is incomplete or
malformed
ORA-06550: line 19, column 1:
PL/SQL: SQL Statement ignored
ORA-06550: line 21, column 22:
PLS-00320: the declaration of the type of this expression is incomplete or
malformed
ORA-06550: line 21, column 1:
PL/SQL: Statement ignored
ORA-06550: line 24, column 10:
PLS-00320: the declaration of the type of this expression is incomplete or
malformed
ORA-06550: line 24, column 1:
PL/SQL: Statement ignored
ORA-06550: line 27, column 10:
PLS-00320: the declaration of the type of this expression is incomplete or
malformed
ORA-06550: line 27, column 1:
PL/SQL: Statement ignored
SQL> ed dd4;
SQL> @dd4;
37 /
TRANS Lock wait
t2 x5
t4 x8 x5
t5 x3 x7
x5<-x5deadlock occured
PL/SQL procedure successfully completed.
SQL> ed dd1;
SQL> @dd1;
42 /
TRANS Lock wait
t1 x1 x8
t1 x6 x2
t2 x4 x7
t2 x5
t3 x2 x7
t4 x7
t4 x8 x5
t5 x3 x7
x5<-x5deadlock occured
x2<-x2deadlock occured
x7<-x7deadlock occured
x7<-x7deadlock occured
x7<-x7deadlock occured
x8<-x8deadlock occured
PL/SQL procedure successfully completed.
SQL>
DD1 :
declare
cursor c1 is
SELECT trans, loc, wait
FROM dd1;
type c_list is varray(20) of dd1.loc%type;
ll c_list:=c_list();
type c_list1 is varray(20) of dd1.wait%type;
l2 c_list1:=c_list1();
type c_list2 is varray(20) of dd1.trans%type;
t c_list:=c_list();
c integer := 0;
d integer :=0;
f integer :=0;
ss c1%rowtype;
begin
open c1;
dbms_output.put_line('TRANS '||' '||'Lock'||' '||'wait');
loop
fetch c1 into ss;
exit when c1%notfound;
c := c+1;
ll.extend;
ll(c) := ss.loc;
f := f+1;
t.extend;
t(f) := ss.trans;
d :=d+1;
l2.extend;
l2(d) := ss.wait;
dbms_output.put_line(ss.trans||' '||ss.loc||' '||ss.wait);
end loop;
for i in 1 .. c loop
for j in 1 .. d loop
if ( ll(i) = l2(j)) then
if(ll(i) != '-')then
dbms_output.put_line(ll(i)||'<-'||l2(j)||'deadlock occured');
end if;
end if;
end loop;
end loop;
end;
DD2:
declare
cursor c1 is
SELECT trans, loc, wait
FROM dd1
WHERE Site='s1';
type c_list is varray(10) of dd1.loc%type;
ll c_list:=c_list();
type c_list1 is varray(10) of dd1.wait%type;
l2 c_list1:=c_list1();
type c_list2 is varray(20) of dd1.trans%type;
t c_list:=c_list();
c integer := 0;
d integer :=0;
ss c1%rowtype;
begin
open c1;
dbms_output.put_line('TRANS '||' '||'Lock'||' '||'wait');
loop
fetch c1 into ss;
exit when c1%notfound;
dbms_output.put_line(ss.trans||' '||ss.loc||' '||ss.wait);
c := c+1;
ll.extend;
ll(c) := ss.loc;
d :=d+1;
l2.extend;
l2(d) := ss.wait;
end loop;
for i in 1 .. c loop
for j in 1 .. d loop
if ( ll(i) = l2(j)) then
dbms_output.put_line(ll(i)||'<-'||l2(j)||'deadlock occured');
end if;
end loop;
end loop;
end;
DD3:
declare
cursor c1 is
SELECT trans, loc, wait
FROM dd1
WHERE Site='s2';
type c_list is varray(10) of dd1.loc%type;
ll c_list:=c_list();
type c_list1 is varray(10) of dd1.wait%type;
l2 c_list1:=c_list1();
type c_list2 is varray(20) of dd1.trans%type;
t c_list:=c_list();
c integer := 0;
d integer :=0;
e integer :=0;
ss c1%rowtype;
begin
open c1;
dbms_output.put_line('TRANS '||' '||'Lock'||' '||'wait');
loop
fetch c1 into ss;
exit when c1%notfound;
dbms_output.put_line(ss.trans||' '||ss.loc||' '||ss.wait);
c := c+1;
ll.extend;
ll(c) := ss.loc;
d :=d+1;
l2.extend;
l2(d) := ss.wait;
end loop;
for i in 1 .. c loop
for j in 1 .. d loop
if ( ll(i) = l2(j)) then
dbms_output.put_line(ll(i)||'<-'||l2(j)||'deadlock occured');
end if;
end loop;
end loop;
if (e = 0) then
dbms_output.put_line('no deadlock ');
end if;
end;
DD4:
declare
cursor c1 is
SELECT trans, loc, wait
FROM dd1
WHERE Site='s3';
type c_list is varray(10) of dd1.loc%type;
ll c_list:=c_list();
type c_list1 is varray(10) of dd1.wait%type;
l2 c_list1:=c_list1();
type c_list2 is varray(20) of dd1.trans%type;
t c_list:=c_list();
c integer := 0;
d integer :=0;
ss c1%rowtype;
begin
open c1;
dbms_output.put_line('TRANS '||' '||'Lock'||' '||'wait');
loop
fetch c1 into ss;
exit when c1%notfound;
dbms_output.put_line(ss.trans||' '||ss.loc||' '||ss.wait);
c := c+1;
ll.extend;
ll(c) := ss.loc;
d :=d+1;
l2.extend;
l2(d) := ss.wait;
end loop;
for i in 1 .. c loop
for j in 1 .. d loop
if ( ll(i) = l2(j)) then
dbms_output.put_line(ll(i)||'<-'||l2(j)||'deadlock occured');
end if;
end loop;
Result:
Thus the Deadlock Detection Algorithm Distributed Database has been developed and
executed successfully.
OBJECT ORIENTED DATABASE – EXTENDED ENTITY RELATIONSHIP (EER)
Aim:
To design an Enhanced Entity Relationship model for University database and to write
Object Query Language (OQL) to manage the database.
SQL Query:
Creation and insertion use University go
------------------------------------------------------------------------------
----------Design and create the tables needed
-------------------------------
---------------------------------------------------------------------------
create table department
(
DeptID int identity(1,1) PRIMARY KEY,
DeptName varchar(10)
)
go
create table dbo.Roles
(
RoleID int not null identity(1,1) PRIMARY KEY,
RoleName varchar(30) not null
)
go
create table dbo.Gender
(
GenderID int not null identity(1,1) PRIMARY KEY,
Gender varchar(10) not null
)
go
create table dbo.StatusTable
(
ID int NOT NULL PRIMARY KEY,
[Status] varchar(10)
)
go
create table dbo.Users
(
UserID int NOT NULL identity(1,1) PRIMARY KEY,
UserName varchar(30),
[Address] varchar(100),
DATE:
EX.NO:
Phone varchar(20),
DeptID int FOREIGN KEY REFERENCES department(DeptID),
Gender int FOREIGN KEY REFERENCES Gender(GenderID),
RoleId int FOREIGN KEY REFERENCES Roles(RoleID),
SubRoleID int FOREIGN KEY REFERENCES SubRoles(SubRoleID),
StatusId int FOREIGN KEY REFERENCES StatusTable(ID)
)
go
create table dbo.SubRoles
(
SubRoleID int identity(1,1) PRIMARY KEY,
RoleID int FOREIGN KEY REFERENCES Roles(RoleID),
SubRoleName varchar(30)
)
go
----------------------------------------------------------------------
--------Insert Values into the tables----------------------------
-------------------------------------------------------------------
insert into dbo.department
values('CSE')
go
insert into dbo.department
values('IT')
go
insert into dbo.department
values('ECE')
go
insert into dbo.Roles
values('Employee')
go
insert into dbo.Roles
values('Student')
go
insert into dbo.Gender
values('Male')
go
insert into dbo.Gender
values('Female')
go
insert into dbo.StatusTable
values(1,'Active')
go
insert into dbo.StatusTable
values(2,'InActive')
go
insert into dbo.SubRoles
values(1,'Faculty')
go
insert into dbo.SubRoles
values(1,'Technicians')
go
insert into dbo.SubRoles
values(1,'Project associates')
go
insert into dbo.SubRoles
values(2,'Full Time')
go
insert into dbo.SubRoles
values(2,'Part Time')
go
insert into dbo.SubRoles
values(2,'Teaching Assistant')
go
insert into users values
('kalpana','2-Richie street, chennai',9840000000,1,2,2,2,1)
--ii. Display the Employee details
--a. Display General Employee details :
select
Username as [EmployeeName],
[Address] as [Address],
Phone as [Contact],
d.DeptName as [Department],
g.Gender as [Gender],
s.SubRoleName as [ROle],
st.Status as [Status]
from users u
inner join department d on d.DeptID = u.DeptID
inner join Gender g on g.GenderID = u.Gender
inner join SubRoles s on s.SubRoleID = u.SubRoleID
inner join StatusTable st on st.ID = u.StatusID
where u.RoleID = 1 --(Employee Role is filtered)
--b. Display Female Employee Details
select
Username as [EmployeeName],
[Address] as [Address],
Phone as [Contact],
d.DeptName as [Department],
g.Gender as [Gender],
s.SubRoleName as [ROle],
st.Status as [Status]
from users u
inner join department d on d.DeptID = u.DeptID
inner join Gender g on g.GenderID = u.Gender
inner join SubRoles s on s.SubRoleID = u.SubRoleID
inner join StatusTable st on st.ID = u.StatusID
where u.RoleID = 1 and u.Gender = 2 --(Employee Role and female gender
- filtered)
--iii. Display Student Details.
--a.Select General Student Details
select
Username as [EmployeeName],
[Address] as [Address],
Phone as [Contact],
d.DeptName as [Department],
g.Gender as [Gender],
s.SubRoleName as [ROle],
st.Status as [Status]
from users u
inner join department d on d.DeptID = u.DeptID
inner join Gender g on g.GenderID = u.Gender
inner join SubRoles s on s.SubRoleID = u.SubRoleID
inner join StatusTable st on st.ID = u.StatusID
where u.RoleID = 2 --(Student Role is filtered)
--b.Display only full time students
select
Username as [EmployeeName],
[Address] as [Address],
Phone as [Contact],
d.DeptName as [Department],
g.Gender as [Gender],
s.SubRoleName as [ROle],
st.Status as [Status]
from users u
inner join department d on d.DeptID = u.DeptID
inner join Gender g on g.GenderID = u.Gender
inner join SubRoles s on s.SubRoleID = u.SubRoleID
inner join StatusTable st on st.ID = u.StatusID
where u.RoleID = 2 and u.SubRoleID = 4--(Student Role and Full-time
SubRole - filtered)
iv Modify User details v Delete User details
--iv Modify Person details
--a.Update Phone number of a user
Update Users
set Phone = 9840202000
where userid = 1
--b.Update all the part time students as full time students and all
the full time students
--as part time students
Update Users
set SubRoleID = Case when SubRoleID = 4 then 5
when SubRoleID = 5 then 4
where SubRoleID in (4,5) --This condition avoids the control to check
in all the rows
v. Delete Person Details
a)Delete an User from the table
Delete from Users
where UserID = 2
b)Delete users if their status is inactive
Delete from users
where StatusID = 2
Result:
Thus the Object Oriented Database for Extended Entity Relationship (EER)
has been developed and executed successfully.
PARALLEL DATABASE UNIVERSITY COUNSELLING FOR
ENGINEERING COLLEGES
AIM:
To implement University Counselling for Engineering Colleges using Parallel Database.
Description:
A variety of hardware architectures allow multiple computers to share access to data, software,
or peripheral devices. A Parallel Database is designed to take advantage of such architectures by
running multiple instances which "share" a single physical database. In appropriate applications, a
parallel server can allow access to a single database by users on multiple machines, with increased
performance.
SQL Query:
The College details, Department details and Vacancy Details are maintained in 3
different sites. By using the information from these 3 sites, seats can be allocated to a student
using Parallel Query Processing.
1.CREATE TABLE hr.admin_emp_dept
PARALLEL COMPRESS
AS SELECT * FROM hr.employees
WHERE department_id = 10;
In this case, the PARALLEL clause tells the database to select an optimum number of parallel
execution servers when creating the table.
DATE:
EX.NO:
parallel query with intra- and inter-operation parallelism, consider a more complex query:
2.SELECT /*+ PARALLEL(employees 4) PARALLEL(departments 4)
USE_HASH(employees) ORDERED */
MAX(salary), AVG(salary) FROM employees, departments
WHERE employees.department_id = departments.department_id
GROUP BY employees.department_id;
RESULT:
Thus the Object Parallel Database fo r University Counselling for Engineering colleges
has been developed and executed successfully.
PARALLEL JOIN AND PARALLEL SORT
ALGORITHM
AIM:
To implement parallel join and parallel sort algorithms to get marks marks from
different colleges and publish 10 ranks for each discipline.
Description:
parallel join and parallel sort:
A parallel join is a method that combines rows from two tables using multi-threading
for sorting and match merging to create final output, the goal of which is to reduce the
total time required to complete the task. The Parallel Join facility can handle multiple
character columns, numeric columns or combinations of character and numeric columns
that are joined between pairs of tables. Numeric columns do not need to be of the same
width to act as a join key, but character columns must be of the same width in order to be a
join key.
Parallel Sort-Merge Method:
The parallel sort-merge join method first performs a parallel sort to order the data,
and then merges the sorted tables in parallel. During the merge, the facility concurrently
joins multiple rows from one table with the corresponding rows in the other table.
SQL Query:
The first parallel join example is a basic SQL query that creates a pair-wise join of
two Server tables, table1 and table2.
1.CREATE TABLE junk as
SELECT *
from path1.table1 a,
path1.table2 b
where a.i = b.i;
DATE:
EX.NO:
2.creates a table which is the result of union of two parallel joins and this example shows
how the sort merges are used for the joins.
CREATE TABLE junk as
SELECT *
from path1.table1 a, path1.table2 b
where a.i = b.i
UNION
SELECT *
from path1.dansjunk3
c, path1.dansjunk4 d
where c.i = d.i;
3. The Parallel Join Facility also includes enhancements for data summarization by
using GROUP BY technique. The following example shows the combined use of both
the parallel join and parallel GROUP BY methods.
CREATE TABLE junk as
SELECT a.c,
b.d, sum(b.e)
from
path1.table1a,
path1.table2b
Where
a.i =b.i
GROUP BY a.d, b.d;
RESULT:
Thus the Object Parallel Database fo r University Counselling for Engineering colleges
has been developed and executed successfully.
ACTIVE DATABASE – IMPLEMENTATION OF TRIGGERS & ASSERTIONS FOR
BANK DATABASE
Aim:
To create Triggers and Assertions for Bank Database handling deposits and loan and
for Admission Database handling seat allocation and vacancy position.
Description:
An active database is a database that includes an event-driven architecture which can respond
to conditions both inside and outside the database. Possible uses include security monitoring,
alerting, statistics gathering and authorization.
Most modern relational databases include active database features in the form of database
triggers.
Typically this behavior is described by event-condition-action
(ECA) rules.
ECA rules comprise three components: event E, condition C, and action A.
The event describes an external happening to which the rule may be able to respond.
The condition examines the context in which the event has taken place.
The action describes the task to be carried out by the rule if the relevant event has taken place
and the condition has evaluated to true. In sum, if the specified event E occurs and if the
condition C is true then the specified action A is executed.
Triggers:
A trigger is a PL/SQL block or a PL/SQL procedure that executes implicitly whenever a
particular event takes place. It can either be:
1. Application trigger: Fires whenever an event occurs with a particular application.
2. Database Trigger: Fires whenever a data event (such as DML) occurs on a schema or database.
Guidelines to Designing Triggers:
o Use triggers to guarantee that when a specific operation is performed, related actions are
performed.
o Only use database triggers for centralized, global operations that should be fired for the
triggering statement, regardless of which user or application issues the statement.
o Do not define triggers to duplicate or replace the functionality already built into the oracle
database. For example do not define trigger to implement integrity rules that can be done by
using declarative constraints.
Elements in a Trigger:
• Trigger timing
o For table: BEFORE, AFTER
o For view: INSTEAD OF
• Trigger event: INSERT, UPDATE, OR DELETE
• Table name: On table, view
• Trigger Type: Row or statement
• When clause: Restricting condition
DATE:
EX.NO:
• Trigger body: PL/SQL block
“Before triggers” execute the trigger body before the triggering DML event on a table.
These are frequently used to determine whether that triggering statement should be allowed to
complete. This situation enables you to eliminate unnecessary processing of the triggering
statement and it eventual rollback in cases where an exception is raised in the triggering action.
“After triggers” are used when the triggering statement is to be completed before the triggering
action and to perform a different action on the same triggering statement if a BEFORE trigger is
already present.
“Instead of Triggers” are used to provide a transparent way of modifying views that cannot be
modified directly through SQL DML statements because the view is not inherently modifiable.
You can write INSERT, UPDATE, and DELETE statements against the view. The INSTEAD
OF trigger works invisibly in the background performing the action coded in the trigger body
directly on the underlying tables.
Triggering user events:
INSERT
UPDATE
DELETE
Trigger Components:
Statement: The trigger body executes once for the triggering event. This is the default. A
statement trigger fires once, even if no rows are affected at all.
Row: The trigger body executes once for each row affected by the triggering event.
A row trigger is not executed if the triggering event affects no rows.
Trigger Body:
The trigger body is a PL/SQL block or a call to a procedure.
Syntax:
CREATE [OR REPLACE] TRIGGER trigger_name
Timing
Event1 [OR event2 OR event3]
ON table_name
Trigger_body
Assertions
An assertion is a predicate expressing a condition we wish the database to always satisfy.
Domain constraints, functional dependency and referential integrity are special forms of
assertion.
Where a constraint cannot be expressed in these forms, we use an assertion, e.g.
Ensuring the sum of loan amounts for each branch is less than the sum of all account
balances at the branch.
Ensuring every loan customer keeps a minimum of $1000 in an account.
An assertion in DQL-92 takes the form,
CREATE ASSERTION <assertion_name> CHECK predicate
Problem Statement:
Create triggers and assertions for Bank database handling deposits and loan and admission
database handling seat allocation and vacancy position. Design the above relational database
schema and implement the following triggers and assertions.
a. When a deposit is made by a customer, create a trigger for updating customers account and
bank account
Create or replace trigger t1 after insert on dep09
for each row
begin
if :new.damt>0 then
update cust09 set cbal=cbal+:new.damt where :new.ano=cust09.ano;
update bank09 set asset=asset+:new.damt where :new.brid=bank09.brid;
end if;
end;
b. When a loan is issued to the customer, create a trigger for updating customer’s loan account
and bank account.
Create or replace trigger t2 after insert on loan09
for each row
begin
if :new.lamt>0 then
update cust09 set cbal=cbal+:new.lamt where :new.ano=cust09.ano;
update bank09 set asset=asset-:new.lamt where :new.brid=bank09.brid;
dbms_output.put_line('customer and bank account updated');
end if;
end;
c. Create assertion for bank database so that the total loan amount does not exceed the total
balance in the bank.
create or replace assertion check (select sum(amt) from jloan where
jloan.name=jbranch.name)>=(select sum(amt) from jaccount where
jaccount.name=jbranch.name)
d. When an admission is made, create a trigger for updating the seat allocation details and
vacancy position.
Create or replace trigger t3 after insert on ad09
for each row
declare
a number(5):=0;
begin
select vp into a from vp09 where dept=:new.dept;
if a>0 then
update sa09 set dsa=dsa+1 where :new.dept=sa09.dept;
update vp09 set vp=vp-1 where :new.dept=vp09.dept;
end if;
dbms_output.put_line('Seat Allocation and Vacancy position updated');
end;
Create the following tables
Customer Table
USE [Bank]
GO
/****** Object: Table [dbo].[Customer] Script Date: 01/01/2014 18:27:59 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Customer](
[CNO] [int] NOT NULL,
[FirstName] [varchar](50) NULL,
[LastName] [varchar](50) NULL,
[ZipCode] [nvarchar](50) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
Data to be inserted
CNO FirstName LastName ZipCode
1 Shyam Sundar 6120
2 Somes Thani 60661
3 Aswin Arun 6677
4 Shankar S 7667
CustomerAccount
USE [Bank]
GO
/****** Object: Table [dbo].[CustomerAccount] Script Date: 01/01/2014 18:29:22 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[CustomerAccount](
[AccountNo] [int] NOT NULL,
[Location] [varchar](50) NULL,
[CNO] [int] NOT NULL,
[Amount] [decimal](18, 0) NULL,
[ApprovedLoanAmount] [decimal](18, 0) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
AccountNo Location CNO Amount ApprovedLoanAmount
1000 Chennai 1 1000 500
CustomerBankAccount
USE [Bank]
GO
/****** Object: Table [dbo].[CustomerBankAccount] Script Date: 01/01/2014 18:29:36
******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[CustomerBankAccount](
[CBNO] [int] NOT NULL,
[BankLocation] [nchar](10) NOT NULL,
[BankName] [varchar](50) NULL,
[Amount] [decimal](18, 0) NOT NULL,
[CNO] [int] NULL,
[Active] [int] NULL,
[LoanAmount] [decimal](18, 0) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
CBNO BankLocation BankName Amount CNO Active LoanAmount
2001 Chennai LLFC 1000 1 NULL 500
2002 Chennai LLFC 0 2 NULL NULL
2003 Chennai LLFC 0 3 NULL NULL
CustomerLoanAccount
USE [Bank]
GO
/****** Object: Table [dbo].[CustomerLoanAccount] Script Date: 01/01/2014 18:29:43
******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[CustomerLoanAccount](
[CLNO] [int] NOT NULL,
[LoanName] [varchar](50) NOT NULL,
[Location] [varchar](50) NOT NULL,
[Amount] [decimal](18, 0) NOT NULL,
[CNO] [int] NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
CLNO LoanName Location Amount CNO
3001 0 Chennai 500 1
3002 0 Chennai 0 2
3003 0 Chennai 0 3
Bandmaster
USE [Bank]
GO
/****** Object: Table [dbo].[BankMaster] Script Date: 01/01/2014 18:29:57 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[BankMaster](
[BankID] [int] NOT NULL,
[Name] [varchar](50) NOT NULL,
[Allocated] [int] NOT NULL,
[Vacancy] [int] NOT NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
BankID Name Allocated Vacancy
1 LLFC 4 196
The scenarios are explained using the following triggers
When a deposit is made by a customer, create a trigger for updating
customers account and bank account
USE [Bank]
GO
/****** Object: Trigger [dbo].[CustomerAccountTrigger] Script Date: 01/01/2014 18:21:42
******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[CustomerAccountTrigger]
ON [dbo].[CustomerAccount]
AFTER INSERT,UPDATE
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
Declare @amount int
Declare @cno int
select @amount=amount,@cno=CNO from inserted
SET NOCOUNT ON;
update CustomerBankAccount set Amount=@amount where CNO=@cno
END
Create assertion for bank database so that the total loan amount does not
exceed
the total balance in the bank.
When a loan is issued to the customer, create a trigger for updating
customer’s loan account and bank account.
USE [Bank]
GO
/****** Object: Trigger [dbo].[CustomerLoanTrigger] Script Date: 01/01/2014 18:22:13
******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[CustomerLoanTrigger]
ON [dbo].[CustomerAccount]
AFTER INSERT,UPDATE
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
Declare @loanamount as int
Declare @cno as int
Declare @balance as decimal(18,0)
select @loanamount=ApprovedLoanAmount,@cno=CNO,@balance=Amount from
inserted
select @balance=Amount from CustomerBankAccount where CNO=@cno
if(@loanamount>@balance)
Begin
RAISERROR ('Loan Amount should not exceed the balance!',10, 1)
ROLLBACK
end
SET NOCOUNT ON;
update CustomerBankAccount set loanamount=@loanamount where CNO=@cno
update CustomerLoanAccount set Amount=@loanamount where CNO=@cno
END
When an admission is made, create a trigger for updating the seat allocation
details and vacancy position.
USE [Bank]
GO
/****** Object: Trigger [dbo].[BankMasterTrigger] Script Date: 01/01/2014 18:21:09
******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[BankMasterTrigger]
ON [dbo].[Customer]
AFTER INSERT,UPDATE
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
Declare @count as int
select @count=COUNT(1) from inserted
SET NOCOUNT ON;
if(@count>0)
Begin
update BankMaster set Allocated=Allocated+1,Vacancy=Vacancy-1
End
END
RESULT:
Thus the Active Database fo r Implementation of Triggers & Assertions for Bank
database has been developed and executed successfully.
DEDUCTIVE DATABASE
AIM:
To construct a knowlegde database for kinship domain(family relations) with
facts.Extract the following a relations using rules.Parent, Sibling, Brother,Sister, Child,
Daughter, Son, Spouse ,Wife, Husband, Grandparent, Grandchild, Cousin, Aunt and Uncle.
PROLOG:
Prolog stands for programming logic, Prolog is a high-level programming language based on
formal logic. Unlike traditional programing language that are based on performing sequences of
commands,Prolog is based on defining and then solving logical formulas. Prolog is sometimes
called a declarative language or a rule-based language because its programs consists of a list of
facts and rules. Prolog is used widely for artificial intelligence applications, particularly expert
systems.
PROCEDURE:
Start>>All programs>>SWI-Prolog>>Prolog
DATE:
EX.NO:
File>>New
Type the filename and select “save”
Type the coding in the window and Save it
Coding:
male(kasthuriraja).
male(dhanush).
male(selva).
male(yatra).
male(linga).
female(vijaya).
female(aishwarya).
female(geethanjali).
female(anjali).
parent(kasthuriraja,dhanush).
parent(vijaya,dhanush).
parent(kasthuriraja,selva).
parent(vijaya,selva).
parent(dhanush,linga).
parent(aishwarya,linga).
parent(dhanush,yatra).
parent(aishwarya,yatra).
parent(selva,anjali).
parent(geethanjali,anjali).
father(F,X):-male(F),parent(F,X).
mother(M,X):-female(M),parent(M,X).
sibling(X,Y):-father(Z,X),father(Z,Y).
child(C,P):-parent(P,C).
brother(B,X):-male(B),sibling(B,X).
sister(S,X):-female(S),sibling(S,X).
daughter(D,X):-female(D),parent(X,D).
son(S,X):-male(S),parent(X,S).
spouse(X,Y):-child(Z,X),child(Z,Y).
wife(W,X):-female(W),male(X),spouse(W,X).
husband(H,X):-male(H),female(X),spouse(H,X).
grandfather(GP,GC):-male(GP),parent(GP,X),parent(X,GC).
grandmother(GP,GC):-female(GP),parent(GP,X),parent(X,GC).
grandchild(GC,GP):-grandmother(GP,GC).
grandchild(GC,GP):-grandfather(GP,GC).
aunt(A,X):-female(A),father(Z,X),brother(Z,A).
aunt(A,X):-female(A),mother(Z,X),sister(Z,A).
uncle(U,X):-male(U),father(Z,X),brother(Z,U).
uncle(U,X):-male(U),mother(Z,X),sister(Z,U).
uncle(U,X):-male(U),father(Z,X),sister(S,Z),husband(U,S).
cousin(X,Y):-parent(Z,X),parent(P,Y),sibling(Z,P).
File>Save As
Enter the filename with extension .pl
Compile the code
Compile>>Compile buffer
Execute with the commands
; at the end of statement indicates to display next valid data. . at the end of statements will end
the display of futher data even if available.
To Edit existing file “File>>Edit>>select the name of the file to be edited”.Make the
modification and use “File>>Save As” to save the file and Click “File>>Reload Modified Files”
to load the currently saved document and check the commands.
RESULT:
Thus the knowledge database for kinship domain with facts has been created and the
rules have been executed.
STUDY AND WORKING OF WEKA TOOL
AIM:
To study and work with WEKA tool classification and clustering algorithms using training
dataset and test dataset.
Description:
Form the different clusters and classify the given data into different classes with the help
of WEKA tool.
RID Age Income Student Credit_rating Class:buys_computer
1 youth high no fair no
2 youth high no excellent no
3 middle_aged high no fair yes
4 senior medium no fair yes
5 senior low yes fair yes
6 senior low yes excellent no
7 middle_aged low yes excellent yes
8 youth medium no fair no
9 youth low yes fair yes
10 senior medium yes fair yes
11 youth medium yes excellent yes
12 middle_aged medium no excellent yes
13 middle_aged high yes fair yes
14 senior medium no excellent no
DATE:
EX.NO:
Snapshots:
Result
Thus the weka experiment has been studied, clustering and classification is done and executed
successfully.
QUERY PROCESSING – IMPLEMENTATION OF AN EFFICIENT QUERY
OPTIMIZER
AIM:
To implement Query Optimizer with Relational Algebraic expression construction and
execution plan generation for choosing an efficient execution strategy for processing the
given query
Description:
Creation of Employee Database
SQL> Create table Empdet ( Empname varchar(20), empid varchar(10), city varchar(20),
Country varchar(20), Branch varchar(30), Experience number(10));
SQL> Select * from Empdet;
EMPNAME EMPID CITY COUNTRY BRANCH EXPERIENCE
Ansar Ali 1420001 San Francisco America San
Francisco
10
Divya 1420002 Perth Australia Perth 4
Amrutha 1420003 New Delhi India New Delhi 8
Sharuk 1455007 London UK London 2
Amir khan 1456032 Chennai India Chennai 6
Anushka 1465603 Barclona America Barclona 1
Priyanka 1450007 London UK London 3
a)Select empid, empname fom employee where experience>5
SQL> Select empid, empname from Empdet where Experience >5;
EMPID EMPNAME
1420001 Ansar Ali
1420003 Amrutha
1456032 Amir khan
DATE:
EX.NO:
b)Find all managers working at London Branch
SQL> Select * from Empdet where Branch='London';
EMPNAME EMPID CITY COUNTRY BRANCH EXPERIENCE
Sharuk 1455007 London UK London 2
Priyanka 1450007 London UK London 3
Result
Thus the Query Processing fo r Implementation of an Efficient Query Optimizer is done and
executed successfully.
DESIGNING XML SCHEMA FOR COMPANY DATABASE
Aim:
To design a XML Schema and to implement the queries using XQuery and XPath.
Description:
An XML Schema
Describes the structure of an XML document.
1. XSD - The <schema> Element
The <schema> element is the root element of every XML Schema.
The <schema> Element
The <schema> element is the root element of every XML Schema:
<?xml version="1.0"?>
<xs:schema>
...
...
</xs:schema>
A simple element is an XML element that can contain only text. It cannot contain any other
elements or attributes.
2. Defining a Simple Element
The syntax for defining a simple element is:
<xs:element name="xxx" type="yyy"/>
where xxx is the name of the element and yyy is the data type of the element.
XML Schema has a lot of built-in data types. The most common types are:
xs:string
xs:decimal
xs:integer
xs:boolean
xs:date
xs:time
3. XSD Attributes
All attributes are declared as simple types.
What is an Attribute?
Simple elements cannot have attributes. If an element has attributes, it is considered to be of a
complex type. But the attribute itself is always declared as a simple type.
How to Define an Attribute?
The syntax for defining an attribute is:
<xs:attribute name="xxx" type="yyy"/>
where xxx is the name of the attribute and yyy specifies the data type of the attribute.
XML Schema has a lot of built-in data types. The most common types are:
xs:string
xs:decimal
xs:integer
DATE:
EX.NO:
xs:boolean
xs:date
xs:time
XPath
XPath is a language for finding information in an XML document.
1. Nodes
In XPath, there are seven kinds of nodes: element, attribute, text, namespace, processinginstruction,
comment, and document nodes.
XML documents are treated as trees of nodes. The topmost element of the tree is called the root
element.
XPath uses path expressions to select nodes in an XML document. The node is selected by
following a path or steps. The most useful path expressions are listed below:
Expression Description
nodename Selects all nodes with the name "nodename"
/ Selects from the root node
// Selects nodes in the document from the current node that match the
selection no matter where they are
. Selects the current node
.. Selects the parent of the current node
@ Selects attributes
XQuery
XQuery is to XML what SQL is to database tables.
XQuery was designed to query XML data.
XQuery uses functions to extract data from XML documents.
1.Path Expressions
XQuery uses path expressions to navigate through elements in an XML document.
The following path expression is used to select all the title elements in the "books.xml" file:
doc("books.xml")/bookstore/book/title
2.Predicates
XQuery uses predicates to limit the extracted data from XML documents.
The following predicate is used to select all the book elements under the bookstore element that
have a price element with a value that is less than 30:
doc("books.xml")/bookstore/book[price<30]
The expression above will select all the title elements under the book elements that are under the
bookstore element that have a price element with a value that is higher than 30.
The following FLWOR expression will select exactly the same as the path expression above:
for $x in doc("books.xml")/bookstore/book
where $x/price>30
return $x/title
The result will be:
<title lang="en">XQuery Kick Start</title>
<title lang="en">Learning XML</title>
With FLWOR you can sort the result:
for $x in doc("books.xml")/bookstore/book
where $x/price>30
order by $x/title
return $x/title
FLWOR is an acronym for "For, Let, Where, Order by, Return".
The for clause selects all book elements under the bookstore element into a variable called $x.
The where clause selects only book elements with a price element with a value greater than 30.
The order by clause defines the sort-order. Will be sort by the title element.
The return clause specifies what should be returned. Here it returns the title elements.
Implement a storage structure for storing XML database in Oracle 9i
Oracle XML DB provides you with the ability to fine tune how XML documents will be stored
and processed in Oracle9i database
Steps:
1.Design an XML Schema for the given company database.
2. Write XML file to store Department, Employee and Project details.
3. Write the queries using Xquery and Xpath and execute it using XQuery Engine.
4. Implement a storage structure for storing XML database in Oracle 9i.
Problem Statement:
Design XML Schema for the given company database
Department ( deptName, deptNo, deptManagerSSN, deptManagerStartDate,deptLocation )
Employee ( empName, empSSN, empSex, empSalary, empBirthDate,empDeptNo,
empSupervisorSSN, empAddress, empWorksOn)
Project ( projName, projNo, projLocation, projDeptNo, projWorker )
//project.xml
<?xml version="1.0"?>
<projects>
<project>
<projName>Web Mining</projName>
<projNo>111</projNo>
<projLoc>Chennai</projLoc>
<projDeptNo>1</projDeptNo>
<projWorkers>
<projWorker>
<name>dhanu</name>
<name>jeyaraman</name>
</projWorker>
</projWorkers>
</project>
<project>
<projName>Cloud Computing</projName>
<projNo>112</projNo>
<projLoc>Chennai</projLoc>
<projDeptNo>1</projDeptNo>
<projWorkers>
<projWorker>
<name>arthi</name>
<name>jeyaraman</name>
</projWorker>
</projWorkers>
</project>
<project>
<projName>BusinessProcess</projName>
<projNo>221</projNo>
<projLoc>Chennai</projLoc>
<projDeptNo>2</projDeptNo>
<projWorkers>
<projWorker>
<name>dhanushya</name>
<name>J</name>
</projWorker>
</projWorkers>
</project>
<project>
<projName>KnowledgeProcess</projName>
<projNo>222</projNo>
<projLoc>Chennai</projLoc>
<projDeptNo>2</projDeptNo>
<projWorkers>
<projWorker>
<name>dhanu</name>
</projWorker>
</projWorkers>
</project>
</projects>
//department.xml
<?xml version="1.0"?>
<departments>
<department>
<deptName>Research</deptName>
<deptNo>1</deptNo>
<deptMgrSSN>11</deptMgrSSN>
<deptMgrStartDate>1/1/2000</deptMgrStartDate>
<deptLocation>Chennai</deptLocation>
</department>
<department>
<deptName>Outsourcing</deptName>
<deptNo>2</deptNo>
<deptMgrSSN>22</deptMgrSSN>
<deptMgrStartDate>1/1/2001</deptMgrStartDate>
<deptLocation>Hyderabad</deptLocation>
</department>
</departments>
//employee.xml
<?xml version="1.0"?>
<employees>
<employee>
<empName>arthi</empName>
<empSSN>11</empSSN>
<empSex>Female</empSex>
<empSalary>900000</empSalary>
<empBirthDate>1-3-89</empBirthDate>
<empDeptNo>1</empDeptNo>
<empAddress>kknagarchennai</empAddress>
<empWorksOn>Web Mining</empWorksOn>
</employee>
<employee>
<empName>maliga</empName>
<empSSN>12</empSSN>
<empSex>Female</empSex>
<empSalary>300000</empSalary>
<empBirthDate>2-3-89</empBirthDate>
<empDeptNo>1</empDeptNo>
<empSupSSN>11</empSupSSN>
<empAddress>annanagarchennai</empAddress>
<empWorksOn>Cloud Computing</empWorksOn>
</employee>
<employee>
<empName>sindhu</empName>
<empSSN>13</empSSN>
<empSex>male</empSex>
<empSalary>300000</empSalary>
<empBirthDate>4-9-89</empBirthDate>
<empDeptNo>1</empDeptNo>
<empSupSSN>11</empSupSSN>
<empAddress>annanagarchennai</empAddress>
<empWorksOn>Cloud Computing</empWorksOn>
</employee>
<employee>
<empName>gg</empName>
<empSSN>14</empSSN>
<empSex>male</empSex>
<empSalary>300000</empSalary>
<empBirthDate>6-9-88</empBirthDate>
<empDeptNo>1</empDeptNo>
<empSupSSN>11</empSupSSN>
<empAddress>annanagarchennai</empAddress>
<empWorksOn>Web Mining</empWorksOn>
</employee>
<employee>
<empName>sruthi</empName>
<empSSN>22</empSSN>
<empSex>Female</empSex>
<empSalary>900000</empSalary>
<empBirthDate>3-3-89</empBirthDate>
<empDeptNo>2</empDeptNo>
<empAddress>T nagarchennai</empAddress>
<empWorksOn>BusinessProcess</empWorksOn>
</employee>
<employee>
<empName>dhanuhsya</empName>
<empSSN>23</empSSN>
<empSex>male</empSex>
<empSalary>300000</empSalary>
<empBirthDate>7-9-1992</empBirthDate>
<empDeptNo>2</empDeptNo>
<empSupSSN>22</empSupSSN>
<empAddress>T nagarchennai</empAddress>
<empWorksOn>BusinessProcess</empWorksOn>
</employee>
<employee>
<empName>dhanush</empName>
<empSSN>24</empSSN>
<empSex>Female</empSex>
<empSalary>400000</empSalary>
<empBirthDate>3-3-90</empBirthDate>
<empDeptNo>2</empDeptNo>
<empSupSSN>22</empSupSSN>
<empAddress>kknagarchennai</empAddress>
<empWorksOn>KnowledgeProcess</empWorksOn>
</employee>
<employee>
<empName>dhanu</empName>
<empSSN>25</empSSN>
<empSex>Female</empSex>
<empSalary>300000</empSalary>
<empBirthDate>3-5-90</empBirthDate>
<empDeptNo>2</empDeptNo>
<empSupSSN>22</empSupSSN>
<empAddress>annanagarchennai</empAddress>
<empWorksOn>KnowledgeProcess</empWorksOn>
</employee>
</employees>
Query 1:
Retrieve the department name, manager name, and manager salary for every department
let $d1:=doc("D:/department.xml")
let $d2:=doc("D:/employee.xml")
for $p1 in $d1/departments/department
for $p2 in $d2/employees/employee
where $p1/deptMgrSSN=$p2/empSSN
return<Result><dept>{$p1/deptName}</dept><mgrName>{$p2/empName}
</mgrName><mgrSal>{$p2/empSalary}</mgrSal></Result>
OUTPUT FOR QUERY1:
<Result>
<dept>
<deptName>Research</deptName>
</dept>
<mgrName>
<empName>arthi</empName>
</mgrName>
<mgrSal>
<empSalary>900000</empSalary>
</mgrSal>
</Result>
<Result>
<dept>
<deptName>Outsourcing</deptName>
</dept>
<mgrName>
<empName>sruthi</empName>
</mgrName>
<mgrSal>
<empSalary>900000</empSalary>
</mgrSal>
</Result>
Query 2:
Retrieve the employee name, supervisor name and employee salary for each employee who
works in the Research Department.
let $d1:=doc(“D:/employee.xml")
let $d2:=doc("D:/department.xml")
let $r:=$d2/departments/department[deptName="Research"]
let $sup:=$d1/employees/employee[empSSN=$r/deptMgrSSN]
for $p1 in $d1/employees/employee
where $p1/empDeptNo=$r/deptNo
return<Result><eName>{$p1/empName}</eName><supName>{$sup/empName}
</supName><empSal>{$p1/empSalary}</empSal></Result>
OUTPUT FOR QUERY2:
<Result>
<eName>
<empName>arthi</empName>
</eName>
<supName>
<empName>arthi</empName>
</supName>
<empSal>
<empSalary>900000</empSalary>
</empSal>
</Result>
<Result>
<eName>
<empName>maliga</empName>
</eName>
<supName>
<empName>arthi</empName>
</supName>
<empSal>
<empSalary>300000</empSalary>
</empSal>
</Result>
<Result>
<eName>
<empName>sindhu</empName>
</eName>
<supName>
<empName>arthi</empName>
</supName>
<empSal>
<empSalary>300000</empSalary>
</empSal>
</Result>
<Result>
<eName>
<empName>gg</empName>
</eName>
<supName>
<empName>arthi</empName>
</supName>
<empSal>
<empSalary>300000</empSalary>
</empSal>
</Result>
Query 3:
Retrieve the project name, controlling department name, number of employees and total hours
worked per week on the project for each project.
let $d1:=doc("D:/department.xml")
let $d2:=doc("D:/project.xml")
for $p1 in $d2/projects/project
let $dep:=$d1/departments/department[deptNo=$p1/projDeptNo]
return<Result><projName>{$p1/projName}</projName><depName>{$dep/deptName}
</depName><workers>{count($p1/projWorkers/projWorker/name)}</workers></Result>
OUTPUT FOR QUERY3:
<projName>
<projName>Web Mining</projName>
</projName>
<depName>
<deptName>Research</deptName>
</depName>
<workers>2</workers>
</Result>
<Result>
<projName>
<projName>Cloud Computing</projName>
</projName>
<depName>
<deptName>Research</deptName>
</depName>
<workers>2</workers>
</Result>
<Result>
<projName>
<projName>BusinessProcess</projName>
</projName>
<depName>
<deptName>Outsourcing</deptName>
</depName>
<workers>2</workers>
</Result>
<Result>
<projName>
<projName>KnowledgeProcess</projName>
</projName>
<depName>
<deptName>Outsourcing</deptName>
</depName>
<workers>1</workers>
</Result>
Query 4:
Retrieve the project name, controlling department name, number of employees and total hours
worked per week on the project for each project with more than one employee working on it.
let $d1:=doc("D:/department.xml")
let $d2:=doc("D:/project.xml")
for $p1 in $d2/projects/project
let $dep:=$d1/departments/department[deptNo=$p1/projDeptNo]
where count($p1/projWorkers/projWorker/name)>1
return<Result><projName>{$p1/projName}</projName><depName>{$dep/deptName}
</depName><workers>{count($p1/projWorkers/projWorker/name)}</workers></Result>
OUTPUT FOR QUERY4:
<Result>
<projName>
<projName>Web Mining</projName>
</projName>
<depName>
<deptName>Research</deptName>
</depName>
<workers>2</workers>
</Result>
<Result>
<projName>
<projName>Cloud Computing</projName>
</projName>
<depName>
<deptName>Research</deptName>
</depName>
<workers>2</workers>
</Result>
<Result>
<projName>
<projName>BusinessProcess</projName>
</projName>
<depName>
<deptName>Outsourcing</deptName>
</depName>
<workers>2</workers>
</Result>
Query 5:
Implement a storage structure for storing XML database
create table dept(deptno number(1),department xmltype);
insert into dept(deptno,department)values(1,XMLTYPE('
<dept>
<deptname>mech</deptname>
<deptmgrssn>1</deptmgrssn>
<deptmgrsd>17/10/2012</deptmgrsd>
<deptloc>chennai</deptloc>
</dept>')
);
select * from temproject;
DEPTNO
----------
PROJECT
--------------------------------------------------------------------------------
1
<project>
<name>ASp.net</name>
<no>1</no>
<location>chennai</location>
2
<project>
DEPTNO
----------
PROJECT
--------------------------------------------------------------------------------
<name>java</name>
<no>5</no>
<location>chennai</location>
<worker>
xQuery.html
<html>
<body>
<h1>Bookstore</h1>
<ul>
{
for $x in doc("books.xml")/bookstore/book
order by $x/title
return <li>{data($x/title)}. Category: {data($x/@category)}</li>
}
</ul>
</body>
</html>
PROCEDURE:
1. Type all project,employee and project and store it in their separate .xml files(eg: store it
in location D:\
2. Double click Basex.jar in BaseX folder
3. Goto Editor tabnew
4. Click file1 taband type/copy the query (copy it from Lab ex document)
5. Click save button give name as query1.xq
6. Then press run(green color button) result can be viewed if successful
7. Then add +tab which is nearer to query1.xq, type the next query and save as query2.xq.
8. Continue for as much query
Result
Thus the design a XML Schema and to implement the queries using XQuery and XPath.
is done and executed successfully.
No comments:
Post a Comment