Database Systems: A Practical Approach to Design, Implementation, and Management, 6/e

by CONNOLLY; BEGG

• ISBN13: 9780132943260
• ISBN10: 0132943263
• eBook ISBN(s): 9780133869750
• Additional ISBN(s): 9780321523068
• Edition: 6th
• Format: Paperback
• Copyright: 2014-01-08
• Publisher: PEARSON
• More Book Details

Summary

Database Systems is ideal for a one- or two-term course in database management or database design in an undergraduate or graduate level course. With its comprehensive coverage, this book can also be used as a reference for IT professionals.

This best-selling text introduces the theory behind databases in a concise yet comprehensive manner, providing database design methodology that can be used by both technical and non-technical readers. The methodology for relational Database Management Systems is presented in simple, step-by-step instructions in conjunction with a realistic worked example using three explicit phases—conceptual, logical, and physical database design.

¿

Teaching and Learning Experience

This program presents a better teaching and learning experience–for you and your students. It provides:

• Database Design Methodology that can be Used by Both Technical and Non-technical Readers
• A Comprehensive Introduction to the Theory behind Databases
• A Clear Presentation that Supports Learning

Author Biography

Thomas M. Connolly is the head of the Computing and Information Systems division at the University of Paisley.

Carolyn E. Begg is a lecturer at the University of Paisley, with research interests in Information Systems, Database Management Systems, and Decision Support Systems within medicine.

Table of Contents

Part 1 Background 1

Chapter 1 Introduction to Databases 3

1.1 Introduction 4

1.2 Traditional File-Based Systems 7

1.2.1 File-Based Approach 7

1.2.2 Limitations of the File-Based Approach 12

1.3 Database Approach 14

1.3.1 The Database 15

1.3.2 The Database Management System (DBMS) 16

1.3.3 (Database) Application Programs 17

1.3.4 Components of the DBMS Environment 18

1.3.5 Database Design: The Paradigm Shift 21

1.4 Roles in the Database Environment 21

1.4.1 Data and Database Administrators 21

1.4.2 Database Designers 22

1.4.3 Application Developers 23

1.4.4 End-Users 23

1.5 History of Database Management Systems 23

1.6 Advantages and Disadvantages of DBMSs 27

Chapter Summary 31

Review Questions 32

Exercises 32

 

Chapter 2 Database Environment 35

2.1 The Three-Level ANSI-SPARC Architecture 36

2.1.1 External Level 37

2.1.2 Conceptual Level 38

2.1.3 Internal Level 38

2.1.4 Schemas, Mappings, and Instances 39

2.1.5 Data Independence 40

2.2 Database Languages 41

2.2.1 The Data Definition Language (DDL) 42

2.2.2 The Data Manipulation Language (DML) 42

2.2.3 Fourth-Generation Languages (4GLs) 44

2.3 Data Models and Conceptual Modeling 45

2.3.1 Object-Based Data Models 46

2.3.2 Record-Based Data Models 46

2.3.3 Physical Data Models 49

2.3.4 Conceptual Modeling 49

2.4 Functions of a DBMS 49

Chapter Summary 54

Review Questions 55

Exercises 56

 

Chapter 3 Database Architectures and the Web 57

3.1 Multi-user DBMS Architectures 58

3.1.1 Teleprocessing 58

3.1.2 File-Server Architecture 59

3.1.3 Traditional Two-Tier Client—Server Architecture 60

3.1.4 Three-Tier Client—Server Architecture 63

3.1.5 N-Tier Architectures 64

3.1.6 Middleware 65

3.1.7 Transaction Processing Monitors 67

3.2 Web Services and Service-Oriented Architectures 69

3.2.1 Web Services 69

3.2.2 Service-Oriented Architectures (SOA) 71

3.3 Distributed DBMSs 72

3.4 Data Warehousing 75

3.5 Cloud Computing 77

3.5.1 Benefits and Risks of Cloud Computing 79

3.5.2 Cloud-based database solutions 82

3.6 Components of a DBMS 86

3.7 Oracle Architecture 89

3.7.1 Oracle’s Logical Database Structure 89

3.7.2 Oracle’s Physical Database Structure 92

Chapter Summary 96

Review Questions 97

Exercises 97

 

Part 2 The Relational Model and Languages 99

Chapter 4 The Relational Model 101

4.1 Brief History of the Relational Model 102

4.2 Terminology 104

4.2.1 Relational Data Structure 104

4.2.2 Mathematical Relations 107

4.2.3 Database Relations 108

4.2.4 Properties of Relations 108

4.2.5 Relational Keys 110

4.2.6 Representing Relational Database Schemas 111

4.3 Integrity Constraints 113

4.3.1 Nulls 113

4.3.2 Entity Integrity 114

4.3.3 Referential Integrity 114

4.3.4 General Constraints 115

4.4 Views 115

4.4.1 Terminology 115

4.4.2 Purpose of Views 116

4.4.3 Updating Views 117

Chapter Summary 117

Review Questions 118

Exercises 118

 

Chapter 5 Relational Algebra and Relational Calculus 119

5.1 The Relational Algebra 120

5.1.1 Unary Operations 120

5.1.2 Set Operations 123

5.1.3 Join Operations 126

5.1.4 Division Operation 129

5.1.5 Aggregation and Grouping Operations 130

5.1.6 Summary of the Relational Algebra Operations 132

5.2 The Relational Calculus 133

5.2.1 Tuple Relational Calculus 133

5.2.2 Domain Relational Calculus 136

5.3 Other Languages 138

Chapter Summary 139

Review Questions 139

Exercises 140

 

Chapter 6 SQL: Data Manipulation 143

6.1 Introduction to SQL 144

6.1.1 Objectives of SQL 144

6.1.2 History of SQL 145

6.1.3 Importance of SQL 147

6.1.4 Terminology 147

6.2 Writing SQL Commands 147

6.3 Data Manipulation 148

6.3.1 Simple Queries 149

6.3.2 Sorting Results (ORDER BY Clause) 157

6.3.3 Using the SQL Aggregate Functions 159

6.3.4 Grouping Results (GROUP BY Clause) 161

6.3.5 Subqueries 164

6.3.6 ANY and ALL 166

6.3.7 Multi-table Queries 168

6.3.8 EXISTS and NOT EXISTS 174

6.3.9 Combining Result Tables (UNION, INTERSECT,

EXCEPT) 175

6.3.10 Database Updates 177

Chapter Summary 181

Review Questions 182

Exercises 182

 

Chapter 7 SQL: Data Definition 185

7.1 The ISO SQL Data Types 186

7.1.1 SQL Identifiers 186

7.1.2 SQL Scalar Data Types 187

7.2 Integrity Enhancement Feature 192

7.2.1 Required Data 192

7.2.2 Domain Constraints 192

7.2.3 Entity Integrity 193

7.2.4 Referential Integrity 194

7.2.5 General Constraints 195

7.3 Data Definition 196

7.3.1 Creating a Database 196

7.3.2 Creating a Table (CREATE TABLE) 197

7.3.3 Changing a Table Definition (ALTER TABLE) 200

7.3.4 Removing a Table (DROP TABLE) 201

7.3.5 Creating an Index (CREATE INDEX) 202

7.3.6 Removing an Index (DROP INDEX) 202

7.4 Views 203

7.4.1 Creating a View (CREATE VIEW) 203

7.4.2 Removing a View (DROP VIEW) 205

7.4.3 View Resolution 206

7.4.4 Restrictions on Views 207

7.4.5 View Updatability 207

7.4.6 WITH CHECK OPTION 208

7.4.7 Advantages and Disadvantages of Views 210

7.4.8 View Materialization 212

7.5 Transactions 213

7.5.1 Immediate and Deferred Integrity Constraints 214

7.6 Discretionary Access Control 214

7.6.1 Granting Privileges to Other Users (GRANT) 216

7.6.2 Revoking Privileges from Users (REVOKE) 217

Chapter Summary 219

Review Questions 220

Exercises 220

 

Chapter 8 Advanced SQL 223

8.1 The SQL Programming Language 224

8.1.1 Declarations 224

8.1.2 Assignments 225

8.1.3 Control Statements 226

8.1.4 Exceptions in PL/SQL 228

8.1.5 Cursors in PL/SQL 229

8.2 Subprograms, Stored Procedures, Functions,

and Packages 232

8.3 Triggers 233

8.4 Recursion 239

Chapter Summary 240

Review Questions 241

Exercises 241

 

 

Chapter 9 Object-Relational DBMSs 243

9.1 Advanced Database Applications 244

9.2 Weaknesses of RDBMSs 249

9.3 Storing Objects in a Relational Database 254

9.3.1 Mapping Classes to Relations 255

9.3.2 Accessing Objects in the Relational Database 256

9.4 Introduction to Object-Relational Database Systems 257

9.5 SQL:2011 260

9.5.1 Row Types 261

9.5.2 User-Defined Types 262

9.5.3 Subtypes and Supertypes 265

9.5.4 User-Defined Routines 266

9.5.5 Polymorphism 269

9.5.6 Reference Types and Object Identity 270

9.5.7 Creating Tables 270

9.5.8 Querying Data 273

9.5.9 Collection Types 275

9.5.10 Typed Views 278

9.5.11 Persistent Stored Modules 279

9.5.12 Triggers 279

9.5.13 Large Objects 282

9.5.14 Recursion 283

9.6 Object-Oriented Extensions in Oracle 283

9.6.1 User-Defined Data Types 284

9.6.2 Manipulating Object Tables 289

9.6.3 Object Views 290

9.6.4 Privileges 291

Chapter Summary 292

Review Questions 292

Exercises 293

 

Part 3 Database Analysis and Design 295

Chapter 10 Database System Development Lifecycle 297

10.1 The Information Systems Lifecycle 298

10.2 The Database System Development Lifecycle 299

10.3 Database Planning 299

10.4 System Definition 302

10.4.1 User Views 302

10.5 Requirements Collection and Analysis 302

10.5.1 Centralized Approach 304

10.5.2 View Integration Approach 304

10.6 Database Design 306

10.6.1 Approaches to Database Design 307

10.6.2 Data Modeling 307

10.6.3 Phases of Database Design 308

10.7 DBMS Selection 311

10.7.1 Selecting the DBMS 311

10.8 Application Design 315

10.8.1 Transaction Design 316

10.8.2 User Interface Design Guidelines 317

10.9 Prototyping 319

10.10 Implementation 319

10.11 Data Conversion and Loading 320

10.12 Testing 320

10.13 Operational Maintenance 321

10.14 CASE Tools 322

Chapter Summary 324

Review Questions 325

Exercises 326

 

Chapter 11 Database Analysis and the DreamHome Case Study 327

11.1 When Are Fact-Finding Techniques Used? 328

11.2 What Facts Are Collected? 329

11.3 Fact-Finding Techniques 330

11.3.1 Examining Documentation 330

11.3.2 Interviewing 330

11.3.3 Observing the Enterprise in Operation 331

11.3.4 Research 332

11.3.5 Questionnaires 332

11.4 Using Fact-Finding Techniques: A Worked -Example 333

11.4.1 The DreamHome Case Study–An Overview of the Current System 334

11.4.2 The DreamHome Case Study–Database Planning 338

11.4.3 The DreamHome Case Study–System Definition 344

11.4.4 The DreamHome Case Study–Requirements Collection and Analysis 345

11.4.5 The DreamHome Case Study–Database Design 353

Chapter Summary 354

Review Questions 354

Exercises 354

 

Chapter 12 Entity—Relationship Modeling 357

12.1 Entity Types 358

12.2 Relationship Types 360

12.2.1 Degree of Relationship Type 362

12.2.2 Recursive Relationship 364

12.3 Attributes 365

12.3.1 Simple and Composite Attributes 365

12.3.2 Single-valued and Multi-valued Attributes 366

12.3.3 Derived Attributes 366

12.3.4 Keys 367

12.4 Strong and Weak Entity Types 369

12.5 Attributes on Relationships 370

12.6 Structural Constraints 371

12.6.1 One-to-One (1:1) Relationships 372

12.6.2 One-to-Many (1:*) Relationships 373

12.6.3 Many-to-Many (*:*) Relationships 374

12.6.4 Multiplicity for Complex Relationships 375

12.6.5 Cardinality and Participation Constraints 376

12.7 Problems with ER Models 378

12.7.1 Fan Traps 378

12.7.2 Chasm Traps 380

Chapter Summary 382

Review Questions 382

Exercises 383

 

Chapter 13 Enhanced Entity—Relationship Modeling 385

13.1 Specialization/Generalization 386

13.1.1 Superclasses and Subclasses 386

13.1.2 Superclass/Subclass Relationships 387

13.1.3 Attribute Inheritance 388

13.1.4 Specialization Process 388

13.1.5 Generalization Process 389

13.1.6 Constraints on Specialization/Generalization 392

13.1.7 Worked Example of using Specialization/ Generalization to Model the Branch View of the DreamHome Case Study 393

13.2 Aggregation 397

13.3 Composition 398

Chapter Summary 399

Review Questions 400

Exercises 400

 

Chapter 14 Normalization 403

14.1 The Purpose of Normalization 404

14.2 How Normalization Supports Database Design 405

14.3 Data Redundancy and Update Anomalies 406

14.3.1 Insertion Anomalies 407

14.3.2 Deletion Anomalies 407

14.3.3 Modification Anomalies 408

14.4 Functional Dependencies 408

14.4.1 Characteristics of Functional Dependencies 408

14.4.2 Identifying Functional Dependencies 412

14.4.3 Identifying the Primary Key for a Relation Using Functional Dependencies 415

14.5 The Process of Normalization 416

14.6 First Normal Form (1NF) 418

14.7 Second Normal Form (2NF) 422

14.8 Third Normal Form (3NF) 423

14.9 General Definitions of 2NF and 3NF 425

Chapter Summary 427

Review Questions 427

Exercises 428

 

Chapter 15 Advanced Normalization 433

15.1 More on Functional Dependencies 434

15.1.1 Inference Rules for Functional Dependencies 434

15.1.2 Minimal Sets of Functional Dependencies 436

15.2 Boyce—Codd Normal Form (BCNF) 437

15.2.1 Definition of BCNF 437

15.3 Review of Normalization Up to BCNF440

15.4 Fourth Normal Form (4NF) 445

15.4.1 Multi-Valued Dependency 446

15.4.2 Definition of Fourth Normal Form 447

15.5 Fifth Normal Form (5NF) 447

15.5.1 Lossless-Join Dependency 448

15.5.2 Definition of Fifth Normal Form 448

Chapter Summary 450

Review Questions 450

Exercises 451

 

Part 4 Methodology 453

Chapter 16 Methodology–Conceptual Database Design 455

16.1 Introduction to the Database Design Methodology 456

16.1.1 What Is a Design Methodology? 456

16.1.2 Conceptual, Logical, and Physical Database Design 457

16.1.3 Critical Success Factors in Database Design 457

16.2 Overview of the Database Design Methodology 458

16.3 Conceptual Database Design Methodology 460

Step 1: Build Conceptual Data Model 460

Chapter Summary 476

Review Questions 476

Exercises 477

 

Chapter 17 Methodology–Logical Database Design

for the Relational Model 479

17.1 Logical Database Design Methodology for the Relational Model 480

Step 2: Build Logical Data Model 480

Chapter Summary 508

Review Questions 509

Exercises 509

 

Chapter 18 Methodology–Physical Database Design for Relational Databases 513

18.1 Comparison of Logical and Physical Database Design 514

18.2 Overview of the Physical Database Design Methodology 515

18.3 The Physical Database Design Methodology for Relational Databases 516

Step 3: Translate Logical Data Model for Target DBMS 516

Step 4: Design File Organizations and Indexes 521

Step 5: Design User Views 534

Step 6: Design Security Mechanisms 534

Chapter Summary 535

Review Questions 536

Exercises 536

 

Chapter 19 Methodology–Monitoring and Tuning the Operational System 537

19.1 Denormalizing and Introducing Controlled Redundancy 537

Step 7: Consider the Introduction of Controlled Redundancy 537

19.2 Monitoring the System to Improve Performance 550

Step 8: Monitor and Tune the Operational System 550

Chapter Summary 554

Review Questions 555

Exercises 555

 

Part 5 Selected Database Issues 557

Chapter 20 Security and Administration 559

20.1 Database Security 560

20.1.1 Threats 561

20.2 Countermeasures–Computer-Based Controls 563

20.2.1 Authorization 564

20.2.2 Access Controls 565

20.2.3 Views 568

20.2.4 Backup and Recovery 568

20.2.5 Integrity 569

20.2.6 Encryption 569

20.2.7 RAID (Redundant Array of Independent Disks) 570

20.3 Security in Microsoft Office Access DBMS 573

20.4 Security in Oracle DBMS 575

20.5 DBMSs and Web Security 579

20.5.1 Proxy Servers 580

20.5.2 Firewalls 580

20.5.3 Message Digest Algorithms and Digital Signatures 581

20.5.4 Digital Certificates 581

20.5.5 Kerberos 582

20.5.6 Secure Sockets Layer and Secure HTTP 582

20.5.7 Secure Electronic Transactions and Secure Transaction Technology 583

20.5.8 Java Security 584

20.5.9 ActiveX Security 586

20.6 Data Administration and Database Administration 586

20.6.1 Data Administration 587

20.6.2 Database Administration 588

20.6.3 Comparison of Data and Database Administration 588

Chapter Summary 589

Review Questions 590

Exercises 590

 

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

21.1 Defining Legal and Ethical Issues in IT 594

21.1.1 Defining Ethics in the Context of IT 594

21.1.2 The Difference Between Ethical and Legal Behavior 595

21.1.3 Ethical Behavior in IT 596

21.2 Legislation and Its Impact on the IT Function 597

21.2.1 Securities and Exchange Commission (SEC) Regulation National Market System (NMS) 597

21.2.2 The Sarbanes-Oxley Act, COBIT, and COSO 598

21.2.3 The Health Insurance Portability and Accountability Act 601

21.2.4 The European Union (EU) Directive on Data Protection of 1995 602

21.2.5 The United Kingdom’s Data Protection Act of 1998 603

21.2.6 Access to Information Laws 604

21.2.7 International Banking–Basel II Accords 606

21.3 Establishing a Culture of Legal and Ethical Data Stewardship 607

21.3.1 Developing an Organization-Wide Policy for Legal and Ethical Behavior 607

21.3.2 Professional Organizations and Codes of Ethics 608

21.3.3 Developing an Organization-Wide Policy for Legal and Ethical Behavior for DreamHome 611

21.4 Intellectual Property 612

21.4.1 Patent 613

21.4.2 Copyright 613

21.4.3 Trademark 614

21.4.4 Intellectual Property Rights Issues for Software 614

21.4.5 Intellectual Property Rights Issues for Data 616

Chapter Summary 616

Review Questions 617

Exercises 618

 

Chapter 22 Transaction Management 619

22.1 Transaction Support 620

22.1.1 Properties of Transactions 623

22.1.2 Database Architecture 623

22.2 Concurrency Control 624

22.2.1 The Need for Concurrency Control 624

22.2.2 Serializability and Recoverability 627

22.2.3 Locking Methods 635

22.2.4 Deadlock 641

22.2.5 Timestamping Methods 644

22.2.6 Multiversion Timestamp Ordering 647

22.2.7 Optimistic Techniques 648

22.2.8 Granularity of Data Items 649

22.3 Database Recovery 652

22.3.1 The Need for Recovery 652

22.3.2 Transactions and Recovery 653

22.3.3 Recovery Facilities 656

22.3.4 Recovery Techniques 659

22.3.5 Recovery in a Distributed DBMS 661

22.4 Advanced Transaction Models 661

22.4.1 Nested Transaction Model 663

22.4.2 Sagas 664

22.4.3 Multilevel Transaction Model 665

22.4.4 Dynamic Restructuring 666

22.4.5 Workflow Models 667

22.5 Concurrency Control and Recovery in Oracle 668

22.5.1 Oracle’s Isolation Levels 669

22.5.2 Multiversion Read Consistency 669

22.5.3 Deadlock Detection 671

22.5.4 Backup and Recovery 671

Chapter Summary 674

Review Questions 675

Exercises 676

 

Chapter 23 Query Processing 679

23.1 Overview of Query Processing 681

23.2 Query Decomposition 684

23.3 Heuristical Approach to Query Optimization 688

23.3.1 Transformation Rules for the Relational Algebra Operations 688

23.3.2 Heuristical Processing Strategies 693

23.4 Cost Estimation for the Relational Algebra Operations 694

23.4.1 Database Statistics 694

23.4.2 Selection Operation (S = sp(R)) 695

23.4.3 Join Operation (T = (R 1F S)) 702

23.4.4 Projection Operation (S = pA1, A2, . . . , A m(R)) 709

23.4.5 The Relational Algebra Set Operations (T = R ¿ S, T = R > S, T = R — S) 711

23.5 Enumeration of Alternative Execution Strategies 712

23.5.1 Pipelining 713

23.5.2 Linear Trees 713

23.5.3 Physical Operators and Execution Strategies 714

23.5.4 Reducing the Search Space 716

23.5.5 Enumerating Left-Deep Trees 717

23.5.6 Semantic Query Optimization 718

23.5.7 Alternative Approaches to Query Optimization 719

23.5.8 Distributed Query Optimization 720

23.6 Query Processing and Optimization 720

23.6.1 New Index Types 723

23.7 Query Optimization in Oracle 724

23.7.1 Rule-Based and Cost-Based Optimization 724

23.7.2 Histograms 728

23.7.3 Viewing the Execution Plan 730

Chapter Summary 731

Review Questions 732

Exercises 733

 

Part 6 Distributed DBMSs and Replication 735

Chapter 24 Distributed DBMSs–Concepts and Design 737

24.1 Introduction 738

24.1.1 Concepts 739

24.1.2 Advantages and Disadvantages of DDBMSs 743

24.1.3 Homogeneous and Heterogeneous DDBMSs 746

24.2 Overview of Networking 749

24.3 Functions and Architectures of a DDBMS 753

24.3.1 Functions of a DDBMS 753

24.3.2 Reference Architecture for a DDBMS 753

24.3.3 Reference Architecture for a Federated MDBS 755

24.3.4 Component Architecture for a DDBMS 756

24.4 Distributed Relational Database Design 757

24.4.1 Data Allocation 758

24.4.2 Fragmentation 759

24.5 Transparencies in a DDBMS 768

24.5.1 Distribution Transparency 768

24.5.2 Transaction Transparency 771

24.5.3 Performance Transparency 774

24.5.4 DBMS Transparency 776

24.5.5 Summary of Transparencies in a DDBMS 776

24.6 Date’s Twelve Rules for a DDBMS 777

Chapter Summary 779

Review Questions 780

Exercises 780

 

Chapter 25 Distributed DBMSs–Advanced Concepts 783

25.1 Distributed Transaction Management 784

25.2 Distributed Concurrency Control 785

25.2.1 Objectives 785

25.2.2 Distributed Serializability 786

25.2.3 Locking Protocols 786

25.3 Distributed Deadlock Management 789

25.4 Distributed Database Recovery 792

25.4.1 Failures in a Distributed Environment 793

25.4.2 How Failures Affect Recovery 794

25.4.3 Two-Phase Commit (2PC) 794

25.4.4 Three-Phase Commit (3PC) 801

25.4.5 Network Partitioning 804

25.5 The X/Open Distributed Transaction Processing Model 806

25.6 Distributed Query Optimization 808

25.6.1 Data Localization 810

25.6.2 Distributed Joins 813

25.6.3 Global Optimization 814

25.7 Distribution in Oracle 818

25.7.1 Oracle’s DDBMS Functionality 818

Chapter Summary 824

Review Questions 824

Exercises 825

 

Chapter 26 Replication and Mobile Databases 827

26.1 Introduction to Data Replication 828

26.1.1 Applications of Replication 829

26.1.2 Replication Model 830

26.1.3 Functional Model of Replication Protocols 831

26.1.4 Consistency 832

26.2 Replication Architecture 832

26.2.1 Kernel-Based Replication 832

26.2.2 Middleware-Based Replication 833

26.2.3 Processing of Updates 834

26.2.4 Propagation of Updates 836

26.2.5 Update Location (Data Ownership) 836

26.2.6 Termination Protocols 840

26.3 Replication Schemes 840

26.3.1 Eager Primary Copy 841

26.3.2 Lazy Primary Copy 846

26.3.3 Eager Update Anywhere 850

 

26.3.4 Lazy Update Anywhere 851

26.3.5 Update Anywhere with Uniform Total Order Broadcast 855

26.3.6 SI and Uniform Total Order Broadcast Replication 859

26.4 Introduction to Mobile Databases 865

26.4.1 Mobile DBMSs 867

26.4.2 Issues with Mobile DBMSs 868

26.5 Oracle Replication 881

26.5.1 Oracle’s Replication Functionality 881

Chapter Summary 888

Review Questions 889

Exercises 889

 

Part 7 Object DBMSs 891

Chapter 27 Object-Oriented DBMSs–Concepts and Design 893

27.1 Next-Generation Database Systems 895

27.2 Introduction to OODBMSs 897

27.2.1 Definition of Object-Oriented DBMSs 897

27.2.2 Functional Data Models 898

27.2.3 Persistent Programming Languages 903

27.2.4 Alternative Strategies for Developing an OODBMS 905

27.3 Persistence in OODBMSs 906

27.3.1 Pointer Swizzling Techniques 908

27.3.2 Accessing an Object 911

27.3.3 Persistence Schemes 913

27.3.4 Orthogonal Persistence 914

27.4 Issues in OODBMSs 916

27.4.1 Transactions 916

27.4.2 Versions 917

27.4.3 Schema Evolution 918

27.4.4 Architecture 921

27.4.5 Benchmarking 923

27.5 Advantages and Disadvantages of OODBMSs 926

27.5.1 Advantages 926

27.5.2 Disadvantages 928

27.6 Comparison of ORDBMS and OODBMS 930

27.7 Object-Oriented Database Design 931

27.7.1 Comparison of Object-Oriented Data Modeling and Conceptual Data Modeling 931

27.7.2 Relationships and Referential Integrity 932

27.7.3 Behavioral Design 934

27.8 Object-Oriented Analysis and Design with UML 936

27.8.1 UML Diagrams 937

27.8.2 Usage of UML in the Methodology for Database Design 942

Chapter Summary 944

Review Questions 945

Exercises 945

 

Chapter 28 Object-Oriented DBMSs–Standards and Systems 947

28.1 Object Management Group 948

28.1.1 Background 948

28.1.2 The Common Object Request Broker Architecture 951

28.1.3 Other OMG Specifications 956

28.1.4 Model-Driven Architecture 959

28.2 Object Data Standard ODMG 3.0, 1999 959

28.2.1 Object Data Management Group 961

28.2.2 The Object Model 962

28.2.3 The Object Definition Language 970

28.2.4 The Object Query Language 973

28.2.5 Other Parts of the ODMG Standard 979

28.2.6 Mapping the Conceptual Design to a Logical (Object-Oriented) Design 982

28.3 ObjectStore 983

28.3.1 Architecture 983

28.3.2 Building an ObjectStore Application 986

28.3.3 Data Definition in ObjectStore 987

28.3.4 Data Manipulation in ObjectStore 991

Chapter Summary 994

Review Questions 995

Exercises 995

 

Part 8 The Web and DBMSs 997

Chapter 29 Web Technology and DBMSs 999

29.1 Introduction to the Internet and the Web 1000

29.1.1 Intranets and Extranets 1002

29.1.2 e-Commerce and e-Business 1003

29.2 The Web 1004

29.2.1 HyperText Transfer Protocol 1005

29.2.2 HyperText Markup Language 1007

29.2.3 Uniform Resource Locators 1009

29.2.4 Static and Dynamic Web Pages 1010

29.2.5 Web Services 1010

29.2.6 Requirements for Web—DBMS Integration 1011

29.2.7 Advantages and Disadvantages of the Web—DBMS Approach 1012

29.2.8 Approaches to Integrating the Web and DBMSs 1016

29.3 Scripting Languages 1017

29.3.1 JavaScript and JScript 1017

29.3.2 VBScript 1018

29.3.3 Perl and PHP 1019

29.4 Common Gateway Interface (CGI) 1019

29.4.1 Passing Information to a CGI Script 1021

29.4.2 Advantages and Disadvantages of CGI 1023

29.5 HTTP Cookies 1024

29.6 Extending the Web Server 1025

29.6.1 Comparison of CGI and API 1026

29.7 Java 1026

29.7.1 JDBC 1030

29.7.2 SQLJ 1036

29.7.3 Comparison of JDBC and SQLJ 1036

29.7.4 Container-Managed Persistence (CMP) 1037

29.7.5 Java Data Objects (JDO) 1041

29.7.6 JPA (Java Persistence API) 1048

29.7.7 Java Servlets 1056

29.7.8 JavaServer Pages 1056

29.7.9 Java Web Services 1057

29.8 Microsoft’s Web Platform 1059

29.8.1 Universal Data Access 1060

29.8.2 Active Server Pages and ActiveX Data Objects 1061

29.8.3 Remote Data Services 1062

29.8.4 Comparison of ASP and JSP 1065

29.8.5 Microsoft .NET 1065

29.8.6 Microsoft Web Services 1070

29.9 Oracle Internet Platform 1071

29.9.1 Oracle WebLogic Server 1072

29.9.2 Oracle Metadata Repository 1073

29.9.3 Oracle Identity Management 1073

29.9.4 Oracle Portal 1074

29.9.5 Oracle WebCenter 1074

29.9.6 Oracle Business Intelligence (BI) Discoverer 1074

29.9.7 Oracle SOA (Service-Oriented Architecture) Suite 1075

Chapter Summary 1078

Review Questions 1079

Exercises 1079

 

Chapter 30 Semistructured Data and XML 1081

30.1 Semistructured Data 1082

30.1.1 Object Exchange Model (OEM) 1084

30.1.2 Lore and Lorel 1085

30.2 Introduction to XML 1089

30.2.1 Overview of XML 1092

30.2.2 Document Type Definitions (DTDs) 1094

30.3 XML-Related Technologies 1097

30.3.1 DOM and SAX Interfaces 1098

30.3.2 Namespaces 1099

30.3.3 XSL and XSLT 1099

30.3.4 XPath (XML Path Language) 1100

30.3.5 XPointer (XML Pointer Language) 1101

30.3.6 XLink (XML Linking Language) 1102

30.3.7 XHTML 1102

30.3.8 Simple Object Access Protocol (SOAP) 1103

30.3.9 Web Services Description Language (WSDL) 1104

30.3.10 Universal Discovery, Description, and Integration (UDDI) 1104

30.3.11 JSON (JavaScript Object Notation) 1106

30.4 XML Schema 1108

30.4.1 Resource Description Framework (RDF) 1114

30.5 XML Query Languages 1118

30.5.1 Extending Lore and Lorel to Handle XML 1119

30.5.2 XML Query Working Group 1120

30.5.3 XQuery–A Query Language for XML 1121

30.5.4 XML Information Set 1131

30.5.5 XQuery 1.0 and XPath 2.0 Data Model (XDM) 1132

30.5.6 XQuery Update Facility 1.0 1138

30.5.7 Formal Semantics 1140

30.6 XML and Databases 1148

30.6.1 Storing XML in Databases 1148

30.6.2 XML and SQL 1151

30.6.3 Native XML Databases 1165

30.7 XML in Oracle 1166

Chapter Summary 1169

Review Questions 1171

Exercises 1172

 

Part 9 Business Intelligence 1173

Chapter 31 Data Warehousing Concepts 1175

31.1 Introduction to Data Warehousing 1176

31.1.1 The Evolution of Data Warehousing 1176

31.1.2 Data Warehousing Concepts 1177

31.1.3 Benefits of Data Warehousing 1178

31.1.4 Comparison of OLTP Systems and Data Warehousing 1178

31.1.5 Problems of Data Warehousing 1180

31.1.6 Real-Time Data Warehouse 1182

31.2 Data Warehouse Architecture 1183

31.2.1 Operational Data 1183

31.2.2 Operational Data Store 1183

31.2.3 ETL Manager 1184

31.2.4 Warehouse Manager 1184

31.2.5 Query Manager 1185

31.2.6 Detailed Data 1185

31.2.7 Lightly and Highly Summarized Data 1185

31.2.8 Archive/Backup Data 1185

31.2.9 Metadata 1186

31.2.10 End-User Access Tools 1186

31.3 Data Warehousing Tools and Technologies 1187

31.3.1 Extraction, Transformation, and Loading (ETL) 1188

31.3.2 Data Warehouse DBMS 1189

31.3.3 Data Warehouse Metadata 1192

31.3.4 Administration and Management Tools 1194

31.4 Data Mart 1194

31.4.1 Reasons for Creating a Data Mart 1195

31.5 Data Warehousing and Temporal Databases 1195

31.5.1 Temporal Extensions to the SQL Standard 1198

31.6 Data Warehousing Using Oracle 1200

31.6.1 Warehouse Features in Oracle 11g 1203

31.6.2 Oracle Support for Temporal Data 1204

Chapter Summary 1205

Review Questions 1206

Exercises 1207

 

Chapter 32 Data Warehousing Design 1209

32.1 Designing a Data Warehouse Database 1210

32.2 Data Warehouse Development Methodologies 1210

32.3 Kimball’s Business Dimensional Lifecycle 1212

32.4 Dimensionality Modeling 1213

32.4.1 Comparison of DM and ER models 1216

32.5 The Dimensional Modeling Stage of Kimball’s Business Dimensional Lifecycle 1217

32.5.1 Create a High-Level Dimensional Model (Phase I) 1217

32.5.2 Identify All Dimension Attributes for the Dimensional Model (Phase II) 1222

32.6 Data Warehouse Development Issues 1225

32.7 Data Warehousing Design Using Oracle 1226

32.7.1 Oracle Warehouse Builder Components 1226

32.7.2 Using Oracle Warehouse Builder 1227

32.7.3 Warehouse Builder Features in Oracle 11g 1231

Chapter Summary 1232

Review Questions 1233

Exercises 1234

 

Chapter 33 OLAP 1237

33.1 Online Analytical Processing 1238

33.1.1 OLAP Benchmarks 1239

33.2 OLAP Applications 1239

33.3 Multidimensional Data Model 1241

33.3.1 Alternative Multidimensional Data Representations 1241

33.3.2 Dimensional Hierarchy 1243

33.3.3 Multidimensional Operations 1245

33.3.4 Multidimensional Schemas 1245

33.4 OLAP Tools 1245

33.4.1 Codd’s Rules for OLAP Tools 1246

33.4.2 OLAP Server–Implementation Issues 1247

33.4.3 Categories of OLAP Servers 1248

33.5 OLAP Extensions to the SQL Standard 1252

33.5.1 Extended Grouping Capabilities 1252

33.5.2 Elementary OLAP Operators 1257

33.6 Oracle OLAP 1259

33.6.1 Oracle OLAP Environment 1259

33.6.2 Platform for Business Intelligence Applications 1260

33.6.3 Oracle Database 1260

33.6.4 Oracle OLAP 1262

33.6.5 Performance 1263

33.6.6 System Management 1264

33.6.7 System Requirements 1264

33.6.8 OLAP Features in Oracle 11g 1264

Chapter Summary 1265

Review Questions 1265

Exercises 1265

 

Chapter 34 Data Mining 1267

34.1 Data Mining 1268

34.2 Data Mining Techniques 1268

34.2.1 Predictive Modeling 1270

34.2.2 Database Segmentation 1271

34.2.3 Link Analysis 1272

34.2.4 Deviation Detection 1273

34.3 The Data Mining Process 1274

34.3.1 The CRISP-DM Model 1274

34.4 Data Mining Tools 1275

34.5 Data Mining and Data Warehousing 1276

34.6 Oracle Data Mining (ODM) 1277

34.6.1 Data Mining Capabilities 1277

34.6.2 Enabling Data Mining Applications 1277

34.6.3 Predictions and Insights 1278

34.6.4 Oracle Data Mining Environment 1278

34.6.5 Data Mining Features in Oracle 11g 1279

Chapter Summary 1279

Review Questions 1280

Exercises 1280

 

Appendices 1281

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

A.1 Branch User Views of DreamHome A-1

A.1.1 Data Requirements A-1

A.1.2 Transaction Requirements (Sample) A-3

A.2 Staff User Views of DreamHome A-4

A.2.1 Data Requirements A-4

A.2.2 Transaction Requirements (Sample) A-5

B Other Case Studies B-1

B.1 The University Accommodation Office Case Study B-1

B.1.1 Data Requirements B-1

B.1.2 Query Transactions (Sample) B-3

B.2 The EasyDrive School of Motoring Case Study B-4

B.2.1 Data Requirements B-4

B.2.2 Query Transactions (Sample) B-5

B.3 The Wellmeadows Hospital Case Study B-5

B.3.1 Data Requirements B-5

B.3.2 Transaction Requirements (Sample) B-12

C Alternative ER Modeling Notations C-1

C.1 ER Modeling Using the Chen Notation C-1

C.2 ER Modeling Using the Crow’s Feet Notation C-1

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

Step 1: Build Conceptual Data Model D-1

Step 2: Build Logical Data Model D-2

Step 3: Translate Logical Data Model for Target DBMS D-5

Step 4: Design File Organizations and Indexes D-5

Step 5: Design User Views D-5

Step 6: Design Security Mechanisms D-5

Step 7: Consider the Introduction of Controlled

Redundancy D-6

Step 8: Monitor and Tune the Operational System D-6

E Introduction to Pyrrho: A Lightweight RDBMS E-1

E.1 Pyrrho Features E-2

E.2 Download and Install Pyrrho E-2

E.3 Getting Started E-3

E.4 The Connection String E-3

E.5 Pyrrho’s Security Model E-4

E.6 Pyrrho SQL Syntax E-4

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