M.Sc. - I.T.: Adbms

M.Sc. – I.T.
Semester II
ADBMS
Sr. No. Title Page No
1 Horizontal fragmentation of database.
2 Vertical fragmentation of database

Creating Replica of database.
3
Create Temporal Database.
4
Implement Active database using Triggers
5
Create ORDBMS Application
6
Implement and retrieve records from a Spatial Database
7
XML Application
8
Practical No.1
Aim:Horizontal fragmentation of database.
Question: Create a global conceptual schema Emp (Eno, Ename, Address,

Email, Salary) and insert 10 records. Divide Emp into horizontal fragments
using the condition that Emp contains tuples with salary < 5000 and Emp with
5000 < salary < 20000 on two different nodes. Fire the following queries:
a) Find the salary of all employees.

b) Find the Email of all employees where salary=4000.
c) Find the employee name and Email where employee number is known.
d) Find the employee name and address where employee number is known.
STEPS FOR CREATING A DISTRIBUTED DATABASE:
*Similarly create database orcl2
Open sqlplus command prompt and login as SYSTEM (USERNAME ) and
admin (PASSWORD)
Open ORCL1 Database:
connect SYSTEM/admin@orcl1;
Creating Link In ORCL1:

create database link conorcl1 connect to SYSTEM identified by
admin using '(DESCRIPTION = (ADDRESS_LIST = (ADDRESS =
(PROTOCOL = TCP) (HOST = localhost)(PORT =1521)))(CONNECT_DATA
=(SID = orcl1)))';
Creating Table emp:

create table emp(employee_id number primary key,name
varchar2(20),address varchar2(20),email varchar2(20),salary
number,doj date);
Inserting Values Into Table emp:

insert into emp
values(1001,'swapnil','Airoli','sj@gmail.com',2000,'2-jan-
2010');
insert into emp
values(1002,'vijay','kalwa','vm@gmail.com',2100,'3-jan-2010');
insert into emp
values(1003,'dipesh','mulund','dk@gmail.com',2456,'7-jan-
2010');
insert into emp
values(1004,'sunita','mulund','sy@gmail.com',4000,'8-jan-
2010');
insert into emp
values(1005,'sibi','dombivli','st@gmail.com',3000,'8-feb-
2010');
Creating Link in ORCL2:

(PROTOCOL = TCP) (HOST = localhost)(PORT =
1521)))(CONNECT_DATA =(SID = orcl2)))';
Creating Table emp:

create table emp(employee_id number primary key,name
varchar2(20),address varchar2(20),email varchar2(20),salary
number,doj date);
Inserting Values into Table emp:

insert into emp
values(1006,'siddh','dombivli','sm@gmail.com',6000,'2-feb-
2010');
insert into emp
values(1007,'chandu','thane','cc@gmail.com',8199,'13-mar-
2010');
insert into emp
values(1008,'raja','mulund','rk@gmail.com',9456,'6-jan-2010');
insert into emp
values(1009,'anup','mulund','ak@gmail.com',10000,'18-feb-
2010');
insert into emp
values(1010,'sanket','dombivli','sk@gmail.com',13000,'8-mar-
2010');
Creating view hrz_view:

Create view hrz_view as select * from emp UNION select * from
emp@conorcl2;
Output Queries:
1) Find the salary of all employees.

SQL>select name,salary from emp union select e1.name,e1.salary
from emp@conorcl1 e1;
Output:
NAME SALARY
-------------------- ----------
anup 10000
chandu 8199
dipesh 2456
raja 9456
sanket 13000
sibi 3000
siddh 6000
sunita 10000
swapnil 2000
vijay 2100
10 rows selected.
2) Find the Email of all employees where salary=4000.

SQL>select name,salary,email from emp where salary >4000 union
select e1.name,e1.salary,e1.email from emp@conorcl1 e1 where
e1.salary>4000;
Output:
NAME SALARY EMAIL
-------------------- ---------- -------------------
anup 10000 ak@gmail.com
chandu 8199 cc@gmail.com
raja 9456 rk@gmail.com
sanket 13000 sk@gmail.com
siddh 6000 sm@gmail.com
sunita 10000 sy@gmail.com
6 rows selected.
3 ) Find the employee name and Email where employee number is
known
SQL>select name,address from emp where employee_id=1005 union
select e1.name ,e1.address from emp@conorcl1 e1 where
e1.employee_id=1005
Output:
NAME ADDRESS
-------------------- --------------------
sibi dombivli
Practical No.2
Aim:Vertical fragmentation of database.
Question: Create a global conceptual schema Emp ( Employee_id,

first_name,last_name,email,address1,address2,address3,doj,Salary,designation)
and insert 10 records. Divide Emp into vertical fragments
Emp(Employee_id,first_name,last_name,email) and emp(Employee_id
,address1,address2,address3),Emp(Employee_id,doj,Salary ,designation)
on two different nodes. Fire the following queries:
a) Find the salary of an Employee where employee number is known.
b) Find the Email where the employee name is known.
c) Find the employee name and Email where employee number is known.
d) Find the employee name whose salary is > 200.
Solution : create databases orcl,orcl1,orcl2 as shown above
Open ORCL Database:

connect SYSTEM/admin@orcl;
Creating link in ORCL:

create database link conorcl connect to SYSTEM identified by
(PROTOCOL = TCP)(HOST = localhost)(PORT = 1521)))(CONNECT_DATA
=(SID = orcl )))';
Creating Table emp:

create table emp(employee_id number primary key,first_name
varchar2(20),last_name varchar2(20),email varchar2(20));

insert into emp
values(1001,'swapnil','jadhav','sj78@gmail.com');
insert into emp
values(1002,'vijay','mhatre','112.vijay@gmail.com');
insert into emp
values(1003,'dipesh','kamble','daku36@gmail.com');
insert into emp values(1004,'sunita','yadav','sy@gmail.com');
insert into emp
values(1005,'hunusukh','wangadu','hw@gmail.com');
Connect SYSTEM/admin@orcl1;

Creating Table emp:

create table emp(employee_id number primary key,address1
varchar2(20),address2 varchar2(20),address3 varchar2(20));

insert into emp values(1001,'near','khadi','airoli gaon');
insert into emp values(1002,'mhatre','nivas','kalwa country');
insert into emp values(1003,'6f','nilsagar daku
colony','mulund');
insert into emp values(1004,'33f','samshan','mulund');
insert into emp values(1005,'4a','ladakh','china');
Creating Link In ORCL3:

Creating Table emp:

create table emp(employee_id number primary key,doj date,
salary number,designation varchar2(20));

insert into emp values(1001,'17-dec-2009',200,'Developer');
insert into emp values(1002,'10-jan-2010',250,'SR developer');
insert into emp values(1003,'10-jan-2010',250,'Entertainer');
insert into emp values(1004,'1-jan-2010',251,'manager');
insert into emp values(1005,'7-jan-2010',500,'sr manager');
Creating view vrt_view:

create view vrt_view as select
e1.employee_id,e1.first_name,e1.last_name,e1.email,e2.address1
,e2.address2,e2.address3,e3.doj,e3.salary,e3.designation from
emp e1,emp@conorcl1 e2,emp@conorcl2 e3 where
e1.employee_id=e2.employee_id and
e2.employee_id=e3.employee_id;
Output Queries:
1) Find the salary of an Employee where employee number is

known.
SQL>select e1.employee_id,e1.first_name,e2.salary from emp
e1,emp@conorcl2 e2 where e1.employee_id=e2.employee_id and
e1.employee_id=1001;
Output:
EMPLOYEE_ID FIRST_NAME SALARY

----------- -------------------- ----------
1001 swapnil 200
2) Find the Email where the employee name is known.

SQL>select e1.employee_id,e1.first_name,e1.email from emp e1
where e1.first_name='vijay'
Output:
EMPLOYEE_ID FIRST_NAME EMAIL
----------- -------------------- --------------------
1002 vijay 112.vijay@gmail.com
3) Find the employee name and Email where employee number is

known.
SQL> select e1.employee_id,e1.first_name,e1.email from emp e1

where e1.employee_id=1001;
Output:
EMPLOYEE_ID FIRST_NAME EMAIL
----------- -------------------- --------------------
1001 swapnil sj78@gmail.com
4) Find the employee name whose salary is > 200.
SQL>select e1.employee_id,e1.first_name,e2.salary from emp
e1,emp@conorcl2 e2 where e1.employee_id=e2.employee_id
and e2.salary>=200;
Output:
EMPLOYEE_ID FIRST_NAME SALARY
----------- -------------------- ----------
1001 swapnil 200
1002 vijay 250
1003 dipesh 250
1004 sunita 251
1005 hunusukh 500
Practical No.3
Aim:Creating Replica of database.
Question: Create global conceptual schema Emp (Eno , Ename, Address, Email,
Salary) and insert 10 records. Store the replication of Emp into two different
nodes and fire the following queries.
a) Find the salary of all employees.

b) Find the email of all employees where salary = 15000.
c) Find the employee name and email where employee number is
Known.
d) Find the employee name and address where employee number is
Known.

Creating Table emp:

create table emp (eno number, ename varchar2(20), address
varchar(20), email varchar2(20), salary number);

Creating Table emp:

create table emp (eno number, ename varchar2(20), address
varchar(20), email varchar2(20), salary number);
Creating Trigger emptrigger:
create or replace trigger emptrigger
after insert or update of ename,address,email,salary or delete
on emp
for each row
begin
if inserting then
INSERT INTO emp@conorc1
VALUES(:new.eno,:new.ename,:new.address,:new.email,:new.salary
);
INSERT INTO emp@conorcl2
VALUES(:new.eno,:new.ename,:new.address,:new.email,:new.salary
);
end if;
if updating then
update emp@conorcl1 set ename=:new.ename where eno=:new.eno;
update emp@conorcl2 set ename=:new.ename where eno=:new.eno;
update emp@conorcl1 set address=:new.address where
eno=:new.eno;
update emp@conorcl2 set address=:new.address where
eno=:new.eno;
update emp@conorcl1 set email=:new.email where eno=:new.eno;
update emp@conorcl2 set email=:new.email where eno=:new.eno;
update emp@conorcl1 set salary=:new.salary where eno=:new.eno;
update emp@conorcl2 set salary=:new.salary where eno=:new.eno;
end if;
if deleting then
delete from emp@conorcl1 where eno=:new.eno;
delete from emp@conorcl2 where eno=:new.eno;
end if;
end;
/
Output Queries:
A) Inserting values into employee table:

insert into emp
values(1002,'vijay','mhatre','112.vijay@gmail.com',3000);
1 row created.
SQL> select * from emp
Output:
E_ID ENAME ADDRESS EMAIL
---------- --------------- ------------------------- ---------
----------------
SALARY
----------
101 Vijay Kalwa vijay@gmail.com
10000
Connect to orcl1 database:

SQL> select * from emp;
Output:
ENO ENAME ADDRESS EMAIL
---------- --------------- ------------------------- ---------
----------------
SALARY
----------
10000


Output:
---------- --------------- ------------------------- ---------
----------------
SALARY
----------
10000
B) Updating the values into employee table:

SQL> update emp set salary = 20000 where e_id=101 ;
1 row updated
Output:
E_ID ENAME ADDRESS EMAIL SALARY
---------- --------------- -------------------------
101 Vijay Kalwa vijay@gmail.com 20000

Output:
---------- --------------- ------------------------- ---------
----------------
SALARY
----------
20000


Output:
---------- --------------- ------------------------- ---------
----------------
SALARY
----------
20000
C) Deleting the values into employee table:

SQL> delete from emp where eno=101;
Output:
1 row deleted.
SQL> select * from emp ;
Output:
no rows selected

Fire the query:

Output:
no rows selected

Fire the query:

Output:
no rows selected
Practical No.4
Aim: implement Temporal Database.
A] Create a table tbl_shares, which stores the, name of company,

number of shares, and price per share at transaction time. Insert 10 records
and fire the following queries.
1) Find all the names of a company whose share price is more than
Rs.100 at 11:45 A.M.
2)Find the name of company which has highest shares price at 5.00
P.M.
B] Create a table employees, which stores the, employee_id, name,

department, salary at transaction time using Time DB .insert 3 records
and fire the following queries.
1)Find all the details where employee_id=10;

A] Temporal Databases:
Creating Table tbl_shares15:
Create table tbl_shares15
(
cname varchar2(20),
nofshares number(5),
pricepshare number(5),
transtime timestamp(6)
)
Inserting Values Into tbl_shares15:

insert into tbl_shares15 values(‘Reliance
Infocom’,250,25,systimestamp);
insert into tbl_shares15 values('Tata',205,20,'05-jun-04
11.45.00.000000 am');
insert into tbl_shares15 values('Wipro',250,25,'10-mar-03
06.15.00.000000 pm');
insert into tbl_shares15 values('Apple',115,15,'08-may-01
07.25.00.000000 am');
insert into tbl_shares15 values('TCS',140,12,'14-apr-05
05.30.00.000000 pm');
insert into tbl_shares15 values('Google',310,30,'12-sep-03
10.30.00.000000 am');
insert into tbl_shares15 values('Hero Honda',100,250,'21-
aug-04 05.30.00.000000 pm');
Queries:
1) Find all the names of a company whose share price is more than
Rs.100 at 11:45 A.M.
select cname from tbl_shares15
where pricepshare>15
and to_char(transtime,'HH12:MI:AM')='11:45:AM'
Output:
cname
--------------------
Tata
2) Find the name of company which has highest shares price
at 5.00 P.M.
select cname from tbl_shares15 where pricepshare in(select
max(pricepshare) from tbl_shares15 where
to_char(transtime,'HH12:MI:AM')='05:30:PM')
Output:
cname
--------------------
Hero Honda
B] Temporal Database Using TimeDB:

Creating Table:
CREATE TABLE Employees (EmpID INTEGER, Name CHAR(30),
Department CHAR(40), Salary INTEGER)
AS VALIDTIME;
Inserting Values Into Table:

VALIDTIME PERIOD [1985-1990)
INSERT INTO Employees VALUES (10, 'John', 'Research', 11000);
VALIDTIME PERIOD [1990-1993)

INSERT INTO Employees VALUES (10, 'John', 'Sales', 11000);
VALIDTIME PERIOD [1993-forever)

INSERT INTO Employees VALUES (10, 'John', 'Sales', 12000);
Output:
Queries:
VALIDTIME
SELECT * FROM Employees where empid=10;
Output:
Practical No.5
Aim: Implement Active database using PL/SQL.
Question: Create table emptab (eno, ename, hrs, pno, super_no) and project
(pname, pno, thrs, head_no) where thrs is the total hours and is the derived
attribute. Its value is the sum of all employees working on that project. eno and
pno are primary keys, head_no is foreign key to emp relation. Insert 10 tuples
and write triggers to do the following.
a) Creating a trigger to insert new employee tuple and display the new total
hours from project table.
b) Creating a trigger to change the hrs of existing employee and display the
new total hours from project table.
c) Creating a trigger to change the project of an employee and display the
d) Creating a trigger to delete the project of an employee.
Creating Table emptab:
Create table emptab(eno number primary key, ename varchar(12),

hrs number, pno number, super_no number)
Creating Table project:

create table project(pname varchar2(20),pno number primary
key,thrs number,head_no number references emptab(eno))
Inserting into table emptab:

insert into emptab values(1001,'Big Boss',55,10,null);
insert into emptab values(1002,'vijay',155,20,1001);
insert into emptab values(1003,'dipesh',175,40,1001);
insert into emptab values(1004,'sunita',455,30,null);
insert into emptab values(1005,'anup',155,10,1003);
insert into emptab values(1006,'swapnil',110,20,1002);
insert into emptab values(1007,'chandu',55,10,1004);
insert into emptab values(1008,'sibi',255,30,1002);
insert into emptab values(1009,'sid',155,10,1005);
insert into emptab values(1010,'raja',235,40,1006);
Inserting into table project:
insert into project values('Bank Management',10,22,1001);

insert into project values('Hospital Management’,20,220,1002);
insert into project values('Speech Recognation',30,220,1004);
insert into project values('Cyber Cafe',40,220,1003);
Updating a table project:
update project set thrs=thrs+(select sum(hrs) from emptab

where pno=10)
where pno=10;

where pno=20)
where pno=20;

where pno=30)
where pno=30;

where pno=40)
where pno=40;
Creating a Triggers:
a)Creating a trigger to insert new employee tuple and display the

create or replace trigger emptrigg

after insert on emptab
when(New.pno is not null)
for each row
begin
update project
set thrs=thrs+:new.hrs
where pno=:new.pno;
end;
Queries:
SQL>insert into emptab values(1011,'abc',21,10,1001)
SQL> SELECT * FROM PROJECT;
Output:
PNAME PNO THRS HEAD_NO
-------------------- ---------- ---------- ---------
Bank Management 10 331 1001
Hospital Management 20 75 1002
Speech Recognation 30 710 1004
Cyber Cafe 40 520 1003
b)Creating a trigger to change the hrs of existing employee and

display the new total hours from project table.
SQL> create or replace trigger emptrigg1
after update on emptab
for each row
begin
update project
set thrs=thrs-:old.hrs+:new.hrs
where pno=:new.pno;
end;
SQL>UPDATE EMPTAB
SET HRS=100
WHERE ENO=1001;
Output:
-------------------- ---------- ---------- ----------
c) Creating a trigger to change the project of an employee and

display the new total hours from project table.
SQL> create or replace trigger emptrigg2
after update of pno on emptab
for each row
begin
update project
set thrs=thrs+:new.hrs
where pno=:new.pno;
update project
set thrs=thrs-:old.hrs
where pno=:old.pno;
end;
SQL> update emptab

set pno=10
where eno=1001
/
Output:
-------------------- ---------- ---------- ----------
d)Creating a trigger to delete the project of an employee
create or replace trigger thrs_4

after update of pno on emptab
for each row
when(old.pno is not null)
begin
update project
set thrs=thrs-:old.hrs
where pno=:new.pno;
end;
/
SQL> update emptab

set pno=10
where eno=1008;
Output:
-------------------- ---------- ---------- ----------
Practical No.6
Aim : implement object oriented database
Solution:
Creating types and tables
SQL>
Create or replace type AddrType1 as object (Pincode number (5), Street char (20),City varchar2(50), state varchar2(40),
no number(4) );
Type created.
SQL>
create or replace type BranchType as object (address AddrType1, phone1 integer,phone2 integer );
Type created.
SQL> create or replace type BranchTableType as table of BranchType;
2/
Type created.
SQL> create or replace type AuthorType as object (name varchar2 (50), addr AddrType1)2 /
Type created.
SQL>
create table Authors of AuthorType;
Table created.
SQL>
create or replace type AuthorListType as varray(10) of ref AuthorType
2/
Type created.
SQL>
create or replace type PublisherType as object(name varchar2(50), addr AddrType1,branches BranchTableType)
SQL>/
Type created
SQL>
create table Publishers of PublisherType NESTED TABLE branches STORE as branchtable
SQL> /
Table created.
SQL>
create table books(title varchar2(50), year date, published_by ref PublisherType,authors AuthorListType)
SQL> /
Table created.
Inserting values in the tables

SQL> insert into Authors values('Sangoi', AddrType1(7000,'Dalal street', 'mumbai','maharashtra',1007));
1 row created.
SQL>
insert into Authors values('Rabiner', AddrType1(7007,'Singh street', 'mumbai','maharashtra',1006));
1 row created.
SQL>
insert into Authors values('Schiller',AddrType1(7008,'Pali street', 'nasik','maharashtra',1008));
1 row created.
SQL>
insert into Authors values('Jerry',AddrType1(7003,'Tagore street', 'mumbai','maharashtra',1003));
1 row created.
SQL>
insert into Authors values('Sangoi',AddrType1(7008,'Dalal street', 'mumbai','maharashtra',1007));
1 row created.
SQL >
insert into Authors values ('A.K.Mehta', AddrType1 (7006,'Nehru street','mumbai','maharashtra',1005));
1 row created
SQL> insert into Authors values ('Ramakrishnan', AddrType1(8002,'Thakur street','pune','maharashtra',13));
1 row created
SQL> insert into Authors values('Richard',AddrType1(7002,'Flower street','pune','maharashtra',03));
1 row created.
SQL>
select * from Authors;
NAME ADDR (PINCODE, STREET, CITY, STATE, NO)
Sangoi ADDRTYPE1 ( 7000, 'Dalal street ‘, 'mumbai', 'maharashtra', 1007)
Rabiner ADDRTYPE1 ( 7007, 'Singh street ', 'mumbai', 'maharashtra', 1006)
Schiller ADDRTYPE1 ( 7008, 'Pali street ', 'nasik', 'maharashtra', 1008)
Jerry ADDRTYPE1 ( 7003, 'Tagore street ', 'mumbai', 'maharashtra', 1003)
Sangoi ADDRTYPE1 ( 7008, 'Dalal street ', 'mumbai', 'maharashtra', 1007)
A.K.Mehta ADDRTYPE1 ( 7006, 'Nehru street ‘, 'mumbai', 'maharashtra', 1005)
Ramakrishnan ADDRTYPE1 (8002, 'Thakur street ', 'pune', 'maharashtra', 13)
Richard ADDRTYPE1 (7002, 'Flower street ‘, 'pune', 'maharashtra', 3)
8 rows selected.
Inserting values in table Publishers
SQL> insert into Publishers values('Raj', AddrType1(4002,'Park street', 'mumbai','maharashtra',03),

BranchTableType(BranchType(AddrType1(5002,'Pali street','mumbai','maharashtra',03),23406,69896)));
1 row created.
SQL> insert into Publishers values('Rohit',AddrType1(7007,'Lovely street','mumbai','maharashtra',07),

BranchTableType(BranchType(AddrType1(7007,'K street','mumbai','maharashtra',1007),4543545,8676775)));
1 row created.
SQL>
insert into Publishers values('Tata',AddrType1(7008,'Jewel
street','mumbai','maharashtra',27),BranchTableType(BranchType(AddrType1(1002,'Diamondstreet','nasik',
'maharashtra',1007),456767,7675757)));
1 row created.
SQL>
insert into Publishers values('Mcgrew', AddrType1(7002,'South street','pune','maharashtra',1007),

BranchTableType(BranchType(AddrType1(1002,'Southstreet','pune', 'maharashtra',1007),4543545,8676775)));
1 row created.
SQL>
insert into Publishers values('Tata', AddrType1(6002,'Gold street','nasik','maharashtra',1007),

BranchTableType(BranchType(AddrType1(6002,'South street','nasik','mha',1007),4543545,8676775)));
1 row created
inserting values in table books
SQL>
insert into books

select 'IP','28-may-1983', ref (pub), AuthorListType(ref(aut)) from
Publishers pub,Authors aut where pub.name='Tata' and aut.name='Richard';
1 row created.
SQL>
insert into books select 'ADBMS','09-jan-1890',ref(pub), AuthorListType(ref(aut)) from Publishers pub,Authors aut
where pub.name='Mcgrew' and aut.name='Sangoi';
1 row created
.SQL>
insert into books
select 'c prog','25-may-1983', ref (pub),AuthorListType(ref(aut)) from
Publishers pub,Authors aut where pub.name='Raj' and aut.name='Ramkrishnan.';
2 rows created.
List all the authors that have the same pin code as their publisher
SQL> select a.name from Authors a, Publishers pwhere a.addr.pincode = p.addr.pincode;
NAME
Richard
Rabiner
Schiller
Sangoi
List of all books that have two or more Authors
SQL>
select * from books b where 1 < (select count (*) from table (b.authors));
no rows selected
List the name of the publisher that has the most branches
SQL>
Select p.name from publishers p, table (p.branches)group by p.name having count(*)> = all (select count(*)from
publishers p, table(p.branches) group by name);
NAME
--------------------------------------------------
Tata
Name of authors who have not published more than a book
SQL>
select a.name from authors a, books b, table (b.authors) vwhere v.column_value = ref(a) group by a.name having count(*)
= 1;
no rows selected
List all the authors who have published more than one book & Name of authors whohave published books with atleast
two different publishers
SQL>
select a.name from authors a, books b, table (b.authors) vwhere v.column_value = ref(a) group by a.name having count(*)
> 1;
NAME
Richard
Sangoi
List all books (title) where the same author appears more than once on the list of authors (assuming that an integrity
constraint requiring that the name of an author isunique in a list of authors has not been specified)
SQL>
select title from authors a, books b, table (b.authors) vwhere v.column_value = ref(a) group by title having count(*) > 1;
TITLE
ADBMS
IP
Practical No.7
Aim: Implement and retrieve records from a Spatial Database.
A] Spatial Database
Create a spatial database that stores the number, name and location, which
consists of four different areas say abc, pqr, mno and xyz. Fire the following
queries.
1. Find the topological intersection of two geometries.
2. Find whether two geometric figures are equivalent to each other.
3. Find the areas of all direction locations.
4. Find the area of only one location.
5. Find the distance between two geometries.
B] Spatial Database
Create a spatial database that stores the number, name and location, which
consists of different areas within the university campus including the main
gate, the playground, the arts and science college buildings, the lad and print
facility building. Fire the following queries.
1. Display area for each object.

2. Find out the distance of the main gate from all other objects
3. Find the intersection area of lab and print facility
4. Distance between arts and science building
5. Find the spatial relationship between canteen and print facility
6. Find the distance between college buildings and canteen.
13
12
11 c
10
8 d
7 b
a
6
e
5
4
g
3
f
2
Symbol Area
A Main gate
B Playground
C Science building
D Lab
E Print facility
F Canteen
G Arts building
A] Spatial Database
Create Table cola_markets:
CREATE TABLE cola_markets (
mkt_id NUMBER PRIMARY KEY,
name VARCHAR2(32),
shape MDSYS.SDO_GEOMETRY);
Insert Values Into Table cola_markets:

INSERT INTO cola_markets VALUES(
1,'abc',
MDSYS.SDO_GEOMETRY(
2003,NULL,NULL,
MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,3),
MDSYS.SDO_ORDINATE_ARRAY(1,1, 5,7)
)
);

2,'pqr',
MDSYS.SDO_GEOMETRY(
2003,NULL,NULL,
MDSYS.SDO_ORDINATE_ARRAY(5,1, 8,1, 8,6, 5,7, 5,1)
)
);

3,'mno',
MDSYS.SDO_GEOMETRY(
2003, NULL,NULL,
MDSYS.SDO_ORDINATE_ARRAY(3,3, 6,3, 6,5, 4,5, 3,3)
)
);

4,'xyz',
MDSYS.SDO_GEOMETRY(
2003, NULL,NULL,
MDSYS.SDO_ORDINATE_ARRAY(8,7, 10,9, 8,11)
)
);
Inserting into USER_SDO_GEOM_METADATA:
INSERT INTO USER_SDO_GEOM_METADATA
VALUES ('cola_markets','shape',
MDSYS.SDO_DIM_ARRAY(
MDSYS.SDO_DIM_ELEMENT('X', 0, 20, 0.005),
MDSYS.SDO_DIM_ELEMENT('Y', 0, 20, 0.005)
),NULL
);
CREATE THE SPATIAL INDEX:

CREATE INDEX cola_spatial_idx
ON cola_markets(shape)
INDEXTYPE IS MDSYS.SPATIAL_INDEX;
Index Created
Queries:
1)Find the topological intersection of two geometries.
SQL> SELECT SDO_GEOM.SDO_INTERSECTION(c_a.shape, c_c.shape,

0.005)
FROM cola_markets c_a, cola_markets c_c
WHERE c_a.name = 'abc' AND c_c.name = 'mno';
Output:
SDO_GEOM.SDO_INTERSECTION(C_A.SHAPE,C_C.SHAPE,0.005)(SDO_GTYPE
, SDO_SRID, SDO_PO
---------------------------------------------------------
SDO_GEOMETRY(2003, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 1003,
1), SDO_ORDINATE_ARR
AY(4, 5, 3, 3, 5, 3, 5, 5, 4, 5))
2)Find whether two geometric figures are equivalent to each

other.
SQL>SELECT SDO_GEOM.RELATE(c_b.shape, 'equal', c_d.shape,
0.005)
FROM cola_markets c_b, cola_markets c_d

WHERE c_b.name = 'abc' AND c_d.name = 'mno';
Output:
SDO_GEOM.RELATE(C_B.SHAPE,'EQUAL',C_D.SHAPE,0.005)
--------------------------------------------------------------
------------------
FALSE
3) Find the areas of all direction locations.

SQL>SELECT name, SDO_GEOM.SDO_AREA(shape, 0.005) FROM
cola_markets;
Output:
NAME SDO_GEOM.SDO_AREA(SHAPE,0.005)
--------------- ---------------------------
abc 24
pqr 16.5
mno 5
xyz 12.5663706
4)Find the area of only one location.

SQL> SELECT c.name, SDO_GEOM.SDO_AREA(c.shape, 0.005) FROM
cola_markets c WHERE c.name = 'xyz';
Output:
NAME SDO_GEOM.SDO_AREA(C.SHAPE,0.005)
----------- --------------------------------
xyz 12.5663706
5) Find the distance between two geometries.

SQL> SELECT SDO_GEOM.SDO_DISTANCE(c_b.shape, c_d.shape, 0.005)
FROM cola_markets c_b, cola_markets c_d
WHERE c_b.name = 'abc' AND c_d.name = 'xyz';
Output:
SDO_GEOM.SDO_DISTANCE(C_B.SHAPE,C_D.SHAPE,0.005)
------------------------------------------------
1.60555128
B] Spatial Database
Creating Table university_camp :

CREATE TABLE university_camp (
mkt_id NUMBER PRIMARY KEY,
name VARCHAR2(32),
shape MDSYS.SDO_GEOMETRY);
/
Inserting Values Into university_camp :
INSERT INTO university_camp VALUES(
1,'a',
MDSYS.SDO_GEOMETRY(
2003,NULL,NULL,
MDSYS.SDO_ORDINATE_ARRAY(1,5, 2,8)
)
);

4,'d',
MDSYS.SDO_GEOMETRY(
2003,NULL,NULL,
MDSYS.SDO_ORDINATE_ARRAY(8,6,10,9)
)
);

5,'e',
MDSYS.SDO_GEOMETRY(
2003,NULL,NULL,
)
);

6,'f',
MDSYS.SDO_GEOMETRY(
2003,NULL,NULL,
)
);
3,'c',
MDSYS.SDO_GEOMETRY(
2003,NULL,NULL,
MDSYS.SDO_ORDINATE_ARRAY(8,10,11,10,11,13,6,13,8,11,8,10)
)
);

7,'g',
MDSYS.SDO_GEOMETRY(
2003, NULL,NULL,
MDSYS.SDO_ORDINATE_ARRAY(4,2,7,2,8,3,7,4,4,4,4,2)
)
);

2,'b',
MDSYS.SDO_GEOMETRY(
2003, NULL,NULL,
MDSYS.SDO_ORDINATE_ARRAY(3,7, 5,5,5,9)
)
);
INSERT INTO USER_SDO_GEOM_METADATA

VALUES ('university_camp','shape',
MDSYS.SDO_DIM_ARRAY(
MDSYS.SDO_DIM_ELEMENT('X', 0, 20, 0.005),
MDSYS.SDO_DIM_ELEMENT('Y', 0, 20, 0.005)
),NULL
);
CREATE THE SPATIAL INDEX:

CREATE INDEX cola_spatial_idx
ON university_camp(shape)
INDEXTYPE IS MDSYS.SPATIAL_INDEX;
Queries:
1)Display area for each object.
SELECT name, SDO_GEOM.SDO_AREA(shape, 0.005) FROM
university_camp;
Output:
NAME SDO_GEOM.SDO_AREA(SHAPE,0.005)
---------- ------------------------------
a 3
d 6
e 6
f 4
c 11
g 7
b 12.5663706
7 rows selected.
2)Find out the distance of the main gate from all other objects.
SELECT SDO_GEOM.SDO_DISTANCE(c_b.shape, c_d.shape, 0.005)
FROM university_camp c_b, university_camp c_d
WHERE c_b.name = 'a' AND c_d.name = 'b';
Output:
------------------------------------------------
1
WHERE c_b.name = 'a' AND c_d.name = 'c';
Output:
------------------------------------------------
6.32455532

WHERE c_b.name = 'a' AND c_d.name = 'd';
Output:
------------------------------------------------
6

WHERE c_b.name = 'a' AND c_d.name = 'e';
Output:
------------------------------------------------
6

WHERE c_b.name = 'a' AND c_d.name = 'f';
Output:
------------------------------------------------
7.28010989

WHERE c_b.name = 'a' AND c_d.name = 'g';
Output:
------------------------------------------------
2.23606798
3.Find the intersection area of lab and print facility.

SELECT SDO_GEOM.SDO_INTERSECTION(c_a.shape, c_c.shape, 0.005)
FROM university_camp c_a, university_camp c_c
WHERE c_a.name = 'd' AND c_c.name = 'e';
Output:
SDO_GEOM.SDO_INTERSECTION(C_A.SHAPE,C_C.SHAPE,0.005)(SDO_GTYPE
, SDO_SRID, SDO_PO
--------------------------------------------------------------
------------------
SDO_GEOMETRY(2003, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 1003,
1), SDO_ORDINATE_ARR
AY(8, 7, 8, 6, 10, 6, 10, 7, 8, 7))
4.Distance between arts and science building.

WHERE c_b.name = 'c' AND c_d.name = 'g';
Output:
---------------------------------------------------------
7.234
5.Find the spatial relationship between canteen and print facility.
SELECT SDO_GEOM.RELATE(c_b.shape, 'anyinteract', c_d.shape,

0.005) FROM university_camp c_b, university_camp c_d
WHERE c_b.name = 'e' AND c_d.name = 'f';
Output:
SDO_GEOM.RELATE(C_B.SHAPE,'ANYINTERACT',C_D.SHAPE,0.005)
--------------------------------------------------------------
------
False
6.Find the distance between college buildings and canteen.

WHERE c_b.name = 'c' AND c_d.name = 'f';
Output:
---------------------------------------------------------
7.234

WHERE c_b.name = 'g' AND c_d.name = 'f';
---------------------------------------------------------
1.762
Practical No.8
Aim:XML Application
A]Create an XML Application using XML as database and ASP.
B] Create a table employee having dept_id as number datatype and

employee_spec as XML datatype(XM_Type). The employee_spec is a schema
with attributes emp_id, name, email, acc_no, managerEmail, dataOf Joning.
Insert 10 tuples into employee table. Fire the following queries on XML
database.
a) Retrieve the names of employee.

b) Retrieve the acc_no of employees.
c) Retrieve the names, acc_no, email of employees.
d) Update the 3rd record from the table and display the name of an
employee.
e) Delete 4th record from the table.
A] Create an XML Application using XML as database and ASP.
VerifyPerson.asp:
<%@ Language=JScript%>
<%Server.ScriptTimeout=21478836%>
<%Response.Buffer=false%>
<%
// 1) To check whether the user has already pressed the
submit button
var submit = Request.Form("submit").Count;
if( submit > 0 ){
// 2) Create the needed to variables to store data

from the form
var name = Request.Form("Name");
var age = Request.Form("Age");
var gender = Request.Form("Gender");
var pcode = Request.Form("PostalCode");
var city = Request.Form("City");
// 3) First check whether the user has entered

anything or not !
var error = "";
if ( name == "" )
error = "Name ";
if ( age == "" )
error += "Age ";
if ( pcode == "")
error += "PostalCode ";
if ( city == "")
error += "City ";
// 4) We have found that the user didnt entered

anything
if(error!=""){
Response.Write("<center>");
Response.Write("");
Response.Write("Please enter the following
data: ");
Response.Write("");
Response.Write(error);
Response.Write("");
Response.Write("");
Response.Write("</center>");
}
else{
// Response.Write(name+" has been added to your
Friend list.");
// 5) Load the xmlDoc and create the required
elements/nodes
var
xmlDoc=Server.CreateObject("MICROSOFT.FreeThreadedXMLDOM");
xmlDoc.async="false";
xmlDoc.load(Server.MapPath("Person.xml"));
var nodeList =
xmlDoc.getElementsByTagName("PersonList");
if(nodeList.length > 0){
var parentNode = nodeList(0) ;
var personNode =
xmlDoc.createElement("Person");
var nameNode =
xmlDoc.createElement("Name");
var ageNode =
xmlDoc.createElement("Age");
var genderNode =
xmlDoc.createElement("Gender");
var pcodeNode =
xmlDoc.createElement("PostalCode");
var cityNode =
xmlDoc.createElement("City");
nameNode.text = name;
ageNode.text = age;
genderNode.text= gender;
pcodeNode.text = pcode;
cityNode.text = city;
// 6) Attach the nodes to the parent node

(Person)
parentNode.appendChild(personNode);
personNode.appendChild(nameNode);
personNode.appendChild(ageNode);
personNode.appendChild(genderNode);
personNode.appendChild(pcodeNode);
personNode.appendChild(cityNode);
// 7) Now save the nodes to the file

xmlDoc.save(Server.MapPath("Person.xml"));
}
}
}
%>
<html><body>
<CENTER>Add My Friends to
XML </CENTER>
<form action="VerifyPerson.asp" method="post" name="frmPerson"
id="frmPerson">
<table border=0 align=center style="height: 184px"
width="255">
<TBODY>
<TR><TD width="78">
Name:</TD><TD
width="154"><INPUT name=Name ></TD>
<TR><TD width="78">Age: 
</TD><TD width="154"><INPUT name=Age ></TD>
<TR><TD width="78">Gender:</TD><TD width="154">
<SELECT style="WIDTH: 154px" name=Gender>
<OPTION value=Male selected>Male</OPTION>
<OPTION value=Female>Female</OPTION>
</SELECT>
</TD>
<TR><TD width="78">Postalcode:
 </TD><TD width="154"><INPUT name=Postalcode ></TD>
<td width="3">
</TR>
<TR><TD width="78">City: 
</TD><TD width="154"><INPUT name=City ID="Text1"></TD>
<td width="3">
</TR>
<td width="78">

<TR>
<TD COLSPAN=2 width="238"><center><INPUT type=submit
value=Submit name=submit>
<INPUT type=reset value=clear form name=reset></center>
</form>
</TABLE> 
<CENTER>My Friend list
 </CENTER> 
<table border="1" style="border-collapse: collapse; height:
20px" bordercolor=#000000 align=center bgColor=silver
width="417">
<TR><TD width="93">

Name</TD>
<TD width="69">Age</TD>
style="BACKGROUND-COLOR: silver">Gender</TD>
style="BACKGROUND-COLOR: silver">Postal
Code</TD>
style="BACKGROUND-COLOR: silver">City</TD>
</TR>
<%
// This part is used to display the data in a table via
XSL
var objXMLDoc =
Server.CreateObject("MICROSOFT.FreeThreadedXMLDOM");
objXMLDoc.async = false;
objXMLDoc.load(Server.MapPath("person.xml"));
var
xsl=Server.CreateObject("MICROSOFT.FreeThreadedXMLDOM");
xsl.async = false;
xsl.load(Server.MapPath("person.xsl"));
var xmlQuery="//Person";
var
docHeadlines=objXMLDoc.documentElement.selectNodes(xmlQuery);
var numNodes;
numNodes=docHeadlines.length;
var nn;
for(var i=0;i<numNodes;i++){
nn = docHeadlines.nextNode();
Response.Write(nn.transformNode(xsl));
}
%>
</table>
</body>
</html>
Person.xml :
<?xml version="1.0" encoding="ISO-8859-1"?>
<PersonList>
<Person><Name>Sonu</Name><Age>25</Age><Gender>Male</Gende
r><PostalCode>99999</PostalCode><City>Thane</City></Person>
<Person><Name>vijay</Name><Age>23</Age><Gender>Male</Gend
er><PostalCode>121</PostalCode><City>mumbai</City></Person>
Person.xsl :
<xsl:stylesheet
xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
version="1.0">
<xsl:template match="Person">
<tr>
<td><xsl:value-of

select="Name"/></td>

select="Age"/></td>

select="Gender"/></td>

select="PostalCode"/></td>

select="City"/></td>
</tr>
</xsl:template>
</xsl:stylesheet>
Output:
B] XML Database
Creating Table emp_xml15

SQL> create table emp_xml15(
dept_id number(4),
employee_spec XMLtype);
Table created.
Inserting Values Into Table emp_xml15:
SQL> insert into emp_xml15 values(1,XMLtype(

'<emp id="1">
<name> sharmila </name>
<email>dave@yahoo.com</email>
<acc_no>23456</acc_no>
<mgr_email>rekha.shah@hotmail.com</mgr_email>
<doj>12/12/2003</doj>
</emp>'));
1 row created.

'<emp id="2">
<name> anita </name>
<email>ani@yahoo.com</email>
<mgr_email>rekha.shah@hotmail.com</mgr_email>
<doj>2/6/2003</doj>
</emp>'));
1 row created.

'<emp id="3">
<name> ekta </name>
<email>ektabhatt@yahoo.com</email>
<mgr_email>ekta.bhatt@hotmail.com</mgr_email>
<doj>24/5/2001</doj>
</emp>'));
1 row created.
'<emp id="4">
<name> nancy </name>
<email>nancyshah@yahoo.com</email>
<mgr_email>ekta.shah@hotmail.com</mgr_email>
<doj>21/5/2002</doj>
</emp>'));
1 row created.

'<emp id="5">
<name> falguni </name>
<email>falgunishah@yahoo.com</email>
<mgr_email>falguni.shah@hotmail.com</mgr_email>
<doj>1/8/2002</doj>
</emp>'));
1 row created.

'<emp id="6">
<name> sweta </name>
<email>swetamehta@yahoo.com</email>
<mgr_email>sweta.mehta@hotmail.com</mgr_email>
<doj>2/1/2001</doj>
</emp>'));
1 row created.

'<emp id="7">
<name> aarti </name>
<email>aartigupta@yahoo.com</email>
<mgr_email>falguni.shah@hotmail.com</mgr_email>
<doj>4/9/2002</doj>
</emp>'));
1 row created.
'<emp id="8">
<name> sandy </name>

<email>sagupta@yahoo.com</email>
<mgr_email>sweta.shah@hotmail.com</mgr_email>
<doj>4/4/2004</doj>
</emp>'))
1 row created.
Queries:
a) Retrieve the names of employee:

SQL> select e.employee_spec.extract('//name/text()').getStringVal() "EMP_NAME" from
emp_xml15 e;
Output:
EMP_NAME
--------------------------------------------------------------------------------
sharmila
anita
ekta
nancy
falguni
sweta
aarti
sandy
b) Retrieve the acc_no of employees:
SQL>select e.employee_spec.extract('//acc_no/text()').getStringVal()
"Acc_No" from emp_xml15 e;
Output:
Acc_No
------------------------------------------------------------------------------
23456
234346
2343456
2343678
2343345
c) Retrieve the names, acc_no, email of employees:

SQL> select e.employee_spec.extract('//name/text()').getStringVal()
"NAME",e.employee_spec.extract('//acc_no/text()').getStringVal()
"ACC_NO",e.employee_spec.extract('//email/text()').getStringVal()
"EMAIL" from emp_xml15 e;
Output:
NAME ACC_NO EMAIL

--------------------------------------------------------------------------------
sharmila 23456 dave@yahoo.com
anita 234346 ani@yahoo.com
ekta 2343456 ektabhatt@yahoo.com
nancy 2343678 nancyshah@yahoo.com
falguni 2343345 falgunishah@yahoo.com
sweta 2343890 swetamehta@yahoo.com
aarti 23433898 aartigupta@yahoo.com
sandy 23567898 sagupta@yahoo.com
d) Update the 3rd record from the table and display the name of
an employee:
SQL> update emp_xml15 e set employee_spec=XMLtype('<emp id="3">
<name> ekta </name>
<doj>24/5/2001</doj>
<update>This is the updated record</update>
</emp>')
where
e.employee_spec.extract('//name/text()').getStringVal()
='ekta';
SQL> select e.employee_spec.extract('//name/text()').getStringVal()"NAME",

e.employee_spec.getClobVal() "EMP_SPECIFICATION"
from emp_xml15 e where
e.employee_spec.extract('//name/text()').getStringVal()='ekta '
Output:
NAME
-------------------------------------------------------------------------------
EMP_SPECIFICATION
-------------------------------------------------------------------------------
ekta
<emp id="3">
<name> ekta </name>
<doj>24/5/2001</doj>
<update>This is the updated record</update>
</emp>
e) Delete the 4th record from the table:
SQL> delete from emp_xml15 e

where e.employee_spec.extract('//name/text()').getStringVal()
='nancy '
SQL> select e.employee_spec.extract('//name/text()').getStringVal() "NAME" from

emp_xml151 e;
Output:
NAME
--------------------------------------------------------------------------------
sharmila
anita
ekta
falguni
sweta
aarti

M.Sc. - I.T.: Adbms

Uploaded by

Document Informationclick to expand document information

Copyright:

Available Formats

M.Sc. - I.T.: Adbms

Uploaded by

Document Information

Original Description:

Original Title

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

M.Sc. - I.T.: Adbms

Uploaded by

Copyright:

Available Formats

M.Sc. – I.T.

1 Horizontal fragmentation of database.

2 Vertical fragmentation of database

Aim:Horizontal fragmentation of database.

Question: Create a global conceptual schema Emp (Eno, Ename, Address,

a) Find the salary of all employees.

Creating Link In ORCL1:

Creating Table emp:

Inserting Values Into Table emp:

Creating Link in ORCL2:

Creating Table emp:

Inserting Values into Table emp:

Creating view hrz_view:

1) Find the salary of all employees.

2) Find the Email of all employees where salary=4000.

Question: Create a global conceptual schema Emp ( Employee_id,

Open ORCL Database:

Creating link in ORCL:

Creating Table emp:

Inserting Values Into Table emp:

Creating Link in ORCL1:

Creating Table emp:

Inserting Values Into Table emp:

Creating Link In ORCL3:

Creating Table emp:

Inserting Values Into Table emp:

Creating view vrt_view:

1) Find the salary of an Employee where employee number is

EMPLOYEE_ID FIRST_NAME SALARY

2) Find the Email where the employee name is known.

3) Find the employee name and Email where employee number is

SQL> select e1.employee_id,e1.first_name,e1.email from emp e1

a) Find the salary of all employees.

Creating Link in ORCL1:

Creating Table emp:

Open ORCL2 Database:

Creating Link in ORCL2:

Creating Table emp:

A) Inserting values into employee table:

SQL> select * from emp

Connect to orcl1 database:

SQL> select * from emp;

Connect to orcl2 database:

SQL> select * from emp;

B) Updating the values into employee table:

SQL> select * from emp;

Connect to orcl1 database:

SQL> select * from emp;

Connect to orcl2 database:

SQL> select * from emp;

C) Deleting the values into employee table:

SQL> select * from emp ;

Connect to orcl1 database:

Fire the query:

Connect to orcl2 database:

Fire the query:

A] Create a table tbl_shares, which stores the, name of company,