Foreword to the 40th Anniversary Edition |
|
xvii | |
Preface |
|
xxv | |
Part 1 Getting Started |
|
1 | (144) |
|
|
2 | (36) |
|
|
2 | (1) |
|
The Importance of Databases in the Internet and Smartphone World |
|
|
3 | (2) |
|
The Characteristics of Databases |
|
|
5 | (5) |
|
A Note on Naming Conventions |
|
|
7 | (1) |
|
A Database Has Data and Relationships |
|
|
7 | (2) |
|
Databases Create Information |
|
|
9 | (1) |
|
|
10 | (1) |
|
Single-User Database Applications |
|
|
10 | (1) |
|
Multiuser Database Applications |
|
|
10 | (1) |
|
E-Commerce Database Applications |
|
|
11 | (1) |
|
Reporting and Data Mining Database Applications |
|
|
11 | (1) |
|
The Components of a Database System |
|
|
11 | (7) |
|
Database Applications and SQL |
|
|
12 | (3) |
|
|
15 | (1) |
|
|
16 | (2) |
|
Personal Versus Enterprise-Class Database Systems |
|
|
18 | (3) |
|
What Is Microsoft Access? |
|
|
18 | (1) |
|
What Is an Enterprise-Class Database System? |
|
|
19 | (2) |
|
|
21 | (3) |
|
Database Design from Existing Data |
|
|
21 | (2) |
|
Database Design for New Systems Development |
|
|
23 | (1) |
|
|
23 | (1) |
|
|
24 | (1) |
|
A Brief History of Database Processing |
|
|
25 | (5) |
|
|
25 | (2) |
|
The Emergence and Dominance of the Relational Model |
|
|
27 | (1) |
|
Postrelational Developments |
|
|
28 | (2) |
|
|
30 | (1) |
|
|
31 | (1) |
|
|
32 | (2) |
|
|
34 | (4) |
|
Chapter 2 Introduction to Structured Query Language |
|
|
38 | (107) |
|
|
38 | (1) |
|
|
39 | (1) |
|
Business Intelligence Systems and Data Warehouses |
|
|
40 | (7) |
|
The Cape Codd Outdoor Sports Extracted Retail Sales Data Database |
|
|
41 | (3) |
|
|
44 | (1) |
|
|
44 | (1) |
|
|
45 | (1) |
|
|
45 | (1) |
|
The CATALOG_SKU_20## Tables |
|
|
46 | (1) |
|
The Complete Cape Codd Data Extract Schema |
|
|
46 | (1) |
|
|
47 | (1) |
|
|
47 | (2) |
|
The SQL SELECT/FROM/WHERE Framework |
|
|
49 | (3) |
|
Reading Specified Columns from a Single Table |
|
|
49 | (2) |
|
Specifying Column Order in SQL Queries from a Single Table |
|
|
51 | (1) |
|
Submitting SQL Statements to the DBMS |
|
|
52 | (14) |
|
Using SQL in Microsoft Access 2016 |
|
|
52 | (6) |
|
Using SQL in Microsoft SQL Server 2017 |
|
|
58 | (3) |
|
Using SQL in Oracle Database |
|
|
61 | (2) |
|
Using SQL in Oracle MySQL 5.7 |
|
|
63 | (3) |
|
SQL Enhancements for Querying a Single Table |
|
|
66 | (14) |
|
Reading Specified Rows from a Single Table |
|
|
66 | (4) |
|
Reading Specified Columns and Rows from a Single Table |
|
|
70 | (1) |
|
Sorting the SQL Query Results |
|
|
70 | (3) |
|
|
73 | (7) |
|
Performing Calculations in SQL Queries |
|
|
80 | (8) |
|
Using SQL Built-in Aggregate Functions |
|
|
81 | (4) |
|
SQL Expressions in SQL SELECT Statements |
|
|
85 | (3) |
|
Grouping Rows in SQL SELECT Statements |
|
|
88 | (5) |
|
Querying Two or More Tables with SQL |
|
|
93 | (22) |
|
Querying Multiple Tables with Subqueries |
|
|
93 | (3) |
|
Querying Multiple Tables with Joins |
|
|
96 | (6) |
|
Comparing Subqueries and Joins |
|
|
102 | (1) |
|
|
102 | (4) |
|
SQL Queries on Recursive Relationships |
|
|
106 | (1) |
|
|
107 | (4) |
|
|
111 | (4) |
|
|
115 | (1) |
|
|
116 | (1) |
|
|
117 | (7) |
|
|
124 | (5) |
|
|
129 | (4) |
|
The Queen Anne Curiosity Shop Project Questions |
|
|
133 | (7) |
|
Morgan Importing Project Questions |
|
|
140 | (5) |
Part 2 Database Design |
|
145 | (178) |
|
Chapter 3 The Relational Model and Normalization |
|
|
146 | (45) |
|
|
146 | (2) |
|
Relational Model Terminology |
|
|
148 | (13) |
|
|
148 | (1) |
|
Characteristics of Relations |
|
|
149 | (2) |
|
|
151 | (1) |
|
To Key, or Not to Key-That Is the Question! |
|
|
152 | (1) |
|
|
152 | (2) |
|
Finding Functional Dependencies |
|
|
154 | (3) |
|
|
157 | (4) |
|
|
161 | (20) |
|
|
161 | (1) |
|
A Short History of Normal Forms |
|
|
162 | (1) |
|
|
163 | (1) |
|
From First Normal Form to Boyce-Codd Normal Form Step by Step |
|
|
164 | (3) |
|
Eliminating Anomalies from Functional Dependencies with BCNF |
|
|
167 | (10) |
|
Eliminating Anomalies from Multivalued Dependencies |
|
|
177 | (4) |
|
|
181 | (1) |
|
|
181 | (1) |
|
|
181 | (1) |
|
|
182 | (1) |
|
|
183 | (2) |
|
|
185 | (1) |
|
|
186 | (1) |
|
The Queen Anne Curiosity Shop Project Questions |
|
|
187 | (2) |
|
Morgan Importing Project Questions |
|
|
189 | (2) |
|
Chapter 4 Database Design Using Normalization |
|
|
191 | (21) |
|
|
191 | (1) |
|
|
192 | (1) |
|
Designing Updatable Databases |
|
|
193 | (4) |
|
Advantages and Disadvantages of Normalization |
|
|
193 | (1) |
|
|
194 | (1) |
|
|
194 | (2) |
|
|
196 | (1) |
|
|
196 | (1) |
|
Designing Read-Only Databases |
|
|
197 | (3) |
|
|
197 | (1) |
|
Customized Duplicated Tables |
|
|
198 | (2) |
|
|
200 | (5) |
|
The Multivalue, Multicolumn Problem |
|
|
200 | (2) |
|
|
202 | (1) |
|
|
203 | (1) |
|
The General-Purpose Remarks Column |
|
|
204 | (1) |
|
|
205 | (1) |
|
|
206 | (1) |
|
|
206 | (2) |
|
|
208 | (1) |
|
|
209 | (1) |
|
The Queen Anne Curiosity Shop Project Questions |
|
|
209 | (1) |
|
Morgan Importing Project Questions |
|
|
210 | (2) |
|
Chapter 5 Data Modeling with the Entity-Relationship Model |
|
|
212 | (55) |
|
|
212 | (1) |
|
The Purpose of a Data Model |
|
|
213 | (1) |
|
The Entity-Relationship Model |
|
|
213 | (14) |
|
|
214 | (1) |
|
|
214 | (1) |
|
|
214 | (1) |
|
|
215 | (2) |
|
|
217 | (1) |
|
|
218 | (1) |
|
Entity-Relationship Diagrams and Their Versions |
|
|
219 | (1) |
|
Variations of the E-R Model |
|
|
219 | (1) |
|
E-R Diagrams Using the IE Crow's Foot Model |
|
|
220 | (2) |
|
Strong Entities and Weak Entities |
|
|
222 | (1) |
|
|
222 | (1) |
|
Non-ID-Dependent Weak Entities |
|
|
223 | (1) |
|
The Ambiguity of the Weak Entity |
|
|
224 | (1) |
|
|
225 | (2) |
|
Patterns in Forms, Reports, and E-R Models |
|
|
227 | (18) |
|
Strong Entity Relationship Patterns |
|
|
228 | (3) |
|
ID-Dependent Relationship Patterns |
|
|
231 | (7) |
|
Mixed Identifying and Nonidentifying Relationship Patterns |
|
|
238 | (3) |
|
The For-Use-By Subtype Pattern |
|
|
241 | (1) |
|
Recursive Relationship Patterns |
|
|
242 | (3) |
|
The Data Modeling Process |
|
|
245 | (7) |
|
|
246 | (1) |
|
|
247 | (2) |
|
The Department/Major Report |
|
|
249 | (1) |
|
The Student Acceptance Letter |
|
|
249 | (3) |
|
|
252 | (1) |
|
|
253 | (1) |
|
|
253 | (3) |
|
|
256 | (6) |
|
|
262 | (3) |
|
The Queen Anne Curiosity Shop Project Questions |
|
|
265 | (1) |
|
Morgan Importing Project Questions |
|
|
265 | (2) |
|
Chapter 6 Transforming Data Models into Database Designs |
|
|
267 | (56) |
|
|
267 | (1) |
|
The Purpose of a Database Design |
|
|
268 | (1) |
|
Create a Table for Each Entity |
|
|
268 | (11) |
|
Selecting the Primary Key |
|
|
268 | (3) |
|
Specifying Alternate Keys |
|
|
271 | (1) |
|
Specifying Column Properties |
|
|
271 | (7) |
|
|
278 | (1) |
|
|
279 | (17) |
|
Relationships Between Strong Entities |
|
|
279 | (4) |
|
Relationships Using ID-Dependent Entities |
|
|
283 | (4) |
|
Relationships with a Weak Non-ID-Dependent Entity |
|
|
287 | (1) |
|
Relationships in Mixed Entity Designs |
|
|
288 | (1) |
|
Relationships Between Supertype and Subtype Entities |
|
|
289 | (1) |
|
|
290 | (2) |
|
Representing Ternary and Higher-Order Relationships |
|
|
292 | (3) |
|
Relational Representation of the Highline University Data Model |
|
|
295 | (1) |
|
Design for Minimum Cardinality |
|
|
296 | (9) |
|
Actions when the Parent Is Required |
|
|
297 | (2) |
|
Actions when the Child Is Required |
|
|
299 | (1) |
|
Implementing Actions for M-O Relationships |
|
|
300 | (1) |
|
Implementing Actions for O-M Relationships |
|
|
301 | (1) |
|
Implementing Actions for M-M Relationships |
|
|
301 | (1) |
|
Designing Special Case M-M Relationships |
|
|
302 | (1) |
|
Documenting the Minimum Cardinality Design |
|
|
302 | (2) |
|
An Additional Complication |
|
|
304 | (1) |
|
Summary of Minimum Cardinality Design |
|
|
304 | (1) |
|
The View Ridge Gallery Database |
|
|
305 | (8) |
|
View Ridge Gallery Database Summary of Requirements |
|
|
305 | (1) |
|
The View Ridge Data Model |
|
|
306 | (1) |
|
Database Design with Data Keys |
|
|
307 | (1) |
|
Minimum Cardinality Enforcement for Required Parents |
|
|
308 | (2) |
|
Minimum Cardinality Enforcement for the Required Child |
|
|
310 | (1) |
|
Column Properties for the View Ridge Database Design Tables |
|
|
311 | (2) |
|
|
313 | (3) |
|
|
316 | (1) |
|
|
316 | (2) |
|
|
318 | (1) |
|
|
319 | (2) |
|
The Queen Anne Curiosity Shop Project Questions |
|
|
321 | (1) |
|
Morgan Importing Project Questions |
|
|
321 | (2) |
Part 3 Database Implementation |
|
323 | (130) |
|
Chapter 7 SQL for Database Construction and Application Processing |
|
|
324 | (100) |
|
|
324 | (1) |
|
The Importance of Working with an Installed DBMS Product |
|
|
325 | (1) |
|
The View Ridge Gallery Database |
|
|
325 | (1) |
|
|
325 | (2) |
|
Managing Table Structure with SQL DDL |
|
|
327 | (15) |
|
Creating the VRG Database |
|
|
327 | (1) |
|
|
327 | (1) |
|
Using the SQL CREATE TABLE Statement |
|
|
328 | (1) |
|
Variations in SQL Data Types and SQUPSM |
|
|
329 | (1) |
|
Creating the VRG Database ARTIST Table |
|
|
329 | (3) |
|
Creating the VRG Database WORK Table and the 1: N ARTIST-to-WORK Relationship |
|
|
332 | (1) |
|
Implementing Required Parent Rows |
|
|
333 | (1) |
|
Implementing 1:1 Relationships |
|
|
334 | (1) |
|
|
334 | (1) |
|
Creating Default Values and Data Constraints with SQL |
|
|
335 | (1) |
|
Creating the VRG Database Tables |
|
|
336 | (4) |
|
The SQL ALTER TABLE Statement |
|
|
340 | (1) |
|
The SQL DROP TABLE Statement |
|
|
340 | (1) |
|
The SQL TRUNCATE TABLE Statement |
|
|
341 | (1) |
|
The SQL CREATE INDEX Statement |
|
|
341 | (1) |
|
|
342 | (10) |
|
The SQL, INSERT Statement |
|
|
342 | (1) |
|
Populating the VRG Database Tables |
|
|
343 | (6) |
|
|
349 | (1) |
|
|
350 | (1) |
|
|
351 | (1) |
|
|
352 | (10) |
|
Using SQL Views to Hide Columns and Rows |
|
|
355 | (1) |
|
Using SQL Views to Display Results of Computed Columns |
|
|
356 | (1) |
|
Using SQL Views to Hide Complicated SQL Syntax |
|
|
357 | (1) |
|
Layering Built-in Functions |
|
|
358 | (2) |
|
Using SQL Views for Isolation, Multiple Permissions, and Multiple Triggers |
|
|
360 | (1) |
|
|
361 | (1) |
|
Embedding SQL in Program Code |
|
|
362 | (16) |
|
Persistent Stored Modules (SQL/PSM) |
|
|
364 | (1) |
|
Using SQL User-Defined Functions |
|
|
364 | (3) |
|
|
367 | (6) |
|
|
373 | (3) |
|
Comparing User-Defined Functions, Triggers, and Stored Procedures |
|
|
376 | (2) |
|
|
378 | (2) |
|
|
380 | (1) |
|
|
381 | (10) |
|
|
391 | (4) |
|
|
395 | (14) |
|
The Queen Anne Curiosity Shop Project Questions |
|
|
409 | (7) |
|
Morgan Importing Project Questions |
|
|
416 | (8) |
|
Chapter 8 Database Redesign |
|
|
424 | (29) |
|
|
424 | (1) |
|
The Need for Database Redesign |
|
|
425 | (1) |
|
SQL Statements for Checking Functional Dependencies |
|
|
425 | (6) |
|
What Is a Correlated Subquery? |
|
|
426 | (5) |
|
How Do I Analyze an Existing Database? |
|
|
431 | (3) |
|
|
432 | (1) |
|
|
433 | (1) |
|
Database Backup and Test Databases |
|
|
433 | (1) |
|
Changing Table Names and Table Columns |
|
|
434 | (4) |
|
|
434 | (2) |
|
Adding and Dropping Columns |
|
|
436 | (1) |
|
Changing a Column Data Type or Column Constraints |
|
|
437 | (1) |
|
Adding and Dropping Constraints |
|
|
438 | (1) |
|
Changing Relationship Cardinalities |
|
|
438 | (4) |
|
Changing Minimum Cardinalities |
|
|
438 | (1) |
|
Changing Maximum Cardinalities |
|
|
439 | (3) |
|
Adding and Deleting Tables and Relationships |
|
|
442 | (1) |
|
|
443 | (1) |
|
|
443 | (2) |
|
|
445 | (1) |
|
|
445 | (2) |
|
|
447 | (1) |
|
|
448 | (1) |
|
The Queen Anne Curiosity Shop Project Questions |
|
|
449 | (1) |
|
Morgan Importing Project Questions |
|
|
450 | (3) |
Part 4 Multiuser Database Processing |
|
453 | (44) |
|
Chapter 9 Managing Multiuser Databases |
|
|
454 | (36) |
|
|
454 | (1) |
|
The Importance of Working with an Installed DBMS Product |
|
|
455 | (1) |
|
|
455 | (2) |
|
Managing the Database Structure |
|
|
456 | (1) |
|
|
457 | (13) |
|
The Need for Atomic Transactions |
|
|
458 | (3) |
|
|
461 | (2) |
|
Optimistic Versus Pessimistic Locking |
|
|
463 | (1) |
|
SQL Transaction Control Language and Declaring Lock Characteristics |
|
|
464 | (2) |
|
Implicit and Explicit Commit Transaction |
|
|
466 | (1) |
|
|
466 | (1) |
|
Transaction Isolation Level |
|
|
467 | (1) |
|
|
468 | (2) |
|
|
470 | (5) |
|
Processing Rights and Responsibilities |
|
|
470 | (1) |
|
|
471 | (1) |
|
|
472 | (2) |
|
|
474 | (1) |
|
|
475 | (1) |
|
Database Backup and Recovery |
|
|
475 | (4) |
|
Recovery via Reprocessing |
|
|
476 | (1) |
|
Recovery via Rollback/Rollforward |
|
|
476 | (3) |
|
|
479 | (1) |
|
Maintaining the Data Repository |
|
|
480 | (1) |
|
|
481 | (1) |
|
|
482 | (1) |
|
|
483 | (1) |
|
|
484 | (1) |
|
|
485 | (1) |
|
The Queen Anne Curiosity Shop Project Questions |
|
|
486 | (2) |
|
Morgan Importing Project Questions |
|
|
488 | (2) |
|
Chapter 10 Managing Databases with Microsoft SQL Server 2017, Oracle Database, and MySQL 5.7 |
|
|
490 | (7) |
|
|
490 | (1) |
|
|
491 | (1) |
|
Using the DBMS Database Administration and Database Development Utilities |
|
|
492 | (1) |
|
|
492 | (1) |
|
Creating and Running SQL Scripts |
|
|
492 | (1) |
|
Reviewing the Database Structure in the DBMS GUI Utility |
|
|
493 | (1) |
|
Creating and Populating the View Ridge Gallery VRG Database Tables |
|
|
493 | (1) |
|
Creating SQL Views for the View Ridge Gallery VRG Database |
|
|
493 | (1) |
|
Importing Microsoft Excel Data into a Database Table |
|
|
493 | (1) |
|
Database Application Logic and SQL/Persistent Stored Modules (SQL/PSM) |
|
|
493 | (1) |
|
|
494 | (1) |
|
|
494 | (1) |
|
DBMS Database Backup and Recovery |
|
|
494 | (1) |
|
Other DBMS Topics Not Discussed |
|
|
494 | (1) |
|
Choose Your DBMS Product(s)! |
|
|
495 | (1) |
|
|
495 | (1) |
|
|
496 | (1) |
|
|
496 | (1) |
|
Chapter 10A Managing Databases with Microsoft SQL Server 2017 Online Chapter |
|
|
|
Chapter 10B Managing Databases with Oracle Database Online Chapter |
|
|
|
Chapter 10C Managing Databases with MySQL 5.7 Online Chapter |
|
|
Part 5 Database Access Standards |
|
497 | (123) |
|
Chapter 11 The Web Server Environment |
|
|
498 | (71) |
|
|
498 | (2) |
|
A Web Database Application for the View Ridge Gallery |
|
|
500 | (1) |
|
The Web Database Processing Environment |
|
|
501 | (1) |
|
Database Server Access Standards |
|
|
502 | (1) |
|
|
503 | (9) |
|
|
504 | (1) |
|
|
505 | (1) |
|
Creating an ODBC Data Source Name |
|
|
506 | (6) |
|
The Microsoft .NET Framework and ADO.NET |
|
|
512 | (11) |
|
|
514 | (4) |
|
|
518 | (1) |
|
|
518 | (5) |
|
|
523 | (4) |
|
|
523 | (2) |
|
Java Server Pages (JSP) and Servlets |
|
|
525 | (1) |
|
|
525 | (2) |
|
Web Database Processing with PHP |
|
|
527 | (13) |
|
Web Database Processing with PHP and the NetBeans IDE |
|
|
527 | (3) |
|
Getting Started with HTML Web Pages |
|
|
530 | (1) |
|
|
530 | (1) |
|
Creating the index.html Web Page |
|
|
530 | (3) |
|
|
533 | (7) |
|
Web Page Examples with PHP |
|
|
540 | (15) |
|
Example 1: Updating a Table |
|
|
541 | (4) |
|
Example 2: Using PHP Data Objects (PDO) |
|
|
545 | (1) |
|
Example 3: Invoking a Stored Procedure |
|
|
546 | (7) |
|
Challenges for Web Database Processing |
|
|
553 | (1) |
|
|
554 | (1) |
|
Extensible Markup Language (XML) |
|
|
555 | (2) |
|
|
555 | (1) |
|
|
556 | (1) |
|
Creating XML Documents from Database Data |
|
|
557 | (1) |
|
Using the SQL SELECT...FOR XML Statement |
|
|
557 | (2) |
|
|
559 | (2) |
|
|
561 | (1) |
|
|
562 | (3) |
|
|
565 | (2) |
|
|
567 | (1) |
|
The Queen Anne Curiosity Shop Project Questions |
|
|
567 | (1) |
|
Morgan Importing Project Questions |
|
|
568 | (1) |
|
Chapter 12 Data Warehouses, Business Intelligence Systems, and Big Data |
|
|
569 | (51) |
|
|
569 | (2) |
|
Business Intelligence Systems |
|
|
571 | (1) |
|
The Relationship Between Operational and BI Systems |
|
|
571 | (1) |
|
Reporting Systems and Data Mining Applications |
|
|
571 | (2) |
|
|
572 | (1) |
|
|
573 | (1) |
|
Data Warehouses and Data Marts |
|
|
573 | (13) |
|
Components of a Data Warehouse |
|
|
573 | (4) |
|
Data Warehouses Versus Data Marts |
|
|
577 | (1) |
|
|
578 | (8) |
|
|
586 | (11) |
|
|
586 | (2) |
|
|
588 | (9) |
|
|
597 | (2) |
|
Distributed Database Processing |
|
|
599 | (2) |
|
Types of Distributed Databases |
|
|
599 | (1) |
|
Challenges of Distributed Databases |
|
|
600 | (1) |
|
Object-Relational Databases |
|
|
601 | (1) |
|
|
602 | (1) |
|
|
603 | (4) |
|
Big Data and the Not Only SQL Movement |
|
|
607 | (4) |
|
|
608 | (2) |
|
|
610 | (1) |
|
|
610 | (1) |
|
|
611 | (2) |
|
|
613 | (1) |
|
|
614 | (2) |
|
|
616 | (1) |
|
|
617 | (1) |
|
The Queen Anne Curiosity Shop Project Questions |
|
|
618 | (1) |
|
Morgan Importing Project Questions |
|
|
619 | (1) |
Online Appendices |
|
620 | (1) |
Bibliography |
|
621 | (2) |
Glossary |
|
623 | (16) |
Index |
|
639 | |