In my practical proposed framework, a Proxy Database Management System (PDBMS) will control multiple gateways and will access to local distributed databases through these gateways. The proposed PDBMS manages and retrieves data from multiple distributed databases throgh a web-based application and that resides on top of the Proxy Database system while providing complete autonomy to individual local distributed database system.
The proposed framework is divided into four layers (as shown in Figure 6 below) based on a classical example (as shown in Figure 5 above) of a data-based architecture is the ANSI/SPARC model by Tsichritzis and Klug [7,8,9]. Please also see the details of work pertaining to the Multidatabase System as 4-tiered client-server model in the distributed databases [6].
In my proposed framework, when proxy tables are created within the Proxy Database system, metadata from local distributed databases are stored within the Proxy Database system tables as we named as Directory / Dictionary / Catalog. This metadata can be queried locally to quickly obtain information about proxy tables and the corresponding distributed tables and viwes. This information will include column attributes, index definitions and what data objects exist at the distribted database servers in the distributed database system.
The maintenance of this metadata now becomes a critical issue, as the local distributed schemas evolve over times and are autonomus, there should be a mechanism to import and synchronize metadata for a proxy table and the proxy database. Please see the details of work of [2,4,5] pertaining to the multidatabase system. I have worked-out this critical issue and obtained the copyright. Please see the details of work [12]. Similarly, I can implement this for a proxy database here.
A Proxy table can also point to a view on the local database, and it needs not come from a single table.
Through this framework, any user sitting on a Proxy Database system can access single or many local distibuted database tables located at different distributed database servers using a single query. A single query can retrieve information from many local distributed database servers table using more than one proxy tables. A single query can also update local distribted database server’s table through a proxy table and I am not considering this issue in this paper and the reason as stated earlier. Any user can retrieve information from two or more local distributed database servers’ tables applying join, union, intersect and in various ways of different SQL statements query on proxy tables. Any user can also transfer data from one local database servers’ table to another local database servers’ table through a proxy table.
Following technical steps are involved in a creation of a Proxy Database using proxy tables from distributed local databases in a Proxy Database system:
A. Local Database Server Interface
It is required to first define local distributed database server interface definition at the Proxy Database Management System (PDBMS) through which a Proxy Database server will be able to interact with all local distributed database servers over the network within the premises. Following parameters needed for the distributed database server interface definition which is as bellows:
Database Server Interface Driver Definition
Syntax
Local Server Name
Local Server IP Address
Local Server Port
Communication Protocol
Example
Local Server Name: Domain1
Local Server IP Address: 144.16.192.211
Local Server Port: 5000
Communication Protocol: TCP
B. Distributed Local Database Gateway
Database Gateways address the needs of data access in a distributed environment. Gateways make it possible to integrate any number of database servers. Proxy table only can be created on the basis of using SQL gateway otherwise system will not understand where to go during creation of Proxy table. In the following way, I define the Gateway definition for the distributed local Database:
Syntax
Local Server Name.Database Name.Database Owner Name.Object Name
Where object’s name is a name of table or view created at the distributed local database server by dbo.
Example
Domain1.HumanResourceB.dbo.Employees
C. Method for Mapping Distributed Local Objects to a Proxy Table and Creating a Proxy Database
Since local tables and views are located at the distributed local database servers, I need to provide access methods over a network, resolve pathname i.e. distributed local gateway, and syntax for this, which will be transparent to the Proxy Database system users. I have already discussed in A and B above, how to define distributed local server’s interface and distributed local database gateway to resolve pathname of the distributed local database server.
As I have explained earlier that a Proxy Database is a set of proxy tables. I am showing here how to create a proxy table in a Proxy Database System and how to map proxy table to the distributed local objects i.e. table(s) of view.
create proxy_table <table_name> [external table] at "LocalDatabaseGateway";
This will create proxy table in the Proxy Database system and this will derive tables’ structure based on metadata it obtains from distributed local database server. I use create proxy_table only if the table does exist at the distributed local database server, external table parameter defines that object is distributed local table(s) or view.
Example 1
Create proxy_table EmployeesDomain1 external table at “Domain1.HumanResource.dbo.Employees”;
I am creating a proxy table EmployeeDomain1 from local distributed table(s) or view which is/are already located at the distributed local Database Server for domain 1 in the Human Resource Database with the name Employees.
Example 2 Alternate ways
create existing table EmployeesDomain1 ( EmployeeCode Int not null, Name varchar(35) not null, phone char(15) not null, address varchar(60) null, city varchar(25) null, state char(2) null, country char (3) null, zip char (7) ) at "Domain1.HumanResource.dbo.Employees", "table";
I use create existing table only if the table does exist at the distributed local database server. I use the same data definition language (DDL) of the distributed local object.
Example 3 Alternate ways
create table EmployeesDomain1 ( EmployeeCode Int not null, Name varchar(35) not null, phone char(15) not null, address varchar(60) null, city varchar(25) null, state char(2) null, country char (3) null, zip char (7) ) at "Domain1.HumanResource.dbo.Employees", "table";
create table command creates the table in the distributed local database server and then creates the proxy table in the Proxy Database system.
Similarly, I can create many proxy tables based on different located distributed local database servers and in resulting of creating a Proxy Database in the Proxy Database Management System.