MDBS Schema Integration The Relational Integration Model Ramon

MDBS Schema Integration: The Relational Integration Model Ramon Lawrence MDBS Schema Integration: The Relational Integration Model Researchers: Ramon Lawrence, Ken Barker University of Manitoba TRLabs - Winnipeg Page 1

MDBS Schema Integration: The Relational Integration Model Ramon Lawrence Outline èIntroduction èThe MDBS architecture and the Integration problem èA schema integration taxonomy èPrevious Work èThe RIM Architecture and the RIM Model èFuture work and conclusions Page 2

MDBS Schema Integration: The Relational Integration Model Ramon Lawrence Database Terminology è database system - a database and a system to manage the data è transaction - an atomic sequence of operations applied to the database è global transaction - a transaction spanning more than one database è schema integration - the process of combining local schemas into a global, integrated schema è multidatabase system (MDBS) - a collection of autonomous, local databases participating in a global database system to share data Page 3

MDBS Schema Integration: The Relational Integration Model Ramon Lawrence MDBS Architecture Global Transactions Global Transaction Manager (GTM) • processes global transactions • insures information in all LDBSs is consistent • submits subtransactions to the GTSs for each LDBS GTM subtransactions GTS LDBS GTS Global Transaction Servers (GTSs) GTS • one for each LDBS • converts subtransactions from the GTM into a form usable by the LDBS and vice versa LDBS Local Database Systems (LDBSs) Local Transactions • databases combined into MDBS • unchanged as still process local transactions Page 4

MDBS Schema Integration: The Relational Integration Model Ramon Lawrence The Integration Problem èIntegrating diverse data sources is an important issue as organizations interconnect their operations and demand more from their database systems èIntegration is a hard problem because structural and semantic conflicts exist èTwo levels of integration: u schema integration u data integration Page 5

MDBS Schema Integration: The Relational Integration Model Ramon Lawrence Schema Integration èSchema integration is the process of combining database schemas into a coherent global view èIntegration problems include: u different data models u incompatible concept representations u different user or view perspectives u structural conflicts within a model u naming conflicts (homonym, synonym) Page 6

MDBS Schema Integration: The Relational Integration Model Ramon Lawrence A Schema Integration Taxonomy Automation Level automatic (dynamic) automatic (static) semi-automatic manual naming interschema NONE structural semantic all Conflicts Resolved behavioral both Transparency Page 7

MDBS Schema Integration: The Relational Integration Model Ramon Lawrence Previous Work èsemantic u Batini èschema u models: (86), canonical models, SDM, DAPLEX re-engineering: mapping tools, schema transformations èmetadata systems: u rule-based systems èobject-oriented u use methods: as a canonical model, schema transformations èapplication-level u language integration: systems, MSQL, IDL, higher order views Page 8

MDBS Schema Integration: The Relational Integration Model Ramon Lawrence Previous Work (cont. ) èInterdatabase u Sheth èAI dependencies: - relaxed consistency, integration rules techniques: u Pegasus (spheres of knowledge), knowledge packets, Carnot project (Cyc knowledge base) èLexical semantics: u Summary Schemas Model (Bright et al. ) - user interface for imprecise queries èIndustrial systems: u Interbase Page 9

MDBS Schema Integration: The Relational Integration Model Ramon Lawrence RIM: Objective èThe objective of the RIM model is to provide a system for automatically integrating diverse relational schemas into a multidatabase èDesirable properties: u individual mappings - information sources integrated one-at-a-time and independently u global view constructed for query transparency u handles schema conflicts - including semantic, structural, and naming conflicts u automated global integration - global view constructed efficiently and automatically Page 10

MDBS Schema Integration: The Relational Integration Model Ramon Lawrence RIM: The Idea èThe idea behind the RIM model is that most (and probably all) schema conflicts can be resolved if we: u eliminate all naming conflicts u define a language capable of determining schema equivalence and performing transformations èWith these two properties, schema conflicts can be resolved automatically at the global level Page 11

MDBS Schema Integration: The Relational Integration Model Ramon Lawrence RIM: The Plan èThe first task is eliminating naming conflicts: u use a global thesaurus/dictionary like SSM u map local schema names into global counterparts u identical concepts can be identified by global name èThe integration language must be defined: u RIM specifications - records capturing semantics of each LDBS in a machine-processable form u global names captured in RIM specs. to identify concepts stored in LDBS Page 12

MDBS Schema Integration: The Relational Integration Model Ramon Lawrence RIM: The Plan (cont. ) èIntegrate RIM specifications: u To query the MDBS, the client downloads and integrates only RIM specs. of LDBSs accessed u Global view is constructed from RIM specs. by automatically combining them at client site using global names and semantic metadata they contain u Use of global names allows system to determine identical concepts even though structural representations may be different u Semantic information captured using metadata Page 13

MDBS Schema Integration: The Relational Integration Model Ramon Lawrence RIM: The Plan (cont. ) èQuerying the MDBS: u queries are posed to the MDBS through the global view at each client u translation from the GV back to the original RIM spec. for each LDBS is performed u the translated queries are sent to each LDBS which transforms the query (specified using RIM) into a query for the LDBS u results are returned to the client which integrates them based on its GV Page 14

MDBS Schema Integration: The Relational Integration Model Ramon Lawrence RIM: Architecture RDBS RIM RIM spec. RIM Integration Global View Client RIM Specifications: • constructed at each RDBS • local concepts mapped to global names • schema can be automatically extracted RIM Integration: • uses needed RIM specs. • constructs global view • resolves conflicts by: • identifying concepts using global names • transforming concepts into a form consistent with the global view Page 15

MDBS Schema Integration: The Relational Integration Model Ramon Lawrence RIM: Using Global Names èGlobal names attempt to capture semantics of data and its structure èResearch has found that a single dictionary term is insufficient to capture all semantics of a given data item èCurrent proposed global name term: u [context term] [concept name] ([adjective phrases]) u [adjective phrase] = [adjective] [preposition] ([context term] or [concept name]) Page 16

MDBS Schema Integration: The Relational Integration Model Ramon Lawrence RIM: Using Global Names (cont. ) èHere a few examples of using global names: u the database stores damage claim information èExample 1: u attribute of claim is called net_amount in system u GN: [Claim] Net Amount èExample 2: u attribute of claim is called claim_date in system u GN 1: [Claim] Claim date (received by system) u GN 2: [Claim] Claim date (received by company) u GN 3: [Claim] Claim date (submitted by claimant) Page 17

MDBS Schema Integration: The Relational Integration Model Ramon Lawrence RIM: The Global Dictionary èTo match concepts across systems, a global dictionary is required. Global names are taken from this dictionary. èDictionary currently chosen is Word. Net developed at Princeton: u complete on-line dictionary with a browser interface u defines multiple definitions per term u has built in hypernym and synonym searching and referencing features èFuture work involves determining how to add locally defined terms into the dictionary if required Page 18

MDBS Schema Integration: The Relational Integration Model Ramon Lawrence RIM: Basic Concepts èThere are 3 basic modeling constructs in RIM: u entity - a concept whose existence does not depend on any other entities u relationship - a combination of two or more entities which does not exists without them u attribute - a characteristic of an entity or a relationship èAll entities and attributes should be identifiable by a global name from the dictionary. Page 19

MDBS Schema Integration: The Relational Integration Model Ramon Lawrence RIM: RIM Specifications èA RIM specification consists of two parts: u table headers - table-level information for each relation in database u table schemas - information at the attribute level of a database relation èMost of the information can be automatically extracted, however the DBA must assign global names to local concepts manually Page 20

MDBS Schema Integration: The Relational Integration Model Ramon Lawrence RIM: The Table Header èThe table header provides table-level information for each relation and has fields: u name - unique table name (local) u record size and count u foreign key list and foreign key access list u record insert/delete/update mechanisms u record name - semantic name for a table record u record type - entity, relationship instance, . . . u record grouping - why are records in the table? u record distinction/duplicates - primary key u table comment Page 21

MDBS Schema Integration: The Relational Integration Model Ramon Lawrence RIM: The Table Schema èThe table schema contains attribute-level information. Some fields include: u field name - database system name u semantic name - global name u field use: ï attribute, key, categorization, summation, date/time, foreign key, logical, numeric, reference Page 22

MDBS Schema Integration: The Relational Integration Model Ramon Lawrence RIM: Semantic Conflicts èThere are 6 basic semantic conflicts in RIM: u attribute-entity conflict u attribute-relationship conflict u entity-entity conflict (not studied) u attribute-attribute conflict (not studied) u relationship-relationship conflict (not studied) èThere is some basic ideas on how to automatically resolve the first 3 conflicts. èConflict resolution is an area of future work. Page 23

MDBS Schema Integration: The Relational Integration Model Ramon Lawrence Conclusions èCurrent integration methodologies are insufficient because they rely on manual intervention and do not resolve all types of conflicts èThe RIM model may be able to integrate diverse relational schemas using a global dictionary, a systematic method for capturing data semantics, and automated procedures for performing client run-time integration Page 24

MDBS Schema Integration: The Relational Integration Model Ramon Lawrence Future Work èDetermining how the RIM specifications can be constructed and what information can be automatically extracted èDeciding the format for the global dictionary èStudying conflict resolution procedures and testing methodology on simple integration problems Page 25