Database Systems
A Practical Approach to Design, Implementation, and Management
6th Edition
Published on 4. September 2014
Book
Paperback/Softback
1440 pages
978-0-13-294326-0 (ISBN)
Description
Database Systems is ideal for a one- or two-term course in database management or database design in an undergraduate or graduate level course. With its comprehensive coverage, this book can also be used as a reference for IT professionals.
This best-selling text introduces the theory behind databases in a concise yet comprehensive manner, providing database design methodology that can be used by both technical and non-technical readers. The methodology for relational Database Management Systems is presented in simple, step-by-step instructions in conjunction with a realistic worked example using three explicit phases-conceptual, logical, and physical database design.
?
Teaching and Learning Experience
This program presents a better teaching and learning experience-for you and your students. It provides:
Database Design Methodology that can be Used by Both Technical and Non-technical Readers
A Comprehensive Introduction to the Theory behind Databases
A Clear Presentation that Supports Learning
This best-selling text introduces the theory behind databases in a concise yet comprehensive manner, providing database design methodology that can be used by both technical and non-technical readers. The methodology for relational Database Management Systems is presented in simple, step-by-step instructions in conjunction with a realistic worked example using three explicit phases-conceptual, logical, and physical database design.
?
Teaching and Learning Experience
This program presents a better teaching and learning experience-for you and your students. It provides:
Database Design Methodology that can be Used by Both Technical and Non-technical Readers
A Comprehensive Introduction to the Theory behind Databases
A Clear Presentation that Supports Learning
More details
Edition
6th edition
Language
English
Place of publication
United States
Publishing group
Pearson Education (US)
Target group
College/higher education
Professional and scholarly
Dimensions
Height: 232 mm
Width: 188 mm
Thickness: 45 mm
Weight
1880 gr
ISBN-13
978-0-13-294326-0 (9780132943260)
Schweitzer Classification
Other editions
Previous edition
Thomas M. Connolly | Carolyn E. Begg
Database Systems
A Practical Approach to Design, Implementation and Management: United States Edition
Book
04/2009
5th Edition
Pearson
€142.36
Article exhausted; check for reprint
Persons
Thomas M. Connolly is the head of the Computing and Information Systems division at the University of Paisley.
Carolyn E. Begg is a lecturer at the University of Paisley, with research interests in Information Systems, Database Management Systems, and Decision Support Systems within medicine.
Carolyn E. Begg is a lecturer at the University of Paisley, with research interests in Information Systems, Database Management Systems, and Decision Support Systems within medicine.
Content
Table of Contents Part 1 Background
Chapter 1 Introduction to Databases
1.1 Introduction
1.2 Traditional File-Based Systems
1.2.1 File-Based Approach
1.2.2 Limitations of the File-Based Approach
1.3 Database Approach
1.3.1 The Database
1.3.2 The Database Management System (DBMS)
1.3.3 (Database) Application Programs
1.3.4 Components of the DBMS Environment
1.3.5 Database Design: The Paradigm Shift
1.4 Roles in the Database Environment
1.4.1 Data and Database Administrators
1.4.2 Database Designers
1.4.3 Application Developers
1.4.4 End-Users
1.5 History of Database Management Systems
1.6 Advantages and Disadvantages of DBMSs
Chapter Summary
Review Questions
Exercises
Chapter 2 Database Environment
2.1 The Three-Level ANSI-SPARC Architecture
2.1.1 External Level
2.1.2 Conceptual Level
2.1.3 Internal Level
2.1.4 Schemas, Mappings, and Instances
2.1.5 Data Independence
2.2 Database Languages
2.2.1 The Data Definition Language (DDL)
2.2.2 The Data Manipulation Language (DML)
2.2.3 Fourth-Generation Languages (4GLs)
2.3 Data Models and Conceptual Modeling
2.3.1 Object-Based Data Models
2.3.2 Record-Based Data Models
2.3.3 Physical Data Models
2.3.4 Conceptual Modeling
2.4 Functions of a DBMS
Chapter Summary
Review Questions
Exercises
Chapter 3 Database Architectures and the Web
3.1 Multi-user DBMS Architectures
3.1.1 Teleprocessing
3.1.2 File-Server Architecture
3.1.3 Traditional Two-Tier Client-Server Architecture
3.1.4 Three-Tier Client-Server Architecture
3.1.5 N-Tier Architectures
3.1.6 Middleware
3.1.7 Transaction Processing Monitors
3.2 Web Services and Service-Oriented Architectures
3.2.1 Web Services
3.2.2 Service-Oriented Architectures (SOA)
3.3 Distributed DBMSs
3.4 Data Warehousing
3.5 Cloud Computing
3.5.1 Benefits and Risks of Cloud Computing
3.5.2 Cloud-based database solutions
3.6 Components of a DBMS
3.7 Oracle Architecture
3.7.1 Oracle's Logical Database Structure
3.7.2 Oracle's Physical Database Structure
Chapter Summary
Review Questions
Exercises
Part 2 The Relational Model and Languages
Chapter 4 The Relational Model
4.1 Brief History of the Relational Model
4.2 Terminology
4.2.1 Relational Data Structure
4.2.2 Mathematical Relations
4.2.3 Database Relations
4.2.4 Properties of Relations
4.2.5 Relational Keys
4.2.6 Representing Relational Database Schemas
4.3 Integrity Constraints
4.3.1 Nulls
4.3.2 Entity Integrity
4.3.3 Referential Integrity
4.3.4 General Constraints
4.4 Views
4.4.1 Terminology
4.4.2 Purpose of Views
4.4.3 Updating Views
Chapter Summary
Review Questions
Exercises
Chapter 5 Relational Algebra and Relational Calculus
5.1 The Relational Algebra
5.1.1 Unary Operations
5.1.2 Set Operations
5.1.3 Join Operations
5.1.4 Division Operation
5.1.5 Aggregation and Grouping Operations
5.1.6 Summary of the Relational Algebra Operations
5.2 The Relational Calculus
5.2.1 Tuple Relational Calculus
5.2.2 Domain Relational Calculus
5.3 Other Languages
Chapter Summary
Review Questions
Exercises
Chapter 6 SQL: Data Manipulation
6.1 Introduction to SQL
6.1.1 Objectives of SQL
6.1.2 History of SQL
6.1.3 Importance of SQL
6.1.4 Terminology
6.2 Writing SQL Commands
6.3 Data Manipulation
6.3.1 Simple Queries
6.3.2 Sorting Results (ORDER BY Clause)
6.3.3 Using the SQL Aggregate Functions
6.3.4 Grouping Results (GROUP BY Clause)
6.3.5 Subqueries
6.3.6 ANY and ALL
6.3.7 Multi-table Queries
6.3.8 EXISTS and NOT EXISTS
6.3.9 Combining Result Tables (UNION, INTERSECT, EXCEPT)
6.3.10 Database Updates
Chapter Summary
Review Questions
Exercises
Chapter 7 SQL: Data Definition
7.1 The ISO SQL Data Types
7.1.1 SQL Identifiers
7.1.2 SQL Scalar Data Types
7.2 Integrity Enhancement Feature
7.2.1 Required Data
7.2.2 Domain Constraints
7.2.3 Entity Integrity
7.2.4 Referential Integrity
7.2.5 General Constraints
7.3 Data Definition
7.3.1 Creating a Database
7.3.2 Creating a Table (CREATE TABLE)
7.3.3 Changing a Table Definition (ALTER TABLE)
7.3.4 Removing a Table (DROP TABLE)
7.3.5 Creating an Index (CREATE INDEX)
7.3.6 Removing an Index (DROP INDEX)
7.4 Views
7.4.1 Creating a View (CREATE VIEW)
7.4.2 Removing a View (DROP VIEW)
7.4.3 View Resolution
7.4.4 Restrictions on Views
7.4.5 View Updatability
7.4.6 WITH CHECK OPTION
7.4.7 Advantages and Disadvantages of Views
7.4.8 View Materialization
7.5 Transactions
7.5.1 Immediate and Deferred Integrity Constraints
7.6 Discretionary Access Control
7.6.1 Granting Privileges to Other Users (GRANT)
7.6.2 Revoking Privileges from Users (REVOKE)
Chapter Summary
Review Questions
Exercises
Chapter 8 Advanced SQL
8.1 The SQL Programming Language
8.1.1 Declarations
8.1.2 Assignments
8.1.3 Control Statements
8.1.4 Exceptions in PL/SQL
8.1.5 Cursors in PL/SQL
8.2 Subprograms, Stored Procedures, Functions, and Packages
8.3 Triggers
8.4 Recursion
Chapter Summary
Review Questions
Exercises
Chapter 9 Object-Relational DBMSs
9.1 Advanced Database Applications
9.2 Weaknesses of RDBMSs
9.3 Storing Objects in a Relational Database
9.3.1 Mapping Classes to Relations
9.3.2 Accessing Objects in the Relational Database
9.4 Introduction to Object-Relational Database Systems
9.5 SQL:2011
9.5.1 Row Types
9.5.2 User-Defined Types
9.5.3 Subtypes and Supertypes
9.5.4 User-Defined Routines
9.5.5 Polymorphism
9.5.6 Reference Types and Object Identity
9.5.7 Creating Tables
9.5.8 Querying Data
9.5.9 Collection Types
9.5.10 Typed Views
9.5.11 Persistent Stored Modules
9.5.12 Triggers
9.5.13 Large Objects
9.5.14 Recursion
9.6 Object-Oriented Extensions in Oracle
9.6.1 User-Defined Data Types
9.6.2 Manipulating Object Tables
9.6.3 Object Views
9.6.4 Privileges
Chapter Summary
Review Questions
Exercises
Part 3 Database Analysis and Design
Chapter 10 Database System Development Lifecycle
10.1 The Information Systems Lifecycle
10.2 The Database System Development Lifecycle
10.3 Database Planning
10.4 System Definition
10.4.1 User Views
10.5 Requirements Collection and Analysis
10.5.1 Centralized Approach
10.5.2 View Integration Approach
10.6 Database Design
10.6.1 Approaches to Database Design
10.6.2 Data Modeling
10.6.3 Phases of Database Design
10.7 DBMS Selection
10.7.1 Selecting the DBMS
10.8 Application Design
10.8.1 Transaction Design
10.8.2 User Interface Design Guidelines
10.9 Prototyping
10.10 Implementation
10.11 Data Conversion and Loading
10.12 Testing
10.13 Operational Maintenance
10.14 CASE Tools
Chapter Summary
Review Questions
Exercises
Chapter 11 Database Analysis and the DreamHome Case Study
11.1 When Are Fact-Finding Techniques Used?
11.2 What Facts Are Collected?
11.3 Fact-Finding Techniques
11.3.1 Examining Documentation
11.3.2 Interviewing
11.3.3 Observing the Enterprise in Operation
11.3.4 Research
11.3.5 Questionnaires
11.4 Using Fact-Finding Techniques: A Worked -Example
11.4.1 The DreamHome Case Study-An Overview of the Current System
11.4.2 The DreamHome Case Study-Database Planning
11.4.3 The DreamHome Case Study-System Definition
11.4.4 The DreamHome Case Study-Requirements Collection and Analysis
11.4.5 The DreamHome Case Study-Database Design
Chapter Summary
Review Questions
Exercises
Chapter 12 Entity-Relationship Modeling
12.1 Entity Types
12.2 Relationship Types
12.2.1 Degree of Relationship Type
12.2.2 Recursive Relationship
12.3 Attributes
12.3.1 Simple and Composite Attributes
12.3.2 Single-valued and Multi-valued Attributes
12.3.3 Derived Attributes
12.3.4 Keys
12.4 Strong and Weak Entity Types
12.5 Attributes on Relationships
12.6 Structural Constraints
12.6.1 One-to-One (1:1) Relationships
12.6.2 One-to-Many (1:*) Relationships
12.6.3 Many-to-Many (*:*) Relationships
12.6.4 Multiplicity for Complex Relationships
12.6.5 Cardinality and Participation Constraints
12.7 Problems with ER Models
12.7.1 Fan Traps
12.7.2 Chasm Traps
Chapter Summary
Review Questions
Exercises
Chapter 13 Enhanced Entity-Relationship Modeling
13.1 Specialization/Generalization
13.1.1 Superclasses and Subclasses
13.1.2 Superclass/Subclass Relationships
13.1.3 Attribute Inheritance
13.1.4 Specialization Process
13.1.5 Generalization Process
13.1.6 Constraints on Specialization/Generalization
13.1.7 Worked Example of using Specialization/ Generalization to Model the Branch View of the DreamHome Case Study
13.2 Aggregation
13.3 Composition
Chapter Summary
Review Questions
Exercises
Chapter 14 Normalization
14.1 The Purpose of Normalization
14.2 How Normalization Supports Database Design
14.3 Data Redundancy and Update Anomalies
14.3.1 Insertion Anomalies
14.3.2 Deletion Anomalies
14.3.3 Modification Anomalies
14.4 Functional Dependencies
14.4.1 Characteristics of Functional Dependencies
14.4.2 Identifying Functional Dependencies
14.4.3 Identifying the Primary Key for a Relation Using Functional Dependencies
14.5 The Process of Normalization
14.6 First Normal Form (1NF)
14.7 Second Normal Form (2NF)
14.8 Third Normal Form (3NF)
14.9 General Definitions of 2NF and 3NF
Chapter Summary
Review Questions
Exercises
Chapter 15 Advanced Normalization
15.1 More on Functional Dependencies
15.1.1 Inference Rules for Functional Dependencies
15.1.2 Minimal Sets of Functional Dependencies
15.2 Boyce-Codd Normal Form (BCNF)
15.2.1 Definition of BCNF
15.3 Review of Normalization Up to BCNF
15.4 Fourth Normal Form (4NF)
15.4.1 Multi-Valued Dependency
15.4.2 Definition of Fourth Normal Form
15.5 Fifth Normal Form (5NF)
15.5.1 Lossless-Join Dependency
15.5.2 Definition of Fifth Normal Form
Chapter Summary
Review Questions
Exercises
Part 4 Methodology
Chapter 16 Methodology-Conceptual Database Design
16.1 Introduction to the Database Design Methodology
16.1.1 What Is a Design Methodology?
16.1.2 Conceptual, Logical, and Physical Database Design
16.1.3 Critical Success Factors in Database Design
16.2 Overview of the Database Design Methodology
16.3 Conceptual Database Design Methodology
Step 1: Build Conceptual Data Model
Chapter Summary
Review Questions
Exercises
Chapter 17 Methodology-Logical Database Design for the Relational Model
17.1 Logical Database Design Methodology for the Relational Model
Step 2: Build Logical Data Model
Chapter Summary
Review Questions
Exercises
Chapter 18 Methodology-Physical Database Design for Relational Databases
18.1 Comparison of Logical and Physical Database Design
18.2 Overview of the Physical Database Design Methodology
18.3 The Physical Database Design Methodology for Relational Databases
Step 3: Translate Logical Data Model for Target DBMS
Step 4: Design File Organizations and Indexes
Step 5: Design User Views
Step 6: Design Security Mechanisms
Chapter Summary
Review Questions
Exercises
Chapter 19 Methodology-Monitoring and Tuning the Operational System
19.1 Denormalizing and Introducing Controlled Redundancy
Step 7: Consider the Introduction of Controlled Redundancy
19.2 Monitoring the System to Improve Performance
Step 8: Monitor and Tune the Operational System
Chapter Summary
Review Questions
Exercises
Part 5 Selected Database Issues
Chapter 20 Security and Administration
20.1 Database Security
20.1.1 Threats
20.2 Countermeasures-Computer-Based Controls
20.2.1 Authorization
20.2.2 Access Controls
20.2.3 Views
20.2.4 Backup and Recovery
20.2.5 Integrity
20.2.6 Encryption
20.2.7 RAID (Redundant Array of Independent Disks)
20.3 Security in Microsoft Office Access DBMS
20.4 Security in Oracle DBMS
20.5 DBMSs and Web Security
20.5.1 Proxy Servers
20.5.2 Firewalls
20.5.3 Message Digest Algorithms and Digital Signatures
20.5.4 Digital Certificates
20.5.5 Kerberos
20.5.6 Secure Sockets Layer and Secure HTTP
20.5.7 Secure Electronic Transactions and Secure Transaction Technology
20.5.8 Java Security
20.5.9 ActiveX Security
20.6 Data Administration and Database Administration
20.6.1 Data Administration
20.6.2 Database Administration
20.6.3 Comparison of Data and Database Administration
Chapter Summary
Review Questions
Exercises
Chapter 21 Professional, Legal, and Ethical Issues in Data Management
21.1 Defining Legal and Ethical Issues in IT
21.1.1 Defining Ethics in the Context of IT
21.1.2 The Difference Between Ethical and Legal Behavior
21.1.3 Ethical Behavior in IT
21.2 Legislation and Its Impact on the IT Function
21.2.1 Securities and Exchange Commission (SEC) Regulation National Market System (NMS)
21.2.2 The Sarbanes-Oxley Act, COBIT, and COSO
21.2.3 The Health Insurance Portability and Accountability Act
21.2.4 The European Union (EU) Directive on Data Protection of 1995
21.2.5 The United Kingdom's Data Protection Act of 1998
21.2.6 Access to Information Laws
21.2.7 International Banking-Basel II Accords
21.3 Establishing a Culture of Legal and Ethical Data Stewardship
21.3.1 Developing an Organization-Wide Policy for Legal and Ethical Behavior
21.3.2 Professional Organizations and Codes of Ethics
21.3.3 Developing an Organization-Wide Policy for Legal and Ethical Behavior for DreamHome
21.4 Intellectual Property
21.4.1 Patent
21.4.2 Copyright
21.4.3 Trademark
21.4.4 Intellectual Property Rights Issues for Software
21.4.5 Intellectual Property Rights Issues for Data
Chapter Summary
Review Questions
Exercises
Chapter 22 Transaction Management
22.1 Transaction Support
22.1.1 Properties of Transactions
22.1.2 Database Architecture
22.2 Concurrency Control
22.2.1 The Need for Concurrency Control
22.2.2 Serializability and Recoverability
22.2.3 Locking Methods
22.2.4 Deadlock
22.2.5 Timestamping Methods
22.2.6 Multiversion Timestamp Ordering
22.2.7 Optimistic Techniques
22.2.8 Granularity of Data Items
22.3 Database Recovery
22.3.1 The Need for Recovery
22.3.2 Transactions and Recovery
22.3.3 Recovery Facilities
22.3.4 Recovery Techniques
22.3.5 Recovery in a Distributed DBMS
22.4 Advanced Transaction Models
22.4.1 Nested Transaction Model
22.4.2 Sagas
22.4.3 Multilevel Transaction Model
22.4.4 Dynamic Restructuring
22.4.5 Workflow Models
22.5 Concurrency Control and Recovery in Oracle
22.5.1 Oracle's Isolation Levels
22.5.2 Multiversion Read Consistency
22.5.3 Deadlock Detection
22.5.4 Backup and Recovery
Chapter Summary
Review Questions
Exercises
Chapter 23 Query Processing
23.1 Overview of Query Processing
23.2 Query Decomposition
23.3 Heuristical Approach to Query Optimization
23.3.1 Transformation Rules for the Relational Algebra Operations
23.3.2 Heuristical Processing Strategies
23.4 Cost Estimation for the Relational Algebra Operations
23.4.1 Database Statistics
23.4.2 Selection Operation (S = sp(R))
23.4.3 Join Operation (T = (R 1F S))
23.4.4 Projection Operation (S = pA1, A2, . . . , A m(R))
23.4.5 The Relational Algebra Set Operations (T = R ? S, T = R > S, T = R - S)
23.5 Enumeration of Alternative Execution Strategies
23.5.1 Pipelining
23.5.2 Linear Trees
23.5.3 Physical Operators and Execution Strategies
23.5.4 Reducing the Search Space
23.5.5 Enumerating Left-Deep Trees
23.5.6 Semantic Query Optimization
23.5.7 Alternative Approaches to Query Optimization
23.5.8 Distributed Query Optimization
23.6 Query Processing and Optimization
23.6.1 New Index Types
23.7 Query Optimization in Oracle
23.7.1 Rule-Based and Cost-Based Optimization
23.7.2 Histograms
23.7.3 Viewing the Execution Plan
Chapter Summary
Review Questions
Exercises
Part 6 Distributed DBMSs and Replication
Chapter 24 Distributed DBMSs-Concepts and Design
24.1 Introduction
24.1.1 Concepts
24.1.2 Advantages and Disadvantages of DDBMSs
24.1.3 Homogeneous and Heterogeneous DDBMSs
24.2 Overview of Networking
24.3 Functions and Architectures of a DDBMS
24.3.1 Functions of a DDBMS
24.3.2 Reference Architecture for a DDBMS
24.3.3 Reference Architecture for a Federated MDBS
24.3.4 Component Architecture for a DDBMS
24.4 Distributed Relational Database Design
24.4.1 Data Allocation
24.4.2 Fragmentation
Chapter 1 Introduction to Databases
1.1 Introduction
1.2 Traditional File-Based Systems
1.2.1 File-Based Approach
1.2.2 Limitations of the File-Based Approach
1.3 Database Approach
1.3.1 The Database
1.3.2 The Database Management System (DBMS)
1.3.3 (Database) Application Programs
1.3.4 Components of the DBMS Environment
1.3.5 Database Design: The Paradigm Shift
1.4 Roles in the Database Environment
1.4.1 Data and Database Administrators
1.4.2 Database Designers
1.4.3 Application Developers
1.4.4 End-Users
1.5 History of Database Management Systems
1.6 Advantages and Disadvantages of DBMSs
Chapter Summary
Review Questions
Exercises
Chapter 2 Database Environment
2.1 The Three-Level ANSI-SPARC Architecture
2.1.1 External Level
2.1.2 Conceptual Level
2.1.3 Internal Level
2.1.4 Schemas, Mappings, and Instances
2.1.5 Data Independence
2.2 Database Languages
2.2.1 The Data Definition Language (DDL)
2.2.2 The Data Manipulation Language (DML)
2.2.3 Fourth-Generation Languages (4GLs)
2.3 Data Models and Conceptual Modeling
2.3.1 Object-Based Data Models
2.3.2 Record-Based Data Models
2.3.3 Physical Data Models
2.3.4 Conceptual Modeling
2.4 Functions of a DBMS
Chapter Summary
Review Questions
Exercises
Chapter 3 Database Architectures and the Web
3.1 Multi-user DBMS Architectures
3.1.1 Teleprocessing
3.1.2 File-Server Architecture
3.1.3 Traditional Two-Tier Client-Server Architecture
3.1.4 Three-Tier Client-Server Architecture
3.1.5 N-Tier Architectures
3.1.6 Middleware
3.1.7 Transaction Processing Monitors
3.2 Web Services and Service-Oriented Architectures
3.2.1 Web Services
3.2.2 Service-Oriented Architectures (SOA)
3.3 Distributed DBMSs
3.4 Data Warehousing
3.5 Cloud Computing
3.5.1 Benefits and Risks of Cloud Computing
3.5.2 Cloud-based database solutions
3.6 Components of a DBMS
3.7 Oracle Architecture
3.7.1 Oracle's Logical Database Structure
3.7.2 Oracle's Physical Database Structure
Chapter Summary
Review Questions
Exercises
Part 2 The Relational Model and Languages
Chapter 4 The Relational Model
4.1 Brief History of the Relational Model
4.2 Terminology
4.2.1 Relational Data Structure
4.2.2 Mathematical Relations
4.2.3 Database Relations
4.2.4 Properties of Relations
4.2.5 Relational Keys
4.2.6 Representing Relational Database Schemas
4.3 Integrity Constraints
4.3.1 Nulls
4.3.2 Entity Integrity
4.3.3 Referential Integrity
4.3.4 General Constraints
4.4 Views
4.4.1 Terminology
4.4.2 Purpose of Views
4.4.3 Updating Views
Chapter Summary
Review Questions
Exercises
Chapter 5 Relational Algebra and Relational Calculus
5.1 The Relational Algebra
5.1.1 Unary Operations
5.1.2 Set Operations
5.1.3 Join Operations
5.1.4 Division Operation
5.1.5 Aggregation and Grouping Operations
5.1.6 Summary of the Relational Algebra Operations
5.2 The Relational Calculus
5.2.1 Tuple Relational Calculus
5.2.2 Domain Relational Calculus
5.3 Other Languages
Chapter Summary
Review Questions
Exercises
Chapter 6 SQL: Data Manipulation
6.1 Introduction to SQL
6.1.1 Objectives of SQL
6.1.2 History of SQL
6.1.3 Importance of SQL
6.1.4 Terminology
6.2 Writing SQL Commands
6.3 Data Manipulation
6.3.1 Simple Queries
6.3.2 Sorting Results (ORDER BY Clause)
6.3.3 Using the SQL Aggregate Functions
6.3.4 Grouping Results (GROUP BY Clause)
6.3.5 Subqueries
6.3.6 ANY and ALL
6.3.7 Multi-table Queries
6.3.8 EXISTS and NOT EXISTS
6.3.9 Combining Result Tables (UNION, INTERSECT, EXCEPT)
6.3.10 Database Updates
Chapter Summary
Review Questions
Exercises
Chapter 7 SQL: Data Definition
7.1 The ISO SQL Data Types
7.1.1 SQL Identifiers
7.1.2 SQL Scalar Data Types
7.2 Integrity Enhancement Feature
7.2.1 Required Data
7.2.2 Domain Constraints
7.2.3 Entity Integrity
7.2.4 Referential Integrity
7.2.5 General Constraints
7.3 Data Definition
7.3.1 Creating a Database
7.3.2 Creating a Table (CREATE TABLE)
7.3.3 Changing a Table Definition (ALTER TABLE)
7.3.4 Removing a Table (DROP TABLE)
7.3.5 Creating an Index (CREATE INDEX)
7.3.6 Removing an Index (DROP INDEX)
7.4 Views
7.4.1 Creating a View (CREATE VIEW)
7.4.2 Removing a View (DROP VIEW)
7.4.3 View Resolution
7.4.4 Restrictions on Views
7.4.5 View Updatability
7.4.6 WITH CHECK OPTION
7.4.7 Advantages and Disadvantages of Views
7.4.8 View Materialization
7.5 Transactions
7.5.1 Immediate and Deferred Integrity Constraints
7.6 Discretionary Access Control
7.6.1 Granting Privileges to Other Users (GRANT)
7.6.2 Revoking Privileges from Users (REVOKE)
Chapter Summary
Review Questions
Exercises
Chapter 8 Advanced SQL
8.1 The SQL Programming Language
8.1.1 Declarations
8.1.2 Assignments
8.1.3 Control Statements
8.1.4 Exceptions in PL/SQL
8.1.5 Cursors in PL/SQL
8.2 Subprograms, Stored Procedures, Functions, and Packages
8.3 Triggers
8.4 Recursion
Chapter Summary
Review Questions
Exercises
Chapter 9 Object-Relational DBMSs
9.1 Advanced Database Applications
9.2 Weaknesses of RDBMSs
9.3 Storing Objects in a Relational Database
9.3.1 Mapping Classes to Relations
9.3.2 Accessing Objects in the Relational Database
9.4 Introduction to Object-Relational Database Systems
9.5 SQL:2011
9.5.1 Row Types
9.5.2 User-Defined Types
9.5.3 Subtypes and Supertypes
9.5.4 User-Defined Routines
9.5.5 Polymorphism
9.5.6 Reference Types and Object Identity
9.5.7 Creating Tables
9.5.8 Querying Data
9.5.9 Collection Types
9.5.10 Typed Views
9.5.11 Persistent Stored Modules
9.5.12 Triggers
9.5.13 Large Objects
9.5.14 Recursion
9.6 Object-Oriented Extensions in Oracle
9.6.1 User-Defined Data Types
9.6.2 Manipulating Object Tables
9.6.3 Object Views
9.6.4 Privileges
Chapter Summary
Review Questions
Exercises
Part 3 Database Analysis and Design
Chapter 10 Database System Development Lifecycle
10.1 The Information Systems Lifecycle
10.2 The Database System Development Lifecycle
10.3 Database Planning
10.4 System Definition
10.4.1 User Views
10.5 Requirements Collection and Analysis
10.5.1 Centralized Approach
10.5.2 View Integration Approach
10.6 Database Design
10.6.1 Approaches to Database Design
10.6.2 Data Modeling
10.6.3 Phases of Database Design
10.7 DBMS Selection
10.7.1 Selecting the DBMS
10.8 Application Design
10.8.1 Transaction Design
10.8.2 User Interface Design Guidelines
10.9 Prototyping
10.10 Implementation
10.11 Data Conversion and Loading
10.12 Testing
10.13 Operational Maintenance
10.14 CASE Tools
Chapter Summary
Review Questions
Exercises
Chapter 11 Database Analysis and the DreamHome Case Study
11.1 When Are Fact-Finding Techniques Used?
11.2 What Facts Are Collected?
11.3 Fact-Finding Techniques
11.3.1 Examining Documentation
11.3.2 Interviewing
11.3.3 Observing the Enterprise in Operation
11.3.4 Research
11.3.5 Questionnaires
11.4 Using Fact-Finding Techniques: A Worked -Example
11.4.1 The DreamHome Case Study-An Overview of the Current System
11.4.2 The DreamHome Case Study-Database Planning
11.4.3 The DreamHome Case Study-System Definition
11.4.4 The DreamHome Case Study-Requirements Collection and Analysis
11.4.5 The DreamHome Case Study-Database Design
Chapter Summary
Review Questions
Exercises
Chapter 12 Entity-Relationship Modeling
12.1 Entity Types
12.2 Relationship Types
12.2.1 Degree of Relationship Type
12.2.2 Recursive Relationship
12.3 Attributes
12.3.1 Simple and Composite Attributes
12.3.2 Single-valued and Multi-valued Attributes
12.3.3 Derived Attributes
12.3.4 Keys
12.4 Strong and Weak Entity Types
12.5 Attributes on Relationships
12.6 Structural Constraints
12.6.1 One-to-One (1:1) Relationships
12.6.2 One-to-Many (1:*) Relationships
12.6.3 Many-to-Many (*:*) Relationships
12.6.4 Multiplicity for Complex Relationships
12.6.5 Cardinality and Participation Constraints
12.7 Problems with ER Models
12.7.1 Fan Traps
12.7.2 Chasm Traps
Chapter Summary
Review Questions
Exercises
Chapter 13 Enhanced Entity-Relationship Modeling
13.1 Specialization/Generalization
13.1.1 Superclasses and Subclasses
13.1.2 Superclass/Subclass Relationships
13.1.3 Attribute Inheritance
13.1.4 Specialization Process
13.1.5 Generalization Process
13.1.6 Constraints on Specialization/Generalization
13.1.7 Worked Example of using Specialization/ Generalization to Model the Branch View of the DreamHome Case Study
13.2 Aggregation
13.3 Composition
Chapter Summary
Review Questions
Exercises
Chapter 14 Normalization
14.1 The Purpose of Normalization
14.2 How Normalization Supports Database Design
14.3 Data Redundancy and Update Anomalies
14.3.1 Insertion Anomalies
14.3.2 Deletion Anomalies
14.3.3 Modification Anomalies
14.4 Functional Dependencies
14.4.1 Characteristics of Functional Dependencies
14.4.2 Identifying Functional Dependencies
14.4.3 Identifying the Primary Key for a Relation Using Functional Dependencies
14.5 The Process of Normalization
14.6 First Normal Form (1NF)
14.7 Second Normal Form (2NF)
14.8 Third Normal Form (3NF)
14.9 General Definitions of 2NF and 3NF
Chapter Summary
Review Questions
Exercises
Chapter 15 Advanced Normalization
15.1 More on Functional Dependencies
15.1.1 Inference Rules for Functional Dependencies
15.1.2 Minimal Sets of Functional Dependencies
15.2 Boyce-Codd Normal Form (BCNF)
15.2.1 Definition of BCNF
15.3 Review of Normalization Up to BCNF
15.4 Fourth Normal Form (4NF)
15.4.1 Multi-Valued Dependency
15.4.2 Definition of Fourth Normal Form
15.5 Fifth Normal Form (5NF)
15.5.1 Lossless-Join Dependency
15.5.2 Definition of Fifth Normal Form
Chapter Summary
Review Questions
Exercises
Part 4 Methodology
Chapter 16 Methodology-Conceptual Database Design
16.1 Introduction to the Database Design Methodology
16.1.1 What Is a Design Methodology?
16.1.2 Conceptual, Logical, and Physical Database Design
16.1.3 Critical Success Factors in Database Design
16.2 Overview of the Database Design Methodology
16.3 Conceptual Database Design Methodology
Step 1: Build Conceptual Data Model
Chapter Summary
Review Questions
Exercises
Chapter 17 Methodology-Logical Database Design for the Relational Model
17.1 Logical Database Design Methodology for the Relational Model
Step 2: Build Logical Data Model
Chapter Summary
Review Questions
Exercises
Chapter 18 Methodology-Physical Database Design for Relational Databases
18.1 Comparison of Logical and Physical Database Design
18.2 Overview of the Physical Database Design Methodology
18.3 The Physical Database Design Methodology for Relational Databases
Step 3: Translate Logical Data Model for Target DBMS
Step 4: Design File Organizations and Indexes
Step 5: Design User Views
Step 6: Design Security Mechanisms
Chapter Summary
Review Questions
Exercises
Chapter 19 Methodology-Monitoring and Tuning the Operational System
19.1 Denormalizing and Introducing Controlled Redundancy
Step 7: Consider the Introduction of Controlled Redundancy
19.2 Monitoring the System to Improve Performance
Step 8: Monitor and Tune the Operational System
Chapter Summary
Review Questions
Exercises
Part 5 Selected Database Issues
Chapter 20 Security and Administration
20.1 Database Security
20.1.1 Threats
20.2 Countermeasures-Computer-Based Controls
20.2.1 Authorization
20.2.2 Access Controls
20.2.3 Views
20.2.4 Backup and Recovery
20.2.5 Integrity
20.2.6 Encryption
20.2.7 RAID (Redundant Array of Independent Disks)
20.3 Security in Microsoft Office Access DBMS
20.4 Security in Oracle DBMS
20.5 DBMSs and Web Security
20.5.1 Proxy Servers
20.5.2 Firewalls
20.5.3 Message Digest Algorithms and Digital Signatures
20.5.4 Digital Certificates
20.5.5 Kerberos
20.5.6 Secure Sockets Layer and Secure HTTP
20.5.7 Secure Electronic Transactions and Secure Transaction Technology
20.5.8 Java Security
20.5.9 ActiveX Security
20.6 Data Administration and Database Administration
20.6.1 Data Administration
20.6.2 Database Administration
20.6.3 Comparison of Data and Database Administration
Chapter Summary
Review Questions
Exercises
Chapter 21 Professional, Legal, and Ethical Issues in Data Management
21.1 Defining Legal and Ethical Issues in IT
21.1.1 Defining Ethics in the Context of IT
21.1.2 The Difference Between Ethical and Legal Behavior
21.1.3 Ethical Behavior in IT
21.2 Legislation and Its Impact on the IT Function
21.2.1 Securities and Exchange Commission (SEC) Regulation National Market System (NMS)
21.2.2 The Sarbanes-Oxley Act, COBIT, and COSO
21.2.3 The Health Insurance Portability and Accountability Act
21.2.4 The European Union (EU) Directive on Data Protection of 1995
21.2.5 The United Kingdom's Data Protection Act of 1998
21.2.6 Access to Information Laws
21.2.7 International Banking-Basel II Accords
21.3 Establishing a Culture of Legal and Ethical Data Stewardship
21.3.1 Developing an Organization-Wide Policy for Legal and Ethical Behavior
21.3.2 Professional Organizations and Codes of Ethics
21.3.3 Developing an Organization-Wide Policy for Legal and Ethical Behavior for DreamHome
21.4 Intellectual Property
21.4.1 Patent
21.4.2 Copyright
21.4.3 Trademark
21.4.4 Intellectual Property Rights Issues for Software
21.4.5 Intellectual Property Rights Issues for Data
Chapter Summary
Review Questions
Exercises
Chapter 22 Transaction Management
22.1 Transaction Support
22.1.1 Properties of Transactions
22.1.2 Database Architecture
22.2 Concurrency Control
22.2.1 The Need for Concurrency Control
22.2.2 Serializability and Recoverability
22.2.3 Locking Methods
22.2.4 Deadlock
22.2.5 Timestamping Methods
22.2.6 Multiversion Timestamp Ordering
22.2.7 Optimistic Techniques
22.2.8 Granularity of Data Items
22.3 Database Recovery
22.3.1 The Need for Recovery
22.3.2 Transactions and Recovery
22.3.3 Recovery Facilities
22.3.4 Recovery Techniques
22.3.5 Recovery in a Distributed DBMS
22.4 Advanced Transaction Models
22.4.1 Nested Transaction Model
22.4.2 Sagas
22.4.3 Multilevel Transaction Model
22.4.4 Dynamic Restructuring
22.4.5 Workflow Models
22.5 Concurrency Control and Recovery in Oracle
22.5.1 Oracle's Isolation Levels
22.5.2 Multiversion Read Consistency
22.5.3 Deadlock Detection
22.5.4 Backup and Recovery
Chapter Summary
Review Questions
Exercises
Chapter 23 Query Processing
23.1 Overview of Query Processing
23.2 Query Decomposition
23.3 Heuristical Approach to Query Optimization
23.3.1 Transformation Rules for the Relational Algebra Operations
23.3.2 Heuristical Processing Strategies
23.4 Cost Estimation for the Relational Algebra Operations
23.4.1 Database Statistics
23.4.2 Selection Operation (S = sp(R))
23.4.3 Join Operation (T = (R 1F S))
23.4.4 Projection Operation (S = pA1, A2, . . . , A m(R))
23.4.5 The Relational Algebra Set Operations (T = R ? S, T = R > S, T = R - S)
23.5 Enumeration of Alternative Execution Strategies
23.5.1 Pipelining
23.5.2 Linear Trees
23.5.3 Physical Operators and Execution Strategies
23.5.4 Reducing the Search Space
23.5.5 Enumerating Left-Deep Trees
23.5.6 Semantic Query Optimization
23.5.7 Alternative Approaches to Query Optimization
23.5.8 Distributed Query Optimization
23.6 Query Processing and Optimization
23.6.1 New Index Types
23.7 Query Optimization in Oracle
23.7.1 Rule-Based and Cost-Based Optimization
23.7.2 Histograms
23.7.3 Viewing the Execution Plan
Chapter Summary
Review Questions
Exercises
Part 6 Distributed DBMSs and Replication
Chapter 24 Distributed DBMSs-Concepts and Design
24.1 Introduction
24.1.1 Concepts
24.1.2 Advantages and Disadvantages of DDBMSs
24.1.3 Homogeneous and Heterogeneous DDBMSs
24.2 Overview of Networking
24.3 Functions and Architectures of a DDBMS
24.3.1 Functions of a DDBMS
24.3.2 Reference Architecture for a DDBMS
24.3.3 Reference Architecture for a Federated MDBS
24.3.4 Component Architecture for a DDBMS
24.4 Distributed Relational Database Design
24.4.1 Data Allocation
24.4.2 Fragmentation