数据库处理 基础、设计与实现 英文版PDF电子书下载

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