Distributed Database Management System UNIT-4. Learning Objective. MCA 325, Distributed DBMS And Object Oriented Databases

(1)

Distributed Database

Management System

g

y

UNIT-4

Learning Objective

Heterogeneous

database-federated database, reference architecture, loosely and tightly coupled,

Alternative architectures, Development tasks, operation – global task management.

Client server databases– SQL server,

Open database connectivity, Constructing an Application.

Definition: ‘Composed of heterogeneous hardware, operating system database management system and applications’. • Provides logically integrated view of existing

heterogeneous distributed databases

Heterogeneous Distributed Database

heterogeneous distributed databases. • The three-level architecture of a

Heterogeneous Distributed Database System (HDDMS)

(2)

External Schema 1 External Schema 2 External Schema 3

User User User User

Heterogeneous Distributed Database

Conceptual Schema

Internal Schema

• Data management system (different vendors)

• Data Models (i.e. relational, text indexing, object)

• Query the transaction processing algorithms

Challenges posed by integrating distributed databases

hypermedia)

• Format (i.e. Structured, unstructured) • Semantics

In a heterogeneous database (HDD), the local database is not managed by the same distributed database management system.

Federated Database:

“A bi ti f t

Challenges posed by integrating distributed databases Cont...

“A combination of autonomous, heterogeneous databases that are operating together”

(3)

• Definition

• Why do we need database federation • Problem with federated database • How does database federation work

Federated Databases

• A collection of databases that are treated as one entity and viewed through a single user interface.

• A federated database system integrates

Definition

y g

heterogeneous, autonomous database systems, whereby both local applications and global applications accessing multiple component database systems are supported.

• Such a federated database system is a complex system of systems which requires a well designed organization at the software architecture level.

(4)

• Significant productivity gains are to be had if you can work on all the different databases, including selects, inserts, updates, and deletes as if all the tables lived in a single database. Database federation makes the tables look like they are all in the same database.

If th l f i i t l d if th

Data Federation

can often can be satisfied with summary tables, there is a huge productivity boost by eliminating the need for a data mart and the corresponding need to create a new server, move significant quantities of data, and so on

• Organization of schemas in a schema architecture Organize the management and stewardship of the various data sets in some way that ensures their interoperability

Problems

• The "federator" system operates on the tables in the remote systems, the "federatees".

• The remote tables appear as virtual tables in the "Federator" database.

• Client application programs can perform

Federated Databases

• Client application programs can perform operations on the virtual tables in the "Federator" database, but the real persistent storage is in the remote database.

(5)

• Each "federatee" views the "federator" as just another database client connection. The "Federatee" is simply servicing client requests for database operations.

• The "federator" needs client software to

Federated Databases Cont…

FEDERATOR FEDERATEE 1 FEDERATEE 2

Members of federation

Figure 1

We wish to consider how contracts written in our framework can interact with one another.

Before doing so, let us return to a more Contract Interactions

intuitive level to see how contracts, unconstrained by our framework, might interact. Two contracts are in conflict when one requires a certain action and the other prohibits it.

(6)

The heart of the federated database problem is to be able to share certain data between independent organizations without giving up full control of that data.

Typically one organization will permit

The Basic Framework: Granting Rights

© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63,By Shivendra Goel. U4.16 Typically, one organization will permit another organization certain accesses to its data provided that the latter organization agrees to full certain obligations.

A problem that needs addressing is that obligations imposed by various contracts may conflict with one another. In the following we present a framework that eliminates potential conflicts between The Basic Framework: Granting Rights Cont…

© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63,By Shivendra Goel. U4.17 eliminates potential conflicts between obligations by eliminating the need for obligations.

In fact, we simply do not allow for obligations in the framework. One might say that there is only one global obligation, namely, to abide by the framework. (We also make the tacit assumption that an inter organizational access privilege can only be

The Basic Framework: Granting Rights Cont…

organizational access privilege can only be granted through a contract.) The desired effect will be achieved partly by the particulars of the contract and partly by the general framework itself. We make no attempt in this framework to address issues of implementation or assurance.

(7)

Specially, we will not be concerned with general mechanisms for guaranteeing that all parties must abide by the contract, though it will be possible for contracts to be written so as to impose some checks and

The Basic Framework: Granting Rights Cont…

• For federated database systems, the traditional three-level database schema architecture must be extended to support the dimensions of distribution heterogeneity, andautonomy.

Reference Architecture

g y y

• It provides the framework in which to understand, categorize and compare different architectural options for developing specific systems.

• Local Schema: A Local Schema is the conceptual schema of a component database system which is expressed in the (native) data model of that component.

Reference Architecture Cont…

• Component Schema:A Component Schema is a Local Schema transformed into the (canonical) data model of the federation layer.

(8)

• Export Schema: An Export Schema is derived from a Component Schema and defines an interface to the local data that is made available to the federation.

Federated Schema:When Exported Schemas are semantically heterogeneous, it is necessary to integrate them using another level. A Federated Schema on this higher level is the result of the integration of multiple

E t S h th idi i t t d

External Schema:An External Schema is a specific view on a Federated Schema or on a Local Schema. External Schemas may base on a specific data model different from the canonical data model. Basically, External

y,

Schemas serve as specific interfaces for applications (local or global).

(9)

• Loosely & tightly coupled • Alternative architecture • Development tasks

• Operation – Global task management Points to be covered

Information systems that provide interoperation and varying degrees of integration among multiple DBs are called.

Multi database systems or Federated systems or

Heterogeneous Distributed Databases

More generally, heterogeneous distributed database systems (HDDBSs)

• User creates and maintains federation schema, Also called Interoperable Database System • Creating schema corresponds to creating a view

against relevant export schemas

Th f h t b f

Loosely coupled FDBSs

• Therefore, each user must be aware of information and structure of the export schemas • Hard to support view updates – therefore,

(10)

• Flexibility of different interpretations possible for same federated schema

• Easier to cope with dynamic changes in Loosely coupled FDBSs -Advantages

• Duplicated effort in creation of similar federated schemas.

• Difficulty in understanding the semantics of schemas available to the user.

Loosely coupled FDBSs - Disadvantages

view updating cannot be supported.

Aim: provide location, replication and distribution transparency

• Federation administrators have full control over creation and maintenance of federated schemas and access to other export

Tightly coupled FDBSs

schemas and access to other export schemas

• Single federated schema same as global schema but view updates possible if administrators understand the mappings.

(11)

• FDBS administrator and component DBSs negotiate creation of export schemas during which administrator. has complete read access to component schema and/or data Violates autonomy

Tightly coupled FDBSs – Disadvantages

• Change in export/component schemas imply redoing federated schema creation.

System architecture - Core components combined in different ways to produce different data management architectures

•Data: Data are the basic facts and information managed by a DBS.

•Database: A database is a repository of

Processors in a FDBS

export schema to limit allowable operations submitted to corresponding component schemas

• Constructing P: Performs query decomposition and merges data

• Multiple databases created for the same functionality

• Different operating systems, data formats, query languages etc

Multi database systems

on heterogeneous computing platforms

Information sharing across dissimilar

platforms

Interconnect previously isolated

software systems (DBMS)

Multi database systems Cont…

software systems (DBMS)

Not

only

invoke

but

also

(13)

Multidatabase has been classified according to the following criteria:

1. Distribution:

* Data can be placed in many distributed Multi database systems Cont…

* Databases can be found in the same or different computer systems.

2. Heterogeneity:

* This is found in the differences in technology i.e., software, hardware, and operating system.

* The two types of heterogeneity are those Multi database systems Cont…

that are due to:

* differences in various DBMSs * differences in data semantics

3. Autonomy:

A component database can have the following autonomies:

•Design Autonomy:Choice of data presentation and attributes.

•Communication Autonomy : ability to decide on its own when to communicate with other components of the federated database system.

(14)

•Execution Autonomy: ability to execute some of its operations locally without influence by external operations that are executed in other component databases or in the federation database system.

•Autonomy (A)

A0: Tight integration A1: Semi-autonomous A2: Total isolation •Distribution (D)

Distribution

(A2. D2, H1) Architectural Alternatives (1)

D0: Non-distributed D1: Client Server D2: Peer-to-peer •Heterogeneity (H) • H0: Homogeneous • H1: Heterogeneous Heterogeneity Autonomy (A0. D0, H0) • (A0, D0, H0)

A collection of logically integrated DBMSs on the same site, also called

Composite Systems

Architectural Alternatives (2)

• (A0, D0, H1)

• Providing integrated access to heterogeneous systems on a single machine

(15)

• (A0, D1, H0)

Client Server distribution • (A0, D2, H0)

Fully distributed

• Semi-autonomous systems, also called

Federated Systems.Each DBMS knows how to participate in the federation

• (A1, D0, H1)

Heterogeneous Federated DBMSs. • (A1, D1, H1)

Distributed Heterogeneous Federated Architectural Alternatives (4)

• (A2, D0, H0)

• Multi-database Systems. Complete homogeneity in component systems is unlikely

• (A2, D0, H1)

Heterogeneous Multi-databases. Similar to (A1, D0, H1), but with full autonomy

• (A2, D1, H1), (A2, D2, H1)

(16)

• (Ax, D1, Hy)

Client Server Architecture • (A0, D2, H0)

Major DBMS Architectures (6)

• (A2, Dx, Hy)

• Multi-database Architecture

• Data management system (different vendors)

• Data Models (i.e. relational, text indexing, object)

• Query the transaction processing

Challenges posed by integrating HDDBS

• Data types (i.e., text graphics, multimedia, hypermedia)

• Format (i.e. Structured, unstructured) • Heterogeneity in Semantics

• Typical Heterogeneous databases include: World Wide Web

Multimedia

• Preservation of the database’s autonomy is Challenges posed by integrating HDDBS Cont...

paramount.

(17)

• Database Systems can range from: browsing across component databases querying a centralised data warehouse querying multiple databases.

• A heterogeneous database system can be Challenges posed by integrating HDDBS Cont...

provided with a multi-database query language.

• Semantic Heterogeneity(Different Database designers represent the same object in different ways.) e.g.

• Synonyms - same entity different names • Homonyms different entities same

Problems in Integrating Heterogeneous Databases

names

• Description Heterogeneity

(Different characteristics are used to describe the same object)

M d l H t it

Problems in Integrating Heterogeneous Databases Cont...

• Model Heterogeneity

(Use of different models to represent the same data e.g. SSADM, E-R Diagram, Use Cases)

(18)

• Based on complete integration to provide a single view

Advantages:

• Consistent, uniform view of and access to d t f

Global Schema Integration

• Users unaware of existing multiple existing DBs

Disadvantages

• Hard to automate creation of a global schema: structural, semantic or behavioral conflicts

• Autonomy esp association autonomy Global Schema Integration Cont…

© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63,By Shivendra Goel. U4.53 Autonomy esp. association autonomy sacrificed: all local data and operations to be revealed

• Loss of semantic information depending on how the schema integration is performed • Correctness of global schema is hard to

prove: hard because of context dependent meanings

Global Schema Integration Cont…

(19)

• GES : global external schema • GCS : global conceptual schema • LES : local external schema • LCS : local conceptual schema

Acronyms Used

• LIS : local internal schema

• Distribute the functionality between clientand server to better manage the complexity of the DBMS • Two-level Architecture

Client Server Architectures

Typical Scenario

1.Client parses a query, decomposes into independent site queries, and sends to appropriate server

2.Each server processes local query and sends the result relation to client

3.Client combines the results of sub-queries Server Client

User

Request Response

• A type of network in which eachworkstation

has equivalent capabilities and responsibilities.

• This differs from client/server architectures, in which some computers are dedicated to

Peer-to-peer Arch.

p serving the others.

• Peer-to-peer networks are generally simpler, but they usually do not offer the same performance under heavy loads.

(20)

• Created Late 80’s

• Uniform interface to write client s/w

for relational dbs

P

l

t d

t

d d f

Open Database Connectivity

• Popular - accepted as standard for

relational database

• Single API for client application to

work with different dbs.

• Applications using ODBC API can communicate with any relational db for which there is an ODBC driver • Compared to other db interfaces it is a low

l l i t f

ODBC API (The Interface)

• Enables client application to • configure and

• control the relational database at a relatively low level

Limited to relational dbs

Due to relational nature, difficult to use for non-relational data sources, such as

Object dbs

ODBC - Limitations

non-relational dbs

network directory services email stores

(21)

• ODBC Driver Manager (ODBC32.DLL) Calls functions in the ODBC drivers (DLLs) to perform operations on the database • Import Library (ODBC32.LIB)

ODBC - Provides

© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63,By Shivendra Goel. U4.61 Client applications link to it to use functions exposed by the ODBC driver manager • ODBC header files for the ODBC API

Client RDBMS Database Server ODBC Drivers ODBC Architecture

© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63,By Shivendra Goel. U4.62 Client Application Code Host System ISAM Files ODBC Driver Manager ODBC32.DLL • Introduction • Transact Sql • History • Releases • Description SQL Server • Description • Future Developments • Working

(22)

• Microsoft SQL Server is a relational database management system (RDBMS) produced by Microsoft.

• Its primary query language is Transact-SQL, an implementation of the ANSI/ISO standard

Microsoft SQL Server

• SQL Server is commonly used by businesses for small- to medium-sized databases, but the past five years have seen greater adoption of the product for larger enterprise databases.

Transact-SQL is an extension to the SQL database programming language.

It is a powerful language offering many features—

• A wide variety of data types

Transact-SQL

• Temporary objects

• System and extended stored procedures

• Scrollable cursors, • Conditional processing, • Transaction control,

• Exception and error handling, and much

Transact-SQL Cont…

(23)

• Microsoft, Sybase and Ashton-Tate originally teamed up to create and market the first version named SQL Server 1.0 for OS/2 (about 1989) which was essentially the same as Sybase SQL Server 3.0 on Unix, VMS t

History

• Microsoft SQL Server 4.2 was shipped around 1992 (available bundled with Microsoft OS/2 version 1.3). Later Microsoft SQL Server 4.21 for Windows NT was released .

• Microsoft SQL Server v6.0 was the first version of SQL Server that was architected for NT and did not include any direction from Sybase.

History Cont…

product to Adaptive Server Enterprise

• SQL Server 7.0 was the first true GUI based database server .

• The current version, Microsoft SQL Server 2005, was released in November of 2005. • Advancements have been made in

History Cont…

Advancements have been made in • performance,

(24)

• several complementary systems that are packaged with SQL Server 2005. These include:

• an ETL tool (SQL Server Integration Services or SSIS),

• a Reporting Server,

History Cont…

Services), and

• several messaging technologies, specifically Service Broker and Notification Services.

• 1993 - SQL Server 4.21 for Windows NT • 1995 - SQL Server 6.0, codenamedSQL95

• 1996 - SQL Server 6.5, codenamedHydra

• 1999 - SQL Server 7.0, codenamedSphinx

Releases

Plato

• 2000 - SQL Server 2000 32-bit, codenamed

Shiloh(version 8.0)

• 2003 - SQL Server 2000 64-bit, codenamed

Liberty

• 2005 SQL Server 2005 codenamedYukon

Releases Cont…

• 2005 - SQL Server 2005, codenamedYukon

(25)

• MS SQL Server uses a variant of SQL called T-SQL, or Transact-SQL, an implementation of SQL-92 with some extensions.

• T-SQL mainly adds additional syntax for use

Description

y y

in stored procedures, and affects the syntax of transaction support. (Note that SQL standards require Atomic, Consistent, Isolated, Durable or "ACID" transactions.)

• MS SQL Server and Sybase/ASE both communicate over networks using an application-level protocol called Tabular Data Stream (TDS).

• The TDS protocol has been implemented in

Description Cont…

© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63,By Shivendra Goel. U4.74 The TDS protocol has been implemented in order to allow more kinds of client applications to communicate with MS SQL Server and Sybase databases.

• MS SQL Server also supports Open Database Connectivity (ODBC).

• According to Microsoft, future versions of SQL Server aim to make data management

• self-tuning, • self organizing, • self maintaining

Future development

self maintaining

• with the introduction of SQL Server Always On technologies, to provide near-zero downtime.

(26)

• Microsoft also aims to intrinsically support many digital data formats, including pictures, audio, video and other multimedia data. In current versions, such multimedia data can be stored as BLOBs (binary large objects), but they are generic bitstreams Intrinsic

Future development Cont…

© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63,By Shivendra Goel. U4.76 but they are generic bitstreams. Intrinsic awareness of multimedia data will allow specialized functions to be performed on them.

• Better support for unstructured and semi-structured data is planned as well.

What is Object Oriented Database? (OODB)

• A database system that incorporates all the important object-oriented concepts.

• Some additional features

• Unique Object identifiers • Persistent object handling

Advantages of OODBS

• Designer can specify the structure of objects and

their behavior (methods)

• Better interaction with object-oriented languages such as Java and C++

• Definition of complex and user-defined types

• Encapsulation of operations and user-defined methods

(27)

• Lack of Industry Standards • RDBMS Popularity

Object Oriented Databases Disadvantages.

Spatial Databases

• It had been state that approximately 75% to 80% of all the information contains some notion of the location.

• So Spatial database system give the

concept of database that helps in keeping the track of objects in multidimensional space.

• A spatial database is also known as a database that is used to store and querying data related to the object in space like points, lines and polygons etc.

Spatial Databases Cont…

• While we can see that most of the data layer can deals with various numeric and character data types in order to implements spatial databases some additional functionalities needs to added in the database to add-on some geometry related spatial data types.

(28)

• A most common example for the spatial data we can see in the map of road, in which we can see various spatial objects along the 2-Dimension, some of these spatial objects are points, line and polygons etc. these objects some how can represents roads, cities, state boundaries etc.

Spatial Databases Cont…

• A road map is a classic example of spatial information. The roads, cities, state boundaries reside on the piece of paper in 2-D format. A GIS is used to retrieve, store and manage the spatial data.

Types of Queries supported by spatial Databases:

1. Spatial Scope-Distance Query. 2. Spatial Nearly Queries 3. Joins Queries.

Spatial Databases Cont…

Multimedia Databases

• Multimedia Database System(MMDBS) are created

in order to cope up with the growth in the usage of huge volume of multimedia data that is used by various applications such as journalism software applications, retailing, entertainment and e-libraries etc.

libraries etc.

• The multimedia data has the influence both directly as well as indirectly in the development of

(29)

• Multimedia data bases are design in the various similar manner that are highlighted by the classical DDBMSs to give ease of use and understandability to its user, along with the MMDBS provide some additional features to its user.

Multimedia Databases

process, retrieve, present and transmit variety of multimedia data-types in lot many formats

multimedia database support more new features as compared to the traditional DBMSs.

Multimedia Databases Cont...

• Multimedia data characterize in images, video,

audio and text.

• Moreover, MMDBSs are very necessary for the

y y

efficient as well as effective management of large amount of multimedia data.

Deductive Databases

• A deductive database is a database system that

can make deductions, these deductions are based on some rules and facts that are stored in the databases (deductive).

• Prolog and Datalog are the languages that are mainly used to specifies rules, facts and queries in the deductive databases.

(30)

• Deductive data bases are based on the desire of combining logic programming with the RDBMS to made such a system that support powerful calculation and also system can give very fast response time and deals with large datasets. Deductive database systems are far more

Deductive Databases Cont…

• Expensive than a RDBMS system but economical against logic programming systems.

• Now a days, Deductive database systems are largely used in academics as compare to industries,

Deductive Databases Cont…

© Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63,By Shivendra Goel. U4.89 but lot many concepts of Deductive database system are used in today's Relation Database systems to give support to various new SQL standards.

Temporal Databases

• Temporal database are created to store the

temporal data, it means data that is dependent on time.

• Temporal DBMS or TDBMS is created in order to support the manipulation as well as maintenance of the temporal data.

• TDBMS provide the Temporal data model (TDM) that comprises of TDDL (Temporal Data Definition Language) and TDML (Temporal Data Manipulation

(31)

• Temporal database objects are defined using TDDL while Temporal database are manipulated i.e., update, delete and retrieve etc. with the help of TDML.

Temporal Databases Cont…

related to the temporal data are very difficult to express in term of simple SQL query, while temporal DBMS give us a better support to implements these specific queries, it may also be possible to use SQL queries for these purpose but again the performance factor is a big issue.

Short Questions

• Explain about the federated database give any example ?

• What is Open database connectivity? • Briefly describe the architecture of SQL

server?

• Write some difference between

heterogeneous and homogenous DDBMS?

Long Questions

• Explain any one heterogeneous DDBMS ? What are the problems that can occur in a

heterogeneous DDBMS.

• Write Short notes on the following : (a) Open Database Connectivity (a) Open Database Connectivity (b) SQL – Server Architecture • Discuss the loosely and tightly coupled

architectures of parallel and distributed systems. • What is federated database system? Explain its

(32)

Long Questions

• Difference between homogenous DDBMS and heterogeneous DDBMS ? Give one example in each case?

• Explain client server database and peer to

peer architecture?

• Explain reference architecture of federated database ?

References

1. S. Ceri, G. Pelagatti, “Distributed Database: Principles and Systems”, McGraw Hill, New York, 1985. 2. M. Tamer Ozsu, Patrick Valduriez, “Principles of

Distributed Databases System”, Pearson, 2nd Ed., 2009.

3 Mario Piattini “Advanced Database Technology and

3. Mario Piattini, Advanced Database Technology and Design”, Artech House, UK, 2000.

4. Shivendra Goel, Divya Goel, “ Distributed Database Management System”, Sun India Publications, 2009. 5. Chhanda Ray, “Distributed Database System”,