Database Design and SQL for DB2

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. 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 This book is designed for professional application developers and college-level students who want to become developers. It features thorough and updated coverage of database design and SQL for DB2. Topics covered include database concepts, SQL inquiries, web applications, and database security. The material is reinforced by numerous illustrations, examples, and exercises