MC Press Online
Welcome to the MC Press Online!
Need help withour eBooks?
Click here, to go to our main store

  MC Press Online eBookStore  

Database Design and SQL for DB2
preview of book Database Design and SQL for DB2
text of book Database Design and SQL for DB2

Database Design and SQL for DB2

Publisher: MC Press Online
Publication Date: 2013
Number of Pages: 505

Free Preview    Email to Friend   Add to wish list
 Available as: (for format`s description click on its name)
Individual E-Version (PDF) Individual E-Version (PDF) ISBN: 978-1-58347-357-3  
 Reg.: $
79.95 per N pages
 You Save: 
$18.78 (23%)
 Online  Open CopyPrint    
all time
Printed Edition   see MC Press Online    
About this title
Database Design and SQL for DB2 engages readers with a hands-on approach that provides start-to-finish coverage of database design and SQL, IBM's strategic language for the IBM i integrated database.

This book is designed for professional application developers and college-level students who want to become developers. This is also an ideal book for developers coming from other database platforms. For many developers, this book can serve in place of IBM's database-related manuals. All the essential SQL and related DB2 topics are covered, from an introductory to an advanced level.

Each chapter includes comprehensive explanations and numerous examples. In addition, the companion website for this book provides additional examples of embedded SQL as well as using client-side technologies such as jQuery, JSON, and AJAX with RPG web applications.

You will find Database Design and SQL for DB2 immediately useful, whether you are just beginning to learn SQL or you are an experienced developer. And you will turn to this book time and time again for quick answers to the best way to design and implement database applications.

With Database Design and SQL for DB2, you will:
  • Model and design databases using entity relationship (ER) diagrams
  • Normalize database tables
  • Implement physical database tables
  • Define referential constraints, primary and unique key indexes, and check constraints to enforce data integrity
  • Use simple and complex SQL statements on single and multiple tables
  • Use the SELECT, UPDATE, INSERT, and DELETE SQL statements
  • Create database tables, views, and indexes
  • Develop RPG web applications that incorporate embedded SQL
  • Incorporate jQuery, JSON, and AJAX into RPG web applications
About author
James Cooper
James Cooper has spent his entire career on IBM systems, including the System/34, System/36, AS/400, and now the System i. He worked as a software developer for many years before moving to the education sector.

For more than 25 years, Jim has been Program Coordinator and Professor in the Computer Studies Department at Lambton College in Sarnia, Ontario, Canada, where he teaches database design, SQL, programming, and Web technologies. Currently, his main focus is client-side Web technologies, including AJAX, JSON, and jQuery Frameworks with RPG server-side programming.

Jim has presented at COMMON and other user group meetings and technical conferences. From 1995 through 2001, he served as Director of the IBM Roundtable College Conference, an IBM international conference held annually for colleges teaching IBM i technologies. As Director of this conference, he received an honorary award from IBM for his efforts with the IBM Academic Initiative Program and the Roundtable College Conference.

Jim has also authored two textbooks on RPG and COBOL programming with John Wiley & Sons Publishing.

Intended Audience
Companion Website

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
  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
  Chapter Summary
  Key Terms

Chapter 3: 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)
  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
  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
  Verify Syntax of SQL Script
  Run SQL Script
  Qualified Names
  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
  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
  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
  Using a Relative Column Number
  Character Functions
  Numeric Functions
  Aggregate Functions
  CASE Function
  Date and Time Arithmetic
Coding Suggestions
  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
  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
SET Operators
  The UNION Operator
  The UNION ALL Operator
  Using a Select List with a UNION Operator
Joining Several Tables
  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
  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
More View Examples
  Accessing Data Using an Index
  Creating an Index
  Dropping an Index
  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
INSERT Statement
UPDATE Statement
DELETE Statement
NULL Values
  Null Indicators
  Using an Array for NULL Indicators
Coding Suggestions
  Chapter Summary
  Key Terms

Chapter 12: SQL Cursors
Basic Elements of SQL Cursors
  Result Set
Defining and Using a Cursor
  Step 2: OPEN Statement
  Step 3: FETCH Statement
  Step 4: CLOSE Statement
Using Clauses with the Cursor Declaration
  WITH HOLD 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
  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
  Chapter Summary
  Key Terms

Chapter 14: Stored Procedures
Stored Procedures
  SQL Procedural Language
An Introduction to User-Defined Functions: Sourced Functions
  User-Defined Functions
  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
  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
  Chapter Summary
  Key Terms

Chapter 17: Database Security and the GRANT and REVOKE Statements
Security Basics
Object Ownership
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
  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
  Chapter Summary
  Key Terms

Related titles
Mastering IBM iMastering IBM i
Programming in RPG IVProgramming in RPG IV
SQL Built-In Functions and Stored ProceduresSQL Built-In Functions and Stored Procedures
SQL for eServer i5 and iSeriesSQL for eServer i5 and iSeries
  Special Offer Code  
Enter your Special Offer Code here:
  Search for  

  Our Products  
Browse all »»
Functions in Free-Format RPG IV
HTML for the Business Developer
Advanced, Integrated RPG

If download option is selected, Adobe Acrobat 5.0 or lateris requiredto read our e-books*

*Windows PC, Mac OS9/OSX, and Linux