Database Design and SQL for DB2

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. CA Database Design and SQL for DB2 By James Cooper MC Press Copyright © 2013 James Cooper All rights reserved. ISBN: 978-1-58347-357-3 Contents Introduction, Chapter 1: Database Concepts, Chapter 2: Conceptual Design Using ER Diagrams, Chapter 3: Normalization, Chapter 4: Physical Database Design: Creating Tables, Chapter 5: Database Constraints, Chapter 6: Single-Table Queries, Chapter 7: Updating Tables, Chapter 8: Multiple-Table Queries, Chapter 9: Subqueries, Chapter 10: Views and Indexes, Chapter 11: Embedded SQL, Chapter 12: SQL Cursors, Chapter 13: The Create SQL Program Command, Chapter 14: Stored Procedures, Chapter 15: Triggers, Chapter 16: Additional SQL Topics, Chapter 17: Database Security and the GRANT and REVOKE Statements, Index, CHAPTER 1 DATABASE CONCEPTS Chapter Objectives Upon completion of this chapter, you should be able to: • Define database and database management system • Describe the steps for the database development process • Explain the purpose of conceptual, logical, and physical database design • Explain the relational database model Introduction To Database and Database Management System A database management system (DBMS) is a set of software programs that control the organization, storage, and retrieval of data in a database. A database is an organized collection of related tables that can be joined to provide information to users. Common relational database management systems that include Structured Query Language (SQL) are IBM® DB2®, Microsoft SQL Server®, MySQL, Oracle®, Sybase, and others. A DBMS is a software system that uses a standard method of cataloging, retrieving, and running queries on data. The DBMS manages incoming data, organizes it, and provides ways for the data to be modified or extracted by users or other programs. A DBMS lets users create and access data in a database. The DBMS manages database requests so users and programs do not have to understand where the data are physically located on disk or who else may be accessing the data. In handling database requests, the DBMS ensures the integrity of the data; that is, it makes sure the data are available and are organized as intended. In addition, the DBMS provides security by ensuring that only those users and programs with access privileges can access the data. Relational Database Model The foundation of most commercial database management systems today is the relational database model, which was first introduced in the paper "A Relational Model of Data for Large Shared Data Banks," published by Edgar Codd in 1970 [Communications of the ACM 13 (6): 377–387]. Since the publication of this paper, the relational model has been developed extensively into common relational database management systems such as DB2, Microsoft SQL Server, MySQL, Oracle, and others. In addition to relational databases, there are also hierarchical, network, and object-oriented databases. THE DB2 DATABASE DB2 refers to an entire family of IBM databases: • DB2 for i • DB2 for z/OS • DB2 for Linux, UNIX and Windows (also referred to as DB2 LUW) This book uses the IBM database management system DB2 for i, which is referred to simply as "DB2" throughout the book. DB2 for i is an integrated part of IBM i, the operating system that runs IBM's System i® hardware platform. DB2 is not purchased as a separate software product, and any application can take advantage of its features. DB2 provides a DBMS that all computer programs use to access data stored in DB2 databases. As mentioned, a database is an organized collection of related tables that can be joined to provide information to users. In the DB2 database approach, tables are defined independently of any applications that use them. Data remains in independent, simple, linear tables, but indexes are created that indicate how the rows of the different tables relate to each other in the sense of the join concept. These indexes can be created before any queries are run, or they can be created at run time as the join query is executed. Database Terminology There are two groups of terms used interchangeably when discussing databases. The reason there are two sets of terms is that before the use of SQL, people used traditional system terms. With SQL, a new set of terms more representative of a typical spreadsheet type of relational table became more common. Thus, the non-SQL world sometimes uses di