Database definitions
This appendix provides the database tables used to build the solution covered in this IBM Redbooks publication.
Create entities and attributes for the business scenario
To create the solution for the Redbooks Company Service Desk scenario, the following database and entities are defined one time. The entities must be defined under a database. For this IBM Redbooks publication, SG248243 database is created using the command below on a DB2 machine:
db2 create database SG248243 using codeset UTF-8 territory en
The entities can change in numbers according to your solution. These entities are created by the database administrators. Also, names of the entities can differ according to the solution or according to company database standards. The entities used in this Redbooks publication are:
•SAW_STATUS
•SAW_REQUESTS
Adding attributes to entities
Status entities
The attributes that are added to SAW_STATUS entity are shown in Figure C-1.
Figure C-1 Attributes for status table
The status table has predefined values. These values are shown in Figure C-2.
Figure C-2 Status values used in the solution
Each claim request starts with a status of Pending. According to the output from human task activities or the system, the status can change to Approved or Rejected.
Request entities
The attributes added to the SAW_REQUESTS entity are shown in Figure C-3.
Figure C-3 Attributes for the SAW_REQUESTS entity
Database entity relations
The relation of the entities is shown in Figure C-4.
Figure C-4 Entity relations
The primary key is the REQUEST_ID of the requests entity. The foreign key is the STATUS_ID to the status entity.
Example C-1 shows the Data Definition Language (DDL) for creating the entities and inserting the data.
Example C-1 DDL for creating the entities for the solution
CREATE TABLE SAW_STATUS (
"STATUS_ID" BIGINT NOT NULL,
"STATUS_NAME" CHAR(20) NOT NULL
)
DATA CAPTURE NONE;
ALTER TABLE SAW_STATUS ADD CONSTRAINT "CC1408484744159" PRIMARY KEY ("STATUS_ID");
COMMENT ON TABLE SAW_STATUS IS 'SG24-8243 Redbook Status table';
insert into SAW_STATUS values (0, 'PENDING'),
insert into SAW_STATUS values (1, 'APPROVED'),
insert into SAW_STATUS values (2, 'REJECTED'),
CREATE TABLE SAW_REQUESTS (
"REQUEST_ID" BIGINT NOT NULL GENERATED ALWAYS AS IDENTITY ( START WITH 1 INCREMENT BY 1 MINVALUE 1 MAXVALUE 9223372036854775807 NO CYCLE NO CACHE),
"SERIAL_NUMBER" CHAR(20) NOT NULL,
"NAME" CHAR(100) NOT NULL,
"TRACKING_NUMBER" CHAR(20) NOT NULL,
"ADDRESS" CHAR(150) NOT NULL,
"STATUS_ID" BIGINT NOT NULL,
"CREATION_TIME" TIMESTAMP,
"REMARKS" CHAR(200),
"COMMENTS" CHAR(200)
)
DATA CAPTURE NONE COMPRESS YES;
ALTER TABLE SAW_REQUESTS ADD CONSTRAINT "CC1408485016720" PRIMARY KEY ("REQUEST_ID");
ALTER TABLE SAW_REQUESTS ADD CONSTRAINT "CC1408485022210" FOREIGN KEY ("STATUS_ID") REFERENCES SAW_STATUS ("STATUS_ID");
COMMENT ON TABLE SAW_REQUESTS IS 'SG24-8243 Redbook Requests table';
In order to run the DDL in Example C-1 on page 241, save the DDL into a file (such as Redbook.sql). Connect to the SG248243 that is created using the command with the db2 user and the password:
db2 connect to SG248243 user db2user using password
Then, you can run the DDL with the command as:
db2 -tvf <path>/Redbook.sql
..................Content has been hidden....................
You can't read the all page of ebook, please click here login for view all page.