DATA MODELING FUNDAMENTALS A Practical Guide for IT Professionals Paulraj Ponniah DATA MODELING FUNDAMENTALS DATA MODELING FUNDAMENTALS A Practical Guide for IT Professionals Paulraj Ponniah Copyright#2007byJohnWiley&Sons,Inc.Allrightsreserved. PublishedbyJohnWiley&Sons,Inc.,Hoboken,NewJersey PublishedsimultaneouslyinCanada. Nopartofthispublicationmaybereproduced,storedinaretrievalsystem,ortransmittedinany formorbyanymeans,electronic,mechanical,photocopying,recording,scanning,orotherwise, exceptaspermittedunderSection107or108ofthe1976UnitedStatesCopyrightAct,without eitherthepriorwrittenpermissionofthePublisher,orauthorizationthroughpaymentofthe appropriateper-copyfeetotheCopyrightClearanceCenter,Inc.,222RosewoodDrive,Danvers, MA01923,(978)750-8400,fax(978)750-4470,oronthewebatwww.copyright.com. RequeststothePublisherforpermissionshouldbeaddressedtothePermissionsDepartment, JohnWiley&Sons,Inc.,111RiverStreet,Hoboken,NJ07030,(201)748-6011, fax(201)748-6008,oronlineathttp://www.wiley.com/go/permission. LimitofLiability/DisclaimerofWarranty:Whilethepublisherandauthorhaveusedtheirbest effortsinpreparingthisbook,theymakenorepresentationsorwarrantieswithrespectto theaccuracyorcompletenessofthecontentsofthisbookandspecificallydisclaimanyimplied warrantiesofmerchantabilityorfitnessforaparticularpurpose.Nowarrantymaybecreated orextendedbysalesrepresentativesorwrittensalesmaterials.Theadviceandstrategiescontained hereinmaynotbesuitableforyoursituation.Youshouldconsultwithaprofessionalwhere appropriate.Neitherthepublishernorauthorshallbeliableforanylossofprofitorany othercommercialdamages,includingbutnotlimitedtospecial,incidental,consequential, orotherdamages. Forgeneralinformationonourotherproductsandservicesorfortechnicalsupport,please contactourCustomerCareDepartmentwithintheUnitedStatesat(800)762-2974, outsidetheUnitedStatesat(317)572-3993orfax(317)572-4002. Wileyalsopublishesitsbooksinavarietyofelectronicformats.Somecontentthatappearsinprint maynotbeavailableinelectronicformats. FormoreinformationaboutWileyproducts,visit ourwebsiteatwww.wiley.com. LibraryofCongressCataloging-in-PublicationData Ponniah,Paulraj. Datamodelingfundamentals:apracticalguideforITprofessionals/byPaulrajPonniah. p.cm. ISBN-13:978-0-471-79049-5(cloth) ISBN-10:0-471-79049-4(cloth) 1. Databasedesign. 2. Datastructures(Computerscience) I. Title. QA76.9.D26P5742007 005.74--dc22 2006038737 PrintedintheUnitedStatesofAmerica 10 9 8 7 6 5 4 3 2 1 To Daniel Arjun, my dear son-in-law and to Reisha and Shoba, my dear daughters-in-law CONTENTS PREFACE xvii ACKNOWLEDGMENTS xxi I INTRODUCTION TO DATA MODELING 1 1 Data Modeling: An Overview 3 Chapter Objectives / 3 Data Model Defined / 4 What Is a Data Model? / 5 WhyData Modeling? / 6 WhoPerforms Data Modeling? / 9 Information Levels / 10 Classification of InformationLevels / 11 Data Models atInformation Levels / 13 Conceptual Data Modeling / 17 Data Model Components / 18 Data ModelingSteps / 20 Data Model Quality / 26 Significance of Data Model Quality / 27 Data Model Characteristics / 27 Ensuring Data Model Quality / 28 Data System Development / 29 Data System Development Life Cycle / 29 Roles and Responsibilities / 33 Modeling the Information Requirements / 33 Applying Agile Modeling Principles / 34 Data ModelingApproaches and Trends / 35 Data ModelingApproaches / 36 Modeling for Data Warehouse / 38 vii