Introduction
The heterogeneity of heterogeneous database systems is mainly reflected in the following aspects:
Heterogeneity of computer architecture
Each participating database can run on mainframes, minicomputers, workstations, PCs or embedded systems.
Heterogeneous basic operating system
The basic operating system of each database system can be Unix, Windows NT, Linux, etc.
The heterogeneous DBMS itself
It can be Oracle, SQL Server, etc., which are both relational database systems, or databases with different data models. Such as relations, patterns, levels, networks, object-oriented, functional databases together form a heterogeneous database system.
----The goal of a heterogeneous database system is to realize the integration and sharing of data information resources, hardware equipment resources and human resources among different databases. One of the key points is to establish a global data model or global external view based on the local database model. This global model is particularly important for the establishment of advanced decision support systems.
----Large organizations have branches in many locations, and each sub-organization has its own information and data in the database, and decision-makers generally only care about the macroscopic and the description of the global model Information. The description of the global model of heterogeneous databases based on data warehouse technology is a good solution. The data warehouse can collect information from multiple databases in a heterogeneous database system and establish a unified global model. At the same time, the collected data also supports access to historical data. Users can make decision support through the unified data interface provided by the data warehouse. Query.
Operation
Import and export data between SQL Server databases
Use SELECT INTO to export data
Use the most in SQL Server The most common is to export data through the SELECT INTO statement. The SELECT INTO statement has two functions at the same time: creating an empty table based on the fields followed by SELECT and the table name followed by INTO (if the SELECT is *, the structure of the empty table and what FROM refers to The structure of the table is the same); insert the data found by SELECT into this empty table. When using the SELECT INTO statement, the table following INTO must not exist in the database, otherwise an error occurs. The following is an example of using SELECT INTO.
Suppose there is a table table1 with fields f1(int) and f2(varchar(50)).
SELECT * INTO table2 FROM table1
This SQL statement inserts all the data of table1 into table1 after creating table2, and you can also change * to f1 or f2 In order to insert data into the appropriate fields.
SELECT INTO can not only create tables in the same data, but also in different SQL Server databases.
USE db1
SELECT * INTO db2.dbo.table2 FROM table1
The above statement creates a table table2 whose owner is dbo in database db2, When creating a table in db2, the currently logged-in user must have permission to create a table in db2 to create table2. One thing to note when using SELECT INTO is that SELECT INTO cannot be used with COMPUTE, because COMPUTE returns a set of records, which will cause ambiguity (that is, you don't know which table to create an empty table based on).
Data sharing
----For heterogeneous database systems, data sharing should achieve two points: one is to achieve database conversion; the other is to achieve transparent access to data. The DM3 system, a commercial database management system with independent copyright, developed by Huazhong University of Science and Technology, realizes these two points through the provided database conversion tool and API interface.
----DM3 provides a database conversion tool, which can convert a model defined in a database system into a model in another database, and then load the data as needed, then the user You can use your familiar database system and familiar query language to achieve the goal of data sharing. The database conversion tool first performs type conversion, accesses the source database system, converts the data definition model of the source database to the data definition model of the target database, and then performs data reorganization, that is, loads the data in the source database system into the target database.
----In the process of conversion, sometimes it is more difficult to achieve strict equivalent conversion. First, determine the various grammatical and semantic conflicts in the two models. These conflicts may include:
naming conflicts
that is, the identifier in the source model may be the purpose The reserved words in the model need to be renamed at this time.
Format conflict
The same data type may have different representation methods and semantic differences. At this time, it is necessary to define the transformation function between the two models.
Structural conflict
If the data definition models between the two database systems are different, such as the relational model and the hierarchical model, then the entity attributes and connections need to be redefined to prevent attributes Or loss of contact information.
----In short, after data conversion, on the one hand, all the information that needs to be shared in the source database model is converted to the destination database, and on the other hand, this conversion cannot contain redundant associations. Information.
----Database conversion tool can realize the data model conversion between different database systems. The problem that needs further research is: If the database conversion is carried out at the same time as the data definition mode conversion and the data conversion, it may cause the same There are multiple copies of data collections in heterogeneous database systems, so new access control mechanisms need to be introduced. On the basis of ensuring the autonomy of each participating database and maintaining its integrity and security, it provides global access control, concurrency mechanism and security control for heterogeneous database systems.
----If the database conversion only performs data definition conversion and does not produce a copy of the data, then access to the data under the framework of the new target database definition model, the realization is still the data in the source database system Visit. At this time, transactions implemented using the data processing language in the new database system cannot directly access the source database and must be translated at the transaction level before they can be executed.
Database system
----Transparent access of data is realized in heterogeneous data system, users can regard heterogeneous distributed database system as ordinary distributed database The system uses the data processing language you are familiar with to access the database, just like accessing a database system. However, there is no widely used data definition model and data query language. To achieve transparent access to data, technologies such as many-to-one conversion and two-way middleware can be used. Open Database Connectivity (ODBC) is a standard application program interface (API) used to access data in related or unrelated database management systems. ODBC provides a set of high-level calling interface specifications and a dynamic link library-based operating support environment for applications. Commonly used front-end tools for database application development, such as Power Builder, Delphi, etc., connect to various database systems through the Open Database Interconnection (ODBC) interface. And most database management systems (such as: Oracle, Sybase, SQL Server, etc.) provide the corresponding ODBC driver, so that the database system has a very good openness. The biggest advantage of the ODBC interface is its interoperability. Ideally, each driver and data source should support exactly the same ODBC function calls and SQL statements, so that ODBC applications can operate all database systems. However, in fact, different databases have different levels of support for SQL syntax. Therefore, the ODBC specification defines the consistency level of the driver. The consistency of the ODBC API determines the types of ODBC functions that the application can call. ODBC 2.0 stipulates There are three levels of functions. DM3 ODBC API supports all the functions of the second level extension in the ODBC 2.0 specification.
Application
With the continuous popularization of Internet applications, Internet heterogeneous distributed information systems are developing rapidly. Java is platform-independent, highly portable, highly secure, Good stability, distributed, object-oriented and other advantages have become the language of choice for Internet application development. In the Internet environment, to realize database applications based on heterogeneous system platforms, a unified programming interface independent of the specific database management system and a general SQL-based database access method must be provided. The Java and database interface specification JDBC (Java Database Connectivity) is a universal application programming interface that supports basic SQL functions. It provides a unified user interface at the level of different database function modules, and is used for direct analysis of heterogeneous databases. Web access provides a new solution. JDBC has been supported by more and more database vendors, connection vendors, Internet service vendors and application programmers.