QUERY DECOMPOSITION LOCALIZATION OF DISTRIBUTED DATA Instructor Dr

QUERY DECOMPOSITION & LOCALIZATION OF DISTRIBUTED DATA Instructor- Dr. Morris M. Liaw Team Information-Group 3 #4 Krishna Sri Niharika Siruvuri #6 Snehith Kumar Dendi #13 Mithra Palwai #23 Akbar Yamin Mughal #25 Bhoomi Gajjar

OUTLINE • Rewriting • Transformation • Equivalence Rules • Localization of Distributed Data • Rewritten Operator Tree • Reduction for Primary Horizontal Fragmentation • Reduction with Selection

REWRITING • Rewriting is the last step of Query Decomposition. • Rewriting is the process of rewriting the query in relational algebra. • Relational Algebra query is graphically represented by Operator Tree • In operator tree leaf node is stored in the database and non leaf node is intermediate relation produced by relational algebra operator • Answer to the query lies in the sequence of operations directed from leaves to the root.

TRANSFORMATION Transformation of tuple relational calculus query into operator tree Different leaf created for different tuple variable Result attributes found in SELECT clause in SQL Leaves are immediately available in FROM clause SQL WHERE clause translated to appropriate sequence of relational operations like select, join etc. , Root node is created as a project operation involving result attributes Sequence can be given directly by the order of appearance of the predicates and operators.

Contd. , Example Find the names of employees other than J. Doe who worked on the CAD/CAM project for either one or two years” whose SQL expression is SELECT ENAME FROM PROJ, ASG, EMP WHERE ASG. ENO = EMP. ENO AND ASG. PNO = PROJ. PNO AND ENAME != "J. Doe" AND PROJ. PNAME = "CAD/CAM" AND (DUR = 12 OR DUR = 24) Operator tree

EQUIVALENCE RULES There are 6 Transformation Equivalence rules that are concerned with basic relational algebra operators 1. Commutativity of binary operators.

Contd. , 2. Associativity of binary operators The Cartesian product and the join are associative operators 3. dempotence of unary operators

Contd. , 4. Commuting selection with projection Selection and projection on the same relation can be commuted as follows 5. Commuting selection with binary operators. Selection and union can be commuted if R and T are union compatible

6. Commuting projection with binary operators. Projection and Cartesian product can be commuted Projection and Difference can be commuted similarly

LOCALIZATION OF DISTRIBUTED DATA • Role of localization layer Global techniques such as decomposing and restructuring queries apply to both centralized and distributed DBMSs which do not take distribution of data into account. Localization layer translates an algebraic query on global relations into an algebraic query expressed on physical fragments. Fragmentation is defined through fragmentation rules, which can be expressed as relational queries. A global relation can be reconstructed by applying the reconstruction (or reverse fragmentation) rules and deriving a relational algebra program whose operands are the fragments This process is known as a localization program. This approach is inefficient because important restructurings and simplifications of the localized query can still be made.

REWRITTEN OPERATOR TREE

Reduction for Primary Horizontal Fragmentation Relation EMP(ENO, ENAME, TITLE) can be split into three horizontal fragments EMP 1, EMP 2, and EMP 3, defined as follows: According to localization program for an horizontally fragmented relation is the union of the fragments, Thus the localized form of any query specified on EMP is obtained by replacing it by the following

Reduction with Selections on fragments that have a qualification contradicting the qualification of the fragmentation rule generate empty relations Example Query