Database Management System - Chapter 6: Developing Data Models for Business Databases

Chapter 6Developing Data Models for Business DatabasesOutline Guidelines for analyzing business information needsTransformations for generating alternative designsFinalizing an ERDSchema ConversionCharacteristics of Business Data Modeling ProblemsPoorly definedConflicting statementsWide scopeMissing detailsMany stakeholdersRequirements in many formatsAdd structureEliminate irrelevant detailsAdd missing detailsNarrow scopeGoals of Narrative Problem AnalysisConsistency with narrativeNo contradictions of explicit narrative statementsIdentify shortcomingsAmbiguous statementsMissing detailsSimplicity preferenceChoose simpler designs especially in initial designAdd refinements and additional details laterSteps of Narrative Problem AnalysisIdentify entity types and attributesDetermine primary keysAdd relationshipsDetermine connectionsDetermine relationship cardinalitiesSimplify relationshipsDetermine Entity Types and AttributesFor entity types, find nouns that represent groups of people, places, things, and eventsFor attributes, look for properties that provide details about the entity typesSimplicity principal: consider as an attribute unless other detailsDetermine Primary KeysStable: never change after assignedSingle purpose: no other purposeGood choices: automatically generated valuesCompromise choice for industry practicesIdentify other unique attributesEntity Identification ExampleIdentify RelationshipsIdentify relationships connecting previously identified entity typesRelationship references involve associations among nouns representing entity typesSentences that involve an entity type having another entity type as a propertySentences that involve an entity type having a collection of another entity type Relationship SimplificationProblem statement requires direct or indirect connectionsHub entity types to simplifyConnect other entity typesSometimes associated with important documentsReduce number of direct connections Relationship Identification ExampleDiagram RefinementsConstruct initial ERDRevise many timesGenerate feasible alternatives and evaluate according to requirementsGather additional requirements if neededUse transformations to suggest feasible alternativesAttribute to Entity Type TransformationCompound Attribute TransformationEntity Type Expansion TransformationWeak to Strong Entity TransformationAttribute History Transformation1-M Relationship TransformationM-N Relationship TransformationLimited History Transformation Generalization Hierarchy Transformation Summary of TransformationsAttribute to entity typeCompound attribute splitEntity type expansionWeak entity to strong entityAdd history: attributes, 1-M relationships, and M-N relationshipsGeneralization hierarchy additionDocumenting an ERDImportant for resolving questions and communicating a designIdentify inconsistency and incompleteness in a specificationIdentify situations when more than one feasible alternative existsDo not repeat the details of the ERDIncorporate documentation into the ERDDocumentation with the ER AssistantAttribute commentsEntity type commentsRelationship commentsDesign justificationsDiagram notesCommon Design ErrorsMisplaced relationships: wrong entity types connectedIncorrect cardinalities: typically using a 1-M relationship instead of a M-N relationship Missing relationships: entity types should be connected directly Overuse of specialized modeling tools: generalization hierarchies, identification dependency, self-referencing relationships, M-way associative entity typesRedundant relationships: derived from other relationships Resolving Design ErrorsMisplaced relationships: use entity type clusters to reason about connectionsIncorrect cardinalities: incomplete requirements: inferences beyond the requirementsMissing relationships: examine implications of requirementsOveruse of specialized modeling tools: only use when usage criteria are metRedundant relationships: examine relationship cycles for derived relationshipsExample Entity Type Cluster Summary of Data Modeling GuidelinesUse notation preciselyStrive for simplicityERD connectionsAvoid over connecting the ERDIdentify hub(s) of the ERDUse specialized patterns carefullyJustify important design decisionsSummary of Basic Conversion RulesEach entity type becomes a table.Each 1-M relationship becomes a foreign key in the table corresponding to the child entity type (the entity type near the crow’s foot symbol).Each M-N relationship becomes an associative table with a combined primary key.Each identifying relationship adds a column to a primary key. Application of Basic Rules (I)CREATE TABLE Course ( PRIMARY KEY (CourseNo) )CREATE TABLE Offering ( PRIMARY KEY OfferNo, FOREIGN KEY (CourseNo) REFERENCES Course ) Application of Basic Rules (II)CREATE TABLE Enrollment ( PRIMARY KEY (StdSSN, OfferNo), FOREIGN KEY (StdSSN) REFERENCES Student, FOREIGN KEY OfferNo REFERENCES Offering )Application of Basic Rules (III)Same conversion result as the previous slideDifferent application of rulesGeneralization Hierarchy RuleMimic generalization hierarchy as much as possibleEach subtype table contains specific columns plus the primary key of its parent table.Foreign key constraints for subtype tablesCASCADE DELETE option for referenced rowsReduce need for null valuesNeed joins and outer joins to combine tables Generalization Hierarchy ExampleEmployee table: EmpNo (PK)SalaryEmp table: EmpNo (PK), EmpNo (FK)HourlyEmp table: EmpNo (PK), EmpNo (FK)Optional 1-M RuleSeparate table for each optional 1-M relationshipAvoids null valuesRequires an extra table and join operationControversial: in most cases 1-M rule is preferred Optional 1-M ExampleCREATE TABLE Teaches ( PRIMARY KEY (OfferNo) , FOREIGN KEY(OfferNo) REFERENCES Offering, FOREIGN KEY(FacSSN) REFERENCES Faculty ) 1-1 RelationshipsCREATE TABLE Office ( PRIMARY KEY (OfficeNo) , FOREIGN KEY(EmpNo) REFERENCES Employee, UNIQUE (EmpNo) )SummaryData modeling is an important skill Use notation preciselyPreference for simpler designsConsider alternative designsReview design for common errorsWork many problems