Intelligent Implementation Processor Design for Oracle Distributed Databases System

Despite the increasing need for modeling and implementing Distributed Databases (DDB), distributed database management systems are still quite far from helping the designer to directly implement its BDD. Indeed, the fundamental principle of implementation of a DDB is to make the database appear as a centralized database, providing series of transparencies, something that is not provided directly by the current DDBMS. We focus in this work on Oracle DBMS which, despite its market dominance, offers only a few logical mechanisms to implement distribution. To remedy this problem, we propose a new architecture of DDBMS Oracle. The idea is based on extending it by an intelligent layer that provides: 1) creation of different types of fragmentation through a GUI for defining different sites geographically dispersed 2) allocation and replication of DB. The system must automatically generate SQL scripts for each site of the original configuration.

💡 Research Summary

This paper, “Intelligent Implementation Processor Design for Oracle Distributed Databases System,” addresses a significant practical challenge in the field of distributed databases: the lack of adequate automated support for implementing a Distributed Database (DDB) design within commercial Database Management Systems (DBMS), specifically Oracle. Despite Oracle’s market dominance, its native tools, like Oracle Partitioning, are limited. They do not automatically ensure distribution transparency (hiding the complexities of distribution from users and applications) and fail to directly support fundamental DDB concepts such as fragmentation, allocation, and replication. Consequently, the database designer is burdened with the massive, error-prone manual task of writing complex SQL scripts—including database links, synonyms, materialized views, and intricate triggers/stored procedures—to manually enforce distribution policies and cross-site integrity constraints.

The core contribution of this work is the proposal of a novel “intelligent layer” architecture intended to extend the existing Oracle DDBMS. This layer aims to bridge the gap between high-level DDB design and its actual implementation. Its primary functions are twofold: first, to provide a graphical user interface (GUI) that allows designers to visually define distribution policies, including different types of fragmentation (horizontal, vertical, hybrid, and derived) and data replication across geographically dispersed sites; and second, to automatically generate the complete suite of valid SQL implementation scripts tailored for each site in the distributed configuration.

The paper provides a comprehensive background on Oracle’s distributed features (Chapter 2), highlighting its client-server architecture, database links, and the manual steps required to simulate transparency. A detailed case study (Chapter 3) of a distributed library management system for a university vividly illustrates the sheer volume and complexity of manual coding currently necessary, using sample scripts for creating links, tables, materialized views, and a multi-branch trigger for insert operations.

The proposed architecture (Chapter 5) is designed with clear objectives: to assist in schema design via a productive GUI, to automate the translation of the validated design into executable SQL, to ensure inter-site integrity constraints through generated triggers, and to guarantee transparency to end-users via automatically created views. The operating logic of this layer is presented as a detailed algorithm that guides the designer through selecting tables, choosing fragmentation types, validating results, and iterating until a complete fragmentation scheme is achieved.

Finally, the paper validates the proposed approach by presenting a functional prototype tool named “Intelligent-DDB” (Chapter 6). Developed using the .NET framework, this wizard-based tool implements the theoretical architecture. It features screens for connecting to the source database, defining a network map of sites, and visually configuring fragmentation rules through interactive interfaces. The tool’s culmination is the automatic generation of site-specific SQL script files, which can then be executed either automatically by the tool (if network permissions allow) or manually by administrators. This prototype demonstrates the feasibility of automating the entire process from graphical design to deployment-ready code, thereby significantly reducing the effort, time, and potential for error in implementing Oracle-based distributed databases.