Database Design and SQL for DB2

✍ Scribed by James Cooper

• Publisher: MC Press
• Year: 2013
• Tongue: English
• Leaves: 836
• Category: Library

✦ Synopsis

Thorough and updated coverage of database design and SQL for DB2 are the focus of this guide for the relational database-management system used on IBM i computer systems. Suitable for classroom instruction or self-study, this book explains the most widely used database language and the way that language is implemented on a variety of computer platforms. Topics covered include database concepts, SQL inquiries, web applications, and database security, and the material is reinforced by numerous illustrations, examples, and exercises.

✦ Table of Contents

Title
Copyright
Contents
Introduction
Intended Audience
Companion Website
Instructors
Students
Contributors
Chapter 1: Database Concepts
Chapter Objectives
Introduction to Database and Database Management System
Relational Database Model
The DB2 Database
Database Terminology
The Importance of Database Design
Database Development Process
Database Planning
Requirements Analysis
Database Design
DBMS Selection
Database Implementation
Testing and Evaluation
Database Maintenance
Operation
End-of-Chapter
Chapter Summary
Key Terms
Chapter 2: Conceptual Design Using Er Diagrams
Introduction to Database Design
Developing Entity Relationship Diagrams
ERD Case Study
Step 1: Identify Entities
Step 2: Identify Attributes
Step 3: Identify Unique Identifier (UID)
Step 4: Determine Relationships
Step 5: Determine Optionality and Cardinality
Step 6: Eliminate Many-to-Many Relationships
Step 7: Named Relationships
Step 8: Determine Data Types
Recursive Relationship
Entity Subtypes
End-of-Chapter
Chapter Summary
Key Terms
Chapter 3: Normalization
Normalization
Normal Forms
Representing Database Tables
Functional Dependency
First Normal Form (1NF)
Second Normal Form (2NF)
Third Normal Form (3NF)
Boyce-Codd Normal Form (BcNF)
Fourth Normal Form (4NF)
Practical Example
First Normal Form (1NF)
Second Normal Form (2NF)
Third Normal Form (3NF)
End-of-Chapter
Chapter Summary
Key Terms
Chapter 4: Physical Database Design: Creating Tables
Physical Database Design
Transforming Conceptual Design to Physical Design
Primary, Candidate, and Foreign Keys
Specify View Implementation
Specify Security Implementation
Specifying Additional Indexes for Performance
Hierarchy of Data
Variables
Database, Tables, Rows, and Columns
Internal Binary Representation of Data
Data Types
Character Data Type
Numeric Data Types
Simulating a Boolean Data Type
Date Format
Timestamp Fields
Sample Data From a Table
Introduction to SQL
Running SQL Commands
Editor Pane
SQL Results Pane
Creating a Schema
Changing the Default Schema
Creating a Table
CREATE TABLE Command
Verify Syntax of SQL Script
Run SQL Script
Constraints
Qualified Names
Comments
NULL Values
Default Values
VARCHAR Data Type
ALTER Table Command
DROP (Delete) Table Command
Saving SQL Scripts
Edit SQL Scripts
Adding Data to a Table
The INSERT Command
Displaying Data in a Table
Display Table Description Information
Rename a Database Object
End-of-Chapter
Chapter Summary
Key Terms
Chapter 5: Database Constraints
Introduction to Constraints
Data Integrity
Entity Integrity
Referential Integrity
Constraint Types
Primary Key Constraints
Unique Constraints
Foreign Key Constraints
Defining Foreign Key Constraints
Foreign Key Actions
Additional Foreign Key Constraint Considerations
Avoid Foreign Key Constraints for Read-Only Tables
Check Constraints
Check Constraint Guidelines
Defining Check Constraints
Representing Boolean Data Types
Beware of Semantics with Check Constraints
Defining Check Constraints at the Table Level
Adding and Removing Check Constraints From an Existing Table
End-of-Chapter
Chapter Summary
Key Terms
Chapter 6: Single-Table Queries
Basic Format of the SELECT Statement
SELECT One Column From a Table
SELECT Multiple Columns From a Table
NULL Values
DISTINCT Keyword
Column Aliases
CONCAT Operator
Using Literals
Computed Columns
WHERE Clause
Simple Search Conditions
Using WHERE with a Primary Key
Null Condition in WHERE Clause
Using a Computed Value with a WHERE Clause
Using Compound Conditions
Negating a Condition
Specifying Order of Evaluation
BETWEEN Operator
LIKE Operator
IN Operator
ORDER BY Clause
Using a Relative Column Number
Functions
Character Functions
Numeric Functions
Aggregate Functions
CASE Function
Date and Time Arithmetic
GROUP BY and HAVING Clauses
Coding Suggestions
End-of-Chapter
Chapter Summary
Key Terms
Chapter 7: Updating Tables
Modifying Table Data
INSERT Statement
Explicit Column Names
Implicit Column Names
Explicit DEFAULT with UPDATE
Inserting Rows with NULL Values
Specifying a Default Date
Multiple-Row INSERT Statement
UPDATE Statement
Updating a Column with a Value From a Subquery
SET Clause Variations
Integrity Constraint Errors
DELETE Statement
Coding Suggestions
End-of-Chapter
Chapter Summary
Key Terms
Chapter 8: Multiple-Table Queries
Introduction to Joins
Join Types
Joins and Keys
Inner Join
Using INNER JOIN/ON Clauses
Using the WHERE Clause for Inner Join
Alias Names
Using the WHERE Clause with an INNER JOIN
Outer Join
Left Outer Join
Right Outer Join
Full Outer Join
Exception Join
Left Exception Join
Right Exception Join
Cross Join
Self-Join
SET Operators
The UNION Operator
The UNION ALL Operator
Using a Select List with a UNION Operator
Joining Several Tables
End-of-Chapter
Chapter Summary
Key Terms
Chapter 9: Subqueries
Introduction to Subqueries
Single-Row or Single-Value Subquery
Multiple-Row Subqueries
The IN Operator
ALL and ANY Operators
Correlated Subqueries
Using the WHERE Clause with a Correlated Subquery
EXISTS Operator
NOT EXISTS Operator
Combining Subqueries and Joins
Using a Nested View
Named Query Blocks Using the WITH Keyword
End-of-Chapter
Chapter Summary
Key Terms
Chapter 10: Views and Indexes
What is a View?
Creating a View
Renaming Columns in a View
Restricting Rows with a WHERE Clause
Using a WHERE Clause with a View
Using a View to Update Data
Using GROUP BY with a View
Using Joins with a View
WITH CHECK OPTION and WITH LOCAL CHECK OPTION
More View Examples
Indexes
Accessing Data Using an Index
Creating an Index
Dropping an Index
End-of-Chapter
Chapter Summary
Key Terms
Chapter 11: Embedded SQL
Introduction to Embedded SQL
Host Variables
Using Comments with Embedded SQL
SQL Error Handling
The SQL Communication Area
MonitorSQL Procedure
SELECT into Statement
INSERT Statement
UPDATE Statement
DELETE Statement
NULL Values
Null Indicators
Using an Array for NULL Indicators
Coding Suggestions
End-of-Chapter
Chapter Summary
Key Terms
Chapter 12: SQL Cursors
Basic Elements of SQL Cursors
Cursor
Result Set
Defining and Using a Cursor
Step 1: DECLARE CURSOR
Step 2: OPEN Statement
Step 3: FETCH Statement
Step 4: CLOSE Statement
Using Clauses with the Cursor Declaration
FOR READ ONLY Clause
FOR UPDATE OF Clause
INSENSITIVE Clause
WITH HOLD Clause
OPTIMIZE Clause
Reading by Key Value
Scrollable Cursors
FETCH Without INTO Clause
Positioned UPDATE and DELETE Statements
Web Application SQL1201
Program SQL1201
Declarecursor Procedure
Open the Cursor
Fetchnext Procedure
MonitorSQL Procedure
Process Multiple Rows From the Cursor
Close Cursor
Dynamic SQL Statements
Dynamic Cursors
Web Application SQL1202
Declarecursor Procedure
Coding Suggestions
End-of-Chapter
Chapter Summary
Key Terms
Chapter 13: The Create SQL Program Command
Creating SQL Programs
CRTSQLRPGI Command Parameters
The SQL Translation Process
Precompilation Step
Compilation Step
Coding Suggestions
End-of-Chapter
Chapter Summary
Key Terms
Chapter 14: Stored Procedures
Stored Procedures
SQL Procedural Language
An Introduction to User-Defined Functions: Sourced Functions
User-Defined Functions
End-of-Chapter
Chapter Summary
Key Terms
Chapter 15: Triggers
Introduction to Database Triggers
Adding a Trigger
Adding a Trigger Using IBM Navigator for I
The Add Trigger Command
Removing Triggers
The Remove Trigger Command
Coding a Trigger Program
Soft-Coding the Trigger Buffer
Considerations for Using Trigger Programs
Interaction of Triggers with Constraints
Coding Suggestions
End-of-Chapter
Chapter Summary
Key Terms
Chapter 16: Additional SQL Topics
INCLUDE Statement
Object and Row Locks and the LOCK TABLE Statement
Object Locks
The LOCK TABLE Statement
Row Locks
Transaction Integrity and the COMMIT and ROLLBACK Statements
Determining Which Commitment Control Environment is Used
The COMMIT and ROLLBACK Statements
Isolation Levels
Row Locking with Commitment Control
A Guide to SQL Naming
SQL Object Names
Qualified Names and Uniqueness
End-of-Chapter
Chapter Summary
Key Terms
Chapter 17: Database Security and the GRANT and REVOKE Statements
Security Basics
Object Ownership
Authorities
Public Authority
Default Public Authority for New Objects Created with a CRTxxx Command
Default Public Authority for New Objects Created with an SQL Create Statement
SQL Privileges
Accessing the Main Database Objects
Schemas
Tables and Views
Distinct Types
Stored Procedures, User-Defined Functions, and Packages
Programs and Service Programs
Column-Level Privileges
The GRANT and REVOKE Statements
Granting Table and View Privileges
Revoking Table and View Privileges
Granting and Revoking Privileges on Other Database Objects
Group Profiles
Authorization Lists
Program Adopted Authority
Database Security Principles and Guidelines
End-of-Chapter
Chapter Summary
Key Terms
Index