The Department Table: DSN8810.DEPT

The Department Table: DSN8810.DEPT

DSN8810.DEPT describes information about departments that may be participating in projects. The following information is stored for each department: the department number, the department name , the employee number for the manager of this department, and the department number for the department to which this department reports . The department number is the primary key.

Referential integrity is used to implement a self-referencing constraint for ADMRDEPT . This referential constraint establishes the higher level department to which this department reports. A constraint also exists for MGRNO to EMPNO , the primary key of the DSN8810.EMP table. It ensures that the manager of a department is a valid employee.

Three indexes have been built for this table: DSN8810.XDEPT1 is a primary key index on DEPTNO , DSN8810.XDEPT2 is an index on MGRNO , and DSN8810.XDEPT3 is an index on ADMRDEPT .

DSN8810.DEPT Table DDL

 CREATE TABLE DSN8810.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 DSN8D81A.DSN8S81D CCSID EBCDIC; ALTER TABLE DSN8810.DEPT   FOREIGN KEY RDD (ADMRDEPT)     REFERENCES DSN8810.DEPT ON DELETE CASCADE; ALTER TABLE DSN8810.DEPT   FOREIGN KEY RDE (MGRNO)     REFERENCES DSN8810.EMP ON DELETE SET NULL;