Properties of Data Replication :

1. Properties of Data Replication : • Replication can increase read performance • Replication can be used to integrate heterogeneous systems that uses different databases. • Replication can be used for both failure and disaster recovery • The costs of using replication is decreased update performance and the costs of managing consistency problems. (Relaxed ACID properties may be used in order to minimize inconsistency).

2. Cases where replication designs and extended transactions are recommended: • ERP systems • E-commerce • Electronic health records (Elektronisk patientjournal) • Logistics • Airline reservation systems • CSCW systemer • Distributed calender systems • Mobile integrated database applications • Supply chain management • Banking systems • Library systems

3. Overview of different types of replication designs: Suppose all replication designs have n physical copies of the same logical table. In the n-safe replication design, all n copies are consistent and up to date. In the quorum-safe design, a quorum of the n copies are consistent and up to date. In the 1-safe design, only one of the n copies is consistent and up to date. In the 0-safe design, none of the copies are consistent and up to date. The inconsistencies can be managed by using countermeasures.

4. Rules for using Data Replication in Distributed Databases with Relaxed ACID Properties • Use only replicated data when it is necessary for availability or economical reasons. • The 0-safe design with local commit is recommended when it is important to update in disconnected mode and it is possible to implement sufficient local countermeasures against the isolation anomalies. • The 0-safe design with deferred commit is recommended when it is important to update in disconnected mode and it is not possible to implement sufficient local countermeasures against the isolation anomalies. • The 1-safe designs are recommended in situations when real time update is not important in disconnected mode. Therefore, updates must wait until the primary copy location has committed the updates.Please notice that the basic 1-safe designs is the cheapest replication method.

5. Design Rules for Replicating Data in Distributed Databases with Relaxed ACID Properties • The quorum-safe design is not recommended as it is only used in DDBMS (Distributed Database Management Systems). • The n-safe design and especially the 2-safe design are only recommended in practice when the are implemented in hardware or managed by the operative system. DDBMSs (Distributed Database Management System) can also manage the n-safe designs but DDBMSs are not used in practice as they are too complex.

6. The Basic 1-safe Design: The 1-safe designs are recommended in situations when real time update is not important in disconnected mode. Therefore, updates may wait until the primary copy location has committed the updates.The basic 1-safe designs is the cheapest replication method.

7. Suppose the ERP system and B2B E-commerce system are heterogeneous. Which ERP tables would you recommend to replicate by using the Basic 1-safe design?

8. Replication Example: The basic 1-safe design is recommended when it does not make major problems if updates are delayed for hours or even days. In the “Danish medicine card” system, all hospitals and private physicians must transfer their medicine prescriptions to a central database by using the 0-safe design with local commit. What replication design would you recommend for the Medicine types? Medicine prescriptions Medicine types

9. In the basic 1-safe replication design, lost transaction may occur when the secondary location takes over after a primary copy failure. The basic 1-safe Design The 1-safe Design with commutative updates Why can lost transactions not occur in the 1-safe design with commutative updates?

10. The Basic 1-safe Design Example: The basic 1-safe design is recommended when it does not make major problems if updates are delayed for hours or even days. Example: Which of the 1-safe designs would you recommend for the Disease types table?Where would you recommend to store the back up of this table?

11. Entities with different versions of 0-safe design Which of the1-safe designswould you recommend?

12. The 0-safe design with local commit: The 0-safe design with local commit is recommended when it is important to update in disconnected mode and it is possible to implement sufficient local countermeasures against the isolation anomalies. Example.

13. The 0-safe design with deferred commit: The 0-safe design with deferred commit is recommended when it is important to update in disconnected mode and it is not possible to implement sufficient local countermeasures against the isolation anomalies. Example.

14. 0-safe Design Example: In the “Danish medicine card” system, all hospitals and private physicians must transfer their medicine prescriptions to a central database by using the 0-safe design with local commit. Which type of 0-safe replication design would you recommend for the Medicine prescriptions? Medicine prescriptions Medicine types

15. Where would you recommend the different types of 0-safe design? Entities with the1-safe design

16. The 0-safe design with local commit is recommended when it is important to operate in disconnected mode, and it is possible to implement sufficient local countermeasures against the isolation anomalies. • The 0-safe design with deferred commit is recommended when it is important to operate in disconnected mode, and when it is not possible to implement sufficient local countermeasures against the isolation anomalies. Electronic Health Records Deffered commit Local commit

17. Suppose the ERP system and B2B E-commerce system are heterogeneous. Which tables would you recommend to replicate by using one the 0-safe designs?

18. The basic N-safe Design The basic 1-safe Design The basic 0-safe Design

19. Evaluation of replication designs

20. Electronic Health Records (EHR)

21. Overview of the most important EHR integr. architectures

29. Which EHR integration architecture would you recommend and why?

30. Overview of the most important EHR integr. architectures

31. Concept definitions used in logistic exercise: Pallet = wooden skeleton where packages may be stored in such a way that they all can be moved by a truck. Collie = alle the packages that are stored on a pallet(palle). Leg = Route or subroute where the transportation does not have stops

32. ER-diagram of a logistics management system Transport Orders Orderlines Customers Route-leg hierarchy from to from Packages, Collies and Containers Routes and legs Locations to Transport operator Damagerelationship Physical containers Package- Collie hierarchy Container-routes relationships of order 3 Scheduled routes and legs Transport medias like ships, airplanes, and trucks. How should the transport orders and sub-orders to sub-contractors be replicated in order to optimize the transports?

33. Global ER-diagram of integrated logistics management Transport Orders Orderlines Customers Route-leg hierarchy from to from Packages, Collies and Containers Routes and legs Locations to Transport operator Damagerelationship Physical containers Package- Collie hierarchy Container-routes relationships of order 3 Scheduled routes and legs Transport medias like ships, airplanes, and trucks. Describe the local databases in the central location of the transport company, the locations of the sub-contractors, and the mobile locations of the Transport medias. Design a workflow with focus on the integration of the local database locations.

34. Petri net:Work flowof a global E-commerce transactions where the stocks are in the locations of the different suppliers. OR split

35. Sub- Petri net of activity 2 AND split AND join OR split May the suppliers be transport sub-contractors?

36. Global ER-diagram of integrated logistics management Transport Orders Orderlines Customers Route-leg hierarchy from to from Packages, Collies and Containers Routes and legs Locations to Transport operator Damagerelationship Physical containers Package- Collie hierarchy Container-routes relationships of order 3 Scheduled routes and legs Transport medias like ships, airplanes, and trucks. How would you recommend to integrate and later merge the shipping companies Maersk and P&O Nedlloyd if Maersk had used the logistics architecture above?

37. Horizontal fragmentation: Fragments = The non-redundant and non-overlapping parts of a global distributed table. Fragments may be allocated in many different locations. Fragment 1 Fragment 2

38. Vertikal Fragmentation: Example:In an employ table some attribytes/fragments may be confidential and stored in a secure location.

39. Fragmentation rules: Horizintal fragmentation supports geografical distribution where different locations use different rows. Vertical fragmentation supports distribution by function where different functions use different attributes.

40. Mixed Fragmentation: Horizontal fragmentation on a vertical fragmentation. Horizontal fragmentation on a vertical fragmentation.

41. Entities with different versions of 0-safe design Describe your recommendations for distributed table fragmentation? Entities with 1-safe design

42. A distributed ERP system = A set of local ERP systems integrated in such a way that each local system can use the resources/stocks managed by the other local ERP systems. Would you recommend the distributed ERP architecture for a mobile salesman?

43. Which tables in Distributed ERP system would you recommend to replicate? • Use only replicated data when it is necessary or convenient for economical reasons. • The 0-safe design with local commit is recommended when it is important to update in disconnected mode and it is possible to implement sufficient local countermeasures against the isolation anomalies. • The 0-safe design with deferred commit is recommended when it is important to update in disconnected mode and it is not possible to implement sufficient local countermeasures against the isolation anomalies. • The 1-safe designs are recommended in situations when real time update is not important in disconnected mode. Therefore, updates may wait until the primary copy location has committed the updates.Please notice that the basic 1-safe designs is the cheapest replication method.

44. Should a traveling salesman have the primary copy property of the customers that are going to be visited?

45. Allokation of fragments: Allocation is the physical placement of fragments in different locations. Allocated fragments may be replicated. Exercise:How are the tables fragmented and allocated in a distributed ERP system? Is it possible to optimize the Product and Product stock tables by integrating them to a single table locally?

46. Replication in a distributed ERP system: Should all attributes in a table have the same replication design? (Analyze the Orderlines or Products ).

47. Exercise: Describe and design the local databases for a distributed brewery with many different production, sale and depot locations. Can the earlier described distributed ERP system be used?

48. Design a Distributed Airline Database Design an integrated distributed database that integrate the databases of different airline companies in a way that optimize performance, availability and consistency of a common distributed airline system with local databases in the airline companies, airports, and “sale offices” at e.g. travel agents, hotels and e-commerce servers.

49. Exercise: Design the workflow and an integrated distributed database that integrate the databases of different airline companies and hotel chains in a way that optimize performance, availability and consistency of a common distributed system with local databases in the airline companies, hotel chains, airports, car rental companies, and “sale offices” at e.g. travel agents, hotels and e-commerce servers. Describe the workflow of the integrated e-commerce system.

50. End of session Thank you !!!