WIXLIB

Chapter 9:Database SystemsComputer Science: An OverviewEleventh EditionbyJ. Glenn BrookshearCopyright 2012 Pearson Education, Inc.Chapter 9: Database Systems 9.1 Database Fundamentals9.2 The Relational Model9.3 Object-Oriented Databases9.4 Maintaining Database Integrity9.5 Traditional File Structures9.6 Data Mining9.7 Social Impact of Database TechnologyCopyright 2012 Pearson Education, Inc.0-21

DatabaseA collection of data that is multidimensionali thinthe sense ththatt internali tl lilinksk bbetweentititsentries make the information accessiblefrom a variety of perspectivesCopyright 2012 Pearson Education, Inc.0-3Figure 9.1 A file versus a databaseorganizationCopyright 2012 Pearson Education, Inc.0-42

Figure 9.2 The conceptual layers of adatabase implementationCopyright 2012 Pearson Education, Inc.0-5Schemas Schema: A description of the structure ofan entireti database,d t buseddbby ddatabaset bsoftware to maintain the database Subschema: A description of only thatportion of the database pertinent to aparticular useruser’ss needs, used to preventsensitive data from being accessed byunauthorized personnelCopyright 2012 Pearson Education, Inc.0-63

Database Management Systems Database Management System (DBMS): Asoftware layer that manipulates a database inresponse to requests from applications Distributed Database: A database stored onmultiple machines– DBMS will mask this organizational detail from itsusers DDatat iindependence:ddTh abilityThebilit tto changehththeorganization of a database without changing theapplication software that uses itCopyright 2012 Pearson Education, Inc.0-7Database Models Database model: A conceptual view of ad t bdatabase– Relational database model– Object-oriented database modelCopyright 2012 Pearson Education, Inc.0-84

Relational Database Model Relation: A rectangular table– Attribute: A column in the table– Tuple: A row in the tableCopyright 2012 Pearson Education, Inc.0-9Figure 9.3 A relation containingemployee informationCopyright 2012 Pearson Education, Inc.0-105

Relational Design Avoid multiple concepts within one relation– Can lead to redundant data– Deleting a tuple could also delete necessarybut unrelated informationCopyright 2012 Pearson Education, Inc.0-11Improving a Relational Design Decomposition: Dividing the columns of arelationl ti iintot ttwo or more relations,l tiduplicating those columns necessary tomaintain relationships– Lossless or nonloss decomposition: A“correct” decomposition that does not lose anyinformationCopyright 2012 Pearson Education, Inc.0-126

Figure 9.4 A relation containingredundancyCopyright 2012 Pearson Education, Inc.0-13Figure 9.5 An employee databaseconsisting of three relationsCopyright 2012 Pearson Education, Inc.0-147

Figure 9.6 Finding the departments inwhich employee 23Y34 has workedCopyright 2012 Pearson Education, Inc.0-15Figure 9.7 A relation and a proposeddecompositionCopyright 2012 Pearson Education, Inc.0-168

Relational Operations Select: Choose rows Project: Choose columns Join: Assemble information from two ormore relationsCopyright 2012 Pearson Education, Inc.0-17Figure 9.8 The SELECT operationCopyright 2012 Pearson Education, Inc.0-189

Figure 9.9 The PROJECT operationCopyright 2012 Pearson Education, Inc.0-19Figure 9.10 The JOIN operationCopyright 2012 Pearson Education, Inc.0-2010

Figure 9.11 Another example of theJOIN operationCopyright 2012 Pearson Education, Inc.0-21Figure 9.12 An application of theJOIN operationCopyright 2012 Pearson Education, Inc.0-2211

Structured Query Language (SQL) Operations to manipulate tuples– insert– update– delete– selectCopyright 2012 Pearson Education, Inc.0-23SQL Examples select EmplId, Deptfrom ASSIGNMENTASSIGNMENT, JOBwhere ASSIGNMENT.JobId JOB.JobIdand ASSIGNMENT.TermData “*” insert into EMPLOYEEvalues (‘43212’,( 43212 , ‘SueSue A. Burt’,Burt ,’33 Fair St.’, ‘444661111’)Copyright 2012 Pearson Education, Inc.0-2412

SQL Examples (continued) delete from EMPLOYEEwhere Name ‘GG. Jerry Smith’Smith update EMPLOYEEset Address ‘1812 Napoleon Ave.’where Name ‘Joe E. Baker’Copyright 2012 Pearson Education, Inc.0-25Object-oriented Databases Object-oriented Database: A databaseconstructedt t d byb applyingl i ththe object-orientedbj t i t dparadigm– Each entity stored as a persistent object– Relationships indicated by links betweenobjects– DBMS maintains inter-object linksCopyright 2012 Pearson Education, Inc.0-2613

Figure 9.13 The associationsbetween objects in an objectoriented databaseCopyright 2012 Pearson Education, Inc.0-27Advantages of Object-orientedDatabases Matches design paradigm of objectorientedi t d applicationsli ti Intelligence can be built into attributehandlers Can handle exotic data types– Example: multimediaCopyright 2012 Pearson Education, Inc.0-2814

Maintaining Database Integrity Transaction: A sequence of operations thatmust all happen together– Example: transferring money between bank accounts Transaction log: A non-volatile record of eachtransaction’s activities, built before thetransaction is allowed to execute– Commit point: The point at which a transaction hasbeen recorded in the log– Roll-back: The process of undoing a transactionCopyright 2012 Pearson Education, Inc.0-29Maintaining database integrity(continued) Simultaneous access problems– Incorrect ssummarymmar problem– Lost update problem Locking preventing others fromaccessing data being used by atransaction– SharedSlock: used when reading data– Exclusive lock: used when altering dataCopyright 2012 Pearson Education, Inc.0-3015

Sequential Files Sequential file: A file whose contents canonlyl bbe readd iin orderd– Reader must be able to detect end-of-file(EOF)– Data can be stored in logical records, sortedby a key field Greatly increases the speed of batch updatesCopyright 2012 Pearson Education, Inc.0-31Figure 9.14 The structure of a simpleemployee file implemented as a text fileCopyright 2012 Pearson Education, Inc.0-3216

Figure 9.15 A procedure for mergingtwo sequential filesCopyright 2012 Pearson Education, Inc.0-33Figure 9.16Applying the mergealgorithm (Lettersare used torepresent entirerecords.The particular letterindicates the valueof the record’skey field.)17

Indexed Files Index: A list of key values and the locationoff theirth i associatedi t d recordsdCopyright 2012 Pearson Education, Inc.0-35Figure 9.17 Opening anindexed fileCopyright 2012 Pearson Education, Inc.0-3618

Hashing Each record has a key field The storage space is divided into buckets A hash function computes a bucketnumber for each key value Each record is stored in the bucketcorresponding to the hash of its keyCopyright 2012 Pearson Education, Inc.0-37Figure 9.18 Hashing the key fieldvalue 25X3Z to one of 41 bucketsCopyright 2012 Pearson Education, Inc.0-3819

Figure 9.19 The rudiments of ahashing systemCopyright 2012 Pearson Education, Inc.0-39Collisions in Hashing Collision: The case of two keys hashing toth same buckettheb k t– Major problem when table is over 75% full– Solution: increase number of buckets andrehash all dataCopyright 2012 Pearson Education, Inc.0-4020

Data Mining Data Mining: The area of computerscienceiththatt dealsd l withith didiscoveringipatterns in collections of data Data warehouse: A static data collectionto be mined– Data cube: Data presented from manyperspectives to enable miningCopyright 2012 Pearson Education, Inc.0-41Data Mining Strategies Class descriptionClass discriminationCluster analysisAssociation analysisOutlier analysisSequential pattern analysisCopyright 2012 Pearson Education, Inc.0-4221

Social Impact of DatabaseTechnology Problems– Massive amounts of personal data are being collected Often without knowledge or meaningful consent of affectedpeople– Data merging produces new, more invasiveinformation– Errors are widely disseminated and hard to correct Remedies– Existing legal remedies often difficult to apply– Negative publicity may be more effectiveCopyright 2012 Pearson Education, Inc.0-4322

Database Management Systems Database Management System(DBMS): A software layer that manipulates a database insoftware layer that manipulates a database in response to requests from applications Distributed Database:A database stored on multiple machines – DBMS will mask this org