1. Chapter 10 Distributed Database Management Systems Database Systems: Design, Implementation, and Management, Sixth Edition, Rob and Coronel

2. In this chapter, you will learn: • What a distributed database management system (DDBMS) is and what its components are • How database implementation is affected by different levels of data and process distribution • How transactions are managed in a distributed database environment • How database design is affected by the distributed database environment Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel

3. The Evolution of Distributed Database Management Systems • Distributed database management system (DDBMS) • Governs storage and processing of logically related data over interconnected computer systems in which both data and processing functions are distributed among several sites Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel

4. The Evolution of Distributed Database Management Systems (continued) • Centralized database required that corporate data be stored in a single central site • Dynamic business environment and centralized database’s shortcomings spawned a demand for applications based on data access from different sources at multiple locations Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel

5. Centralized Database Management System Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel

6. DDBMS Advantages • Data are located near “greatest demand” site • Faster data access • Faster data processing • Growth facilitation • Improved communications • Reduced operating costs • User-friendly interface • Less danger of a single-point failure • Processor independence Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel

7. DDBMS Disadvantages • Complexity of management and control • Security • Lack of standards • Increased storage requirements • Greater difficulty in managing the data environment • Increased training cost Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel

8. Distributed Processing Environment Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel

9. Distributed Database Environment Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel

10. Characteristics of Distributed Management Systems • Application interface • Validation • Transformation • Query optimization • Mapping • I/O interface • Formatting • Security • Backup and recovery • DB administration • Concurrency control • Transaction management Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel

11. Characteristics of Distributed Management Systems (continued) • Must perform all the functions of a centralized DBMS • Must handle all necessary functions imposed by the distribution of data and processing • Must perform these additional functions transparently to the end user Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel

12. A Fully Distributed Database Management System Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel

13. DDBMS Components • Must include (at least) the following components: • Computer workstations • Network hardware and software • Communications media • Transaction processor (or, application processor, or transaction manager) • Software component found in each computer that requests data • Data processor or data manager • Software component residing on each computer that stores and retrieves data located at the site • May be a centralized DBMS Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel

14. Distributed Database System Components Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel

15. Database Systems: Levels of Data and Process Distribution Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel

16. Single-Site Processing, Single-Site Data (SPSD) • All processing is done on single CPU or host computer (mainframe, midrange, or PC) • All data are stored on host computer’s local disk • Processing cannot be done on end user’s side of the system • Typical of most mainframe and midrange computer DBMSs • DBMS is located on the host computer, which is accessed by dumb terminals connected to it • Also typical of the first generation of single-user microcomputer databases Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel

17. Single-Site Processing, Single-Site Data (Centralized) Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel

18. Multiple-Site Processing, Single-Site Data (MPSD) • Multiple processes run on different computers sharing a single data repository • MPSD scenario requires a network file server running conventional applications that are accessed through a LAN • Many multi-user accounting applications, running under a personal computer network, fit such a description Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel

19. Multiple-Site Processing, Single-Site Data Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel

20. Multiple-Site Processing, Multiple-Site Data (MPMD) • Fully distributed database management system with support for multiple data processors and transaction processors at multiple sites • Classified as either homogeneous or heterogeneous • Homogeneous DDBMSs • Integrate only one type of centralized DBMS over a network Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel

21. Multiple-Site Processing, Multiple-Site Data (MPMD) (continued) • Heterogeneous DDBMSs • Integrate different types of centralized DBMSs over a network • Fully heterogeneous DDBMS • Support different DBMSs that may even support different data models (relational, hierarchical, or network) running under different computer systems, such as mainframes and microcomputers Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel

22. Heterogeneous Distributed Database Scenario Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel

23. Distributed Database Transparency Features • Allow end user to feel like database’s only user • Features include: • Distribution transparency • Transaction transparency • Failure transparency • Performance transparency • Heterogeneity transparency Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel

24. 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 • Location transparency • Local mapping transparency Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel

25. A Summary of Transparency Features Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel

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

27. Transaction Transparency • Ensures database transactions will maintain distributed database’s integrity and consistency Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel

28. 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 • Accesses data at a single remote site Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel

29. 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

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

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

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

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

34. Another Distributed Request Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel

35. Distributed Concurrency Control • Multisite, 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

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

37. 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

38. 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

39. Performance Transparency and Query Optimization (continued) • Must provide distribution transparency as well as replica transparency • Replica transparency: • DDBMS’s ability to hide the existence of multiple copies of data from the user • Query optimization techniques: • Manual or automatic • Static or dynamic • Statistically based or rule-based algorithms Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel

40. 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

41. 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

42. 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

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

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

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

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

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

48. 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

49. Table Contents After the Mixed Fragmentation Process Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel

50. Data Replication Database Systems: Design, Implementation, & Management, 6th Edition, Rob & Coronel