Cover......Page 1
Title Page......Page 5
Copyright Page......Page 6
Acknowledgments......Page 29
About the Authors......Page 31
Contents......Page 9
Preface......Page 21
Part 1 Getting Started......Page 35
Chapter Objectives......Page 36
The Importance of Databases in the Internet and Smartphone World......Page 37
The Characteristics of Databases......Page 39
A Note on Naming Conventions......Page 40
A Database Has Data and Relationships......Page 41
Databases Create Information......Page 42
Multiuser Database Applications......Page 43
E-Commerce Database Applications......Page 44
The Components of a Database System......Page 45
Database Applications and SQL......Page 46
The DBMS......Page 48
The Database......Page 49
What Is Microsoft Access?......Page 51
What Is an Enterprise-Class Database System?......Page 52
Database Design from Existing Data......Page 54
Database Redesign......Page 56
What You Need to Learn......Page 57
The Early Years......Page 58
The Emergence and Dominance of the Relational Model......Page 60
Post-Relational Developments......Page 61
Summary......Page 63
Key Terms......Page 64
Review Questions......Page 65
Project Questions......Page 67
Chapter Objectives......Page 70
Cape Codd Outdoor Sports......Page 71
Business Intelligence Systems and Data Warehouses......Page 72
The Cape Codd Outdoor Sports Extracted Retail Sales Data......Page 73
RETAIL_ORDER Data......Page 74
SKU_DATA Table......Page 76
The Complete Cape Codd Data Extract Schema......Page 77
SQL Background......Page 78
The SQL SELECT/FROM/WHERE Framework......Page 79
Reading Specified Columns from a Single Table......Page 80
Specifying Column Order in SQL Queries from a Single Table......Page 81
Using SQL in Microsoft Access 2013......Page 82
Using SQL in Microsoft SQL Server 2014......Page 87
Using SQL in Oracle Database......Page 90
Using SQL in Oracle MySQL 5.6......Page 92
Reading Specified Rows from a Single Table......Page 95
Sorting the SQL Query Results......Page 99
SQL WHERE Clause Options......Page 102
Using SQL Built-in Aggregate Functions......Page 109
SQL Expressions in SQL SELECT Statements......Page 113
Grouping Rows in SQL SELECT Statements......Page 116
Querying Multiple Tables with Subqueries......Page 121
Querying Multiple Tables with Joins......Page 124
The SQL JOIN ON Syntax......Page 129
Outer Joins......Page 132
Using SQL Set Operators......Page 136
Summary......Page 139
Key Terms......Page 140
Review Questions......Page 141
Project Questions......Page 148
Case Questions......Page 151
The Queen Anne Curiosity Shop......Page 155
Morgan Importing......Page 163
Part 2 Database Design......Page 167
Chapter Objectives......Page 168
Relations......Page 170
Characteristics of Relations......Page 171
Alternative Terminology......Page 173
Functional Dependencies......Page 174
Finding Functional Dependencies......Page 176
Keys......Page 179
Modification Anomalies......Page 182
A Short History of Normal Forms......Page 183
From First Normal Form to Boyce-Codd Normal Form Step by Step......Page 184
Eliminating Anomalies from Functional Dependencies with BCNF......Page 189
Eliminating Anomalies from Multivalued Dependencies......Page 198
Domain/Key Normal Form......Page 201
Key Terms......Page 202
Review Questions......Page 203
Project Questions......Page 205
Case Questions......Page 206
The Queen Anne Curiosity Shop......Page 207
Morgan Importing......Page 209
Chapter Objectives......Page 211
Assess Table Structure......Page 212
Advantages and Disadvantages of Normalization......Page 213
Normalizing with SQL......Page 214
Choosing Not to Use BCNF......Page 215
Designing Read-Only Databases......Page 216
Customized Duplicated Tables......Page 217
Common Design Problems......Page 219
The Multivalue, Multicolumn Problem......Page 220
Inconsistent Values......Page 221
Missing Values......Page 222
The General-Purpose Remarks Column......Page 223
Key Terms......Page 224
Review Questions......Page 225
Case Questions......Page 227
The Queen Anne Curiosity Shop......Page 228
Morgan Importing......Page 229
Chapter Objectives......Page 230
Entities......Page 231
Identifiers......Page 232
Relationships......Page 233
Maximum Cardinality......Page 235
Minimum Cardinality......Page 236
Variations of the E-R Model......Page 237
E-R Diagrams Using the IE Crowβs Foot Model......Page 238
ID-Dependent Entities......Page 240
Non-ID-Dependent Weak Entities......Page 241
Subtype Entities......Page 242
Strong Entity Patterns......Page 245
ID-Dependent Relationships......Page 249
Mixed Identifying and Nonidentifying Patterns......Page 255
The For-Use-By Pattern......Page 258
Recursive Patterns......Page 259
The Data Modeling Process......Page 262
The Department Report......Page 263
The Department/Major Report......Page 264
The Student Acceptance Letter......Page 266
Summary......Page 268
Key Terms......Page 269
Review Questions......Page 270
Project Questions......Page 272
Case Questions......Page 278
The Queen Anne Curiosity Shop......Page 280
Morgan Importing......Page 281
Chapter Objectives......Page 282
Selecting the Primary Key......Page 283
Specifying Column Properties......Page 286
Verify Normalization......Page 293
Relationships Between Strong Entities......Page 294
Relationships Using ID-Dependent Entities......Page 297
Relationships with a Weak Non-ID-Dependent Entity......Page 301
Relationships in Mixed Entity Designs......Page 302
Relationships Between Supertype and Subtype Entities......Page 304
Recursive Relationships......Page 305
Representing Ternary and Higher-Order Relationships......Page 306
Relational Representation of the Highline University Data Model......Page 308
Design for Minimum Cardinality......Page 311
Actions when the Parent Is Required......Page 312
Actions when the Child Is Required......Page 313
Implementing Actions for M-O Relationships......Page 314
Implementing Actions for M-M Relationships......Page 315
Documenting the Minimum Cardinality Design......Page 316
An Additional Complication......Page 317
The View Ridge Gallery Database......Page 318
View Ridge Gallery Database Summary of Requirements......Page 319
The View Ridge Data Model......Page 320
Database Design with Data Keys......Page 321
Minimum Cardinality Enforcement for Required Parents......Page 322
Minimum Cardinality Enforcement for the Required Child......Page 323
Column Properties for the View Ridge Database Design Tables......Page 325
Summary......Page 327
Review Questions......Page 329
Project Questions......Page 331
Case Questions......Page 332
Morgan Importing......Page 334
Part 3 Database Implementation......Page 335
Chapter Objectives......Page 336
SQL DDL and DML......Page 337
Using SQL Scripts......Page 339
Using the SQL CREATE TABLE Statement......Page 340
Creating the VRG Database ARTIST Table......Page 341
Creating the VRG Database WORK Table and the 1:N ARTIST-to-WORK Relationship......Page 344
Implementing Required Parent Rows......Page 345
Creating Default Values and Data-Constraints with SQL......Page 346
Creating the VRG Database Tables......Page 348
The SQL ALTER TABLE Statement......Page 351
The SQL DROP TABLE Statement......Page 352
The SQL CREATE INDEX Statement......Page 353
The SQL INSERT Statement......Page 354
Populating the VRG Database Tables......Page 355
The SQL UPDATE Statement......Page 361
The SQL MERGE Statement......Page 362
Using SQL Views......Page 363
Using SQL Views to Hide Columns and Rows......Page 366
Using SQL Views to Hide Complicated SQL Syntax......Page 368
Layering Built-in Functions......Page 369
Using SQL Views for Isolation, Multiple Permissions, and Multiple Triggers......Page 371
Updating SQL Views......Page 372
Embedding SQL in Program Code......Page 373
Using SQL User-Defined Functions......Page 375
Using SQL Triggers......Page 379
Using Stored Procedures......Page 384
Summary......Page 388
Key Terms......Page 390
Review Questions......Page 391
Project Questions......Page 400
Case Questions......Page 403
The Queen Anne Curiosity Shop......Page 417
Morgan Importing......Page 424
Chapter Objectives......Page 430
SQL Statements for Checking Functional Dependencies......Page 431
What Is a Correlated Subquery......Page 432
How Do I Analyze an Existing Database......Page 437
Reverse Engineering......Page 438
Database Backup and Test Databases......Page 439
Changing Table Names......Page 440
Adding and Dropping Columns......Page 442
Changing a Column Data Type or Column Constraints......Page 443
Changing Minimum Cardinalities......Page 444
Changing Maximum Cardinalities......Page 445
Forward Engineering......Page 448
Summary......Page 449
Review Questions......Page 450
Project Questions......Page 452
Case Questions......Page 453
The Queen Anne Curiosity Shop......Page 454
Morgan Importing......Page 455
Part 4 Multiuser Database Processing......Page 457
Chapter Objectives......Page 458
Database Administration......Page 459
Managing the Database Structure......Page 460
Concurrency Control......Page 461
The Need for Atomic Transactions......Page 462
Resource Locking......Page 465
Optimistic Versus Pessimistic Locking......Page 467
SQL Transaction Control Language and Declaring Lock Characteristics......Page 468
Implicit and Explicit COMMIT TRANSACTION......Page 469
Consistent Transactions......Page 470
Transaction Isolation Level......Page 471
SQL Cursors......Page 472
Processing Rights and Responsibilities......Page 474
DBMS Security......Page 475
DBMS Security Guidelines......Page 476
Application Security......Page 477
The SQL Injection Attack......Page 478
Recovery via Reprocessing......Page 479
Recovery via Rollback/Rollforward......Page 480
Managing the DBMS......Page 482
Maintaining the Data Repository......Page 483
Summary......Page 484
Key Terms......Page 485
Review Questions......Page 486
Project Questions......Page 487
Case Questions......Page 488
The Queen Anne Curiosity Shop......Page 489
Morgan Importing......Page 490
Chapter Objectives......Page 492
Installing the DBMS......Page 493
Creating and Running SQL Scripts......Page 494
Database Application Logic and SQL/Persistent Stored Modules (SQL/PSM)......Page 495
Choose Your DBMS Product(s)!......Page 496
Summary......Page 497
Project Questions......Page 498
Part 5 Database Access Standards......Page 499
Chapter Objectives......Page 500
A Web Database Application for the View Ridge Gallery......Page 502
The Web Database Processing Environment......Page 503
Database Server Access Standards......Page 504
The ODBC Standard......Page 505
ODBC Architecture......Page 506
Conformance Levels......Page 507
Creating an ODBC Data Source Name......Page 508
The Microsoft.NET Framework and ADO.NET......Page 514
OLE DB......Page 516
ADO and ADO.NET......Page 519
The ADO.NET Object Model......Page 520
JDBC......Page 524
Java Server Pages (JSP) and Servlets......Page 526
Apache Tomcat......Page 527
Web Database Processing with PHP......Page 528
Web Database Processing with PHP and the NetBeans IDE......Page 529
Getting Started with HTML Web Pages......Page 531
Creating the index.html Web Page......Page 532
Using PHP......Page 534
Web Page Examples with PHP......Page 541
Example 1: Updating a Table......Page 542
Example 3: Invoking a Stored Procedure......Page 547
Challenges for Web Database Processing......Page 553
The Importance of XML......Page 554
XML as a Markup Language......Page 555
Using the SQL SELECT . . . FOR XML Statement......Page 556
Summary......Page 558
Key Terms......Page 559
Review Questions......Page 560
Project Questions......Page 563
The Queen Anne Curiosity Shop......Page 565
Morgan Importing......Page 566
Chapter Objectives......Page 567
Reporting Systems......Page 569
Data Warehouses and Data Marts......Page 570
Components of a Data Warehouse......Page 571
Data Warehouses Versus Data Marts......Page 573
Dimensional Databases......Page 575
RFM Analysis......Page 582
OLAP......Page 584
Data Mining......Page 593
Types of Distributed Databases......Page 594
Challenges of Distributed Databases......Page 595
Object-Relational Databases......Page 596
Virtualization......Page 597
Cloud Computing......Page 598
Column Family Databases......Page 600
MapReduce......Page 603
Summary......Page 604
Key Terms......Page 605
Review Questions......Page 606
Project Questions......Page 608
Case Questions......Page 609
The Queen Anne Curiosity Shop......Page 610
Morgan Importing......Page 611
Appendices: ONLINE APPENDICES: SEE PAGE 610 FOR INSTRUCTIONS......Page 612
Bibliography......Page 613
A......Page 615
C......Page 616
D......Page 617
F......Page 619
I......Page 620
L......Page 621
N......Page 622
O......Page 623
P......Page 624
R......Page 625
S......Page 626
U......Page 628
X......Page 629
C......Page 630
D......Page 631
E......Page 632
I......Page 633
M......Page 634
O......Page 635
R......Page 636
S......Page 637
T......Page 638
X......Page 639