Department table (DSN8C10.DEPT)

The department table describes each department in the enterprise and identifies its manager and the department to which it reports.

Begin general-use programming interface information.

The department table resides in table space DSN8D12A.DSN8S12D and is created with the following statement:

CREATE TABLE DSN8C10.DEPT
      (DEPTNO    CHAR(3)           NOT NULL,
       DEPTNAME  VARCHAR(36)       NOT NULL,
       MGRNO     CHAR(6)                   ,
       ADMRDEPT  CHAR(3)           NOT NULL,
       LOCATION  CHAR(16)                  ,
       PRIMARY KEY (DEPTNO)                )
  IN DSN8D12A.DSN8S12D
  CCSID EBCDIC;

Because the department table is self-referencing, and also is part of a cycle of dependencies, its foreign keys must be added later with the following statements:

ALTER TABLE DSN8C10.DEPT
      FOREIGN KEY RDD (ADMRDEPT) REFERENCES DSN8C10.DEPT
              ON DELETE CASCADE;
 
ALTER TABLE DSN8C10.DEPT
      FOREIGN KEY RDE (MGRNO) REFERENCES DSN8C10.EMP
              ON DELETE SET NULL;
End general-use programming interface information.

Content of the department table

The following table shows the content of the columns in the department table.

Table 1. Columns of the department table
Column Column name Description
1 DEPTNO Department ID, the primary key.
2 DEPTNAME A name that describes the general activities of the department.
3 MGRNO Employee number (EMPNO) of the department manager.
4 ADMRDEPT ID of the department to which this department reports; the department at the highest level reports to itself.
5 LOCATION The remote location name.

The following table shows the indexes of the department table.

Table 2. Indexes of the department table
Name On column Type of index
DSN8C10.XDEPT1 DEPTNO Primary, ascending
DSN8C10.XDEPT2 MGRNO Ascending
DSN8C10.XDEPT3 ADMRDEPT Ascending

The following table shows the content of the department table.


Table 3. DSN8C10.DEPT: department table
DEPTNO DEPTNAME MGRNO ADMRDEPT LOCATION
A00 SPIFFY COMPUTER SERVICE DIV. 000010 A00 ----------------
B01 PLANNING 000020 A00 ----------------
C01 INFORMATION CENTER 000030 A00 ----------------
D01 DEVELOPMENT CENTER ------ A00 ----------------
E01 SUPPORT SERVICES 000050 A00 ----------------
D11 MANUFACTURING SYSTEMS 000060 D01 ----------------
D21 ADMINISTRATION SYSTEMS 000070 D01 ----------------
E11 OPERATIONS 000090 E01 ----------------
E21 SOFTWARE SUPPORT 000100 E01 ----------------
F22 BRANCH OFFICE F2 ------ E01 ----------------
G22 BRANCH OFFICE G2 ------ E01 ----------------
H22 BRANCH OFFICE H2 ------ E01 ----------------
I22 BRANCH OFFICE I2 ------ E01 ----------------
J22 BRANCH OFFICE J2 ------ E01 ----------------

The LOCATION column contains null values until sample job DSNTEJ6 updates this column with the location name.

Relationship to other tables

The department table is self-referencing: the value of the administering department must be a valid department ID.

The department table is a parent table of the following :

  • The employee table, through a foreign key on column WORKDEPT
  • The project table, through a foreign key on column DEPTNO

The department table is a dependent of the employee table, through its foreign key on column MGRNO.