View Integration Among Databases

SCHEMA INTEGRATION SCHEMA (VIEW) INTEGRATED SCHEMA USA SCHEMA (VIEW) Europe • PURPOSES FOR SCHEMA / VIEW INTEGRATION Global – NEW DATABASE DESIGN: MERGE MULTIPLE PEOPLE’S VIEWS – DATABASE REDESIGN: MERGE MULTIPLE EXISTING DB’S TO NEW DB – DATA WAREHOUSE: CREATE INTEGRATED SCHEMA / DATABASE – INTEGRATED VIEW FOR DISTRIBUTED HETEROGENEOUS DBMS EVOLUTION OF CONCEPTUAL DATABASE DESIGN • TRADITIONAL DATA MODELS HIERARCHICAL NETWORK CUSTOMER DEPT DIVISION DEPARTMENT EMPLOYEES PRODUCT PRODUCTS EMPLOYEE PROJECT • SEMANTICALLY -- RICHER DATA MODELS – – – – ENTITY-RELATIONSHIP (ER) MODEL SEMANTIC DATA MODEL OBJECT-ORIENTED (OO) DATA MODEL OBJECT-RELATIONAL (OR) DATA MODEL • “CONCEPTUAL” DATABASE DESIGN – LEADS TO “CONCEPTUAL SCHEMA” – AIDS INTEGRATION (“SCHEMA INTEGRATION”) RELATIONAL DEPARTMENT Table EMPLOYEE Table PRODUCT Table PROCESS OF SCHEMA INTEGRATION • DATA -- AN AUTONOMOUS RESOURCE • NEED TO CAPTURE “MEANING” OF DATA • DESIRE “ENTERPRISE-WIDE” VIEW OF DATA TERMINOLOGY • SCHEMA INTEGRATION – COMPONENT SCHEMA – INTEGRATED SCHEMA • VIEW INTEGRATION – USER VIEW – CONCEPTUAL VIEW • DATABASE INTEGRATION – LOCAL SCHEMA – GLOBAL SCHEMA EXAMPLE • TWO SCHEMAS DEVELOPED BY TWO GROUPS – BOTH FOCUS ON BOOKS/PUBLICATIONS ENTITY-RELATION (ER) DIAGRAM – For Book Distributor • ENTITIES • RELATIONSHIPS • ATTRIBUTES Name University Book Publisher Address Name Title Produces Used by Has Name Topics State Book Distributor Name Publisher Name Title Publication University Book University Library Title Code Publisher State Address Name Step 1. Original schemas Topics Name Keyword Name Title Publisher Publication University Book Title Code Research Area Title Code Publisher State Address Title Entity and Attribute Equivalence Topics Name Name Name Title Publisher Topics Step 2. Choose “Topics” for “keyword” (Schema 2). University Book Publisher Publication Title Code State Address Title Attribute vs Entity Title Code Research Area Topics Topics Step 3. Make Publisher into an entity (Schema 2). Title Code Research Area Title Publication Name Code Publisher Topics Title Address Book Title Code Research Area Name University State Step 4. Superimposition of schema Title Publication Name Code Publisher Title Address Book Topics Title Code Research Area Name University State Generalization & Specialization Step 5. Creation of subset relationship Title Publication Name Title Code Publisher Title Topics Address Book Name University State Step 6. Drop the properties of Book common to Publication Inheritance Code Research Area CAUSES OF DIFFERENCES • DIFFERENT PERSPECTIVES – DIFFERENT NAME FOR SAME CONCEPT – INTERVENING STEPS • EMPLOYEE-DEPARTMENT • EMPLOYEE-PROJECT-DEPARTMENT • EQUIVALENCE AMONG CONSTRUCTS – MODEL AS ATTRIBUTE OR ENTITY (E.G., PUBLISHER) • INCOMPATIBLE DESIGN SPECIFICATIONS – RELATIONSHIP DIFFERENCES (E.G., 1:N VS. N:M) SOME EXAMPLES • NAMING CONFLICTS – HOMONYMS: SAME NAME, DIFFERENT CONCEPT E.G., EQUIPMENT E.G., “SIZE” OF DRESS = 1 NUMBER, “SIZE” OF PANTS = 2 NUMBERS) – SYNONYMS: SAME CONCEPT, DIFFERENT NAMES E.G., CLIENT, CUSTOMER, PATIENT, ... • STRUCTURAL CONFLICTS – TYPE: ENTITY VS. ATTRIBUTE (E.G., PUBLISHER) – DEPENDENCY: 1.1 VS. N:M (E.G., HUSBAND: WIFE) – KEY: ALTERNATE WAYS TO IDENTIFY ENTITY (E.G., SS# VS. EMP#) – BEHAVIORAL: DIFFERENT INSERTION/DELETION POLICIES (E.G., CAN YOU HAVE DEPARTMENT WITH NO EMPLOYEES?) EXAMPLE SCHEMAS TO BE INTEGRATED: Departmental database name Registrar’s database n DEPT sub # COURSE I n m name id STUDENT PROFESSOR I n name position name id grade STUDENT sub # SUBJECT Student Telephone directory Staff Telephone directory I I STAFF STUDENT street city phone # name year I name room # ext # O/I OFFICE ADDRESS I I HOME ADDRESS room # ext # TERM ADDRESS street city phone # street city phone # SUMMARY • INCREASING NEED TO PROVIDE AN INTEGRATED GLOBAL VIEW OF AN ORGANIZATION’S INFORMATION - AND SOMETIMES RELATED ORGANIZATIONS (CUSTOMERS / SUPPLIER) • AN IMPORTANT STEP IS THE CREATION OF A GLOBAL SCHEMA - INTEGRATES SEPARATE SCHEMAS - CONTAINS ALL THE CRITICAL INFORMATION NEEDED