Distributed DBMSs Concepts and Design Chapter 22 in

Distributed DBMSs – Concepts and Design Chapter 22 in Textbook

Overview Concepts. What is a distributed DBMS? Distributed Processing. Homogeneous vs. Heterogeneous. Functions of a DDBMS. Components of a DDBMS. Advantages and Disadvantages. DDBMS Design. Fragmentation. Replication. Allocation. DDBMS Transparencies. Date’s 12 Rules for a DDBMS. 2

Concepts Centralized DBMS systems with a single logical database located at one site under the control of a single DBMS. Distributed DBs logically interrelated collection of shared data physically distributed over a computer network. Applications can be classified into: 3 Local applications. Global applications.

Distributed DBMS The software system that: manages the distributed DBs. makes distribution transparent to users. allows users to access data on their own site as well as remote sites. Transparent distribution is the fundamental principle of DDBMS. 4

Characteristics of DDBMS • A collection of logically related shared data. • The data is split into a number of fragments. • Fragments may be replicated. • Fragments/replicas are allocated to sites. • The sites are linked by a communications networks. • The data at each site is under the control of a DBMS. • The DBMS at each site can handle local applications. • Each DBMS participates in at least one global application. 5

Distributed DBMS Topology Site 1 Site 4 Site 2 Computer Network Site 3 Data itself is distributed and access to it can be local or remote. 6

Distributed Processing Site 1 Site 4 Site 2 Computer Network Site 3 Data itself is centralized but access to it can be local or remote. 7

Homogeneous vs. Heterogeneous DDBMS Homogenous system: all sites use the same DBMS product. Heterogeneous system: sites may run different DBMS products & data model. 8 Possible differences between data in different DBS: • Data type difference. • Value difference. • Semantic difference.

Functions of a DDBMS • Provide access to remote sites and allow transfer of queries & data among the network’s site. • Store data distribution details. • Distributed data processing. • Security control. • Concurrency control. • Recovery services. 9

Components of a DDBMS Global system catalog Site 1 GSC DDBMS DC DDBMS LDBMS Computer Network GSC DC DB Data communication component 10 Site 3

Advantages of DDBMS • Reflects organizational structure. • Improve sharability & local autonomy. • Improved availability. • Improved reliability. • Improved performance. 11

Disadvantages of DDBMS • Complexity. • Cost. • Security. • Integrity control more difficult. • Lack of standards. • Lack of experience. • DB design more complex. 12

Distributed Relational DB Design We have a group of tables and we want to distribute them between a group of sites. Consists of 3 major steps: 1. Fragmentation divide a relation into a number of sub-relations (fragments). (Horizontal & vertical). 2. Replication make a copy of a fragment. 3. Allocation decide where (which site) each of the fragments and replicas are to be stored. 13

Distributed Relational DB Design When we fragment, replicate and allocate, we try to achieve: • Locality of reference. • Improved reliability and availability. • Good performance. • Balanced storage capacities and costs. • Minimal communication costs. 14

Rules of Fragmentation Completeness: Nothing (rows or columns) gets lost while we fragment. Reconstruction: We can get back the original table after we fragmented it. Dis-jointness: No row or column appears in 2 fragments (there is 1 exception). 15

Types of Fragmentation Horizontal fragmentation Vertical fragmentation Mixed fragmentation 16

Original Property. For. Rent Table Property. No PA 14 PG 16 PG 21 PG 36 PL 94 17 Street City Post. Code Type Rooms Rent Owner. No Staff. No Branch. No 16 Holhead Aberdeen AB 7 5 SU 6 Lawrence Glasgow G 11 9 QX 5 Novar Dr Glasgow G 12 9 AX 18 Dale Rd Glasgow G 12 2 Manor Rd Glasgow G 32 4 QX 6 Argy 11 St London NW 2 House Flat 6 3 4 5 3 4 650 350 450 600 375 400 CO 46 CO 40 CO 93 CO 87 SA 9 SG 14 SG 37 SL 41 B 007 B 003 B 005

Horizontal Fragmentation Based on type of property. P 1: Type=‘House’ (Property. For. Rent) P 2: Type=‘Flat’ (Property. For. Rent) Fragment P 1 Property. No PA 14 PG 21 Street City Post. Code Type Rooms Rent Owner. No Staff. No Branch. No 16 Holhead Aberdeen AB 7 5 SU House 18 Dale Rd Glasgow G 12 House 6 5 650 600 CO 46 CO 87 SA 9 SG 37 Fragment P 2 Branch. No Property. No Street PL 94 PG 36 PG 16 6 Argy 11 St 6 Lawrence 2 Manor Rd 5 Novar Dr 18 B 007 B 003 City Post. Code Type Rooms London NW 2 Glasgow G 11 9 QX Glasgow G 32 4 QX Glasgow G 12 9 AX Flat 4 3 3 4 Rent Owner. No Staff. No 400 350 375 450 CO 87 CO 40 CO 93 SL 41 SG 14 SG 37 SG 14 B 005 B 003

Original Staff Table Staff. No Position sex DOB Salary FName SL 21 Manager M 1 Oct 93 SG 37 Assistant F 10 Nov 60 12000 Ann SG 14 Supervisor M 24 Mar 58 18000 David Assistant F 3 Jun 40 24000 Susan SG 5 19 30000 John LName Branch. No White B 005 Beech B 003 Ford B 003 Brand B 007

Vertical Fragmentation S 1: staffno, Position, sex, DOB, Salary(STAFF) S 2: staffno, fname, lname, Branch. No(STAFF) Fragment S 1 Staff. No Position Fragment S 2 sex DOB Salary Staff. No FName LName Branch. No SL 21 Manager M 1 Oct 93 30000 SL 21 John White B 005 SG 37 Assistant F 10 Nov 60 12000 SG 37 Ann Beech B 003 SG 14 Supervisor M 24 Mar 58 18000 Assistant F 3 Jun 40 24000 SG 14 David Ford B 003 SG 5 Susan Brand B 007 SG 5 20

Mixed Fragmentation – Vertical then Horizontal Fragment S 2. 1 S 1: staffno, Position, sex, DOB, Salary(STAFF) S 2: staffoo, fname, lname, Branch. No(STAFF) Staff. No S 2. 1: Branch. No=‘B 005’ (S 2) S 2. 2: Branch. No=‘B 003’ (S 2) S 2. 3: Branch. No=‘B 007’ (S 2) Fragment S 2. 2 Staff. No Fragment S 1 Staff. No Position sex DOB Salary 30000 SL 21 Manager M 1 Oct 93 SG 37 Assistant F 10 Nov 60 12000 SG 14 Supervisor M 24 Mar 58 18000 Assistant F 3 Jun 40 24000 SG 5 21 SL 21 SG 37 SG 14 FName John LName White Branch. No B 005 FName LName Branch. No Ann David Beech Ford B 003 Branch. No Fragment S 2. 3 Staff. No FName LName SG 5 Susan Brand B 007

Derived Horizontal Fragmentation is the horizontal fragmentation of a table (child), T 1, because we horizontally fragmented another related table (parent), T 2. It is not explicitly specified in design but implied from fragmentation of T 2. T 1 (child) has a foreign key that belongs to T 2 (parent). Relationship between T 1 and T 2 either 1 -to-1 or Many-to 1. Use Semi-join operation: 22

Derived Horizontal Fragmentation Staff. No Position DOB Salary FName SL 21 Manager M 1 Oct 93 SG 37 Assistant F 10 Nov 60 12000 Ann SG 14 Supervisor M 24 Mar 58 18000 David Assistant F 3 Jun 40 24000 Susan SG 5 sex 30000 John LName Branch. No White B 005 Beech B 003 Ford B 003 Brand B 007 You were required by the design to horizontally fragment Staff table. S 1: Branch. No=‘B 003’ (Staff) S 2: Branch. No=‘B 005’ (Staff) S 3: Branch. No=‘B 007’ (Staff) 23

Derived Horizontal Fragmentation Fragment S 1 Staff. No Position sex DOB Salary FName F 10 Nov 60 12000 Ann SG 37 Assistant SG 14 Supervisor M 24 Mar 58 18000 David LName Branch. No Beech B 003 Ford B 003 Fragment S 2 Staff. No Position SL 21 Manager sex DOB M 1 Oct 93 LName Branch. No 30000 John White B 005 Salary FName LName 24000 Susan Brand Salary FName Fragment S 3 Staff. No Position SG 5 24 Assistant sex DOB F 3 Jun 40 Branch. No B 007

Derived Horizontal Fragmentation After we fragmented Staff, we found out that there is a table related to it, Property. For. Rent. Staff 1 handles N Property. For. Rent Because Staff is now fragmented, it makes sense to fragment Property. For. Rent too. S 1: Branch. No=‘B 003’ (Staff) S 2: Branch. No=‘B 005’ (Staff) S 3: Branch. No=‘B 007’ (Staff) 25 Pi: Property. For. Rent staff. No Si

Original Property. For. Rent Table Property. No PA 14 PG 16 PG 21 PG 36 PL 94 26 Street City Post. Code Type Rooms Rent Owner. No Staff. No Branch. No 16 Holhead Aberdeen AB 7 5 SU 6 Lawrence Glasgow G 11 9 QX 5 Novar Dr Glasgow G 12 9 AX 18 Dale Rd Glasgow G 12 2 Manor Rd Glasgow G 32 4 QX 6 Argy 11 St London NW 2 House Flat 6 3 4 5 3 4 650 350 450 600 375 400 CO 46 CO 40 CO 93 CO 87 SA 9 SG 14 SG 37 SL 41 B 007 B 003 B 005

Derived Horizontal Fragmentation Fragment P 1 Property. No PG 4 PG 21 PG 36 PG 16 Street City 6 Lawrence Glasgow 18 Dale Rd Glasgow 2 Manor Rd Glasgow 5 Novar Dr Glasgow Post. Code Type Rooms Rent Owner. No Staff. No Branch. No G 11 9 QX G 12 G 32 4 QX G 12 9 AX Flat House Flat 3 5 3 4 350 600 375 450 CO 40 CO 87 CO 93 SG 14 SG 37 SG 14 B 003 Fragment P 2 Property. No PL 94 Street City Post. Code Type Rooms 6 Argy 11 St London NW 2 Flat 4 Rent Owner. No Staff. No Branch. No 400 CO 87 SL 41 B 005 Fragment P 3 Property. No PA 14 27 Street City Post. Code Type Rooms 16 Holhead Aberdeen AB 7 5 SU House 6 Rent Owner. No Staff. No Branch. No 650 CO 46 SA 9 B 007

Transparencies in a DDBMS 4 main transparencies: Distribution Transparency. 1. a. b. c. d. e. 2. 3. 4. 28 Fragmnetation. Location. Replication. Local Mapping. Naming. Transaction Transparency. Performance Transparency. DBMS Transparency.

1. Distribution Transparency Allows the user to perceive the DB as a single, logical entity. Types: a. b. c. d. e. Fragmentation: the user does not need to know the data is fragmented. Location: the user does not need to know the location of fragments. Replication: the user does not need to know the fragments are replicated. Local Mapping: the user specifies the fragment and its location. Naming: DDBMS makes sure every item name is unique. Consider the distribution of the STAFF relation: S 1: staffno, Position, sex, DOB, Salary(STAFF) S 2: staffno, fname, lname, Branch. No (STAFF) S 21: Branch. No=‘B 003’ (S 2) S 22: Branch. No=‘B 005’ (S 2) S 22: Branch. No=‘B 007’ (S 2) 29

a. Fragmentation Transparency § § § Highest level of distribution transparency. The user does not need to know that the data is fragmented. User treats DDB like a centralized DB. The database access are based on the global schema. Fragmentation of the data can be changed without impacting the user. Example: SELECT Fname, Lname FROM Staff WHERE position = ‘Manager’; 30

b. Location Transparency § The middle level of distribution transparency. § The user must know that the data is fragmented but still does not need to know the location of the data. § Data location can be changed without impact on the user. Example: SELECT Fname, Lname FROM S 21 WHERE staff. No IN (SELECT staff. No FROM S 1 WHERE position=‘Manager’) UNION SELECT Fname, Lname FROM S 22 WHERE staff. No IN (SELECT staff. No FROM S 1 WHERE position=‘Manager’) UNION SELECT Fname, Lname FROM S 23 WHERE staff. No IN (SELECT staff. No FROM S 1 WHERE position=‘Manager’) 31

c. Replication Transparency User unaware of replication and location but knows that data is fragmented. On the same level with location transparency. 32

d. Local Mapping Transparency § The lowest level of distribution transparency. § The user knows that the data is fragmented and the location of the data. Example: SELECT Fname, Lname FROM S 21 AT SITE 3 WHERE staff. No IN (SELECT staff. No FROM S 1 AT SITE 5 WHERE position=‘Manager’) UNION SELECT Fname, Lname FROM S 22 AT SITE 5 WHERE staff. No IN (SELECT staff. No FROM S 1 AT SITE 5 WHERE position=‘Manager’) UNION SELECT Fname, Lname FROM S 23 AT SITE 7 WHERE staff. No IN (SELECT staff. No FROM S 1 AT SITE 5 WHERE position=‘Manager’) 33

e. Naming Transparency § § Each item in distributed database must have a unique name. DDBMS must ensure that no two sites violate that. Solutions § Create a central name server. § § § Prefix an object with the identifier of the site. § 34 Bottleneck. against local autonomy. loss of distribution transparency.

2. Transaction Transparency All transactions must ensure the consistency and integrity of the DDB. Each transaction that needs to access data in multiple sites is divided into multiple sub-transactions. Even if transaction is split, atomicity has to be maintained. 35

3. Performance Transparency DDBMS performs as if it were a centralized DBMS. Should not suffer because it is distributed (network communication cost). When a site issues a query, the system must figure out the fastest way of executing it. Distributed Query Processor (DQP) must figure out: 36 Which fragment to access. Which copy of fragment to access (if replication is used). Where are the fragments.

3. Performance Transparency Consider the following distributed DB: Property(Property. No, city) 10, 000 records in London Client(Client. No, max. Price) 100, 000 records in Glasgow Viewing(Propert. No, Client. No) 1, 000 records in London site wants to list properties in Aberdeen that have been viewed by clients who have a maximum price limit greater than 200, 000. SELECT FROM p. property. No Property P INNER JOIN (Client c INNER JOIN Viewing v ON c. client. No = v. client. No) ON WHERE p. property. No = v. property. No p. city = ‘Aberdeen’ AND c. maxprice > 200000; 37

3. Performance Transparency After the query is issued, DDBMS must determine the most cost-effective strategy to execute the query. Strategies: Move Client table to London and process query there. Move Property and Viewing relation to Glasgow and process query there then return result. Join Property and Viewing at London, project only property number and client number and move result to Glasgow to join with clients with salary > 200, 000 then return results. Select clients at Glasgow with salary > 200000, move them to London and join with viewing and Aberdeen property. 1. 2. 3. 4. 38

4. DBMS Transparency Hides the fact that different sites have different local DBMSs. Heterogeneous DDBMSs. 39

Date’s 12 Rules for a DDBMS 1. Local autonomy. 2. No reliance on a central site. 3. Continuous operation. 4. Location independence. 5. Fragmentation independence. 6. Replication independence. 7. Distributed query processing. 8. Distributed transaction processing. 9. Hardware independence. 10. Operating system independence. 11. Network independence. 12. Database independence. 40