16DistributedDatabase

Distributed Relational Database Systems

Outline

Distributed database ? What is it ?
Centralized database schema model
Mainframe application architecture
Client-server architecture
Distributed client-server architecture
Web based architecture
Distributed Web based architecture
Distributed database schema architecture
Transparency
Autonomy
Advantages and disadvantages of DDBS
Distributed database system in PostgreSQL

A Distributed DataBase System (DDBS) is a collection of multiple logically related databases distributed over a computer network.

DDBS is a collection of database systems together with software providing a required set of operations on data and management features.

We distinguish two type of distributed database systems: homogeneous DDBS and heterogeneous DDBS.

Homogeneous DDBS is a collection of identical database systems where database management software is coming from the same vendor and distributed over a computer network, e.g. a collection of Oracle database systems.

Heterogeneous DDBS is a collection of different database systems where a database management software is coming from different vendors and it is distributed over a computer network, e.g. a collection of Oracle + MySQL + DB/2 + MongoDB + XML native database systems + Excel spreadsheets + ... , some other systems.

Distributed database ? What is it ?

Distributed database system (DDBS) is a collection of multiple logically related databases distributed over a computer network
Distributed database management system (DDBMS) is a collection of database systems together with software providing a required set of operations on data and management features
Homogeneous DDBS is a collection of identical database systems distributed over a computer network, e.g. a collection of Oracle database systems
Heterogeneous DDBS is a collection of different database systems distributed over a computer network, e.g. a collection of Oracle + MySQL + DB/2 + MongoDB + XML native database systems + Excel spreadsheets + ... , systems

The current slide shows a sample organization of heterogeneous distributed database system. A heterogeneous database systems integrate a relational database system, NoSQL database system, XML native database systems and ordinary file system. NoSQL database system is a database system that provides a logical view of data different from relational view. XML native database system is a system that provides XML as logical view of data.

All components of distributed database system are geographically distributed over different locations all over the world. Each system is connected to its own local area network. It is possible that there are more heterogeneous database systems connected to a local area network. Local area networks are connected to wide area network that allows for communication of geographically distributed local database systems.

Heterogeneity of different database systems has a number of dimensions. Database systems participating in a distributed database system can provide different logical views of data stored in each system. It is so called data model heterogeneity.

Database systems participating in a distributed database system can be implemented with software provided by different vendors. It is so call interpretation heterogeneity. Data kept in database systems participating in a distributed database system may have different semantics, for examplea distance can be measured in kilometres in one database and and in miles in another database.

Distributed database ? What is it ?

A sample organization of heterogeneous distributed database system

Distributed Relational Database Systems

Outline

Distributed database ? What is it ?
Centralized database schema model
Mainframe application architecture
Client-server architecture
Distributed client-server architecture
Web based architecture
Distributed Web based architecture
Distributed database schema architecture
Transparency
Autonomy
Advantages and disadvantages of DDBS
Distributed database system in PostgreSQL

The database schemas of centralized database system show data at several level of abstraction. At the lowest level of abstraction and the closest to pure hardware level, a physical schema shows a database as a collection of files located at a number of persistent storage devices. The internal structures of such files determine persistent storage allocation strategy, a way how rows are stored in data blocks (row-by-row method versus column-by column method), persistent storage structures of indices or clusters, etc.

At logical level a database systems provide an abstract view of data in the terms of particular data structures like tables, data graphs, trees, etc.

A conceptual schema level provides the topmost view of data in terms of real world objects described by the attributes, associations between the classes of objects and generalizations over the classes of objects. A conceptual schema, usually a diagram, provides information about the contents of a database in the terms of a fragment of the reality described by a database.

Entire conceptual schema is usually to extensive for the individual users. For example a conceptual schema of an entire industrial organization is too large for users from finance department, hr department, manufacturing department and so on. It is why entire conceptual schema of a centralized database is partitioned into the external schemas and such external schemas are presented to the individual groups of users.

In a centralized database schema model the users are provided their personal external schemas (views) of data

The external schemas are integrated into a single conceptual schema later on transformed into a logical schema and implemented as a physical schema

Centralized database schema model

In a centralized database schema model the users are provided their personal external schemas (views) of data
The external schemas are integrated into a single conceptual schema later on transformed into a logical schema and implemented as a physical schema

ORM

Distributed Relational Database Systems

Outline

Distributed database ? What is it ?
Centralized database schema model
Mainframe application architecture
Client-server architecture
Distributed client-server architecture
Web based architecture
Distributed Web based architecture
Distributed database schema architecture
Transparency
Autonomy
Advantages and disadvantages of DDBS
Distributed database system in PostgreSQL

We start a review of different architectures of centralized and distributed database systems from mainframe application architecture typical for a period of time between 1960s to early 1980s.

In such approach database systems architecture was strongly determined by the available hardware. In a typical hardware configuration so called "dumb terminals" are directly connected to a mainframe computer. A single centralized database server is located at a mainframe computer. The users in front of "dumb terminals are directly connected to a database server.

All presentation logic of database applications (command line and sometimes graphical interface) and business logic (implementation of database application) are located on a mainframe computer. All data is located on the persistent storage devices directly connected to a mainframe computer.

Mainframe application architecture

In mainframe application architecture "dumb" terminals are connected to a single database server

Distributed Relational Database Systems

Outline

Distributed database ? What is it ?
Centralized database schema model
Mainframe application architecture
Client-server architecture
Distributed client-server architecture
Web based architecture
Distributed Web based architecture
Distributed database schema architecture
Transparency
Autonomy
Advantages and disadvantages of DDBS
Distributed database system in PostgreSQL

In a client-server architecture "dumb terminals" are replaced with the limited computational power personal computers (starting in mid 1980s). Then entire presentation logic (user interface) moved to a personal computer playing mainly a role of an intelligent terminal. Some simple components of business logic have been also moved to personal computer.

Entire functionality of a database system is implemented as a database server. A database server is a dedicated piece of software responsible for storing and maintenance of data. The remaining components of the business logic are stored in a data dictionary of a database server. Distribution of business logic between clients running on personal computers and database server running on a powerful hardware system allowed for a better balance of overall workload and reduction of the amounts of data transferred from a database server to a client (due to some business logic earlier stored and now running on a database server).

Client-server architecture

In client-server architecture personal systems communicate with a single database server

Distributed Relational Database Systems

Outline

Distributed database ? What is it ?
Centralized database schema model
Mainframe application architecture
Client-server architecture
Distributed client-server architecture
Web based architecture
Distributed Web based architecture
Distributed database schema architecture
Transparency
Autonomy
Advantages and disadvantages of DDBS
Distributed database system in PostgreSQL

At this point in time, together with the progresses of networking technologies, it was quite easy to upgrade a typical client-server architecture to distributed client-server architecture.

In distributed client server architecture we add more database servers to the system and we allow the clients to connect to any number of database servers at a time. Like before, presentation logic is entirely located at the clients and business logic is distributed between the clients and database servers.

An important improvement when compared with pure client-server architecture is the ability of database servers to communicated with each other and to transfer data from one database to another. For example if two or more database servers support a relational database model then it is possible to join two or more relational tables located at different database servers.

Distributed client-server architecture

In distributed client-server architecture personal systems communicate with many database servers

Distributed Relational Database Systems

Outline

Distributed database ? What is it ?
Centralized database schema model
Mainframe application architecture
Client-server architecture
Distributed client-server architecture
Web based architecture
Distributed Web based architecture
Distributed database schema architecture
Transparency
Autonomy
Advantages and disadvantages of DDBS
Distributed database system in PostgreSQL

An important progress step in the development of distributed database systems is Web based architecture.

Like in the previous two cases of client-server and distributed client-server architectures all presentation logic is removed from database servers.

In Web based database architecture presentation logic is distributed between the client systems and Web servers also called as application servers.

Business logic is distributed between application servers and database servers. Application servers are build on top of ordinary Web servers. The clients access application servers and pass their request to access/process data to application servers. Then application servers "cooperate" with database servers on passing the clients' requests and on transferring data back to the clients.

Like in the client-server architecture database servers have the abilities to communicate and to pass data between each other.

A layer of application servers between clients and database servers reduces processing load on database servers and provides more persistent and transient storage to be used for data caching.

Web based architecture

In Web based architecture personal systems communicate with the Web servers that communicate with a single database server

Distributed Relational Database Systems

Outline

Distributed database ? What is it ?
Centralized database schema model
Mainframe application architecture
Client-server architecture
Distributed client-server architecture
Web based architecture
Distributed Web based architecture
Distributed database schema architecture
Transparency
Autonomy
Advantages and disadvantages of DDBS
Distributed database system in PostgreSQL

Distributed Web based architecture

In Distributed Web based architecture personal systems communicate with the Web servers that communicate with many database servers

Distributed Relational Database Systems

Outline

Distributed database ? What is it ?
Centralized database schema model
Mainframe application architecture
Client-server architecture
Distributed client-server architecture
Web based architecture
Distributed Web based architecture
Distributed database schema architecture
Transparency
Autonomy
Advantages and disadvantages of DDBS
Distributed database system in PostgreSQL

Distributed database schema architecture

In distributed database schema architecture a global conceptual schema hides distribution from the users
The users can see a distributed database system as a single monolithic database system

Distributed Relational Database Systems

Outline

Distributed database ? What is it ?
Centralized database schema model
Mainframe application architecture
Client-server architecture
Distributed client-server architecture
Web based architecture
Distributed Web based architecture
Distributed database schema architecture
Transparency
Autonomy
Advantages and disadvantages of DDBS
Distributed database system in PostgreSQL

A very important concept in organization of distributed database system is "transparency".

Transparency means hiding information from end users. Ther are few very different categories of transparency such as: data organization transparency, replication transparency, fragmentation transparency, design transparency and execution transparency.

Data organization (distribution or network) transparency means hiding network related information and data placement information; it is either location or naming transparency. In location transparency the users do not know the location details distributed database systems.

A global naming procedure is applied to the data objects located in different database systems. It is an example of naming transparency.

Location transparency hides information about the locations of data fragments in the database systems participating in a distributed system. Location transparency allows the operations to be independent on the locations of data objects.

Replication transparency hides from the users information about data replicated in many database systems of a distributed system. Replication transparency means that users are unaware of the existence of multiple copies of the same data objects

Fragmentation transparency hides from the users information about fragmentation data objects, for example vertical projections of one relational table located at different sites of distributed system. Fragmentation transparency means that users are unaware of data fragmentation over many sites. It is implement as combination of vertical and horizontal fragmentation.

Vertical fragmentation picks selected columns from a relational table together with a primary key column(s). Vertical fragments are located at the database systems depending on how frequently are used. Vertical fragments can be replicated in many database systems.

Horizontal fragmentation picks subsets of the rows from a relational table. The horizontal fragments are located at the database systems depending on how frequently are used. Horizontal fragments can be replicated in many database systems.

Transparency

Transparency means hiding information from end users
Data organization (distribution or network) transparency means hiding network related information and data placement information; it is either location or naming transparency
Naming transparency allows for global naming of data objects
Location transparency allows the operations to be independent on the locations of data objects
Replication transparency means that users are unaware of the existence of multiple copies of the same data objects
Fragmentation transparency means that users are unaware of data fragmentation over many sites; it includes vertical and horizontal fragmentation

Transparency

Design transparency means that users are unaware of how distributed database was designed
Execution transparency means that users are unaware of how database transactions are processed

Distributed Relational Database Systems

Outline

Distributed database ? What is it ?
Centralized database schema model
Mainframe application architecture
Client-server architecture
Distributed client-server architecture
Web based architecture
Distributed Web based architecture
Distributed database schema architecture
Transparency
Autonomy
Advantages and disadvantages of DDBS
Distributed database system in PostgreSQL

Another very important concept in organization of distributed database system is a level of "autonomy" granted to each database system participating a distributed system.

Autonomy determines a level of independence of individual nodes in distributed database system. A database system being a member of distributed database system may have very low degree of autonomy. Then, all its actions depend on a coordinator node of a distributed database system. High degree autonomy is required for flexibility and customized maintenance of distributed database system.

The dimensions of autonomy include: design, communication and execution autonomy.

Design autonomy means a level of independence of data model usage and transaction management technique between the nodes.

Communication autonomy means a level of independence to which a node can share information with other nodes

Execution autonomy means a level of independence to which users act as they please.

Autonomy

Autonomy determines a level of independence of individual nodes in distributed database system
High degree autonomy is required for flexibility and customized maintenance of distributed database system
Design autonomy means a level of independence of data model usage and transaction management technique between the nodes
Communication autonomy means a level of independence to which a node can share information with other nodes
Execution autonomy means a level of independence to which users act as they please

Distributed Relational Database Systems

Outline

Distributed database ? What is it ?
Centralized database schema model
Mainframe application architecture
Client-server architecture
Distributed client-server architecture
Web based architecture
Distributed Web based architecture
Distributed database schema architecture
Transparency
Autonomy
Advantages and disadvantages of DDBS
Distributed database system in PostgreSQL

Distributed database systems have their advantages and disadvantages.

The advantages include:

Higher level of reliability and availability

Improved ease and flexibility of application development

Improved performance

Easier expansion

Higher level of reliability and availability is possible when some data is replicated in different sites of distributed database system. Then, if one of the sites is unavailable due to heavy load or network problem it is always possible to get data from another site. If data is replicated in another site then such replication can be considered as some sort of a distributed backup that can be used in emergency situation.

Improved easy and flexibility of application development is possible when data is replicated in several site and some of these site are considered as productions sites while the other are development sites. Then there is no need suspend a production system because of application testing.

Distribution of data over many sites allows for parallelization of data processing in a number of sites. Also if data is not available at one site then it can be accessed in another site. Parallelization of data processing allows for improved performance.

A distributed database system always implement simple the expansion mechanisms through addition of new database sites.

Advantages and disadvantages of DDBS

Higher level of reliability and availability
Improved ease and flexibility of application development
Improved performance
Easier expansion

Distributed database systems have their advantages and disadvantages.

The disadvantages include:

Keeping track of data distribution

Distributed query processing

Distributed transaction management

Replicated data management

Distributed database recovery

Security

Distributed directory (catalog) management

Whenever data is distributed over a number of sites in a distributed database system it is necessary to keep information about the locations and replications of data items. Such information must be frequently updated when data is transferred between the sites and/or new replicates are created and/or old replicase a removed. Maintenance of information on data distribution takes additional time and imposed an additional workload on the database systems participating in a distributed system.

Optimization of query processing in a distributed system is a complex problem. Distribution and replication contributes to more query processing plans generate and evaluated by a query optimizer. It slows down optimization of query processing.

Distributed transaction processing is a difficult problem because of synchronization of many different transaction processing systems. For example, synchornization of transaction scheduling based on 2phase locking protocol in one database site and timestamp ordering protocol at another database site.

Another difficult problem is management of replicated data. Enforcement of strict consistency constraints on replicated data significantly slows dow distributed transaction processing.

The same performance problems occur in distributed database recovery. If one of subtransaction of distributed database transactions fails and it must be rolled back then all other subtransactions must be rolled back as well. A failure of a database site participating in a distributed system may require synchronization with the contents of the other sites during a recovery process.

Due to data replication more data is exposed to security risks. Data security protection can be implemented many different ways database sites participating in a distributed system. It makes coordination of data security policies and processes much harder than in a single centralized system.

The importance of distributed directory was already mentioned in a context of keeping information about location and replication of data items.

Advantages and disadvantages of DDBS

Keeping track of data distribution
Distributed query processing
Distributed transaction management
Replicated data management
Distributed database recovery
Security
Distributed directory (catalog) management

Distributed Relational Database Systems

Outline

Distributed database ? What is it ?
Centralized database schema model
Mainframe application architecture
Client-server architecture
Distributed client-server architecture
Web based architecture
Distributed Web based architecture
Distributed database schema architecture
Transparency
Autonomy
Advantages and disadvantages of DDBS
Distributed database system in PostgreSQL

Distributed Database System PostgreSQL

Implementation of distributed database system requires access with SQL to data located at a remote database system from outside a host database system
SQL/Management of External Data (MED) allows for access to data located at an external database system through implementation of foreign data wrappers (FDW)
A module postgres_fdw in PostgreSQL provides the foreign data wrapper postgres_fdw
A module postgres_fdw replaces the older dblink module

A procedure that is used to access to data located at an external/remote database with a module postgres_fdw consists of the following steps.

(1) First we must install an extension postgres_fdw (foreign data wrapper).

(2) Next, we must create a foreign server to determine a location of data stored in an external PostgreSQL database system.

(3) Next, we must create a user mapping, to identify the user/role that will be used on a foreign/remote server created in the previous step.

(4) Finally, we must create the foreign tables to access the relational tables located on a foreign/remote server from a user connected to a host server.

The steps (2), (3), and (4) provide information about location of a remote database system, a user who owns data to be accessed at a remote database system and the names of tables located on a remote database system.

Foreign tables created at a host site play a role of ''views'' of relational tables located at a remote database system. Foreign tables, like relational views'', located at a host site do not occupy any persistent storage at a host site, However, foreign tables can be used in the same way as relational views in SELECT statements.

Distributed Database System PostgreSQL

A procedure to access to data located at an external/remote database with a module postgres_fdw consists of the following steps:
- install an extension postgres_fdw
- create a foreign server to determine a location of data stored in an external PostgreSQL database system
- create a user mapping, to identify the user/role that will be used on a foreign/remote server
- create the foreign tables to access the relational tables located on a foreign/remote server

Distributed Database System PostgreSQLn

As an example, we consider a database that can be created through processing of the following CREATE TABLE statements

     
CREATE TABLE DEPARTMENT(
 name               VARCHAR(50)        NOT NULL,
 code               CHAR(5)            NOT NULL,
 total_staff_number DECIMAL(2)         NOT NULL,
 chair              VARCHAR(50)            NULL,
 budget             DECIMAL(9,1)       NOT NULL,
  CONSTRAINT dept_pkey PRIMARY KEY(name),
  CONSTRAINT dept_ckey1 UNIQUE(code),
  CONSTRAINT dept_ckey2 UNIQUE(chair),
  CONSTRAINT dept_check1 CHECK (total_staff_number BETWEEN 1 AND 50) );

     
CREATE TABLE COURSE(
 cnum               CHAR(7)           NOT NULL,
 title              VARCHAR(200)      NOT NULL,
 credits            DECIMAL(2)        NOT NULL,
 offered_by         VARCHAR(50)           NULL,
  CONSTRAINT course_pkey PRIMARY KEY(cnum),
  CONSTRAINT course_check1 CHECK (credits IN (6, 12)),
  CONSTRAINT course_fkey1 FOREIGN KEY(offered_by)
                          REFERENCES DEPARTMENT(name) ON DELETE CASCADE );

We shall use two database servers located at db1teach.adeis.uow.edu.au and db2teach.adeis.uow.edu.au sites.

We assume that a database server located at db1teach.adeis.uow.edu.au is a host database server and a database server located at db2teach.adeis.uow.edu.au is a remote database server.

A user xyz007 loaded data into the relational table DEPARTMENT and COURSE located at db1teach.adeis.uow.edu.au.

Next a user xyz007 connected to a database server located at db2teach.adeis.uow.edu.au and created identical relational table DEPARTMENT and COURSE.

A user xyz007 did not load data directly to the relational tables DEPARTMENT and COURSE. The plans are to copy the contents of the relational tables DEPARTMENT and COURSE located at db2teach.adeis.uow.edu.au to the same relation al tables located at db1teach.adeis.uow.edu.au. When successful such operation ca be repeated after the modifications performed on DEPARTMENT and COURSE to keep the contents of relational tables consistent at both locations.

A contents of a relational table can be copied into another relational table with an extended version of INSERT statement associated with SELECT statement. For example

INSERT INTO REMOTE_DEPARTMENT (SELECT * FROM DEPARTMENT);

A problem in this case is such that a table NEW_DEPARTMENT is located at a different database server from a table DEPARTMENT. PostgreSQL solution of the problem implemented as foreign data wrapper is to create a foreign relational table at a location db1teach.adeis.uow.edu.au (location of a host database) and to ''bind'' (or to ''map'') such table into the respective table located at db2teach.adeis.uow.edu.au.

A foreign table REMOTE_DEPARTMENT is similar to a relational view. It does not occupy any persistent storage at db1teach.adeis.uow.edu.au and it can be used to perform retrieval and data manipulations on the respective relational table DEPARTMENT located at db2teach.adeis.uow.edu.au.

A table REMOTE DEPARTMENT provides a "window" through which a table DEPARTMENT located at db2teach.adeis.uow.edu.au can be msearched and amnipulated by a host located at db1teach.adeis.uow.edu.au.

Distributed Database System PostgreSQL

A user xyz007 creates the relational tables DEPARTMENT and COURSE on a database server db1teach.adeis.uow.edu.au
A user xyz007 loads data into the relational tables DEPARTMENT and COURSE on a database server db1teach.adeis.uow.edu.au
A user xyz007 creates the relational tables DEPARTMENT and COURSE on a database server db2teach.adeis.uow.edu.au
A user xyz007 would like to copy information about a department Computer Science from a database server db1teach.adeis.uow.edu.au to a database server db2teach.adeis.uow.edu.au
To do so a user xyz007 must create the foreign relational tables with the names REMOTE_DEPARTMENT and REMOTE_COURSE on a database server db1teach.adeis.uow.edu.au and to map such tables into the relational tables DEPARTMENT and COURSE on a database server db2teach.adeis.uow.edu.au

The foreign tables can be used by a user xyz007 to remotely operate on the relational tables DEPARTMENT and COURSE on a database server db2teach.adeis.uow.edu.au without connecting to the server.

The foreign relational tables REMOTE_DEPARTMENT and REMOTE_COURSE created on a database server db1teach.adeis.uow.edu.au represent the respective relational tables DEPARTMENT and COURSE on a database server db2teach.adeis.uow.edu.au

If in a general case we would like to access a relational table R located on a database server we are not connected to then we must create a foreign relational table REMOTE_R on a database server we are connected to and we must map a table REMOTE_R into a table R on a database server we are not connected to.

A name of a foreign relational table is up to us but it must be different from the already existing relational tables or foreign relational tables

Distributed Database System PostgreSQL

Then a user xyz007 can remotely operate on the relational tables DEPARTMENT and COURSE on a database server db2teach.adeis.uow.edu.au without connecting to the server
The foreign relational tables REMOTE_DEPARTMENT and REMOTE_COURSE created on a database server db1teach.adeis.uow.edu.au represent the respective relational tables DEPARTMENT and COURSE on a database server db2teach.adeis.uow.edu.au
If in a general case we would like to access a relational table R located on a database server we are not connected to then we must create a foreign relational table REMOTE_R on a database server we are connected to and we must map a table REMOTE_R into a table R on a database server we are not connected to
A name of a foreign relational table is up to us but it must be different from the already existing relational tables or foreign relational tables

Distributed Database System PostgreSQL

To create a foreign relational table a user xyz007 creates an extension postgres_fdw (note, that a privilege to create the extansion has been already granted to a user xyz007
Next, a user xyz007 creates a foreign server using CREATE SERVER statement to a database server on a host db2teach.adaeis.uow.edu.au listening on port 5432

The database to which the connection is made is named xyz007 on the remote server

  
CREATE SERVER foreign_server FOREIGN DATA WRAPPER postgres_fdw
  OPTIONS (host 'db2teach.adeis.uow.edu.au', port '5432', dbname 'xyz007');

Next, a user xyz007 creates a user mapping with CREATE USER MAPPING statement to identify a user that will be used on the remote server.

CREATE USER MAPPING FOR xyz007 SERVER foreign_server OPTIONS (user 'xyz007', password 'xyz007');

The mapping allows a user xyz007 located at db1teach.adeis.uow.edu.au to connect as user xyz007 with a password xyz007 to a database server located at db1teach.adeis.uow.edu.au.

Finally, a user xyz007 creates a foreign relational table REMOTE_DEPARTMENT with CREATE FOREIGN TABLE statement.

CREATE FOREIGN TABLE REMOTE_DEPARTMENT (

name VARCHAR(50) NOT NULL,

code CHAR(5) NOT NULL,

total_staff_number DECIMAL(2) NOT NULL,

chair VARCHAR(50) NULL,

budget DECIMAL(9,1) NOT NULL )

SERVER foreign_server

OPTIONS (schema_name 'public', table_name 'department');

A statement CREATE FOREIGN table determines a structure of REMOTE_DEPARTMENT foreign table, a remote database server (foreign_server) where a table is located, name of a table at remote server (department) and a schema (public) where the table is located. Note that a structure of FOREIGN_DEPARTMENT table is exactly the same as a structure of DEPARTMENT table at a local host db1teach.adeis.uow.edu.au.

Distributed Database System PostgreSQL

Next, a user xyz007 creates a user mapping with CREATE USER MAPPING statement to identify a user that will be used on the remote server

Next, a user xyz007 creates a foreign relational table REMOTE_DEPARTMENT with CREATE FOREIGN TABLE statement

  
CREATE FOREIGN TABLE REMOTE_DEPARTMENT (
 name               VARCHAR(50)        NOT NULL,
 code               CHAR(5)            NOT NULL,
 total_staff_number DECIMAL(2)         NOT NULL,
 chair              VARCHAR(50)            NULL,
 budget             DECIMAL(9,1)       NOT NULL )
   SERVER foreign_server
   OPTIONS (schema_name 'public', table_name 'department');

Next, a user xyz007 create a foreign table REMOTE_COURSE in the following way.

CREATE FOREIGN TABLE REMOTE_COURSE(

cnum CHAR(7) NOT NULL,

title VARCHAR(200) NOT NULL,

credits DECIMAL(2) NOT NULL,

offered_by VARCHAR(50) NULL )

SERVER foreign_server

OPTIONS (schema_name 'public', table_name 'course');

Now, when both foreign tables are ready a user xyz ciopies the contents of a table DEPARTMENT into a table REMOTE_DEPARTMENT with a statement

Next, a user xyz007 copies information about Computer Science department from DEPARTMENT table to REMOTE_DEPARTMENT table

INSERT INTO REMOTE_DEPARTMENT(

SELECT *

FROM DEPARTMENT

WHERE name = 'Computer Science');

Note that a table DEPARTMENT must be copied first due to a referential integrity constraint enforced on a relational table COURSE

CONSTRAINT course_fkey1 FOREIGN KEY(offered_by) REFERENCES DEPARTMENT(name) ON DELETE CASCADE );

Distributed Database System PostgreSQL

Next, a user xyz007 creates a foreign relational table REMOTE COURSE with CREATE FOREIGN TABLE statement

  
CREATE FOREIGN TABLE REMOTE_COURSE(
 cnum               CHAR(7)           NOT NULL,
 title              VARCHAR(200)      NOT NULL,
 credits            DECIMAL(2)        NOT NULL,
 offered_by         VARCHAR(50)           NULL )
   SERVER foreign_server
   OPTIONS (schema_name 'public', table_name 'course');

Next, a user xyz007 copies information about Computer Science department from DEPARTMENT table to REMOTE_DEPARTMENT table

Distributed Database System PostgreSQL

Next, a user xyz007 copies information about the courses offered by Computer Science department from COURSE table to REMOTE_COURSE table

References

T. Connoly, C. Begg, Database Systems, A Practical Approach to Design, Implementation, and Management, Chapter 24 Distributed DBMs - Concepts and Design, Chapter 25.7 Distribution in Oracle, Pearson Education Ltd, 2015
PostgreSQL 16.1 Documentation, F.38. postgres_fdw — access data stored in external PostgreSQL servers