The DreamHome case study 16.13 of the textbook.?

D atabase System

s A Practical Approach to D

esign, Im

plem entation, and M

anagem ent

C o nno

lly • B egg

SIX t

h e

D It

Io n

GloBal eDItIonGlo

B a

l eD

It Io

n

this is a special edition of an established title widely used by colleges and universities throughout the world. Pearson published this exclusive edition for the benefit of students outside the United States and Canada. If you purchased this book within the United States or Canada you should be aware that it has been imported without the approval of the Publisher or author.

Pearson Global Edition

GloBal eDItIon

For these Global Editions, the editorial team at Pearson has collaborated with educators across the world to address a wide range of subjects and requirements, equipping students with the best possible learning tools. This Global Edition preserves the cutting-edge approach and pedagogy of the original, but also features alterations, customization and adaptation from the North american version.

Database Systems A Practical Approach to Design, Implementation, and Management SIXth eDItIon

Thomas Connolly • Carolyn Begg

CONNOLLY_1292061189_mech.indd 1 23/06/14 2:23 pm

ONLINE ACCESS Thank you for purchasing a new copy of Database Systems, Sixth Edition. Your textbook includes one year of prepaid access to the book’s Companion Website. This prepaid subscription provides you with full access to the following student support areas:

• online appendices

• tutorials on selected chapters

• DreamHome web implementation

Use a coin to scratch off the coating and reveal your student access code. Do not use a knife or other sharp object as it may damage the code.

To access the Database Systems, Sixth Edition., Companion Website for the first time, you will need to register online using a computer with an Internet connection and a web browser. The process takes just a couple of minutes and only needs to be completed once.

1. Go to www.pearsonglobaleditions.com/connolly

2. Click on Companion Website.

3. Click on the Register button.

4. On the registration page, enter your student access code* found beneath the scratch-off panel. Do not type the dashes. You can use lower- or uppercase.

5. Follow the on-screen instructions. If you need help at any time during the online registration process, simply click the Need Help? icon.

6. Once your personal Login Name and Password are confirmed, you can begin using the Database Systems Companion Website!

To log in after you have registered:

You only need to register for this Companion Website once. After that, you can log in any time at www.pearsonglobaleditions.com/connolly by providing your Login Name and Password when prompted.

*Important: The access code can only be used once. This subscription is valid for one year upon activation and is not transferable. If this access code has already been revealed, it may no longer be valid. If this is the case, you can purchase a subscription by going to www.pearsonglobaleditions.com/connolly and following the on-screen instructions.

CONNOLLY_1292061189_ifc.indd 1 23/06/14 2:24 pm

Database systems A Practical Approach to Design, Implementation, and Management

SIXth EDItIon

GlobAl EDItIon

A01_CONN3067_06_SE_FM.indd 1 17/06/14 5:38 PM

A01_CONN3067_06_SE_FM.indd 2 17/06/14 5:38 PM

Database systems A Practical Approach to Design, Implementation, and Management

SIXth EDItIon

GlobAl EDItIon

thomas m. Connolly z Carolyn e. begg University of the west of sCotlanD

Boston Columbus Indianapolis New York San Francisco Upper Saddle River Amsterdam Cape Town Dubai London Madrid Milan Munich Paris Montréal Toronto

Delhi Mexico City São Paulo Sydney Hong Kong Seoul Singapore TaiPei Tokyo

A01_CONN3067_06_SE_FM.indd 3 17/06/14 5:38 PM

Editorial Director, ECS: Marcia Horton Head of Learning Asset Acquisition, Global Editions: Laura Dent Acquisitions Editor: Matt Goldstein Acquisitions Editor, Global Editions: Subhasree Patra Program Manager: Kayla Smith-Tarbox Director of Marketing: Christy Lesko Marketing Manager: Yezan Alayan Marketing Assistant: Jon Bryant Director of Production: Erin Gregg Senior Managing Editor: Scott Disanno Senior Project Manager: Marilyn Lloyd Media Producer, Global Editions: M Vikram Kumar Project Editor, Global Editions: K.K. Neelakantan

Senior Production Manufacturing Controller, Global Editions: Trudy Kimber Manufacturing Buyer: Linda Sager Art Director: Jayne Conte Cover Designer: Lumina Datamatics Text Designer: Susan Raymond Manager, Text Permissions: Tim Nicholls Text Permission Project Manager: Jenell Forschler Cover Image: © Africa Studio/

Shutterstock Media Project Manager: Renata Butera Full-Service Project Management: Vasundhara Sawhney/

Cenveo® Publisher Services

Credits and acknowledgments borrowed from other sources and reproduced, with permission, in this textbook appear on the Credits page at the end of the book.

Pearson Education Limited Edinburgh Gate Harlow Essex CM20 2JE England

and Associated Companies throughout the world

Visit us on the World Wide Web at: www.pearsonglobaleditions.com

© Pearson Education Limited 2015

The rights of Thomas Connolly and Carolyn Begg to be identified as the authors of this work have been asserted by them in accordance with the Copyright, Designs and Patents Act 1988.

Authorized adaptation from the United States edition, entitled Database Systems: A Practical Approach to Design, Implementation, and Management, 6th edition, ISBN 978-0-13-294326-0, by Thomas Connolly and Carolyn Begg, published by Pearson Education © 2015.

All rights reserved. No part of this publication may be reproduced, stored in a retrieval system, or transmitted in any form or by any means, electronic, mechanical, photocopying, recording or otherwise, without either the prior written permission of the publisher or a license permitting restricted copying in the United Kingdom issued by the Copyright Licensing Agency Ltd, Saffron House, 6–10 Kirby Street, London EC1N 8TS.

All trademarks used herein are the property of their respective owners. The use of any trademark in this text does not vest in the author or publisher any trademark ownership rights in such trademarks, nor does the use of such trademarks imply any affiliation with or endorsement of this book by such owners.

British Library Cataloguing-in-Publication Data

A catalogue record for this book is available from the British Library

10 9 8 7 6 5 4 3 2 1 15 14 13 12 11

ISBN 10: 1-292-06118-9 ISBN 13: 978-1-292-06118-4

Typeset by Cenveo Publishing Services.

Printed and bound by Courier Westford in The United States of America.

A01_CONN3067_06_SE_FM.indd 4 03/07/14 9:49 AM

To Sheena, for her patience, understanding, and love. To our beautiful children Kathryn, Michael and Stephen with all our love. And to my brother, Francis, who died during the writing of this book.

Thomas M. Connolly

To my past, present, and future students at UWS.

Carolyn E. Begg

A01_CONN3067_06_SE_FM.indd 5 17/06/14 5:38 PM

A01_CONN3067_06_SE_FM.indd 6 17/06/14 5:38 PM

Preface 35

Part 1 Background 49

Chapter 1 Introduction to Databases 51

Chapter 2 Database Environment 83

Chapter 3 Database Architectures and the Web 105

Part 2 The Relational Model and Languages 147

Chapter 4 The Relational Model 149

Chapter 5 Relational Algebra and Relational Calculus 167

Chapter 6 SQL: Data Manipulation 191

Chapter 7 SQL: Data Definition 233

Chapter 8 Advanced SQL 271

Chapter 9 Object-Relational DBMSs 291

Part 3 Database Analysis and Design 343

Chapter 10 Database System Development Lifecycle 345

Chapter 11 Database Analysis and the DreamHome Case Study 375

Chapter 12 Entity–Relationship Modeling 405

Chapter 13 Enhanced Entity–Relationship Modeling 433

Chapter 14 Normalization 451

Chapter 15 Advanced Normalization 481

Part 4 Methodology 501

Chapter 16 Methodology—Conceptual Database Design 503

Chapter 17 Methodology—Logical Database Design for the Relational Model 527

Brief Contents

7

A01_CONN3067_06_SE_FM.indd 7 17/06/14 5:38 PM

8 | Brief Contents Chapter 18 Methodology—Physical Database Design

for Relational Databases 561

Chapter 19 Methodology—Monitoring and Tuning the Operational System 585

Part 5 Selected Database Issues 605

Chapter 20 Security and Administration 607

Chapter 21 Professional, Legal, and Ethical Issues in Data Management 641

Chapter 22 Transaction Management 667

Chapter 23 Query Processing 727

Part 6 Distributed DBMSs and Replication 783

Chapter 24 Distributed DBMSs—Concepts and Design 785

Chapter 25 Distributed DBMSs—Advanced Concepts 831

Chapter 26 Replication and Mobile Databases 875

Part 7 Object DBMSs 939

Chapter 27 Object-Oriented DBMSs—Concepts and Design 941

Chapter 28 Object-Oriented DBMSs—Standards and Systems 995

Part 8 The Web and DBMSs 1045

Chapter 29 Web Technology and DBMSs 1047

Chapter 30 Semistructured Data and XML 1129

Part 9 Business Intelligence 1221

Chapter 31 Data Warehousing Concepts 1223

Chapter 32 Data Warehousing Design 1257

Chapter 33 OLAP 1285

Chapter 34 Data Mining 1315

A01_CONN3067_06_SE_FM.indd 8 17/06/14 5:38 PM

Brief Contents | 9 Appendices 1329

A Users’ Requirements Specification for DreamHome Case Study A-1

B Other Case Studies B-1

C Alternative ER Modeling Notations C-1

D Summary of the Database Design Methodology for Relational Databases D-1

E Introduction to Pyrrho: A Lightweight RDBMS E-1

F File Organizations and Indexes (Online) F-1

G When Is a DBMS Relational? (Online) G-1

H Commercial DBMSs: Access® and Oracle® (Online) H-1

I Programmatic SQL (Online) I-1

J Estimating Disk Space Requirements (Online) J-1

K Introduction to Object-Oriented Concepts (Online) K-1

L Example Web Scripts (Online) L-1

M Query-By-Example (QBE) (Online) M-1

N Third Generation Manifestos (Online) N-1

O Postgres—An Early ORDBMS (Online) O-1

References R-1 Further Reading FR-1 Index IN-1

A01_CONN3067_06_SE_FM.indd 9 17/06/14 5:38 PM

A01_CONN3067_06_SE_FM.indd 10 17/06/14 5:38 PM

Preface 35

Part 1 Background 49

Chapter 1 Introduction to Databases 51

1.1 Introduction 52

1.2 Traditional File-Based Systems 55 1.2.1 File-Based Approach 55 1.2.2 Limitations of the File-Based Approach 60

1.3 Database Approach 62 1.3.1 The Database 63 1.3.2 The Database Management System (DBMS) 64 1.3.3 (Database) Application Programs 65 1.3.4 Components of the DBMS Environment 66 1.3.5 Database Design: The Paradigm Shift 69

1.4 Roles in the Database Environment 69 1.4.1 Data and Database Administrators 69 1.4.2 Database Designers 70 1.4.3 Application Developers 71 1.4.4 End-Users 71

1.5 History of Database Management Systems 71

1.6 Advantages and Disadvantages of DBMSs 75

Chapter Summary 79 Review Questions 80 Exercises 80

Chapter 2 Database Environment 83

2.1 The Three-Level ANSI-SPARC Architecture 84 2.1.1 External Level 85 2.1.2 Conceptual Level 86 2.1.3 Internal Level 86 2.1.4 Schemas, Mappings, and Instances 87 2.1.5 Data Independence 88

2.2 Database Languages 89 2.2.1 The Data Definition Language (DDL) 90

Contents

11

A01_CONN3067_06_SE_FM.indd 11 17/06/14 5:38 PM

12 | Contents 2.2.2 The Data Manipulation Language (DML) 90 2.2.3 Fourth-Generation Languages (4GLs) 92

2.3 Data Models and Conceptual Modeling 93 2.3.1 Object-Based Data Models 94 2.3.2 Record-Based Data Models 94 2.3.3 Physical Data Models 97 2.3.4 Conceptual Modeling 97

2.4 Functions of a DBMS 97

Chapter Summary 102 Review Questions 103 Exercises 104

Chapter 3 Database Architectures and the Web 105 3.1 Multi-user DBMS Architectures 106

3.1.1 Teleprocessing 106 3.1.2 File-Server Architecture 107 3.1.3 Traditional Two-Tier Client–Server Architecture 108 3.1.4 Three-Tier Client–Server Architecture 111 3.1.5 N-Tier Architectures 112 3.1.6 Middleware 113 3.1.7 Transaction Processing Monitors 115

3.2 Web Services and Service-Oriented Architectures 117 3.2.1 Web Services 117 3.2.2 Service-Oriented Architectures (SOA) 119

3.3 Distributed DBMSs 120

3.4 Data Warehousing 123

3.5 Cloud Computing 125 3.5.1 Benefits and Risks of Cloud Computing 127 3.5.2 Cloud-based database solutions 130

3.6 Components of a DBMS 134

3.7 Oracle Architecture 137 3.7.1 Oracle’s Logical Database Structure 137 3.7.2 Oracle’s Physical Database Structure 140

Chapter Summary 144 Review Questions 145 Exercises 145

Part 2 The Relational Model and Languages 147

Chapter 4 The Relational Model 149 4.1 Brief History of the Relational Model 150

4.2 Terminology 152 4.2.1 Relational Data Structure 152

A01_CONN3067_06_SE_FM.indd 12 17/06/14 5:38 PM

Contents | 13 4.2.2 Mathematical Relations 155 4.2.3 Database Relations 156 4.2.4 Properties of Relations 156 4.2.5 Relational Keys 158 4.2.6 Representing Relational Database Schemas 159

4.3 Integrity Constraints 161 4.3.1 Nulls 161 4.3.2 Entity Integrity 162 4.3.3 Referential Integrity 162 4.3.4 General Constraints 163

4.4 Views 163 4.4.1 Terminology 163 4.4.2 Purpose of Views 164 4.4.3 Updating Views 165

Chapter Summary 165 Review Questions 166 Exercises 166

Chapter 5 Relational Algebra and Relational Calculus 167

5.1 The Relational Algebra 168 5.1.1 Unary Operations 168 5.1.2 Set Operations 171 5.1.3 Join Operations 174 5.1.4 Division Operation 177 5.1.5 Aggregation and Grouping Operations 178 5.1.6 Summary of the Relational Algebra Operations 180

5.2 The Relational Calculus 181 5.2.1 Tuple Relational Calculus 181 5.2.2 Domain Relational Calculus 184

5.3 Other Languages 186

Chapter Summary 187 Review Questions 187 Exercises 188

Chapter 6 SQL: Data Manipulation 191

6.1 Introduction to SQL 192 6.1.1 Objectives of SQL 192 6.1.2 History of SQL 193 6.1.3 Importance of SQL 195 6.1.4 Terminology 195

6.2 Writing SQL Commands 195

A01_CONN3067_06_SE_FM.indd 13 17/06/14 5:38 PM

14 | Contents 6.3 Data Manipulation 196

6.3.1 Simple Queries 197 6.3.2 Sorting Results (ORDER BY Clause) 205 6.3.3 Using the SQL Aggregate Functions 207 6.3.4 Grouping Results (GROUP BY Clause) 209 6.3.5 Subqueries 212 6.3.6 ANY and ALL 214 6.3.7 Multi-table Queries 216 6.3.8 EXISTS and NOT EXISTS 222 6.3.9 Combining Result Tables (UNION, INTERSECT, EXCEPT) 223 6.3.10 Database Updates 225

Chapter Summary 229 Review Questions 230 Exercises 230

Chapter 7 SQL: Data Definition 233

7.1 The ISO SQL Data Types 234 7.1.1 SQL Identifiers 234 7.1.2 SQL Scalar Data Types 235

7.2 Integrity Enhancement Feature 240 7.2.1 Required Data 240 7.2.2 Domain Constraints 240 7.2.3 Entity Integrity 241 7.2.4 Referential Integrity 242 7.2.5 General Constraints 243

7.3 Data Definition 244 7.3.1 Creating a Database 244 7.3.2 Creating a Table (CREATE TABLE) 245 7.3.3 Changing a Table Definition (ALTER TABLE) 248 7.3.4 Removing a Table (DROP TABLE) 249 7.3.5 Creating an Index (CREATE INDEX) 250 7.3.6 Removing an Index (DROP INDEX) 250

7.4 Views 251 7.4.1 Creating a View (CREATE VIEW) 251 7.4.2 Removing a View (DROP VIEW) 253 7.4.3 View Resolution 254 7.4.4 Restrictions on Views 255 7.4.5 View Updatability 255 7.4.6 WITH CHECK OPTION 256 7.4.7 Advantages and Disadvantages of Views 258 7.4.8 View Materialization 260

A01_CONN3067_06_SE_FM.indd 14 17/06/14 5:38 PM

Contents | 15 7.5 Transactions 261

7.5.1 Immediate and Deferred Integrity Constraints 262

7.6 Discretionary Access Control 262 7.6.1 Granting Privileges to Other Users (GRANT) 264 7.6.2 Revoking Privileges from Users (REVOKE) 265

Chapter Summary 267 Review Questions 268 Exercises 268

Chapter 8 Advanced SQL 271

8.1 The SQL Programming Language 272 8.1.1 Declarations 272 8.1.2 Assignments 273 8.1.3 Control Statements 274 8.1.4 Exceptions in PL/SQL 276 8.1.5 Cursors in PL/SQL 277

8.2 Subprograms, Stored Procedures, Functions, and Packages 280

8.3 Triggers 281

8.4 Recursion 287

Chapter Summary 288 Review Questions 289 Exercises 289

Chapter 9 Object-Relational DBMSs 291

9.1 Advanced Database Applications 292

9.2 Weaknesses of RDBMSs 297

9.3 Storing Objects in a Relational Database 302 9.3.1 Mapping Classes to Relations 303 9.3.2 Accessing Objects in the Relational Database 304

9.4 Introduction to Object-Relational Database Systems 305

9.5 SQL:2011 308 9.5.1 Row Types 309 9.5.2 User-Defined Types 310 9.5.3 Subtypes and Supertypes 313 9.5.4 User-Defined Routines 314 9.5.5 Polymorphism 317 9.5.6 Reference Types and Object Identity 318 9.5.7 Creating Tables 318 9.5.8 Querying Data 321

A01_CONN3067_06_SE_FM.indd 15 17/06/14 5:38 PM

9.5.9 Collection Types 323 9.5.10 Typed Views 326 9.5.11 Persistent Stored Modules 327 9.5.12 Triggers 327 9.5.13 Large Objects 330 9.5.14 Recursion 331

9.6 Object-Oriented Extensions in Oracle 331 9.6.1 User-Defined Data Types 332 9.6.2 Manipulating Object Tables 337 9.6.3 Object Views 338 9.6.4 Privileges 339

Chapter Summary 340 Review Questions 340 Exercises 341

Part 3 Database Analysis and Design 343

Chapter 10 Database System Development Lifecycle 345

10.1 The Information Systems Lifecycle 346

10.2 The Database System Development Lifecycle 347

10.3 Database Planning 347

10.4 System Definition 350 10.4.1 User Views 350

10.5 Requirements Collection and Analysis 350 10.5.1 Centralized Approach 352 10.5.2 View Integration Approach 352

10.6 Database Design 354 10.6.1 Approaches to Database Design 355 10.6.2 Data Modeling 355 10.6.3 Phases of Database Design 356

10.7 DBMS Selection 359 10.7.1 Selecting the DBMS 359

10.8 Application Design 363 10.8.1 Transaction Design 364 10.8.2 User Interface Design Guidelines 365

10.9 Prototyping 367

10.10 Implementation 367

10.11 Data Conversion and Loading 368

16 | Contents

A01_CONN3067_06_SE_FM.indd 16 17/06/14 5:38 PM

10.12 Testing 368

10.13 Operational Maintenance 369

10.14 CASE Tools 370

Chapter Summary 372 Review Questions 373 Exercises 374

Chapter 11 Database Analysis and the DreamHome Case Study 375

11.1 When Are Fact-Finding Techniques Used? 376

11.2 What Facts Are Collected? 377

11.3 Fact-Finding Techniques 378 11.3.1 Examining Documentation 378 11.3.2 Interviewing 378 11.3.3 Observing the Enterprise in Operation 379 11.3.4 Research 380 11.3.5 Questionnaires 380

11.4 Using Fact-Finding Techniques: A Worked -Example 381 11.4.1 The DreamHome Case Study—An Overview of the Current System 382 11.4.2 The DreamHome Case Study—Database Planning 386 11.4.3 The DreamHome Case Study—System Definition 392 11.4.4 The DreamHome Case Study—Requirements Collection and Analysis 393 11.4.5 The DreamHome Case Study—Database Design 401

Chapter Summary 402 Review Questions 402 Exercises 402

Chapter 12 Entity–Relationship Modeling 405

12.1 Entity Types 406

12.2 Relationship Types 408 12.2.1 Degree of Relationship Type 410 12.2.2 Recursive Relationship 412

12.3 Attributes 413 12.3.1 Simple and Composite Attributes 413 12.3.2 Single-valued and Multi-valued Attributes 414 12.3.3 Derived Attributes 414 12.3.4 Keys 415

12.4 Strong and Weak Entity Types 417

12.5 Attributes on Relationships 418

Contents | 17

A01_CONN3067_06_SE_FM.indd 17 17/06/14 5:38 PM

12.6 Structural Constraints 419 12.6.1 One-to-One (1:1) Relationships 420 12.6.2 One-to-Many (1:*) Relationships 421 12.6.3 Many-to-Many (*:*) Relationships 422 12.6.4 Multiplicity for Complex Relationships 423 12.6.5 Cardinality and Participation Constraints 424

12.7 Problems with ER Models 426 12.7.1 Fan Traps 426 12.7.2 Chasm Traps 428

Chapter Summary 430 Review Questions 430 Exercises 431

Chapter 13 Enhanced Entity–Relationship Modeling 433

13.1 Specialization/Generalization 434 13.1.1 Superclasses and Subclasses 434 13.1.2 Superclass/Subclass Relationships 435 13.1.3 Attribute Inheritance 436 13.1.4 Specialization Process 436 13.1.5 Generalization Process 437 13.1.6 Constraints on Specialization/Generalization 440 13.1.7 Worked Example of using Specialization/ Generalization to Model the Branch View of the DreamHome Case Study 441

13.2 Aggregation 445

13.3 Composition 446

Chapter Summary 447 Review Questions 448 Exercises 448

Chapter 14 Normalization 451

14.1 The Purpose of Normalization 452

14.2 How Normalization Supports Database Design 453

14.3 Data Redundancy and Update Anomalies 454 14.3.1 Insertion Anomalies 455 14.3.2 Deletion Anomalies 455 14.3.3 Modification Anomalies 456

14.4 Functional Dependencies 456 14.4.1 Characteristics of Functional Dependencies 456 14.4.2 Identifying Functional Dependencies 460 14.4.3 Identifying the Primary Key for a Relation Using Functional Dependencies 463

14.5 The Process of Normalization 464

18 | Contents

A01_CONN3067_06_SE_FM.indd 18 17/06/14 5:38 PM

14.6 First Normal Form (1NF) 466

14.7 Second Normal Form (2NF) 470

14.8 Third Normal Form (3NF) 471

14.9 General Definitions of 2NF and 3NF 473

Chapter Summary 475 Review Questions 475 Exercises 476

Chapter 15 Advanced Normalization 481

15.1 More on Functional Dependencies 482 15.1.1 Inference Rules for Functional Dependencies 482 15.1.2 Minimal Sets of Functional Dependencies 484

15.2 Boyce–Codd Normal Form (BCNF) 485 15.2.1 Definition of BCNF 485

15.3 Review of Normalization Up to BCNF440

15.4 Fourth Normal Form (4NF) 493 15.4.1 Multi-Valued Dependency 494 15.4.2 Definition of Fourth Normal Form 495

15.5 Fifth Normal Form (5NF) 495 15.5.1 Lossless-Join Dependency 496 15.5.2 Definition of Fifth Normal Form 496

Chapter Summary 498 Review Questions 498 Exercises 499

Part 4 Methodology 501

Chapter 16 Methodology—Conceptual Database Design 503

16.1 Introduction to the Database Design Methodology 504 16.1.1 What Is a Design Methodology? 504 16.1.2 Conceptual, Logical, and Physical Database Design 505 16.1.3 Critical Success Factors in Database Design 505

16.2 Overview of the Database Design Methodology 506

16.3 Conceptual Database Design Methodology 508 Step 1: Build Conceptual Data Model 508

Chapter Summary 524 Review Questions 524 Exercises 525

Contents | 19

A01_CONN3067_06_SE_FM.indd 19 17/06/14 5:38 PM

Chapter 17 Methodology—Logical Database Design for the Relational Model 527

17.1 Logical Database Design Methodology for the Relational Model 528

Step 2: Build Logical Data Model 528

Chapter Summary 556 Review Questions 557 Exercises 557

Chapter 18 Methodology—Physical Database Design for Relational Databases 561

18.1 Comparison of Logical and Physical Database Design 562

18.2 Overview of the Physical Database Design Methodology 563

18.3 The Physical Database Design Methodology for Relational Databases 564 Step 3: Translate Logical Data Model for Target DBMS 564 Step 4: Design File Organizations and Indexes 569 Step 5: Design User Views 582 Step 6: Design Security Mechanisms 582

Chapter Summary 583 Review Questions 584 Exercises 584

Chapter 19 Methodology—Monitoring and Tuning the Operational System 585

19.1 Denormalizing and Introducing Controlled Redundancy 585 Step 7: Consider the Introduction of Controlled

Redundancy 585

19.2 Monitoring the System to Improve Performance 598 Step 8: Monitor and Tune the Operational System 598

Chapter Summary 602 Review Questions 603 Exercises 603

Part 5 Selected Database Issues 605

Chapter 20 Security and Administration 607

20.1 Database Security 608 20.1.1 Threats 609

20 | Contents

A01_CONN3067_06_SE_FM.indd 20 17/06/14 5:38 PM

20.2 Countermeasures—Computer-Based Controls 611 20.2.1 Authorization 612 20.2.2 Access Controls 613 20.2.3 Views 616 20.2.4 Backup and Recovery 616 20.2.5 Integrity 617 20.2.6 Encryption 617 20.2.7 RAID (Redundant Array of Independent Disks) 618

20.3 Security in Microsoft Office Access DBMS 621

20.4 Security in Oracle DBMS 623

20.5 DBMSs and Web Security 627 20.5.1 Proxy Servers 628 20.5.2 Firewalls 628 20.5.3 Message Digest Algorithms and Digital Signatures 629 20.5.4 Digital Certificates 629 20.5.5 Kerberos 630 20.5.6 Secure Sockets Layer and Secure HTTP 630 20.5.7 Secure Electronic Transactions and Secure Transaction Technology 631 20.5.8 Java Security 632 20.5.9 ActiveX Security 634

20.6 Data Administration and Database Administration 634 20.6.1 Data Administration 635 20.6.2 Database Administration 636 20.6.3 Comparison of Data and Database Administration 636

Chapter Summary 637 Review Questions 638 Exercises 638

Chapter 21 Professional, Legal, and Ethical Issues in Data Management 641

21.1 Defining Legal and Ethical Issues in IT 642 21.1.1 Defining Ethics in the Context of IT 642 21.1.2 The Difference Between Ethical and Legal Behavior 643 21.1.3 Ethical Behavior in IT 644

21.2 Legislation and Its Impact on the IT Function 645 21.2.1 Securities and Exchange Commission (SEC) Regulation National Market System (NMS) 645 21.2.2 The Sarbanes-Oxley Act, COBIT, and COSO 646 21.2.3 The Health Insurance Portability and Accountability Act 649 21.2.4 The European Union (EU) Directive on Data Protection of 1995 650 21.2.5 The United Kingdom’s Data Protection Act of 1998 651

Contents | 21

A01_CONN3067_06_SE_FM.indd 21 17/06/14 5:38 PM

21.2.6 Access to Information Laws 652 21.2.7 International Banking—Basel II Accords 654

21.3 Establishing a Culture of Legal and Ethical Data Stewardship 655 21.3.1 Developing an Organization-Wide Policy for Legal and Ethical Behavior 655 21.3.2 Professional Organizations and Codes of Ethics 656 21.3.3 Developing an Organization-Wide Policy for Legal and Ethical Behavior for DreamHome 659

21.4 Intellectual Property 660 21.4.1 Patent 661 21.4.2 Copyright 661 21.4.3 Trademark 662 21.4.4 Intellectual Property Rights Issues for Software 662 21.4.5 Intellectual Property Rights Issues for Data 664

Chapter Summary 664 Review Questions 665 Exercises 666

Chapter 22 Transaction Management 667

22.1 Transaction Support 668 22.1.1 Properties of Transactions 671 22.1.2 Database Architecture 671

22.2 Concurrency Control 672 22.2.1 The Need for Concurrency Control 672 22.2.2 Serializability and Recoverability 675 22.2.3 Locking Methods 683 22.2.4 Deadlock 689 22.2.5 Timestamping Methods 692 22.2.6 Multiversion Timestamp Ordering 695 22.2.7 Optimistic Techniques 696 22.2.8 Granularity of Data Items 697

22.3 Database Recovery 700 22.3.1 The Need for Recovery 700 22.3.2 Transactions and Recovery 701 22.3.3 Recovery Facilities 704 22.3.4 Recovery Techniques 707 22.3.5 Recovery in a Distributed DBMS 709

22.4 Advanced Transaction Models 709 22.4.1 Nested Transaction Model 711 22.4.2 Sagas 712 22.4.3 Multilevel Transaction Model 713 22.4.4 Dynamic Restructuring 714 22.4.5 Workflow Models 715

22 | Contents

A01_CONN3067_06_SE_FM.indd 22 17/06/14 5:38 PM

22.5 Concurrency Control and Recovery in Oracle 716 22.5.1 Oracle’s Isolation Levels 717 22.5.2 Multiversion Read Consistency 717 22.5.3 Deadlock Detection 719 22.5.4 Backup and Recovery 719

Chapter Summary 722 Review Questions 723 Exercises 724

Chapter 23 Query Processing 727

23.1 Overview of Query Processing 729

23.2 Query Decomposition 73

Collepals.com Plagiarism Free Papers

Are you looking for custom essay writing service or even dissertation writing services? Just request for our write my paper service, and we'll match you with the best essay writer in your subject! With an exceptional team of professional academic experts in a wide range of subjects, we can guarantee you an unrivaled quality of custom-written papers.

Get ZERO PLAGIARISM, HUMAN WRITTEN ESSAYS

Why Hire Collepals.com writers to do your paper?

Quality- We are experienced and have access to ample research materials.

We write plagiarism Free Content

Confidential- We never share or sell your personal information to third parties.

Support-Chat with us today! We are always waiting to answer all your questions.