Database Systems: Design, Implementation and Management

Front Cover......Page 1
Title Page......Page 2
Copyright......Page 3
Table of Contents......Page 8
PART I: DATABASE CONCEPTS......Page 32
Business Vignette: The Relational Revolution......Page 33
CHAPTER 1: DATABASE SYSTEMS......Page 34
1.2 Data vs. Information......Page 35
1.3.1 Role and Advantages of the DBMS......Page 37
1.3.2 Types of Databases......Page 39
1.4 Why Database Design is Important......Page 40
1.5.2 Computerized File Systems......Page 41
1.6 Problems with File System Data Processing......Page 44
1.6.1 Structural and Data Dependence......Page 45
1.6.2 Data Redundancy......Page 46
1.7 Database Systems......Page 47
1.7.1 The Database System Environment......Page 48
1.7.2 DBMS Functions......Page 50
1.7.3 Managing the Database System: A Shift in Focus......Page 53
Key Terms......Page 55
Problems......Page 56
CHAPTER 2: DATA MODELS......Page 59
2.2 The Importance of Data Models......Page 60
2.3 Data Model Basic Building Blocks......Page 61
2.4 Business Rules......Page 62
2.4.2 Translating Business Rules into Data Model Components......Page 63
2.5 The Evolution of Data Models......Page 64
2.5.1 Hierarchical and Network Models......Page 65
2.5.2 The Relational Model......Page 66
2.5.3 The Entity Relationship Model......Page 68
2.5.4 The Object-Oriented (OO) Model......Page 70
2.5.5 Newer Data Models: Object/Relational and XML......Page 72
2.5.7 Data Models: A Summary......Page 73
2.6.1 The External Model......Page 76
2.6.2 The Conceptual Model......Page 78
2.6.4 The Physical Model......Page 79
Key Terms......Page 81
Review Questions......Page 82
Problems......Page 83
PART II: DESIGN CONCEPTS......Page 86
Business Vignette: BP’s Data Modeling Initiative......Page 87
CHAPTER 3: THE RELATIONAL DATABASE MODEL......Page 88
3.1.1 Tables and Their Characteristics......Page 89
3.2 Keys......Page 92
3.3 Integrity Rules......Page 96
3.4 Relational Set Operators......Page 98
3.5 The Data Dictionary and the System Catalog......Page 104
3.6.1 The 1:M Relationship......Page 106
3.6.3 The M:N Relationship......Page 108
3.7 Data Redundancy Revisited......Page 114
3.8 Indexes......Page 116
3.9 Codd’s Relational Database Rules......Page 118
Key Terms......Page 119
Review Questions......Page 120
Problems......Page 122
CHAPTER 4: ENTITY RELATIONSHIP (ER) MODELING......Page 129
4.1.1 Entities......Page 130
4.1.2 Attributes......Page 131
4.1.3 Relationships......Page 135
4.1.4 Connectivity and Cardinality......Page 137
4.1.6 Relationship Strength......Page 138
4.1.7 Weak Entities......Page 140
4.1.8 Relationship Participation......Page 143
4.1.9 Relationship Degree......Page 146
4.1.10 Recursive Relationships......Page 147
4.1.11 Associative (Composite) Entities......Page 151
4.2 Developing an ER Diagram......Page 153
4.3 Database Design Challenges: Conflicting Goals......Page 158
Key Terms......Page 164
Review Questions......Page 165
Problems......Page 167
Cases......Page 170
CHAPTER 5: ADVNCED DATA MODELING......Page 177
5.1.1 Entity Supertypes and Subtypes......Page 178
5.1.2 Specialization Hierarchy......Page 179
5.1.3 Inheritance 150......Page 180
5.1.5 Disjoint and Overlapping Constraints......Page 181
5.1.6 Completeness Constraint......Page 183
5.2 Entity Clustering......Page 184
5.3 Entity Integrity: Selecting Primary Keys......Page 185
5.3.2 Primary Key Guidelines......Page 186
5.3.3 When to Use Composite Primary Keys......Page 187
5.3.4 When to Use Surrogate Primary Keys......Page 188
5.4 Design Cases: Learning Flexible Database Design......Page 189
5.4.1 Design Case #1: Implementing 1:1 Relationships......Page 190
5.4.2 Design Case #2: Maintaining History of Time-Variant Data......Page 191
5.4.3 Design Case #3: Fan Traps......Page 192
5.4.4 Design Case #4: Redundant Relationships......Page 194
Key Terms......Page 195
Review Questions......Page 196
Problems......Page 197
Cases......Page 198
CHAPTER 6: NORMALIZATION OF DATABASE TABLES......Page 204
6.2 The Need for Normalization......Page 205
6.3 The Normalization Process......Page 209
6.3.1 Conversion to First Normal Form......Page 211
6.3.2 Conversion to Second Normal Form......Page 214
6.3.3 Conversion to Third Normal Form......Page 215
6.4 Improving the Design......Page 217
6.5 Surrogate Key Considerations......Page 221
6.6.1 The Boyce-Codd Normal Form (BCNF)......Page 222
6.6.2 Fourth Normal Form (4NF)......Page 226
6.7 Normalization and Database Design......Page 227
6.8 Denormalization......Page 230
6.9 Data-Modeling Checklist......Page 234
Key Terms......Page 236
Review Questions......Page 237
Problems......Page 238
PART III: ADVANCED DESIGN AND IMPLEMENTATION......Page 248
Business Vignette: The Many Benefits of BI......Page 249
CHAPTER 7: INTRODUCTION TO STRUCTURED QUERY LANGUAGE (SQL)......Page 250
7.1 Introduction to SQL......Page 251
7.2.1 The Database Model......Page 253
7.2.3 The Database Schema......Page 255
7.2.4 Data Types......Page 256
7.2.5 Creating Table Structures......Page 259
7.2.6 SQL Constraints......Page 262
7.2.7 SQL Indexes......Page 265
7.3.1 Adding Table Rows......Page 267
7.3.3 Listing Table Rows......Page 268
7.3.5 Restoring Table Contents......Page 270
7.3.6 Deleting Table Rows......Page 271
7.4.1 Selecting Rows with Conditional Restrictions......Page 272
7.4.3 Logical Operators: AND‚ OR‚ and NOT......Page 277
7.4.4 Special Operators......Page 279
7.5.1 Changing a Column’s Data Type......Page 283
7.5.3 Adding a Column......Page 284
7.5.5 Advanced Data Updates......Page 285
7.5.6 Copying Parts of Tables......Page 287
7.5.7 Adding Primary and Foreign Key Designations......Page 288
7.6.1 Ordering a Listing......Page 289
7.6.3 Aggregate Functions......Page 291
7.6.4 Grouping Data......Page 296
7.7 Virtual Tables: Creating a View......Page 299
7.8 Joining Database Tables......Page 300
7.8.2 Recursive Joins......Page 303
7.8.3 Outer Joins......Page 304
Summary......Page 306
Review Questions......Page 307
Problems......Page 308
Cases......Page 317
CHAPTER 8: ADVANCED SQL......Page 327
8.1 Relational Set Operators......Page 328
8.1.1 UNION......Page 329
8.1.3 INTERSECT......Page 330
8.1.4 MINUS......Page 331
8.1.5 Syntax Alternatives......Page 333
8.2 SQL Join Operators......Page 335
8.2.1 Cross Join......Page 336
8.2.3 Join USING Clause......Page 337
8.2.4 JOIN ON Clause......Page 338
8.2.5 Outer Joins......Page 339
8.3 Subqueries and Correlated Queries......Page 342
8.3.1 WHERE Subqueries......Page 344
8.3.2 IN Subqueries......Page 345
8.3.3 HAVING Subqueries......Page 346
8.3.4 Multirow Subquery Operators: ANY and ALL......Page 347
8.3.5 FROM Subqueries......Page 348
8.3.6 Attribute List Subqueries......Page 349
8.3.7 Correlated Subqueries......Page 351
8.4.1 Date and Time Functions......Page 354
8.4.3 String Functions......Page 357
8.4.4 Conversion Functions......Page 358
8.5 Oracle Sequences......Page 360
8.6 Updatable Views......Page 363
8.7 Procedural SQL......Page 366
8.7.1 Triggers......Page 371
8.7.2 Stored Procedures......Page 380
8.7.3 PL/SQL Processing with Cursors......Page 384
8.7.4 PL/SQL Stored Functions......Page 387
8.8 Embedded SQL......Page 388
Summary......Page 393
Review Questions......Page 394
Problems......Page 395
Cases......Page 399
CHAPTER 9: DATABASE DESIGN......Page 402
9.1 The Information System......Page 403
9.2 The Systems Development Life Cycle (SDLC)......Page 405
9.2.2 Analysis......Page 406
9.2.5 Maintenance......Page 407
9.3.1 The Database Initial Study......Page 408
9.3.2 Database Design......Page 412
9.3.3 Implementation and Loading......Page 414
9.3.4 Testing and Evaluation......Page 416
9.3.6 Maintenance and Evolution......Page 419
9.4 Conceptual Design......Page 420
9.4.1 Data Analysis and Requirements......Page 421
9.4.2 Entity Relationship Modeling and Normalization......Page 423
9.4.3 Data Model Verification......Page 426
9.5 DBMS Software Selection......Page 429
9.6.1 Map the Conceptual Model to the Logical Model......Page 430
9.6.3 Validate Logical Model Integrity Constraints......Page 432
9.7.1 Define Data Storage Organization......Page 433
9.7.3 Determine Performance Measures......Page 434
9.8 Database Design Strategies......Page 435
9.9 Centralized vs. Decentralized Design......Page 436
Key Terms......Page 439
Problems......Page 440
PART IV: ADVANCED DATABASE CONCEPTS......Page 442
Business Vignette: Combating Data Explosion......Page 443
CHAPTER 10: TRANSACTION MANAGEMENT AND CONCURRENCY CONTROL......Page 444
10.1 What Is a Transaction?......Page 445
10.1.1 Evaluating Transaction Results......Page 446
10.1.3 Transaction Management with SQL......Page 449
10.1.4 The Transaction Log......Page 450
10.2 Concurrency Control......Page 451
10.2.1 Lost Updates......Page 452
10.2.2 Uncommitted Data......Page 453
10.2.3 Inconsistent Retrievals......Page 454
10.2.4 The Scheduler......Page 455
10.3 Concurrency Control with Locking Methods......Page 456
10.3.1 Lock Granularity......Page 457
10.3.2 Lock Types......Page 460
10.3.3 Two-Phase Locking to Ensure Serializability......Page 461
10.3.4 Deadlocks......Page 462
10.4 Concurrency Control with Time Stamping Methods......Page 463
10.4.1 Wait/Die and Wound/Wait Schemes......Page 464
10.6 Database Recovery Management......Page 465
10.6.1 Transaction Recovery......Page 466
Summary......Page 470
Review Questions......Page 471
Problems......Page 472
CHAPTER 11: DATABASE PERFORMANCE TUNING AND QUERY OPTIMIZATION......Page 475
11.1 Database Performance-Tuning Concepts......Page 476
11.1.2 DBMS Architecture......Page 477
11.1.3 Database Statistics......Page 479
11.2 Query Processing......Page 481
11.2.1 SQL Parsing Phase......Page 482
11.2.4 Query Processing Bottlenecks......Page 483
11.3 Indexes and Query Optimization......Page 484
11.4 Optimizer Choices......Page 486
11.4.1 Using Hints to Affect Optimizer Choices......Page 488
11.5.1 Index Selectivity......Page 489
11.5.2 Conditional Expressions......Page 490
11.6 Query Formulation......Page 492
11.7 DBMS Performance Tuning......Page 493
11.8 Query Optimization Example......Page 495
Summary......Page 504
Review Questions......Page 505
Problems......Page 506
CHAPTER 12: DISTRIBUTED DATABASE MANAGEMENT SYSTEMS......Page 510
12.1 The Evolution of Distributed Database Management Systems......Page 511
12.2 DDBMS Advantages and Disadvantages......Page 513
12.3 Distributed Processing and Distributed Databases......Page 514
12.4 Characteristics of Distributed Database Management Systems......Page 515
12.5 DDBMS Components......Page 516
12.6.1 Single-Site Processing‚ Single-Site Data (SPSD)......Page 518
12.6.2 Multiple-Site Processing‚ Single-Site Data (MPSD)......Page 519
12.6.3 Multiple-Site Processing‚ Multiple-Site Data (MPMD)......Page 520
12.7 Distributed Database Transparency Features......Page 521
12.8 Distribution Transparency......Page 522
12.9.1 Distributed Requests and Distributed Transactions......Page 524
12.9.3 Two-Phase Commit Protocol......Page 528
12.10 Performance Transparency and Query Optimization......Page 529
12.11.1 Data Fragmentation......Page 531
12.11.2 Data Replication......Page 534
12.11.3 Data Allocation......Page 536
12.12 Client/Server vs.DDBMS......Page 537
12.13 C. J. Data’s Twelve Commandments for Distributed Databases......Page 538
Summary......Page 539
Review Questions......Page 540
Problems......Page 541
CHAPTER 13: BUSINESS INTELLIGENCE AND DATA WAREHOUSES......Page 544
13.2 Business Intelligence......Page 545
13.3 Business Intelligence Architecture......Page 547
13.4.1 Operational Data vs. Decision Support Data......Page 551
13.4.2 Decision Support Database Requirements......Page 553
13.5 The Data Warehouse......Page 556
13.5.1 Twelve Rules that Define a Data Warehouse......Page 558
13.6.1 Multidimensional Data Analysis Techniques......Page 559
13.6.5 OLAP Architecture......Page 563
13.6.6 Relational OLAP......Page 567
13.6.7 Multidimensional OLAP......Page 569
13.6.8 Relational vs. Multidimensional OLAP......Page 570
13.7.1 Facts......Page 571
13.7.3 Attributes......Page 572
13.7.4 Attribute Hierarchies......Page 574
13.7.5 Star Schema Representation......Page 575
13.7.6 Performance-Improving Techniques for the Star Schema......Page 578
13.8.1 The Data Warehouse as an Active Decision Support Framework......Page 581
13.8.4 Apply Database Design Procedures......Page 582
13.9 Data Mining......Page 583
13.10 SQL Extensions for OLAP......Page 586
13.10.1 The ROLLUP Extension......Page 587
13.10.2 The CUBE Extension......Page 588
13.10.3 Materialized Views......Page 589
Summary......Page 594
Review Questions......Page 595
Problems......Page 596
PART V: DATABASES AND THE INTERNET......Page 602
Business Vignette: KBB Transforms with Innovative Web Services......Page 603
CHAPTER 14: DATABASE CONNECTIVITY AND WEB TECHNOLOGIES......Page 604
14.1.2 ODBC‚ DAO‚ and RDO......Page 605
14.1.3 OLE-DB......Page 609
14.1.4 ADO.NET......Page 611
14.1.5 Java Database Connectivity (JDBC)......Page 613
14.2 Internet Databases......Page 615
14.2.1 Web-to-Database Middleware: Server-Side Extensions......Page 616
14.2.2 Web Server Interfaces......Page 618
14.2.3 The Web Browser......Page 619
14.2.4 Client-Side Extensions......Page 620
14.2.5 Web Application Servers......Page 621
14.3 Extensible Markup Language (XML)......Page 622
14.3.1 Document Type Definitions (DTD) and XML Schemas......Page 624
14.3.2 XML Presentation......Page 626
14.3.3 XML Applications......Page 627
14.4 SQL Data Services......Page 630
Summary......Page 632
Review Questions......Page 633
Problems......Page 634
PART VI: DATABASE ADMINISTRATION......Page 636
Business Vignette: The Rising SQL Injection Threat......Page 637
CHAPTER 15: DATABASE ADMINISTRATION AND SECURITY......Page 638
15.1 Data as a Corporate Asset......Page 639
15.2 The Need for and Role of a Database in an Organization......Page 640
15.3 Introduction of a Database: Special Considerations......Page 642
15.4 The Evolution of the Database Administration Function......Page 643
15.5 The Database Environment’s Human Component......Page 646
15.5.1 The DBA’s Managerial Role......Page 648
15.5.2 The DBA’s Technical Role......Page 653
15.6.1 Security Policies......Page 659
15.6.2 Security Vulnerabilities......Page 660
15.6.3 Database Security......Page 661
15.7.1 The Data Dictionary......Page 663
15.7.2 CASE Tools......Page 665
15.8 Developing a Data Administration Strategy......Page 667
15.9 The DBA at Work: Using Oracle for Database Administration......Page 669
15.9.2 The Default Login......Page 670
15.9.3 Ensuring an Automatic RDBMS Start......Page 671
15.9.4 Creating Tablespaces and Datafiles......Page 672
15.9.5 Managing the Database Objects:Tables,Views,Triggers, and Procedures......Page 673
15.9.6 Managing Users and Establishing Security......Page 674
15.9.7 Customizing the Database Initialization Parameters......Page 677
Summary......Page 678
Review Questions......Page 679
Glossary......Page 683
Index......Page 702