Section 7.17. Case Study

7.17. Case Study

You have been given the task to create several DB2 database objects for a proof-of-concept exercise on a single-partition environment. You start by creating the database testdb on your Windows server's E: drive as follows:

 CREATE DATABASE testdb ON E: 

This command takes a few minutes to complete as it creates several default database objects, including the default table spaces (SYSCATSPACE, TEMPSPACE1, and USERSPACE1) and the default buffer pool (IBMDEFAULTBP). Because this is just a test database, you decide to use the USERSPACE1 default table space for your user tables and the default buffer pool.

After the database is created, before you can create any object, you need to connect to the database; thus you perform this operation:

 CONNECT TO testdb 

Given that this is a single-partition environment, you don't bother about creating partition groups. Therefore, you start with the creation of your tables. The requirements for this proof-of-concept exercise indicate that you need to create a table for the departments in your organization. Every department must have a department ID, which must be unique within the organization. Every department must also have a name and a manager ID. Because all three of these columns are required, you need to define them as NOT NULL. To ensure that the department ID is unique, you have two options: create a unique index or define the column as the primary key for the table. You decide to create a primary key because you want to define relationships between the department ID in this table and other tables later.

Therefore, you create the table as follows.

 CREATE TABLE dept (        deptID    INT       NOT NULL PRIMARY KEY,        deptname  CHAR(30)  NOT NULL,        mngrID    INT       NOT NULL) 

You also could have created the table in two steps as follows.

 CREATE TABLE dept (        deptID    INT       NOT NULL,        deptname  CHAR(30)  NOT NULL,        mngrID    INT       NOT NULL) ALTER TABLE dept ADD PRIMARY KEY (deptID) 

Next, because your applications tend to scan the table looking for department names on a regular basis, you create an index on the deptname column in the dept table as follows.

 CREATE INDEX deptnmx ON dept (deptname) 

Next, you create the table of employees for your organization. Every employee has an employee ID, first and last names, and a salary. In addition, every employee belongs to a department. You issue the following statement to create the emp table.

 CREATE TABLE emp (        empID   INT             NOT NULL PRIMARY KEY,        fname   CHAR(30)        NOT NULL,        lname   CHAR(30)        NOT NULL,        deptID  INT             NOT NULL,        salary  DECIMAL (12,2)        ) 

Because your applications scan the table looking for employees' names on a regular basis, you also create an index on the first and last name columns in the table as follows.

 CREATE INDEX empnmx ON emp (lname, fname) 

The employee and department tables are related: the department table is the parent table, and the employee table is the dependent table; therefore, any department ID in the employee table must exist in the department table. To establish this relationship, you create a referential constraint as follows.

 ALTER TABLE emp ADD FOREIGN KEY (deptID) REFERENCES dept 

Alternatively, you could have set up the same referential constraint by using a unique constraint in the dept table instead of the primary key as follows.

 CREATE TABLE dept (        deptID    INT       NOT NULL,        deptname  CHAR(30)  NOT NULL,        mngrID    INT       NOT NULL        ) ALTER TABLE dept ADD CONSTRAINT deptun UNIQUE (deptID) ALTER TABLE emp  ADD FOREIGN KEY (deptID) REFERENCES dept (deptID) 

This is important because you may want to have more than one referential constraint for the same base table, and this allows you to reference different keys in the parent table.

There is also a requirement to enforce a rule that no employee can have a salary greater than $100,000.00; therefore, you create a check constraint as follows.

 ALTER TABLE emp ADD CONSTRAINT salary CHECK (salary <= 100000.00) 

After the table successfully completes, you test the CHECK constraint with the following statement.

 INSERT INTO emp VALUES (123, 'Sam ', 'Johnson ', 345, 100005) 

This INSERT statement fails as expected with this message.

[View full width]
 SQL0545N  The requested operation is not allowed because a row does not satisfy the check  constraint "MYSCHEMA.EMP.SALARY". SQLSTATE=23513 

You know your applications already perform the salary check constraint before the data is entered into DB2, so you wonder if you can remove this constraint in DB2. However, when you ask your manager, he says the problem with that approach is that DB2 would not know that those rules exist and may therefore need to do extra checks that could cause inefficient access. To overcome this, you create informational constraints so that the DB2 optimizer knows about the rules when building the optimal access plan, but DB2 does not enforce the rules when the data is being manipulated. Therefore, you change the constraint you created earlier.

First you drop the constraint.

 ALTER TABLE emp DROP CONSTRAINT salary 

Then you recreate it again as follows.

 ALTER TABLE emp ADD CONSTRAINT salary       CHECK (salary < 100000.00)       NOT ENFORCED ENABLE QUERY OPTIMIZATION 

You save all of these commands in a script file. Next, because several developers in your company will use this testdb database to test different things, you decide to create the objects again, but this time using a different schema. Because you have SYSADM authority, you can issue the following commands.

 CREATE SCHEMA developer1 SET CURRENT SCHEMA developer1 

You then execute the script file, which creates all the objects again, but in a different schema.

And that's it for your proof-of-concept exercise!