ADVANCED DATABASE TECHNOLOGY LAB

MANUAL

Submitted By
Name : Pranjal Arun Sahare
Roll no : 39

Practical Performed in Guidance of

Prof. Milind Tote

Department of IT-CSE

JD COLLEGE OF ENGINEERING AND

MANAGEMENT
2020 - 2021

Practical No –1
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.

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

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 pric
(1 row(s) affected)
4. Find total number of book at site S2. select *from openquery(cs03c025,'select
sum(totalstock) from bookstore.dbo.Books')

Practical no 2
Aim : University wants to track persons associated with them. A person can be an Employee or
Student. Employees are Faculty, Technicians and Project associates. Students are Full time
students, Part time students and Teaching Assistants.

a. Design an Enhanced Entity Relationship (EER) Model for university database .

For our sample database application, consider a UNIVERSITY database that keeps track of students and
their majors, transcripts, and registration as well as of the university’s course offerings. The database
also keeps track of the sponsored research projects of faculty and graduate students. This schema is
shown in Figure 8.9. A discussion of the requirements that led to this schema follows.

For each person, the database maintains information on the person’s Name [Name], Social Security
number [Ssn], address [Address], sex [Sex], and birth date [Bdate].

Two subclasses of the PERSON entity type are identified: FACULTY and STUDENT.
Specific attributes of FACULTY are rank [Rank] (assistant, associate, adjunct, research, visiting, and so
on), office [Foffice], office phone [Fphone], and salary[Salary].

All faculty members are related to the academic department(s) with which they are affiliated
[BELONGS] (a faculty member can be associated with several departments, so the relationship is M:N).

A specific attribute of STUDENT is [Class] (freshman=1, sophomore=2, ..., graduate student=5).

Each STUDENT is also related to his or her major and minor departments (if known) [MAJOR] and
[MINOR], to the course sections he or she is currently attending [REGISTERED], and to the courses
completed [TRANSCRIPT]. Each TRANSCRIPT instance includes the grade the student received
[Grade] in a section of a course.

GRAD_STUDENT is a subclass of STUDENT, with the defining predicate Class = 5.

For each graduate student, we keep a list of previous degrees in a composite, multivalued attribute
[Degrees].We also relate the graduate student to a faculty advisor [ADVISOR] and to a thesis committee
[COMMITTEE], if one exists.
An academic department has the attributes name [Dname], telephone [Dphone], and office number
[Office] and is related to the faculty member who is its chairperson [CHAIRS] and to the college to
which it belongs [CD]. Each college has attributes college name [Cname], office number [Coffice], and
the name of its dean [Dean].

A course has attributes course number [C#], course name [Cname], and course description [Cdesc].
Several sections of each course are offered, with each section having the attributes section number
[Sec#] and the year and quarter in which the section was offered ([Year] and [Qtr]).10 Section numbers
uniquely identify each section.

The sections being offered during the current quarter are in a subclass

CURRENT_SECTION of SECTION, with the defining predicate Qtr = Current_qtr and Year =
Current_year. Each section is related to the instructor who taught or is teaching it ([TEACH]), if that
instructor is in the database.

The category INSTRUCTOR_RESEARCHER is a subset of the union of FACULTY and

GRAD_STUDENT and includes all faculty, as well as graduate students who are supported by teaching
or research. Finally, the entity type GRANT keeps track of research grants and contracts awarded to the
university.

Each grant has attributes grant title [Title], grant number [No], the awarding agency [Agency], and the
starting date [St_date]. A grant is related to one principal investigator [PI] and to all researchers it
supports [SUPPORT].

Each instance of support has as attributes the starting date of support [Start], the ending date of the
support (if known) [End], and the percentage of time being spent on the project [Time] by the researcher
being supported.
Practical No 3
Aim:
To dsign an Enhanced Entity Relationship (EER) Model for university database Write
OQL for the following
i. Insert details in each object. ii.
Display the Employee details. iii.
Display Student Details. iv. Modify
person details. v. Delete person details

TO CREATE CLASSES:
Use queries inside the box SQL statement and excute it.
Create Classes called Student then Employee and then Univ
Q1: CREATE CLASS Student (name string, regno integer,department string, emp REFERENCES SET
(Employee) INVERSE Employee.starred_in, ownedby REFERENCES (Univ) INVERSE Univ.owns);
Q2: CREATE CLASS Employee (name string, street string, city string, starred_in REFERENCES SET
(Student) INVERSE Student.emp);

Q3: CREATE CLASS Univ (name string,address string, owns REFERENCES SE (Student)INVERSE
Student.ownedby);

TO INSERT THE VALUES:

insert into Univ(name,address) values('Anna','Gundiy') returning ref(Student) into Univ1 ; insert into
Student(name,regno,department,ownedby) values('ani',201,'cse',Univ1) returning ref(Employee) into
Student1;

insert into Employee(name,street,city,starred_in) values('aadh','sainagar','chennai',Student1); insert into

Univ(name,address) values('Madras','chennai') returning ref(Student) into Univ1; insert into
Student(name,regno,department,ownedby) values('priya',202,'ece',Univ1) returning ref(Employee) into
Student1;

insert into Employee(name,street,city,starred_in) values('Paul ','vellore','gandhi nagar',Student1); insert into

Univ(name,address) values('Thiruvallur','chennai') returning ref(Student) into Univ1; insert into
Student(name,regno,department,ownedby) values('bhaviya',203,'eee',Univ1) returning ref(Employee) into
Student1;

insert into Employee(name,street,city,starred_in) values('anand ','trichy','anna salai',Student1); insert into

Univ(name,address) values('periyar','chennai') returning ref(Student) into Univ1; insert into
Student(name,regno,department,ownedby) values('mano',204,'IT',Univ1) returning ref(Employee) into
Student1;

insert into Employee(name,street,city,starred_in) values('krish','madurai','kambam',Student1); insert into

Univ(name,address) values('sathyabama','chennai') returning ref(Student) into Univ1; insert into
Student(name,regno,department,ownedby) values('surya',205,'cse',Univ1) returning ref(Employee) into
Student1;

insert into Employee(name,street,city,starred_in) values('karthi','arani','chethpait',Student1); insert into

Univ(name,address) values('bharathi','kovai') returning ref(Student) into Univ1; insert into
Student(name,regno,department,ownedby) values('malathi',206,'IT',Univ1) returning ref(Employee) into
Student1;

insert into Employee(name,street,city,starred_in) values('ramani','thirunelveli','bharathinagar',Student1); insert

into Univ(name,address) values('bharathidasan','chennai') returning ref(Student) into Univ1;

insert into Student(name,regno,department,ownedby) values('divi',207,'ece',Univ1) returning ref(Employee) into

Student1;

insert into Employee(name,street,city,starred_in) values('kalai','villupuram','bharathinagar',Student1); insert

into Univ(name,address) values('anna','kovai') returning ref(Student) into Univ1; insert into
Student(name,regno,department,ownedby) values('sandhiya',208,'ece',Univ1) returning ref(Employee) into
Student1;
insert into Employee(name,street,city,starred_in) values('anadhi','villupuram','kamarajarsalai',Student1);

TO DISPLAYING EMPLOYEE AND STUDENT DETAILS:

Select * from Student;
Select * from Employee;

TO MODIFY THE DETAILS

update Student s set s.name='mano' where s.name='ani';

TO DELETE THE DETAILS

delete from student where name ='mano';

RESULT:
Thus an Enhanced Entity Relationship (EER) Model for university database by using Matisse
Enterprise manager has been created and successfully executed the following i. Insert details in each
object. ii. Display the Employee details. iii. Display Student Details. iv. Modify person details. v.
Delete person details

Practical No 4
AIM : Consider the application for University Counseling for Engineering Colleges. The college,
department and vacancy details are maintained in 3 sites. Students are allocated colleges in these 3
sites simultaneously. Implement this application using parallel database [State any assumptions
you have made].

Two forms are created for viewing available seats and allocating seats.
The first form will view only the available seats in each site. The second form is used to allocate seats
for students. If a seat is allocated to a student, all the 3 sites are updated in parallel.

Implementation:
Form 1: To view Number of seats available
Codes:
Public Class Form2
Dim cnn As New OleDb.OleDbConnection
Private Sub Button7_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles
Button7.Click
Me.Close()
End Sub
Private Sub RefreshData()
If Not cnn.State = ConnectionState.Open Then
'open connection
cnn.Open() End If
Dim da As New OleDb.OleDbDataAdapter("SELECT CollegeName,Department,Vacancy FROM
Site1", cnn)
Dim db As New OleDb.OleDbDataAdapter("SELECT CollegeName,Department,Vacancy FROM
Site2", cnn)
Dim dc As New OleDb.OleDbDataAdapter("SELECT CollegeName,Department,Vacancy
FROM Site3", cnn)
Dim dt As New DataTable
Dim du As New DataTable
Dim dv As New DataTable
'fill data to datatable
da.Fill(dt) db.Fill(du)
dc.Fill(dv)
'offer data in data table into datagridview Me.Site1DataGridView.DataSource = dt
Me.Site2DataGridView.DataSource = du
Me.Site3DataGridView.DataSource = dv
'close connection cnn.Close()
End Sub
Private Sub Site1BindingNavigatorSaveItem_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs)
Me.Validate()
Me.Site1BindingSource.EndEdit()
Me.TableAdapterManager.UpdateAll(Me.Database1DataSet)
End Sub
Private Sub Form2_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles
MyBase.Load
'TODO: This line of code loads data into the 'Database1DataSet1.Site3' table. You can move, or
remove it, as needed.
Me.Site3TableAdapter1.Fill(Me.Database1DataSet1.Site3)
'TODO: This line of code loads data into the 'Database1DataSet1.Site2' table. You can move, or
remove it, as needed.
Me.Site2TableAdapter1.Fill(Me.Database1DataSet1.Site2)
'TODO: This line of code loads data into the 'Database1DataSet1.Site1' table. You can move, or
remove it, as needed.
Me.Site1TableAdapter1.Fill(Me.Database1DataSet1.Site1)
'TODO: This line of code loads data into the 'Database1DataSet.Site3' table. You can move, or
remove it, as needed.
cnn = New OleDb.OleDbConnection cnn.ConnectionString =
"Provider=Microsoft.Jet.Oledb.4.0; Data Source=" &
Application.StartupPath & "\Database1.mdb"
'get data into list
Me.RefreshData()
End Sub
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)
Handles Button1.Click
Dim cmd As New OleDb.OleDbCommand
If Not cnn.State = ConnectionState.Open Then
'open connection if it is not yet open
cnn.Open() End If
cmd.Connection = cnn
cmd.CommandText = "UPDATE Site1 SET Vacancy = Vacancy - 1 Where (CollegeName='" &
Me.TextBox3.Text & "')"

cmd.ExecuteNonQuery() cmd.CommandText = "UPDATE Site2 SET Vacancy = Vacancy - 1 Where

(CollegeName='"
& Me.TextBox3.Text & "')" cmd.ExecuteNonQuery() cmd.CommandText = "UPDATE Site3 SET
Vacancy = Vacancy - 1 Where (CollegeName='"
& Me.TextBox3.Text & "')"
cmd.ExecuteNonQuery()
'refresh data in list
RefreshData()
'clear form 'close
connection cnn.Close()
End Sub
Private Sub Site3DataGridView_CellContentClick(ByVal sender As System.Object, ByVal e
As System.Windows.Forms.DataGridViewCellEventArgs) Handles
Site3DataGridView.CellContentClick
End Sub
End Class

Conclusion:
This software provides an efficient way of managing university counselling application for
engineering colleges.

Practical No 5
AIM:
To implement parallel join and parallel sort algorithms to get marks from different colleges of the
university and publish 10 ranks for each discipline. Objective:
There are 5 processors working in parallel environment and producing output. The output record
contains college details and student mark information.
Implement parallel join and parallel sort algorithms to get the marks from different colleges of the
university and publish 10 ranks for each discipline.

Assumption Made:
Five tables are created in which each table represents a college. Each table contains various departments
and its overall percentage.
Query is written to extract the details from all the 5 tables in parallel and colleges are arranged based on
their department overall percentage.

Implementation:

Creating of Tables
SQL> create table REC (id int, collg varchar(20), cse int, mech int, it int, civil int, ece int); Table
created.

SQL> create table PEC (id int, collg varchar(20), cse int, mech int, it int, civil int, ece int); Table
created.

SQL> create table APEC (id int, collg varchar(20), cse int, mech int, it int, civil int, ece int); Table
created.

SQL> create table SSN (id int, collg varchar(20), cse int, mech int, it int, civil int, ece int); Table
created.

SQL> create table SVCE (id int, collg varchar(20), cse int, mech int, it int, civil int, ece int); Table
created.

Insertion of Values in the tables

SQL> insert into REC values(1,'REC',88,74,90,98,77);

1 row created.
SQL> select * from rec;
ID COLLG CSE MECH IT CIVIL
---------- -------------------- ---------- ---------- ---------- ----------
ECE
----------
1 REC 88 74 90 98
77
SQL> insert into PEC values(1,'PEC',70,78,80,82,80); 1 row
created.
SQL> insert into APEC values(1,'APEC',80,71,92,90,70); 1 row
created.
SQL> insert into SSN values(1,'SSN',72,73,88,76,67); 1 row
created.
SQL> insert into SVCE values(1,'SVCE',68,64,60,91,90); 1 row
created.

SQL> select * from rec;

ID COLLG CSE MECH IT CIVIL
---------- -------------------- ---------- ---------- ---------- ----------
ECE
----------
1 REC 88 74 90 98
77

SQL> select * from pec

2;
ID COLLG CSE MECH IT CIVIL
---------- -------------------- ---------- ---------- ---------- ----------
ECE
----------
1 PEC 70 78 80 82
80

SQL> select * from ssn;

ID COLLG CSE MECH IT CIVIL
---------- -------------------- ---------- ---------- ---------- ----------
ECE
----------
1 SSN 72 73 88 76
67

SQL> select * from SVCE;

ID COLLG CSE MECH IT CIVIL
---------- -------------------- ---------- ---------- ---------- ----------
ECE
----------
1 SVCE 68 64 60 91
90

SQL> select * from apec;

ID COLLG CSE MECH IT CIVIL
---------- -------------------- ---------- ---------- ---------- ----------
ECE
----------
1 APEC 80 71 92 90
70

SQL> select CSE,MECH from REC;

CSE MECH
---------- ---------- 88 74

SQL> select CSE from REC union select CSE from apec;
CSE
---------- 80
88

SQL> select collg,CSE,mech,it,civil from rec union select collg,cse,mech,it,civil from apec order by cse desc;

COLLG CSE MECH IT CIVIL

-------------------- ---------- ---------- ---------- ----------
REC 88 74 90 98
APEC 80 71 92 90

SQL> select collg,CSE from rec union select collg,cse from apec union select collg,cse from pec unio n select
collg,cse from ssn union select collg,cse from ssn order by cse;
COLLG CSE
-------------------- ----------
PEC 70
SSN 72
APEC 80
REC 88

SQL> select collg,CSE from rec union select collg,cse from apec union select collg,cse from pec unio n select
collg,cse from ssn union select collg,cse from ssn union select collg,cse from svce order b y cse;
COLLG CSE
-------------------- ----------
SVCE 68
PEC 70
SSN 72
APEC 80
REC 88

SQL> select collg,mech from rec union select collg,mech from apec union select collg,mech from pec u nion
select collg,mech from ssn union select collg,mech from svce order by mech;
COLLG MECH
-------------------- ----------
SVCE 64
APEC 71
SSN 73
REC 74
PEC 78

SQL> select collg,mech from rec union select collg,mech from apec union select collg,mech from pec u
nion select collg,mech from ssn union select collg,mech from svce order by mech desc;
COLLG MECH
-------------------- ----------
PEC 78
REC 74
SSN 73
APEC 71
SVCE 64

SQL> select collg,it from rec union select collg,it from apec union select collg,it from pec union s elect collg,it
from ssn union select collg,it from svce order by it;
COLLG IT
-------------------- ----------
SVCE 60
PEC 80
SSN 88
REC 90
APEC 92

SQL> select collg,ece from rec union select collg,ece from apec union select collg,ece from pec unio
n select collg,ece from ssn union select collg,ece from svce order by ece;
COLLG ECE
-------------------- ----------
SSN 67
APEC 70
REC 77
PEC 80
SVCE 90
SQL> select collg,civil from rec union select collg,civil from apec union select collg,civil from pe c union
select collg,civil from ssn union select collg,civil from svce order by civil;
COLLG CIVIL
-------------------- ----------
SSN 76
PEC 82
APEC 90
SVCE 91
REC 98

SQL> (select * from apec);

ID COLLG CSE MECH IT CIVIL
---------- -------------------- ---------- ---------- ---------- ----------
ECE
----------
1 APEC 80 71 92 90
70
SQL> (select * from apec) union (select * from rec);
ID COLLG CSE MECH IT CIVIL
---------- -------------------- ---------- ---------- ---------- ----------
ECE
----------
1 APEC 80 71 92 90
70
1 REC 88 74 90 98
77

SQL> (select collg,cse from REC) union (select collg,cse from apec) order by cse;
COLLG CSE
-------------------- ----------
APEC 80
REC 88
SQL> (select collg,mech from rec) union (select collg,mech from apec)union (select collg,mech from p ec)
union (select collg,mech from ssn) union (select collg,mech from svce) order by mech desc;
COLLG MECH
-------------------- ----------
PEC 78
REC 74
SSN 73
APEC 71
SVCE 64

SQL> select collg,mech from rec union select collg,mech from apec union select collg,mech from pec u
nion select collg,mech from ssn union select collg,mech from svce order by mech desc;
COLLG MECH
-------------------- ----------
PEC 78
REC 74
SSN 73
APEC 71
SVCE 64
SQL> commit
2l
3

SQL> commit; Commit

complete.

Practical No 06
AIM:
To create triggers and assertions for Bank database handling deposits and loan and admission
database handling seat allocation and vacancy position.
PROCEDURE:
1. Start the program
2. Create the relational database schema and implement the following trigger and assertions.
3. When a deposit is made by customer, create a trigger for updating customers account and bank
account.
4. When a loan is issued to the customer, create a trigger for updating customer’s loan account and
bank account.
5. Create assertion for bank database so that the total loan amount does not exceed the total balance
in the bank.
6. When an admission is made, create a trigger for updating the seat allocation details and vacancy
position.
7. Stop the program.
STEP 1:
Create a trigger for updating customers account and bank account when a deposit is made by an
customer.
Syntax:
Query1: create table bank(bank_no number primary key,bank_name varchar2(50),
balance number); Table Created.
Query2: create table cust(acc_no number primary key,cust_name varchar2(50),acc_type
varchar2(15),balance number);
Table Created.
Query3: create table deposit(deposit_id number,bank_no number,acc_no number,amount
number,foreign key (acc_no) references cust(acc_no),foreign key (bank_no) references
bank(bank_no));
Table Created.
Query4: create sequence deposit_seq start with 1 increment by 1;
Sequence Created.
Query5: create table loan1(loan1_id number,bank_no number,acc_no number,amount
number,foreign key (acc_no) references cust(acc_no),foreign key (bank_no) references
bank(bank_no));
Table Created.
Query 6:create sequence loan1_seq start with 1 increment by 1; Sequence
Created.
Trigger 1:
create or replace trigger
deposit_trg after insert on
deposit for each row begin
update bank set balance=balance+:new.amount where bank_no=:new.bank_no; update
cust set balance=balance+:new.amount where acc_no=:new.acc_no; end;
Statement Processed.
Trigger 2:
create or replace trigger
loan1_trg after insert on
loan1 for each row begin
update bank set balance=balance-:new.amount where bank_no=:new.bank_no; update
cust set balance=balance+:new.amount where acc_no=:new.acc_no; end;
Statement Processed.
Query7: insert into bank values(002,'INDIAN_BANK',5000000); 1 row(s)
inserted.
Query8: insert into bank values(003,'CANARA_BANK',3000000); 1 row(s)
inserted.
Query9: insert into cust values(1234,'SANTHOSH','SAVINGS',75000); 1 row(s)
inserted.
Query10: insert into deposit values(deposit_seq.nextval,002,1234,2500); 1 row(s)
inserted.
Query11: select * from cust;
Query12: select * from bank;
Query13: insert into cust values(12345,'SANTHOSH','LOAN',0); 1 row(s)
inserted.
Query14: insert into loan1 values(loan1_seq.nextval,002,1235,200); 1 row(s)
inserted.

Create a trigger for updating customer’s loan account and bank account when a loan is issued to
the customer.
Syntax:
Query1: create table bank(bank_no number primary key, bank_namevarchar2(50), balance
number);
Table Created.
Query2: create table cust(acc_no number primary key,cust_name varchar2(50),acc_type
varchar2(15),balance number); Table Created.
Query3: create table loan3(loan_id number,bank_no number,acc_no number,amount
number,foreign key (acc_no) references cust(acc_no),foreign key (bank_no) references
bank(bank_no)); Table Created.
Query4: create sequence loan3_seq start with 1 increment by 1; Sequence
Created.
Trigger 1:
create or replace trigger
loan3_trg after insert on
loan3 for each row begin
update bank set balance=balance-:new.amount where bank_no=:new.bank_no; update
cust set balance=balance+:new.amount where acc_no=:new.acc_no; end;
Statement Processed.
Query5: insert into bank values(002,'INDIAN_BANK',5000); 1 row(s)
inserted.
Query6: insert into bank values(003,'CANARA_BANK',3000); 1 row(s)
inserted.
Query7:

Create assertion for bank database so that the total loan amount does not exceed the total balance in the
bank.
Syntax:
Query1: create table bank(bank_no number primary key, bank_namevarchar2(50),
balance number); Table Created.
Query2: create table cust(acc_no number primary key,cust_name varchar2(50),acc_type
varchar2(15),balance number);
Table Created.
Query3: create table loan3(loan_id number,bank_no number,acc_no number,amount
number,foreign key (acc_no) references cust(acc_no),foreign key (bank_no) references
bank(bank_no));
Table Created.
Query4: create sequence loan3_seq start with 1 increment by 1; Sequence
Created.
Trigger 1:
create or replace trigger
loan3_trg after insert on
loan3 for each row begin
update bank set balance=balance-:new.amount where bank_no=:new.bank_no; update
cust set balance=balance+:new.amount where acc_no=:new.acc_no; end;
Statement Processed.
Query 5: insert into bank values(002,'INDIAN_BANK',100000);
1 row(s) inserted.
Query 6: insert into cust values(12345,'SANTHOSH','LOAN',0);
1 row(s) inserted.
Query7:Select * from bank;

RESULT:
Thus the triggers and assertions has been created, executed and verified successfully.

Practical No 07
AIM:
To construct a knowledge database for kinship domain (family relations) with facts.
Extract the following relations using rules:
Parent, Sibling, Brother, Sister, Child, Daughter, Son, Spouse, Wife, husband,
Grandparent, Grandchild, Cousin, Aunt and Uncle.

PROCEDURE:
1. Start
2. Download the Prolog 7.1.11 software and install it.
3. Open Prolog 7.1.11 and click onto the File -> New and save the filename with filename.pl and click
save.
4. Type the program and save it as File -> Save buffer and close the current window.
5. Open the Main window and type consult(filename). to compile the program.
6. If it is true then the program has no errors.
7. Then check for each and every condition.
8. Terminate the program.

PROGRAM:
Step 1:
In the edit window type the following code. Describe each and every conditions and
relations here. male(raja). male(bharani). male(pandithurai). male(kannan).
male(semban). male(ragul).
female(ranimuthu). female(poorni).
female(anburani). female(jansi).
female(moni). female(vaishu).
female(inbam). female(nive).
parent(anburani,pandithurai).
parent(anburani,ranimuthu).
parent(anburani,jansi).
parent(semban,raja).
parent(seemban,kannan).
parent(semban,inbam).
parent(raja,bharani).
parent(raja,poorni).
parent(raja,rahul).
parent(ranimuthu,bharani).
parent(ranimuthu,poorni).
parent(ranimuthu,rahul).
parent(jansi,moni). parent(jansi,vaishu). parent(kannan,moni).
parent(kannan,vaishu). parent(pandithurai,nive).
married(raja,ranimuthu). married(kannan,jansi). sister(X,Y):-
female(X),parent(Par,X),parent(Par,Y), X\=Y. daughter(X,Y):-
female(X),parent(Y,X). son(X,Y):- male(X),parent(Y,X).
brother(X,Y):-male(X),parent(Somebody,X),parent(Somebody,Y), X\=Y.
father(X,Y):-male(X),parent(X,Y). mother(X,Y):-female(X),parent(X,Y).
grandparent(X,Y):-parent(Z,X),parent(Z,Y). sibling(X,Y):-parent(Z,X),parent(Z,Y).
aunt(X,Y):-female(X),sister(X,Mom),mother(Mom,Y). aunt(X,Y):-
female(X),sister(X,Dad),father(Dad,Y). uncle(X,Y):-brother(X,Par),parent(Par,Y).
cousin(X,Y):-uncle(Unc,X),father(Unc,Y).
spouse(X,Y):-married(X,Y). husband(X,Y):-male(X),married(X,Y).
wife(Y,X):-female(Y),married(X,Y).
child(Y,X):-parent(X,Y). grandchild(X,Z):-grandparent(Z,X).
Step 2:
Compile and run the coding for all the relations given above.

RESULT:
Thus the Deuctive Database for Kinship domain with facts are executed and verified successfully.

Practical No 08

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 tab new
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.