PMIT-6102 Advanced Database Systems

PMIT-6102Advanced Database Systems By- JesminAkhter Assistant Professor, IIT, Jahangirnagar University

Class Test -02Solution It is not guaranteed that all the solutions are correct.

Tutorial Q • Briefly describe the correctness of Fragmentation. • What is the basic difference between Primary horizontal fragmentation and Derived horizontal fragmentation with an example. • Write an iterative algorithm that would generate a complete and minimal set of predicates Pr’ from a given a set of simple predicates Pr. • Show the reconstruction of Hybrid Fragmentation. • Describe Allocation Model. • Draw the Generic Layering Scheme for Distributed Query Processing.

Tutorial Q We assume that relations EMP and ASG are horizontally fragmented. Fragments ASG1, ASG2, EMP1, and EMP2 are stored at sites 1, 2, 3, and 4,respectively, and the result is expected at site 5. Two strategy are given bellow: Which strategy is better? Site 1 Site 2 Site 3 Site 4 Site 5 ASG1=ENO≤“E3”(ASG) EMP1=ENO≤“E3”(EMP) EMP2=ENO>“E3”(EMP) Result ASG2=ENO>“E3”(ASG) Site 5 Site 5 result = EMP1’EMP2’ result2=(EMP1EMP2)ENODUR>37(ASG1ASG1) EMP1’ EMP2’ ASG1 ASG2 EMP1 EMP2 Site 3 Site 4 EMP2’=EMP2 ENOASG2’ EMP1’=EMP1 ENOASG1’ Site 1 Site 2 Site 3 Site 4 (b) Strategy B ASG1’ ASG2’ Site 1 Site 2 ASG1’=DUR>37(ASG1) ASG2’=DUR>37(ASG2) (a) Strategy A Slide 4

Briefly describe the correctness rules of Fragmentation. Correctness rules of Fragmentation There are the following three rules during fragmentation, which, together, ensure that the database does not undergo semantic change during fragmentation. • Completeness • Decomposition of relation R into fragments R1, R2, ..., Rn is complete if and only if each data item in R can also be found in some Ri • This property, which is identical to the lossless decomposition property of normalization • it ensures that the data in a global relation are mapped into fragments without any loss Answer 01:

Correctness of Fragmentation • Reconstruction • If relation R is decomposed into fragments FR={R1, R2, ..., Rn}, then there should exist some relational operator ∇such that R = ∇ Ri , • The reconstructability of the relation from its fragments ensures that constraints defined on the data in the form of dependencies are preserved.

Correctness of Fragmentation • Disjointness • If relation R is horizontally decomposed into fragments FR={R1, R2, ..., Rn}, and data item djis in Rj, then dj should not be in any other fragment Rk (k ≠ j ). • This criterion ensures that the horizontal fragments are disjoint. • If relation R is vertically decomposed, its primary key attributes are typically repeated in all its fragments (for reconstruction). • Therefore, in case of vertical partitioning, disjointness is defined only on the non-primary key attributes of a relation.

What is the basic difference between Primary horizontal fragmentation and Derived horizontal fragmentation. Give an example both of them. Answer 02: • Primary horizontal fragmentation of a relation is performed using predicates that are defined on that relation. • Derived horizontal fragmentation is the partitioning of a relation results from predicates being defined on another relation.

Primary Horizontal Fragmentation • PROJ1 =  LOC=“Montreal” (PROJ) • PROJ2 =  LOC=“New York” (PROJ) • PROJ3 =  LOC=“Paris” (PROJ) Primary Horizontal Fragmentation of Relation PROJ

EMP1= EMP ⋉SKILL1 EMP2 = EMP ⋉SKILL2 where SKILL1 = SAL≤30000(SKILL) SKILL2 = SAL>30000(SKILL) EMP1 ENO ENAME TITLE E3 A. Lee Mech. Eng. E2 M. Smith Syst. Anal. E4 J. Miller Programmer E5 B. Casey Syst. Anal. E7 R. Davis Mech. Eng. E6 L. Chu Elect. Eng. E8 J. Jones Syst. Anal. Derived Horizontal Fragmentation EMP2 ENO ENAME TITLE E1 J. Doe Elect. Eng.

Write an iterative algorithm that would generate a complete and minimal set of predicates Pr’ from a given a set of simple predicates Pr. Answer 03: COM_MIN Algorithm Given: a relation R and a set of simple predicates Pr Output: a complete and minimal set of simple predicates Pr' for Pr Rule 1: a relation or fragment is partitioned into at least two parts which are accessed differently by at least one application.

COM_MIN Algorithm • Initialization : • find a pi Prsuch that pi partitions R according to Rule 1 • set Pr' = pi ; PrPr– {pi}; F {fi} • Iteratively add predicates to Pr' until it is complete • find a pj Prsuch that pj partitions some fk defined according to minterm predicate over Pr' according to Rule 1 • set Pr' = Pr'  {pj}; Pr Pr– {pj}; F F {fj} • if pk Pr' which is nonrelevant then Pr'Pr' – {pk} FF – {fk}

Show the reconstruction of Hybrid Fragmentation. • To reconstruct the original global relation in case of hybrid fragmentation, one starts at the leaves of the partitioning tree and moves upward by performing joins and unions. Answer 04: Reconstruction of Hybrid Fragmentation

General Form min(Total Cost) subject to response time constraint storage constraint processing constraint Decision Variable Describe Allocation Model.Answer 05:Allocation Model 1 if fragment Fi is stored at site Sj xij 0 otherwise

Total Cost Storage Cost (of fragment Fjat Sk) We choose a different approach in our model of the database allocation problem (DAP) and specify it as consisting of the processing cost (PC) and the transmission cost (TC). Thus the query processing cost (QPC) for application qi is: processing component + transmission component Allocation Model  query processing cost  (unit storage cost at Sk) (size of Fj) xjk all queries   cost of storing a fragment at a site all sites all fragments

Allocation Model • Query Processing Cost • Processing component PC, consists of three cost factors • the access cost (AC) + the integrity enforcement cost (IE) + the concurrency control cost (CC) • Access cost • The first two terms calculate the number of accesses of user query qi to fragment Fj. • We assume that the local costs of processing them are identical. • The summation gives the total number of accesses for all the fragments referenced by qi. Multiplication by LPCk gives the cost of this access at site Sk. • We again use xijto select only those cost values for the sites where fragments are stored. • Integrity enforcement and concurrency control costs • Can be similarly calculated   xij local processing cost at a site (no. of update accesses+ no. of read accesses)  all sites all fragments

Query Processing Cost Transmission component cost of processing updates + cost of processing retrievals In update queries it is necessary to inform all the sites where replicas exist, while in retrieval queries, it is sufficient to access only one of the copies. In addition, at the end of an update request, there is no data transmission back to the originating site other than a confirmation message, whereas the retrieval-only queries may result in significant data transmission. Cost of updates Retrieval Cost Allocation Model     update message cost  acknowledgment cost all sites all fragments all sites all fragments  min (cost of retrieval command  cost of sending back the result) all sites all fragments

Allocation Model • Constraints • Response Time execution time of query ≤ max. allowable response time for that query • Storage Constraint (for a site) • Processing constraint (for a site)  storage requirement of a fragment at that site  storage capacity at that site all fragments  processing load of a query at that site  processing capacity of that site all queries

Answer 06: Layers of Query Processing Calculus Query on Distributed Relations GLOBAL SCHEMA Query Decomposition Algebraic Query on Distributed Relations CONTROL SITE FRAGMENT SCHEMA Data Localization Fragment Query STATS ON FRAGMENTS Global Optimization Optimized Fragment Query with Communication Operations LOCAL SCHEMAS LOCAL SITES Local Optimization Optimized Local Queries Slide 19

Solution-07 Slide 20

Thank You Slide 21

PMIT-6102 Advanced Database Systems