PART 1&GETTING STARTED 1
Chapter 1:Introduction 2
Chapter Objectives 2
The Characteristics of Databases 3
A Note on Naming Conventions 3
A Database Has Data and Relationships 4
Databases Create Information 5
Database Examples 6
Single-User Database Applications 6
Multiuser Database Applications 6
E-Commerce Database Applications 7
Reporting and Data Mining Database Applications 7
The Components of a Database System 8
Database Applications and SQL 9
The DBMS 11
The Database 12
Personal Versus Enterprise-Class Database Systems 13
What Is Microsoft Access? 13
What Is an Enterprise-Class Database System? 15
Database Design 16
Database Design from Existing Data 16
Database Design for New Systems Development 17
Database Redesign 18
What You Need to Learn 19
A Brief History of Database Processing 20
The Early Years 20
The Emergence and Dominance of the Relational Model 22
Post-Relational Developments 23
Summary 24
Key Terms 35
Review Questions 26
Project Questions 28
Chapter 2:Introduction to Structured Query Language 31
Chapter Objectives 31
Cape Codd Outdoor Sports 32
The Retail Sales Data Extraction 33
RETAIL_ORDER Data 33
ORDER_ITEM Data 34
SKU_DATA Table 35
Data Extracts Are Common 35
SQL Background 35
The SQL SELECT/FROM/WHERE Framework 36
Reading Specified Columns from a Single Table 36
Reading Specified Rows from a Single Table 38
Reading Specified Columns and Rows from a Single Table 39
Submitting SQL Statements to the DBMS 40
Using SQL in Microsoft Access 2007 40
Using SQL in Microsoft SQL Server 2008 46
Using SQL in Oracle Database 11g 48
Using SQL in Sun Microsystems MySQL 5.1 50
SQL Enhancements for Querying a Single Table 52
Sorting the Results 53
SQL WHERE Clause Options 55
Combing the SQL WHERE Clause and the SQL ORDER BY Clause 59
Performing Calculations in SQL Queries 59
Using SQL Built-in Functions 59
SQL Expressions in SQL SELECT Statements 62
Grouping in SQL SELECT Statements 64
Looking for Patterns in NASDAQ Trading 67
Investigating the Characteristics of the Data 68
Searching for Patterns in Trading by Day of Week 69
Querying Two or More Table swith SQL 70
Querying Multiple Tables with Subqueries 70
Querying Multiple Tables with Joins 73
Comparing Subqueries and Joins 76
Summary 77
Key Terms 77
Review Questions 78
Project Questions 82
Marcia's Dry Cleaning 86
Morgan Importing 89
PART 2&DATABASE DESIGN 93
Chapter 3:The Relational Model and Normalization 94
Chapter Objectives 94
Relational Model Terminology 96
Relations 96
Characteristics of Relations 97
Alternative Terminology 98
Functional Dependencies 100
Finding Functional Dependencies 101
Keys 104
Normal Forms 106
Modification Anomalies 106
A Short History of Normal Forms 107
Normalization Categories 108
From First Normal Form to Boyce-Codd Normal Form 108
Eliminating Anomalies from Functional Dependencies 110
Eliminating Anomalies from Multivalued Dependencies 117
Fifth Normal Form 120
Domain/Key Normal Form 120
Summary 121
Key Terms 121
Review Questions 122
Project Questions 124
Marcia's Dry Cleaning 125
Morgan Importing 126
Chapter 4:Database Design Using Normalization 127
Chapter Objectives 127
Assess Table Structure 128
Designing Updatable Databases 129
Advantages and Disadvantages of Normalization 129
Functional Dependencies 129
Normalizing with SQL 130
Choosing Not to Use BCNF 131
Multivalued Dependencies 132
Designing Read-Only Databases 132
Denormalization 132
Customized Duplicated Tables 134
Common Design Problems 135
The Multivalue,Multicolumn Problem 135
Inconsistent Values 136
Missing Values 137
The General-Purpose Remarks Column 138
Summary 139
Key Terms 139
Review Questions 139
Project Questions 141
Marcia's Dry Cleaning 142
Morgan Importing 143
Chapter 5:Data Modeling with the Entity-Relationship Model 144
Chapter Objectives 144
The Purpose of a Data Model 145
The Entity-Relationship Model 145
Entities 145
Attributes 146
Identifiers 146
Relationships 147
Maximum Cardinality 149
Minimum Cardinality 150
Entity-Relationship Diagrams and Their Versions 151
Variations of the E-R Model 151
E-R Diagrams Using the IE Crow's Foot Model 152
Strong Entities and Weak Entities 153
ID-Dependent Entities 153
Non-ID-Dependent Weak Entities 154
Subtype Entities 156
Patterns in Forms,Reports,and E-R Models 157
Strong Entity Patterns 158
ID-Dependent Relationships 162
Mixed Identifying and Nonidentifying Patterns 168
The For-Use-By Pattern 171
Recursive Patterns 172
The Data Modeling Process 174
The College Report 175
The Department Report 175
The Department/Major Report 176
The Student Acceptance Letter 178
Summary 180
Key Terms 181
Review Questions 182
Project Questions 184
Marcia's Dry Cleaning 191
Morgan Importing 191
Chapter 6:Transforming Data Models into Database Designs 192
Chapter Objectives 192
Create a Table for Each Entity 193
Selecting the Primary Key 194
Specifying Candidate(Alternate)Keys 195
Specify Column Properties 195
Verify Normalization 197
Create Relationships 198
Relationships Between Strong Entities 198
Relationships Using ID-Dependent Entities 201
Relationships with a Weak Non-ID-Dependent Entity 206
Relationships in Mixed Entity Designs 206
Relationships Between Supertype and Subtype Entities 208
Recursive Relationships 208
Representing Ternary and Higher-Order Relationships 210
Relational Representation of the Highline University Data Model 213
Design for Minimum Cardinality 214
Actions When the Parent Is Required 216
Actions When the Child Is Required 217
Implementing Actions for M-O Relationships 218
Implementing Actions for O-M Relationships 219
Implementing Actions for M-M Relationships 219
Designing Special Case M-M Relationships 219
Documenting the Minimum Cardinality Design 220
An Additional Complication 222
Summary of Minimum Cardinality Design 222
The View Ridge Gallery Database 222
Summary of Requirements 222
The View Ridge Data Model 223
Database Design with Data Keys 224
Minimum Cardinality Enforcement for Required Parents 225
Minimum Cardinality Enforcement for the Required Child 227
Column Properties for the View Ridge Database Design Tables 228
Summary 230
Key Terms 231
Review Questions 231
Project Questions 233
Marcia's Dry Cleaning 234
Morgan Importing 234
PART 3&DATABASE IMPLEMENTATION 235
Chapter 7:SQL for Database ConstruCtion and Application Processing 236
Chapter Objectives 236
The View Ridge Gallery Database 237
SQL DDL,DML and a New Type of Join 237
Managing Table Structure with SQL DDL 238
Creating the View Ridge Database 238
Using the SQL CREATE TABLE Statement 239
Variations in SQL Data Types 239
Creating the ARTIST Table 240
Creating the WORK Table and the 1:N ARTIST-to-WORK Relationship 244
Implementing Required Parent Rows 245
Implementing 1:1 Relationships 245
Casual Relationships 245
Creating Default Values and Data Constraints with SQL 246
Creating the View Ridge Database Tables 248
The SQL ALTER Statement 251
The SQL DROP TABLE Statement 252
SQL DML Statements 252
The SQL INSERT Statement 252
Populating the View Ridge Database Tables 253
The SQL UPDATE Statement 259
The SQL DELETE Statement 260
New Forms of Join 260
The SQL JOIN ON Syntax 260
Outer Joins 262
Using SQL Views 266
Using SQL Views to Hide Columns and Rows 268
Using SQL Views to Display Results of Computed Columns 269
Using SQL Views to Hide Complicated SQL Syntax 270
Layering Built-in Functions 270
Using SQL Views for Isolation,Multiple Permissions,and Multiple Triggers 273
Updating SQL Views 273
Embedding SQL in Program Code 274
Using SQL Triggers 275
Using Triggers to Provide Default Values 276
Using Triggers to Enforce Data Constraints 278
Using Triggers to Update Views 279
Using Triggers to Implement Referential Integrity Actions 279
Using Stored Procedures 281
Advantages of Stored Procedures 281
The WORK_AddWorkTransaction Stored Procedure 283
Summary 283
Key Terms 285
Review Questions 286
Project Questions 290
Marcia's Dry Cleaning 293
Morgan Importing 294
Chapter 8:Database Redesign 295
Chapter Objectives 295
The Need for Database Redesign 296
SQL Statements for Checking Functional Dependencies 296
Correlated Subqueries 297
Analyzing the Existing Database 302
Reverse Engineering 302
Dependency Graphs 303
Database Backup and Test Databases 304
Changing Table Names and Table Columns 305
Changing Table Names 305
Adding and Dropping Columns 306
Changing a Column Data Type or Column Constraints 307
Adding and Dropping Constraints 308
Changing Relationship Cardinalities and Properties 308
Changing Minimum Cardinalities 308
Changing Maximum Cardinalities 309
Adding and Deleting Tables and Relationships 312
Forward Engineering(?) 312
Summary 313
Key Terms 314
Review Questions 314
Project Questions 316
Marcia's Dry Cleaning 317
Morgan Importing 318
PART 4&MULTIUSER DATABASE PROCESSING 319
Chapter 9:Managing Multiuser Databases 320
Chapter Objectives 320
Database Administration 321
Managing the Database Structure 322
Concurrency Control 323
The Need for Atomic Transactions 324
Resource Locking 328
Optimistic Versus Pessimistic Locking 330
Declaring Lock Characteristics 331
Consistent Transactions 332
Transaction Isolation Level 333
Cursor Type 334
Database Security 335
Processing Rights and Responsibilities 335
DBMS Security 337
DBMS Security Guidelines 337
Application Security 339
SQL Injection Attack 340
Database Backup and Recovery 340
Recoveryvia Reprocessing 341
Recoveryvia Rollback/Rollforward 341
Managing the DMBS 343
Maintaining the Data Repository 344
Summary 345
Key Terms 346
Review Questions 347
Project Questions 348
Marcia's Dry Cleaning 349
Morgan Importing 350
Chapter 10:Managing Databases with SQL Server 2008 351
Chapter Objectives 351
Installing SQL Server 2008 352
The Microsoft SQL Server 2008 Management Studio 353
Creating an SQL Server 2008 Database 354
SQL Server 2008 Utilities 356
SQL CMD and Microsoft PowerShell 356
Microsoft SQL CLR 356
SQL Server 2008 GUI Displays 357
SQL Server 2008 SQL Statements and SQL Scripts 357
Creating and Populating the View Ridge Database Tables 360
Creating the View Ridge Database Table Structure 360
Reviewing Database Structures in the SQL Server GUI Display 364
Indexes 368
Populating the VRG Tables with Data 369
Creating Views 379
SQL Server 2008 Application Logic 381
Transact-SQL 382
Stored Procedures 385
Triggers 393
Concurrency Control 407
Transaction Isolation Level 408
Cursor Concurrency 408
Locking Hints 409
SQL Server 2008 Security 410
SQL Server Database Security Settings 412
SQL Server 2008 Backup and Recovery 414
Backing Up a Database 414
SQL Server Recovery Models 415
Restoring a Database 416
Database Maintenance Plans 416
Topics Not Discussed in This Chapter 417
Summary 417
Key Terms 417
Review Questions 418
Project Questions 420
Marcia's Dry Cleaning 421
MorganImporting 421
PART 5&DATABASE ACCESS STANDARDS 427
Chapter 11:The Web Server Environment 428
Chapter Objectives 428
The Web Database Processing Environment 429
The ODBC Standard 431
ODBC Architecture 431
Conformance Levels 432
Creating an ODBC Data Source Name 434
The Mircosoft.NET Framework and ADO.NET 437
OLE DB 438
ADO and ADO.NET 442
The ADO.NET Object Model 442
The JAVA Platform 446
JDBC 446
Java Server Pages(JSP)and Servlets 448
Apache Tomcat 449
Web Database Processing with PHP 449
Web Database Processing with PHP and Eclipse 451
Getting Started with HTML Web Pages 453
The index.html Web Page 453
Creating the index.html Web Page 453
Using PHP 456
Challenges for Web Database Processing 463
Web Page Examples with PHP 463
Example 1:Updating a Table 464
Example 2:Using PHP Data Objects(PDO) 470
Example 3:Invoking a Stored Procedure 470
Summmary 478
Key Terms 480
Review Questions 481
Project Questions 483
Marcia's Dry Cleaning 485
Morgan Importing 485
Chapter 12:Database Processing with XML 486
Chapter Objectives 486
The Importance of XML 487
XML as a Markup Language 488
XML Document Type Declarations 489
Materializing XML Documents with XSLT 489
XML Schema 494
XML Schema Validation 494
Elements and Attributes 495
Flat Versus Structured Schemas 496
Global Elements 500
Creating XML Documents from Database Data 502
Using the SQLSELECT...FOR XML Statement 502
Multitable SELECT with FOR XML 506
An XML Schema for All CUSTOMER Purchases 510
A Schema with Two Multivalue Paths 514
Why Is XML Important? 514
Additional XML Standards 520
Summary 522
Key Terms 523
Review Questions 523
Project Questions 524
Marcia's Dry Cleaning 525
Morgan Importing 525
Chapter 13:Database Processing for Business Intelligence Systems 526
Chapter Objectives 526
Business Intelligence Systems 526
The Relationship Between Operational and BI Systems 526
Reporting Systems and Data Mining Applications 527
Reporting Systems 528
Data Mining Applications 528
Data Warehouses and Data Marts 528
Components of a Data Warehouse 528
Data Warehouses Versus Data Marts 531
Dimensional Databases 532
Reporting Systems 539
RFM Analysis 540
Producing the RFM Report 541
Reporting System Components 544
Report Types 545
Report Media 545
Report Modes 546
Report System Functions 546
OLAP 549
Data Mining 552
Unsupervised Data Mining 554
Supervised Data Mining 555
Three Popular Data Mining Techniques 555
Market Basket Analysis 555
Using SQL for Market Basket Analysis 555
Summary 557
Key Terms 558
Review Questions 559
Project Questions 561
Marcia's Dry Cleaning 563
Morgan Importing 564
Bibliography 567
Glossary 569
Index 591