Asked By
Jwells
0 points
N/A
Posted on - 10/27/2011
There is some research I am carrying out and I am a bit lost. The research is concerning database systems and SQL in specific. I wanted to find out, how can SQL be used to create location transparency in a distributed database?
Answered By
Jackman
0 points
N/A
#96860
SQL transparency in a distributed database
The terms distributed database and distributed processing are very much in relation with each other. Yet they have distinct meaning. This kind of arrangement gives the distribution system a way of giving data access. In this kind of set up an application can simultaneously access or modify the data in a single distribution environment for example a single query from a manufacturer client can retrieve both from department table and product table from a remote hq database. In this case for a client the location and platform of the database are transparent.
Answered By
Paul Mac
0 points
N/A
#96861
SQL transparency in a distributed database
Local transparency exists when a user can universally refer to a database objects.
Oracle database distributes database system that allows the application developers to hide the physical location of the database objects from users.
Using local transparency access to remote data is simple and the administrators can move the database objects with no impact on end user.
Synonyms are used to establish transparency for table and supporting objects in the schema.
Example:
The following statements create synonyms in a database for tables in another, remote database.
CREATE PUBLIC SYNONYM emp
 FOR [email protected]_auto.com;
CREATE PUBLIC SYNONYM dept
 FOR [email protected]_auto.com;
Now, rather than access the remote tables with a query such as:
SELECT ename, dname
 FROM [email protected]_auto.com e,
      [email protected]_auto.com d
 WHERE e.deptno = d.deptno;
An application can issue a much simpler query that does not have to account for the location of the remote tables.
SELECT ename, dname
 FROM emp e, dept d
 WHERE e.deptno = d.deptno;
