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.
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. |