Transcription

Database SystemsIntroductiontoDatabasesNenadandData WarehousesJukicLoyola University ChicagoSusan VrbskyTheUniversity of AlabamaSvetlozar NestorovUniversity of ChicagoPEARSONBoston ColumbusAmsterdamCapeDelhi MexicoIndianapolisNew York San FranciscoUpper SaddleRiverTown Dubai London Madrid Milan Munich Paris Montreal TorontoCitySao PauloSydney Hong KongSeoulSingapore Taipei Tokyo

CONTENTSPrefacexviiAcknowledgmentsxxiiiAbout the epsin the11Development of Database SystemsDatabase Requirements Collection,Database ModelingDatabaseDatabaseFront-End777Database Administration and MaintenanceThe Next Version of the DatabaseDatabase7People Involved with Database SystemsAnalysts, Designers, and DevelopersFront-EndApplications Analysts and DevelopersDatabase End UsersversusRelational DBMSBookTermsIntroductionBasic EREntities99Analytical Databases9101110Review Questions Operational Databases2 DATABASE89Topics OverviewKeyChapter8DatabaseDatabase AdministratorsPart e UseDefinition, and MENTS AND ER e and Non-Unique)Relationships15Cardinality ConstraintsTypes of RelationshipsRelationships1415(Maximum Cardinality-Wise)and Relationship InstancesRelationship Attributes171819Example: Set of Database Requirements and ER DiagramComposite Attributes22Composite Unique Attribute23Multiple Unique Attributes (Candidate Keys)Multivalued AttributesDerived AttributeOptional Attribute2124252526ix

Example: Entity Containing Various Types of AttributesExact Minimum and MaximumCardinality inUnary Relationships and RelationshipMultiple RelationshipsWeak27RelationshipsRoles2728Between Same Entities3030EntityNaming Conventions for Entities, Attributes, and Relationships 33MultipleERDiagrams33Example: Another Set of Database Requirements and an ER DiagramDatabaseRequirements and ER Model UsageVarious ER NotationsEnhanced ER (EER)3940Relationships with MultipleA Note About M:NSame Entities3536Instances Between the40A Note About Associa tive Entities43Ternary (and Higher Degree) RelationshipsA Note About50SummaryExercises53Key Terms52Mini Cases 52 53Chapter 3 RELATIONAL DATABASE MODELINGIntroduction45Review Questions 5757Relational Database Model: BasicConcepts57Primary Key 59Mapping Entities into RelationsMapping59Composite AttributesEntities withinto Relations60Mapping Entities with Unique Composite Attributes into RelationsMappingOptional Attributes into RelationsEntities withEntity Integrity Constraint6263Foreign KeyMapping Relationships into Relational Database ConstructsMapping 1:M R Diagram intoEntities with Candidateinto RelationsMapping68Integrity ConstraintExample: MappingaRelational SchemaKeys (Multiple Unique Attributes)7172Mapping Entities with Derived Attributes into RelationsExample: Mappinga70Entities with Multivalued Attributes into Relational DatabaseConstructsinto6364Mapping 1:1 RelationshipsReferential6162anEntity Containing Various Types of AttributesRelational Schema74Mapping Unary Relationships74Mapping 1:M Unary RelationshipsMappingM:NUnary RelationshipsMapping 1:1 Unary Relationships747576Mapping Multiple Relationships Between theMapping Weak EntitiesExample: Mapping73Same Entities7677another ERDiagram intoRelational Database Constraints78aRelational Schema78

Implicit Constraints78User-Defined ConstraintsA NoteAbout Mapping Associative EntitiesA Note AboutOption84Designer-Created Primary Keys and the Autonumber84A Note AboutSummaryUPDATEPerforming87Exercises89Both ER and RelationalTermsKey ModelingReview Questions 9191Operation Example91Operation Example92Modify Operation Example92Update Operation Terminology NoteUpdate Anomalies9393Example Scenario93Example Relation (Containing Redundant ionalDependency NotationDependencies Example9899Augmented Functional DependenciesEquivalentFunctionalTypes of ve Functional100101DependencyFunctionalAnother FunctionalDependenciesDependenciesPartial Functional9497Streamlining Functional Dependencies102102DependencyDependencies Example102104First Normal Form(1NF)Second Normal FormThird Normal Form105(2NF)(3NF)Other Normal Forms107108109Eliminating Redundancy and Resolving Update AnomaliesAnother NormalizationA NoteExample114A Note About Denormalization: NormalizationPerformance109112About Normalization Exceptionsversus115A Note About ERModeling versusNormalizationA Note About Designer-Added EntitiesDatabase ContentKey 8991Update OperationsFull8885OPERATIONS, UPDATE ANOMALIES,IntroductionDelete88Mini Cases AND NORMALIZATIONInsert83Mapping Ternary RelationshipsA Note AboutChapter 481Terms116(Tables) and Keys for Streamlining117120 Review Questions120 Exercises120

xiiContentsChapter 5 SQL127Introduction127SQL Commands OverviewData DefinitionLanguage (DDL)Data Control128Language (DCL) and Transaction ControlLanguage (TCL)SQL Data Types128128Brief SQL Syntax NotesCREATE TABLE131INSERT INTO132SELECT128129DROP TABLE134WHERE136DISTINCT137ORDER BY138139Aggregate FunctionsGROUP BYHAVINGNestedIN127Manipulation Language 151Joining Multiple RelationsALTER TABLEUPDATEDELETE152153153154CREATE VIEW and DROP VIEWSet154Operators: UNION, INTERSECT, EXCEPT (MINUS)Additional SQLExamples with Additional SQLCREATE TABLE (Additional Example)INSERT INTO(Additional Example)Constraint ManagementJOIN withoutIS NULLEXISTSNOT157157160163JOIN of a Relation with Itself (Self-JOIN)OUTER JOINCommands160(Additional Examples)SELECT155163164UsingaPrimary Key/Foreign Key Combination166166167Inserting fromaQueryOther SQL Functionalities168168A Note About Inappropriate Use of Observed Values in SQLA Note166About SQL Standard and SQL Syntax DifferencesSQL Syntax DifferenceNote 1: DATE168169and TIME Data Types170

ContentsSQL Syntax Difference Note 2: FOREIGN KEYSQL Syntax DifferenceNote 3:170Usage of AS Keyword with AliasesSQL Syntax Difference Note 4: ALTER TABLESQL Syntax DifferenceNote 5: SetSQL Syntax Difference NoteSQL Syntax DifferenceKey Terms174ExercisesChapter1717:Review QuestionsMini Cases 174Delete175Integrity Constraint: Delete and Update ImplementationOptions178181Implementing Delete and Update OptionsImplementingUser-Defined ConstraintsCHECK Clause185Implementing User-Defined Constraints187IndexingDatabase Front-EndQuality192Issues197A Note About Assertions andKeyTerms204 Analytical DatabasesTriggers202Review QuestionsIntroductionAnalyticalOperational InformationMakeup DifferencesTechnical DifferencesFunctional ubject Oriented213Enterprise-WideHistorical213213Time VariantRetrieval of213Analytical Information213Detailed and/or Summarized Data214Data WarehouseComponentsSystemsData WarehouseETL207209The Data Warehouse DefinitionSource 207versusStructured204207DATA WAREHOUSING CONCEPTSData184185Other Mechanisms for7177177Update OptionsChapter 177OptionsData1731736 DATABASE IMPLEMENTATION AND USEReferential172Constraint ManagementNote 8: GROUP BY 175IntroductionPart 2172OperatorsDifference Note 6: FULL OUTER JOINSQL Syntax170214215215216Data Warehouse Front-End(Bl) Applications216Exercises204186xiii

xivContentsData Marts216Steps in Development of Data Warehouses 217Requirements Collection, Definition, and VisualizationData WarehouseCreating the220ModelingData WarehouseETL InfrastructureCreating220220Developing Front-End (Bl) ApplicationsData WarehouseDeploymentDate Warehouse Use220221221Data Warehouse Administration and MaintenanceThe Next Version of the Data WarehouseKeyChapterTerms222218221Review Questions 2212228 DATA WAREHOUSE AND DATA MART MODELINGIntroduction225DimensionalInitialModeling: Basic ConceptsExample:225Dimensional Model Based onaSingleCharacteristics of Dimensions and Facts and theExampleTransaction Time in the Fact TableMultiple Fact arity of theLine-Item243248248Transaction-Level Detailed Fact TableDimensions andTimestampsApproach250251Type 3 Approach252Snowflake ModelModeling Issues253254254Data Warehouse (Data Mart)Modeling ApproachesNormalized Data WarehouseExample of a NormalizedData Warehouse256259Example of a Dimensionally Modeled WarehouseIndependentData MartsA Note About260263Comparing Dimensional Modeling and ER ModelingTechniques 264Data Warehouse/Data Mart DesignTermsExercises255255Dimensionally Modeled Data WarehouseKey249250Additional DimensionalCubes245Fact TableType 1 Approach2240243Aggregated Fact TableversusSlowly Changing237Fact TablesAggregatedFact TableAggregated235in a Dimensional ModelDetailed Fact TableAnof the Initial234Transaction Identifier in the Fact TableAnAnalysis226231Additional Possible Fact AttributesTypeSource229Expanded Example: Dimensional Model BasedSources225265266 Review QuestionsMini Cases272265 as

ContentsChapter9 DATA WAREHOUSE IMPLEMENTATION AND USEIntroductionCreating 71:a273Data Warehouse273Extraction, Transformation, LoadOnline Analytical Processing (OLAP)OLAP/BI ToolsSlice and Dice283Pivot285(Rotate)Drill Down and Drill280282286UpAdditional OLAP/BI ToolsOLAP/BI Tools275281OLAP/BI Tools FunctionalitiesFunctionalityExecutive DashboardData WarehouseNotes(Bl) ApplicationsDeployment292A Note About OLAP/BI Tools Data ArchitecturePart 3293Options295295ROLAP296HOLAP297TermsOther TopicsChapter289292A Note About OLAP/BI Tools Database ModelsKey288288PurposeData Warehouse/Data Mart Front-EndMOLAP273297Review Questions 297Exercises 29930110 OVERVIEW OF DBMS FUNCTIONALITIES ANDDATABASE ADMINISTRATIONIntroduction301301DBMS Components301Database Administration Overview302Monitoring and Maintaining the Database SystemData302303DictionarySecuring the Database Against Unauthorized Access 305DatabaseProvidingBackup and RecoveryEnsuring Database IntegrityOptimizing306307Database Performance307Developing and Implementing Database PoliciesStandardsKeyand308Terms309Review Questions 309AppendicesAppendixA ENHANCED ER311AppendixB FURTHER NOTES ON NORMALIZATION AND HIGHERNORMAL FORMSAppendix CAppendix317ENTERPRISE RESOURCE PLANNING(ERP)D DATA GOVERNANCE AND MASTER DATAMANAGEMENT326323xv

xviContentsAppendixE OBJECT-ORIENTED DATABASESAppendixF DISTRIBUTEDDATABASES, PARALLEL DATABASES,AND CLOUD COMPUTINGAppendix G DATA MININGAppendixH XMLAppendixGlossaryIndex361369343346I NOSQL DATABASESAppendix J BIG DATA330356353336

Introduction 273 Creatinga Data Warehouse 273 71:Extraction, Transformation,Load 275 OnlineAnalyticalProcessing(OLAP) 280 OLAP/BITools 281 OLAP/BIToolsFunctionalities 282 SliceandDice 283 Pivot(Rotate) 285 Drill DownandDr