Wiley Pathways Introduction to Database Management

Dr. Mark L. Gillenson has been practicing, researching, teaching, writing, and, most importantly, thinking, about data and database management for over twenty-five years, split between working for the IBM Corporation and being a professor in the academic world. While working for IBM, he designed databases for IBM's corporate headquarters, consulted on database issues for some of IBM's largest customers, taught database management at the prestigious IBM Systems Research Institute in New York, and conducted database seminars throughout the United States and on four continents. In one such seminar, he taught introduction to database to an IBM development group that went on to develop one of IBM's first relational database management system products, SQL/DS. Paulraj Ponniah, PhD, a veteran IT professional, specializes in the design and implementation of database and data warehouse systems, as well as in teaching database and data warehouse courses. Frank Miller has nearly 30 years experience designing, developing, and delivering technical training materials. For 20 of those years, SQL language database management systems have been an area of special interest and expertise. During that time he has written books and other training materials that are used by various schools and professional organizations in the United States and around the world.

• 1 Introducing to Data and Data Management 1Introduction 21.1 Understanding the Role of Data and Databases 21.1.1 A Practical Example 31.1.2 Understanding Data Management51.1.3 The Need for Data Management 5Self-Check 71.2 Understanding Data Sources71.2.1 Picking a Starting Point 71.2.2 Identifying Primary Processes 81.2.3 Specific Data Sources 9Self-Check 121.3 Potential Data Concerns 121.3.1 Managing Data Accuracy 131.3.2 Managing Data Security 131.3.3 Managing Data Organization 161.3.4 Managing Data Access 16Self-Check 18Summary 19Key Terms 19Assess Your Understanding 20Summary Questions 20Applying This Chapter 22You Try It 232 Introducing Databases and Database Management Systems 24Introduction 252.1Introduction to Key Database Concepts 252.1.1Database Approach to Data 252.1.2Understanding Basic Concepts 262.1.3Database Use 29Self-Check312.2 Understanding Basic Database Models 312.2.1 The Hierarchical Database Model 322.2.2 The Network Database Model 332.2.3 The Relational Database Model 342.2.4 The Object-Oriented Database Model 352.2.5 The Object-Relational Database Model 36Self-Check 372.3 Database Components 382.3.1 Hardware Components 402.3.2 Software Requirements 432.3.3 DBMS Components452.3.4 Understanding People and Procedures 50Self-Check 53Summary 54Key Terms 54Assess Your Understanding 55Summary Questions 55Applying This Chapter 57You Try It 593 Data Modeling 60Introduction 613.1 Understanding Database Design 613.1.1 Understanding the Design Process 613.1.2 Determining the Database Type 633.1.3 Understanding Modeling Goals 663.1.4 Understanding Business Rules 67Self-Check 703.2 Understanding Relational Database Models 703.2.1 Entity-Relationship (E-R) Modeling Concepts 713.2.2 Introducing Basic Database Objects 75Self-Check 793.3 Understanding Relationships 793.3.1 Binary Relationships 803.3.2 Unary Relationships 843.3.3 Ternary Relationships 863.3.4 Breaking Down Many-to-Many Relationships 87Self-Check913.4 Comparing Data Models 913.4.1 Choosing a Modeling Tool 923.4.2 The General Hardware Company 933.4.3 Good Reading Bookstores 95Self-Check 97Summary 97Key Terms 97Assess Your Understanding 99Summary Questions 99Applying This Chapter 101You Try It1034 Designing a Database 104Introduction 1054.1Designing Relational Tables 1054.1.1Converting a Single Entity 1054.1.2Converting Binary Relationships 1064.1.3Converting Unary Relationships 113Self-Check 1174.2 Comparing Relational Designs 1174.2.1 Designing General Hardware 1174.2.2 Designing Good Reading Bookstores 120Self-Check 1234.3 Normalizing Data 1234.3.1 Using Normalization Techniques 1234.3.2 Normalizing Data by the Numbers 1254.3.3 Shortening the Process 1344.3.4 Denormalizing Data 134Self-Check 136Summary 136Key Terms 137Assess Your Understanding 138Summary Questions 138Applying This Chapter 140You Try It 1425 Implementing a Database 146Introduction 1475.1 Physical Design and Implementation 1475.1.1 Understanding Design Requirements 1475.1.2 Business Environment Requirements 1495.1.3 Data Characteristics 1495.1.4 Application Characteristics 1515.1.5 Operational Requirements 1525.1.6 The Hardware and Software Environment 1525.1.7 Evaluating Implementation Options 154Self-Check 1585.2 Adjusting Your Design to the Real World 1585.2.1 Ensuring Data Integrity 1595.2.2 Adjusting Factors Related to Performance 162Self-Check 1715.3 Implementing Database Objects 1715.3.1 Implementing Your Final Table Design 1715.3.2 Implementing Indexes 1735.3.3 Implementing Views 175Self-Check 177Summary 177Key Terms 177Assess Your Understanding 178Summary Questions 178Applying This Chapter 180You Try It1826 Understanding the SQL Language 184Introduction 1856.1 Introducing the SQL Language 1856.1.1 Understanding SQL Features 1856.1.2 Using SQL 1866.1.3 Understanding Command Basics 190Self-Check 1926.2 Understanding SELECT Fundamentals 1926.2.1 Working with SELECT 1926.2.2 Using Simple Data Retrieval 1936.2.3 Retrieving Other Values 194Self-Check 1966.3 Understanding Operators and Functions 1966.3.1 Arithmetic Operators 1966.3.2 Comparison and Logical Operators 1976.3.3 Standard SQL Functions 2016.3.4 Function Variations 202Self-Check 2086.4 Understanding DML Commands 2086.4.1 Using INSERT 2086.4.2 Using UPDATE 2096.4.3 Using DELETE 211Self-Check2136.5 Understanding DDL Commands 2136.5.1 Using CREATE 2146.5.2 Using ALTER 2166.5.3 Using DROP 216Self-Check 216Summary 217Key Terms 217Assess Your Understanding 218Summary Questions 218Applying This Chapter 220You Try It2227 Data Access and Manipulation 223Introduction2247.1 Using SELECT Statement Advanced Syntax 2247.1.1 Understanding SELECT Statement Syntax 2247.1.2 Filtering Your Result 2267.1.3 Managing Your Result Set 2297.1.4 Sorting, Organizing, and Grouping Data 2317.1.5 Understanding Operator Precedence 2377.1.6 Combining Statement Results 2397.1.7 Using SELECT with Other Commands 240Self-Check 2427.2 Using Joins and Subqueries 2427.2.1 Understanding Joins 2427.2.2 Using Different Join Syntaxes 2447.2.3 Using Basic Subqueries 246Self-Check 2507.3 Using Batches and Scripts 2507.3.1 Writing Batches and Scripts 2517.3.2 Understanding Basic Programming Concepts 252Self-Check 256Summary 256Key Terms 256Assess Your Understanding 257Summary Questions 257Applying This Chapter 259You Try It2628 Improving Data Access 263Introduction 2648.1 Understanding Performance Roadblocks 2648.1.1 Recognizing Potential Bottlenecks 2658.1.2 Understanding Hardware Performance 2658.1.3 Understanding Database Performance 2708.1.4 Performance Monitoring 2708.1.5 Knowing What to Use 274Self-Check 2758.2 Using Indexes and Views 2758.2.1 Working with Indexes 2768.2.2 Working with Views 280Self-Check 2838.3Using Programmable Objects 2848.3.1Understanding Procedures 2848.3.2Understanding Functions 287Self-Check 292Summary 292Key Terms 292Assess Your Understanding 294Summary Questions 294Applying This Chapter 296You Try It2989 Database Administration 300Introduction 3019.1 Understanding the Need for Administration 3019.1.1 Identifying Administration Roles 3029.1.2 Justifying the Need for Administration 302Self-Check 3069.2 Identifying Administration Responsibilities 3079.2.1 Understanding Data Administration Responsibilities 3089.2.2 Understanding Database Administration Responsibilities 313Self-Check 3189.3 Understanding Management Tasks 3189.3.1 Considering “What” and “When” 3199.3.2 Considering “When” and “How” 3199.3.3 Ongoing Management Tasks 3219.3.4 Considering Troubleshooting 324Self-Check 326Summary 326Key Terms 326Assess Your Understanding 327Summary Questions 327Applying This Chapter 329You Try It 33010 Transactions and Locking 332Introduction33310.1 Understanding Transaction Basics 33310.1.1 Understanding Transaction Processing 33310.1.2 Using Transaction Commands 33610.1.3 Understanding Transaction Properties 34110.1.4 Understanding Transaction Scope 34110.1.5 Recognizing and Resolving Potential Problems 345Self-Check 34710.2 Managing Concurrency Control 34710.2.1 The Need for Concurrency Management 34810.2.2 Recognizing Concurrency Problems 34810.2.3 Designing for Concurrency 35210.2.4 Concurrency Methods 352Self-Check 35510.3 SQL Server Transaction Management 35510.3.1 Understanding Transaction Processing 35510.3.2 Managing Locks, Locking, and Transaction Isolation 35610.3.3 Recognizing, Clearing, and Preventing Deadlocks 358Self-Check 362Summary 362Key Terms 363Assess Your Understanding 364Summary Questions 364Applying This Chapter 366You Try It 36711 Data Access and Security 368Introduction36911.1 Understanding Database Connections 36911.1.1 Understanding Connectivity Concepts 36911.1.2 Understanding Client/Server Connectivity 37611.1.3 Understanding Multitier Connectivity 379Self-Check 38211.2 Managing Access Control 38211.2.1 Controlling Server Access 38311.2.2 Controlling Database Access 38611.2.3 Understanding the Connection Process 387Self-Check 39211.3 Protecting Your Data 39211.3.1 Implementing Data Permissions 39311.3.2 Minimizing Table Access 39711.3.3 Keeping Data Safe 39711.3.4 Understanding RAID Configurations 39711.3.5 Using Data Backups 40011.3.6 Protecting Your Server 404Self-Check 405Summary 406Key Terms 406Assess Your Understanding 407Summary Questions 407Applying This Chapter 409You Try It 41112 Supporting Database Applications 412Introduction 41312.1 Supporting a Centralized Database 41312.1.1 Understanding Local Area Networks (LANs) 41312.1.2 Understanding Data Configurations 41412.1.3 Understanding Server Configurations 41612.1.4 Consolidating Data Sources 417Self-Check 41912.2 Supporting a Distributed Database 41912.2.1 Understanding Distributed Data 41912.2.2 Understanding Replicated Data 42312.2.3 Understanding Partitioned Data 42512.2.4 Distributed Data Support Issues 429Self-Check 43412.3 Understanding Internet Issues 43412.3.1 Managing Performance Issues 43512.3.2 Managing Availability Issues 43612.3.3 Managing Security and Privacy Issues 438Self-Check 441Summary 442Key Terms 442Assess Your Understanding 443Summary Questions 443Applying This Chapter 445You Try It 447Glossary 448Index 465