Department table (DEPARTMENT)

 

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

The department table is created with the following CREATE TABLE and ALTER TABLE statements:

CREATE TABLE DEPARTMENT       (DEPTNO    CHAR(3)           NOT NULL,
       DEPTNAME  VARCHAR(36)       NOT NULL,
       MGRNO     CHAR(6)                   ,
       ADMRDEPT  CHAR(3)           NOT NULL, 
       LOCATION  CHAR(16),
       PRIMARY KEY (DEPTNO))


ALTER TABLE DEPARTMENT       ADD FOREIGN KEY ROD (ADMRDEPT)
          REFERENCES DEPARTMENT           ON DELETE CASCADE

The following foreign key is added later.

ALTER TABLE DEPARTMENT       ADD FOREIGN KEY RDE (MGRNO)
          REFERENCES EMPLOYEE           ON DELETE SET NULL

The following indexes are created.

CREATE UNIQUE INDEX XDEPT1
       ON DEPARTMENT (DEPTNO)


CREATE INDEX XDEPT2
       ON DEPARTMENT (MGRNO)


CREATE INDEX XDEPT3
       ON DEPARTMENT (ADMRDEPT)

The following alias is created for the table.

CREATE ALIAS DEPT FOR DEPARTMENT

The following table shows the content of the columns.

Table 1. Columns of the department table

Column name	Description
DEPTNO	Department number or ID.
DEPTNAME	A name describing the general activities of the department.
MGRNO	Employee number (EMPNO) of the department manager.
ADMRDEPT	The department (DEPTNO) to which this department reports; the department at the highest level reports to itself.
LOCATION	Location of the department.

• DEPARTMENT
  Here is a complete listing of the data in the DEPARTMENT table.

 

Parent topic:

DB2 Universal Database for iSeries sample tables