Creating a Simple Database

[ Team LiB ]

Creating a Simple Database First, you create a simple database where you accept most of the available defaults. To create a database with the Create Database Wizard, follow these steps: Tip You must have SYSADM authority to create a database. Your username must belong to the Administrators group to have SYSADM authority. Start the Control Center, if not already started. As you learned earlier, to start the Control Center, choose Start | Programs|IBM DB2| General Administration Tools | Control Center. Expand the object folders until you see the Databases folder. Right-click the folder and select Create from the pop-up menu. The Create Database Wizard appears, open at the first page (see Figure 9.1). Figure 9.1. The Create Database Wizard. Fill in the first page as follows: Database Name� Enter a name to identify your database. This name must be different from any other database names cataloged on this drive. The name must contain no more than eight characters; use characters A through Z, 0 through 9, @, #, or $; and can't begin with a number. For this example, enter CDLIB. Tip If you're planning to use the database in a communications environment or outside North America, avoid using the @, #, and $ symbols in the name. Default Drive� Select the drive that you want to use. Make sure that you'll have enough space on this drive to contain all the data you'll eventually have in your database. All associated database files will be stored in this drive. (Later, you'll see how to create and spread data over table spaces.) Tip To estimate the size of a database, you must include the size of the system catalog tables (initially 1600KB, but grow as objects and privileges are added to the database), the size of each user table in the database, the index space, the log file space, and the temporary work space. Alias� An alias is a nickname given to the database. If you leave this text box blank, it will default to the name of the database. Leave this blank so that it will default to CDLIB. Comment� In this text box, enter an optional description for your database. The comment can contain up to 30 characters. Enter a comment such as Library of CDs. Click the Finish button to create the database. (Pages 2 through 7 are more advanced topics and are covered in the next section.) After a few minutes, you'll get a message stating that the database is created and that you should run the Configuration Advisor Wizard to tune your database. Click No to skip this step. This topic is covered in the next section. Check the main panel of the Control Center to see a folder added for the CDLIB database. At this point, the database is empty. To make it useful, you need to create tables, add data, and set up referential constraints. Creating Tables You can create tables with the Control Center through the Create Table Wizard. The Create Table Wizard allows you to create simple tables as well as more complicated ones with referential constraints. For this example, you'll use the Create Table Wizard to create a simple table and add the constraints in a later step. To create the CATEGORY table, which will contain the music categories of the CDs in this collection, follow these steps: Start the Control Center, if it's not already started. Expand the object folders under the CDLIB database folder until you see the Tables folder. Right-click that folder. Select Create from the pop-up menu to start the Create Table Wizard (see Figure 9.2). Figure 9.2. The Create Table Wizard's Name page. On the Name page, enter the following information: Table Schema� Specify the schema for the table you're creating. The initial value is the username that you're using. My username is SVISSER, so tables I create with my username are named SVISSER.TABLE_NAME. This is fine if you're the only user of the database. You might want to have a more universal schema name such as a department or function name. For example, the Payroll department might want to use the schema PAYROLL instead of someone's username. Select an existing schema or type the name of a new schema. (You must have IMPLICIT_SCHEMA authority to be able to create new schemas.) Rather than use my username, I use ADMINISTRATOR. Table Name� The name of your table must be unique within the schema and can contain up to 128 characters and cannot be SYSIBM, SYSCAT, SYSFUN, or SYSSTAT. For this example, name the table CATEGORY. Comment� In this text box, enter an optional description for your table. The comment can contain up to 30 characters. Click Next to continue. On the Columns page, you can click the Add Predefined button to add a column from the list of predefined columns on the Select Columns dialog box to use for your table (see Figure 9.3). For example, if you're keeping a list of addresses, click the Addresses item in the Column Categories list to see all the predefined columns for an address list. Each column is defined with preset column sizes and characteristics. The selected column moves to the Columns to Create list. Figure 9.3. Use the Select Columns dialog box to add a predefined column to your table. For this example, you want to create two columns that aren't in any of these predefined lists. To create these, click the Add button on the Columns page of the Create Table Wizard. This opens the Add Column dialog box, as shown in Figure 9.4. Figure 9.4. The Add Column dialog box of the Table Wizard. On the Add Column dialog box, you must define the characteristics of the new column. For our example, enter the details as follows: Tip Plan carefully when setting the characteristics for each column. Most columns can't easily be altered after you finish creating the table unless you drop the table and re-create it. Column Name� Enter a unique name for the column. The name can contain up to 30 characters. For this example, enter CATEGORYID. Data Type� Many data types are available in DB2, and you also can make your own unique data types. Examples of some predefined data types include INTEGER, BIGINT, SMALLINT, DOUBLE, DECIMAL, CHARACTER, and VARCHAR. For this example, select SMALLINT because you want CATEGORYID to be a small integer. Data Type Characteristics� The data type characteristics that you need to define depend on the data type that you selected. You selected the SMALLINT data type, so you have no additional characteristics to define. Default� You can enter an optional value that you want used as a default whenever a value isn't entered. For example, if for a field named COUNTRY the most common entry is UNITED STATES, you should set the default to this value so that when you don't enter a value, DB2 will fill in this value. For this example, leave it blank. Nullable� Select the Nullable check box if you want to allow this field to be blank. Nullable is the default. For this example, you want CATEGORYID to be the primary key, so clear the Nullable check box. Tip If the column will be a primary key column, it can't be nullable. A primary key cannot be nullable because the primary key must exist in every row in the table. This guarantees that the row can be found. Click OK when finished. You'll return to the Columns page of the Create Table Wizard and see that the column has been added. Click Add to create a second column with the following information: Column Name� Enter the name DESCRIPTION for this column. Data Type� For this example, select VARCHAR because you want the description to contain characters and the length to be variable. Tip Using the VARCHAR data type is more efficient than using the CHARACTER data type to store character values. Because VARCHAR is variable, it uses only the necessary space to store the value, whereas the CHARACTER data type reserves a block of space for the value regardless of its actual size. Data Type Characteristics� An upper value must still be specified for VARCHAR. For a category description, 30 characters should be enough. Default� Don't define a default for this field. Nullable� Make the description field required even though it won't be the primary key. Click OK to add the column and to exit the Add Column dialog box. The second page of the Create Table Wizard now shows the two columns created as shown in Figure 9.5. Figure 9.5. The Create Table Wizard's Columns page. On the Table Space page, you can create table spaces for storing the table data. This topic is important for more sophisticated databases, but because you are creating a simple and small database, leave this topic for later. This topic is covered in Day 10, "Creating Table Spaces;" leave the default values for these fields for now and click Next to move to the Keys page. On the Keys page (see Figure 9.6), you can define a primary key, unique key, or foreign key for the new table. Figure 9.6. The Create Table Wizard's Keys page. Click Add Primary to create a primary key from the list of columns that you have in the table. In this example, you want to have CATEGORYID as the primary key. Move CATEGORYID into the Primary key columns list (see Figure 9.7) and click OK to create the primary key. You learn how to create a unique key and foreign key in a later section. Click Next to continue. Figure 9.7. The Define Primary Key dialog box launched from the Create Table Wizard's Keys page. Tip If the column selected to be the primary key was defined as nullable in the previous page, you'll get an error message saying that you can't use this column as part of the primary key. If you get this message, click Back to return to the previous page, select the column that you need to modify, click Change, and make the column not nullable. On the Dimensions page, you can set up data clustering. Defining more than one dimension on the table enables multidimensional clustering. For example, defining a dimension on a YEAR column and another on a MONTH column causes all data rows from the same year and month to be co-located on the disk. This can lead to performance gains for queries that involve YEAR, MONTH, or both. Click Next to continue. On the Constraints page, you can define check constraints, or business rules, that specify the values for every row in the table. Check constraints are checked whenever data is inserted or updated. This topic is covered later in today's lesson. Click Next to continue. On the Summary page, you are shown a summary of all the choices you made while using the Create Table Wizard. Click the Show SQL button to show the SQL code that will be used to create the table. Click Finish to create the table as defined in the summary page. If you want to look at your empty table, use the Control Center. Click the Tables folder in the main Control Center panel. In the right panel, you'll see all the tables now in the database. Most of these are DB2 system tables that you can view but can't change. Scroll down until you see the CATEGORY table just created. Right-click CATEGORY and choose Sample Contents from the pop-up menu. You'll see the two table columns that you added, with no data. Next, you'll add data to this table. Now close this window by clicking Close button. Adding Data to Tables You can add data to tables in several ways in DB2. Most likely you'll have an application from which you can enter data into DB2 databases. As mentioned in earlier lessons, you can create your own applications by using many popular programming languages such as C++, C, Visual Basic, Power Builder, or Java, or applications such as Microsoft Access to help you enter data. If you don't have an application, you can enter data by using the Command Center and simple SQL statements. Follow these steps: Start the Command Center. If it is not already open, choose Start | Programs| IBM DB2| Command Tools | Command Center to open it. In the main panel, connect to the CDLIB database, and enter the following commands in the Command section: insert into CATEGORY (CATEGORYID, DESCRIPTION) values (1, 'Rock'), (2, 'Classical'), (3, 'Country');

Click the gears button to process the command. The Results screen opens, showing that the command completed successfully.

Continue to add data in this way. Return to the Script screen, click the down arrow to recall the command, change the values as appropriate to add another row, and click the gears button again. (You need to connect to the database only once.)

To see the data that you've just entered, you can issue a SELECT statement from the Script screen:

select * from CATEGORY;

Click the gears button to see the data that you've entered (see Figure 9.8).

Figure 9.8. Sampling data in the Command Center

Setting Up Referential Integrity

A database usually isn't a simple table with a list of items, as you've created so far. It's usually made up of many tables, each containing normalized data.

As discussed on Day 8, normalizing helps you avoid redundancies and inconsistencies in your data. The main idea behind normalizing is to reduce tables to a set of columns where all the non-key columns depend on the entire primary key of the table.

Each table is linked to ensure that certain business rules are followed. These rules are normally called referential integrity. For example, in the CATEGORY table, you've created a list of music categories. Now create a table that lists the record company associated with an album and another table that lists the albums available on CD for each artist. You can even take this further and add a table that contains graphic files of the band or album cover, audio samples of the CD itself, and even video clips. Each table that you create will contain a certain type of information and will be connected to one or more of the tables with foreign keys.

To see how this is done, create ALBUM, a table that contains the name of the album and its year of release and length, along with relationships to the CATEGORY, RECORDLABEL, ALBUMCOVER, and GROUP tables.

Because you want to add foreign keys to the table as you define it, use the Create Table Wizard:

1. Start the Control Center, if it's not already started.

   
   

2. Expand the object folders under the CDLIB database folder until you see the Tables folder. Right-click that folder.

   
   

3. Select Create | Table from the pop-up menu to start the Create Table Wizard.

   
   

4. On the Name page, enter the following information:

   
   

   • Table Schema� Select ADMINISTRATOR from the list of existing schemas.

   • Table Name� For this example, name the table ALBUM.

   
   

5. Click Next to continue.

   
   

6. On the Columns page of the wizard, select from a list of predefined columns to use for your table. For this example, you want to create columns for ALBUMID, TITLE, RELEASEYEAR, CATEGORYID, RECORDLABELID, ALBUMCOVERID, and LENGTH.

   
   

7. Click the Add button to open the Add Column dialog box and fill in the details as follows:

   
   

   Column Name	Data Type	Nullable
   ALBUMID	SMALLINT	No
   TITLE	VARCHAR, 45	No
   RELEASEYEAR	SMALLINT	No
   CATEGORYID	SMALLINT	No
   RECORDLABELID	SMALLINT	No
   ALBUMCOVERID	SMALLINT	Yes
   LENGTH	SMALLINT	Yes

   
   

   See Figure 9.9 to see all the columns and their characteristics. Click the Keys page to continue.

   
   
   

   Figure 9.9. The Create Table Wizard's Columns page

   
   
   

   
   

   
   
   
   

8. On the Keys page, click Add Primary to define a primary key for the new table (see Figure 9.10). From the list of columns in the table, select the columns you want to have as a primary key. In this example, you want to have ALBUMID as the primary key, so move ALBUMID into the Key Columns list and click OK to continue.

   
   
   

   Figure 9.10. The Create Table Wizard's Define Primary Key dialog box.

   
   
   

   
   

   
   
   
   

9. On the Keys page, click Add Foreign to add a foreign key to the table (see Figure 9.11). Fill in these fields:

   
   
   

   Figure 9.11. The Add Foreign Key dialog box.

   
   
   

   
   

   
   
   
   

   • Table Schema� Specify the name of the schema of the parent key. In this example, the schema for all the tables is SVISSER.

   • Table Name� Specify the name of the parent table. In this example, you want CATEGORY as the parent table. Choose CATEGORY; its primary key value will appear in the Primary Key box.

   • Primary Key� This box is filled in automatically when you select the parent table. The primary key of the CATEGORY table is CATEGORYID, so this appears in the Primary Key box.

   • Foreign Key� In the Available Columns box, select columns that you want to define as foreign keys. Each column you select must match a primary key column for the parent table in meaning and data type. In this example, select CATEGORYID to be the foreign key.

   • On Delete� From this optional list, specify the action to take place on the dependent table when a row is deleted from the parent table. In this example, the ALBUM table is dependent on the parent table CATEGORY. Suppose that you have Rock listed in the CATEGORY table and decide to delete it. Because you also have a row in the ALBUM table that uses Rock as a category and these rows are linked, you now have to decide what to do with all the rows in ALBUM. Your choices are No Action (the default), Restrict, Cascade, or Select NULL. If you select No Action or Restrict, DB2 will give an error message if you attempt to delete a row in the parent table if there are dependent rows. No rows will be deleted. If you choose Cascade, the row in the parent table is deleted along with any of the dependent rows in dependent tables. If you choose Specify NULL, when the parent row is deleted, any dependent rows will replace all columns with a NULL value if it's allowable. For this example, leave this at the default value: No Action.

   • On Update� From this optional list, specify the action to take place on the dependent table when a row of the parent table is updated. Your choices are No Action and Restrict. If you choose No Action or Restrict, an error will occur if you attempt to update a row in the parent table. For this example, use the default value: No Action.

   • Constraint Name� In this optional text box, type a name for the constraint that you're defining. If a name isn't entered, one is generated for you. It's useful to have a meaningful constraint name. If an error occurs, the constraint name is used to describe the error condition. A meaningful name helps correct the error.

   
   

10. Click OK to add the foreign key.

    
    

11. On the Constraints page of the Create Table Wizard, you can add rules to help ensure that only valid data is inserted into the table (see Figure 9.12).

    
    
    

    Figure 9.12. The Create Table Wizard's Constraints page.

    
    
    

    
    

    
    
    
    

12. Click Add to open the Add Check Constraint dialog box to define a constraint (see Figure 9.13). For example, the column RELEASEYEAR is intended to contain the year the album or CD was released. This date is printed on the back of each CD. Depending on your collection, the release years may range from 1950 to present day. You can add a check constraint to ensure that only dates in a specific range are entered.

    
    
    

    Figure 9.13. The Add Check Constraint dialog box launched from the Create Table Wizard's Constraints page.

    
    
    

    
    

    
    
    
    

13. You can define a check constraint that references a single column by specifying that column in the Check Condition box. For example, you can add a check constraint to validate the year entered for the release date of a CD. A check condition of releaseyear between 1950 and 2020 would be suitable. (In Day 15, "Using System Administration Tools," you'll add a trigger to ensure that a year beyond the current year isn't entered.)

    
    

    Note

    
    
    

    Check constraints aren't checked for inconsistencies, duplicate conditions, or equivalent conditions. Therefore, contradictory or redundant check constraints can be defined, resulting in possible errors at execution time.

    
    
    
    

    Although the Constraint Name and Comment text boxes are optional, they're useful. If you don't enter values for these text boxes, the system generates a unique but incomprehensible name for the constraint. If you violate a check constraint condition, having a useful name appear in the message makes it easier to correct the error.

    
    

14. You can add multiple check constraints before closing this dialog box. Click Add on the Add Check Constraint dialog box to add the constraint and click Close when you've finished adding constraints.

    
    

15. Click Finish to create the table.

    
    

Altering Tables

After you create a table, the attributes that you can change are limited. You can do the following:

• Change the size of a column�for example, increase the size of a column defined with the data type VARCHAR.

• Change the comment and the data capture for propagation option.

• Add new columns and change, remove, or rearrange the new columns.

• Add unique, foreign, and partitioning keys for the table.

• Add or drop columns from the primary keys for the table or change the constraint name.

• Add, change, or remove new check constraints.

• Change the lock size, percentage of free space, and other performance-related details.

Tip

If you're altering a table by adding a foreign key, you need to turn off constraint checking before you alter the table. After you add the foreign key, you need to turn constraint checking back on.

Note

If you turn off constraint checking for a parent table, the foreign key constraints of all its dependent and descendent tables are put in check-pending state.

Right-click the table you want to alter and select Alter from the pop-up menu. The Alter Table dialog box opens. If you want to change an attribute in the table but it isn't in this list, you'll need to create a new table with the new attributes and import the data from the existing table.

[ Team LiB ]