Database systems: a practical approach to design, implementation, and management

Cover......Page 1
Title......Page 5
Copyright......Page 6
Contents......Page 13
Preface......Page 37
Part 1 Background......Page 51
 Chapter 1 Introduction to Databases......Page 53
  1.1 Introduction......Page 54
   1.2.1 File-Based Approach......Page 57
   1.2.2 Limitations of the File-Based Approach......Page 62
  1.3 Database Approach......Page 64
   1.3.1 The Database......Page 65
   1.3.2 The Database Management System (DBMS)......Page 66
   1.3.3 (Database) Application Programs......Page 67
   1.3.4 Components of the DBMS Environment......Page 68
   1.4.1 Data and Database Administrators......Page 71
   1.4.2 Database Designers......Page 72
  1.5 History of Database Management Systems......Page 73
  1.6 Advantages and Disadvantages of DBMSs......Page 77
  Chapter Summary......Page 81
  Exercises......Page 82
 Chapter 2 Database Environment......Page 85
  2.1 The Three-Level ANSI-SPARC Architecture......Page 86
   2.1.1 External Level......Page 87
   2.1.3 Internal Level......Page 88
   2.1.4 Schemas, Mappings, and Instances......Page 89
   2.1.5 Data Independence......Page 90
  2.2 Database Languages......Page 91
   2.2.2 The Data Manipulation Language (DML)......Page 92
   2.2.3 Fourth-Generation Languages (4GLs)......Page 94
  2.3 Data Models and Conceptual Modeling......Page 95
   2.3.2 Record-Based Data Models......Page 96
  2.4 Functions of a DBMS......Page 99
  Chapter Summary......Page 104
  Review Questions......Page 105
  Exercises......Page 106
 Chapter 3 Database Architectures and the Web......Page 107
   3.1.1 Teleprocessing......Page 108
   3.1.2 File-Server Architecture......Page 109
   3.1.3 Traditional Two-Tier Client–Server Architecture......Page 110
   3.1.4 Three-Tier Client–Server Architecture......Page 113
   3.1.5 N-Tier Architectures......Page 114
   3.1.6 Middleware......Page 115
   3.1.7 Transaction Processing Monitors......Page 117
   3.2.1 Web Services......Page 119
   3.2.2 Service-Oriented Architectures (SOA)......Page 121
  3.3 Distributed DBMSs......Page 122
  3.4 Data Warehousing......Page 125
  3.5 Cloud Computing......Page 127
   3.5.1 Benefits and Risks of Cloud Computing......Page 129
   3.5.2 Cloud-Based Database Solutions......Page 132
  3.6 Components of a DBMS......Page 136
   3.7.1 Oracle’s Logical Database Structure......Page 139
   3.7.2 Oracle’s Physical Database Structure......Page 142
  Chapter Summary......Page 146
  Exercises......Page 147
Part 2 The Relational Model and Languages......Page 149
 Chapter 4 The Relational Model......Page 151
  4.1 Brief History of the Relational Model......Page 152
   4.2.1 Relational Data Structure......Page 154
   4.2.2 Mathematical Relations......Page 157
   4.2.4 Properties of Relations......Page 158
   4.2.5 Relational Keys......Page 160
   4.2.6 Representing Relational Database Schemas......Page 161
   4.3.1 Nulls......Page 163
   4.3.3 Referential Integrity......Page 164
   4.4.1 Terminology......Page 165
   4.4.2 Purpose of Views......Page 166
  Chapter Summary......Page 167
  Exercises......Page 168
 Chapter 5 Relational Algebra and Relational Calculus......Page 169
   5.1.1 Unary Operations......Page 170
   5.1.2 Set Operations......Page 173
   5.1.3 Join Operations......Page 176
   5.1.4 Division Operation......Page 179
   5.1.5 Aggregation and Grouping Operations......Page 180
   5.1.6 Summary of the Relational Algebra Operations......Page 182
   5.2.1 Tuple Relational Calculus......Page 183
   5.2.2 Domain Relational Calculus......Page 186
  5.3 Other Languages......Page 188
  Review Questions......Page 189
  Exercises......Page 190
 Chapter 6 SQL: Data Manipulation......Page 193
   6.1.1 Objectives of SQL......Page 194
   6.1.2 History of SQL......Page 195
  6.2 Writing SQL Commands......Page 197
  6.3 Data Manipulation......Page 198
   6.3.1 Simple Queries......Page 199
   6.3.2 Sorting Results (ORDER BY Clause)......Page 207
   6.3.3 Using the SQL Aggregate Functions......Page 209
   6.3.4 Grouping Results (GROUP BY Clause)......Page 211
   6.3.5 Subqueries......Page 214
   6.3.6 ANY and ALL......Page 216
   6.3.7 Multi-table Queries......Page 218
   6.3.8 EXISTS and NOT EXISTS......Page 224
   6.3.9 Combining Result Tables (UNION, INTERSECT, EXCEPT)......Page 225
   6.3.10 Database Updates......Page 227
  Chapter Summary......Page 231
  Exercises......Page 232
 Chapter 7 SQL: Data Definition......Page 235
   7.1.1 SQL Identifiers......Page 236
   7.1.2 SQL Scalar Data Types......Page 237
   7.2.2 Domain Constraints......Page 242
   7.2.3 Entity Integrity......Page 243
   7.2.4 Referential Integrity......Page 244
   7.2.5 General Constraints......Page 245
   7.3.1 Creating a Database......Page 246
   7.3.2 Creating a Table (CREATE TABLE)......Page 247
   7.3.3 Changing a Table Definition (ALTER TABLE)......Page 250
   7.3.4 Removing a Table (DROP TABLE)......Page 251
   7.3.6 Removing an Index (DROP INDEX)......Page 252
   7.4.1 Creating a View (CREATE VIEW)......Page 253
   7.4.2 Removing a View (DROP VIEW)......Page 255
   7.4.3 View Resolution......Page 256
   7.4.5 View Updatability......Page 257
   7.4.6 WITH CHECK OPTION......Page 258
   7.4.7 Advantages and Disadvantages of Views......Page 260
   7.4.8 View Materialization......Page 262
  7.5 Transactions......Page 263
  7.6 Discretionary Access Control......Page 264
   7.6.1 Granting Privileges to Other Users (GRANT)......Page 266
   7.6.2 Revoking Privileges from Users (REVOKE)......Page 267
  Chapter Summary......Page 269
  Exercises......Page 270
 Chapter 8 Advanced SQL......Page 273
   8.1.1 Declarations......Page 274
   8.1.2 Assignments......Page 275
   8.1.3 Control Statements......Page 276
   8.1.4 Exceptions in PL/SQL......Page 278
   8.1.5 Cursors in PL/SQL......Page 279
  8.2 Subprograms, Stored Procedures, Functions, and Packages......Page 282
  8.3 Triggers......Page 283
  8.4 Recursion......Page 289
  Chapter Summary......Page 290
  Exercises......Page 291
 Chapter 9 Object-Relational DBMSs......Page 293
  9.1 Advanced Database Applications......Page 294
  9.2 Weaknesses of RDBMSs......Page 299
  9.3 Storing Objects in a Relational Database......Page 304
   9.3.1 Mapping Classes to Relations......Page 305
   9.3.2 Accessing Objects in the Relational Database......Page 306
  9.4 Introduction to Object-Relational Database Systems......Page 307
  9.5 SQL:2011......Page 310
   9.5.1 Row Types......Page 311
   9.5.2 User-Defined Types......Page 312
   9.5.3 Subtypes and Supertypes......Page 315
   9.5.4 User-Defined Routines......Page 316
   9.5.5 Polymorphism......Page 319
   9.5.7 Creating Tables......Page 320
   9.5.8 Querying Data......Page 323
   9.5.9 Collection Types......Page 325
   9.5.10 Typed Views......Page 328
   9.5.12 Triggers......Page 329
   9.5.13 Large Objects......Page 332
  9.6 Object-Oriented Extensions in Oracle......Page 333
   9.6.1 User-Defined Data Types......Page 334
   9.6.2 Manipulating Object Tables......Page 339
   9.6.3 Object Views......Page 340
   9.6.4 Privileges......Page 341
  Review Questions......Page 342
  Exercises......Page 343
Part 3 Database Analysis and Design......Page 345
 Chapter 10 Database System Development Lifecycle......Page 347
  10.1 The Information Systems Lifecycle......Page 348
  10.3 Database Planning......Page 349
  10.5 Requirements Collection and Analysis......Page 352
   10.5.2 View Integration Approach......Page 354
  10.6 Database Design......Page 356
   10.6.2 Data Modeling......Page 357
   10.6.3 Phases of Database Design......Page 358
   10.7.1 Selecting the DBMS......Page 361
  10.8 Application Design......Page 365
   10.8.1 Transaction Design......Page 366
   10.8.2 User Interface Design Guidelines......Page 367
  10.10 Implementation......Page 369
  10.12 Testing......Page 370
  10.13 Operational Maintenance......Page 371
  10.14 CASE Tools......Page 372
  Chapter Summary......Page 374
  Review Questions......Page 375
  Exercises......Page 376
 Chapter 11 Database Analysis and the DreamHome Case Study......Page 377
  11.1 When Are Fact-Finding Techniques Used?......Page 378
  11.2 What Facts Are Collected?......Page 379
   11.3.2 Interviewing......Page 380
   11.3.3 Observing the Enterprise in Operation......Page 381
   11.3.5 Questionnaires......Page 382
  11.4 Using Fact-Finding Techniques: A......Page 383
   11.4.1 The DreamHome Case Study—An Overview of the Current System......Page 384
   11.4.2 The DreamHome Case Study—Database Planning......Page 388
   11.4.3 The DreamHome Case Study—System Definition......Page 394
   11.4.4 The DreamHome Case Study—Requirements Collection and Analysis......Page 395
   11.4.5 The DreamHome Case Study—Database Design......Page 403
  Exercises......Page 404
 Chapter 12 Entity–Relationship Modeling......Page 407
  12.1 Entity Types......Page 408
  12.2 Relationship Types......Page 410
   12.2.1 Degree of Relationship Type......Page 412
   12.2.2 Recursive Relationship......Page 414
   12.3.1 Simple and Composite Attributes......Page 415
   12.3.3 Derived Attributes......Page 416
   12.3.4 Keys......Page 417
  12.4 Strong and Weak Entity Types......Page 419
  12.5 Attributes on Relationships......Page 420
  12.6 Structural Constraints......Page 421
   12.6.1 One-to-One (1:1) Relationships......Page 422
   12.6.2 One-to-Many (1:*) Relationships......Page 423
   12.6.3 Many-to-Many (*:*) Relationships......Page 424
   12.6.4 Multiplicity for Complex Relationships......Page 425
   12.6.5 Cardinality and Participation Constraints......Page 426
   12.7.1 Fan Traps......Page 428
   12.7.2 Chasm Traps......Page 430
  Review Questions......Page 432
  Exercises......Page 433
 Chapter 13 Enhanced Entity–Relationship Modeling ......Page 435
   13.1.1 Superclasses and Subclasses......Page 436
   13.1.2 Superclass/Subclass Relationships......Page 437
   13.1.4 Specialization Process......Page 438
   13.1.5 Generalization Process......Page 439
   13.1.6 Constraints on Specialization/Generalization......Page 442
   13.1.7 Worked Example of using Specialization/Generalization to Model the Branch View of the DreamHome Case Study......Page 443
  13.2 Aggregation......Page 447
  13.3 Composition......Page 448
  Chapter Summary......Page 449
  Exercises......Page 450
 Chapter 14 Normalization ......Page 453
  14.1 The Purpose of Normalization......Page 454
  14.2 How Normalization Supports Database Design......Page 455
  14.3 Data Redundancy and Update Anomalies......Page 456
   14.3.2 Deletion Anomalies......Page 457
   14.4.1 Characteristics of Functional Dependencies......Page 458
   14.4.2 Identifying Functional Dependencies......Page 462
   14.4.3 Identifying the Primary Key for a Relation Using Functional Dependencies......Page 465
  14.5 The Process of Normalization......Page 466
  14.6 First Normal Form (1NF)......Page 468
  14.7 Second Normal Form (2NF)......Page 472
  14.8 Third Normal Form (3NF)......Page 473
  14.9 General Definitions of 2NF and 3NF......Page 475
  Review Questions......Page 477
  Exercises......Page 478
 Chapter 15 Advanced Normalization ......Page 483
   15.1.1 Inference Rules for Functional Dependencies......Page 484
   15.1.2 Minimal Sets of Functional Dependencies......Page 486
   15.2.1 Definition of BCNF......Page 487
  15.3 Review of Normalization Up to BCNF......Page 490
  15.4 Fourth Normal Form (4NF)......Page 495
   15.4.1 Multi-Valued Dependency......Page 496
  15.5 Fifth Normal Form (5NF)......Page 497
   15.5.2 Definition of Fifth Normal Form......Page 498
  Review Questions......Page 500
  Exercises......Page 501
Part 4 Methodology ......Page 503
 Chapter 16 Methodology—Conceptual Database Design......Page 505
   16.1.1 What Is a Design Methodology?......Page 506
   16.1.3 Critical Success Factors in Database Design......Page 507
  16.2 Overview of the Database Design Methodology......Page 508
  Chapter Summary ......Page 510
  Review Questions......Page 526
  Exercises......Page 527
 Chapter 17 Methodology—Logical Database Design for the Relational Model ......Page 529
  Chapter Summary ......Page 530
  Exercises......Page 559
 Chapter 18 Methodology—Physical Database Design for Relational Databases......Page 563
  18.1 Comparison of Logical and Physical Database Design......Page 564
  18.2 Overview of the Physical Database Design Methodology......Page 565
  Step 3: Translate Logical Data Model for Target DBMS......Page 566
  Step 5: Design User Views ......Page 571
  Step 6: Design Security Mechanisms......Page 584
  Chapter Summary......Page 585
  Exercises......Page 586
  19.2 Monitoring the System to Improve Performance ......Page 587
  Step 8: Monitor and Tune the Operational System......Page 600
  Chapter Summary......Page 604
  Exercises......Page 605
Part 5 Selected Database Issues......Page 607
 Chapter 20 Security and Administration......Page 609
  20.1 Database Security......Page 610
   20.1.1 Threats......Page 611
  20.2 Countermeasures—Computer-Based Controls......Page 613
   20.2.1 Authorization......Page 614
   20.2.2 Access Controls......Page 615
   20.2.4 Backup and Recovery......Page 618
   20.2.6 Encryption......Page 619
   20.2.7 RAID (Redundant Array of Independent Disks)......Page 620
  20.3 Security in Microsoft Office Access DBMS......Page 623
  20.4 Security in Oracle DBMS......Page 625
  20.5 DBMSs and Web Security......Page 629
   20.5.2 Firewalls......Page 630
   20.5.4 Digital Certificates......Page 631
   20.5.6 Secure Sockets Layer and Secure HTTP......Page 632
   20.5.7 Secure Electronic Transactions and Secure Transaction Technology......Page 633
   20.5.8 Java Security......Page 634
  20.6 Data Administration and Database Administration......Page 636
   20.6.1 Data Administration......Page 637
   20.6.3 Comparison of Data and Database Administration......Page 638
  Chapter Summary......Page 639
  Exercises......Page 640
 Chapter 21 Professional, Legal, and Ethical Issues in Data Management......Page 643
   21.1.1 Defining Ethics in the Context of IT......Page 644
   21.1.2 The Difference Between Ethical and Legal Behavior......Page 645
   21.1.3 Ethical Behavior in IT......Page 646
   21.2.1 Securities and Exchange Commission (SEC) Regulation National Market System (NMS)......Page 647
   21.2.2 The Sarbanes-Oxley Act, COBIT, and COSO......Page 648
   21.2.3 The Health Insurance Portability and Accountability Act......Page 651
   21.2.4 The European Union (EU) Directive on Data Protection of 1995......Page 652
   21.2.5 The United Kingdom’s Data Protection Act of 1998......Page 653
   21.2.6 Access to Information Laws......Page 654
   21.2.7 International Banking—Basel II Accords......Page 656
   21.3.1 Developing an Organization-Wide Policy for Legal and Ethical Behavior......Page 657
   21.3.2 Professional Organizations and Codes of Ethics......Page 658
   21.3.3 Developing an Organization-Wide Policy for Legal and Ethical Behavior for DreamHome......Page 661
  21.4 Intellectual Property......Page 662
   21.4.2 Copyright......Page 663
   21.4.4 Intellectual Property Rights Issues for Software......Page 664
  Chapter Summary......Page 666
  Review Questions......Page 667
  Exercises......Page 668
 Chapter 22 Transaction Management......Page 669
  22.1 Transaction Support......Page 670
   22.1.2 Database Architecture......Page 673
   22.2.1 The Need for Concurrency Control......Page 674
   22.2.2 Serializability and Recoverability......Page 677
   22.2.3 Locking Methods......Page 685
   22.2.4 Deadlock......Page 691
   22.2.5 Timestamping Methods......Page 694
   22.2.6 Multiversion Timestamp Ordering......Page 697
   22.2.7 Optimistic Techniques......Page 698
   22.2.8 Granularity of Data Items......Page 699
   22.3.1 The Need for Recovery......Page 702
   22.3.2 Transactions and Recovery......Page 703
   22.3.3 Recovery Facilities......Page 706
   22.3.4 Recovery Techniques......Page 709
  22.4 Advanced Transaction Models......Page 711
   22.4.1 Nested Transaction Model......Page 713
   22.4.2 Sagas......Page 714
   22.4.3 Multilevel Transaction Model......Page 715
   22.4.4 Dynamic Restructuring......Page 716
   22.4.5 Workflow Models......Page 717
  22.5 Concurrency Control and Recovery in Oracle......Page 718
   22.5.2 Multiversion Read Consistency......Page 719
   22.5.4 Backup and Recovery......Page 721
  Chapter Summary......Page 724
  Review Questions......Page 725
  Exercises......Page 726
 Chapter 23 Query Processing......Page 729
  23.1 Overview of Query Processing......Page 731
  23.2 Query Decomposition......Page 734
   23.3.1 Transformation Rules for the Relational Algebra Operations......Page 738
   23.3.2 Heuristical Processing Strategies......Page 743
   23.4.1 Database Statistics......Page 744
   23.4.2 Selection Operation (S = σp(R))......Page 745
   23.4.3 Join Operation (T = (R ⋈F S))......Page 752
   23.4.4 Projection Operation (S = π A1, A2, . . . , A m(R))......Page 759
   23.4.5 The Relational Algebra Set Operations (T = R ∪ S, T = R ∩ S, T = R – S)......Page 761
  23.5 Enumeration of Alternative Execution Strategies......Page 762
   23.5.2 Linear Trees......Page 763
   23.5.3 Physical Operators and Execution Strategies......Page 764
   23.5.4 Reducing the Search Space......Page 766
   23.5.5 Enumerating Left-Deep Trees......Page 767
   23.5.6 Semantic Query Optimization......Page 768
   23.5.7 Alternative Approaches to Query Optimization......Page 769
  23.6 Query Processing and Optimization......Page 770
   23.6.1 New Index Types......Page 773
   23.7.1 Rule-Based and Cost-Based Optimization......Page 774
   23.7.2 Histograms......Page 778
   23.7.3 Viewing the Execution Plan......Page 780
  Chapter Summary......Page 781
  Review Questions......Page 782
  Exercises......Page 783
Part 6 Distributed DBMSs and Replication......Page 785
 Chapter 24 Distributed DBMSs—Concepts and Design......Page 787
  24.1 Introduction......Page 788
   24.1.1 Concepts......Page 789
   24.1.2 Advantages and Disadvantages of DDBMSs......Page 793
   24.1.3 Homogeneous and Heterogeneous DDBMSs......Page 796
  24.2 Overview of Networking......Page 799
   24.3.2 Reference Architecture for a DDBMS......Page 803
   24.3.3 Reference Architecture for a Federated MDBS......Page 805
   24.3.4 Component Architecture for a DDBMS......Page 806
  24.4 Distributed Relational Database Design......Page 807
   24.4.1 Data Allocation......Page 808
   24.4.2 Fragmentation......Page 809
   24.5.1 Distribution Transparency......Page 818
   24.5.2 Transaction Transparency......Page 821
   24.5.3 Performance Transparency......Page 824
   24.5.5 Summary of Transparencies in a DDBMS......Page 826
  24.6 Date’s Twelve Rules for a DDBMS......Page 827
  Chapter Summary......Page 829
  Exercises......Page 830
 Chapter 25 Distributed DBMSs—Advanced Concepts......Page 833
  25.1 Distributed Transaction Management......Page 834
   25.2.1 Objectives......Page 835
   25.2.3 Locking Protocols......Page 836
  25.3 Distributed Deadlock Management......Page 839
   25.4.1 Failures in a Distributed Environment......Page 843
   25.4.3 Two-Phase Commit (2PC)......Page 844
   25.4.4 Three-Phase Commit (3PC)......Page 851
   25.4.5 Network Partitioning......Page 854
  25.5 The X/Open Distributed Transaction Processing Model......Page 856
  25.6 Distributed Query Optimization......Page 858
   25.6.2 Distributed Joins......Page 863
   25.6.3 Global Optimization......Page 864
   25.7.1 Oracle’s DDBMS Functionality......Page 868
  Review Questions......Page 874
  Exercises......Page 875
 Chapter 26 Replication and Mobile Databases......Page 877
  26.1 Introduction to Data Replication......Page 878
   26.1.1 Applications of Replication......Page 879
   26.1.2 Replication Model......Page 880
   26.1.3 Functional Model of Replication Protocols......Page 881
   26.2.1 Kernel-Based Replication......Page 882
   26.2.2 Middleware-Based Replication......Page 883
   26.2.3 Processing of Updates......Page 884
   26.2.5 Update Location (Data Ownership)......Page 886
   26.2.6 Termination Protocols......Page 890
   26.3.1 Eager Primary Copy......Page 891
   26.3.2 Lazy Primary Copy......Page 896
   26.3.3 Eager Update Anywhere......Page 900
   26.3.4 Lazy Update Anywhere......Page 901
   26.3.5 Update Anywhere with Uniform Total Order Broadcast......Page 905
   26.3.6 SI and Uniform Total Order Broadcast Replication......Page 909
  26.4 Introduction to Mobile Databases......Page 915
   26.4.1 Mobile DBMSs......Page 917
   26.4.2 Issues with Mobile DBMSs......Page 918
   26.5.1 Oracle’s Replication Functionality......Page 931
  Chapter Summary......Page 938
  Exercises......Page 939
Part 7 Object DBMSs......Page 941
 Chapter 27 Object-Oriented DBMSs—Concepts and Design......Page 943
  27.1 Next-Generation Database Systems......Page 945
   27.2.1 Definition of Object-Oriented DBMSs......Page 947
   27.2.2 Functional Data Models......Page 948
   27.2.3 Persistent Programming Languages......Page 953
   27.2.4 Alternative Strategies for Developing an OODBMS......Page 955
  27.3 Persistence in OODBMSs......Page 956
   27.3.1 Pointer Swizzling Techniques......Page 958
   27.3.2 Accessing an Object......Page 961
   27.3.3 Persistence Schemes......Page 963
   27.3.4 Orthogonal Persistence......Page 964
   27.4.1 Transactions......Page 966
   27.4.2 Versions......Page 967
   27.4.3 Schema Evolution......Page 968
   27.4.4 Architecture......Page 971
   27.4.5 Benchmarking......Page 973
   27.5.1 Advantages......Page 976
   27.5.2 Disadvantages......Page 978
  27.6 Comparison of ORDBMS and OODBMS......Page 980
   27.7.1 Comparison of Object-Oriented Data Modeling......Page 981
   27.7.2 Relationships and Referential Integrity......Page 982
   27.7.3 Behavioral Design......Page 984
  27.8 Object-Oriented Analysis and Design with UML......Page 986
   27.8.1 UML Diagrams......Page 987
   27.8.2 Usage of UML in the Methodology for Database Design......Page 992
  Chapter Summary......Page 994
  Exercises......Page 995
 Chapter 28 Object-Oriented DBMSs—Standards and Systems......Page 997
   28.1.1 Background......Page 998
   28.1.2 The Common Object Request Broker Architecture......Page 1001
   28.1.3 Other OMG Specifications......Page 1006
  28.2 Object Data Standard ODMG 3.0, 1999......Page 1009
   28.2.1 Object Data Management Group......Page 1011
   28.2.2 The Object Model......Page 1012
   28.2.3 The Object Definition Language......Page 1020
   28.2.4 The Object Query Language......Page 1023
   28.2.5 Other Parts of the ODMG Standard......Page 1029
   28.2.6 Mapping the Conceptual Design to a Logical (Object-Oriented) Design......Page 1032
   28.3.1 Architecture......Page 1033
   28.3.2 Building an ObjectStore Application......Page 1036
   28.3.3 Data Definition in ObjectStore......Page 1037
   28.3.4 Data Manipulation in ObjectStore......Page 1041
  Chapter Summary......Page 1044
  Exercises......Page 1045
Part 8 The Web and DBMSs......Page 1047
 Chapter 29 Web Technology and DBMSs......Page 1049
  29.1 Introduction to the Internet and the Web......Page 1050
   29.1.1 Intranets and Extranets......Page 1052
   29.1.2 e-Commerce and e-Business......Page 1053
  29.2 The Web......Page 1054
   29.2.1 HyperText Transfer Protocol......Page 1055
   29.2.2 HyperText Markup Language......Page 1057
   29.2.3 Uniform Resource Locators......Page 1059
   29.2.5 Web Services......Page 1060
   29.2.6 Requirements for Web–DBMS Integration......Page 1061
   29.2.7 Advantages and Disadvantages of the Web–DBMS Approach......Page 1062
   29.2.8 Approaches to Integrating the Web and DBMSs......Page 1066
   29.3.1 JavaScript and JScript......Page 1067
   29.3.2 VBScript......Page 1068
  29.4 Common Gateway Interface (CGI)......Page 1069
   29.4.1 Passing Information to a CGI Script......Page 1071
   29.4.2 Advantages and Disadvantages of CGI......Page 1073
  29.5 HTTP Cookies......Page 1074
  29.6 Extending the Web Server......Page 1075
  29.7 Java......Page 1076
   29.7.1 JDBC......Page 1080
   29.7.3 Comparison of JDBC and SQLJ......Page 1086
   29.7.4 Container-Managed Persistence (CMP)......Page 1087
   29.7.5 Java Data Objects (JDO)......Page 1091
   29.7.6 JPA (Java Persistence API)......Page 1098
   29.7.8 JavaServer Pages......Page 1106
   29.7.9 Java Web Services......Page 1107
  29.8 Microsoft’s Web Platform......Page 1109
   29.8.1 Universal Data Access......Page 1110
   29.8.2 Active Server Pages and ActiveX Data Objects......Page 1111
   29.8.3 Remote Data Services......Page 1112
   29.8.5 Microsoft .NET......Page 1115
   29.8.6 Microsoft Web Services......Page 1120
  29.9 Oracle Internet Platform......Page 1121
   29.9.1 Oracle WebLogic Server......Page 1122
   29.9.3 Oracle Identity Management......Page 1123
   29.9.6 Oracle Business Intelligence (BI) Discoverer......Page 1124
   29.9.7 Oracle SOA (Service-Oriented Architecture) Suite......Page 1125
  Chapter Summary......Page 1128
  Exercises......Page 1129
 Chapter 30 Semistructured Data and XML......Page 1131
  30.1 Semistructured Data......Page 1132
   30.1.1 Object Exchange Model (OEM)......Page 1134
   30.1.2 Lore and Lorel......Page 1135
  30.2 Introduction to XML......Page 1139
   30.2.1 Overview of XML......Page 1142
   30.2.2 Document Type Definitions (DTDs)......Page 1144
  30.3 XML-Related Technologies......Page 1147
   30.3.1 DOM and SAX Interfaces......Page 1148
   30.3.3 XSL and XSLT......Page 1149
   30.3.4 XPath (XML Path Language)......Page 1150
   30.3.5 XPointer (XML Pointer Language)......Page 1151
   30.3.7 XHTML......Page 1152
   30.3.8 Simple Object Access Protocol (SOAP)......Page 1153
   30.3.10 Universal Discovery, Description, and Integration (UDDI)......Page 1154
   30.3.11 JSON (JavaScript Object Notation)......Page 1156
  30.4 XML Schema......Page 1158
   30.4.1 Resource Description Framework (RDF)......Page 1164
  30.5 XML Query Languages......Page 1168
   30.5.1 Extending Lore and Lorel to Handle XML......Page 1169
   30.5.2 XML Query Working Group......Page 1170
   30.5.3 XQuery—A Query Language for XML......Page 1171
   30.5.4 XML Information Set......Page 1181
   30.5.5 XQuery 1.0 and XPath 2.0 Data Model (XDM)......Page 1182
   30.5.6 XQuery Update Facility 1.0......Page 1188
   30.5.7 Formal Semantics......Page 1190
   30.6.1 Storing XML in Databases......Page 1198
   30.6.2 XML and SQL......Page 1201
   30.6.3 Native XML Databases......Page 1215
  30.7 XML in Oracle......Page 1216
  Chapter Summary......Page 1219
  Review Questions......Page 1221
  Exercises......Page 1222
Part 9 Business Intelligence......Page 1223
 Chapter 31 Data Warehousing Concepts......Page 1225
   31.1.1 The Evolution of Data Warehousing......Page 1226
   31.1.2 Data Warehousing Concepts......Page 1227
   31.1.4 Comparison of OLTP Systems and Data Warehousing......Page 1228
   31.1.5 Problems of Data Warehousing......Page 1230
   31.1.6 Real-Time Data Warehouse......Page 1232
   31.2.2 Operational Data Store......Page 1233
   31.2.4 Warehouse Manager......Page 1234
   31.2.8 Archive/Backup Data......Page 1235
   31.2.10 End-User Access Tools......Page 1236
  31.3 Data Warehousing Tools and Technologies......Page 1237
   31.3.1 Extraction, Transformation, and Loading (ETL)......Page 1238
   31.3.2 Data Warehouse DBMS......Page 1239
   31.3.3 Data Warehouse Metadata......Page 1242
  31.4 Data Mart......Page 1244
  31.5 Data Warehousing and Temporal Databases......Page 1245
   31.5.1 Temporal Extensions to the SQL Standard......Page 1248
  31.6 Data Warehousing Using Oracle......Page 1250
   31.6.1 Warehouse Features in Oracle 11g......Page 1253
   31.6.2 Oracle Support for Temporal Data......Page 1254
  Chapter Summary......Page 1255
  Review Questions......Page 1256
  Exercises......Page 1257
 Chapter 32 Data Warehousing Design......Page 1259
  32.2 Data Warehouse Development Methodologies......Page 1260
  32.3 Kimball’s Business Dimensional Lifecycle......Page 1262
  32.4 Dimensionality Modeling......Page 1263
   32.4.1 Comparison of DM and ER models......Page 1266
   32.5.1 Create a High-Level Dimensional Model (Phase I)......Page 1267
   32.5.2 Identify All Dimension Attributes for the Dimensional Model (Phase II)......Page 1272
  32.6 Data Warehouse Development Issues......Page 1275
   32.7.1 Oracle Warehouse Builder Components......Page 1276
   32.7.2 Using Oracle Warehouse Builder......Page 1277
   32.7.3 Warehouse Builder Features in Oracle 11g......Page 1281
  Chapter Summary......Page 1282
  Review Questions......Page 1283
  Exercises......Page 1284
 Chapter 33 OLAP......Page 1287
  33.1 Online Analytical Processing......Page 1288
  33.2 OLAP Applications......Page 1289
   33.3.1 Alternative Multidimensional Data Representations......Page 1291
   33.3.2 Dimensional Hierarchy......Page 1293
  33.4 OLAP Tools......Page 1295
   33.4.1 Codd’s Rules for OLAP Tools......Page 1296
   33.4.2 OLAP Server—Implementation Issues......Page 1297
   33.4.3 Categories of OLAP Servers......Page 1298
   33.5.1 Extended Grouping Capabilities......Page 1302
   33.5.2 Elementary OLAP Operators......Page 1307
   33.6.1 Oracle OLAP Environment......Page 1309
   33.6.3 Oracle Database......Page 1310
   33.6.5 Performance......Page 1312
   33.6.8 OLAP Features in Oracle 11g......Page 1314
  Exercises......Page 1315
 Chapter 34 Data Mining......Page 1317
  34.2 Data Mining Techniques......Page 1318
   34.2.1 Predictive Modeling......Page 1320
   34.2.2 Database Segmentation......Page 1321
   34.2.3 Link Analysis......Page 1322
   34.2.4 Deviation Detection......Page 1323
   34.3.1 The CRISP-DM Model......Page 1324
  34.4 Data Mining Tools......Page 1325
  34.5 Data Mining and Data Warehousing......Page 1326
   34.6.1 Data Mining Capabilities......Page 1327
   34.6.4 Oracle Data Mining Environment......Page 1328
  Chapter Summary......Page 1329
  Exercises......Page 1330
 Appendices......Page 1331
    A.1.1 Data Requirements......Page 1333
    A.1.2 Transaction Requirements (Sample)......Page 1335
    A.2.1 Data Requirements......Page 1336
    A.2.2 Transaction Requirements (Sample)......Page 1337
    B.1.1 Data Requirements......Page 1339
    B.1.2 Query Transactions (Sample)......Page 1341
    B.2.1 Data Requirements......Page 1342
    B.3.1 Data Requirements......Page 1343
    B.3.2 Transaction Requirements (Sample)......Page 1350
   C.2 ER Modeling Using the Crow’s Feet Notation......Page 1351
   Step 1: Build Conceptual Data Model......Page 1357
   Step 2: Build Logical Data Model......Page 1358
   Step 6: Design Security Mechanisms......Page 1361
   Step 8: Monitor and Tune the Operational System......Page 1362
  E Introduction to Pyrrho: A Lightweight RDBMS......Page 1363
   E.2 Download and Install Pyrrho......Page 1364
   E.4 The Connection String......Page 1365
   E.6 Pyrrho SQL Syntax......Page 1366
References......Page 1381
Further Reading......Page 1399
Index......Page 1413