Database Systems: Design, Implementation and Management, 14th Edition PDF by Carlos Coronel and Steven Morris

By

Database Systems: Design, Implementation, & Management, 14th Edition

By Carlos Coronel and Steven Morris

Database Systems Design, Implementation, & Management, 14th Edition

Contents:

Preface, xiv

Text Features, xx

Additional Features, xxii

Acknowledgments, xxiv

Part 1

Database Concepts

Chapter 1

Database Systems 3

1-1 Why Databases? 4

1-2 Data versus Information 5

1-3 Introducing the Database 8

1-3a Role and Advantages of the DBMS 8

1-3b Types of Databases 10

1-4 Why Database Design Is Important 12

1-5 Evolution of File System Data Processing 15

1-5a Manual File Systems 16

1-5b Computerized File Systems 16

1-5c File System Redux: Modern End-User Productivity

Tools 18

1-6 Problems with File System Data Processing 18

1-6a Structural and Data Dependence 19

1-6b Data Redundancy 20

1-6c Data Anomalies 21

1-7 Database Systems 21

1-7a The Database System Environment 22

1-7b DBMS Functions 24

1-7c Managing the Database System: A Shift in Focus 27

1-8 Preparing for Your Database Professional Career 28

Summary 29

Key Terms 29

Review Questions 30

Problems 31

Chapter 2

Data Models 33

2-1 Data Modeling and Data Models 34

2-2 The Importance of Data Models 35

2-3 Data Model Basic Building Blocks 36

2-4 Business Rules 37

2-4a Discovering Business Rules 37

2-4b Translating Business Rules into Data Model

Components 38

2-4c Naming Conventions 39

2-5 The Evolution of Data Models 39

2-5a Hierarchical and Network Models 39

2-5b The Relational Model 41

2-5c The Entity Relationship Model 43

2-5d The Object-Oriented Model 45

2-5e Object/Relational and XML 47

2-5f Emerging Data Models: Big Data and NoSQL 48

2-5g Data Models: A Summary 51

2-6 Degrees of Data Abstraction 52

2-6a The External Model 55

2-6b The Conceptual Model 56

2-6c The Internal Model 57

2-6d The Physical Model 58

Summary 59

Key Terms 60

Review Questions 60

Problems 61

Part 2

Design Concepts

Chapter 3

The Relational Database Model 65

3-1 A Logical View of Data 67

3-1a Tables and Their Characteristics 67

3-2 Keys 69

3-2a Dependencies 69

3-2b Types of Keys 70

3-3 Integrity Rules 73

3-4 Relational Algebra 75

3-4a Formal Definitions and Terminology 75

3-4b Relational Set Operators 76

3-5 The Data Dictionary and the

System Catalog 84

3-6 Relationships within the Relational Database 86

3-6a The 1:M Relationship 86

3-6b The 1:1 Relationship 88

3-6c The M:N Relationship 90

3-7 Data Redundancy Revisited 94

3-8 Indexes 96

3-9 Codd’s Relational Database Rules 98

Summary 98

Key Terms 99

Review Questions 99

Problems 102

Chapter 4

Entity Relationship (ER) Modeling 108

4-1 The Entity Relationship Model 109

4-1a Entities 110

4-1b Attributes 110

4-1c Relationships 116

4-1d Connectivity and Cardinality 116

4-1e Existence Dependence 119

4-1f Relationship Strength 119

4-1g Weak Entities 122

4-1h Relationship Participation 124

4-1i Relationship Degree 126

4-1j Recursive Relationships 128

4-1k Associative (Composite) Entities 132

4-2 Developing an ER Diagram 134

4-3 Database Design Challenges:

Conflicting Goals 142

Summary 146

Key Terms 147

Review Questions 147

Problems 149

Cases 155

Chapter 5

Advanced Data Modeling 162

5-1 The Extended Entity Relationship Model 163

5-1a Entity Supertypes and Subtypes 163

5-1b Specialization Hierarchy 164

5-1c Inheritance 165

5-1d Subtype Discriminator 167

5-1e Disjoint and Overlapping Constraints 167

5-1f Completeness Constraint 169

5-1g Specialization and Generalization 170

5-2 Entity Clustering 170

5-3 Entity Integrity: Selecting Primary Keys 171

5-3a Natural Keys and Primary Keys 172

5-3b Primary Key Guidelines 172

5-3c When to Use Composite Primary Keys 172

5-3d When to Use Surrogate Primary Keys 174

5-4 Design Cases: Learning Flexible Database Design 175

5-4a Design Case 1: Implementing 1:1

Relationships 176

5-4b Design Case 2: Maintaining History of Time-Variant

Data 177

5-4c Design Case 3: Fan Traps 180

5-4d Design Case 4: Redundant Relationships 181

Summary 182

Key Terms 182

Review Questions 182

Problems 183

Cases 185

Chapter 6

Normalization of Database Tables 192

6-1 Database Tables and Normalization 193

6-2 The Need for Normalization 194

6-3 The Normalization Process 197

6-3a Conversion to First Normal Form (1NF) 198

6-3b Conversion to Second Normal Form (2NF) 202

6-3c Conversion to Third Normal Form (3NF) 204

6-4 Improving the Design 206

6-5 Surrogate Key Considerations 210

6-6 Higher-Level Normal Forms 212

6-6a The Boyce-Codd Normal Form 212

6-6b Fourth Normal Form (4NF) 215

6-6c Fifth Normal Form (5NF) 217

6-7 Normalization and Database Design 218

6-8 Denormalization 222

6-9 Data-Modeling Checklist 225

Summary 227

Key Terms 227

Review Questions 228

Problems 229

Part 3

Advanced Design and Implementation

Chapter 7

Introduction to Structured Query

Language (SQL) 239

7-1 SQL Basics 240

7-1a Data Types 241

7-1b SQL Queries 242

7-1c The Database Model 243

7-2 Basic SELECT Queries 244

7-3 SELECT Statement Options 245

7-3a Using Column Aliases 246

7-3b Using Computed Columns 247

7-3c Arithmetic Operators:

The Rule of Precedence 248

7-3d Date Arithmetic 249

7-3e Listing Unique Values 250

7-4 FROM Clause Options 251

7-5 ORDER BY Clause Options 252

7-6 WHERE Clause Options 254

7-6a Selecting Rows with Conditional Restrictions 255

7-6b Using Comparison Operators on Character

Attributes 257

7-6c Using Comparison Operators on Dates 258

7-6d Logical Operators: AND, OR, and NOT 258

7-6e Special Operators 260

7-7 JOIN Operations 264

7-7a Natural Join 265

7-7b JOIN USING Syntax 267

7-7c JOIN ON Syntax 268

7-7d Common Attribute Names 269

7-7e Old-Style Joins 269

7-7f Outer Joins 271

7-7g Cross Join 274

7-7h Joining Tables with an Alias 275

7-7i Recursive Joins 275

7-8 Aggregate Processing 277

7-8a Aggregate Functions 277

7-8b Grouping Data 281

7-8c HAVING Clause 284

7-9 Subqueries 286

7-9a WHERE Subqueries 287

7-9b IN Subqueries 288

7-9c HAVING Subqueries 289

7-9d Multirow Subquery Operators: ALL and ANY 289

7-9e FROM Subqueries 290

7-9f Attribute List Subqueries 291

7-9g Correlated Subqueries 293

7-10 SQL Functions 296

7-10a Date and Time Functions 297

7-10b Numeric Functions 300

7-10c String Functions 300

7-10d Conversion Functions 302

7-11 Relational Set Operators 304

7-11a UNION 305

7-11b UNION ALL 306

7-11c INTERSECT 307

7-11d EXCEPT (MINUS) 308

7-11e Syntax Alternatives 309

7-12 Crafting SELECT Queries 310

7-12a Know Your Data 310

7-12b Know the Problem 310

7-12c Build One Clause at a Time 311

Summary 312

Key Terms 313

Review Questions 314

Problems 315

Chapter 8

Advanced SQL 351

8-1 Data Definition Commands 352

8-1a Starting Database Model 352

8-1b Creating the Database 354

8-1c The Database Schema 354

8-1d Data Types 355

8-2 Creating Table Structures 358

8-2a CREATE TABLE command 358

8-2b SQL Constraints 362

8-2c Creating a Table with a SELECT Statement 365

8-2d SQL Indexes 366

8-3 Altering Table Structures 367

8-3a Changing a Column’s Data Type 368

8-3b Changing a Column’s Data Characteristics 368

8-3c Adding a Column 368

8-3d Adding Primary Key, Foreign Key, and Check

Constraints 369

8-3e Dropping a Column 369

8-3f Deleting a Table from the Database 370

8-4 Data Manipulation Commands 370

8-4a Adding Table Rows 370

8-4b Inserting Table Rows with a SELECT Subquery 372

8-4c Saving Table Changes 374

8-4d Updating Table Rows 374

8-4e Deleting Table Rows 377

8-4f Restoring Table Contents 378

8-5 Virtual Tables: Creating a View 378

8-5a Updatable Views 380

8-6 Auto Increment, Identity, and Sequences 381

8-7 Procedural SQL 387

8-7a Stored Procedures 389

8-7b Working with Variables 391

8-7c Conditional Execution 392

8-7d Iteration or Looping 393

8-7e SELECT Processing with Cursors 396

8-7f Stored Procedures with Parameters 399

8-7g Triggers 401

8-7h User Defined Functions 412

8-8 Embedded SQL 412

Summary 417

Key Terms 418

Review Questions 418

Problems 418

Cases 425

Chapter 9

Database Design 431

9-1 The Information System 432

9-2 The Systems Development Life Cycle 434

9-2a Planning 434

9-2b Analysis 436

9-2c Detailed Systems Design 436

9-2d Implementation 437

9-2e Maintenance 437

9-3 The Database Life Cycle 437

9-3a The Database Initial Study 438

9-3b Database Design 442

9-3c Implementation and Loading 444

9-3d Testing and Evaluation 445

9-3e Operation 448

9-3f Maintenance and Evolution 448

9-4 Conceptual Design 448

9-4a Data Analysis and Requirements 450

9-4b Entity Relationship Modeling and

Normalization 452

9-4c Data Model Verification 455

9-4d Distributed Database Design 458

9-5 DBMS Software Selection 458

9-6 Logical Design 459

9-6a Map the Conceptual Model to the Logical Model

Components 459

9-6b Validate the Logical Model Using Normalization 461

9-6c Validate the Logical Model Integrity Constraints 461

9-6d Validate the Logical Model against User

Requirements 462

9-7 Physical Design 462

9-7a Define Data Storage Organization 463

9-7b Define Integrity and Security Measures 463

9-7c Determine Performance Measurements 464

9-8 Database Design Strategies 464

9-9 Centralized versus Decentralized Design 465

Summary 468

Key Terms 468

Review Questions 468

Problems 469

Part 4

Advanced Database Concepts

Chapter 10

Transaction Management and

Concurrency Control 473

10-1 What Is a Transaction? 474

10-1a Evaluating Transaction Results 476

10-1b Transaction Properties 478

10-1c Transaction Management with SQL 479

10-1d The Transaction Log 480

10-2 Concurrency Control 481

10-2a Lost Updates 481

10-2b Uncommitted Data 482

10-2c Inconsistent Retrievals 483

10-2d The Scheduler 484

10-3 Concurrency Control with Locking Methods 486

10-3a Lock Granularity 486

10-3b Lock Types 489

10-3c Two-Phase Locking to Ensure Serializability 490

10-3d Deadlocks 491

10-4 Concurrency Control with Time Stamping

Methods 492

10-4a Wait/Die and Wound/Wait Schemes 493

10-5 Concurrency Control with Optimistic Methods 494

10-6 ANSI Levels of Transaction Isolation 494

10-7 Database Recovery Management 496

10-7a Transaction Recovery 497

Summary 501

Key Terms 501

Review Questions 502

Problems 502

Chapter 11

Database Performance Tuning and

Query Optimization 506

11-1 Database Performance-Tuning Concepts 507

11-1a Performance Tuning: Client and Server 508

11-1b DBMS Architecture 509

11-1c Database Query Optimization Modes 511

11-1d Database Statistics 512

11-2 Query Processing 513

11-2a SQL Parsing Phase 514

11-2b SQL Execution Phase 515

11-2c SQL Fetching Phase 515

11-2d Query Processing Bottlenecks 515

11-3 Indexes and Query Optimization 516

11-4 Optimizer Choices 518

11-4a Using Hints to Affect Optimizer Choices 520

11-5 SQL Performance Tuning 521

11-5a Index Selectivity 521

11-5b Conditional Expressions 522

11-6 Query Formulation 524

11-7 DBMS Performance Tuning 525

11-8 Query Optimization Example 527

Summary 533

Key Terms 534

Review Questions 534

Problems 535

Chapter 12

Distributed Database Management

Systems 539

12-1 The Evolution of Distributed Database Management

Systems 540

12-2 DDBMS Advantages and Disadvantages 542

12-3 Distributed Processing and Distributed

Databases 543

12-4 Characteristics of Distributed Database Management

Systems 545

12-5 DDBMS Components 546

12-6 Levels of Data and Process Distribution 547

12-6a Single-Site Processing, Single-Site Data 547

12-6b Multiple-Site Processing, Single-Site Data 548

12-6c Multiple-Site Processing, Multiple-Site Data 549

12-7 Distributed Database Transparency Features 550

12-8 Distribution Transparency 551

12-9 Transaction Transparency 553

12-9a Distributed Requests and Distributed Transactions 553

12-9b Distributed Concurrency Control 556

12-9c Two-Phase Commit Protocol 557

12-10 Performance and Failure Transparency 558

12-11 Distributed Database Design 559

12-11a Data Fragmentation 559

12-11b Data Replication 563

12-11c Data Allocation 565

12-12 The CAP Theorem 565

12-13 C. J. Date’s 12 Commandments for Distributed

Databases 567

Summary 568

Key Terms 568

Review Questions 569

Problems 570

Chapter 13

Business Intelligence and Data

Warehouses 573

13-1 The Need for Data Analysis 574

13-2 Business Intelligence 574

13-2a Business Intelligence Architecture 576

13-2b Business Intelligence Benefits 580

13-2c Business Intelligence Evolution 580

13-2d Business Intelligence Technology Trends 582

13-3 Decision Support Data 583

13-3a Operational Data versus Decision Support Data 583

13-3b Decision Support Database Requirements 586

13-4 The Data Warehouse 588

13-4a Data Marts 590

13-4b Twelve Rules That Define a Data Warehouse 591

13-5 Star Schemas 592

13-5a Facts 592

13-5b Dimensions 592

13-5c Attributes 593

13-5d Attribute Hierarchies 595

13-5e Star Schema Representation 596

13-5f Performance-Improving Techniques for the Star

Schema 598

13-6 Online Analytical Processing 602

13-6a Multidimensional Data Analysis Techniques 602

13-6b Advanced Database Support 604

13-6c Easy-to-Use End-User Interfaces 604

13-6d OLAP Architecture 604

13-6e Relational OLAP 607

13-6f Multidimensional OLAP 608

13-6g Relational versus Multidimensional OLAP 609

13-7 Data Analytics 610

13-7a Data Mining 610

13-7b Predictive Analytics 613

13-8 SQL Analytic Functions 614

13-8a The ROLLUP Extension 615

13-8b The CUBE Extension 616

13-8c Materialized Views 618

13-9 Data Visualization 621

13-9a The Need for Data Visualization 622

13-9b The Science of Data Visualization 624

13-9c Understanding the Data 626

13-10 Data Lake 627

Summary 628

Key Terms 628

Review Questions 629

Problems 630

Chapter 14

Big Data and NoSQL 640

14-1 Big Data 641

14-1a Volume 643

14-1b Velocity 644

14-1c Variety 645

14-1d Other Characteristics 646

14-2 Hadoop 647

14-2a HDFS 648

14-2b MapReduce 650

14-2c Hadoop Ecosystem 652

14-2d Hadoop Pushback 654

14-3 NoSQL 654

14-3a Key-Value Databases 655

14-3b Document Databases 656

14-3c Column-Oriented Databases 657

14-3d Graph Databases 660

14-3e Aggregate Awareness 662

14-4 NewSQL Databases 662

14-5 Working with Document Databases

Using MongoDB 663

14-5a Importing Documents in MongoDB 664

14-5b Example of a MongoDB Query Using find() 665

14-6 Working with Graph Databases Using Neo4j 666

14-6a Creating Nodes in Neo4j 667

14-6b Retrieving Node Data with MATCH and WHERE 668

14-6c Retrieving Relationship Data with MATCH and

WHERE 669

Summary 670

Key Terms 672

Review Questions 673

Part 5

Databases and the Internet

Chapter 15

Database Connectivity and Web Technologies 675

15-1 Database Connectivity 676

15-1a Native SQL Connectivity 677

15-1b ODBC, DAO, and RDO 678

15-1c OLE-DB 680

15-1d ADO.NET 683

15-1e Java Database Connectivity (JDBC) 685

15-2 Database Internet Connectivity 686

15-2a Web-to-Database Middleware: Server-Side

Extensions 687

15-2b Web Server Interfaces 689

15-2c The Web Browser 690

15-2d Client-Side Extensions 691

15-2e Web Application Servers 692

15-2f Web Database Development 692

15-3 Extensible Markup Language (XML) 696

15-3a Document Type Definitions (DTD) and XML

Schemas 698

15-3b XML Presentation 700

15-3c XML Applications 702

15-4 Cloud Computing Services 703

15-4a Cloud Implementation Types 706

15-4b Characteristics of Cloud Services 706

15-4c Types of Cloud Services 707

15-4d Cloud Services: Advantages and Disadvantages 708

15-4e SQL Data Services 709

Summary 710

Key Terms 711

Review Questions 712

Problems 713

Part 6

Database Administration

Chapter 16

Database Administration and

Security 715

16-1 Data as a Corporate Asset 716

16-2 The Need for a Database and Its Role in an

Organization 718

16-3 Introduction of a Database: Special Considerations 719

16-4 The Evolution of Database Administration 721

16-5 The Database Environment’s Human Component 724

16-5a The DBA’s Managerial Role 726

16-5b The DBA’s Technical Role 731

16-6 Security 737

16-6a Security Policies 738

16-6b Security Vulnerabilities 738

16-6c Database Security 740

16-7 Database Administration Tools 741

16-7a The Data Dictionary 742

16-7b Case Tools 744

16-8 Developing a Data Administration Strategy 746

16-9 The DBA’s Role in the Cloud 748

16-10 The DBA at Work: Using Oracle for Database

Administration 749

16-10a Oracle Database Administration Tools 750

16-10b Ensuring That the RDBMS Starts Automatically 751

16-10c Creating Tablespaces and Datafiles 751

16-10d Managing Users and Establishing Security 753

16-10e Customizing the Database Initialization

Parameters 756

Summary 757

Key Terms 758

Review Questions 758

Glossary 760

Index 775

The following appendices are included on the Instructor and Student Resource Sites at www.cengage.com.

Appendix A: Designing Databases with Lucidchart: A Tutorial

Appendix B: The University Lab: Conceptual Design

Appendix C: The University Lab: Conceptual Design Verification,

Logical Design, and Implementation

Appendix D: Converting an ER Model into a Database Structure

Appendix E: Comparison of ER Modeling Notations

Appendix F: Client/Server Systems

Appendix G: Object-Oriented Databases

Appendix H: Unified Modeling Language (UML)

Appendix I: Databases in Electronic Commerce

Appendix J: Web Database Development with ColdFusion

Appendix K: The Hierarchical Database Model

Appendix L: The Network Database Model

Appendix M: MS Access Tutorial

Appendix N: Creating a New Database Using Oracle

Appendix O: Data Warehouse Implementation Factors

Appendix P: Working with MongoDB

Appendix Q: Working with Neo4j

This book is US$10
To get free sample pages OR Buy this book


Share this Book!

Leave a Comment

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