Concepts of Database Management, Tenth Edition
By Lisa Friedrichsen, Lisa Ruffolo, Ellen F. Monk, Joy L. Starks, Philip J. Pratt, Mary Z. Last
Contents:
Preface xv
Module 1
Introduction to Database Management 1
Introduction 1
JC Consulting Company Background 1
Selecting a Database Solution 4
Defining Database Terminology 4
Storing Data 5
Identifying Database Management Systems 11
Advantages of a Properly Designed Relational Database 14
Key Factors for a Healthy Relational Database 15
Big Data 16
Preparing for a Career in Database Administration and Data Analysis 16
Introduction to the Pitt Fitness Database Case 16
Introduction to the Sports Physical Therapy Database Case 20
Summary 24
Key Terms 24
Module Review Questions 25
Problems 25
Critical Thinking Questions 26
JC Consulting Case Exercises 26
Problems 26
Critical Thinking Questions 27
Pitt Fitness Case Exercises 27
Problems 27
Critical Thinking Questions 29
Sports Physical Therapy Case Exercises 29
Problems 29
Critical Thinking Questions 30
Module 2
The Relational Model: Introduction, QBE, and Relational Algebra 31
Introduction 31
Examining Relational Databases 31
Relational Database Shorthand 35
Creating Simple Queries and Using Query-By-Example 36
Selecting Fields and Running the Query 37
Saving and Using Queries 38
Using Simple Criteria 40
Parameter Queries 41
Comparison Operators 42
Using Compound Criteria 42
Creating Computed Fields 46
Summarizing with Aggregate Functions and Grouping 49
Sorting Records 52
Sorting on Multiple Keys 54
Joining Tables 56
Joining Multiple Tables 60
Using an Update Query 62
Using a Delete Query 63
Using a Make-Table Query 64
Optimizing Queries 65
Examining Relational Algebra 65
Selection 66
Projection 66
Joining 67
Union 69
Intersection 70
Difference 70
Product 71
Division 71
Summary 72
Key Terms 73
Module Review Questions 74
Problems 74
Critical Thinking Questions 75
JC Consulting Case Exercises: QBE 76
Problems 76
Critical Thinking Questions 77
JC Consulting Case Exercises: Relational Algebra 77
Problems 77
Pitt Fitness Case Exercises 79
Problems 79
Critical Thinking Questions 80
Sports Physical Therapy Case Exercises 81
Problems 81
Critical Thinking Questions 82
Module 3
The Relational Model: SQL 83
Introduction 83
Getting Started with SQL 84
Opening an SQL Query Window in Access 84
Changing the Font and Font Size in SQL View 85
Creating a Table 85
Naming Conventions 85
Data Types 86
Selecting Data 88
Numeric Criteria 90
Text Criteria 91
Date Criteria 93
Comparing Two Fields 94
Saving SQL Queries 94
Using Compound Conditions: AND Criteria 95
Using Compound Conditions: OR Criteria 95
Using the BETWEEN Operator 96
Using the NOT Operator 98
Creating Calculated Fields 99
Using Wildcards and the LIKE Operator 101
Using the IN Operator 102
Sorting Records 102
Sorting on Multiple Fields 103
Using Aggregate Functions 104
Grouping Records 105
Limiting Records with the HAVING clause 107
Writing Subqueries 108
Joining Tables with the WHERE Clause 109
Joining More Than Two Tables with the WHERE Clause 111
Using the UNION Operator 112
Updating Values with the SQL UPDATE Command 113
Inserting a Record with the SQL INSERT Command 114
Deleting Records with the SQL DELETE Command 116
Saving Query Results as a Table 117
Developing Career Skills: SQL 118
Accessing Free SQL Tutorials 118
Summary 119
Key Terms 120
Module Review Questions 120
Problems 120
Critical Thinking Question 122
JC Consulting Case Exercises 122
Problems 122
Critical Thinking Questions 124
Pitt Fitness Case Exercises 124
Problems 124
Critical Thinking Questions 126
Sports Physical Therapy Case Exercises 126
Problems 126
Critical Thinking Questions 129
Module 4
The Relational Model: Advanced Topics 131
Introduction 131
Creating and Using Views 131
Using Indexes 134
Examining Database Security Features 137
Preventing Unauthorized Access 138
Safely Distributing Information 139
Providing Physical Security 139
Enforcing Integrity Rules 139
Entity Integrity 139
Referential Integrity 140
Cascade Options 142
Legal-Values Integrity 143
Changing the Structure of a Relational Database 144
Adding a New Field to a Table 144
Modifying Field Properties 145
Deleting a Field 146
Deleting a Table 146
Using SQL JOIN Commands 147
LEFT Joins 149
RIGHT Joins 150
Applying Referential Integrity: Error Messages 152
Applying Referential Integrity: Null Values 153
Using the System Catalog 153
Using Stored Procedures and Triggers 154
Triggers 154
Career Skills: Database Administrators 157
Summary 158
Key Terms 159
Module Review Questions 159
Problems 159
Critical Thinking Question 161
JC Consulting Case Exercises 161
Problems 161
Critical Thinking Questions 162
Pitt Fitness Case Exercises 162
Problems 162
Critical Thinking Questions 164
Sports Physical Therapy Case Exercises 164
Problems 164
Critical Thinking Questions 166
Module 5
Database Design: Normalization 167
Introduction 167
Case Study: Faculty/Student Advising Assignments 167
Data Modification Anomalies 168
Functional Dependence 170
Keys 171
First Normal Form 171
Atomic Values 173
Breaking Out Atomic Values Using Query Design View 173
Creating a Blank Database with Access 174
Importing Excel Data into an Access Database 174
Algorithms 175
Creating Fields 178
Creating New Fields in Table Design View 178
Updating Fields 179
Updating Field Values Using Query Design View 179
Creating the 1NF Table 181
Creating a New Table in Query Design View 181
Using Atomic Values for Quantities 183
Finding Duplicate Records 184
Finding Duplicate Records in Query Design View 184
Second Normal Form 186
Benefits of Normalization 190
Third Normal Form 190
Fourth Normal Form 191
Creating Lookup Tables in Query Design View 192
Beyond Fourth Normal Form 194
Summary 196
Key Terms 196
Module Review Questions 197
Problems 197
Critical Thinking Questions 198
JC Consulting Case Exercises 198
Problems 198
Critical Thinking Questions 199
Pitt Fitness Case Exercises 200
Problems 200
Critical Thinking Questions 202
Sports Physical Therapy Case Exercises 202
Problems 202
Critical Thinking Questions 205
Module 6
Database Design: Relationships 207
Introduction 207
User Views 208
Documenting a Relational Database Design 209
Database Design Language (DBDL) 209
Setting Keys and Indexes 211
Entity-Relationship (E-R) Diagrams 213
Crow’s Foot Notation 214
Microsoft Access E-R Diagram in the Relationships window 215
The Entity-Relationship Model (ERM) 217
Exploring One-to-Many Relationships in Access 220
Table Datasheet View 220
Subdatasheets 221
Lookup Properties 222
Subforms 225
Working with One-to-Many Relationships in Query Datasheet View 225
Other Relationship Types 229
One-to-One Relationships 229
Many-to-Many Relationships 232
Summary 235
Key Terms 235
Module Review Questions 236
Problems 236
Critical Thinking Questions 237
JC Consulting Case Exercises 237
Problems 237
Critical Thinking Questions 238
Pitt Fitness Case Exercises 238
Problems 238
Critical Thinking Questions 239
Sports Physical Therapy Case Exercises 239
Problems 239
Critical Thinking Questions 240
Module 7
Database Management Systems Processes and Services 241
Introduction 241
Create, Read, Update, and Delete Data 242
Provide Catalog Services 243
Catalog Services in Microsoft Access 243
Catalog Services in Enterprise Database Management Systems 244
Support Concurrent Updates 244
Concurrent Updates in Microsoft Access 244
Concurrent Updates in Enterprise Database Management Systems 245
Recover Data 246
Recovering Data in Microsoft Access 246
Recovering Data in Enterprise Database Management Systems 247
Forward Recovery 248
Backward Recovery 249
Provide Security Services 250
Encryption 250
Authentication 250
Authorization 250
Views 250
Privacy 250
Provide Data Integrity Features 251
Support Data Independence 252
Adding a Field 252
Changing the Property of a Field 252
Managing Indexes 252
Changing the Name of a Field, Table, or View 252
Adding or Changing a Relationship 252
Support Data Replication 253
Summary 254
Key Terms 254
Module Review Questions 255
Problems 255
Critical Thinking Questions 256
JC Consulting Case Exercises 256
Problems 256
Critical Thinking Questions 257
Pitt Fitness Case Exercises 257
Problems 257
Critical Thinking Questions 259
Sports Physical Therapy Case Exercises 259
Problems 259
Critical Thinking Questions 260
Module 8
Database Industry Careers 261
Introduction 261
Careers in the Database Industry 261
Role of a Database Administrator 262
Duties and Responsibilities of a DBA 262
Database Policy Formulation and Enforcement 263
Access Privileges 263
Security 264
Disaster Planning 264
Archiving 265
Database Technical Functions 266
Database Design 266
SQL and Views 266
Testing 267
Performance Tuning 267
DBMS Maintenance 267
Database Administrative Functions 268
Data Dictionary Management 268
Training 268
Professionals Reporting to the DBA 268
Responsibilities of a Data Analyst 270
Responsibilities of a Data Scientist 270
Database Industry Certifications 271
Summary 274
Key Terms 274
Module Review Questions 275
Problems 275
Critical Thinking Questions 276
JC Consulting Case Exercises 276
Problems 276
Critical Thinking Questions 276
Pitt Fitness Case Exercises 277
Problems 277
Critical Thinking Questions 278
Sports Physical Therapy Case Exercises 278
Problems 278
Critical Thinking Questions 279
Module 9
Database Industry Trends 281
Introduction 281
Database Architectures 281
Centralized Approach 281
Cloud Computing 283
Personal Computer Revolution 284
Client/Server Architecture 286
Access and Client/Server Architecture 286
Three-Tier Client/Server Architecture 287
Data Warehouses 289
Online Analytical Processing (OLAP) 289
Codd’s Rules for OLAP Systems 292
Current OLAP Vendors 292
Distributed Databases 293
Rules for Distributed Databases 293
Summary of Current Database Architecture Implementations 293
Selecting a Relational Database System 294
Software Solution Stacks 297
NoSQL Database Management Systems 299
Object-Oriented Database Management Systems 300
Rules for Object-Oriented Database Management Systems 301
Big Data 302
Google Analytics 302
Data Formats 303
XML 304
JSON 306
Data Visualization Tools 307
Visualization Tools in Microsoft Excel 307
Microsoft Power BI 309
Tableau 309
Summary 311
Key Terms 311
Module Review Questions 313
Problems 313
Critical Thinking Questions 314
JC Consulting Case Exercises 314
Problems 314
Critical Thinking 315
Pitt Fitness Case Exercises 315
Problems 315
Critical Thinking Questions 316
Sports Physical Therapy Case Exercises 317
Problems 317
Critical Thinking Questions 318
Appendix A
Comprehensive Design Example: Douglas College 319
Douglas College Requirements 319
General Description 319
Report Requirements 319
Update (Transaction) Requirements 323
Douglas College Information-Level Design 323
Final Information-Level Design 340
Exercises 341
Appendix B
SQL Reference 349
Alter Table 349
Column or Expression List (Select Clause) 349
Computed Fields 350
Functions 350
Conditions 350
Simple Conditions 350
Compound Conditions 350
BETWEEN Conditions 351
LIKE Conditions 351
IN Conditions 351
CREATE INDEX 351
CREATE TABLE 352
CREATE VIEW 352
DATA TYPES 353
DELETE ROWS 353
DROP INDEX 354
DROP TABLE 354
GRANT 354
INSERT 354
INTEGRITY 355
JOIN 355
REVOKE 356
SELECT 356
SELECT INTO 357
SUBQUERIES 357
UNION 358
UPDATE 358
Appendix C
FAQ Reference 359
Appendix D
Introduction To MysqL 361
Introduction 361
Downloading and Installing Mysql 361
Running Mysql Workbench and Connecting to Mysql Server 365
Opening an Sql File In Mysql Workbench 366
Running an Sql Script in Mysql Workbench 367
Refreshing Schemas in Mysql Workbench 367
Viewing Table Data in Mysql Workbench 367
Writing Sql in Mysql Workbench 368
Practicing With Mysql Workbench 369
Summary 370
Key Terms 370
Appendix E
A Systems Analysis Approach to Information-Level Requirements 371
Introduction 371
Information Systems 371
System Requirement Categories 372
Output Requirements 372
Input Requirements 372
Processing Requirements 373
Technical and Constraining Requirements 373
Determining System Requirements 373
Interviews 373
Questionnaires 374
Document Collection 374
Observation 374
Research 374
Transitioning From Systems Analysis to Systems Design 374
Key Terms 375
Critical Thinking Questions 375
Glossary 377
Index 391