MODERN
`
`
`
`
`
`DATABASE
`
`MANAGEMENT
`
`
`
`oe
`
`JEFFREYA HOFFER
`Univversity ofDayton
`| ARY B PRESCOTT
`
`University of Tampa
`
`FRED R. McFADDEN
`University ofColorado—ColoradooSprings
`
`|
`
`Prentice
`:
`BENT
`:
`; aeeeef
`Upper Saddle River, New Jersey 07458
`
`
`EVERNOTEEx. 1019
`
`10f5
`
`

`

`Eprrorray,ASSIstany: Maat Van Uitert
`MEvia Project Manacrr: Joan Waxman
`SENIOR MarketingManacrr: Sharon Turkoyich’
`Marketing Assistany- Scott Patterson
`MANAciIngEviror (Propucrroy). Cynthia Regan
`Propucrion Evrror: Michae]Reynolds
`PropucrionAsstsrany: DianneFalcone
`PERMISsIons SUPERVisoR: Suzanne Grappi
`AssociaryDirtcror, Manuracruring. VincentScelta
`PRopucrion Manacrp- Amold Vila
`Desren Direcror: Patricia Smythe
`
`a
`
`MANacrr, Mutrimepra Propuction: ChristyMahon
`
`McFadden.—¢;h ed,
`p.
`cm.
`
`DatabaseManagement.
`
`Ul. Title
`QA76.9.D3 M395 900]
`005.74—ae9]
`
`I Hoffer,Jeffrey4,
`
`I. Prescoit,MaryB.
`
`Copyright©2002byPearsonEducation,Inc,,UpperSaddleRiver,NewJersey,07458,
`
`
`
`recording, or likewise, For
`d Permissions Department,
`
`09876 5439
`’ ISBN 9-13-033969_5
`
`20f5
`
`

`

`
`
`
`|oe CHAPTER 1
`
`THE DATABASE ENVIRONMENT
`
`building separate databases, called “data warehouses,” for this type of decision sup-
`port application (Lambert, 1996).
`.
`*
`,
`Use ofdatabases to support customer relationship management, on-line shop-
`ping, and employee relationship managementis increasingly important. ‘Databases:
`are fundamental to most information systems now, from small databases used in per
`sonal digital assistants and information appliances to the verylarge databases that
`support enterprise-wide information systems.
`Although the future of databasesis assured, much work remains to be done.
`Manyorganizations have a proliferation of incompatible databases that were devel
`oped to meet immediate needs, rather than based on a plannedstrategy or a well-
`managed evolution. Much ofthe data are trappedin older, “legacy” systems, and the”
`data are often ofpoorquality. New skills are required to design data warehouses, and
`thereisacritical shortage ofskills in areas such as database analysis, database design.
`data administration, and database administration. We address these and other
`importantissues in this textbook.
`.
`3
`A course in database management has emerged as one of the most important
`courses in the information systems curriculum today. As an information systems pro-
`fessional, you must be prepared to analyze database requirements and design and
`implementdatabases within the contextof information systems development. You
`must be prepared as well to consult with end-users and show them how they can use
`databases (or data warehouses) to build decision support systems and executive
`informationsystems for competitive advantage. And, the widespread use of databases
`attached to Websites in order to return dynamic information to users of the Website
`requires that you understand not only how to attach databases to the Web,but also
`how to secure those databases so that their contents may be viewed but not compro-
`mised by outside users.
`In this chapter we introducethe basic concepts of databases and database man-
`agement systems (DBMS). We describe traditional file management systems and
`some of their shortcomingsthat led to the database approach. We describe the range
`of database applications, from personal computers and digital assistants to work-
`group, departmental, and enterprise databases. Next we consider the benefits, costs,
`and risks of using the database approach. We conclude the chapter with a summary
`of the evolution ofdatabase systems and ofthe range of technologies used to build,
`use, and manage databases. This chapteris intended to serve as a previewof the top-
`ics in the remainderof the text.
`
`Database: An organized collection
`oflogicallyrelated data.
`
`BASIC CONCEPTS AND DEFINITIONS
`
`We define a database as anorganized collection of logically related data. A
`database maybeofany size and complexity. For example, a salesperson may main-
`tain a small database of customer contacts on her laptop computer that consists
`of a few megabytes of data. A large corporation may build a very large database
`consisting of several terabytes of data (a terabyte is a trillion bytes) on a large
`mainframe computerthat is used for decision support applications (Winter,
`1997). Very large data warehouses contain more than a petabyte of data (a petabyte
`is a quadrillion bytes). (We assume throughout the text that all databases are
`computer-based.)
`
`Data
`
`Historically, the term data referred to knownfacts that could be recorded and
`stored on computer media. For example in a salesperson’s database, the data would
`include facts such as customer name, address, and telephone number. This defini-
`
`3 0f 5
`
`

`

`Aimese
`
`CHAPTER 1
`
`THE DATABASE ENVIRONMENT
`
`Conversion Costs
`
`,
`
`a
`
`The termlegacysystems is widely used to refer to older applicationsin an o
`zation that are based onfile processing and/or older database technology. The
`of converting these older systems to modern database technology—measure
`terms of dollars, time, and organizational commitment—mayoften seem prohik
`to an organization.As will be shown in Chapter 11, the use of data warehousesis
`strategy for continuing to use older systems while at the same time exploiting a
`ermdatabase technology and techniques (Ritter, 1999),
`
`Need for Explicit Backup and Recovery
`A shared corporate database must be accurate and available at all times.
`requires that comprehensive procedures be “developed and used for provi
`backup copies of data and for restoring a database when damage occurs. A moe
`database managementsystem normally automates many more of the backup
`recovery tasks thanafile system. We describe procedures for security, backup.
`recovery in Chapter12.
`
`Organizational Conflict
`
`A shared database requires a consensus on data definitions and ownershi
`well as responsibilities for accurate data maintenance. Experience has shown
`conflicts on data definitions, data formats and coding, rights to update shared d
`andassociated issues are frequent andoften difficult to resolve. Handling t
`issues requires organizational commitment to the database approach, organizat
`ally astute database administrators, and a sound evolutionary approachto datak
`development.
`If strong top management support of and commitmentto the database appro
`is lacking, end-user development of stand-alone databasesis likely to prolife
`These databases do notfollow the general database approachthat we have descril
`and theyare unlikely to provide the benefits describedearlier.
`
`COMPONENTS OF THE DATABASE
`ENVIRONMENT
`
`The major components ofa typical database environment and their relati
`ships are shownin Figure 1-10. You have already been introduced to some (but
`all) of these components in previoussections. Followingis a brief description of
`nine components shown in Figure 1-10.
`1. Computer-aided software engineering (CASE) tools Automated tools v
`to design databases and application programs. We describe the use of C:
`tools for database design and developmentthroughoutthe text.
`2. Repository Centralized knowledge base for all data definitions, data r
`tionships, screen and report formats, and othersystem components. A rep
`itory contains an extended set of metadata important for managing databa
`as well as other components of an information system. We describe the rep
`itory in Chapter 12.
`3. Database management system (DBMS) Commercial software (and occasi
`ally, hardware and firmware) system used to define, create, maintain,
`2
`provide controlled access to the database and also to the repository.
`describe the functions of a DBMSin Chapters 12 and 13.
`
`40f5
`
`Repository: A centralized knowl-
`edge base ofall data definitions, data
`relationships, screen and report for-
`mats, and other system components.
`
`system
`Database management
`(DBMS): A software application that
`is used to create, maintain, and pro-
`vide controlled access to user data-
`bases.
`
`

`

`THE RELATIONAL DATA MODEL
`
`Therelational data model wasfirst introduced in 1970 by E. E Codd,the
`
`IBM (Codd, 1970). Two early research projects were launchedto provethe feas
`of the relational model and to develo
`
`pment of System R (a prototype
`The second,at the University of
`fornia at Berkeley, led to the development
`of Ingres, an academically orie
`RDBMS. Commercial RDBMS products from
`numerous vendors started to ap
`about 1980 (see the Website for this book for |
`inks to RDBMSand other DBMS
`dors). Today RDBMSs have become the domi
`nant technology for database
`ma
`ment, and there are literally hundreds of
`RDBMSproducts for computers
`from personal computers to mainframes.
`
`
`
`
`
`
`|16a CHAPTER 5 LOGICAL DATABASE DESIGN AND THE RELATIONAL MODEL
`
`
`
`tional data model.) We next describe and illustrate the process of transform
`E-R modelinto the relational model. Many CASE tools support this transfor
`today: however,it is important that you understand the underlying principle
`
`
`procedures, We then describe the concepts of normalization in detail, No
`tion, which is the process ofdesigning well-structured relations, is an importar
`
`ponentof logical design for the relational model. Finally, we describe how to
`relations while avoiding commonpitfalls that May occurin this process.
`
`The objective of logical database design is to translate the conceptual
`(which represents an organization’s requirements for ‘data) into a logical é
`design that can be implemented on a chosen database managementsyster
`resulting databases must meet user needs for data sharing, flexibility, and =
`
`
`access. The concepts presentedin this chapterare essential to your understand
`the database developmentprocess,
`
`Relation: A named two-dimensional
`table of data.
`
`Basic Definitions
`Therelational data model'tepresents data in the form oftables. The relat
`
`model is based on mathematical theory and therefore has a solid theoreticalfo
`tion. However, we need only a few simple concepts to describe the relational me
`
`andit is therefore easily understood and used by those unfamiliar with the unc =
`ing theory. Therelational data model consists of the following three compone
`
`(Fleming and von Halle, 1989):
`1. Data structure Data are organized in the form of tables with rows.
`columns.
`
`2. Data manipulation Powerful operations (using the SQL language) are »
`4
`to manipulate data stored in the relations.
`
`3. Data integrity . Facilities are included to specify business rules that mains
`the integrity of data when they are manipulated.
`
`We discuss data structure and data integrity in this section. Data manipulation is
`cussed in Chapters7, 8, and 10.
`
`Relational Data Structure A relation is a named, two-dimensional table of &
`Eachrelation (or table) consists of a set of named columns and an arbitrary num
`ofunnamed rows. An attribute, consistent withits definition in Chapter 3, is a nan
`w of a relation corresponds to a record that cont
`data (attribute) values for a single entity. Figure 5-1 shows an example of a relas
`
`named EMPLOYEE]. This relation contains the following attributes describ:
`employees: Emp_ID, Name, Dept_Name, and Salary. Thefive rows of the table cor
`
`spond to five employees.Itis importantto understand that the sample data in Fig
`
`5 of 5
`
`