Database design, application development, and administration

873 Pages·2019·18.57 MB·English

by  Michael V. Mannino

DATABASE
DESIGN, APPLICATION
DEVELOPMENT, & ADMINISTRATION
7e
MICHAEL MANNINO
DATABASE DESIGN,
APPLICATION DEVELOPMENT,
AND ADMINISTRATION
26008_fm_pi-xxx.indd 1 3/3/18 12:34 AM
26008_fm_pi-xxx.indd 2 3/3/18 12:34 AM
DATABASE DESIGN,
APPLICATION
DEVELOPMENT, AND
ADMINISTRATION
SEVENTH EDITION
Michael V. Mannino
University of Colorado, Denver
26008_fm_pi-xxx.indd 3 3/3/18 12:34 AM
© 2019 CHICAGO BUSINESS PRESS
DATABASE DESIGN, APPLICATION DEVELOPMENT, AND ADMINISTRATION
SEVENTH EDITION
ALL RIGHTS RESERVED. No part of this work covered by the copyright herein may be
reproduced, transmitted, stored or used in any form or by any means graphic, electronic, or
mechanical, including but not limited to photocopying, recording, scanning, digitizing, taping,
web distribution, information networks, or information storage and retrieval systems, except
as permitted under Section 107 or 108 of the 1976 United States Copyright Act, without the
prior written permission of the publisher.
For product information or assistance visit: www.chicagobusinesspress.com
ISBN-13: 978-1-948426-00-8
ISBN-10: 1-948426-00-5
26008_fm_pi-xxx.indd 4 3/3/18 12:34 AM
BRIEF CONTENTS
Part I INTRODUCTION TO DATABASE ENVIRONMENTS 1
1 Introduction to Database Management 3
2 Introduction to Database Development 25
Part II UNDERSTANDING RELATIONAL DATABASES 45
3 The Relational Data Model 47
4 Query Formulation with SQL 77
Part III DATA MODELING 141
5 Understanding Entity Relationship Diagrams 143
6 Developing Data Models for Business Databases 179
Part IV RELATIONAL DATABASE DESIGN 233
7 Normalization Concepts and Processes 235
8 Physical Database Design 267
Part V APPLICATION DEVELOPMENT WITH RELATIONAL DATABASES 319
9 Advanced Query Formulation with SQL 321
10 Application Development with Views 375
11 Stored Procedures and Triggers 415
Part VI DATA WAREHOUSE PROCESSING 477
12 Data Warehouse Concepts and Management 479
13 Conceptual Design of Data Warehouses 509
14 Data Integration Concepts and Practices 549
15 Query Formulation for Data Warehouses 585
v
26008_fm_pi-xxx.indd 5 3/3/18 12:34 AM
vi Brief Contents
Part VII MANAGING DATABASE ENVIRONMENTS 641
16 Data and Database Administration 643
17 Transaction Management 681
18 Client-Server Processing, Parallel Database Processing, and Distributed Databases 725
19 DBMS Extensions for Object and NoSQL Databases 767
Bibliography 829
Indexes 833
26008_fm_pi-xxx.indd 6 3/3/18 12:34 AM
CONTENTS
Part I INTRODUCTION TO DATABASE ENVIRONMENTS 1
1 Introduction to Database Management 3
Learning Objectives 3
Overview 3
1.1 Database Characteristics 4
1.2 Features of Database Management Systems 6
1.2.1 Database Definition 6
1.2.2 Nonprocedural Access 7
1.2.3 Application Development and Procedural Language Interface 8
1.2.4 Features to Support Database Operations 9
1.2.5 Third-Party Features 10
1.3 Development of Database Technology and Market Structure 10
1.3.1 Evolution of Database Technology 11
1.3.2 Current Market for Database Software 12
1.4 Architectures of Database Management Systems 13
1.4.1 Data Independence and the Three Schema Architecture 13
1.4.2 Parallel and Distributed Database Processing 15
1.5 Organizational Impacts of Database Technology 17
1.5.1 Interacting with Databases 18
1.5.2 Managing Data Resources in Organizations 19
Closing Thoughts 20
Review Concepts 21
Questions 21
Problems 23
References for Further Study 23
2 Introduction to Database Development 25
Learning Objectives 25
Overview 25
2.1 Information Systems 26
2.1.1 Components of Information Systems 26
2.1.2 Information Systems Development Process 26
2.2 Goals of Database Development 28
2.2.1 Develop a Common Vocabulary 28
2.2.2 Define the Meaning of Data 29
2.2.3 Ensure Data Quality 29
2.2.4 Find an Efficient Implementation 30
2.3 Database Development Process 30
2.3.1 Phases of Database Development 30
2.3.2 Skills in Database Development 34
vii
26008_fm_pi-xxx.indd 7 3/3/18 12:34 AM
viii Contents
2.4 Tools for Database Development 35
2.4.1 Diagramming 35
2.4.2 Documentation 36
2.4.3 Analysis 36
2.4.4 Prototyping Tools 36
2.4.5 Commercial CASE Tools 37
Closing Thoughts 40
Review Concepts 41
Questions 42
Problems 43
References for Further Study 43
Part II UNDERSTANDING RELATIONAL DATABASES 45
3 The Relational Data Model 47
Learning Objectives 47
Overview 47
3.1 Basic Elements 48
3.1.1 Tables 48
3.1.2 Connections among Tables 49
3.1.3 Alternative Terminology 51
3.2 Integrity Rules 51
3.2.1 Definition of the Integrity Rules 51
3.2.2 Application of the Integrity Rules 52
3.2.3 Graphical Representation of Referential Integrity 56
3.3 Delete and Update Actions for Referenced Rows 57
3.4 Operators of Relational Algebra 59
3.4.1 Restrict (Select) and Project Operators 59
3.4.2 Extended Cross Product Operator 60
3.4.3 Join Operator 61
3.4.4 Outer Join Operator 63
3.4.5 Union, Intersection, and Difference Operators 66
3.4.6 Summarize Operator 68
3.4.7 Divide Operator 69
3.4.8 Summary of Operators 70
Closing Thoughts 71
Review Concepts 71
Questions 72
Problems 73
References for Further Study 76
Appendix 3.A: CREATE TABLE Statements for the University Database Tables ONLINE
Appendix 3.B: SQL:2016 Syntax Summary ONLINE
Appendix 3.C: Generation of Unique Values for Primary Keys ONLINE
4 Query Formulation with SQL 77
Learning Objectives 77
Overview 77
4.1 Background 78
4.1.1 Brief History of SQL 78
4.1.2 Scope of SQL 79
4.2 Getting Started with the SELECT Statement 80
4.2.1 Single Table Problems 83
4.2.2 Joining Tables 91
26008_fm_pi-xxx.indd 8 3/3/18 12:34 AM
Contents ix
4.2.3 Summarizing Tables with GROUP BY and HAVING 93
4.2.4 Improving the Appearance of Results 97
4.3 Conceptual Evaluation Process for SELECT Statements 99
4.4 Critical Questions for Query Formulation 104
4.5 Refining Query Formulation Skills with Examples 105
4.5.1 Joining Multiple Tables with the Cross Product Style 105
4.5.2 Joining Multiple Tables with the Join Operator Style 109
4.5.3 Self-Joins and Multiple Joins between Two Tables 112
4.5.4 Combining Joins and Grouping 114
4.5.5 Traditional Set Operators in SQL 115
4.6 SQL Modification Statements 117
4.7 Query Formulation Errors and Coding Practices 120
Closing Thoughts 124
Review Concepts 125
Questions 128
Problems 130
References for Further Study 140
Appendix 4.A: SQL:2016 Syntax Summary ONLINE
Appendix 4.B: Syntax Differences among Major DBMS Products ONLINE
Part III DATA MODELING 141
5 Understanding Entity Relationship Diagrams 143
Learning Objectives 143
Overview 143
5.1 Introduction to Entity Relationship Diagrams 144
5.1.1 Basic Symbols 144
5.1.2 Relationship Cardinality 145
5.1.3 Comparison to Relational Database Diagrams 147
5.2 Understanding Relationships 147
5.2.1 Identification Dependency (Weak Entity Types and Identifying Relationships) 147
5.2.2 Relationship Patterns 148
5.2.3 Equivalence between 1-M and M-N Relationships 152
5.3 Classification in the Entity Relationship Model 153
5.3.1 Generalization Hierarchies 153
5.3.2 Disjointness and Completeness Constraints 154
5.3.3 Multiple Levels of Generalization 154
5.4 Notation Summary and Diagram Rules 155
5.4.1 Notation Summary 155
5.4.2 Diagram Rules 157
5.5 Comparison to Other Notations 160
5.5.1 Range of ERD Variations in Data Modeling Tools 160
5.5.2 ERD Notation in Aqua Data Studio 161
5.5.3 ERD Notation in Oracle SQL Developer 163
5.5.4 Entity Relationship Stencil in Visio Professional 164
5.5.5 ERD Notation in Visual Paradigm 165
5.5.6 Class Diagram Notation of the Unified Modeling Language 165
Closing Thoughts 168
Review Concepts 169
Questions 169
Problems 171
References for Further Study 177
26008_fm_pi-xxx.indd 9 3/3/18 12:34 AM