Download - CP7211 Advanced Databases Laboratory Manual
-
8/10/2019 CP7211 Advanced Databases Laboratory Manual
1/63
-
8/10/2019 CP7211 Advanced Databases Laboratory Manual
2/63
DISTRIBUTED DATABASEAIM:
To develop and write SQL queries for a distributed database of BookStore at foursites S1, S2, S3 and S4. The Bookstores are divided into four sites by their ZIP codes.Distributed Database DesignDescription:
A distributed database is a database in which storage devices are not all attached to acommon processing unit such as the CPU, controlled by a distributed database managementsystem. (together sometimes called a distributed database system ). It may be stored in multiplecomputers, located in the same physical location; or may be dispersed over a network ofinterconnected computers. Unlike parallel systems, in which the processors are tightly coupledand constitute a single database system, a distributed database system consists of loosely-coupledsites that share no physical components.
There are two principal approaches to store a relation r in a distributed database system:A) ReplicationB) Fragmentation/Partitioning
A) Replication: In replication, the system maintains several identical replicas of the same relationr 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 r 1, r 2, r 3....r n in such a waythat the actual relation could be reconstructed from the fragments and then the fragments arescattered to different locations. There are basically two schemes of fragmentation:
Horizontal fragmentation - splits the relation by assigning each tuple of r to one ormore fragments.
Vertical fragmentation - splits the relation by decomposing the schema R ofrelation r.
Implementing distributed databases using SQL Server 2005
Linked servers provide SQL Server the ability to access data from remote data sources. Usingthese 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 sourcewithout a linked server.
DATE:
EX.NO:
-
8/10/2019 CP7211 Advanced Databases Laboratory Manual
3/63
Steps:1. Create Horizontal Partition:
First partition a table horizontally. In designing a partitioning scheme, it must be clear whatdata belongs to each member table. The original table is replaced with several smallermember 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 membertable is on a separate member server. For the greatest location transparency, the name of themember databases should be the same on each member server, although this is notrequired.Forexample: Server1.CustomerDB , Server2.CustomerDB , Server3.CustomerDB .
1.1 Creating Member TablesDesign the member tables so that each table stores a horizontal slice of the original table basedon a range of key values. The ranges are based on the data values in a partitioning column. Therange of values in each member table is enforced by a CHECK constraint on the partitioningcolumn, 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 anydata 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 proceduresp_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
-
8/10/2019 CP7211 Advanced Databases Laboratory Manual
4/63
Parameter Descriptionserver 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 notspecified, the provider name is the SQL Server data source. The explicit provider_name for SQL Server is SQLNCLI (for Microsoft SQL Native ClientOLE 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 informationrequired 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 memberserver, 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 acopy of the original table is on each member server, but each server has only a member table anda 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 iswritten 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 values(list of values);
select *from ;
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 1003. 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
-
8/10/2019 CP7211 Advanced Databases Laboratory Manual
5/63
(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 ONGO
SET QUOTED_IDENTIFIER ONGO
SET ANSI_PADDING ONGO
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 OFFGO
USE [S4]GO
/****** Object: Table [dbo].[BookStore] Script Date: 3/8/2014 3:07:23 AM ******/SET ANSI_NULLS ON
-
8/10/2019 CP7211 Advanced Databases Laboratory Manual
6/63
GO
SET QUOTED_IDENTIFIER ONGO
SET ANSI_PADDING ONGO
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 OFFGO
USE [S4]GOdrop table [Stock]
/****** Object: Table [dbo].[Stock] Script Date: 3/8/2014 3:07:50 AM ******/SET ANSI_NULLS ONGO
SET QUOTED_IDENTIFIER ONGO
SET ANSI_PADDING ONGO
CREATE TABLE [dbo].[Stock]([StoreNo] [uniqueidentifier] NOT NULL,[ISBN] [uniqueidentifier] NOT NULL,
-
8/10/2019 CP7211 Advanced Databases Laboratory Manual
7/63
[Qty] [int] NULL) ON [PRIMARY]
GO
SET ANSI_PADDING OFFGO
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.Booksselect * from s3.dbo.Booksselect * 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.BookStoreselect * from s2.dbo.BookStoreselect * from s3.dbo.BookStoreselect * 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'),
-
8/10/2019 CP7211 Advanced Databases Laboratory Manual
8/63
(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 booksselect * from s1.dbo.Books unionselect * from s2.dbo.Books unionselect * from s3.dbo.Books unionselect * from s4.dbo.Books
--details from bookstoreselect * from s1.dbo.BookStore unionselect * from s2.dbo.BookStore unionselect * from s3.dbo.BookStore unionselect * from s4.dbo.BookStore
--Details from stock
select * from s1.dbo.Stock unionselect * from s2.dbo.Stock unionselect * from s3.dbo.Stock unionselect * 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 unionselect * from s2.dbo.Books unionselect * from s3.dbo.Books unionselect * from s4.dbo.Books
)select * from BooksView
Create view BookStoreView as
( select * from s1.dbo.BookStore unionselect * from s2.dbo.BookStore unionselect * from s3.dbo.BookStore unionselect * from s4.dbo.BookStore
)select * from BookStoreView
-
8/10/2019 CP7211 Advanced Databases Laboratory Manual
9/63
Create view StockView as(
select * from s1.dbo.Stock unionselect * from s2.dbo.Stock unionselect * 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 bvinner join StockView sv on sv.ISBN=bv.ISBNinner join BookStoreView bvs on bvs.StoreNo=sv.StoreNowhere bv.Price between 15 and 55group 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 bvinner join s2.dbo.Stock sv on sv.ISBN=bv.ISBN
inner join s2.dbo.books bvs on bvs.StoreNo=sv.StoreNogroup by bv.ISBN
Result:
Thus the Distributed Database has been developed and executed successfully.
-
8/10/2019 CP7211 Advanced Databases Laboratory Manual
10/63
DEADLOCK DETECTION ALGORITHM FOR DISTRIBUTED DATABASE USINGWAIT- FOR GRAPH
Aim:
To implement Deadlock Detection Algorithm for Distributed Database using Wait-forGraph 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:
-
8/10/2019 CP7211 Advanced Databases Laboratory Manual
11/63
CODINGS:Connected to:Oracle9i Release 9.0.1.1.1 - ProductionJServer Release 9.0.1.1.1 - Production
SQL> create table dd1(trans varchar(20),loc varchar2(10),wait varchar2(10),sitevarchar2(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.
-
8/10/2019 CP7211 Advanced Databases Laboratory Manual
12/63
-
8/10/2019 CP7211 Advanced Databases Laboratory Manual
13/63
PL/SQL: Item ignoredORA-06550: line 2, column 9:PLS-00341: declaration of cursor 'C1' is incomplete or malformedORA-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 ormalformedORA-06550: line 19, column 1:PL/SQL: SQL Statement ignoredORA-06550: line 23, column 10:PLS-00320: the declaration of the type of this expression is incomplete ormalformedORA-06550: line 23, column 1:PL/SQL: Statement ignoredORA-06550: line 26, column 9:
PLS-00320: the declaration of the type of this expression is incomplete ormalformedORA-06550: line 26, column 1:PL/SQL: Statement ignoredORA-06550: line 29, column 10:PLS-00320: the declaration of the type of this expression is incomplete orSQL> 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 declaredORA-06550: line 23, column 1:PL/SQL: Statement ignoredSQL> ed dd1;SQL> @dd1;42 /
TRANS Lock waitt1 x1 x8t1 x6 x2t2 x4 x7t2 x5t3 x2 x7t4 x7t4 x8 x5t5 x3 x7x5
-
8/10/2019 CP7211 Advanced Databases Laboratory Manual
14/63
x7 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 nameORA-06550: line 3, column 1:PL/SQL: SQL Statement ignoredORA-06550: line 2, column 9:PLS-00341: declaration of cursor 'C1' is incomplete or malformedORA-06550: line 14, column 4:PL/SQL: Item ignoredORA-06550: line 19, column 15:PLS-00320: the declaration of the type of this expression is incomplete ormalformedORA-06550: line 19, column 1:PL/SQL: SQL Statement ignoredORA-06550: line 21, column 22:PLS-00320: the declaration of the type of this expression is incomplete ormalformedORA-06550: line 21, column 1:PL/SQL: Statement ignoredORA-06550: line 24, column 10:PLS-00320: the declaration of the type of this expression is incomplete ormalformedORA-06550: line 24, column 1:PL/SQL: Statement ignored
-
8/10/2019 CP7211 Advanced Databases Laboratory Manual
15/63
ORA-06550: line 27, column 10:PLS-00320: the declaration of the type of this expression is incomplete ormalformedORA-06550: line 27, column 1:PL/SQL: Statement ignored
ORA-06550: line 36, column 5:PLS-00201: identifier 'E' muSQL> 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 nameORA-06550: line 3, column 1:
PL/SQL: SQL Statement ignoredORA-06550: line 2, column 9:PLS-00341: declaration of cursor 'C1' is incomplete or malformedORA-06550: line 15, column 4:PL/SQL: Item ignoredORA-06550: line 20, column 15:PLS-00320: the declaration of the type of this expression is incomplete ormalformedORA-06550: line 20, column 1:PL/SQL: SQL Statement ignoredORA-06550: line 22, column 22:PLS-00320: the declaration of the type of this expression is incomplete ormalformedORA-06550: line 22, column 1:PL/SQL: Statement ignoredORA-06550: line 25, column 10:PLS-00320: the declaration of the type of this expression is incomplete ormalformedORA-06550: line 25, column 1:PL/SQL: Statement ignoredORA-06550: line 28, column 10:PLS-00320: the declaration of the type of this expression is incomplete ormalformedORA-06550: line 28, column 1:PL/SQL: Statement ignoredSQL> ed dd3;SQL> @dd3;41 /
TRANS Lock waitt1 x6 x2
-
8/10/2019 CP7211 Advanced Databases Laboratory Manual
16/63
t2 x4 x7t4 x7x7 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 nameORA-06550: line 3, column 1:
PL/SQL: SQL Statement ignoredORA-06550: line 2, column 9:PLS-00341: declaration of cursor 'C1' is incomplete or malformedORA-06550: line 14, column 4:PL/SQL: Item ignoredORA-06550: line 19, column 15:PLS-00320: the declaration of the type of this expression is incomplete ormalformedORA-06550: line 19, column 1:PL/SQL: SQL Statement ignoredORA-06550: line 21, column 22:PLS-00320: the declaration of the type of this expression is incomplete ormalformedORA-06550: line 21, column 1:PL/SQL: Statement ignoredORA-06550: line 24, column 10:PLS-00320: the declaration of the type of this expression is incomplete ormalformedORA-06550: line 24, column 1:PL/SQL: Statement ignoredORA-06550: line 27, column 10:PLS-00320: the declaration of the type of this expression is incomplete ormalformedORA-06550: line 27, column 1:PL/SQL: Statement ignoredSQL> ed dd4;SQL> @dd4;37 /
TRANS Lock waitt2 x5
-
8/10/2019 CP7211 Advanced Databases Laboratory Manual
17/63
t4 x8 x5t5 x3 x7x5 ed dd1;SQL> @dd1;42 /
TRANS Lock waitt1 x1 x8t1 x6 x2t2 x4 x7t2 x5t3 x2 x7t4 x7
t4 x8 x5t5 x3 x7x5
-
8/10/2019 CP7211 Advanced Databases Laboratory Manual
18/63
open c1;dbms_output.put_line('TRANS '||' '||'Lock'||' '||'wait');loopfetch 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 loopfor j in 1 .. d loopif ( ll(i) = l2(j)) thenif(ll(i) != '-')thendbms_output.put_line(ll(i)||'
-
8/10/2019 CP7211 Advanced Databases Laboratory Manual
19/63
loopfetch 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 loopfor j in 1 .. d loopif ( ll(i) = l2(j)) thendbms_output.put_line(ll(i)||'
-
8/10/2019 CP7211 Advanced Databases Laboratory Manual
20/63
ll(c) := ss.loc;d :=d+1;l2.extend;l2(d) := ss.wait;end loop;
for i in 1 .. c loopfor j in 1 .. d loopif ( ll(i) = l2(j)) thendbms_output.put_line(ll(i)||'
-
8/10/2019 CP7211 Advanced Databases Laboratory Manual
21/63
d :=d+1;l2.extend;l2(d) := ss.wait;end loop;for i in 1 .. c loop
for j in 1 .. d loopif ( ll(i) = l2(j)) thendbms_output.put_line(ll(i)||'
-
8/10/2019 CP7211 Advanced Databases Laboratory Manual
22/63
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))
gocreate 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)gocreate table dbo.StatusTable(ID int NOT NULL PRIMARY KEY,
[Status] varchar(10))gocreate table dbo.Users(UserID int NOT NULL identity(1,1) PRIMARY KEY,UserName varchar(30),[Address] varchar(100),
DATE:
EX.NO:
-
8/10/2019 CP7211 Advanced Databases Laboratory Manual
23/63
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))gocreate 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.departmentvalues('CSE')goinsert into dbo.departmentvalues('IT')goinsert into dbo.departmentvalues('ECE')goinsert into dbo.Rolesvalues('Employee')goinsert into dbo.Rolesvalues('Student')
goinsert into dbo.Gendervalues('Male')goinsert into dbo.Gendervalues('Female')goinsert into dbo.StatusTablevalues(1,'Active')goinsert into dbo.StatusTablevalues(2,'InActive')go
-
8/10/2019 CP7211 Advanced Databases Laboratory Manual
24/63
insert into dbo.SubRolesvalues(1,'Faculty')goinsert into dbo.SubRolesvalues(1,'Technicians')
goinsert into dbo.SubRolesvalues(1,'Project associates')goinsert into dbo.SubRolesvalues(2,'Full Time')goinsert into dbo.SubRolesvalues(2,'Part Time')goinsert into dbo.SubRoles
values(2,'Teaching Assistant')goinsert into users values('kalpana','2-Richie street, chennai',9840000000,1,2,2,2,1)--ii. Display the Employee details--a. Display General Employee details :selectUsername 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.DeptIDinner join Gender g on g.GenderID = u.Genderinner join SubRoles s on s.SubRoleID = u.SubRoleIDinner join StatusTable st on st.ID = u.StatusIDwhere u.RoleID = 1 --(Employee Role is filtered)
--b. Display Female Employee DetailsselectUsername as [EmployeeName],[Address] as [Address],Phone as [Contact],d.DeptName as [Department],g.Gender as [Gender],s.SubRoleName as [ROle],st.Status as [Status]
-
8/10/2019 CP7211 Advanced Databases Laboratory Manual
25/63
from users uinner join department d on d.DeptID = u.DeptIDinner join Gender g on g.GenderID = u.Genderinner join SubRoles s on s.SubRoleID = u.SubRoleIDinner 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 DetailsselectUsername 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 uinner join department d on d.DeptID = u.DeptIDinner join Gender g on g.GenderID = u.Genderinner join SubRoles s on s.SubRoleID = u.SubRoleIDinner join StatusTable st on st.ID = u.StatusID
where u.RoleID = 2 --(Student Role is filtered)--b.Display only full time studentsselectUsername 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.DeptIDinner join Gender g on g.GenderID = u.Genderinner join SubRoles s on s.SubRoleID = u.SubRoleIDinner join StatusTable st on st.ID = u.StatusIDwhere u.RoleID = 2 and u.SubRoleID = 4--(Student Role and Full-timeSubRole - filtered)iv Modify User details v Delete User details--iv Modify Person details--a.Update Phone number of a userUpdate Usersset Phone = 9840202000where userid = 1
-
8/10/2019 CP7211 Advanced Databases Laboratory Manual
26/63
-
8/10/2019 CP7211 Advanced Databases Laboratory Manual
27/63
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 parallelexecution servers when creating the table.
DATE:
EX.NO:
-
8/10/2019 CP7211 Advanced Databases Laboratory Manual
28/63
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.
-
8/10/2019 CP7211 Advanced Databases Laboratory Manual
29/63
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:
-
8/10/2019 CP7211 Advanced Databases Laboratory Manual
30/63
-
8/10/2019 CP7211 Advanced Databases Laboratory Manual
31/63
ACTIVE DATABASE IMPLEMENTATION OF TRIGGERS & ASSERTIONS FOR
BANK DATABASE
Aim:To create Triggers and Assertions for Bank Database handling deposits and loan andfor Admission Database handling seat allocation and vacancy position.
Description:
An active database is a database that includes an event-driven architecture which can respondto 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 placeand the condition has evaluated to true. In sum, if the specied event E occurs and if thecondition C is true then the specied actio n 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 thetriggering 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 oracledatabase. For example do not define trigger to implement integrity rules that can be done byusing declarative constraints.Elements in a Trigger: Trigger timingo For table: BEFORE, AFTERo 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:
-
8/10/2019 CP7211 Advanced Databases Laboratory Manual
32/63
Trigger body: PL/SQL blockBefore 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 tocomplete. This situation enables you to eliminate unnecessary processing of the triggeringstatement 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 triggeringaction and to perform a different action on the same triggering statement if a BEFORE trigger isalready present.Instead of Triggers are used to provide a transparent way of modifying views that cannot bemodified directly through SQL DML statements because the view is not inherently modifiable.You can write INSERT, UPDATE, and DELETE statements against the view. The INSTEADOF trigger works invisibly in the background performing the action coded in the trigger bodydirectly on the underlying tables.Triggering user events:
INSERT UPDATE
DELETETrigger 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
TimingEvent1 [OR event2 OR event3]ON table_name
Trigger_bodyAssertions
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 ofassertion.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 CHECK predicate Problem Statement:Create triggers and assertions for Bank database handling deposits and loan and admissiondatabase handling seat allocation and vacancy position. Design the above relational databaseschema and implement the following triggers and assertions.
-
8/10/2019 CP7211 Advanced Databases Laboratory Manual
33/63
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 dep09for each row begin
if :new.damt>0 thenupdate 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 customers loan accountand bank account.
Create or replace trigger t2 after insert on loan09for each row beginif :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 andvacancy position.
Create or replace trigger t3 after insert on ad09for each rowdeclarea number(5):=0; beginselect vp into a from vp09 where dept=:new.dept;if a>0 thenupdate 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 TableUSE [Bank]GO
-
8/10/2019 CP7211 Advanced Databases Laboratory Manual
34/63
/****** Object: Table [dbo].[Customer] Script Date: 01/01/2014 18:27:59 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ON
GOSET ANSI_PADDING ONGOCREATE TABLE [dbo].[Customer](
[CNO] [int] NOT NULL,[FirstName] [varchar](50) NULL,[LastName] [varchar](50) NULL,[ZipCode] [nvarchar](50) NULL
) ON [PRIMARY]GOSET ANSI_PADDING OFF
GOData to be insertedCNO FirstName LastName ZipCode1 Shyam Sundar 61202 Somes Thani 606613 Aswin Arun 66774 Shankar S 7667
CustomerAccountUSE [Bank]GO
/****** Object: Table [dbo].[CustomerAccount] Script Date: 01/01/2014 18:29:22 ******/SET ANSI_NULLS ONGO
SET QUOTED_IDENTIFIER ONGO
SET ANSI_PADDING ONGO
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
-
8/10/2019 CP7211 Advanced Databases Laboratory Manual
35/63
-
8/10/2019 CP7211 Advanced Databases Laboratory Manual
36/63
CustomerLoanAccount
USE [Bank]GO
/****** Object: Table [dbo].[CustomerLoanAccount] Script Date: 01/01/2014 18:29:43******/SET ANSI_NULLS ONGO
SET QUOTED_IDENTIFIER ONGO
SET ANSI_PADDING ONGO
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 OFFGO
CLNO LoanName Location Amount CNO3001 0 Chennai 500 13002 0 Chennai 0 23003 0 Chennai 0 3
Bandmaster
USE [Bank]GO
/****** Object: Table [dbo].[BankMaster] Script Date: 01/01/2014 18:29:57 ******/SET ANSI_NULLS ONGO
SET QUOTED_IDENTIFIER ONGO
-
8/10/2019 CP7211 Advanced Databases Laboratory Manual
37/63
SET ANSI_PADDING ONGO
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 OFFGOBankID Name Allocated Vacancy1 LLFC 4 196
The scenarios are explained using the following triggers
When a deposit is made by a customer, create a trigger for updatingcustomers account and bank account
USE [Bank]GO/****** Object: Trigger [dbo].[CustomerAccountTrigger] Script Date: 01/01/2014 18:21:42******/
SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOALTER TRIGGER [dbo].[CustomerAccountTrigger]
ON [dbo].[CustomerAccount]AFTER INSERT,UPDATE
ASBEGIN
-- SET NOCOUNT ON added to prevent extra result sets from-- interfering with SELECT statements.
Declare @amount intDeclare @cno int
select @amount=amount,@cno=CNO from inserted
SET NOCOUNT ON;update CustomerBankAccount set Amount=@amount where CNO=@cno
-
8/10/2019 CP7211 Advanced Databases Laboratory Manual
38/63
END
Create assertion for bank database so that the total loan amount does notexceed
the total balance in the bank.
When a loan is issued to the customer, create a trigger for updatingcustomers loan account and bank account.
USE [Bank]GO/****** Object: Trigger [dbo].[CustomerLoanTrigger] Script Date: 01/01/2014 18:22:13******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOALTER TRIGGER [dbo].[CustomerLoanTrigger]
ON [dbo].[CustomerAccount]AFTER INSERT,UPDATE
ASBEGIN
-- SET NOCOUNT ON added to prevent extra result sets from-- interfering with SELECT statements.Declare @loanamount as intDeclare @cno as intDeclare @balance as decimal(18,0)
select @loanamount=ApprovedLoanAmount,@cno=CNO,@balance=Amount frominserted
select @balance=Amount from CustomerBankAccount where CNO=@cno
if(@loanamount>@balance)Begin
RAISERROR ('Loan Amount should not exceed the balance!',10, 1)ROLLBACK
endSET NOCOUNT ON;update CustomerBankAccount set loanamount=@loanamount where CNO=@cnoupdate CustomerLoanAccount set Amount=@loanamount where CNO=@cno
END
-
8/10/2019 CP7211 Advanced Databases Laboratory Manual
39/63
When an admission is made, create a trigger for updating the seat allocationdetails and vacancy position.
USE [Bank]GO/****** Object: Trigger [dbo].[BankMasterTrigger] Script Date: 01/01/2014 18:21:09******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOALTER TRIGGER [dbo].[BankMasterTrigger]
ON [dbo].[Customer]AFTER INSERT,UPDATE
ASBEGIN
-- 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)Beginupdate BankMaster set Allocated=Allocated+1,Vacancy=Vacancy-1End
END
RESULT:Thus the Active Database fo r Implementation of Triggers & Assertions for Bank
database has been developed and executed successfully.
-
8/10/2019 CP7211 Advanced Databases Laboratory Manual
40/63
DEDUCTIVE DATABASE
AIM:
To construct a knowlegde database for kinship domain(family relations) withfacts.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 onformal logic. Unlike traditional programing language that are based on performing sequences ofcommands,Prolog is based on defining and then solving logical formulas. Prolog is sometimescalled a declarative language or a rule-based language because its programs consists of a list offacts and rules. Prolog is used widely for artificial intelligence applications, particularly expert
systems.
PROCEDURE:
Start>>All programs>>SWI-Prolog>>Prolog
DATE:
EX.NO:
-
8/10/2019 CP7211 Advanced Databases Laboratory Manual
41/63
File>>New
Type the filename and select save
-
8/10/2019 CP7211 Advanced Databases Laboratory Manual
42/63
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).
-
8/10/2019 CP7211 Advanced Databases Laboratory Manual
43/63
File>Save As
Enter the filename with extension .pl
Compile the code
Compile>>Compile buffer
-
8/10/2019 CP7211 Advanced Databases Laboratory Manual
44/63
Execute with the commands
; at the end of statement indicates to display next valid data. . at the end of statements will endthe display of futher data even if available.
To Edit existing file File>>Edit>>select the name of the file to be edited.Make themodification and use File>>Save As to save the file and Click File>>Reload Modified Filesto load the currently saved document and check the commands.
RESULT:
Thus the knowledge database for kinship domain with facts has been created and therules have been executed.
-
8/10/2019 CP7211 Advanced Databases Laboratory Manual
45/63
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 helpof WEKA tool.
RID Age Income Student Credit_rating Class:buys_computer1 youth high no fair no2 youth high no excellent no3 middle_aged high no fair yes4 senior medium no fair yes5 senior low yes fair yes6 senior low yes excellent no7 middle_aged low yes excellent yes8 youth medium no fair no9 youth low yes fair yes10 senior medium yes fair yes11 youth medium yes excellent yes12 middle_aged medium no excellent yes13 middle_aged high yes fair yes14 senior medium no excellent no
DATE:
EX.NO:
-
8/10/2019 CP7211 Advanced Databases Laboratory Manual
46/63
Snapshots:
-
8/10/2019 CP7211 Advanced Databases Laboratory Manual
47/63
-
8/10/2019 CP7211 Advanced Databases Laboratory Manual
48/63
-
8/10/2019 CP7211 Advanced Databases Laboratory Manual
49/63
-
8/10/2019 CP7211 Advanced Databases Laboratory Manual
50/63
QUERY PROCESSING IMPLEMENTATION OF AN EFFICIENT QUERY
OPTIMIZER
AIM:
To implement Query Optimizer with Relational Algebraic expression construction andexecution plan generation for choosing an efficient execution strategy for processing thegiven 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 SanFrancisco
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:
-
8/10/2019 CP7211 Advanced Databases Laboratory Manual
51/63
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 andexecuted successfully.
-
8/10/2019 CP7211 Advanced Databases Laboratory Manual
52/63
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 ElementThe element is the root element of every XML Schema.The ElementThe element is the root element of every XML Schema:...
...A simple element is an XML element that can contain only text. It cannot contain any otherelements or attributes.
2. Defining a Simple ElementThe syntax for defining a simple element is: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 AttributesAll 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 acomplex type. But the attribute itself is always declared as a simple type.
How to Define an Attribute?The syntax for defining an attribute is: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:
-
8/10/2019 CP7211 Advanced Databases Laboratory Manual
53/63
-
8/10/2019 CP7211 Advanced Databases Laboratory Manual
54/63
for $x in doc("books.xml")/bookstore/bookwhere $x/price>30return $x/titleThe result will be:XQuery Kick Start
Learning XMLWith FLWOR you can sort the result:for $x in doc("books.xml")/bookstore/bookwhere $x/price>30order by $x/titlereturn $x/titleFLWOR 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 storedand 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 databaseDepartment ( deptName, deptNo, deptManagerSSN, deptManagerStartDate,deptLocation )Employee ( empName, empSSN, empSex, empSalary, empBirthDate,empDeptNo,empSupervisorSSN, empAddress, empWorksOn)Project ( projName, projNo, projLocation, projDeptNo, projWorker )
//project.xml
Web Mining111Chennai1
dhanu
-
8/10/2019 CP7211 Advanced Databases Laboratory Manual
55/63
jeyaraman
Cloud Computing112Chennai1
arthijeyaraman
BusinessProcess221Chennai2
dhanushyaJ
KnowledgeProcess222Chennai2
dhanu
//department.xml
Research1
-
8/10/2019 CP7211 Advanced Databases Laboratory Manual
56/63
-
8/10/2019 CP7211 Advanced Databases Laboratory Manual
57/63
-
8/10/2019 CP7211 Advanced Databases Laboratory Manual
58/63
25Female3000003-5-902
22annanagarchennaiKnowledgeProcess
Query 1:Retrieve the department name, manager name, and manager salary for every departmentlet $d1:=doc("D:/department.xml")let $d2:=doc("D:/employee.xml")for $p1 in $d1/departments/departmentfor $p2 in $d2/employees/employee
where $p1/deptMgrSSN=$p2/empSSNreturn{$p1/deptName}{$p2/empName}{$p2/empSalary}
OUTPUT FOR QUERY1:
Research
arthi
900000
Outsourcing
sruthi
900000
-
8/10/2019 CP7211 Advanced Databases Laboratory Manual
59/63
Query 2:Retrieve the employee name, supervisor name and employee salary for each employee whoworks 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/employeewhere $p1/empDeptNo=$r/deptNoreturn{$p1/empName}{$sup/empName}{$p1/empSalary}
OUTPUT FOR QUERY2:
arthi
arthi
900000
maliga
arthi
300000
sindhu
arthi
300000
-
8/10/2019 CP7211 Advanced Databases Laboratory Manual
60/63
-
8/10/2019 CP7211 Advanced Databases Laboratory Manual
61/63
Outsourcing2
KnowledgeProcessOutsourcing
1
Query 4:Retrieve the project name, controlling department name, number of employees and total hoursworked 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/projectlet $dep:=$d1/departments/department[deptNo=$p1/projDeptNo]where count($p1/projWorkers/projWorker/name)>1return{$p1/projName}{$dep/deptName}{count($p1/projWorkers/projWorker/name)}
OUTPUT FOR QUERY4:
Web Mining
Research
2
Cloud Computing
Research
2
-
8/10/2019 CP7211 Advanced Databases Laboratory Manual
62/63
BusinessProcessOutsourcing
2Query 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('mech1
17/10/2012chennai'));
select * from temproject;DEPTNO
----------PROJECT--------------------------------------------------------------------------------
1
ASp.net1chennai
2
DEPTNO----------PROJECT--------------------------------------------------------------------------------
java5chennai
-
8/10/2019 CP7211 Advanced Databases Laboratory Manual
63/63
xQuery.html
Bookstore{for $x in doc("books.xml")/bookstore/bookorder by $x/titlereturn {data($x/title)}. Category: {data($x/@category)}}
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 folder3. Goto Editor t ab new4. 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 successful7. Then add +tab which is nearer to query1.xq, type the next query and save as query2.xq.8. Continue for as much query