Distributed Database Management System UNIT4 Bharati Vidyapeeths Institute

Distributed Database Management System UNIT-4 © Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63, By Dr. Imran Khan (Asst. Prof. ) U 4. 1

Learning Objective Heterogeneous databasefederated 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. © Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63, By Dr. Imran Khan (Asst. Prof. ) U 4. 2

Heterogeneous Distributed Database Definition: ‘Composed of heterogeneous hardware, operating system database management system and applications’. • Provides logically integrated view of existing heterogeneous distributed databases. • The three-level architecture of a Heterogeneous Distributed Database System (HDDMS) © Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63, By Dr. Imran Khan (Asst. Prof. ) U 4. 3

Heterogeneous Distributed Database User External Schema 1 User External Schema 2 User External Schema 3 Conceptual Schema Internal Schema © Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63, By Dr. Imran Khan (Asst. Prof. ) U 4. 4

Challenges posed by integrating distributed databases • Data management system (different vendors) • Data Models (i. e. relational, text indexing, object) • Query the transaction processing algorithms • Data types (i. e. , text graphics, multimedia, hypermedia) • Format (i. e. Structured, unstructured) • Semantics © Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63, By Dr. Imran Khan (Asst. Prof. ) U 4. 5

Challenges posed by integrating distributed databases Cont. . . In a heterogeneous database (HDD), the local database is not managed by the same distributed database management system. Federated Database: “A combination of autonomous, heterogeneous databases that are operating together” © Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63, By Dr. Imran Khan (Asst. Prof. ) U 4. 6

Federated Databases • • • Definition Why do we need database federation Problem with federated database How does database federation work Reference Architecture © Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63, By Dr. Imran Khan (Asst. Prof. ) U 4. 7

Definition • A collection of databases that are treated as one entity and viewed through a single user interface. • A federated database system integrates heterogeneous, autonomous database systems, whereby both local applications and global applications accessing multiple component database systems are supported. © Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63, By Dr. Imran Khan (Asst. Prof. ) U 4. 8

Federated Databases • Such a federated database system is a complex system of systems which requires a well designed organization at the software architecture level. © Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63, By Dr. Imran Khan (Asst. Prof. ) U 4. 9

Data Federation • 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 the volume of queries is not large, and if they 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 © Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63, By Dr. Imran Khan (Asst. Prof. ) U 4. 10

Problems • Organization of schemas in a schema architecture Organize the management and stewardship of the various data sets in some way that ensures their interoperability. © Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63, By Dr. Imran Khan (Asst. Prof. ) U 4. 11

Federated Databases • 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 operations on the virtual tables in the "Federator" database, but the real persistent storage is in the remote database. © Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63, By Dr. Imran Khan (Asst. Prof. ) U 4. 12

Federated Databases Cont… • 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 access each remote database © Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63, By Dr. Imran Khan (Asst. Prof. ) U 4. 13

Members of federation FEDERATOR FEDERATEE 1 FEDERATEE 2 Figure 1 © Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63, By Dr. Imran Khan (Asst. Prof. ) U 4. 14

Contract Interactions We wish to consider how contracts written in our framework can interact with one another. Before doing so, let us return to a more 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. © Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63, By Dr. Imran Khan (Asst. Prof. ) U 4. 15

The Basic Framework: Granting Rights 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 another organization certain accesses to its data provided that the latter organization agrees to full certain obligations. © Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63, By Dr. Imran Khan (Asst. Prof. ) U 4. 16

The Basic Framework: Granting Rights Cont… 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 obligations by eliminating the need for obligations. © Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63, By Dr. Imran Khan (Asst. Prof. ) U 4. 17

The Basic Framework: Granting Rights Cont… 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 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. © Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63, By Dr. Imran Khan (Asst. Prof. ) U 4. 18

The Basic Framework: Granting Rights Cont… 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 balances toward this end. © Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63, By Dr. Imran Khan (Asst. Prof. ) U 4. 19

Reference Architecture • For federated database systems, the traditional three-level database schema architecture must be extended to support the dimensions of distribution heterogeneity, and autonomy. • It provides the framework in which to understand, categorize and compare different architectural options for developing specific systems. © Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63, By Dr. Imran Khan (Asst. Prof. ) U 4. 20

Reference Architecture Cont… • 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. • Component Schema: A Component Schema is a Local Schema transformed into the (canonical) data model of the federation layer. © Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63, By Dr. Imran Khan (Asst. Prof. ) U 4. 21

Reference Architecture Cont… • 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. © Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63, By Dr. Imran Khan (Asst. Prof. ) U 4. 22

Reference Architecture Cont… 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 Export Schemas; thus, providing an integrated view. © Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63, By Dr. Imran Khan (Asst. Prof. ) U 4. 23

Reference Architecture Cont… 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 Schemas serve as specific interfaces for applications (local or global). © Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63, By Dr. Imran Khan (Asst. Prof. ) U 4. 24

Points to be covered • • Loosely & tightly coupled Alternative architecture Development tasks Operation – Global task management © Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63, By Dr. Imran Khan (Asst. Prof. ) U 4. 25

Heterogeneous Distributed Databases Information systems that provide interoperation and varying degrees of integration among multiple DBs are called. Multi database systems or Federated systems or More generally, heterogeneous distributed database systems (HDDBSs) © Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63, By Dr. Imran Khan (Asst. Prof. ) U 4. 26

Loosely coupled FDBSs • User creates and maintains federation schema, Also called Interoperable Database System • Creating schema corresponds to creating a view against relevant export schemas • Therefore, each user must be aware of information and structure of the export schemas • Hard to support view updates – therefore, assume highly autonomous read-only DBs © Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63, By Dr. Imran Khan (Asst. Prof. ) U 4. 27

Loosely coupled FDBSs - Advantages • Flexibility of different interpretations possible for same federated schema • Easier to cope with dynamic changes in schemas since it is easier to create views. Detection of changes is however expensive. © Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63, By Dr. Imran Khan (Asst. Prof. ) U 4. 28

Loosely coupled FDBSs - Disadvantages • Duplicated effort in creation of similar federated schemas. • Difficulty in understanding the semantics of schemas available to the user. • Due to possible multiple view creations, view updating cannot be supported. © Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63, By Dr. Imran Khan (Asst. Prof. ) U 4. 29

Tightly coupled FDBSs Aim: provide location, replication and distribution transparency • Federation administrators have full control over creation and maintenance of federated schemas and access to other export schemas • Single federated schema same as global schema but view updates possible if administrators understand the mappings. © Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63, By Dr. Imran Khan (Asst. Prof. ) U 4. 30

Tightly coupled FDBSs – Disadvantages • 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 • Change in export/component schemas imply redoing federated schema creation. © Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63, By Dr. Imran Khan (Asst. Prof. ) U 4. 31

More on Federated Databases 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 data structured according to a data model. • Commands: Commands are requests for specific actions that are either entered by a user or generated by a processor. © Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63, By Dr. Imran Khan (Asst. Prof. ) U 4. 32

More on Federated Databases Cont… • Processors: Processors are software modules that manipulate commands and data. • Schemas: Schemas are descriptions of data managed by one or more DBMSs. A schema consists of schema objects and their interrelationships. • Mappings: Mappings are functions that correlate the schema objects in one schema to the schema objects in another schema. © Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63, By Dr. Imran Khan (Asst. Prof. ) U 4. 33

Processors in a FDBS • Transforming P: Uses mappings to transform commands from internal command language to local query language etc. • Filtering P: Uses access control specified in export schema to limit allowable operations submitted to corresponding component schemas • Constructing P: Performs query decomposition and merges data © Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63, By Dr. Imran Khan (Asst. Prof. ) U 4. 34

Multi database systems • Multiple databases created for the same functionality • Different operating systems, data formats, query languages etc • Typically DBs managed by DBMSs running on heterogeneous computing platforms © Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63, By Dr. Imran Khan (Asst. Prof. ) U 4. 35

Multi database systems Cont… Information sharing across dissimilar platforms Interconnect previously isolated software systems (DBMS) Not only invoke but also coordinate interactions © Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63, By Dr. Imran Khan (Asst. Prof. ) U 4. 36

Multi database systems Cont… Multidatabase has been classified according to the following criteria: 1. Distribution: * Data can be placed in many distributed databases. * Databases can be found in the same or different computer systems. © Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63, By Dr. Imran Khan (Asst. Prof. ) U 4. 37

Multi database systems Cont… 2. Heterogeneity: * This is found in the differences in technology i. e. , software, hardware, and operating system. * The two types of heterogeneity are those that are due to: * differences in various DBMSs * differences in data semantics © Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63, By Dr. Imran Khan (Asst. Prof. ) U 4. 38

Multi database systems Cont… 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. © Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63, By Dr. Imran Khan (Asst. Prof. ) U 4. 39

Multi database systems Cont… 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. © Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63, By Dr. Imran Khan (Asst. Prof. ) U 4. 40

Architectural Alternatives (1) • Autonomy (A) A 0: Tight integration A 1: Semi-autonomous A 2: Total isolation • Distribution (D) D 0: Non-distributed D 1: Client Server D 2: Peer-to-peer • Heterogeneity (H) • H 0: Homogeneous • H 1: Heterogeneous Distribution (A 2. D 2, H 1) Autonomy (A 0. D 0, H 0) Heterogeneity © Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63, By Dr. Imran Khan (Asst. Prof. ) U 4. 41

Architectural Alternatives (2) • (A 0, D 0, H 0) A collection of logically integrated DBMSs on the same site, also called Composite Systems • (A 0, D 0, H 1) • Providing integrated access to heterogeneous systems on a single machine © Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63, By Dr. Imran Khan (Asst. Prof. ) U 4. 42

Architectural Alternatives (3) • (A 0, D 1, H 0) Client Server distribution • (A 0, D 2, H 0) Fully distributed • (A 1, D 0, H 0) • Semi-autonomous systems, also called Federated Systems. Each DBMS knows how to participate in the federation © Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63, By Dr. Imran Khan (Asst. Prof. ) U 4. 43

Architectural Alternatives (4) • (A 1, D 0, H 1) Heterogeneous Federated DBMSs. • (A 1, D 1, H 1) Distributed Heterogeneous Federated DBMSs • (A 2, D 0, H 0) • Multi-database Systems. Complete homogeneity in component systems is unlikely © Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63, By Dr. Imran Khan (Asst. Prof. ) U 4. 44

Architectural Alternatives (5) • (A 2, D 0, H 1) Heterogeneous Multi-databases. Similar to (A 1, D 0, H 1), but with full autonomy • (A 2, D 1, H 1), (A 2, D 2, H 1) Distributed Multi-database Systems © Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63, By Dr. Imran Khan (Asst. Prof. ) U 4. 45

Major DBMS Architectures (6) • (Ax, D 1, Hy) Client Server Architecture • (A 0, D 2, H 0) Peer-to-peer Architecture • (A 2, Dx, Hy) • Multi-database Architecture © Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63, By Dr. Imran Khan (Asst. Prof. ) U 4. 46

Challenges posed by integrating HDDBS • Data management system (different vendors) • Data Models (i. e. relational, text indexing, object) • Query the transaction processing algorithms • Data types (i. e. , text graphics, multimedia, hypermedia) • Format (i. e. Structured, unstructured) • Heterogeneity in Semantics © Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63, By Dr. Imran Khan (Asst. Prof. ) U 4. 47

Challenges posed by integrating HDDBS Cont. . . • Typical Heterogeneous databases include: World Wide Web Multimedia • Preservation of the database’s autonomy is paramount. • Data access facilities in Heterogeneous © Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63, By Dr. Imran Khan (Asst. Prof. ) U 4. 48

Challenges posed by integrating HDDBS Cont. . . • Database Systems can range from: browsing across component databases querying a centralised data warehouse querying multiple databases. • A heterogeneous database system can be provided with a multi-database query language. © Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63, By Dr. Imran Khan (Asst. Prof. ) U 4. 49

Problems in Integrating Heterogeneous Databases • Semantic Heterogeneity (Different Database designers represent the same object in different ways. ) e. g. • Synonyms - same entity different names • Homonyms - different entities same names © Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63, By Dr. Imran Khan (Asst. Prof. ) U 4. 50

Problems in Integrating Heterogeneous Databases Cont. . . • Description Heterogeneity (Different characteristics are used to describe the same object) • Model Heterogeneity (Use of different models to represent the same data e. g. SSADM, E-R Diagram, Use Cases) © Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63, By Dr. Imran Khan (Asst. Prof. ) U 4. 51

Global Schema Integration • Based on complete integration to provide a single view Advantages: • Consistent, uniform view of and access to data for users • Users unaware of existing multiple existing DBs © Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63, By Dr. Imran Khan (Asst. Prof. ) U 4. 52

Global Schema Integration Cont… Disadvantages • Hard to automate creation of a global schema: structural, semantic or behavioral conflicts • Autonomy esp. association autonomy sacrificed: all local data and operations to be revealed © Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63, By Dr. Imran Khan (Asst. Prof. ) U 4. 53

Global Schema Integration Cont… • 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 © Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63, By Dr. Imran Khan (Asst. Prof. ) U 4. 54

Acronyms Used • • • GES : global external schema GCS : global conceptual schema LES : local external schema LCS : local conceptual schema LIS : local internal schema © Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63, By Dr. Imran Khan (Asst. Prof. ) U 4. 55

Client Server Architectures • Distribute the functionality between client and server to better manage the complexity of the DBMS • Two-level Architecture User 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 Client Response Request Server © Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63, By Dr. Imran Khan (Asst. Prof. ) U 4. 56

Peer-to-peer Arch. • A type of network in which each workstation has equivalent capabilities and responsibilities. • This differs from client/server architectures, in which some computers are dedicated to serving the others. • Peer-to-peer networks are generally simpler, but they usually do not offer the same performance under heavy loads. © Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63, By Dr. Imran Khan (Asst. Prof. ) U 4. 57

Open Database Connectivity • Created Late 80’s • Uniform interface to write client s/w for relational dbs • Popular - accepted as standard for relational database • Single API for client application to work with different dbs. © Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63, By Dr. Imran Khan (Asst. Prof. ) U 4. 58

ODBC API (The Interface) • 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 level interface • Enables client application to • configure and • control the relational database at a relatively low level © Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63, By Dr. Imran Khan (Asst. Prof. ) U 4. 59

ODBC - Limitations Limited to relational dbs Due to relational nature, difficult to use for non-relational data sources, such as Object dbs non-relational dbs network directory services email stores etc © Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63, By Dr. Imran Khan (Asst. Prof. ) U 4. 60

ODBC - Provides • ODBC Driver Manager (ODBC 32. DLL) Calls functions in the ODBC drivers (DLLs) to perform operations on the database • Import Library (ODBC 32. LIB) Client applications link to it to use functions exposed by the ODBC driver manager • ODBC header files for the ODBC API © Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63, By Dr. Imran Khan (Asst. Prof. ) U 4. 61

ODBC Architecture ODBC Drivers Client Application Code RDBMS Database Server ODBC Driver Manager Host System ODBC 32. DLL ISAM Files © Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63, By Dr. Imran Khan (Asst. Prof. ) U 4. 62

SQL Server • • Introduction Transact Sql History Releases Description Future Developments Working © Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63, By Dr. Imran Khan (Asst. Prof. ) U 4. 63

Microsoft SQL Server • 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 Structured Query Language (SQL) used by both Microsoft and Sybase. • 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. © Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63, By Dr. Imran Khan (Asst. Prof. ) U 4. 64

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 • Temporary objects • System and extended stored procedures © Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63, By Dr. Imran Khan (Asst. Prof. ) U 4. 65

Transact-SQL Cont… • • Scrollable cursors, Conditional processing, Transaction control, Exception and error handling, and much more © Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63, By Dr. Imran Khan (Asst. Prof. ) U 4. 66

History • 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, etc. • 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. © Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63, By Dr. Imran Khan (Asst. Prof. ) U 4. 67

History Cont… • Microsoft SQL Server v 6. 0 was the first version of SQL Server that was architected for NT and did not include any direction from Sybase. • Later, Sybase changed the name of its product to Adaptive Server Enterprise © Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63, By Dr. Imran Khan (Asst. Prof. ) U 4. 68

History Cont… • 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 • performance, • the client IDE tools, and © Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63, By Dr. Imran Khan (Asst. Prof. ) U 4. 69

History Cont… • several complementary systems that are packaged with SQL Server 2005. These include: an ETL tool (SQL Server Integration Services or SSIS), a Reporting Server, an OLAP and data mining server (Analysis Services), and several messaging technologies, specifically Service Broker and Notification Services. © Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63, By Dr. Imran Khan (Asst. Prof. ) U 4. 70

Releases • • • 1993 - SQL Server 4. 21 for Windows NT 1995 - SQL Server 6. 0, codenamed SQL 95 1996 - SQL Server 6. 5, codenamed Hydra 1999 - SQL Server 7. 0, codenamed Sphinx 1999 - SQL Server 7. 0 OLAP, codenamed Plato © Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63, By Dr. Imran Khan (Asst. Prof. ) U 4. 71

Releases Cont… • 2000 - SQL Server 2000 32 -bit, codenamed Shiloh (version 8. 0) • 2003 - SQL Server 2000 64 -bit, codenamed Liberty • 2005 - SQL Server 2005, codenamed Yukon (version 9. 0) © Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63, By Dr. Imran Khan (Asst. Prof. ) U 4. 72

Description • 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 in stored procedures, and affects the syntax of transaction support. (Note that SQL standards require Atomic, Consistent, Isolated, Durable or "ACID" transactions. ) © Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63, By Dr. Imran Khan (Asst. Prof. ) U 4. 73

Description Cont… • 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 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). © Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63, By Dr. Imran Khan (Asst. Prof. ) U 4. 74

Future development • According to Microsoft, future versions of SQL Server aim to make data management • self-tuning, • self organizing, • self maintaining • with the introduction of SQL Server Always On technologies, to provide near-zero downtime. © Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63, By Dr. Imran Khan (Asst. Prof. ) U 4. 75

Future development Cont… • 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 awareness of multimedia data will allow specialized functions to be performed on them. • Better support for unstructured and semistructured data is planned as well. © Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63, By Dr. Imran Khan (Asst. Prof. ) U 4. 76

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 © Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63, By Dr. Imran Khan (Asst. Prof. ) U 4. 77

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 © Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63, By Dr. Imran Khan (Asst. Prof. ) U 4. 78

Object Oriented Databases Disadvantages. • Lack of Industry Standards • RDBMS Popularity © Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63, By Dr. Imran Khan (Asst. Prof. ) U 4. 79

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. © Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63, By Dr. Imran Khan (Asst. Prof. ) U 4. 80

Spatial Databases Cont… • 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. • 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. © Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63, By Dr. Imran Khan (Asst. Prof. ) U 4. 81

Spatial Databases Cont… • 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. • 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. © Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63, By Dr. Imran Khan (Asst. Prof. ) U 4. 82

Spatial Databases Cont… Types of Queries supported by spatial Databases: 1. Spatial Scope-Distance Query. 2. Spatial Nearly Queries 3. Joins Queries. © Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63, By Dr. Imran Khan (Asst. Prof. ) U 4. 83

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, e-retailing, entertainment and elibraries etc. • The multimedia data has the influence both directly as well as indirectly in the development of multimedia databases. © Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63, By Dr. Imran Khan (Asst. Prof. ) U 4. 84

Multimedia Databases • 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. • These MMDBs are provide a frame work to store, 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. © Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63, By Dr. Imran Khan (Asst. Prof. ) U 4. 85

Multimedia Databases Cont. . . • Multimedia data characterize in images, video, audio and text. • Moreover, MMDBSs are very necessary for the efficient as well as effective management of large amount of multimedia data. © Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63, By Dr. Imran Khan (Asst. Prof. ) U 4. 86

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. © Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63, By Dr. Imran Khan (Asst. Prof. ) U 4. 87

Deductive Databases Cont… • 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. © Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63, By Dr. Imran Khan (Asst. Prof. ) U 4. 88

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, but lot many concepts of Deductive database system are used in today's Relation Database systems to give support to various new SQL standards. © Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63, By Dr. Imran Khan (Asst. Prof. ) U 4. 89

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 Language) © Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63, By Dr. Imran Khan (Asst. Prof. ) U 4. 90

Temporal Databases Cont… • Temporal database objects are defined using TDDL while Temporal database are manipulated i. e. , update, delete and retrieve etc. with the help of TDML. • Further, it can be seen that some specific queries 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. © Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63, By Dr. Imran Khan (Asst. Prof. ) U 4. 91

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? © Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63, By Dr. Imran Khan (Asst. Prof. ) U 4. 92

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 (b) SQL – Server Architecture • Discuss the loosely and tightly coupled architectures of parallel and distributed systems. • What is federated database system? Explain its features. © Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63, By Dr. Imran Khan (Asst. Prof. ) U 4. 93

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 ? © Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63, By Dr. Imran Khan (Asst. Prof. ) U 4. 94

References 1. S. Ceri, G. Pelagatti, “Distributed Database: Principles and Systems”, Mc. Graw Hill, New York, 1985. 2. M. Tamer Ozsu, Patrick Valduriez, “Principles of Distributed Databases System”, Pearson, 2 nd Ed. , 2009. 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”, Pearson, 2009. © Bharati Vidyapeeth’s Institute of Computer Applications and Management, New Delhi-63, By Dr. Imran Khan (Asst. Prof. ) U 4. 95