1st International Workshop on Bidirectional Transformations - Bx 2012 Tallin, March 25, 2012

1. 1st International Workshop on Bidirectional Transformations - Bx 2012Tallin, March 25, 2012 Bidirectional Transformations in Database EngineeringJean-Luc Hainaut, Anthony Cleve

2. Objectives of the conference short practical intuitive introduction to the (x) transformational paradigm, applied to the DB domain. Reference Based on: Hainaut, J-L., Cleve, A., Transformational Approach to Database Engineering and Evolution, tutorial, Dagstuhl seminar 11031 on "Bx", January 2011 Hainaut, J-L, The Transformational Approach to Database Engineering, in Lämmel, R., Saraiva, J., Visser, V., (Eds), Generative and Transformational Techniques in Software Engineering, pp. 95-143, LNCS 4143, Springer, 2006)

3. 1. Introduction 2. Transformational engineering 3. Modeling data structures 4. Schema transformations 5. Bidirectional transformations 6. Typology of practical elementary transformations 7. Typology of practical complex transformations 8. Transformational modeling of database engineering processes 9. Conclusions and perspectives (A. Schema transformations in CASE tools) Contents

4. 1. Introduction

5. Goal: to introduce the conference(what else would you have expected?)

6. "Bidirectional transformations (bx) are a mechanism for maintaining the consistency of at least two related sources of information." "Such sources can be databases, software models, documents, graphs, and trees." 1. Introduction • bx appear in many aspects of the data & DB realm: • Model-Driven Software/DB Development: to compute and synchronize views of software/DB models. • Relational Databases: to construct updatable views. • Data Transformation, Integration, and Exchange: to map data across paradigms, merge it from multiple sources, and exchange it between sources. • Data Synchronizers: to bridge the gap between replicas in different formats. • Serializers: to mediate between external data (binary or sequential data representations on the wire or the le system) and structured objects in memory. source: GRACE Report, 2008

7.  • Model-Driven DatabaseEngineering: to compute and synchronize views of databaseschemas. 1. Introduction • Focus of this conference: • Model-Driven SoftwareDevelopment: to compute and synchronize views of softwaremodels.

8. 1. Introduction • DB Engineering = { domain analysis, • logical design, • physical design, • view derivation, • optimisation, • code generation, • reverse engineering, • maintenance, • evolution, • migration, • integration, • etc.}

9. conceptual schema logical schema physical schema DDL code 1. Introduction • Two directions of synchronization: • Vertical synchronization: in the design abstraction artifact hierarchy , how to ensure that there is no information loss? • horizontal synchronization: when an artifact changes, how to resynchronize the other artifacts?

10. conceptual schema conceptual schema' Client program Client program' logical schema logical schema' physical schema physical schema' DDL code DDL code' data' data 1. Introduction • Two directions of synchronization: • Vertical synchronization: in the design abstraction artifact hierarchy, how to ensure that there is no information loss? • horizontal synchronization: when an artifact changes, how to resynchronize the other artifacts?

11. 2. Transformational engineering

12. Goal: to show a practical application of schema transformationsorhow to motivate participants at 9 o'clock, summer time?

13. through translation rules through transformations 2. Transformational engineering How to derive a target schema from a source schema?

14. Rule-based engineering the target specification is produced following a set of translation rules. Transformation-based engineering the target specification is produced by application of a chain of substitution operators to the source specifications. 2. Transformational engineering Rule-basedvsTransformation-based engineering

15. Rule-based viewof Database Engineering Example: producing a relational schema from a conceptual schema 2. Transformational engineering  Physical schema (MS Access) Conceptual schema (ER)

16. Rule-based viewof Database Engineering Natural procedure: through translation rules 2. Transformational engineering

17. Rule-based viewof Database Engineering 2. Transformational engineering • OK, but what if: •  attribute A is at level 2, 3, …? •  attribute A is not atomic? •  relationship type R is many-to-many, or one-to-one, or N-ary? •  etc. •  Combinatorial explosion and complexity of the set of rules.

18. Transformation-based viewof Database Engineering Transforming the multivalued attribute Author 2. Transformational engineering 

19. Transformation-based viewof Database Engineering Transforming the many-to-many relationship type write 2. Transformational engineering 

20. Transformation-based viewof Database Engineering Transforming the one-to-many relationship type aw (and the others) 2. Transformational engineering 

21. Transformation-based viewof Database Engineering Coding (generally simple; rule-based or transformational) 2. Transformational engineering  No more than 5 WRITE rows per BOOK row.

22. 2. Transformational engineering • Transformation-based viewof Database Engineering • What if the attribute is multivalued, compound and comprises other multivalued components ? rule? 

23. Transformation-based viewof Database Engineering 2. Transformational engineering  Note: slightly different variant of the transformation of an attribute into an entity type

24. Transformation-based viewof Database Engineering 2. Transformational engineering  

25. Transformation-based viewof Database Engineering 2. Transformational engineering   

26. Transformation-based viewof Database Engineering Observations no new operators iterative application of known operators compositional property of transformations (the composition of two transformations still is a transformation) no combinatorial explosion, just the right (small) set of operators need for meta-rules for applying the operators (a transformation plan) 2. Transformational engineering

27. What now? 2. Transformational engineering

28. Questions We need to represent schemas in a great variety of models (GER = Generic ER model) What is a transformation and how to specify it? Does a transformation preserve the information contents of a schema? Let us be more concrete: what about PRACTICAL transformations? How do transformations help in REAL database engineering processes? 1. Introduction

29. 3. Modeling data structures

30. Goal: to define a common formalism to specify data structures in different data modelsorhow to tidy the data model Babel tower?

31. Dealing with multiple models A typical organization uses N different data models. E.g., it commonly uses DB2 databases, also uses a legacy IDMS database, writes its conceptual schemas in the ER model, quite often transfers data between databases, exchanges data with its environment, standardizes on XML format, plans to migrate some databases to other platforms, prepares the development of a datawarehouse, study the feasibility to merge several departments (and their information systems), etc. 3. Modeling Data Structures

32. Dealing with multiple models DB2 DB2 3. Modeling Data Structures organization conceptual schema application program design data warehouse operational data IDMS Oracle Oracle migrate ETL extract & export XML import environment XML

33. Dealing with multiple models Considering all the inter-model and intra-model conversions, the organization requires N x N different mappings (= 16). Srel>er Srel>rel Ser>er Srer>rel Relational Model ER Model Srel>xml Ser>cod Srel>cod Ser>xml Scod>rel Sxml>er Sxml>rel Scod>er CODASYL Model XML Model Scod>xml Sxml>xml Scod>cod Sxml>cod 3. Modeling Data Structures

34. Dealing with multiple models The usual answer: introducing a pivot model. Considering all the inter-model and intra-model conversions, the organization requires 2 x N + 1 different mappings (= 9). Ser>p Srel>p Sp>er Sp>p Sp>rel Relational Model ER Model Sp>xml Sp>cod Pivot Model Sxml>p Scod>p CODASYL Model XML Model 3. Modeling Data Structures

35. Dealing with multiple models Example: relational logical design. complex Sp>p ER Model Pivot Model Relational Model Ser>p Sp>rel very simple very simple 3. Modeling Data Structures Logical design conceptual schema logical schema

36. GER: the Generic Entity-Relationship model A large spectrum data structure model Encompasses several paradigms: ER, UML, SQL, CODASYL, IMS, file structures, XML, etc. Encompasses several levels of abstraction: conceptual, logical, physical, external Chosen as the pivot model in this tutorial 3. Modeling Data Structures Pivot Model GER Model 

37. GER: the Generic Entity-Relationship model Conceptual schema fragment (1) entity type attribute Is-a all-attribute ID relationship type role (with cardinality) hybrid ID 3. Modeling Data Structures

38. GER: the Generic Entity-Relationship model Conceptual schema fragment (2) 3. Modeling Data Structures multivalued attribute optional attribute compound attribute N-ary relationship type

39. GER: the Generic Entity-Relationship model Logical schema fragment record set / table array multivalued field foreign key 3. Modeling Data Structures

40. GER: the Generic Entity-Relationship model Physical schema fragment: RDB unique index index storage space 3. Modeling Data Structures

41. 4. Schema transformations

42. Goal: to define more precisely the nature of schema transformationoractually, what is a transformation?

43. A transformation T replaces a construct C in a schema S1 with another construct C', leading to schema S2 T schemas S2 S1 C C' 4. Schema transformations

44. If the schema describes actual data, the transformation should also tell how to convert the data (t) ... 4. Schema transformations T schemas S2 S1 C C' t data c' c

45. A transformation S is defined by two mappings T and t S= <T,t> T: structural mapping = syntax of S t: instance mapping = semantics of S T C C' = T(C) inst_of inst_of t c c' = t(c) 4. Schema transformations

46. Mapping T can be specified with two predicates: P: minimal pre-condition Q: maximal post-condition S= <T,t> = <P,Q,t> 4. Schema transformations

47. Expressing structural predicates through any logic-based language • entity-type(E) there exists an entity type with name E 4. Schema transformations relational (more concise, a name denotes an object) object-based (more general, a name is a property of an object) • entity-type(e) there exists an entity type denoted by e • name(e,E) the name of e is E must allow specification AND reasoning (e.g., DL)

48. Expressing structural predicates intuitive example 4. Schema transformations • entity-type(E) there exists an entity type with name E • attribute(O,A,m,M,T) object (with name) O has an attribute with name A, cardinality m-M and type T • id(O,Cp) object (with name) O has an identifier comprising components Cp • rel-type(R) there exists a rel-type with name R • role(R,r,E,m,M) rel-type R has a role with name r, played by E, with cardinality m-M

49. Specifying an entity type: entity-type(CUSTOMER)  attribute(CUSTOMER,Cust#,1,1,integer)  attribute(CUSTOMER,Name,1,1,string)  attribute(CUSTOMER,Phone,0,5,string)  id(CUSTOMER,{Cust#}) 4. Schema transformations

50. Practically, a structural predicate can be defined graphically: entity-type(CUSTOMER)  attribute(CUSTOMER,Cust#,1,1,integer)  attribute(CUSTOMER,Name,1,1,string)  attribute(CUSTOMER,Phone,0,5,string)  id(CUSTOMER,{Cust#}) 4. Schema transformations =