Database Management System - Chapter 12: View Design and Integration

Chapter 12View Design and IntegrationOutline Motivation for view design and integrationView design with formsView integrationMotivationDatabase complexity reflects organizational complexity.Time-consuming and labor-intensive processCollect requirements from different user groupsCoordination among designer team membersManage complexity of large designsManaging Complexity As the “divide and conquer” strategy is used to manage complexity, view design and integration is an approach to managing complexity of the database design effort. Overview of View Design and IntegrationView Design with Forms Important source of database requirementsReverse the process described in the Chapter 10Derive an ERD that is consistent with the formFive step procedure Sample Customer Order FormForm AnalysisCreate an ERD to represent a formERD supports form and other anticipated processingERD should be consistent with the formERD is a view of the database Form Analysis StepsStep 1: Define Form StructureConstruct a hierarchy that depicts the form structureMost forms consist of a simple hierarchy where the main form is the parent and the subform is the child.Complex forms can have parallel subforms and more levels in the hierarchy. Hierarchical Form StructureStep 2: Identify Entity Types Split each node in the hierarchical structure into one or more entity types.Make an entity type if a form field is a potential primary key and there are other associated fields in the form. Entity Types for the Customer Order FormStep 3: Attach Attributes Attach attributes to the entity types identified in the previous step Group together fields that are associated with the primary keys found in Step 2Form fields close together may belong in the same entity type Add Attributes Step 4: Add Relationships Relationships involving the form entity typeForm entity type contains the form's primary keyRelationships between the form entity type and other entity types derived from the parent node: usually 1-M.Add a relationship to connect the form entity type to an entity type in the child nodeAdd relationships to connect entity types derived from the child node if not already connectedEntity Relationship Diagram Step 5: Check Completeness and ConsistencyThe ERD should adhere to the diagram rules specified in Chapter 5. In addition, the ERD should be consistent and complete with respect to the form structure.Explore diagram transformations as suggested in Chapter 6. Consistency Rules for Relationship CardinalitiesIn at least one direction, the maximum cardinality should be one for relationships connecting entity types derived from the same node (parent or child). In at least one direction, the maximum cardinality should be greater than one for relationships connecting entity types derived from nodes on different levels of the form hierarchy. Analysis of M-Way Relationships using FormsChoice between M-way and binary relationships can be difficult.Data entry forms provide a context to understand M-way relationships.An M-way relationship may be needed if a form shows a data entry pattern involving three entity types. Sample Project Purchasing Form ERD for the Project Purchase Form Sample Purchasing Form ERD for the Purchasing FormView IntegrationCombine individual views into a complete database designIncremental and parallel integration approaches Incremental Approach Parallel ApproachIntegration Strategy Precedence RelationshipsForm A precedes form B if form A must be completed before form B.Preceding forms typically provide data for subsequent forms.Place forms with precedence relationships in the same view subset Precedence ExampleResolving Synonyms and HomonymsSynonym: spelled differently but have the same meaningHomonym: same sound and often the same spelling but different meaningForms provide a context to resolveMajor part of standardizing a vocabularyView Integration Examples Sample Invoice FormIncremental Integration ExampleThe following 5 slides demonstrate the Incremental Integration process by integrating the Invoice Form with the ERD for Customer Order Form Form Hierarchy Identify entity types and attach attributesMatch Form FieldsMatch to existing entity typesOrder No matches the Order entity type.Customer No, Customer Name, Address, City, State, and Zip match the Customer entity type.Product No, Description, and Unit Price match the Product entity type.Analyze HomonymsRevise the Customer entity type with two sets of address fields: billing address fields and shipping address fields.Add shipping address fields to the Invoice entity type.Create a new entity type (ShipAddress) with the shipping address fields. Integrated ERD (incremental)Parallel Integration ExampleThe difference between the parallel and incremental approaches is that integration occurs later in the parallel approach.For the parallel approach, ERDs for forms must be constructed before merging. ERD for the Invoice Form Integrated ERD (Parallel)SummaryView design and integration is an important skill for designing large databases.Manage complexity of large development effortsThe result of form analysis is an ERD that is a view of the database.Two approaches for View Integration, incremental and parallel