Menu Expand
Database Systems: A Practical Approach to Design, Implementation, and Management, Global Edition

Database Systems: A Practical Approach to Design, Implementation, and Management, Global Edition

Thomas Connolly | Carolyn Begg

(2015)

Additional Information

Book Details

Abstract

This book 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

Table of Contents

Section Title Page Action Price
Cover Cover
Access for Companion Website IBC
Database Systems 3
Copyright 4
Dedication 5
Brief Contents 7
Contents 11
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
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
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
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 Definitio 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
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
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
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
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
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 488
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
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.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
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.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 732
23.3 Heuristical Approach to Query Optimization 736
23.3.1 Transformation Rules for the Relational Algebra Operations 736
23.3.2 Heuristical Processing Strategies 741
23.4 Cost Estimation for the Relational Algebra Operations 742
23.4.1 Database Statistics 742
23.4.2 Selection Operation (S = σp(R)) 743
23.4.3 Join Operation (T = (R ⋈F S)) 750
23.4.4 Projection Operation (S = пA1, A2, . . . , A m(R)) 757
23.4.5 The Relational Algebra Set Operations (T = R ∪ S, T = R ∩ S, T = R – S) 759
23.5 Enumeration of Alternative Execution Strategies 760
23.5.1 Pipelining 761
23.5.2 Linear Trees 761
23.5.3 Physical Operators and Execution Strategies 762
23.5.4 Reducing the Search Space 764
23.5.5 Enumerating Left-Deep Trees 765
23.5.6 Semantic Query Optimization 766
23.5.7 Alternative Approaches to Query Optimization 767
23.5.8 Distributed Query Optimization 768
23.6 Query Processing and Optimization 768
23.6.1 New Index Types 771
23.7 Query Optimization in Oracle 772
23.7.1 Rule-Based and Cost-Based Optimization 772
23.7.2 Histograms 776
23.7.3 Viewing the Execution Plan 778
Chapter Summary 779
Review Questions 780
Exercises 781
Part 6: Distributed DBMSs and Replication 783
Chapter 24: Distributed DBMSs—Concepts and Design 785
24.1 Introduction 786
24.1.1 Concepts 787
24.1.2 Advantages and Disadvantages of DDBMSs 791
24.1.3 Homogeneous and Heterogeneous DDBMSs 794
24.2 Overview of Networking 797
24.3 Functions and Architectures of a DDBMS 801
24.3.1 Functions of a DDBMS 801
24.3.2 Reference Architecture for a DDBMS 801
24.3.3 Reference Architecture for a Federated MDBS 803
24.3.4 Component Architecture for a DDBMS 804
24.4 Distributed Relational Database Design 805
24.4.1 Data Allocation 806
24.4.2 Fragmentation 807
24.5 Transparencies in a DDBMS 816
24.5.1 Distribution Transparency 816
24.5.2 Transaction Transparency 819
24.5.3 Performance Transparency 822
24.5.4 DBMS Transparency 824
24.5.5 Summary of Transparencies in a DDBMS 824
24.6 Date’s Twelve Rules for a DDBMS 825
Chapter Summary 827
Review Questions 828
Exercises 828
Chapter 25: Distributed DBMSs—Advanced Concepts 831
25.1 Distributed Transaction Management 832
25.2 Distributed Concurrency Control 833
25.2.1 Objectives 833
25.2.2 Distributed Serializability 834
25.2.3 Locking Protocols 834
25.3 Distributed Deadlock Management 837
25.4 Distributed Database Recovery 840
25.4.1 Failures in a Distributed Environment 841
25.4.2 How Failures Affect Recovery 842
25.4.3 Two-Phase Commit (2PC) 842
25.4.4 Three-Phase Commit (3PC) 849
25.4.5 Network Partitioning 852
25.5 The X/Open Distributed Transaction Processing Model 854
25.6 Distributed Query Optimization 856
25.6.1 Data Localization 858
25.6.2 Distributed Joins 861
25.6.3 Global Optimization 862
25.7 Distribution in Oracle 866
25.7.1 Oracle’s DDBMS Functionality 866
Chapter Summary 872
Review Questions 872
Exercises 873
Chapter 26: Replication and Mobile Databases 875
26.1 Introduction to Data Replication 876
26.1.1 Applications of Replication 877
26.1.2 Replication Model 878
26.1.3 Functional Model of Replication Protocols 879
26.1.4 Consistency 880
26.2 Replication Architecture 880
26.2.1 Kernel-Based Replication 880
26.2.2 Middleware-Based Replication 881
26.2.3 Processing of Updates 882
26.2.4 Propagation of Updates 884
26.2.5 Update Location (Data Ownership) 884
26.2.6 Termination Protocols 888
26.3 Replication Schemes 889
26.3.1 Eager Primary Copy 889
26.3.2 Lazy Primary Copy 894
26.3.3 Eager Update Anywhere 898
26.3.4 Lazy Update Anywhere 899
26.3.5 Update Anywhere with Uniform Total Order Broadcast 903
26.3.6 SI and Uniform Total Order Broadcast Replication 907
26.4 Introduction to Mobile Databases 913
26.4.1 Mobile DBMSs 915
26.4.2 Issues with Mobile DBMSs 916
26.5 Oracle Replication 929
26.5.1 Oracle’s Replication Functionality 929
Chapter Summary 936
Review Questions 937
Exercises 937
Part 7: Object DBMSs 939
Chapter 27: Object-Oriented DBMSs—Concepts and Design 941
27.1 Next-Generation Database Systems 943
27.2 Introduction to OODBMSs 945
27.2.1 Definition of Object-Oriented DBMSs 945
27.2.2 Functional Data Models 946
27.2.3 Persistent Programming Languages 951
27.2.4 Alternative Strategies for Developing an OODBMS 953
27.3 Persistence in OODBMSs 954
27.3.1 Pointer Swizzling Techniques 956
27.3.2 Accessing an Object 959
27.3.3 Persistence Schemes 961
27.3.4 Orthogonal Persistence 962
27.4 Issues in OODBMSs 964
27.4.1 Transactions 964
27.4.2 Versions 965
27.4.3 Schema Evolution 966
27.4.4 Architecture 969
27.4.5 Benchmarking 971
27.5 Advantages and Disadvantages of OODBMSs 974
27.5.1 Advantages 974
27.5.2 Disadvantages 976
27.6 Comparison of ORDBMS and OODBMS 978
27.7 Object-Oriented Database Design 979
27.7.1 Comparison of Object-Oriented Data Modeling and Conceptual Data Modeling 979
27.7.2 Relationships and Referential Integrity 980
27.7.3 Behavioral Design 982
27.8 Object-Oriented Analysis and Design with UML 984
27.8.1 UML Diagrams 985
27.8.2 Usage of UML in the Methodology for Database Design 990
Chapter Summary 992
Review Questions 993
Exercises 993
Chapter 28: Object-Oriented DBMSs—Standards and Systems 995
28.1 Object Management Group 996
28.1.1 Background 996
28.1.2 The Common Object Request Broker Architecture 999
28.1.3 Other OMG Specifications 1004
28.1.4 Model-Driven Architecture 1007
28.2 Object Data Standard ODMG 3.0, 1999 1007
28.2.1 Object Data Management Group 1009
28.2.2 The Object Model 1010
28.2.3 The Object Definition Language 1018
28.2.4 The Object Query Language 1021
28.2.5 Other Parts of the ODMG Standard 1027
28.2.6 Mapping the Conceptual Design to a Logical (Object-Oriented) Design 1030
28.3 ObjectStore 1031
28.3.1 Architecture 1031
28.3.2 Building an ObjectStore Application 1034
28.3.3 Data Definition in ObjectStore 1035
28.3.4 Data Manipulation in ObjectStore 1039
Chapter Summary 1042
Review Questions 1043
Exercises 1043
Part 8: The Web and DBMSs 1045
Chapter 29: Web Technology and DBMSs 1047
29.1 Introduction to the Internet and the Web 1048
29.1.1 Intranets and Extranets 1050
29.1.2 e-Commerce and e-Business 1051
29.2 The Web 1052
29.2.1 HyperText Transfer Protocol 1053
29.2.2 HyperText Markup Language 1055
29.2.3 Uniform Resource Locators 1057
29.2.4 Static and Dynamic Web Pages 1058
29.2.5 Web Services 1058
29.2.6 Requirements for Web–DBMS Integration 1059
29.2.7 Advantages and Disadvantages of the Web–DBMS Approach 1060
29.2.8 Approaches to Integrating the Web and DBMSs 1064
29.3 Scripting Languages 1065
29.3.1 JavaScript and JScript 1065
29.3.2 VBScript 1066
29.3.3 Perl and PHP 1067
29.4 Common Gateway Interface (CGI) 1067
29.4.1 Passing Information to a CGI Script 1069
29.4.2 Advantages and Disadvantages of CGI 1071
29.5 HTTP Cookies 1072
29.6 Extending the Web Server 1073
29.6.1 Comparison of CGI and API 1074
29.7 Java 1074
29.7.1 JDBC 1078
29.7.2 SQLJ 1084
29.7.3 Comparison of JDBC and SQLJ 1084
29.7.4 Container-Managed Persistence (CMP) 1085
29.7.5 Java Data Objects (JDO) 1089
29.7.6 JPA (Java Persistence API) 1096
29.7.7 Java Servlets 1104
29.7.8 JavaServer Pages 1104
29.7.9 Java Web Services 1105
29.8 Microsoft’s Web Platform 1107
29.8.1 Universal Data Access 1108
29.8.2 Active Server Pages and ActiveX Data Objects 1109
29.8.3 Remote Data Services 1110
29.8.4 Comparison of ASP and JSP 1113
29.8.5 Microsoft .NET 1113
29.8.6 Microsoft Web Services 1118
29.9 Oracle Internet Platform 1119
29.9.1 Oracle WebLogic Server 1120
29.9.2 Oracle Metadata Repository 1121
29.9.3 Oracle Identity Management 1121
29.9.4 Oracle Portal 1122
29.9.5 Oracle WebCenter 1122
29.9.6 Oracle Business Intelligence (BI) Discoverer 1122
29.9.7 Oracle SOA (Service-Oriented Architecture) Suite 1123
Chapter Summary 1126
Review Questions 1127
Exercises 1127
Chapter 30: Semistructured Data and XML 1129
30.1 Semistructured Data 1130
30.1.1 Object Exchange Model (OEM) 1132
30.1.2 Lore and Lorel 1133
30.2 Introduction to XML 1137
30.2.1 Overview of XML 1140
30.2.2 Document Type Definitions (DTDs) 1142
30.3 XML-Related Technologies 1145
30.3.1 DOM and SAX Interfaces 1146
30.3.2 Namespaces 1147
30.3.3 XSL and XSLT 1147
30.3.4 XPath (XML Path Language) 1148
30.3.5 XPointer (XML Pointer Language) 1149
30.3.6 XLink (XML Linking Language) 1150
30.3.7 XHTML 1150
30.3.8 Simple Object Access Protocol (SOAP) 1151
30.3.9 Web Services Description Language (WSDL) 1152
30.3.10 Universal Discovery, Description, and Integration (UDDI) 1152
30.3.11 JSON (JavaScript Object Notation) 1154
30.4 XML Schema 1156
30.4.1 Resource Description Framework (RDF) 1162
30.5 XML Query Languages 1166
30.5.1 Extending Lore and Lorel to Handle XML 1167
30.5.2 XML Query Working Group 1168
30.5.3 XQuery—A Query Language for XML 1169
30.5.4 XML Information Set 1179
30.5.5 XQuery 1.0 and XPath 2.0 Data Model (XDM) 1180
30.5.6 XQuery Update Facility 1.0 1186
30.5.7 Formal Semantics 1188
30.6 XML and Databases 1196
30.6.1 Storing XML in Databases 1196
30.6.2 XML and SQL 1199
30.6.3 Native XML Databases 1213
30.7 XML in Oracle 1214
Chapter Summary 1217
Review Questions 1219
Exercises 1220
Part 9: Business Intelligence 1221
Chapter 31: Data Warehousing Concepts 1223
31.1 Introduction to Data Warehousing 1224
31.1.1 The Evolution of Data Warehousing 1224
31.1.2 Data Warehousing Concepts 1225
31.1.3 Benefits of Data Warehousing 1226
31.1.4 Comparison of OLTP Systems and Data Warehousing 1226
31.1.5 Problems of Data Warehousing 1228
31.1.6 Real-Time Data Warehouse 1230
31.2 Data Warehouse Architecture 1231
31.2.1 Operational Data 1231
31.2.2 Operational Data Store 1231
31.2.3 ETL Manager 1232
31.2.4 Warehouse Manager 1232
31.2.5 Query Manager 1233
31.2.6 Detailed Data 1233
31.2.7 Lightly and Highly Summarized Data 1233
31.2.8 Archive/Backup Data 1233
31.2.9 Metadata 1234
31.2.10 End-User Access Tools 1234
31.3 Data Warehousing Tools and Technologies 1235
31.3.1 Extraction, Transformation, and Loading (ETL) 1236
31.3.2 Data Warehouse DBMS 1237
31.3.3 Data Warehouse Metadata 1240
31.3.4 Administration and Management Tools 1242
31.4 Data Mart 1242
31.4.1 Reasons for Creating a Data Mart 1243
31.5 Data Warehousing and Temporal Databases 1243
31.5.1 Temporal Extensions to the SQL Standard 1246
31.6 Data Warehousing Using Oracle 1248
31.6.1 Warehouse Features in Oracle 11g 1251
31.6.2 Oracle Support for Temporal Data 1252
Chapter Summary 1253
Review Questions 1254
Exercises 1255
Chapter 32: Data Warehousing Design 1257
32.1 Designing a Data Warehouse Database 1258
32.2 Data Warehouse Development Methodologies 1258
32.3 Kimball’s Business Dimensional Lifecycle 1260
32.4 Dimensionality Modeling 1261
32.4.1 Comparison of DM and ER models 1264
32.5 The Dimensional Modeling Stage of Kimball’s Business Dimensional Lifecycle 1265
32.5.1 Create a High-Level Dimensional Model (Phase I) 1265
32.5.2 Identify All Dimension Attributes for the Dimensional Model (Phase II) 1270
32.6 Data Warehouse Development Issues 1273
32.7 Data Warehousing Design Using Oracle 1274
32.7.1 Oracle Warehouse Builder Components 1274
32.7.2 Using Oracle Warehouse Builder 1275
32.7.3 Warehouse Builder Features in Oracle 11g 1279
Chapter Summary 1280
Review Questions 1281
Exercises 1282
Chapter 33: OLAP 1285
33.1 Online Analytical Processing 1286
33.1.1 OLAP Benchmarks 1287
33.2 OLAP Applications 1287
33.3 Multidimensional Data Model 1289
33.3.1 Alternative Multidimensional Data Representations 1289
33.3.2 Dimensional Hierarchy 1291
33.3.3 Multidimensional Operations 1293
33.3.4 Multidimensional Schemas 1293
33.4 OLAP Tools 1293
33.4.1 Codd’s Rules for OLAP Tools 1294
33.4.2 OLAP Server—Implementation Issues 1295
33.4.3 Categories of OLAP Servers 1296
33.5 OLAP Extensions to the SQL Standard 1300
33.5.1 Extended Grouping Capabilities 1300
33.5.2 Elementary OLAP Operators 1305
33.6 Oracle OLAP 1307
33.6.1 Oracle OLAP Environment 1307
33.6.2 Platform for Business Intelligence Applications 1308
33.6.3 Oracle Database 1308
33.6.4 Oracle OLAP 1310
33.6.5 Performance 1311
33.6.6 System Management 1312
33.6.7 System Requirements 1312
33.6.8 OLAP Features in Oracle 11g 1312
Chapter Summary 1313
Review Questions 1313
Exercises 1313
Chapter 34: Data Mining 1315
34.1 Data Mining 1316
34.2 Data Mining Techniques 1316
34.2.1 Predictive Modeling 1318
34.2.2 Database Segmentation 1319
34.2.3 Link Analysis 1320
34.2.4 Deviation Detection 1321
34.3 The Data Mining Process 1322
34.3.1 The CRISP-DM Model 1322
34.4 Data Mining Tools 1323
34.5 Data Mining and Data Warehousing 1324
34.6 Oracle Data Mining (ODM) 1325
34.6.1 Data Mining Capabilities 1325
34.6.2 Enabling Data Mining Applications 1325
34.6.3 Predictions and Insights 1326
34.6.4 Oracle Data Mining Environment 1326
34.6.5 Data Mining Features in Oracle 11g 1327
Chapter Summary 1327
Review Questions 1328
Exercises 1328
Appendices 1329
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
References R-1
Further Reading FR-1
Index IN-1