10.8 Distributed Database Transparency Features

1. 10.8 Distributed Database Transparency Features • Allow end user to feel like database’s only user • Features include: • Distribution transparency • Partition, Replication, Location • Transaction transparency • Maintain ACID properties • Failure transparency • Performance transparency • No performance degradation • Heterogeneity transparency Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel

2. 10.9 Distribution Transparency • Allows management of a physically dispersed database as though it were a centralized database • Three levels of distribution transparency are recognized: • Fragmentation transparency • Supported by Distributed Data Catalog(DDC) • Distributed global scheme is used by local TPs to translate user requests into subqueries (remote) that will be processed by different DPs • Location transparency • Local mapping transparency Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel

3. A Summary of Transparency Features IF THE SQL Statement Requires: Why Not Considered: No Yes? Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel

4. Fragment Locations Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel

5. To list all the employees with date-of-birth prior to January 1, 1940 SELECT * FROM EMPLOYEE WHERE EMP_DOB < ’01-JAN-1940’; • The DB supports Fragment Transparency • The DB supports Location Transparency • The DB supports Local Mapping Transparency SELECT * FROM E1 WHERE EMP_DOB < ’01-JAN-1940’ UNION SELECT * FROM E2 WHERE EMP_DOB < ’01-JAN-1940’; UNION SELECT * FROM E3 WHERE EMP_DOB < ’01-JAN-1940’; SELECT * FROM E1NODE NY WHERE EMP_DOB < ’01-JAN-1940’ UNION SELECT * FROM E2NODE ATL WHERE EMP_DOB < ’01-JAN-1940’; UNION SELECT * FROM E3NODE MIAWHERE EMP_DOB < ’01-JAN-1940’; Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel

6. 10.10 Transaction Transparency • Ensures database transactions will maintain distributed database’s integrity and consistency • Transaction transparency ensures that the transaction will be completed only if all database sites involved in the transaction complete their part of the transaction Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel

7. Distributed Requests and Distributed Transactions • Distributed transaction • Can update or request data from several different remote sites on a network • Remote request • Lets a single SQL statement access data to be processed by a single remote database processor • Remote transaction • Composed of several requests, Accesses data at a single remote site Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel

8. Distributed Requests and Distributed Transactions (continued) • Distributed transaction • Allows a transaction to reference several different (local or remote) DP sites • Distributed request • Lets a single SQL statement reference data located at several different local or remote DP sites Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel

9. A Remote Request Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel

10. A Remote Transaction Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel

11. A Distributed Transaction Each request can access only one remote site at a time Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel

12. A Distributed Request Each request can access data from several local or remote sites at a time Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel

13. Another Distributed Request Full fragmentation transparency support is provided by a DBMS that supports distributed requests Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel

14. Distributed Concurrency Control • Multi-site, multiple-process operations are much more likely to create data inconsistencies and deadlocked transactions than are single-site systems Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel

15. The Effect of a Premature COMMIT Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel

16. Two-Phase Commit Protocol • Distributed databases make it possible for a transaction to access data at several sites • Final COMMIT must not be issued until all sites have committed their parts of the transaction • Two-phase commit protocol requires each individual DP’s transaction log entry be written before the database fragment is actually updated Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel

17. Two-Phase Commit Protocol • Phase 1: Preparation • The coordinator sends a PREPARE TO COMMIT message to all subordinates • The subordinates receive the message, write the transaction log using the write-ahead protocol, and send an acknowledgement (YES or NO) message to the coordinator • The coordinator makes sure all nodes are ready to commit (acknowledged with YES), otherwise it aborts the transaction • If all nodes are YES (Prepared to COMMIT), the transaction goes to Phase 2. Otherwise, the coordinator broadcasts an ABORT message to all subordinates Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel

18. Two-Phase Commit Protocol • Phase 2: The Final COMMIT • The coordinator broadcasts a COMMIT message to all subordinates and waits for the replies • Each subordinate receives the COMMIT message, then updates the database using the DO protocol • The subordinates reply with a COMMITTED or NOT COMMITTED message to the coordinator • If any subordinate did not commit, the coordinator sends an ABORT message, thereby forcing them to UNDO all changes Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel

19. 10.11 Performance Transparency and Query Optimization • Objective of query optimization routine is to minimize total cost associated with the execution of a request • Costs associated with a request are a function of the: • Access time (I/O) cost • Communication cost • CPU time cost Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel

20. Performance Transparency and Query Optimization • Must provide distribution transparency as well as replicatransparency • Replica transparency: • DDBMS’s ability to hide the existence of multiple copies of data from the user • Query optimization algorithms are based on two principles: • The selection of the optimum execution order • The selection of sites to be accessed to minimize the communication costs Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel

21. Performance Transparency and Query Optimization • Query optimization techniques: • Manual or automatic • Static or dynamic • Static query optimization takes place at compilation time • Dynamic query optimization takes place at execution time • Statistically (dynamic or manual) based or rule-based algorithms Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel

22. 10.12 Distributed Database Design • Data fragmentation: • How to partition the database into fragments • Data replication: • Which fragments to replicate • Data allocation: • Where to locate those fragments and replicas Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel

23. Data Fragmentation • Breaks single object into two or more segments or fragments • Each fragment can be stored at any site over a computer network • Information about data fragmentation is stored in the distributed data catalog (DDC), from which it is accessed by the TP to process user requests Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel

24. Data Fragmentation Strategies • Horizontal fragmentation: • Division of a relation into subsets (fragments) of tuples (rows) • Vertical fragmentation: • Division of a relation into attribute (column) subsets • Mixed fragmentation: • Combination of horizontal and vertical strategies Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel

25. A Sample CUSTOMER Table Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel

26. Horizontal Fragmentation of the CUSTOMER Table by State Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel

27. Table Fragments in Three Locations Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel

28. Vertically Fragmented Table Contents Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel

29. Mixed Fragmentation of the CUSTOMER Table Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel

30. Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel

31. Data Replication • Storage of data copies at multiple sites served by a computer network • Fragment copies can be stored at several sites to serve specific information requirements • Can enhance data availability and response time • Can help to reduce communication and total query costs Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel

32. Data Replication mutual consistency rule: all copies of data fragments be identical Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel

33. The Replica Overhead Imposed on DDBMS • The processes that the DDBMS must perform to use the database replication • If the DB is fragmented, the DDBMS must decompose a query into sub-queries to access the appropriate fragment • If the DB is replicated, the DDBMS must decide which copy to access • The TP sends a data request to each selected DP for execution • The DP receives and executes each request and sends the data back to TP • The TP assembles the DP responses Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel

34. Replication Scenarios • Fullyreplicated database: • Stores multiple copies of each database fragment at multiple sites • Can be impractical due to amount of overhead • Partially replicated database: • Stores multiple copies of some database fragments at multiple sites • Most DDBMSs are able to handle the partially replicated database well • Unreplicated database: • Stores each database fragment at a single site • No duplicate database fragments • Factors influence the decision to use data replication • DB size, Usage frequency, Costs Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel

35. Data Allocation • Deciding where to locate data • Allocation strategies: • Centralized data allocation • Entire database is stored at one site • Partitioned data allocation • Database is divided into several disjointed parts (fragments) and stored at several sites • Replicated data allocation • Copies of one or more database fragments are stored at several sites • Data distribution over a computer network is achieved through data partition, data replication, or a combination of both Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel

36. 10.13 Client/Server vs. DDBMS • Client/Server architecture refers to the way in which computers interact to form a system • Features a user of resources, or a client, and a provider of resources, or a server • Can be used to implement a DBMS in which the client is the TP and the server is the DP Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel

37. Client/Server Advantages • Less expensive than alternate minicomputer or mainframe solutions • Allow end user to use microcomputer’s GUI, thereby improving functionality and simplicity • More people with PC skills than with mainframe skills in the job market • PC is well established in the workplace • Numerous data analysis and query tools exist to facilitate interaction with DBMSs available in the PC market • Considerable cost advantage to offloading applications development from the mainframe to powerful PCs Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel

38. Client/Server Disadvantages • Creates a more complex environment, in which different platforms (LANs, operating systems, and so on) are often difficult to manage • An increase in the number of users and processing sites often paves the way for security problems • Possible to spread data access to a much wider circle of users increases demand for people with broad knowledge of computers and software increases burden of training and cost of maintaining the environment Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel

39. 10.14 C. J. Date’s Twelve Commandments for Distributed Databases • Local site independence • Central site independence • Failure independence • Location transparency • Fragmentation transparency • Replication transparency • Distributed query processing • Distributed transaction processing • Hardware independence • Operating system independence • Network independence • Database independence Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel

40. Summary • Distributed database stores logically related data in two or more physically independent sites connected via a computer network • Database is divided into fragments • Distributed databases require distributed processing • Main components of a DDBMS are the transaction processor and the data processor Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel

41. Summary (continued) • Current database systems can be classified by extent to which they support processing and data distribution • DDBMS characteristics are best described as a set of transparencies • A transaction is formed by one or more database requests • A database can be replicated over several different sites on a computer network • Client/server architecture refers to the way in which two computers interact over a computer network to form a system Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel