1 / 29

Data integration and transformation 3. Data Exchange

Data integration and transformation 3. Data Exchange. Paolo Atzeni Dipartimento di Informatica e Automazione Università Roma Tre 28/10/2009. References.

dennis
Download Presentation

Data integration and transformation 3. Data Exchange

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Data integration and transformation3. Data Exchange Paolo Atzeni Dipartimento di Informatica e Automazione Università Roma Tre 28/10/2009

  2. References • Ronald Fagin, Laura M. Haas, Mauricio Hernandez, Renee J. Miller, Lucian Popa, and Yannis Velegrakis "Clio: Schema Mapping Creation and Data Exchange" A.T. Borgida et al. (Eds.): Mylopoulos Festschrift, LNCS 5600, Springer-Verlag Berlin Heidelberg, 2009, pp. 198–236. and other papers cited in it ITD - 3 - 28/10/2009

  3. Data exchange • Given a source and a target schema, find a transformation from the former to the latter ITD - 3 - 28/10/2009

  4. Data exchange, a typical approach (the Clio project) Schema Match Mapping generation Source schema Target schema Query generation ITD - 3 - 28/10/2009

  5. Simple example Dept(Id,DeptName)Emp(Code,EmpName,Dept) Employee(Id,Name,DeptId) (with FK fromDeptIdtoDept.Id) Assume weknowthat Employee.IdcorrespondstoCode NamecorrespondstoEmpName DeptNamecorrespondstoDept Wewouldliketoobtain a querythatpopulatesEmp SELECT Id as Code, Name AS EmpName, DeptName AS Dept FROM Employee JOIN Dept ON DeptId = Dept.Id ITD - 3 - 28/10/2009

  6. Better visualization Employee Id Name DeptId Dept Id DeptName Emp Code EmpName Dept • Wewanttoobtain • SELECT Id as Code, Name AS EmpName, DeptName AS Dept • FROM Employee JOIN Dept ON DeptId = Dept.Id • and not • SELECT Id as Code, Name AS EmpName, NULL AS DeptFROM Employee • UNION • SELECT NULL as Code, NULL AS EmpName, DeptNameAS DeptFROM Dept • nor • SELECT Id as Code, NULLAS EmpName, NULL AS DeptFROM Employee • UNION • … ITD - 3 - 28/10/2009

  7. The main issue • How do we discover we should use a join and not one or two unions? • Attributes that appear together in a relation • Id,Name in the source and Code,EmpName in the target • The foreign key ITD - 3 - 28/10/2009

  8. Data exchange, another example Address ( Id Addr ) Professor ( Id Name Sal ) Student ( Name GPA Yr ) Personnel ( Id Name Sal Addr ) PayRate ( Rank HrRate ) WorksOn ( Name Proj Hrs ProjRank ) • Foreignkeys • between the twoId • betweenProjRankandRank • between the twoName ITD - 3 - 28/10/2009

  9. Data exchange, example Address ( Id Addr ) Professor ( Id Name Sal ) Student ( Name GPA Yr ) Personnel ( Id Name Sal Addr ) PayRate ( Rank HrRate ) WorksOn ( Name Proj Hrs ProjRank ) • Assume we are given correspondences, which involve functions: • Usually identity • PayRate(HrRate)*WorksOn(Hrs) → Personnel(Sal) ITD - 3 - 28/10/2009

  10. Data exchange, example Address ( Id Addr ) Professor ( Id Name Sal ) Student ( Name GPA Yr ) Personnel ( Id Name Sal Addr ) PayRate ( Rank HrRate ) WorksOn ( Name Proj Hrs ProjRank ) • How do we combine HrRate and Hrs? • Via a join suggested by foreign keys • Foreign key between ProjRank and ProjRank suggests a join • Foreign keys over Nameand between Yr and Ranksuggest another ITD - 3 - 28/10/2009

  11. Heuristic • We have many correspondences • Group correspondences in such a way that each set contains at most one correspondence for each attribute in the target • We are interested in sets where the source attribute are either in the same relations or in relations whose join is meaningful ITD - 3 - 28/10/2009

  12. Address ( Id Addr ) Professor ( Id Name Sal ) Student ( Name GPA Yr ) Personnel ( Id Name Sal Addr ) PayRate ( Rank HrRate ) WorksOn ( Name Proj Hrs ProjRank ) Partition the correspondences • … and foreachpartition the joins are meaningful ITD - 3 - 28/10/2009

  13. Address ( Id Addr ) Professor ( Id Name Sal ) Student ( Name GPA Yr ) Personnel ( Id Name Sal Addr ) PayRate ( Rank HrRate ) WorksOn ( Name Proj Hrs ProjRank ) The process, example SELECT P.Id, P.Name, P.Sal, A.AddrFROM Professor P, Address A WHERE A.Id = P.Id UNION ALL SELECT NULL AS Id, S.Name, p.HrRate * W.Hrs, NULL AS AddrFROM PayRate P, Student S, WorksOn WWHERE W.Name = S.Name AND S.Yr = P.Rank ITD - 3 - 28/10/2009

  14. More complex example (with nesting) Companies Name Address Year Grants Gid Recipient Amount Supervisor Manager Contacts Cid Email Phone Organizations Code Year Fundings FId FinId Finances FinId Budget Phone f1 Nested relation f4 Organizations f2 Code Year Fundings FId FinId f3 HAL 301 302 SM PH 303 ITD - 3 - 28/10/2009

  15. Correspondences (given by a "schema matcher") Companies Name Address Year Grants Gid Recipient Amount Supervisor Manager Contacts Cid Email Phone Organizations Code Year Fundings FId FinId Finances FinId Budget Phone v1 f1 v2 f4 v3 f2 f3 v4 ITD - 3 - 28/10/2009

  16. Let us formalize correspondences n,d,y Companies(n,d,y) → y',F Organizations(n,y',F)) Companies Name Address Year Grants Gid Recipient Amount Supervisor Manager Contacts Cid Email Phone v1 v1 Organizations Code Year Fundings FId FinId Finances FinId Budget Phone g,r,a,s,m Grants(g,r,a,s,m) → c,y,F,fOrganiz…(c,y,F)), F(g,f) f1 v2 v2 g, r, a, s, m Grants(g,r,a,s,m) → f,p Finances(f,a,p) f4 v3 v3 f2 c, e, p Contacts(c,e,p) → f,b Finances(f,b,p) v4 f3 v4 ITD - 3 - 28/10/2009

  17. Correspondences alone are not enough n,d,y Companies(n,d,y) → y',F Organizations(n,y',F)) Companies Name Address Year Grants GId Recipient Amount Supervisor Manager Contacts Cid Email Phone Organizations Code Year Fundings FId FinId Finances FinId Budget Phone v1 v1 g,r,a,s,m Grants(g,r,a,s,m) → c,y,F,fOrganiz…(c,y,F)), F(g,f) f1 v2 v2 g, r, a, s, m Grants(g,r,a,s,m) → f,p Finances(f,a,p) f4 Organizations v3 Companies v3 Code Year Fundings f2 SM 303 Name 302 301 HAL GId PH Address NY SF Seattle HAL Rec.t HAL PH Amt 30 40 30 1984 Year 1920 1957 FId FinId c, e, p Contacts(c,e,p) → f,b Finances(f,b,p) v4 HAL f3 Grants v4 SM PH 301 302 ITD - 3 - 28/10/2009

  18. More complex mappings are needed,representing associations n,d,y,g,a,s,mCompanies(n,d,y), Grants(g,n,a,s,m) → y',F,fOrganizations(n,y',F)), F(g,f) Companies Name Address Year Grants GId Recipient Amount Supervisor Manager Contacts Cid Email Phone Organizations Code Year Fundings FId FinId Finances FinId Budget Phone v1 Note: The "association" betweencompanies and grants in the source issuggestedby f1 (a foreign key) f1 v2 g, r, a, s, m Grants(g,r,a,s,m) → f,p Finances(f,a,p) f4 Organizations v3 Companies v3 Code Year Fundings f2 GId 303 302 301 SM PH Name HAL Address SF Seattle NY PH HAL Rec.t HAL 30 30 40 Amt 1920 1957 1984 Year FId FinId c, e, p Contacts(c,e,p) → f,b Finances(f,b,p) v4 HAL 301 f3 302 Grants v4 SM PH 303 ITD - 3 - 28/10/2009

  19. Yet more complex n,d,y,g,a,s,mCompanies(n,d,y), Grants(g,n,a,s,m) → y',F,f, p Organizations(n,y',F), F(g,f), Finances(f,a,p) Companies Name Address Year Grants Gid Recipient Amount Supervisor Manager Contacts Cid Email Phone v1 Organizations Code Year Fundings FId FinId Finances FinId Budget Phone f1 v2 • Notes: • Three tuples are generatedforeachpairofrelatedcompanies and grants • The mappingspecifiesthatthereexistanf, appearing in twoplaces, withoutsayingwhichitsvalueshouldbe f4 v3 f2 f3 v4 ITD - 3 - 28/10/2009

  20. A final issue • How do weobtain the phonetobe put in finances? • Isit the supervisor's one or the manager's? • FKssuggesteither (or evenboth) • Humaninterventionisneededtochoose Companies Name Address Year Grants Gid Recipient Amount Supervisor Manager Contacts Cid Email Phone v1 Organizations Code Year Fundings FId FinId Finances FinId Budget Phone f1 v2 f4 v3 f2 f3 v4 ITD - 3 - 28/10/2009

  21. Various solutions in nested caseswith possibily undesirable features Organizations Code Year Fundings FId FinId Companies HAL k1 301 302 k1 SM PH 303 k1 SM k1 k1 k1 303 Name 302 HAL 301 PH GId FinId Budget 30 40 30 Address NY Seattle SF Rec.t HAL PH HAL 30 40 Amt 30 1920 Year 1957 1984 phone Grants Finances ITD - 3 - 28/10/2009

  22. A better solution Organizations Code Year Fundings FId FinId Companies HAL k1 301 302 k2 SM PH 303 k3 SM k1 k2 k3 303 Name 302 HAL 301 PH GId FinId Budget 30 40 30 Address NY Seattle SF Rec.t HAL PH HAL 30 40 Amt 30 1920 Year 1957 1984 phone Grants Finances ITD - 3 - 28/10/2009

  23. A more verbose notation for mappings n,d,y,g,a,s,mCompanies(n,d,y), Grants(g,n,a,s,m) → y',F,f, p Organizations(n,y',F)), F(g,f), Finances(f,a,p) Companies Name Address Year Grants Gid Recipient Amount Supervisor Manager Contacts Cid Email Phone v1 Organizations Code Year Fundings FId FinId Finances FinId Budget Phone f1 v2 foreach c in companies, g in grants where c.name=g.recipient exists o in organizations, f in o.fundings, i in finances where f.finId = i.finId with o.code = c.name and f.fId = g.gId and i.budget = g.amount f4 v3 f2 f3 v4 query on the source query on the target correspondences ITD - 3 - 28/10/2009

  24. The mapping as a source-to-target constraint foreach c in companies, g in grants where c.name=g.recipient exists o in organizations, f in o.fundings, i in finances where f.finId = i.finId with o.code = c.name and f.fId = g.gId and i.budget = g.amount QS QT "the result of QT (over the target, projected as in the with-clause) must contain the result of QS (over the source, projected as in the with-clause)" QS Companies Name Address Year Grants Gid Recipient Amount Supervisor Manager Contacts Cid Email Phone v1 Organizations Code Year Fundings FId FinId Finances FinId Budget Phone QT f1 v2 f4 v3 f2 f3 v4 ITD - 3 - 28/10/2009

  25. Syntax and restrictions foreach x1 in g1, . . . , xn in gn where B1 exists y1 in g'1, . . . , ym in g'm where B2 with e1 = e'1 and . . . and ek = e'k foreach c in companies, g in grants where c.name=g.recipient exists o in organizations, f in o.fundings, i in finances where f.finId = i.finId with o.code = c.name and f.fId = g.gId and i.budget = g.amount • xi in gi (generator) • xi variable • gi set (either the root or a set nestedwithinit) • B1conjunctionofequalitiesover the xi variables yi in g'i B2 similar e1 = e'1 … equalities between a source expression and a target expression Restrictions: See paper, page 210, lines 5+: "The mapping is well formed …" ITD - 3 - 28/10/2009

  26. Schema constraints • Referential integrity is essential in this approach as the basis for the discovery of "associations" • Given the nested model, they need a rather complex definition • So, two steps • Paths (primary paths and relative paths) • Nested referential integrity (NRI) constraints ITD - 3 - 28/10/2009

  27. Primary paths • Primary path (given a schema root R, that is a first level element in the schema): • x1 in g1, x2 in g2, …, xn in gn • where g1 is an expression on R (just R?), gi (for i ≥ 2) g1 is an expression on xi-1 • Examples • c in companies • o in organizations • o in organizations, f in o.fundings ITD - 3 - 28/10/2009

  28. Relative paths • Primary path (given a schema root R, that is a first level element in the schema): • x1 in g1, x2 in g2, …, xn in gn • where g1 is an expression on R (just R?), gi (for i ≥ 2) g1 is an expression on xi-1 • Relative path with respect to a variable x • x1 in g1, x2 in g2, …, xn in gn • where g1 is an expression on x (just x?), gi (for i ≥ 2) g1 is an expression on xi-1 • Example • f in o.fundings ITD - 3 - 28/10/2009

  29. Nested referential integrity (NRI) constraints • foreach P1exists P2where B • P1is a primarypath • P2iseither a primarypath or a relative pathwithrespectto a variable in P1 • B is a conjunctionofequalitiesbetweenanexpression on a variableof P1 and anexpression on a variableof P2 • Example foreach o in organizations, f in o.fundings exists i in finances wheref.finId = i.finId Organizations Code Year Fundings FId FinId Finances FinId Budget Phone f4 ITD - 3 - 28/10/2009

More Related