Wiley Pathways Introduction to Database Management

You can get there Where do you want to go? You might already be working in the information technology field and may be looking to expand your skills. You might be setting out on a new career path. Or, you might want to learn more about exciting opportunities in database management. Wherever you want to go, Introduction to Databases will help you get there. Easy-to-read, practical, and up-to-date, this text not only helps you learn fundamental database design and management concepts, it also helps you master the core competencies and skills you need to succeed in the classroom and in the real world. The book's brief, modular format and variety of built-in learning resources enable you to learn at your own pace and focus your studies. With this book, you will be able to: * Appreciate the key role of data in daily business operations and strategic decisions. * Understand databases, database management systems, and SQL, the software on which they are based, from the ground up. * Know how to gather and organize critical business information, design a database based on this information, and retrieve and modify that information in a useful manner. * Use accepted data modeling procedures to design a relational database. * Master the concept of data normalization and the use of standard normalization rules. * Explore critical real-world issues including application integration and securing data against disclosure and loss. Wiley Pathways helps you achieve your goals Not every student is on the same path, but every student wants to succeed. The Information Technology series in the new Wiley Pathways imprint helps you achieve your goals. The books in this series--Introduction to Databases, Introduction to Programming Using Visual Basic, Introduction to Operating Systems, Networking Basics, Windows Network Administration, Network Security Fundamentals, and PC Hardware Essentials--offer a coordinated information technology curriculum. Learn more at www.wiley.com/go/pathways

Table of Contents:
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