DB2 Server for VSE & VM: Interactive SQL Guide and Reference

DEPARTMENT Table

The DEPARTMENT table describes each department in the business and identifies its manager and the department to which it reports. The table contents are shown in Figure 58; a description of the columns is shown in Figure 59.

Figure 58. DEPARTMENT Table Contents

DEPTNO	DEPTNAME	MGRNO	ADMRDEPT
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

Figure 59. Columns of the DEPARTMENT Table

Column Name Description DEPTNO Department number, the primary key DEPTNAME A name describing the general activities of the department MGRNO Employee number (EMPNO) of the department manager ADMRDEPT Number of the department to which this department reports; the department at the highest level reports to itself

The DEPARTMENT table is created with:

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

After the EMPLOYEE table has been created, a foreign key is added to the DEPARTMENT table with this statement:

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

Relationship to Other Tables

DEPARTMENT is a parent of the EMPLOYEE and PROJECT tables.

The DEPARTMENT table is a dependent of the EMPLOYEE table; the MGRNO column is the foreign key in the DEPARTMENT table and references EMPNO, the primary key in the EMPLOYEE table.