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
