书籍详情
数据库系统：设计、实现与管理（第五版 英文版）
作者：（英）康诺利，（英）贝格 著
出版社：电子工业出版社
出版时间：2012-01-01
ISBN：9787121149962
定价：¥109.00
购买这本书可以去
内容简介
《数据库系统：设计、实现与管理（第5版）（英文版）》是数据库领域的经典著作，内容系统全面，实用性强，被世界多所大学选为数据库相关课程的教材。《数据库系统：设计、实现与管理（第5版）（英文版）》主要内容有：数据库系统和数据库设计的基本知识；关系模型和关系语言；数据库分析和设计的主要技术；数据库设计方法学；数据库安全、事务管理、查询处理与优化；分布式DBMS与数据复制技术；面向对象数据库技术；DBMS与Web技术的结合，半结构化数据与XML；与商务智能有关的一些日益重要的技术，包括数据仓库、联机分析处理和数据挖掘以及数据库架构等。
作者简介
暂缺《数据库系统：设计、实现与管理（第五版 英文版）》作者简介
目录
Contents
PART 1 Background
CHAPTER 1 Introduction to Databases 2
1.1 Introduction 3
1.2 Traditional File-Based Systems 5
1.2.1 File-Based Approach 5
1.2.2 Limitations of the File-Based Approach 10
1.3 Database Approach 12
1.3.1 The Database 12
1.3.2 The Database Management System (DBMS) 13
1.3.3 (Database) Application Programs 14
1.3.4 Components of the DBMS Environment 16
1.3.5 Database Design: The Paradigm Shift 18
1.4 Roles in the Database Environment 18
1.4.1 Data and Database Administrators 19
1.4.2 Database Designers 19
1.4.3 Application Developers 20
1.4.4 End-Users 20
1.5 History of Database Management Systems 20
1.6 Advantages and Disadvantages of DBMSs 23
Chapter Summary 27
Review Questions 28
Exercises 28
CHAPTER 2 Database Environment 30
2.1 The Three-Level ANSI-SPARC Architecture 31
2.1.1 External Level 32
2.1.2 Conceptual Level 32
2.1.3 Internal Level 32
2.1.4 Schemas, Mappings, and Instances 33
2.1.5 Data Independence 34
2.2 Database Languages 35
2.2.1 The Data Definition Language (DDL) 35
2.2.2 The Data Manipulation Language (DML) 36
2.2.3 Fourth-Generation Languages (4GLs) 37
2.3 Data Models and Conceptual Modeling 38
2.3.1 Object-Based Data Models 39
2.3.2 Record-Based Data Models 39
2.3.3 Physical Data Models 42
2.3.4 Conceptual Modeling 42
2.4 Functions of a DBMS 42
Chapter Summary 46
Review Questions 47
Exercises 47
CHAPTER 3 Database Architectures and the Web 49
3.1 Multi-user DBMS Architectures 49
3.1.1 Teleprocessing 50
3.1.2 File-Server Architecture 50
3.1.3 Traditional Two-Tier Client-Server Architecture 51
3.1.4 Three-Tier Client-Server Architecture 53
3.1.5 N-Tier Architectures 55
3.1.6 Middleware 56
3.1.7 Transaction Processing Monitors 58
3.2 Web Services and Service-Oriented Architectures 59
3.2.1 Web Services 59
3.2.2 Service-Oriented Architectures (SOA) 60
3.3 Distributed DBMSs 62
3.4 Data Warehousing 64
3.5 Components of a DBMS 66
3.6 Oracle Architecture 69
3.6.1 Oracle誷 Logical Database Structure 69
3.6.2 Oracle誷 Physical Database Structure 71
Chapter Summary 74
Review Questions 75
Exercises 75
PART 2 The Relational Model and Languages
CHAPTER 4 The Relational Model 78
4.1 Brief History of the Relational Model 79
4.2 Terminology 80
4.2.1 Relational Data Structure 80
4.2.2 Mathematical Relations 82
4.2.3 Database Relations 83
4.2.4 Properties of Relations 84
4.2.5 Relational Keys 85
4.2.6 Representing Relational Database Schemas 86
4.3 Integrity Constraints 88
4.3.1 Nulls 88
4.3.2 Entity Integrity 88
4.3.3 Referential Integrity 89
4.3.4 General Constraints 89
4.4 Views 90
4.4.1 Terminology 90
4.4.2 Purpose of Views 90
4.4.3 Updating Views 91
Chapter Summary 91
Review Questions 92
Exercises 92
CHAPTER 5 Relational Algebra and Relational Calculus 94
5.1 The Relational Algebra 95
5.1.1 Unary Operations 96
5.1.2 Set Operations 97
5.1.3 Join Operations 100
5.1.4 Division Operation 103
5.1.5 Aggregation and Grouping Operations 103
5.1.6 Summary of the Relational Algebra Operations 105
5.2 The Relational Calculus 106
5.2.1 Tuple Relational Calculus 106
5.2.2 Domain Relational Calculus 109
5.3 Other Languages 111
Chapter Summary 111
Review Questions 112
Exercises 112
CHAPTER 6 SQL: Data Manipulation 115
6.1 Introduction to SQL 116
6.1.1 Objectives of SQL 116
6.1.2 History of SQL 117
6.1.3 Importance of SQL 118
6.1.4 Terminology 119
6.2 Writing SQL Commands 119
6.3 Data Manipulation 120
6.3.1 Simple Queries 120
6.3.2 Sorting Results (ORDER BY Clause) 127
6.3.3 Using the SQL Aggregate Functions 128
6.3.4 Grouping Results (GROUP BY Clause) 130
6.3.5 Subqueries 133
6.3.6 ANY and ALL 135
6.3.7 Multi-table Queries 136
6.3.8 EXISTS and NOT EXISTS 141
6.3.9 Combining Result Tables (UNION, INTERSECT, EXCEPT) 142
6.3.10 Database Updates 144
Chapter Summary 148
Review Questions 149
Exercises 149
CHAPTER 7 SQL: Data Definition 151
7.1 The ISO SQL Data Types 152
7.1.1 SQL Identifiers 152
7.1.2 SQL Scalar Data Types 152
7.1.3 Exact Numeric Data 153
7.2 Integrity Enhancement Feature 156
7.2.1 Required Data 156
7.2.2 Domain Constraints 156
7.2.3 Entity Integrity 157
7.2.4 Referential Integrity 158
7.2.5 General Constraints 159
7.3 Data Definition 159
7.3.1 Creating a Database 160
7.3.2 Creating a Table (CREATE TABLE) 160
7.3.3 Changing a Table Definition (ALTER TABLE) 163
7.3.4 Removing a Table (DROP TABLE) 164
7.3.5 Creating an Index (CREATE INDEX) 165
7.3.6 Removing an Index (DROP INDEX) 166
7.4 Views 166
7.4.1 Creating a View (CREATE VIEW) 166
7.4.2 Removing a View (DROP VIEW) 168
7.4.3 View Resolution 169
7.4.4 Restrictions on Views 170
7.4.5 View Updatability 170
7.4.6 WITH CHECK OPTION 171
7.4.7 Advantages and Disadvantages of Views 172
7.4.8 View Materialization 174
7.5 Transactions 175
7.5.1 Immediate and Deferred Integrity Constraints 176
7.6 Discretionary Access Control 176
7.6.1 Granting Privileges to Other Users (GRANT) 177
7.6.2 Revoking Privileges from Users (REVOKE) 179
Chapter Summary 180
Review Questions 181
Exercises 181
CHAPTER 8 Advanced SQL 184
8.1 The SQL Programming Language 184
8.1.1 Declarations 185
8.1.2 Assignments 186
8.1.3 Control Statements 186
8.1.4 Exceptions in PL/SQL 188
8.1.5 Cursors in PL/SQL 189
8.2 Subprograms, Stored Procedures, Functions, and Packages192
8.3 Triggers 193
8.4 Recursion 198
Chapter Summary 199
Review Questions 200
Exercises 200
CHAPTER 9 Query-By-Example 202
9.1 Introduction to Microsoft Office Access Queries 203
9.2 Building Select Queries Using QBE 204
9.2.1 Specifying Criteria 205
9.2.2 Creating Multi-table Queries 207
9.2.3 Calculating Totals 208
9.3 Using Advanced Queries 210
9.3.1 Parameter Query 210
9.3.2 Crosstab Query 211
9.3.3 Find Duplicates Query 213
9.3.4 Find Unmatched Query 213
9.3.5 Autolookup Query 214
9.4 Changing the Content of Tables Using Action Queries 215
9.4.1 Make-Table Action Query 215
9.4.2 Delete Action Query 217
9.4.3 Update Action Query 217
9.4.4 Append Action Query 218
Exercises 220
PART 3 Database Analysis and Design
CHAPTER 10 Database System Development Lifecycle 222
10.1 The Information Systems Lifecycle 223
10.2 The Database System Development Lifecycle 223
10.3 Database Planning 224
10.4 System Definition 226
10.4.1 User Views 226
10.5 Requirements Collection and Analysis 226
10.5.1 Centralized Approach 228
10.5.2 View Integration Approach 228
10.6 Database Design 230
10.6.1 Approaches to Database Design 230
10.6.2 Data Modeling 230
10.6.3 Phases of Database Design 231
10.7 DBMS Selection 233
10.7.1 Selecting the DBMS 234
10.8 Application Design 236
10.8.1 Transaction Design 237
10.8.2 User Interface Design Guidelines 238
10.9 Prototyping 239
10.10 Implementation 240
10.11 Data Conversion and Loading 240
10.12 Testing 241
10.13 Operational Maintenance 241
10.14 CASE Tools 242
Chapter Summary 244
Review Questions 245
Exercises 245
CHAPTER 11 Database Analysis and the DreamHome Case Study 247
11.1 When Are Fact-Finding Techniques Used? 248
11.2 What Facts Are Collected? 248
11.3 Fact-Finding Techniques 249
11.3.1 Examining Documentation 249
11.3.2 Interviewing 250
11.3.3 Observing the Enterprise in Operation 250
11.3.4 Research 251
11.3.5 Questionnaires 251
11.4 Using Fact-Finding Techniques: A Worked Example 252
11.4.1 The DreamHome Case Study—An Overview of the Current System252
11.4.2 The DreamHome Case Study—Database Planning 255
11.4.3 The DreamHome Case Study—System Definition 260
11.4.4 The DreamHome Case Study—Requirements Collection andAnalysis 261
11.4.5 The DreamHome Case Study—Database Design 268
Chapter Summary 268
Review Questions 268
Exercises 269
CHAPTER 12 Entity-Relationship Modeling 270
12.1 Entity Types 272
12.2 Relationship Types 272
12.2.1 Degree of Relationship Type 274
12.2.2 Recursive Relationship 275
12.3 Attributes 276
12.3.1 Simple and Composite Attributes 277
12.3.2 Single-valued and Multi-valued Attributes 277
12.3.3 Derived Attributes 277
12.3.4 Keys 278
12.4 Strong and Weak Entity Types 279
12.5 Attributes on Relationships 280
12.6 Structural Constraints 281
12.6.1 One-to-One (1:1) Relationships 281
12.6.2 One-to-Many (1:*) Relationships 282
12.6.3 Many-to-Many (*:*) Relationships 283
12.6.4 Multiplicity for Complex Relationships 284
12.6.5 Cardinality and Participation Constraints 285
12.7 Problems with ER Models 286
12.7.1 Fan Traps 287
12.7.2 Chasm Traps 288
Chapter Summary 289
Review Questions 290
Exercises 291
CHAPTER 13 Enhanced Entity-Relationship Modeling 293
13.1 Specialization/Generalization 294
13.1.1 Superclasses and Subclasses 294
13.1.2 Superclass/Subclass Relationships 294
13.1.3 Attribute Inheritance 295
13.1.4 Specialization Process 296
13.1.5 Generalization Process 296
13.1.6 Constraints on Specialization/Generalization 298
13.1.7 Worked Example of using Specialization/Generalizationto
Model the Branch View of the DreamHome Case Study 299
13.2 Aggregation 303
13.3 Composition 303
Chapter Summary 304
Review Questions 305
Exercises 305
CHAPTER 14 Normalization 306
14.1 The Purpose of Normalization 307
14.2 How Normalization Supports Database Design 307
14.3 Data Redundancy and Update Anomalies 308
14.3.1 Insertion Anomalies 309
14.3.2 Deletion Anomalies 309
14.3.3 Modification Anomalies 310
14.4 Functional Dependencies 310
14.4.1 Characteristics of Functional Dependencies 310
14.4.2 Identifying Functional Dependencies 314
14.4.3 Identifying the Primary Key for a Relation Using FunctionalDependencies 316
14.5 The Process of Normalization 317
14.6 First Normal Form (1NF) 318
14.7 Second Normal Form (2NF) 322
14.8 Third Normal Form (3NF) 323
14.9 General Definitions of 2NF and 3NF 325
Chapter Summary 326
Review Questions 326
Exercises 327
CHAPTER 15 Advanced Normalization 329
15.1 More on Functional Dependencies 330
15.1.1 Inference Rules for Functional Dependencies 330
15.1.2 Minimal Sets of Functional Dependencies 331
15.2 Boyce-Codd Normal Form (BCNF) 332
15.2.1 Definition of BCNF 332
15.3 Review of Normalization Up to BCNF 335
15.4 Fourth Normal Form (4NF) 340
15.4.1 Multi-Valued Dependency 340
15.4.2 Definition of Fourth Normal Form 341
15.5 Fifth Normal Form (5NF) 341
15.5.1 Lossless-Join Dependency 341
15.5.2 Definition of Fifth Normal Form 342
Chapter Summary 343
Review Questions 343
Exercises 344
PART 4 Methodology
CHAPTER 16 Methodology—Conceptual Database Design 346
16.1 Introduction to the Database Design Methodology 347
16.1.1 What Is a Design Methodology? 347
16.1.2 Conceptual, Logical, and Physical Database Design 347
16.1.3 Critical Success Factors in Database Design 348
16.2 Overview of the Database Design Methodology 348
16.3 Conceptual Database Design Methodology 350
Step 1: Build Conceptual Data Model 350
Chapter Summary 363
Review Questions 364
Exercises 364
CHAPTER 17 Methodology袻ogical Database Design for the RelationalModel 366
17.1 Logical Database Design Methodology for the Relational Model366
Step 2: Build Logical Data Model 367
Chapter Summary 390
Review Questions 390
Exercises 391
CHAPTER 18 Methodology裀hysical Database Design for RelationalDatabases 393
18.1 Comparison of Logical and Physical Database Design 394
18.2 Overview of the Physical Database Design Methodology 394
18.3 The Physical Database Design Methodology for RelationalDatabases 395
Step 3: Translate Logical Data Model for Target DBMS 396
Step 4 : Transactions 400
Step 5: Design User Views 410
Step 6: Design Security Mechanisms 411
Chapter Summary 411
Review Questions 412
Exercises 412
CHAPTER 19 Methodology袽onitoring and Tuning the Operational System414
19.1 Denormalizing and Introducing Controlled Redundancy 414
Step 7: Consider the Introduction of Controlled Redundancy414
19.2 Monitoring the System to Improve Performance 424
Step 8: Monitor and Tune the Operational System 424
Chapter Summary 428
Review Questions 428
Exercises 428
PART 5 Selected Database Issues
CHAPTER 20 Security and Administration 430
20.1 Database Security 430
20.1.1 Threats 431
20.2 Countermeasures-Computer-Based Controls 433
20.2.1 Authorization 434
20.2.2 Access Controls 435
20.2.3 Views 437
20.2.4 Backup and Recovery 437
20.2.5 Integrity 438
20.2.6 Encryption 438
20.2.7 RAID (Redundant Array of Independent Disks) 439
20.3 Security in Microsoft Office Access DBMS 441
20.4 Security in Oracle DBMS 443
20.5 DBMSs and Web Security 446
20.5.1 Proxy Servers 447
20.5.2 Firewalls 447
20.5.3 Message Digest Algorithms and Digital Signatures 448
20.5.4 Digital Certificates 448
20.5.5 Kerberos 449
20.5.6 Secure Sockets Layer and Secure HTTP 449
20.5.7 Secure Electronic Transactions and Secure TransactionTechnology 450
20.5.8 Java Security 450
20.5.9 ActiveX Security 453
20.6 Data Administration and Database Administration 453
20.6.1 Data Administration 453
20.6.2 Database Administration 454
20.6.3 Comparison of Data and Database Administration 454
Chapter Summary 455
Review Questions 456
Exercises 456
CHAPTER 21 Professional, Legal, and Ethical Issues in DataManagement 457
21.1 Defining Legal and Ethical Issues in IT 457
21.1.1 Defining Ethics in the Context of IT 458
21.1.2 The Difference Between Ethical and Legal Behavior 458
21.1.3 Ethical Behavior in IT 459
21.2 Legislation and Its Impact on the IT Function 460
21.2.1 Securities and Exchange Commission (SEC) Regulation NationalMarket System (NMS) 460
21.2.2 The Sarbanes-Oxley Act, COBIT, and COSO 460
21.2.3 The Health Insurance Portability and Accountability Act461
21.2.4 The European Union (EU) Directive on Data Protection of 1995462
21.2.5 The United Kingdom誷 Data Protection Act of 1998 463
21.2.6 International Banking袯asel II Accords 463
21.3 Establishing a Culture of Legal and Ethical Data Stewardship464
21.3.1 Developing an Organization-Wide Policy for Legal and EthicalBehavior 464
21.3.2 Professional Organizations and Codes of Ethics 465
21.3.3 Developing an Organization-Wide Policy for Legal and EthicalBehavior for DreamHome 468
21.4 Intellectual Property 469
21.4.1 Patent 469
21.4.2 Copyright 469
21.4.3 Trademark 470
21.4.4 Intellectual Property Rights Issues for Software 470
21.4.5 Intellectual Property Rights Issues for Data 472
Chapter Summary 472
Review Questions 473
Exercises 473
CHAPTER 22 Transaction Management 474
22.1 Transaction Support 475
22.1.1 Properties of Transactions 477
22.1.2 Database Architecture 477
22.2 Concurrency Control 478
22.2.1 The Need for Concurrency Control 478
22.2.2 Serializability and Recoverability 480
22.2.3 Locking Methods 487
22.2.4 Deadlock 492
22.2.5 Timestamping Methods 495
22.2.6 Multiversion Timestamp Ordering 498
22.2.7 Optimistic Techniques 499
22.2.8 Granularity of Data Items 500
22.3 Database Recovery 502
22.3.1 The Need for Recovery 503
22.3.2 Transactions and Recovery 503
22.3.3 Recovery Facilities 506
22.3.4 Recovery Techniques 508
22.3.5 Recovery in a Distributed DBMS 510
22.4 Advanced Transaction Models 510
22.4.1 Nested Transaction Model 512
22.4.2 Sagas 513
22.4.3 Multilevel Transaction Model 514
22.4.4 Dynamic Restructuring 515
22.4.5 Workflow Models 516
22.5 Concurrency Control and Recovery in Oracle 517
22.5.1 Oracle誷 Isolation Levels 517
22.5.2 Multiversion Read Consistency 517
22.5.3 Deadlock Detection 519
22.5.4 Backup and Recovery 519
Chapter Summary 521
Review Questions 522
Exercises 523
CHAP TER 23 Query Processing 525
23.1 Overview of Query Processing 526
23.2 Query Decomposition 529
23.3 Heuristical Approach to Query Optimization 532
23.3.1 Transformation Rules for the Relational Algebra Operations532
23.3.2 Heuristical Processing Strategies 535
23.4 Cost Estimation for the Relational Algebra Operations537
23.4.1 Database Statistics 537
23.4.2 Selection Operation (S = sp(R)) 538
23.4.3 Join Operation (T = (R S)) 544
23.4.4 Projection Operation (S = ∏A1, A2,. . . , Am(R)) 549
23.4.5 The Relational Algebra Set Operations (T = R S, T= R S, T =R - S) 551
23.5 Enumeration of Alternative Execution Strategies 552
23.5.1 Pipelining 552
23.5.2 Linear Trees 553
23.5.3 Physical Operators and Execution Strategies 554
23.5.4 Reducing the Search Space 555
23.5.5 Enumerating Left-Deep Trees 555
23.5.6 Semantic Query Optimization 557
23.5.7 Alternative Approaches to Query Optimization 557
23.5.8 Distributed Query Optimization 558
23.6 Query Optimization in Oracle 558
23.6.1 Rule-Based and Cost-Based Optimization 559
23.6.2 Histograms 561
23.6.3 Viewing the Execution Plan 563
Chapter Summary 564
Review Questions 565
Exercises 566
PART 6 Distributed DBMSs and Replication
CHAPTER 24 Distributed DBMSs—Concepts and Design 570
24.1 Introduction 571
24.1.1 Concepts 571
24.1.2 Advantages and Disadvantages of DDBMSs 575
24.1.3 Homogeneous and Heterogeneous DDBMSs 577
24.2 Overview of Networking 580
24.3 Functions and Architectures of a DDBMS 583
24.3.1 Functions of a DDBMS 583
24.3.2 Reference Architecture for a DDBMS 583
24.3.3 Reference Architecture for a Federated MDBS 584
24.3.4 Component Architecture for a DDBMS 585
24.4 Distributed Relational Database Design 586
24.4.1 Data Allocation 587
24.4.2 Fragmentation 588
24.5 Transparencies in a DDBMS 595
24.5.1 Distribution Transparency 595
24.5.2 Transaction Transparency 597
24.5.3 Performance Transparency 600
24.5.4 DBMS Transparency 602
24.5.5 Summary of Transparencies in a DDBMS 602
24.6 Date誷 Twelve Rules for a DDBMS 603
Chapter Summary 604
Review Questions 605
Exercises 606
CHAPTER 25 Distributed DBMSs—Advanced Concepts 608
25.1 Distributed Transaction Management 609
25.2 Distributed Concurrency Control 610
25.2.1 Objectives 610
25.2.2 Distributed Serializability 610
25.2.3 Locking Protocols 611
25.2.4 Timestamp Protocols 613
25.3 Distributed Deadlock Management 613
25.4 Distributed Database Recovery 616
25.4.1 Failures in a Distributed Environment 616
25.4.2 How Failures Affect Recovery 617
25.4.3 Two-Phase Commit (2PC) 618
25.4.4 Three-Phase Commit (3PC) 623
25.4.5 Network Partitioning 626
25.5 The X/Open Distributed Transaction Processing Model 628
25.6 Distributed Query Optimization 630
25.6.1 Data Localization 631
25.6.2 Distributed Joins 634
25.6.3 Global Optimization 635
25.7 Distribution in Oracle 639
25.7.1 Oracle誷 DDBMS Functionality 639
Chapter Summary 643
Review Questions 644
Exercises 644
CHAPTER 26 Replication and Mobile Databases 646
26.1 Introduction to Data Replication 646
26.1.1 Synchronous Versus Asynchronous Replication 648
26.1.2 Applications of Replication 648
26.2 Replication Servers 649
26.2.1 Replication Server Functionality 649
26.2.2 Data Ownership 649
26.2.3 Implementation Issues 652
26.3 Introduction to Mobile Databases 655
26.3.1 Mobile DBMSs 656
26.3.2 Issues with Mobile DBMSs 657
26.4 Oracle Replication 661
26.4.1 Oracle誷 Replication Functionality 662
Chapter Summary 666
Review Questions 667
Exercises 667
PART 7 Object DBMSs
CHAPTER 27 Object-Oriented DBMSs—Concepts and Design 670
27.1 Advanced Database Applications 671
27.2 Weaknesses of RDBMSs 675
27.3 Storing Objects in a Relational Database 680
27.3.1 Mapping Classes to Relations 681
27.3.2 Accessing Objects in the Relational Database 682
27.4 Next-Generation Database Systems 683
27.5 Introduction to OODBMSs 684
27.5.1 Definition of Object-Oriented DBMSs 684
27.5.2 Functional Data Models 686
27.5.3 Persistent Programming Languages 690
27.5.4 The Object-Oriented Database System Manifesto 691
27.5.5 Alternative Strategies for Developing an OODBMS 693
27.6 Persistence in OODBMSs 693
27.6.1 Pointer Swizzling Techniques 695
27.6.2 Accessing an Object 698
27.6.3 Persistence Schemes 699
27.6.4 Orthogonal Persistence 701
27.7 Issues in OODBMSs 702
27.7.1 Transactions 703
27.7.2 Versions 703
27.7.3 Schema Evolution 704
27.7.4 Architecture 707
27.7.5 Benchmarking 708
27.8 Advantages and Disadvantages of OODBMSs 711
27.8.1 Advantages 711
27.8.2 Disadvantages 712
27.9 Object-Oriented Database Design 714
27.9.1 Comparison of Object-Oriented Data Modeling and ConceptualData Modeling 714
27.9.2 Relationships and Referential Integrity 715
27.9.3 Behavioral Design 717
27.10 Object-Oriented Analysis and Design with UML 718
27.10.1 UML Diagrams 719
27.10.2 Usage of UML in the Methodology for Database Design723
Chapter Summary 724
Review Questions 726
Exercises 726
CHAPTER 28 Object-Oriented DBMSs—Standards and Systems 728
28.1 Object Management Group 729
28.1.1 Background 729
28.1.2 The Common Object Request Broker Architecture 731
28.1.3 Other OMG Specifications 736
28.1.4 Model-Driven Architecture 738
28.2 Object Data Standard ODMG 3.0, 1999 738
28.2.1 Object Data Management Group 738
28.2.2 The Object Model 740
28.2.3 The Object Definition Language 746
28.2.4 The Object Query Language 748
28.2.5 Other Parts of the ODMG Standard 754
28.2.6 Mapping the Conceptual Design to a Logical (Object-Oriented)Design 756
28.3 ObjectStore 757
28.3.1 Architecture 757
28.3.2 Building an ObjectStore Application 759
28.3.3 Data Definition in ObjectStore 761
28.3.4 Data Manipulation in ObjectStore 763
Chapter Summary 766
Review Questions 767
Exercises 767
CHAPTER 29 Object-Relational DBMSs 768
29.1 Introduction to Object-Relational Database Systems 769
29.2 The Third-Generation Database Manifestos 771
29.2.1 The Third-Generation Database System Manifesto 772
29.2.2 The Third Manifesto 772
29.3 Postgres—An Early ORDBMS 774
29.3.1 Objectives of Postgres 774
29.3.2 Abstract Data Types 775
29.3.3 Relations and Inheritance 775
29.3.4 Object Identity 776
29.4 SQL:2008 777
29.4.1 Row Types 778
29.4.2 User-Defined Types 778
29.4.3 Subtypes and Supertypes 781
29.4.4 User-Defined Routines 783
29.4.5 Polymorphism 784
29.4.6 Reference Types and Object Identity 785
29.4.7 Creating Tables 786
29.4.8 Querying Data 788
29.4.9 Collection Types 789
29.4.10 Typed Views 792
29.4.11 Persistent Stored Modules 793
29.4.12 Triggers 793
29.4.13 Large Objects 796
29.4.14 Recursion 797
29.5 Query Processing and Optimization 797
29.5.1 New Index Types 800
29.6 Object-Oriented Extensions in Oracle 801
29.6.1 User-Defined Data Types 801
29.6.2 Manipulating Object Tables 806
29.6.3 Object Views 807
29.6.4 Privileges 808
29.7 Comparison of ORDBMS and OODBMS 808
Chapter Summary 809
Review Questions 810
Exercises 810
PART 8 The Web and DBMSs
CHAPTER 30 Web Technology and DBMSs 812
30.1 Introduction to the Internet and the Web 813
30.1.1 Intranets and Extranets 814
30.1.2 e-Commerce and e-Business 815
30.2 The Web 816
30.2.1 HyperText Transfer Protocol 817
30.2.2 HyperText Markup Language 818
30.2.3 Uniform Resource Locators 819
30.2.4 Static and Dynamic Web Pages 820
30.2.5 Web Services 821
30.2.6 Requirements for Web-DBMS Integration 822
30.2.7 Advantages and Disadvantages of the Web-DBMS Approach822
30.2.8 Approaches to Integrating the Web and DBMSs 826
30.3 Scripting Languages 826
30.3.1 JavaScript and JScript 827
30.3.2 VBScript 828
30.3.3 Perl and PHP 828
30.4 Common Gateway Interface (CGI) 829
30.4.1 Passing Information to a CGI Script 830
30.4.2 Advantages and Disadvantages of CGI 831
30.5 HTTP Cookies 832
30.6 Extending the Web Server 833
30.6.1 Comparison of CGI and API 834
30.7 Java 835
30.7.1 JDBC 838
30.7.2 SQLJ 842
30.7.3 Comparison of JDBC and SQLJ 842
30.7.4 Container-Managed Persistence (CMP) 843
30.7.5 Java Data Objects (JDO) 846
30.7.6 JPA (Java Persistence API) 852
30.7.7 Java Servlets 859
30.7.8 JavaServer Pages 860
30.7.9 Java Web Services 860
30.8 Microsoft誷 Web Platform 862
30.8.1 Universal Data Access 863
30.8.2 Active Server Pages and ActiveX Data Objects 863
30.8.3 Remote Data Services 866
30.8.4 Comparison of ASP and JSP 867
30.8.5 Microsoft .NET 867
30.8.6 Microsoft Web Services 871
30.9 Oracle Internet Platform 872
30.9.1 Oracle Application Server (OracleAS) 872
Chapter Summary 877
Review Questions 878
Exercises 879
CHAPTER 31 Semistructured Data and XML 880
31.1 Semistructured Data 881
31.1.1 Object Exchange Model (OEM) 882
31.1.2 Lore and Lorel 883
31.2 Introduction to XML 886
31.2.1 Overview of XML 889
31.2.2 Document Type Definitions (DTDs) 891
31.3 XML-Related Technologies 893
31.3.1 DOM and SAX Interfaces 894
31.3.2 Namespaces 894
31.3.3 XSL and XSLT 895
31.3.4 XPath (XML Path Language) 896
31.3.5 XPointer (XML Pointer Language) 897
31.3.6 XLink (XML Linking Language) 897
31.3.7 XHTML 897
31.3.8 Simple Object Access Protocol (SOAP) 898
31.3.9 Web Services Description Language (WSDL) 898
31.3.10 Universal Discovery, Description, and Integration (UDDI)899
31.4 XML Schema 901
31.4.1 Resource Description Framework (RDF) 907
Notation3 (N3) and Turtle 908
31.5 XML Query Languages 910
31.5.1 Extending Lore and Lorel to Handle XML 911
31.5.2 XML Query Working Group 912
31.5.3 XQuery-A Query Language for XML 913
31.5.4 XML Information Set 921
31.5.5 XQuery 1.0 and XPath 2.0 Data Model (XDM) 922
31.5.6 XQuery Update Facility 1.0 927
31.5.7 Formal Semantics 929
31.6 XML and Databases 935
31.6.1 Storing XML in Databases 936
31.6.2 XML and SQL 937
31.6.3 Native XML Databases 947
31.7 XML in Oracle 949
Chapter Summary 951
Review Questions 953
Exercises 954
PART 9 Business Intelligence
CHAPTER 32 Data Warehousing Concepts 956
32.1 Introduction to Data Warehousing 956
32.1.1 The Evolution of Data Warehousing 957
32.1.2 Data Warehousing Concepts 957
32.1.3 Benefits of Data Warehousing 958
32.1.4 Comparison of OLTP Systems and Data Warehousing 959
32.1.5 Problems of Data Warehousing 960
32.1.6 Real-Time Data Warehouse 962
32.2 Data Warehouse Architecture 962
32.2.1 Operational Data 962
32.2.2 Operational Data Store 963
32.2.3 ETL Manager 963
32.2.4 Warehouse Manager 963
32.2.5 Query Manager 964
32.2.6 Detailed Data 964
32.2.7 Lightly and Highly Summarized Data 964
32.2.8 Archive/Backup Data 964
32.2.9 Metadata 964
32.2.10 End-User Access Tools 965
32.3 Data Warehousing Tools and Technologies 966
32.3.1 Extraction, Transformation, and Loading (ETL) 966
32.3.2 Data Warehouse DBMS 968
32.3.3 Data Warehouse Metadata 970
32.3.4 Administration and Management Tools 971
32.4 Data Mart 971
32.4.1 Reasons for Creating a Data Mart 972
32.5 Data Warehousing Using Oracle 972
32.5.1 New Warehouse Features in Oracle 10g/11g 975
Chapter Summary 976
Review Questions 977
Exercise 977
CHAPTER 33 Data Warehousing Design 978
33.1 Designing a Data Warehouse Database 978
33.2 Data Warehouse Development Methodologies 979
33.3 Kimball誷 Business Dimensional Lifecycle 980
33.4 Dimensional Modeling 981
33.4.1 Comparison of DM and ER models 984
33.5 The Dimensional Modeling Stage of Kimball誷 BusinessDimensional Lifecycle 984
33.5.1 Create a High-Level Dimensional Model (Phase I) 985
Step 1: Select Business Process 985
Step 2: Declare Grain 985
Step 3: Choose Dimensions 986
Step 4: Identify Facts 987
33.5.2 Identify All Dimension Attributes for the Dimensional Model(Phase II) 989
33.6 Data Warehouse Development Issues 990
33.7 Data Warehousing Design Using Oracle 991
33.7.1 Oracle Warehouse Builder Components 992
33.7.2 Using Oracle Warehouse Builder 992
33.7.3 New Warehouse Builder Features in Oracle 10g/11g 996
Chapter Summary 997
Review Questions 998
Exercises 998
CHAPTER 34 OLAP 999
34.1 Online Analytical Processing 999
34.1.1 OLAP Benchmarks 1000
34.2 OLAP Applications 1001
34.3 Multidimensional Data Model 1002
34.3.1 Alternative Multidimensional Data Representations 1002
34.3.2 Dimensional Hierarchy 1004
34.3.3 Multidimensional Operations 1005
34.3.4 Multidimensional Schemas 1005
34.4 OLAP Tools 1006
34.4.1 Codd誷 Rules for OLAP Tools 1006
34.4.2 OLAP Server-Implementation Issues 1007
34.4.3 Categories of OLAP Servers 1008
34.5 OLAP Extensions to the SQL Standard 1011
34.5.1 Extended Grouping Capabilities 1011
34.5.2 Elementary OLAP Operators 1015
34.6 Oracle OLAP 1017
34.6.1 Oracle OLAP Environment 1017
34.6.2 Platform for Business Intelligence Applications 1017
34.6.3 Oracle Database 1018
34.6.4 Oracle OLAP 1019
34.6.5 Performance 1020
34.6.6 System Management 1021
34.6.7 System Requirements 1021
34.6.8 New OLAP Features in Oracle 11g 1021
Chapter Summary 1021
Review Questions 1022
Exercises 1022
CHAPTER 35 Data Mining 1023
35.1 Data Mining 1023
35.2 Data Mining Techniques 1024
35.2.1 Predictive Modeling 1025
35.2.2 Database Segmentation 1026
35.2.3 Link Analysis 1027
35.2.4 Deviation Detection 1028
35.3 The Data Mining Process 1028
35.3.1 The CRISP-DM Model 1028
35.4 Data Mining Tools 1030
35.5 Data Mining and Data Warehousing 1031
35.6 Oracle Data Mining (ODM) 1031
35.6.1 Data Mining Capabilities 1031
35.6.2 Enabling Data Mining Applications 1031
35.6.3 Predictions and Insights 1032
35.6.4 Oracle Data Mining Environment 1032
35.6.5 New Data Mining Features in Oracle 11g 1033
Chapter Summary 1033
Review Questions 1034
Exercises 1034
Appendices
APPENDIX A Users誖equirements Specification for DreamHome Case Study1036
APPENDIX B Other Case Studies 1040
APPENDIX C Alternative ER Modeling Notations 1049
APPENDIX D Summary of the Database Design Methodology forRelational Databases 1053
APPENDIX E Introduction to Pyrrho: A Lightweight RDBMS 1057
References 1069
Further Reading 1081
APPENDIX F File Organizations and Indexes (Online)
APPENDIX G When Is a DBMS Relational? (Online)
APPENDIX H Commercial DBMSs: Access and Oracle (Online)
APPENDIX I Programmatic SQL (Online)
APPENDIX J Estimating Disk Space Requirements (Online)
APPENDIX K Introduction to Object-Orientation (Online)
APPENDIX L Example Web Scripts (Online)
PART 1 Background
CHAPTER 1 Introduction to Databases 2
1.1 Introduction 3
1.2 Traditional File-Based Systems 5
1.2.1 File-Based Approach 5
1.2.2 Limitations of the File-Based Approach 10
1.3 Database Approach 12
1.3.1 The Database 12
1.3.2 The Database Management System (DBMS) 13
1.3.3 (Database) Application Programs 14
1.3.4 Components of the DBMS Environment 16
1.3.5 Database Design: The Paradigm Shift 18
1.4 Roles in the Database Environment 18
1.4.1 Data and Database Administrators 19
1.4.2 Database Designers 19
1.4.3 Application Developers 20
1.4.4 End-Users 20
1.5 History of Database Management Systems 20
1.6 Advantages and Disadvantages of DBMSs 23
Chapter Summary 27
Review Questions 28
Exercises 28
CHAPTER 2 Database Environment 30
2.1 The Three-Level ANSI-SPARC Architecture 31
2.1.1 External Level 32
2.1.2 Conceptual Level 32
2.1.3 Internal Level 32
2.1.4 Schemas, Mappings, and Instances 33
2.1.5 Data Independence 34
2.2 Database Languages 35
2.2.1 The Data Definition Language (DDL) 35
2.2.2 The Data Manipulation Language (DML) 36
2.2.3 Fourth-Generation Languages (4GLs) 37
2.3 Data Models and Conceptual Modeling 38
2.3.1 Object-Based Data Models 39
2.3.2 Record-Based Data Models 39
2.3.3 Physical Data Models 42
2.3.4 Conceptual Modeling 42
2.4 Functions of a DBMS 42
Chapter Summary 46
Review Questions 47
Exercises 47
CHAPTER 3 Database Architectures and the Web 49
3.1 Multi-user DBMS Architectures 49
3.1.1 Teleprocessing 50
3.1.2 File-Server Architecture 50
3.1.3 Traditional Two-Tier Client-Server Architecture 51
3.1.4 Three-Tier Client-Server Architecture 53
3.1.5 N-Tier Architectures 55
3.1.6 Middleware 56
3.1.7 Transaction Processing Monitors 58
3.2 Web Services and Service-Oriented Architectures 59
3.2.1 Web Services 59
3.2.2 Service-Oriented Architectures (SOA) 60
3.3 Distributed DBMSs 62
3.4 Data Warehousing 64
3.5 Components of a DBMS 66
3.6 Oracle Architecture 69
3.6.1 Oracle誷 Logical Database Structure 69
3.6.2 Oracle誷 Physical Database Structure 71
Chapter Summary 74
Review Questions 75
Exercises 75
PART 2 The Relational Model and Languages
CHAPTER 4 The Relational Model 78
4.1 Brief History of the Relational Model 79
4.2 Terminology 80
4.2.1 Relational Data Structure 80
4.2.2 Mathematical Relations 82
4.2.3 Database Relations 83
4.2.4 Properties of Relations 84
4.2.5 Relational Keys 85
4.2.6 Representing Relational Database Schemas 86
4.3 Integrity Constraints 88
4.3.1 Nulls 88
4.3.2 Entity Integrity 88
4.3.3 Referential Integrity 89
4.3.4 General Constraints 89
4.4 Views 90
4.4.1 Terminology 90
4.4.2 Purpose of Views 90
4.4.3 Updating Views 91
Chapter Summary 91
Review Questions 92
Exercises 92
CHAPTER 5 Relational Algebra and Relational Calculus 94
5.1 The Relational Algebra 95
5.1.1 Unary Operations 96
5.1.2 Set Operations 97
5.1.3 Join Operations 100
5.1.4 Division Operation 103
5.1.5 Aggregation and Grouping Operations 103
5.1.6 Summary of the Relational Algebra Operations 105
5.2 The Relational Calculus 106
5.2.1 Tuple Relational Calculus 106
5.2.2 Domain Relational Calculus 109
5.3 Other Languages 111
Chapter Summary 111
Review Questions 112
Exercises 112
CHAPTER 6 SQL: Data Manipulation 115
6.1 Introduction to SQL 116
6.1.1 Objectives of SQL 116
6.1.2 History of SQL 117
6.1.3 Importance of SQL 118
6.1.4 Terminology 119
6.2 Writing SQL Commands 119
6.3 Data Manipulation 120
6.3.1 Simple Queries 120
6.3.2 Sorting Results (ORDER BY Clause) 127
6.3.3 Using the SQL Aggregate Functions 128
6.3.4 Grouping Results (GROUP BY Clause) 130
6.3.5 Subqueries 133
6.3.6 ANY and ALL 135
6.3.7 Multi-table Queries 136
6.3.8 EXISTS and NOT EXISTS 141
6.3.9 Combining Result Tables (UNION, INTERSECT, EXCEPT) 142
6.3.10 Database Updates 144
Chapter Summary 148
Review Questions 149
Exercises 149
CHAPTER 7 SQL: Data Definition 151
7.1 The ISO SQL Data Types 152
7.1.1 SQL Identifiers 152
7.1.2 SQL Scalar Data Types 152
7.1.3 Exact Numeric Data 153
7.2 Integrity Enhancement Feature 156
7.2.1 Required Data 156
7.2.2 Domain Constraints 156
7.2.3 Entity Integrity 157
7.2.4 Referential Integrity 158
7.2.5 General Constraints 159
7.3 Data Definition 159
7.3.1 Creating a Database 160
7.3.2 Creating a Table (CREATE TABLE) 160
7.3.3 Changing a Table Definition (ALTER TABLE) 163
7.3.4 Removing a Table (DROP TABLE) 164
7.3.5 Creating an Index (CREATE INDEX) 165
7.3.6 Removing an Index (DROP INDEX) 166
7.4 Views 166
7.4.1 Creating a View (CREATE VIEW) 166
7.4.2 Removing a View (DROP VIEW) 168
7.4.3 View Resolution 169
7.4.4 Restrictions on Views 170
7.4.5 View Updatability 170
7.4.6 WITH CHECK OPTION 171
7.4.7 Advantages and Disadvantages of Views 172
7.4.8 View Materialization 174
7.5 Transactions 175
7.5.1 Immediate and Deferred Integrity Constraints 176
7.6 Discretionary Access Control 176
7.6.1 Granting Privileges to Other Users (GRANT) 177
7.6.2 Revoking Privileges from Users (REVOKE) 179
Chapter Summary 180
Review Questions 181
Exercises 181
CHAPTER 8 Advanced SQL 184
8.1 The SQL Programming Language 184
8.1.1 Declarations 185
8.1.2 Assignments 186
8.1.3 Control Statements 186
8.1.4 Exceptions in PL/SQL 188
8.1.5 Cursors in PL/SQL 189
8.2 Subprograms, Stored Procedures, Functions, and Packages192
8.3 Triggers 193
8.4 Recursion 198
Chapter Summary 199
Review Questions 200
Exercises 200
CHAPTER 9 Query-By-Example 202
9.1 Introduction to Microsoft Office Access Queries 203
9.2 Building Select Queries Using QBE 204
9.2.1 Specifying Criteria 205
9.2.2 Creating Multi-table Queries 207
9.2.3 Calculating Totals 208
9.3 Using Advanced Queries 210
9.3.1 Parameter Query 210
9.3.2 Crosstab Query 211
9.3.3 Find Duplicates Query 213
9.3.4 Find Unmatched Query 213
9.3.5 Autolookup Query 214
9.4 Changing the Content of Tables Using Action Queries 215
9.4.1 Make-Table Action Query 215
9.4.2 Delete Action Query 217
9.4.3 Update Action Query 217
9.4.4 Append Action Query 218
Exercises 220
PART 3 Database Analysis and Design
CHAPTER 10 Database System Development Lifecycle 222
10.1 The Information Systems Lifecycle 223
10.2 The Database System Development Lifecycle 223
10.3 Database Planning 224
10.4 System Definition 226
10.4.1 User Views 226
10.5 Requirements Collection and Analysis 226
10.5.1 Centralized Approach 228
10.5.2 View Integration Approach 228
10.6 Database Design 230
10.6.1 Approaches to Database Design 230
10.6.2 Data Modeling 230
10.6.3 Phases of Database Design 231
10.7 DBMS Selection 233
10.7.1 Selecting the DBMS 234
10.8 Application Design 236
10.8.1 Transaction Design 237
10.8.2 User Interface Design Guidelines 238
10.9 Prototyping 239
10.10 Implementation 240
10.11 Data Conversion and Loading 240
10.12 Testing 241
10.13 Operational Maintenance 241
10.14 CASE Tools 242
Chapter Summary 244
Review Questions 245
Exercises 245
CHAPTER 11 Database Analysis and the DreamHome Case Study 247
11.1 When Are Fact-Finding Techniques Used? 248
11.2 What Facts Are Collected? 248
11.3 Fact-Finding Techniques 249
11.3.1 Examining Documentation 249
11.3.2 Interviewing 250
11.3.3 Observing the Enterprise in Operation 250
11.3.4 Research 251
11.3.5 Questionnaires 251
11.4 Using Fact-Finding Techniques: A Worked Example 252
11.4.1 The DreamHome Case Study—An Overview of the Current System252
11.4.2 The DreamHome Case Study—Database Planning 255
11.4.3 The DreamHome Case Study—System Definition 260
11.4.4 The DreamHome Case Study—Requirements Collection andAnalysis 261
11.4.5 The DreamHome Case Study—Database Design 268
Chapter Summary 268
Review Questions 268
Exercises 269
CHAPTER 12 Entity-Relationship Modeling 270
12.1 Entity Types 272
12.2 Relationship Types 272
12.2.1 Degree of Relationship Type 274
12.2.2 Recursive Relationship 275
12.3 Attributes 276
12.3.1 Simple and Composite Attributes 277
12.3.2 Single-valued and Multi-valued Attributes 277
12.3.3 Derived Attributes 277
12.3.4 Keys 278
12.4 Strong and Weak Entity Types 279
12.5 Attributes on Relationships 280
12.6 Structural Constraints 281
12.6.1 One-to-One (1:1) Relationships 281
12.6.2 One-to-Many (1:*) Relationships 282
12.6.3 Many-to-Many (*:*) Relationships 283
12.6.4 Multiplicity for Complex Relationships 284
12.6.5 Cardinality and Participation Constraints 285
12.7 Problems with ER Models 286
12.7.1 Fan Traps 287
12.7.2 Chasm Traps 288
Chapter Summary 289
Review Questions 290
Exercises 291
CHAPTER 13 Enhanced Entity-Relationship Modeling 293
13.1 Specialization/Generalization 294
13.1.1 Superclasses and Subclasses 294
13.1.2 Superclass/Subclass Relationships 294
13.1.3 Attribute Inheritance 295
13.1.4 Specialization Process 296
13.1.5 Generalization Process 296
13.1.6 Constraints on Specialization/Generalization 298
13.1.7 Worked Example of using Specialization/Generalizationto
Model the Branch View of the DreamHome Case Study 299
13.2 Aggregation 303
13.3 Composition 303
Chapter Summary 304
Review Questions 305
Exercises 305
CHAPTER 14 Normalization 306
14.1 The Purpose of Normalization 307
14.2 How Normalization Supports Database Design 307
14.3 Data Redundancy and Update Anomalies 308
14.3.1 Insertion Anomalies 309
14.3.2 Deletion Anomalies 309
14.3.3 Modification Anomalies 310
14.4 Functional Dependencies 310
14.4.1 Characteristics of Functional Dependencies 310
14.4.2 Identifying Functional Dependencies 314
14.4.3 Identifying the Primary Key for a Relation Using FunctionalDependencies 316
14.5 The Process of Normalization 317
14.6 First Normal Form (1NF) 318
14.7 Second Normal Form (2NF) 322
14.8 Third Normal Form (3NF) 323
14.9 General Definitions of 2NF and 3NF 325
Chapter Summary 326
Review Questions 326
Exercises 327
CHAPTER 15 Advanced Normalization 329
15.1 More on Functional Dependencies 330
15.1.1 Inference Rules for Functional Dependencies 330
15.1.2 Minimal Sets of Functional Dependencies 331
15.2 Boyce-Codd Normal Form (BCNF) 332
15.2.1 Definition of BCNF 332
15.3 Review of Normalization Up to BCNF 335
15.4 Fourth Normal Form (4NF) 340
15.4.1 Multi-Valued Dependency 340
15.4.2 Definition of Fourth Normal Form 341
15.5 Fifth Normal Form (5NF) 341
15.5.1 Lossless-Join Dependency 341
15.5.2 Definition of Fifth Normal Form 342
Chapter Summary 343
Review Questions 343
Exercises 344
PART 4 Methodology
CHAPTER 16 Methodology—Conceptual Database Design 346
16.1 Introduction to the Database Design Methodology 347
16.1.1 What Is a Design Methodology? 347
16.1.2 Conceptual, Logical, and Physical Database Design 347
16.1.3 Critical Success Factors in Database Design 348
16.2 Overview of the Database Design Methodology 348
16.3 Conceptual Database Design Methodology 350
Step 1: Build Conceptual Data Model 350
Chapter Summary 363
Review Questions 364
Exercises 364
CHAPTER 17 Methodology袻ogical Database Design for the RelationalModel 366
17.1 Logical Database Design Methodology for the Relational Model366
Step 2: Build Logical Data Model 367
Chapter Summary 390
Review Questions 390
Exercises 391
CHAPTER 18 Methodology裀hysical Database Design for RelationalDatabases 393
18.1 Comparison of Logical and Physical Database Design 394
18.2 Overview of the Physical Database Design Methodology 394
18.3 The Physical Database Design Methodology for RelationalDatabases 395
Step 3: Translate Logical Data Model for Target DBMS 396
Step 4 : Transactions 400
Step 5: Design User Views 410
Step 6: Design Security Mechanisms 411
Chapter Summary 411
Review Questions 412
Exercises 412
CHAPTER 19 Methodology袽onitoring and Tuning the Operational System414
19.1 Denormalizing and Introducing Controlled Redundancy 414
Step 7: Consider the Introduction of Controlled Redundancy414
19.2 Monitoring the System to Improve Performance 424
Step 8: Monitor and Tune the Operational System 424
Chapter Summary 428
Review Questions 428
Exercises 428
PART 5 Selected Database Issues
CHAPTER 20 Security and Administration 430
20.1 Database Security 430
20.1.1 Threats 431
20.2 Countermeasures-Computer-Based Controls 433
20.2.1 Authorization 434
20.2.2 Access Controls 435
20.2.3 Views 437
20.2.4 Backup and Recovery 437
20.2.5 Integrity 438
20.2.6 Encryption 438
20.2.7 RAID (Redundant Array of Independent Disks) 439
20.3 Security in Microsoft Office Access DBMS 441
20.4 Security in Oracle DBMS 443
20.5 DBMSs and Web Security 446
20.5.1 Proxy Servers 447
20.5.2 Firewalls 447
20.5.3 Message Digest Algorithms and Digital Signatures 448
20.5.4 Digital Certificates 448
20.5.5 Kerberos 449
20.5.6 Secure Sockets Layer and Secure HTTP 449
20.5.7 Secure Electronic Transactions and Secure TransactionTechnology 450
20.5.8 Java Security 450
20.5.9 ActiveX Security 453
20.6 Data Administration and Database Administration 453
20.6.1 Data Administration 453
20.6.2 Database Administration 454
20.6.3 Comparison of Data and Database Administration 454
Chapter Summary 455
Review Questions 456
Exercises 456
CHAPTER 21 Professional, Legal, and Ethical Issues in DataManagement 457
21.1 Defining Legal and Ethical Issues in IT 457
21.1.1 Defining Ethics in the Context of IT 458
21.1.2 The Difference Between Ethical and Legal Behavior 458
21.1.3 Ethical Behavior in IT 459
21.2 Legislation and Its Impact on the IT Function 460
21.2.1 Securities and Exchange Commission (SEC) Regulation NationalMarket System (NMS) 460
21.2.2 The Sarbanes-Oxley Act, COBIT, and COSO 460
21.2.3 The Health Insurance Portability and Accountability Act461
21.2.4 The European Union (EU) Directive on Data Protection of 1995462
21.2.5 The United Kingdom誷 Data Protection Act of 1998 463
21.2.6 International Banking袯asel II Accords 463
21.3 Establishing a Culture of Legal and Ethical Data Stewardship464
21.3.1 Developing an Organization-Wide Policy for Legal and EthicalBehavior 464
21.3.2 Professional Organizations and Codes of Ethics 465
21.3.3 Developing an Organization-Wide Policy for Legal and EthicalBehavior for DreamHome 468
21.4 Intellectual Property 469
21.4.1 Patent 469
21.4.2 Copyright 469
21.4.3 Trademark 470
21.4.4 Intellectual Property Rights Issues for Software 470
21.4.5 Intellectual Property Rights Issues for Data 472
Chapter Summary 472
Review Questions 473
Exercises 473
CHAPTER 22 Transaction Management 474
22.1 Transaction Support 475
22.1.1 Properties of Transactions 477
22.1.2 Database Architecture 477
22.2 Concurrency Control 478
22.2.1 The Need for Concurrency Control 478
22.2.2 Serializability and Recoverability 480
22.2.3 Locking Methods 487
22.2.4 Deadlock 492
22.2.5 Timestamping Methods 495
22.2.6 Multiversion Timestamp Ordering 498
22.2.7 Optimistic Techniques 499
22.2.8 Granularity of Data Items 500
22.3 Database Recovery 502
22.3.1 The Need for Recovery 503
22.3.2 Transactions and Recovery 503
22.3.3 Recovery Facilities 506
22.3.4 Recovery Techniques 508
22.3.5 Recovery in a Distributed DBMS 510
22.4 Advanced Transaction Models 510
22.4.1 Nested Transaction Model 512
22.4.2 Sagas 513
22.4.3 Multilevel Transaction Model 514
22.4.4 Dynamic Restructuring 515
22.4.5 Workflow Models 516
22.5 Concurrency Control and Recovery in Oracle 517
22.5.1 Oracle誷 Isolation Levels 517
22.5.2 Multiversion Read Consistency 517
22.5.3 Deadlock Detection 519
22.5.4 Backup and Recovery 519
Chapter Summary 521
Review Questions 522
Exercises 523
CHAP TER 23 Query Processing 525
23.1 Overview of Query Processing 526
23.2 Query Decomposition 529
23.3 Heuristical Approach to Query Optimization 532
23.3.1 Transformation Rules for the Relational Algebra Operations532
23.3.2 Heuristical Processing Strategies 535
23.4 Cost Estimation for the Relational Algebra Operations537
23.4.1 Database Statistics 537
23.4.2 Selection Operation (S = sp(R)) 538
23.4.3 Join Operation (T = (R S)) 544
23.4.4 Projection Operation (S = ∏A1, A2,. . . , Am(R)) 549
23.4.5 The Relational Algebra Set Operations (T = R S, T= R S, T =R - S) 551
23.5 Enumeration of Alternative Execution Strategies 552
23.5.1 Pipelining 552
23.5.2 Linear Trees 553
23.5.3 Physical Operators and Execution Strategies 554
23.5.4 Reducing the Search Space 555
23.5.5 Enumerating Left-Deep Trees 555
23.5.6 Semantic Query Optimization 557
23.5.7 Alternative Approaches to Query Optimization 557
23.5.8 Distributed Query Optimization 558
23.6 Query Optimization in Oracle 558
23.6.1 Rule-Based and Cost-Based Optimization 559
23.6.2 Histograms 561
23.6.3 Viewing the Execution Plan 563
Chapter Summary 564
Review Questions 565
Exercises 566
PART 6 Distributed DBMSs and Replication
CHAPTER 24 Distributed DBMSs—Concepts and Design 570
24.1 Introduction 571
24.1.1 Concepts 571
24.1.2 Advantages and Disadvantages of DDBMSs 575
24.1.3 Homogeneous and Heterogeneous DDBMSs 577
24.2 Overview of Networking 580
24.3 Functions and Architectures of a DDBMS 583
24.3.1 Functions of a DDBMS 583
24.3.2 Reference Architecture for a DDBMS 583
24.3.3 Reference Architecture for a Federated MDBS 584
24.3.4 Component Architecture for a DDBMS 585
24.4 Distributed Relational Database Design 586
24.4.1 Data Allocation 587
24.4.2 Fragmentation 588
24.5 Transparencies in a DDBMS 595
24.5.1 Distribution Transparency 595
24.5.2 Transaction Transparency 597
24.5.3 Performance Transparency 600
24.5.4 DBMS Transparency 602
24.5.5 Summary of Transparencies in a DDBMS 602
24.6 Date誷 Twelve Rules for a DDBMS 603
Chapter Summary 604
Review Questions 605
Exercises 606
CHAPTER 25 Distributed DBMSs—Advanced Concepts 608
25.1 Distributed Transaction Management 609
25.2 Distributed Concurrency Control 610
25.2.1 Objectives 610
25.2.2 Distributed Serializability 610
25.2.3 Locking Protocols 611
25.2.4 Timestamp Protocols 613
25.3 Distributed Deadlock Management 613
25.4 Distributed Database Recovery 616
25.4.1 Failures in a Distributed Environment 616
25.4.2 How Failures Affect Recovery 617
25.4.3 Two-Phase Commit (2PC) 618
25.4.4 Three-Phase Commit (3PC) 623
25.4.5 Network Partitioning 626
25.5 The X/Open Distributed Transaction Processing Model 628
25.6 Distributed Query Optimization 630
25.6.1 Data Localization 631
25.6.2 Distributed Joins 634
25.6.3 Global Optimization 635
25.7 Distribution in Oracle 639
25.7.1 Oracle誷 DDBMS Functionality 639
Chapter Summary 643
Review Questions 644
Exercises 644
CHAPTER 26 Replication and Mobile Databases 646
26.1 Introduction to Data Replication 646
26.1.1 Synchronous Versus Asynchronous Replication 648
26.1.2 Applications of Replication 648
26.2 Replication Servers 649
26.2.1 Replication Server Functionality 649
26.2.2 Data Ownership 649
26.2.3 Implementation Issues 652
26.3 Introduction to Mobile Databases 655
26.3.1 Mobile DBMSs 656
26.3.2 Issues with Mobile DBMSs 657
26.4 Oracle Replication 661
26.4.1 Oracle誷 Replication Functionality 662
Chapter Summary 666
Review Questions 667
Exercises 667
PART 7 Object DBMSs
CHAPTER 27 Object-Oriented DBMSs—Concepts and Design 670
27.1 Advanced Database Applications 671
27.2 Weaknesses of RDBMSs 675
27.3 Storing Objects in a Relational Database 680
27.3.1 Mapping Classes to Relations 681
27.3.2 Accessing Objects in the Relational Database 682
27.4 Next-Generation Database Systems 683
27.5 Introduction to OODBMSs 684
27.5.1 Definition of Object-Oriented DBMSs 684
27.5.2 Functional Data Models 686
27.5.3 Persistent Programming Languages 690
27.5.4 The Object-Oriented Database System Manifesto 691
27.5.5 Alternative Strategies for Developing an OODBMS 693
27.6 Persistence in OODBMSs 693
27.6.1 Pointer Swizzling Techniques 695
27.6.2 Accessing an Object 698
27.6.3 Persistence Schemes 699
27.6.4 Orthogonal Persistence 701
27.7 Issues in OODBMSs 702
27.7.1 Transactions 703
27.7.2 Versions 703
27.7.3 Schema Evolution 704
27.7.4 Architecture 707
27.7.5 Benchmarking 708
27.8 Advantages and Disadvantages of OODBMSs 711
27.8.1 Advantages 711
27.8.2 Disadvantages 712
27.9 Object-Oriented Database Design 714
27.9.1 Comparison of Object-Oriented Data Modeling and ConceptualData Modeling 714
27.9.2 Relationships and Referential Integrity 715
27.9.3 Behavioral Design 717
27.10 Object-Oriented Analysis and Design with UML 718
27.10.1 UML Diagrams 719
27.10.2 Usage of UML in the Methodology for Database Design723
Chapter Summary 724
Review Questions 726
Exercises 726
CHAPTER 28 Object-Oriented DBMSs—Standards and Systems 728
28.1 Object Management Group 729
28.1.1 Background 729
28.1.2 The Common Object Request Broker Architecture 731
28.1.3 Other OMG Specifications 736
28.1.4 Model-Driven Architecture 738
28.2 Object Data Standard ODMG 3.0, 1999 738
28.2.1 Object Data Management Group 738
28.2.2 The Object Model 740
28.2.3 The Object Definition Language 746
28.2.4 The Object Query Language 748
28.2.5 Other Parts of the ODMG Standard 754
28.2.6 Mapping the Conceptual Design to a Logical (Object-Oriented)Design 756
28.3 ObjectStore 757
28.3.1 Architecture 757
28.3.2 Building an ObjectStore Application 759
28.3.3 Data Definition in ObjectStore 761
28.3.4 Data Manipulation in ObjectStore 763
Chapter Summary 766
Review Questions 767
Exercises 767
CHAPTER 29 Object-Relational DBMSs 768
29.1 Introduction to Object-Relational Database Systems 769
29.2 The Third-Generation Database Manifestos 771
29.2.1 The Third-Generation Database System Manifesto 772
29.2.2 The Third Manifesto 772
29.3 Postgres—An Early ORDBMS 774
29.3.1 Objectives of Postgres 774
29.3.2 Abstract Data Types 775
29.3.3 Relations and Inheritance 775
29.3.4 Object Identity 776
29.4 SQL:2008 777
29.4.1 Row Types 778
29.4.2 User-Defined Types 778
29.4.3 Subtypes and Supertypes 781
29.4.4 User-Defined Routines 783
29.4.5 Polymorphism 784
29.4.6 Reference Types and Object Identity 785
29.4.7 Creating Tables 786
29.4.8 Querying Data 788
29.4.9 Collection Types 789
29.4.10 Typed Views 792
29.4.11 Persistent Stored Modules 793
29.4.12 Triggers 793
29.4.13 Large Objects 796
29.4.14 Recursion 797
29.5 Query Processing and Optimization 797
29.5.1 New Index Types 800
29.6 Object-Oriented Extensions in Oracle 801
29.6.1 User-Defined Data Types 801
29.6.2 Manipulating Object Tables 806
29.6.3 Object Views 807
29.6.4 Privileges 808
29.7 Comparison of ORDBMS and OODBMS 808
Chapter Summary 809
Review Questions 810
Exercises 810
PART 8 The Web and DBMSs
CHAPTER 30 Web Technology and DBMSs 812
30.1 Introduction to the Internet and the Web 813
30.1.1 Intranets and Extranets 814
30.1.2 e-Commerce and e-Business 815
30.2 The Web 816
30.2.1 HyperText Transfer Protocol 817
30.2.2 HyperText Markup Language 818
30.2.3 Uniform Resource Locators 819
30.2.4 Static and Dynamic Web Pages 820
30.2.5 Web Services 821
30.2.6 Requirements for Web-DBMS Integration 822
30.2.7 Advantages and Disadvantages of the Web-DBMS Approach822
30.2.8 Approaches to Integrating the Web and DBMSs 826
30.3 Scripting Languages 826
30.3.1 JavaScript and JScript 827
30.3.2 VBScript 828
30.3.3 Perl and PHP 828
30.4 Common Gateway Interface (CGI) 829
30.4.1 Passing Information to a CGI Script 830
30.4.2 Advantages and Disadvantages of CGI 831
30.5 HTTP Cookies 832
30.6 Extending the Web Server 833
30.6.1 Comparison of CGI and API 834
30.7 Java 835
30.7.1 JDBC 838
30.7.2 SQLJ 842
30.7.3 Comparison of JDBC and SQLJ 842
30.7.4 Container-Managed Persistence (CMP) 843
30.7.5 Java Data Objects (JDO) 846
30.7.6 JPA (Java Persistence API) 852
30.7.7 Java Servlets 859
30.7.8 JavaServer Pages 860
30.7.9 Java Web Services 860
30.8 Microsoft誷 Web Platform 862
30.8.1 Universal Data Access 863
30.8.2 Active Server Pages and ActiveX Data Objects 863
30.8.3 Remote Data Services 866
30.8.4 Comparison of ASP and JSP 867
30.8.5 Microsoft .NET 867
30.8.6 Microsoft Web Services 871
30.9 Oracle Internet Platform 872
30.9.1 Oracle Application Server (OracleAS) 872
Chapter Summary 877
Review Questions 878
Exercises 879
CHAPTER 31 Semistructured Data and XML 880
31.1 Semistructured Data 881
31.1.1 Object Exchange Model (OEM) 882
31.1.2 Lore and Lorel 883
31.2 Introduction to XML 886
31.2.1 Overview of XML 889
31.2.2 Document Type Definitions (DTDs) 891
31.3 XML-Related Technologies 893
31.3.1 DOM and SAX Interfaces 894
31.3.2 Namespaces 894
31.3.3 XSL and XSLT 895
31.3.4 XPath (XML Path Language) 896
31.3.5 XPointer (XML Pointer Language) 897
31.3.6 XLink (XML Linking Language) 897
31.3.7 XHTML 897
31.3.8 Simple Object Access Protocol (SOAP) 898
31.3.9 Web Services Description Language (WSDL) 898
31.3.10 Universal Discovery, Description, and Integration (UDDI)899
31.4 XML Schema 901
31.4.1 Resource Description Framework (RDF) 907
Notation3 (N3) and Turtle 908
31.5 XML Query Languages 910
31.5.1 Extending Lore and Lorel to Handle XML 911
31.5.2 XML Query Working Group 912
31.5.3 XQuery-A Query Language for XML 913
31.5.4 XML Information Set 921
31.5.5 XQuery 1.0 and XPath 2.0 Data Model (XDM) 922
31.5.6 XQuery Update Facility 1.0 927
31.5.7 Formal Semantics 929
31.6 XML and Databases 935
31.6.1 Storing XML in Databases 936
31.6.2 XML and SQL 937
31.6.3 Native XML Databases 947
31.7 XML in Oracle 949
Chapter Summary 951
Review Questions 953
Exercises 954
PART 9 Business Intelligence
CHAPTER 32 Data Warehousing Concepts 956
32.1 Introduction to Data Warehousing 956
32.1.1 The Evolution of Data Warehousing 957
32.1.2 Data Warehousing Concepts 957
32.1.3 Benefits of Data Warehousing 958
32.1.4 Comparison of OLTP Systems and Data Warehousing 959
32.1.5 Problems of Data Warehousing 960
32.1.6 Real-Time Data Warehouse 962
32.2 Data Warehouse Architecture 962
32.2.1 Operational Data 962
32.2.2 Operational Data Store 963
32.2.3 ETL Manager 963
32.2.4 Warehouse Manager 963
32.2.5 Query Manager 964
32.2.6 Detailed Data 964
32.2.7 Lightly and Highly Summarized Data 964
32.2.8 Archive/Backup Data 964
32.2.9 Metadata 964
32.2.10 End-User Access Tools 965
32.3 Data Warehousing Tools and Technologies 966
32.3.1 Extraction, Transformation, and Loading (ETL) 966
32.3.2 Data Warehouse DBMS 968
32.3.3 Data Warehouse Metadata 970
32.3.4 Administration and Management Tools 971
32.4 Data Mart 971
32.4.1 Reasons for Creating a Data Mart 972
32.5 Data Warehousing Using Oracle 972
32.5.1 New Warehouse Features in Oracle 10g/11g 975
Chapter Summary 976
Review Questions 977
Exercise 977
CHAPTER 33 Data Warehousing Design 978
33.1 Designing a Data Warehouse Database 978
33.2 Data Warehouse Development Methodologies 979
33.3 Kimball誷 Business Dimensional Lifecycle 980
33.4 Dimensional Modeling 981
33.4.1 Comparison of DM and ER models 984
33.5 The Dimensional Modeling Stage of Kimball誷 BusinessDimensional Lifecycle 984
33.5.1 Create a High-Level Dimensional Model (Phase I) 985
Step 1: Select Business Process 985
Step 2: Declare Grain 985
Step 3: Choose Dimensions 986
Step 4: Identify Facts 987
33.5.2 Identify All Dimension Attributes for the Dimensional Model(Phase II) 989
33.6 Data Warehouse Development Issues 990
33.7 Data Warehousing Design Using Oracle 991
33.7.1 Oracle Warehouse Builder Components 992
33.7.2 Using Oracle Warehouse Builder 992
33.7.3 New Warehouse Builder Features in Oracle 10g/11g 996
Chapter Summary 997
Review Questions 998
Exercises 998
CHAPTER 34 OLAP 999
34.1 Online Analytical Processing 999
34.1.1 OLAP Benchmarks 1000
34.2 OLAP Applications 1001
34.3 Multidimensional Data Model 1002
34.3.1 Alternative Multidimensional Data Representations 1002
34.3.2 Dimensional Hierarchy 1004
34.3.3 Multidimensional Operations 1005
34.3.4 Multidimensional Schemas 1005
34.4 OLAP Tools 1006
34.4.1 Codd誷 Rules for OLAP Tools 1006
34.4.2 OLAP Server-Implementation Issues 1007
34.4.3 Categories of OLAP Servers 1008
34.5 OLAP Extensions to the SQL Standard 1011
34.5.1 Extended Grouping Capabilities 1011
34.5.2 Elementary OLAP Operators 1015
34.6 Oracle OLAP 1017
34.6.1 Oracle OLAP Environment 1017
34.6.2 Platform for Business Intelligence Applications 1017
34.6.3 Oracle Database 1018
34.6.4 Oracle OLAP 1019
34.6.5 Performance 1020
34.6.6 System Management 1021
34.6.7 System Requirements 1021
34.6.8 New OLAP Features in Oracle 11g 1021
Chapter Summary 1021
Review Questions 1022
Exercises 1022
CHAPTER 35 Data Mining 1023
35.1 Data Mining 1023
35.2 Data Mining Techniques 1024
35.2.1 Predictive Modeling 1025
35.2.2 Database Segmentation 1026
35.2.3 Link Analysis 1027
35.2.4 Deviation Detection 1028
35.3 The Data Mining Process 1028
35.3.1 The CRISP-DM Model 1028
35.4 Data Mining Tools 1030
35.5 Data Mining and Data Warehousing 1031
35.6 Oracle Data Mining (ODM) 1031
35.6.1 Data Mining Capabilities 1031
35.6.2 Enabling Data Mining Applications 1031
35.6.3 Predictions and Insights 1032
35.6.4 Oracle Data Mining Environment 1032
35.6.5 New Data Mining Features in Oracle 11g 1033
Chapter Summary 1033
Review Questions 1034
Exercises 1034
Appendices
APPENDIX A Users誖equirements Specification for DreamHome Case Study1036
APPENDIX B Other Case Studies 1040
APPENDIX C Alternative ER Modeling Notations 1049
APPENDIX D Summary of the Database Design Methodology forRelational Databases 1053
APPENDIX E Introduction to Pyrrho: A Lightweight RDBMS 1057
References 1069
Further Reading 1081
APPENDIX F File Organizations and Indexes (Online)
APPENDIX G When Is a DBMS Relational? (Online)
APPENDIX H Commercial DBMSs: Access and Oracle (Online)
APPENDIX I Programmatic SQL (Online)
APPENDIX J Estimating Disk Space Requirements (Online)
APPENDIX K Introduction to Object-Orientation (Online)
APPENDIX L Example Web Scripts (Online)
猜您喜欢
