Concepts of Database Management 10th Edition PDF by Lisa Friedrichsen, Lisa Ruffolo, Ellen F. Monk, Joy L. Starks, Philip J. Pratt, Mary Z. Last

By

Concepts of Database Management, Tenth Edition

Lisa Friedrichsen, Lisa Ruffolo, Ellen F. Monk, Joy L. Starks, Philip J. Pratt, Mary Z. Last

Concepts of Database Management

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

This book is US$10. Order for this book:
(Request for free sample pages click on "Order Now" button)

Book Order
Or, Send email: [email protected]

Share this Book!

Leave a Comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.