Wiley Pathways Introduction to Database Management

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