DatabaseManagementSyStemS

DeSigning & BuilDing BuSineSS ApplicAtionS

Gerald V. Post

Fourth Edition

DatabaseManagementSystemsDesigning and BuildingBusiness Applications

Version 4.0.0

Gerald V. PostUniversity of the Pacific

Database Management SystemsDesigning and Building Business Applications

Copyright © 2007 by Gerald V. PostAll rights reserved. No part of this publication may be reproduced or distributed in any form or stored in any database or retrieval system without the prior written consent of Gerald V. Post.

Students:Your honesty is critical to your reputation. No company wants to hire a thief—particularly for jobs as critical as application development and database adminis-tration. If someone is willing to steal something as inexpensive as an e-book, how can that person be trusted with billions of dollars in corporate accounts?

You are not allowed to “share” this book in any form with anyone else. You can-not give or sell any information from this publication in any form to anyone else.

To purchase this book or other books: http://JerryPost.com

To my wife Sarah and to Jessie and Simon who would rather chase my mountain bike than watch me write. The Pet Store case is named in memory of Sarah’s mother.

vPreface

v

PrefaceGoals and Philosophy

The goal of this text is straightforward: At the end of the text, students should be able to evaluate a business situation and build a database application.

Building an application requires four basic steps. The book is organized by these four steps: (1) Database Design, (2) SQL Queries, (3) Application forms and reports, and (4) Database administration.

The first two steps (design and SQL) are standard for almost every database management system. Normalization shows how to carefully design databases to gain the strengths of the database approach. SQL is a standard query language that is used for virtually every step of application development. These two topics must be covered carefully and thoroughly, particularly because they are both difficult topics for students.

The concepts of forms and reports are relatively standard, but every database system has different tools to build applications. Similarly, database administrators perform relatively standard tasks at every company, but the tools are different for every system. The main textbook explains the basic tasks in neutral terms that ap-ply to every database management system.

WorkbooksStudents need to do more than learn the basic concepts and theory. They need to actually build an application. A database management system (DBMS) is a com-plex tool with thousands of options and individual quirks. The accompanying workbooks are designed to show students how to perform the basic tasks in each chapter for a specific DBMS. The workbooks show how to build a database appli-cation for a specific company. The first step is to choose a specific DBMS. Later, students can work through additional workbooks to see the differences in DBMS technologies.

The main textbook sets the overall structure and explains how databases work and why you need to perform certain tasks. The individual workbooks show you how to build an application using a specific tool. If you want an even more de-tailed step-by-step approach, you can purchase print copies of two books pub-lished by Prentice-Hall: Introduction to Oracle and Introduction to SQL Server; both by James Perry and Gerald Post.

Learning AssessmentLearning assessment is important to students as well as faculty and employers. Students need to determine what aspects they are strong in and which ones need additional work. Students need to understand that if they can successfully learn this material, they will have acquired several skills that will get them jobs and help them contribute to businesses by being able to quickly build and maintain business applications.

Learning assessment in this book is straightforward: At the end of the book, students should be able to analyze a business situation and develop a database application. The complexity of the application and tools used will depend on the specific class and the background of the students. Students should develop a term-project as part of a course. Several sample projects are included as a separate proj-ect book and several more are in the workbook. The project provides an excellent

viPreface

opportunity to assess overall learning. The final project can be evaluated in terms of (1) correctly meeting the business needs, (2) an efficient database structure, and (3) usability.

It is also useful to assess individual skills independently—particularly if groups are used to create the final project. In this case, assessment consists of individual exams for (1) database design and normalization, (2) SQL and creating queries from business questions, and (3) selected topics including database programming, security, data mining, and distributed systems.

OrganizationThe organization of the text follows the basic steps of ap-plication development: design, queries, applications, admin-istration, and advanced topics. Some instructors might prefer to teach queries before database design, so the initial chapters are written with that flexibility.

The introduction explains the importance of databases and relates database applications to topics the students have likely seen in other classes.

The section on database de-sign has two chapters: Chapter 2 on general design techniques (systems techniques, diagram-ming, and control) and Chapter 3, which details data normaliza-tion. The objective is to cover design early in the term so that students can get started on their end-of-term projects. Students should use the online Database Design system to work on exercises for both chapters to obtain feedback.

Queries are covered in two chapters. Chapter 4 introduces queries and focuses on the fundamentals of converting business questions to SQL queries. Chapter 5 discusses more complex queries. including subqueries and outer joins.

Part 3 describes the development of database applications, beginning with the essentials of building forms and reports in Chapter 6. Chapter 7 examines the common problems created in a multiuser environment. It explains the techniques used to handle data integrity and transactions. Chapter 8 shows how to put every-thing together to build a complete application. Chapter 9 explains why analytical processing requires a different database configuration than transaction processing. It covers the main tools for analysis and data mining in a nonstatistical context.

Part 4 examines various topics in database administration. Chapter 10 exam-ines management issues emphasizing planning, implementation, performance, and security. It explains the major tasks and controls needed by an administrator. Chapter 11 investigates the growing importance of providing distributed access to databases. It examines the impact of various network configurations. Chapter 12

Chapter 1: Introduction

Part 1: Systems Design Chapter 2: Database Design Appendix: Database Design System Chapter 3: Data Normalization Appendix:FormalDefinitionsofNormalization

Part 2: Queries Chapter 4: Data Queries Appendix: SQL Syntax Chapter 5: Advanced Queries and Subqueries Appendix: Introduction to Programming

Part 3: Applications Chapter 6: Forms and Reports Chapter 7: Database Integrity and Transactions Chapter 8: Applications Chapter 9: Data Warehouses and Data Mining

Part 4: Database Administration Chapter 10: Database Administration Chapter 11: Distributed Databases Chapter 12: Physical Data Storage

viiPreface

leans toward computer science when it looks at how the DBMS physically stores data. The last two chapters can be difficult to cover in a single-term course, but are presented at an introductory level.

Additionally, four chapters have appendixes that discuss programming concepts that are more technical. The appendix to Chapter 2 describes the online database design system that is available to instructors and students. It provides immedi-ate feedback on database designs, making it easier for students to understand the problems and explore different designs. The appendix to Chapter 3 presents the formal definitions of normalization. They are provided for instructors and students who want to see the more formal set-theory definitions. The appendix to Chapter 4 is a convenient list of the primary SQL statements. The appendix to Chapter 5 provides an introduction to programming. It is designed as a summary or simple reminder notes.

PedagogyThe educational goal of the text is straightforward and emphasized in every chap-ter: By the end of the text, students should be able to build business applications using a DBMS. The text uses examples to apply the concepts described in the text. Students should be encouraged to apply the knowledge from each chapter by solv-ing the exercises and working on their final projects.

Each chapter contains several sections to assist in understanding the material and in applying it to the design and creation of business applications:

What You Will Learn in This Chapter• . A list of questions that are an-swered within the chapter. Each question is echoed at the start of a section.A Developer’s View• . A student’s perspective of the chapter contents.Chapter Summary• . A brief review of the chapter topics.A Developer’s View• . A short summary of how the material in the chapter applies to building applications.Key Words• . A list of words introduced in the chapter. A full glossary is pro-vided at the end of the text.Additional Reading• . References for more detailed investigation of the topics.Website References• . Some sites provide detailed information on the topic. Some are newsgroups where developers share questions and tips.Review Questions• . Designed as a study guide for the exams, with a focus on the major topics within the chapter.Exercises• . Problems that apply the concepts presented in the chapter. Most require the use of a DBMS.Projects• . Several longer projects are available in a separate online docu-ment. They are suitable for an end-of-term project.Workbooks• . Each workbook outlines the steps to build an application using a specific DBMS. Each workbook chapter illustrates tasks that match the discussion in the textbook. The workbook also provides exercises to build six other databases for different companies.

viiiPreface

Sample Databases• . Three sample databases are provided to illustrate the concepts. Sally’s Pet Store illustrates a database in the early design stag-es, whereas Rolling Thunder Bicycles presents a more finished application, complete with realistic data. Corner Med is a database for a neighborhood medical facility that tracks patient and physician interactions. Exercises for all three databases are provided in the chapters. The sample databases can be installed in several DBMS formats.

Features of the Text1. Focus on modern business application development.

Database design explained in terms of business modeling.• Application hands-on emphasis with many examples and exercises.• Emphasis on modern graphical user interface applications.• Chapters on database programming and application development.• Introduction to data mining.• Some answers to exercises are provided to students through the online sys-• tem. The exercises with answers are highlighted in the text with a light bulb icon. Sometimes students need to see a solved problem.

2. Hot topics.Description and use of the unified modeling language (UML) for model-• ing and system diagrams. This new standard will soon be required for all designers.In-depth discussion of security topics in a database environment.• Development of databases for the Internet and intranets.• Emphasis on SQL 92, with an introduction to SQL 99 and the XML features • of SQL 2003.Integrated applications and objects in databases.•

3. Applied business exercises and cases.Many database design problems.• Exercises covering all aspects of application development.• Sample cases suitable for end-of-term projects.•

4. A complete sample database application (Rolling Thunder Bicycles).Fully functional business database.• Sample data and data generator routines.• Program code to illustrate common database operations.•

5. Two additional databases (Sally’s Pet Store and Corner Med) for comparison and additional assignments.

6. Lecture notes as PowerPoint slide show.7. Hundreds of database exercises and problems for students to work on. 8. Workbooks built for specific database technologies that illustrate the hands-

on steps needed to build an actual application. Check the online site for ver-sions of the workbooks for additional systems.

ixPreface

End-of-Term ProjectsSeveral projects are described in the project document available online. These cases are suitable for end-of-term projects. Students should be able to build a complete application in one term. The grading focus should be on the final proj-ect. However, the instructor should evaluate at least two intermediate stages: (1) a list of the normalized tables collected shortly after Chapter 3 is completed and (2) a design preview consisting of at least two major forms and two reports collected shortly after Chapter 6. The six additional cases in the workbook can also be used as an end-of-term project.

Some instructors may choose to assign the projects as group assignments. However, it is often wiser to avoid this approach and require individual work. The project is a key learning tool. If some members of the group avoid working on the project, they will lose an important learning opportunity.

Database Design and the Unified Modeling LanguageFor several years, entity-relationship diagrams were the predominant modeling technique for database design. However, this approach causes problems for in-structors (and students) because there are several different diagramming tech-niques. This edition continues to help solve these problems by incorporating the Unified Modeling Language (UML) method, instead of traditional entity-rela-tionship (ER) diagramming, as the modeling technique for database design. This change will be most apparent in the replacement of the ER diagram notation and terminology with the parallel concepts in UML class diagrams.

CustomerIDLastNameFirstNamePhone…

EmployeeIDLastNameFirstNameCommission…

OrderIDOrderDateEmployeeIDCustomerID…

ItemIDDescriptionPrice…

CustomerIDContactTerms…

CustomerIDContactAgency…

WarehouseLocation…

ShipIDShipiDate…

Customer

Order

Corporation Government

WarehouseShipment

SalesPerson

Item

Shipment

1…1

0…*1…1

0…*

0…*

0…*

0…*0…*

1…1

1…1

1…*

0…1

UML class diagram.

xPreface

UML class diagrams, although very similar to ER diagrams, are superior in several ways. First, they are standardized, so students (and instructors) need learn only one set of notations. Second, they are “cleaner” in the sense that they are eas-ier to read without the bubbles and cryptic notations of traditional ER diagrams. Third, they provide an introduction to object-oriented design, so students will be better prepared for future development issues. Fourth, with the rapid adoption of UML as a standard design methodology, students will be better prepared to move into future jobs. Many of the systems design organizations have adopted UML as a standard method for designing systems. UML has the support of major authors in systems design (e.g., Booch, Rumbaugh, and Jacobsen) as well as being supported by the major software development firms including IBM, Microsoft, and Oracle. Note that Microsoft Access and SQL Server both use a diagramming tool that is similar to UML. In addition, students should have little difficulty transferring their knowledge of the UML method if they need to work with older ER methods.

The basic similarities between ER and class diagrams are (1) entities (class-es) are drawn as boxes, (2) binary relationships (associations) are drawn as con-necting lines, and (3) N-ary associations (relationships) are drawn as diamonds. Hence, the overall structures are similar. The main differences between UML and ER diagrams occur in the details. In UML the multiplicity of an association is shown as simple numerical notation instead of as a cryptic icon. An example is shown in the accompanying figures.

UML also has provisions for n-ary associations and allows associations to be defined as classes. There are provisions for naming all associations, including di-rectional names to assist in reading the diagram. Several situations have defined icons for the association ends, such as composition (rarely handled by ER) and subtypes (poorly handled by ER).

Customer

SalesPersonOrder

ItemCorporation Government

Warehouse

Shipment

CustomerID

LastName

FirstName

Phone

OrderID

OrderDate

EmployeeID

CustomerID

CustomerID

ContactTerm

s

CustomerID

ContactAgency

Is A

EmployeeID

LastName

FirstName

Commission

ItemID

DescriptionPrice

Warehouse

Location

Corresponding Entity Relationship diagram.

xiPreface

More details of the UML approach are shown in Chapters 2 and 3. Only a small fraction of the UML diagrams, notation, and terminology will be used in the data-base text. You can find the full specification on the Web at http://www¬306.ibm.com/software/rational.com/uml/.

Instructional SupportThe online Database Design expert system• . It has been statistically proven to help students learn database design. It provides immediate feedback to students. It saves hours of instructor grading time. It contains over 100 de-sign problems that can be used for teaching or testing.A test bank• with multiple choice and short answer questions.Lecture notes• and overheads are available as slide shows in Microsoft Pow-erPoint format. The slides contain all the figures and additional notes. The slides are organized into lectures and can be rearranged to suit individual preferences.The sample databases• and solutions can be downloaded from the online site. The instructor can add new data, modify the exercises, or use them to expand the discussion in the text. The databases are provided for several DBMSs.An online discussion forum• for registered faculty members. A place to dis-cuss teaching ideas, ask for help, or make suggestions.The Instructor’s Manual• contains answers to the exercises.

The Online SystemTo students, the most important change in the fourth edition is placing everything online. The main reason for this move is cost. The main textbook and the work-books were rewritten and expanded. The costs and student prices for the com-bined print books were out of line. Electronically, it is possible to make the entire set available for an almost trivial fee. It is impossible to illustrate multiple DBMSs within a single textbook. Splitting the workbooks from the main text makes it possible to cover a variety of DBMSs—without confusing the reader. The DBMS market is becoming increasingly fragmented, and students need (1) a relatively agnostic main textbook to describe the common features, and (2) a workbook that provides the hands-on steps to actually build a database application. The online e-book method provides the additional benefit of showing the students how to accomplish the same tasks with multiple DBMSs. Even if students begin by learn-ing one DBMS, they can download a second or third workbook and transfer their knowledge to a new DBMS.

E-books provide additional benefits, including advanced search capabilities. Students can also set bookmarks and highlight sections. More importantly, they get to keep the books, instead of being forced to sell them back at the end of the term. Database application development is an important topic, and the examples, comments, and tips in the books will be valuable to students throughout their careers.

xii

Brief Contents1 Introduction

Part One: Systems Design2 Database Design3 Data Normalization

Part Two: Queries4 Data Queries5 Advanced Queries and Subqueries

Part Three: Applications6 Forms and Reports7 Database Integrity and Transactions8 Applications9 Data Warehouses and Data Mining

Part Four: Database Administration10 Database Administration11 Distributed Databases12 Physical Data Storage

xiiiContentsContentsChapter 1: Introduction, 1Introduction, 2Databases and Application Development, 3Components of a Database Management System, 6

Database Engine, 7Data Dictionary, 8Query Processor, 9Report Service, 9Forms Development, 11Management Utilities and Security, 11

Advantages of the Database Management System Approach, 12

Focus on Data, 13Data Independence, 14Data Independence and Web Applications, 15

Leading Commercial Database Systems, 16The Evolution of Database Management Systems, 17

Hierarchical Databases, 17Network Databases, 18Relational Databases, 19Object-Oriented Databases, 20

Application Development, 24Introduction to this Book’s Databases, 25

Sally’s Pet Store, 25Corner Med, 26Rolling Thunder Bicycles, 26

Starting a Project: The Feasibility Study, 27Costs, 28Benefits, 28

Summary, 29Key Terms, 30Review Questions, 30Exercises, 31Web Site References, 34Additional Reading, 34

Part One: Systems Design, 35

Chapter 2: Database Design, 36Introduction, 37Getting Started, 39Designing Databases, 40

Identifying User Requirements, 40

Business Objects, 40Tables and Relationships, 41Definitions, 42Primary Key, 42

Class Diagrams, 43Classes and Entities, 44Associations and Relationships, 45Class Diagram Details, 45

Sally’s Pet Store Class Diagram, 55Data Types (Domains), 58

Text, 58Numbers, 59Dates and Times, 60Binary Objects, 61Computed Values, 61User-Defined Types (Domains/Objects), 62

Events, 62Large Projects, 64Rolling Thunder Bicycles, 65Application Design, 70Corner Med, 72Summary, 75Key Terms, 76Review Questions, 76Exercises, 77Web Site References, 86Additional Reading, 86Appendix: Database Design System, 87

Sample Problem: Customer Orders, 88Getting Started: Identifying Columns, 88Creating a Table and Adding Columns, 89Relationships: Connecting Tables, 91Saving and Opening Solutions, 92Grading: Detecting and Solving Problems, 93Specifying Data Types, 96Generating Tables, 97

Chapter 3: Data Normalization, 99Introduction, 100Tables, Classes, and Keys, 101

Composite Keys, 101Surrogate Keys, 102Notation, 103

Database Normalization: Atomic Values and Dependency, 104

xivContents

Atomic Data Values, 104Dependency, 107

Sample Database for Typical Sales, 108Initial Objects, 109Initial Form Evaluation, 110Problems with Repeating Sections, 112

First Normal Form, 112Repeating Groups, 113Multiple Repeating Groups, 114Nested Repeating Groups, 114

Second Normal Form, 115Problems with First Normal Form, 115Second Normal Form Definition, 116

Third Normal Form, 119Problems with Second Normal Form, 119Third Normal Form Definition, 119Checking Your Work, 122

Beyond Third Normal Form, 122Boyce-Codd Normal Form, 123Fourth Normal Form, 124Domain-Key Normal Form, 124Summary, 126

Data Rules and Integrity, 126The Effects of Business Rules, 128Converting a Class Diagram to Normalized Tables, 130

One-to-Many Relationships, 131Many-to-Many Relationships, 133N-ary Associations, 134Generalization or Subtypes, 135Composition, 138Recursive (Reflexive) Associations, 138

The Pet Store Example, 139View Integration, 141

The Pet Store Example, 142Rolling Thunder Sample Integration Problem, 144

Data Dictionary, 150DBMS Table Definition, 150Data Volume and Usage, 154

Summary, 156Key Terms, 157Review Questions, 157Exercises, 158Web Site References, 166

Additional Reading, 166Appendix:FormalDefinitionsofNormalization,167

Initial Definitions, 167Normal Form Definitions, 168

Part Two: Queries, 172

Chapter 4: Data Queries, 173Introduction, 174Three Tasks of a Query Language, 175Four Questions to Retrieve Data, 175

What Output Do You Want to See?, 176What Do You Already Know?, 177What Tables Are Involved?, 177How Are the Tables Joined?, 178

Sally’s Pet Store, 179Vendor Differences, 180Query Basics, 180

Single Tables, 181Introduction to SQL, 182Sorting the Output, 183Distinct, 183Criteria, 184Pattern Matching, 185Boolean Algebra, 187DeMorgan’s Law, 189Useful WHERE Clauses, 192

Computations, 193Basic Arithmetic Operators, 193Aggregation, 193Functions, 195

Subtotals and GROUP BY, 197Conditions on Totals (HAVING), 199WHERE versus HAVING, 199The Best and the Worst, 200

Multiple Tables, 201Joining Tables, 202Identifying Columns in Different Tables, 203Joining Many Tables, 203Hints on Joining Tables, 205Table Alias, 207Create View, 207

Summary, 209Key Terms, 210Review Questions, 210

xvContents

Exercises, 211Web Site References, 216Additional Reading, 216Appendix: SQL Syntax, 217

Chapter 5: Advanced Queries and Subqueries, 220Introduction, 221Sally’s Pet Store, 222Subqueries, 222

Calculations or Simple Lookup, 223Subqueries and Sets of Data , 224Subquery with ANY, ALL, and EXISTS, 227

Subtraction: NOT IN, 227OUTER JOINS, 229Correlated Subqueries Are Dangerous, 231More Features and Tricks with SQL SELECT, 234

UNION, INTERSECT, EXCEPT, 234Multiple JOIN Columns, 235Reflexive Join, 236CASE Function, 239Inequality Joins, 239Questions with “Every” Need the EXISTS Clause, 240SQL SELECT Summary, 243

SQLDataDefinitionCommands,244SQL Data Manipulation Commands, 246

INSERT and DELETE, 247UPDATE, 248

Quality: Testing Queries, 249Summary, 250Key Terms, 251Review Questions, 251Exercises, 252Web Site References, 257Additional Reading, 258Appendix: Introduction to Programming, 259

Variables and Data, 259Variable Scope, 260Computations, 261Standard Internal Functions, 263Input and Output, 264Conditions, 265Loops, 266Subroutines, 267Summary, 268

Part Three: Applications, 269

Chapter 6: Forms and Reports, 270Introduction, 271Effective Design of Reports and Forms, 272

Human Factors Design, 273Standard Form Controls, 275User Interface—Web Notes, 277User Interface—Events, 278User Interface—Accessibility Issues, 278User Interface—International Environment, 279

Form Layout, 281Tabular Forms, 281Single-Row or Columnar-Forms, 282Subform Forms, 284Startup Forms, 285

Creating Forms, 285Updateable Queries, 286Linked Forms, 287Properties and Controls, 288Controls on Forms, 289Multiple Forms, 292

Direct Manipulation of Graphical Objects, 293Sally’s Pet Store Example, 294The Internet, 294Complications and Limitations of a Graphical Approach, 295

Database Design Revisited, 296Reports, 297

Report Design, 298Terminology, 299Basic Report Types, 299Charts, 305

Summary, 306Key Terms, 307Review Questions, 307Exercises, 307Web Site References, 310Additional Reading, 310

Chapter 7: Database Integrity and Transactions, 311Introduction, 312Procedural Languages, 312

Where Should Code Be Located?, 313User-Defined Functions, 314

xviContents

Looking Up Data, 316Data Triggers, 316

Statement versus Row Triggers, 317Canceling Data Changes in Triggers, 318Cascading Triggers, 319INSTEAD OF Triggers, 320Trigger Summary, 321

Transactions, 321A Transaction Example, 322Starting and Ending Transactions, 323SAVEPOINT, 324

Multiple Users and Concurrent Access, 324Pessimistic Locks: Serialization, 325Multiuser Databases: Concurrent Access and Deadlock, 326Optimistic Locks, 328

ACID Transactions, 330Key Generation, 332Database Cursors, 333

Cursor Basics, 334Scrollable Cursors, 334Changing or Deleting Data with Cursors, 336Cursors with Parameters, 338

Merchandise Inventory at Sally’s Pet Store, 338Summary, 343Key Terms, 344Review Questions, 344Exercises, 344Web Site References, 347Additional Reading, 347

Chapter 8: Application Development, 349Introduction, 350Design Consistency, 351

Page Design Templates, 351Usability , 352Fonts and Customization, 353

Application Structure, 354Designing Applications, 355The Startup Form, 356Sally’s Pet Store: Application Organization, 357Administrative Tasks, 359

Menus and Toolbars, 360Purpose of the Menu, 361Toolbars, 361

Creating Menus and Toolbars, 362Custom Help, 362

Creating a Help File for Windows, 363Context-Sensitive Help, 366Windows Vista, 367

Handling Errors, 368Catching Errors, 368Logging Errors, 369Debugging, 369

Testing, 370Form and Module Testing, 370Integrated Application Testing, 371Stress or Performance Testing, 371Usability Testing, 371Security Testing, 372

Deploying an Application, 373Packaging Files, 373Installation Programs, 374Server and Database Configuration, 374

Summary, 374Key Terms, 375Review Questions, 375Exercises, 376Web Site References, 377Additional Reading, 377

Chapter 9: Data Warehouses and Data Mining, 378Introduction, 379Indexes, 380

Binary Search, 380Pointers and Indexes, 381Creating Indexes, 382Problems with Indexes, 384

Data Warehouses and Online Analytical Processing, 384

Data Warehouse Goals, 385Data Warehouse Issues, 386

OLAP Concepts, 388OLAP Database Design, 390

Snowflake Design, 391Star Design, 392

OLAP Data Analysis, 393Cube Browsers, 393OLAP in SQL, 395SQL Analytic Functions, 400

xviiContents

SQL OLAP Windows Partition, 401Data Mining and Business Intelligence, 404

Classification, 405Association Rules/Market Basket Analysis, 406Cluster Analysis, 408Geographic Analysis, 409

Summary, 410Key Terms, 411Review Questions, 411Exercises, 411Additional Reading, 413

Part Four: Database Administration, 415

Chapter 10: Database Administration, 416Introduction, 417Data Administrator, 418Database Administrator, 420Database Structure, 421Metadata, 422Database Tasks by Development Stages, 423

Database Planning, 423Database Design, 423Database Implementation, 425Database Operation and Maintenance, 425

Backup and Recovery, 427Security and Privacy, 429

Data Privacy, 430Threats, 430Physical Security, 431Managerial Controls, 432Logical Security, 432Division of Duties, 439Software Updates, 440

Encryption, 440Sally’s Pet Store, 444Summary, 445Key Terms, 446Review Questions, 447Exercises, 447Web Site References, 450Additional Reading, 450

Chapter 11: Distributed Databases, 451Introduction, 452

Distributed Databases, 453Goals and Rules, 454Advantages and Applications, 455Creating a Distributed Database System, 456Network Speeds, 458Query Processing and Data Transfer, 458Data Replication, 460Generating Keys with Replicated Data, 461Concurrency, Locks, and Transactions, 462Distributed Transaction Managers, 463Distributed Design Questions, 465

Client/Server Databases, 465Client/Server versus File Server, 466Three-Tier Client/Server Model, 467The Back End: Server Databases, 469The Front End: Windows Clients, 469Maintaining Database Independence in the Client, 470

Centralizing with a Web Server, 472Web Server Database Fundamentals, 473Browser and Server Perspectives, 474

Data Transmission Issues in Applications, 475XML: Transferring Data to Diverse Systems, 477Sally’s Pet Store, 481Summary, 482Key Terms, 483Review Questions, 483Exercises, 484Web Site References, 486Additional Reading, 486

Chapter 12: Physical Database Design, 487Introduction, 488Physical Data Storage, 488Table Operations, 489

Retrieve Data, 490Store Data, 492Reorganize the Database, 492Identifying Problems, 493

Data Storage Methods, 493Sequential Storage, 494Pointers and Indexes, 495Linked Lists, 497B+Trees, 499Direct or Hashed Access, 502

xviiiContents

Bitmap Index, 503Comparison of Access Methods, 503

Storing Data Columns, 504Text and Numbers, 504Image and Binary Data, 506Transferring Data with Delimited Files, 506

Data Clustering and Partitioning, 507Data Clustering, 507Data Partitioning , 508

Managing Tablespaces, 510Sally’s Pet Store, 510Summary, 511Key Terms, 512Review Questions, 512Exercises, 512 Web Site References, 514Additional Reading, 514