1 Introducing to Data and Data Management 1
Introduction 2
1.1 Understanding the Role of Data and Databases 2
1.1.1 A Practical Example 3
1.1.2 Understanding Data Management5
1.1.3 The Need for Data Management 5
Self-Check 7
1.2 Understanding Data Sources7
1.2.1 Picking a Starting Point 7
1.2.2 Identifying Primary Processes 8
1.2.3 Specific Data Sources 9
Self-Check 12
1.3 Potential Data Concerns 12
1.3.1 Managing Data Accuracy 13
1.3.2 Managing Data Security 13
1.3.3 Managing Data Organization 16
1.3.4 Managing Data Access 16
Self-Check 18
Summary 19
Key Terms 19
Assess Your Understanding 20
Summary Questions 20
Applying This Chapter 22
You Try It 23
2 Introducing Databases and Database Management Systems 24
Introduction 25
2.1Introduction to Key Database Concepts 25
2.1.1Database Approach to Data 25
2.1.2Understanding Basic Concepts 26
2.1.3Database Use 29
Self-Check31
2.2 Understanding Basic Database Models 31
2.2.1 The Hierarchical Database Model 32
2.2.2 The Network Database Model 33
2.2.3 The Relational Database Model 34
2.2.4 The Object-Oriented Database Model 35
2.2.5 The Object-Relational Database Model 36
Self-Check 37
2.3 Database Components 38
2.3.1 Hardware Components 40
2.3.2 Software Requirements 43
2.3.3 DBMS Components45
2.3.4 Understanding People and Procedures 50
Self-Check 53
Summary 54
Key Terms 54
Assess Your Understanding 55
Summary Questions 55
Applying This Chapter 57
You Try It 59
3 Data Modeling 60
Introduction 61
3.1 Understanding Database Design 61
3.1.1 Understanding the Design Process 61
3.1.2 Determining the Database Type 63
3.1.3 Understanding Modeling Goals 66
3.1.4 Understanding Business Rules 67
Self-Check 70
3.2 Understanding Relational Database Models 70
3.2.1 Entity-Relationship (E-R) Modeling Concepts 71
3.2.2 Introducing Basic Database Objects 75
Self-Check 79
3.3 Understanding Relationships 79
3.3.1 Binary Relationships 80
3.3.2 Unary Relationships 84
3.3.3 Ternary Relationships 86
3.3.4 Breaking Down Many-to-Many Relationships 87
Self-Check91
3.4 Comparing Data Models 91
3.4.1 Choosing a Modeling Tool 92
3.4.2 The General Hardware Company 93
3.4.3 Good Reading Bookstores 95
Self-Check 97
Summary 97
Key Terms 97
Assess Your Understanding 99
Summary Questions 99
Applying This Chapter 101
You Try It103
4 Designing a Database 104
Introduction 105
4.1Designing Relational Tables 105
4.1.1Converting a Single Entity 105
4.1.2Converting Binary Relationships 106
4.1.3Converting Unary Relationships 113
Self-Check 117
4.2 Comparing Relational Designs 117
4.2.1 Designing General Hardware 117
4.2.2 Designing Good Reading Bookstores 120
Self-Check 123
4.3 Normalizing Data 123
4.3.1 Using Normalization Techniques 123
4.3.2 Normalizing Data by the Numbers 125
4.3.3 Shortening the Process 134
4.3.4 Denormalizing Data 134
Self-Check 136
Summary 136
Key Terms 137
Assess Your Understanding 138
Summary Questions 138
Applying This Chapter 140
You Try It 142
5 Implementing a Database 146
Introduction 147
5.1 Physical Design and Implementation 147
5.1.1 Understanding Design Requirements 147
5.1.2 Business Environment Requirements 149
5.1.3 Data Characteristics 149
5.1.4 Application Characteristics 151
5.1.5 Operational Requirements 152
5.1.6 The Hardware and Software Environment 152
5.1.7 Evaluating Implementation Options 154
Self-Check 158
5.2 Adjusting Your Design to the Real World 158
5.2.1 Ensuring Data Integrity 159
5.2.2 Adjusting Factors Related to Performance 162
Self-Check 171
5.3 Implementing Database Objects 171
5.3.1 Implementing Your Final Table Design 171
5.3.2 Implementing Indexes 173
5.3.3 Implementing Views 175
Self-Check 177
Summary 177
Key Terms 177
Assess Your Understanding 178
Summary Questions 178
Applying This Chapter 180
You Try It182
6 Understanding the SQL Language 184
Introduction 185
6.1 Introducing the SQL Language 185
6.1.1 Understanding SQL Features 185
6.1.2 Using SQL 186
6.1.3 Understanding Command Basics 190
Self-Check 192
6.2 Understanding SELECT Fundamentals 192
6.2.1 Working with SELECT 192
6.2.2 Using Simple Data Retrieval 193
6.2.3 Retrieving Other Values 194
Self-Check 196
6.3 Understanding Operators and Functions 196
6.3.1 Arithmetic Operators 196
6.3.2 Comparison and Logical Operators 197
6.3.3 Standard SQL Functions 201
6.3.4 Function Variations 202
Self-Check 208
6.4 Understanding DML Commands 208
6.4.1 Using INSERT 208
6.4.2 Using UPDATE 209
6.4.3 Using DELETE 211
Self-Check213
6.5 Understanding DDL Commands 213
6.5.1 Using CREATE 214
6.5.2 Using ALTER 216
6.5.3 Using DROP 216
Self-Check 216
Summary 217
Key Terms 217
Assess Your Understanding 218
Summary Questions 218
Applying This Chapter 220
You Try It222
7 Data Access and Manipulation 223
Introduction224
7.1 Using SELECT Statement Advanced Syntax 224
7.1.1 Understanding SELECT Statement Syntax 224
7.1.2 Filtering Your Result 226
7.1.3 Managing Your Result Set 229
7.1.4 Sorting, Organizing, and Grouping Data 231
7.1.5 Understanding Operator Precedence 237
7.1.6 Combining Statement Results 239
7.1.7 Using SELECT with Other Commands 240
Self-Check 242
7.2 Using Joins and Subqueries 242
7.2.1 Understanding Joins 242
7.2.2 Using Different Join Syntaxes 244
7.2.3 Using Basic Subqueries 246
Self-Check 250
7.3 Using Batches and Scripts 250
7.3.1 Writing Batches and Scripts 251
7.3.2 Understanding Basic Programming Concepts 252
Self-Check 256
Summary 256
Key Terms 256
Assess Your Understanding 257
Summary Questions 257
Applying This Chapter 259
You Try It262
8 Improving Data Access 263
Introduction 264
8.1 Understanding Performance Roadblocks 264
8.1.1 Recognizing Potential Bottlenecks 265
8.1.2 Understanding Hardware Performance 265
8.1.3 Understanding Database Performance 270
8.1.4 Performance Monitoring 270
8.1.5 Knowing What to Use 274
Self-Check 275
8.2 Using Indexes and Views 275
8.2.1 Working with Indexes 276
8.2.2 Working with Views 280
Self-Check 283
8.3Using Programmable Objects 284
8.3.1Understanding Procedures 284
8.3.2Understanding Functions 287
Self-Check 292
Summary 292
Key Terms 292
Assess Your Understanding 294
Summary Questions 294
Applying This Chapter 296
You Try It298
9 Database Administration 300
Introduction 301
9.1 Understanding the Need for Administration 301
9.1.1 Identifying Administration Roles 302
9.1.2 Justifying the Need for Administration 302
Self-Check 306
9.2 Identifying Administration Responsibilities 307
9.2.1 Understanding Data Administration Responsibilities 308
9.2.2 Understanding Database Administration Responsibilities 313
Self-Check 318
9.3 Understanding Management Tasks 318
9.3.1 Considering “What” and “When” 319
9.3.2 Considering “When” and “How” 319
9.3.3 Ongoing Management Tasks 321
9.3.4 Considering Troubleshooting 324
Self-Check 326
Summary 326
Key Terms 326
Assess Your Understanding 327
Summary Questions 327
Applying This Chapter 329
You Try It 330
10 Transactions and Locking 332
Introduction333
10.1 Understanding Transaction Basics 333
10.1.1 Understanding Transaction Processing 333
10.1.2 Using Transaction Commands 336
10.1.3 Understanding Transaction Properties 341
10.1.4 Understanding Transaction Scope 341
10.1.5 Recognizing and Resolving Potential Problems 345
Self-Check 347
10.2 Managing Concurrency Control 347
10.2.1 The Need for Concurrency Management 348
10.2.2 Recognizing Concurrency Problems 348
10.2.3 Designing for Concurrency 352
10.2.4 Concurrency Methods 352
Self-Check 355
10.3 SQL Server Transaction Management 355
10.3.1 Understanding Transaction Processing 355
10.3.2 Managing Locks, Locking, and Transaction Isolation 356
10.3.3 Recognizing, Clearing, and Preventing Deadlocks 358
Self-Check 362
Summary 362
Key Terms 363
Assess Your Understanding 364
Summary Questions 364
Applying This Chapter 366
You Try It 367
11 Data Access and Security 368
Introduction369
11.1 Understanding Database Connections 369
11.1.1 Understanding Connectivity Concepts 369
11.1.2 Understanding Client/Server Connectivity 376
11.1.3 Understanding Multitier Connectivity 379
Self-Check 382
11.2 Managing Access Control 382
11.2.1 Controlling Server Access 383
11.2.2 Controlling Database Access 386
11.2.3 Understanding the Connection Process 387
Self-Check 392
11.3 Protecting Your Data 392
11.3.1 Implementing Data Permissions 393
11.3.2 Minimizing Table Access 397
11.3.3 Keeping Data Safe 397
11.3.4 Understanding RAID Configurations 397
11.3.5 Using Data Backups 400
11.3.6 Protecting Your Server 404
Self-Check 405
Summary 406
Key Terms 406
Assess Your Understanding 407
Summary Questions 407
Applying This Chapter 409
You Try It 411
12 Supporting Database Applications 412
Introduction 413
12.1 Supporting a Centralized Database 413
12.1.1 Understanding Local Area Networks (LANs) 413
12.1.2 Understanding Data Configurations 414
12.1.3 Understanding Server Configurations 416
12.1.4 Consolidating Data Sources 417
Self-Check 419
12.2 Supporting a Distributed Database 419
12.2.1 Understanding Distributed Data 419
12.2.2 Understanding Replicated Data 423
12.2.3 Understanding Partitioned Data 425
12.2.4 Distributed Data Support Issues 429
Self-Check 434
12.3 Understanding Internet Issues 434
12.3.1 Managing Performance Issues 435
12.3.2 Managing Availability Issues 436
12.3.3 Managing Security and Privacy Issues 438
Self-Check 441
Summary 442
Key Terms 442
Assess Your Understanding 443
Summary Questions 443
Applying This Chapter 445
You Try It 447
Glossary 448
Index 465
