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.

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