Database Management System - Chapter 5: Understanding Entity Relationship Diagrams

Chapter 5Understanding Entity Relationship DiagramsOutline Notation basicsUnderstanding relationshipsGeneralization hierarchiesBusiness rule representationDiagram rulesAlternative notationsBasic SymbolsCardinalities Cardinality NotationClassification of CardinalitiesMinimum cardinality basedMandatory: existence dependentOptionalMaximum cardinality basedFunctional1-MM-N1-1Summary of CardinalitiesMore Relationship ExamplesComparison to Access NotationUnderstanding RelationshipsIdentification dependencyM-N relationships with attributesSelf identifying relationshipsM-way relationshipsEquivalence between M-N and 1-M relationshipsIdentification DependencyM-N Relationships with AttributesM-N Relationships with Attributes (II)Instance Diagrams for Self-Referencing RelationshipsERD Notation for Self-Referencing RelationshipsAssociative Entity Types for M-way RelationshipsRelationship EquivalenceReplace M-N relationship Associative entity typeTwo identifying 1-M relationshipsM-N relationship versus associative entity typeLargely preferenceAssociative entity type is more flexible in some situationsAssociative Entity Type ExampleGeneralization HierarchiesInheritanceSubtypes inherit attributes of supertypes (direct and indirect)Allows abbreviation of attribute listApplies to code (methods) as well as attributes (data)Generalization ConstraintsMultiple Levels of GeneralizationComprehensive ExampleBusiness RulesEnforce organizational policiesPromote efficient communicationFormal representation in ERDInformal representation in documentation associated with an ERDUse rules language to formally represent in relational database after conversionFormal RepresentationPrimary key constraints: entity identificationNamed relationships: direct connections among business entitiesIdentification dependency: knowledge of other entities for identificationCardinalities: restrict number of related entities in a business situationGeneralization hierarchies: classification of business entities and organizational policiesInformal RepresentationSpecify as documentation associated elements of an ERDCandidate key constraints: alternate ways to identify business entitiesReasonable values: fixed collection of values or consistent with another attributeNull value constraints: data collection completenessDefault values: simplify data entry and provide value when unknownDiagram RulesEnsure that ERD notation is correctly usedSimilar to syntax rules for a computer languageCompleteness rules: no missing specificationsConsistency rules: no conflicts among specificationsSupported by the ER AssistantCompleteness RulesPrimary Key Rule: all entity types have a PK (direct, indirect, or inherited)Naming Rule: all entity types, relationships, and attributes have a nameCardinality Rule: cardinality is specified in both directions for each relationshipEntity Participation Rule: all entity types participate in an at least one relationship except for entity types in a generalization hierarchyGeneralization Hierarchy Participation Rule: at least one entity type in a generalization hierarchy participates in a relationshipPrimary Key Rule IssuePrimary key rule is simple in most casesFor some weak entities, the PK rule is subtleWeak entity with only one 1-M identifying relationshipWeak entity must have a local key to augment the borrowed PK from the parent entity typeViolation of PK rule if local key is missingPK Rule Violation ExampleNaming Consistency RulesEntity Name Rule: entity type names must be uniqueAttribute Name Rule: attribute names must be unique within each entity type and relationshipInherited Attribute Rule: attribute names in a subtype do not match inherited (direct or indirect) attribute names.Relationship NamesNo uniqueness requirementParticipating entities provide a context for relationship namesUse unique names as much as possible to distinguish relationshipsMust provide unique names for multiple relationships between the same entity typesConnection Consistency RulesRelationship/Entity Connection Rule: relationships connect two entity types (not necessarily distinct)Relationship/Relationship Connection Rule: relationships are not connected to other relationships Redundant Foreign Key Rule: foreign keys are not used.Identification Dependency RulesWeak entity rule: weak entities have at least one identifying relationship Identifying relationship rule: at least one participating entity type must be weak for each identifying relationshipIdentification dependency cardinality rule: the minimum and maximum cardinality must equal 1 for a weak entity in all identifying relationshipsExample of Diagram ErrorsCorrected ERDSupport in the ER Assistant Relationship formation rules are supported by diagram constructionOther rules are supported by the Check Diagram featureFor the Redundant Foreign Key rule, the ER Assistant detects FKs that have the same name as the associated PKsERD VariationsNo standard ERD notationSymbol variationsPlacement of cardinality symbolsRule variationsBe prepared to adjust to the ERD notation in use by each employer ERD Rule VariationsLack of ERD standardsM-way relationshipsM-N relationshipsRelationships with attributesSelf-referencing relationshipsRelationships connected to other relationshipsAdapt to notations in work environmentsChen ERD NotationUnified Modeling LanguageStandard notation for object-oriented modelingObjectsObject featuresInteractions among objectsUML supports class diagrams, interface diagrams, and interaction diagramsMore complex than ERD notationSimple Class DiagramAssociation ClassGeneralization RelationshipComposition RelationshipSummaryData modeling is an important skillCrow’s Foot ERD notation is widely usedUse notation preciselyUse the diagram rules to ensure structural consistency and completenessUnderstanding the ERD notation is a prerequisite to applying the notation on business problems