Fundamentals of Database Management Systems

Mark L. Gillenson is Professor of Management Information Systems and Director of MBA Programs in the Fogelman College of Business & Economics of the University of Memphis in Memphis, TN. He received his B.S. degree in Mathematics from Rensselaer Polytechnic Institute, Troy, N.Y. and his M.S. and Ph.D. degrees in Computer and Information Science from The Ohio State University, Columbus, OH. Dr. Gillenson worked for the IBM Corp. for 15 years in a variety of positions, including seven years as a faculty member of the prestigious IBM Systems Research Institute. Subsequently, he was a professor at the University of Miami, Miami FL. Dr. Gillenson's areas of interest are database management systems, particularly database administration and database design, electronic commerce, and software testing. He is an associate editor of the Journal of Database Management and of the journal Pattern Recognition. Dr. Gillenson's research has appeared in MIS Quarterly, Communications of the ACM, Methods of Information in Medicine, and other leading journals. His books include Strategic Planning, Systems Analysis, and Database Design, 1984, Database: Step-by-Step, Second Edition, 1990, and Fundamentals of Database Management Systems, 2005, all published by John Wiley & Sons.

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