Project table (DSN8A10.PROJ)

The sample project table describes each project that the business is currently undertaking. Data that is contained in each row of the table includes the project number, name, person responsible, and schedule dates.

The project table resides in database DSN8D10A. Because this table has foreign keys that reference DEPT and EMP, those tables and the indexes on their primary keys must be created first. Then PROJ is created with the following statement:

Begin general-use programming interface information.
CREATE TABLE DSN8A10.PROJ
               (PROJNO   CHAR(6) PRIMARY KEY NOT NULL,
                PROJNAME VARCHAR(24)    NOT NULL WITH DEFAULT
                  'PROJECT NAME UNDEFINED',
                DEPTNO   CHAR(3)        NOT NULL REFERENCES
                  DSN8A10.DEPT ON DELETE RESTRICT,
                RESPEMP  CHAR(6)        NOT NULL REFERENCES
                  DSN8A10.EMP ON DELETE RESTRICT,
                PRSTAFF  DECIMAL(5, 2)          ,
                PRSTDATE DATE                   ,
                PRENDATE DATE                   ,
                MAJPROJ  CHAR(6))
        IN DSN8D10A.DSN8S10P
        CCSID EBCDIC;

Because the project table is self-referencing, the foreign key for that constraint must be added later with the following statement: 

ALTER TABLE DSN8A10.PROJ
      FOREIGN KEY RPP (MAJPROJ) REFERENCES DSN8A10.PROJ
              ON DELETE CASCADE;
End general-use programming interface information.

Content of the project table

The following table shows the content of the columns of the project table.

Table 1. Columns of the project table
Column Column name Description
1 PROJNO Project ID (the primary key)
2 PROJNAME Project name
3 DEPTNO ID of department responsible for the project
4 RESPEMP ID of employee responsible for the project
5 PRSTAFF Estimated mean number of persons that are needed between PRSTDATE and PRENDATE to complete the whole project, including any subprojects
6 PRSTDATE Estimated project start date
7 PRENDATE Estimated project end date
8 MAJPROJ ID of any project of which this project is a part

The following table shows the indexes for the project table:

Table 2. Indexes of the project table
Name On column Type of index
DSN8A10.XPROJ1 PROJNO Primary, ascending
DSN8A10.XPROJ2 RESPEMP Ascending

Relationship to other tables

The table is self-referencing: a non-null value of MAJPROJ must be a valid project number. The table is a parent table of the project activity table, through a foreign key on column PROJNO. It is a dependent of the following tables:

  • The department table, through its foreign key on DEPTNO
  • The employee table, through its foreign key on RESPEMP