Fundamentals of Database Management Systems

Preface xiii About The Author xvii CHAPTER 1 DATA: THE NEW CORPORATE RESOURCE 1 Introduction 2 The History of Data 2 The Origins of Data 2 Data Through the Ages 5 Early Data Problems Spawn Calculating Devices 7 Swamped with Data 8 Modern Data Storage Media 9 Data in Today's Information Systems Environment 12 Using Data for Competitive Advantage 12 Problems in Storing and Accessing Data 12 Data as a Corporate Resource 13 The Database Environment 14 Summary 15 CHAPTER 2 DATA MODELING 19 Introduction 20 Binary Relationships 20 What is a Binary Relationship? 20 Cardinality 23 Modality 24 More About Many-to-Many Relationships 25 Unary Relationships 28 One-to-One Unary Relationship 28 One-to-Many Unary Relationship 29 Many-to-Many Unary Relationship 29 Ternary Relationships 31 Example: The General Hardware Company 31 Example: Good Reading Book Stores 34 Example: World Music Association 35 Example: Lucky Rent-A-Car 36 Summary 37 CHAPTER 3 THE DATABASE MANAGEMENT SYSTEM CONCEPT 41 Introduction 42 Data Before Database Management 43 Records and Files 43 Basic Concepts in Storing and Retrieving Data 46 The Database Concept 48 Data as a Manageable Resource 48 Data Integration and Data Redundancy 49 Multiple Relationships 56 Data Control Issues 58 Data Independence 60 DBMS Approaches 60 Summary 63 CHAPTER 4 RELATIONAL DATA RETRIEVAL: SQL 67 Introduction 68 Data Retrieval with the SQL SELECT Command 68 Introduction to the SQL SELECT Command 68 Basic Functions 70 Built-In Functions 81 Grouping Rows 83 The Join 85 Subqueries 86 A Strategy for Writing SQL SELECT Commands 89 Example: Good Reading Book Stores 90 Example: World Music Association 92 Example: Lucky Rent-A-Car 95 Relational Query Optimizer 97 Relational DBMS Performance 97 Relational Query Optimizer Concepts 97 Summary 99 CHAPTER 5 THE RELATIONAL DATABASE MODEL: INTRODUCTION 105 Introduction 106 The Relational Database Concept 106 Relational Terminology 106 Primary and Candidate Keys 109 Foreign Keys and Binary Relationships 111 Data Retrieval from a Relational Database 124 Extracting Data from a Relation 124 The Relational Select Operator 125 The Relational Project Operator 125 Combination of the Relational Select and Project Operators 126 Extracting Data Across Multiple Relations: Data Integration 127 Example: Good Reading Book Stores 129 Example: World Music Association 130 Example: Lucky Rent-A-Car 132 Summary 132 CHAPTER 6 THE RELATIONAL DATABASE MODEL: ADDITIONAL CONCEPTS 137 Introduction 138 Relational Structures for Unary and Ternary Relationships 139 Unary One-to-Many Relationships 139 Unary Many-to-Many Relationships 143 Ternary Relationships 146 Referential Integrity 150 The Referential Integrity Concept 150 Three Delete Rules 152 Summary 153 CHAPTER 7 LOGICAL DATABASE DESIGN 157 Introduction 158 Converting E-R Diagrams into Relational Tables 158 Introduction 158 Converting a Simple Entity 158 Converting Entities in Binary Relationships 160 Converting Entities in Unary Relationships 164 Converting Entities in Ternary Relationships 166 Designing the General Hardware Co. Database 166 Designing the Good Reading Bookstores Database 170 Designing the World Music Association Database 171 Designing the Lucky Rent-A-Car Database 173 The Data Normalization Process 174 Introduction to the Data Normalization Technique 175 Steps in the Data Normalization Process 177 Example: General Hardware Co. 185 Example: Good Reading Bookstores 186 Example: World Music Association 188 Example: Lucky Rent-A-Car 188 Testing Tables Converted from E-R Diagrams with Data Normalization 189 Building the Data Structure with SQL 191 Manipulating the Data with SQL 192 Summary 193 CHAPTER 8 PHYSICAL DATABASE DESIGN 199 Introduction 200 Disk Storage 202 The Need for Disk Storage 202 How Disk Storage Works 203 File Organizations and Access Methods 207 The Goal: Locating a Record 207 The Index 207 Hashed Files 215 Inputs to Physical Database Design 218 The Tables Produced by the Logical Database Design Process 219 Business Environment Requirements 219 Data Characteristics 219 Application Characteristics 220 Operational Requirements: Data Security, Backup, and Recovery 220 Physical Database Design Techniques 221 Adding External Features 221 Reorganizing Stored Data 224 Splitting a Table into Multiple Tables 226 Changing Attributes in a Table 227 Adding Attributes to a Table 228 Combining Tables 230 Adding New Tables 232 Example: Good Reading Book Stores 233 Example: World Music Association 234 Example: Lucky Rent-A-Car 235 Summary 237 CHAPTER 9 OBJECT-ORIENTED DATABASE MANAGEMENT 247 Introduction 248 Terminology 250 Complex Relationships 251 Generalization 251 Inheritance of Attributes 253 Operations, Inheritance of Operations, and Polymorphism 254 Aggregation 255 The General Hardware Co. Class Diagram 256 The Good Reading Bookstores Class Diagram 256 The World Music Association Class Diagram 259 The Lucky Rent-A-Vehicle Class Diagram 260 Encapsulation 260 Abstract Data Types 262 Object/Relational Database 263 Summary 264 CHAPTER 10 DATA ADMINISTRATION, DATABASE ADMINISTRATION, AND DATA DICTIONARIES 269 Introduction 270 The Advantages of Data and Database Administration 271 Data as a Shared Corporate Resource 271 Efficiency in Job Specialization 272 Operational Management of Data 273 Managing Externally Acquired Databases 273 Managing Data in the Decentralized Environment 274 The Responsibilities of Data Administration 274 Data Coordination 274 Data Planning 275 Data Standards 275 Liaison to Systems Analysts and Programmers 276 Training 276 Arbitration of Disputes and Usage Authorization 277 Documentation and Publicity 277 Data's Competitive Advantage 277 The Responsibilities of Database Administration 278 DBMS Performance Monitoring 278 DBMS Troubleshooting 278 DBMS Usage and Security Monitoring 279 Data Dictionary Operations 279 DBMS Data and Software Maintenance 280 Database Design 280 Data Dictionaries 281 Introduction 281 A Simple Example of Metadata 282 Passive and Active Data Dictionaries 284 Relational DBMS Catalogs 287 Data Repositories 287 Summary 287 CHAPTER 11 DATABASE CONTROL ISSUES: SECURITY, BACKUP AND RECOVERY, CONCURRENCY 291 Introduction 292 Data Security 293 The Importance of Data Security 293 Types of Data Security Breaches 294 Methods of Breaching Data Security 294 Types of Data Security Measures 296 Backup and Recovery 303 The Importance of Backup and Recovery 303 Backup Copies and Journals 303 Forward Recovery 304 Backward Recovery 305 Duplicate or ''Mirrored'' Databases 306 Disaster Recovery 306 Concurrency Control 308 The Importance of Concurrency Control 308 The Lost Update Problem 308 Locks and Deadlock 309 Versioning 310 Summary 311 CHAPTER 12 CLIENT/SERVER DATABASE AND DISTRIBUTED DATABASE 315 Introduction 316 Client/Server Databases 316 Distributed Database 321 The Distributed Database Concept 321 Concurrency Control in Distributed Databases 325 Distributed Joins 327 Partitioning or Fragmentation 329 Distributed Directory Management 330 Distributed DBMSs: Advantages and Disadvantages 331 Summary 332 CHAPTER 13 THE DATA WAREHOUSE 335 Introduction 336 The Data Warehouse Concept 338 The Data is Subject Oriented 338 The Data is Integrated 339 The Data is Non-Volatile 339 The Data is Time Variant 339 The Data Must Be High Quality 340 The Data May Be Aggregated 340 The Data is Often Denormalized 340 The Data is Not Necessarily Absolutely Current 341 Types of Data Warehouses 341 The Enterprise Data Warehouse (EDW) 342 The Data Mart (DM) 342 Which to Choose: The EDW, the DM, or Both? 342 Designing a Data Warehouse 343 Introduction 343 General Hardware Co. Data Warehouse 344 Good Reading Bookstores Data Warehouse 348 Lucky Rent-A-Car Data Warehouse 350 What About a World Music Association Data Warehouse? 351 Building a Data Warehouse 352 Introduction 352 Data Extraction 352 Data Cleaning 354 Data Transformation 356 Data Loading 356 Using a Data Warehouse 357 On-Line Analytic Processing 357 Data Mining 357 Administering a Data Warehouse 360 Challenges in Data Warehousing 361 Summary 362 CHAPTER 14 DATABASES AND THE INTERNET 365 Introduction 366 Database Connectivity Issues 367 Expanded Set of Data Types 373 Database Control Issues 374 Performance 374 Availability 375 Scalability 376 Security and Privacy 376 Data Extraction into XML 379 Summary 381 INDEX 385