Understanding Databases
Concepts and Practice

Understanding Databases: Concepts and Practice is an accessible, highly visual introduction to database systems for undergraduate students across many majors. Designed for self-contained first courses in the subject, this interactive e-textbook covers fundamental database topics including conceptual design, the relational data model, relational algebra and calculus, Structured Query Language (SQL), database manipulation, transaction management, and database design theory.

Visual components and self-assessment features provide a more engaging and immersive method of learning that enables students to develop a solid foundation in both database theory and practical application. Concise, easy-to-digest chapters offer ample opportunities for students to practice and master the material, and include a variety of solved real-world problems, self-check questions, and hands-on collaborative activities that task students to build a functioning database. This Enhanced eText also offers interactive multiple-choice questions with immediate feedback that allow students to self-assess as they proceed through the book. Case studies, illustrative examples, color summary figures and tables with annotations, and other pedagogical tools are integrated throughout the text to increase comprehension and retention of key concepts and help strengthen students? problem-solving skills.

Preface xi

1 Introduction to Databases and the Relational Data Model 1

1.1 Databases are a Tool 2

1.2 Overview of Data and Models 6

1.3 The Relational Data Model 14

1.3.1 Definition 14

1.3.2 Uniqueness 16

1.3.3 Referential Integrity 20

Special Topic: Primary Key and Referential Integrity 26

1.3.4 Additional Constraints 27

2 Conceptual Design 43

2.1 Gathering Requirements 44

2.2 Entity-Relationship Diagrams 46

How To: Design an Entity-Relationship Diagram 50

Special Topic: (Min, Max) Pairs 54

Special Topic: Recursive Relationships and Role Names 54

Special Topic: Ternary Relationships 56

Special Topic: EER for Modeling Inheritance 56

2.3 Mapping ER Diagrams to Tables 62

How To: Map an ER Diagram to Relations 67

2.4 Other Graphical Approaches 73

3 Relational Algebra 103

3.1 Query Design 104

How To: Query Design 104

3.2 Algebra Operators 109

Note: Overview of Relational Algebra 110

3.2.1 Filtering 111

3.2.2 Sets 114

3.2.3 Joins 116

3.2.4 Division 119

3.3 Relational Completeness 127

3.4 Query Optimization 134

How To: Heuristic Query Optimization 136

4 Relational Calculus 161

4.1 Logical Foundations 162

Note: Overview of Relational Calculus Languages 163

4.2 Tuple Relational Calculus 164

4.2.1 Fundamental Query Expressions 165

How To: Writing a Fundamental Query in TRC 165

vi

4.2.2 Quantification of Variables 167

4.2.3 Atoms and Formula 172

4.2.4 Relational Completeness 175

4.3 Domain Relational Calculus 183

4.3.1 Fundamental Query Expressions 183

How To: Writing a Fundamental Query in DRC 184

4.3.2 Quantification of Variables 187

4.3.3 Atoms and Formula 193

4.3.4 Relational Completeness 195

4.4 Safety 204

5 SQL: An Introduction to Querying 237

5.1 Foundations 237

Note: SQL Syntax 238

Syntax: Basic SQL Query 240

5.2 Fundamental Query Expressions 246

How To: Writing a Fundamental Query in SQL 246

5.2.1 Queries involving One Table 247

5.2.2 Queries involving Multiple Tables 250

How To: Writing a Reflection Query 255

5.3 Nested Queries 261

Special Topic: A glimpse at query optimization 264

Special Topic: Views and Inline Views 266

5.4 Set Operators 270

5.5 Aggregation and Grouping 276

Special Topic: Arithmetic Expressions 281

5.6 Querying with null Values 285

5.7 Relational Completeness 289

5.7.1 Fundamental Operators 290

5.7.2 Additional Operators 292

6 SQL: Beyond the Query Language 329

6.1 Data Definition 329

Syntax: Create Table Statement 331

Syntax: Drop Table Statement 336

Syntax: Alter Table Statement 337

Special Topic: Create Index 338

Syntax: Create View Statement 339

6.2 Data Manipulation 342

Syntax: Insert Into Statement 343

Special Topic: Database Population 346

Syntax: Update Statement 347

Syntax: Delete Statement 349

6.3 Database User Privileges 352

Syntax: Grant Statement 354

Syntax: Revoke Statement 355

7 Database Programming 371

7.1 Persistent Stored Modules 372

Syntax: Create Procedure Statement 374

Syntax: Create Function Statement 376

7.2 Overview of Call-Level Interface 382

7.3 Java and JDBC 385

7.4 Python and DB-API 393

8 XML and Databases 431

8.1 Overview of XML 432

8.2 DTD 439

Syntax: DTD Overview 440

8.3 XML Schema 448

Syntax: XSD Overview of Element and Attribute Declarations 450

Syntax: XSD Attribute Declarations: use, default, fixed 460

8.4 Structuring XML for Data Exchange 467

9 Transaction Management 491

9.1 ACID Properties of a Transaction 492

9.2 Recovery control 498

How To: Recovery Control: UNDO and REDO 501

9.3 Concurrency control 504

9.3.1 Serializability 507

How To: Create a Precedence Graph 508

9.3.2 Locking 512

9.3.3 Timestamps 520

Algorithm: Basic Timestamp Protocol 521

10 More on Database Design 543

10.1 Database Design Goals 544

10.2 Functional Dependencies 546

Algorithm: Attribute Closure 550

Special Topic: Minimal Set of Functional Dependencies 552

How To: Heuristic Determination of a Candidate Key 552

10.3 Decomposition 558

How To: Determine Breakdown of F for a Decomposition 559

How To: Determine Lossless Pairwise Decomposition 562

Algorithm: Lossless-Join Property for Database Schema 565

10.4 Normal Forms 571

How To: Determine the Normal Form of a Relation 574

Algorithm: BCNF Decomposition Algorithm 575

A WinRDBI 599

A.1 Overview 599

A.2 Query Languages 600

A.3 Implementation Overview 606

A.4 Summary 606