DDL Scripts from a Data Model

— Create tables section —————–

— Table DEPARTMENT

CREATE TABLE “DEPARTMENT”(

“DEPARTMENT_NO” Integer NOT NULL,

“DEPARTMENT_NAME” Varchar2(30 ) NOT NULL

)

/
— Add keys for table DEPARTMENT

ALTER TABLE “DEPARTMENT” ADD CONSTRAINT “Department_PK” PRIMARY KEY (“DEPARTMENT_NO”)

/

— Table TITLE

CREATE TABLE “TITLE”(

“TITLE_CODE” Integer NOT NULL,

“TITLE_DESCRIPTION” Varchar2(30 ) NOT NULL

)

/

— Add keys for table TITLE

ALTER TABLE “TITLE” ADD CONSTRAINT “TITLE_PK” PRIMARY KEY (“TITLE_CODE”)

/

— Table PROJECT

CREATE TABLE “PROJECT”(

“PROJECT_CODE” Integer NOT NULL,

“SOFTWARE_USED” Varchar2(30 ) NOT NULL

)

/

— Add keys for table PROJECT

ALTER TABLE “PROJECT” ADD CONSTRAINT “PROJECT_PK” PRIMARY KEY (“PROJECT_CODE”)

/

— Table EMPLOYEE

CREATE TABLE “EMPLOYEE”(

“EMPLOYEE_NO” Integer NOT NULL,

“DEPARTMENT_NO” Integer NOT NULL,

“TITLE_CODE” Integer NOT NULL,

“EMPLOYEE_NAME” Varchar2(30 ) NOT NULL,

“MANAGER_NO” Integer

)

/

— Add keys for table EMPLOYEE

ALTER TABLE “EMPLOYEE” ADD CONSTRAINT “EMPLOYEE_PK” PRIMARY KEY (“EMPLOYEE_NO”)

/

— Table EMPLOYEE_PROJECT

CREATE TABLE “EMPLOYEE_PROJECT”(

“EMPLOYEE_NO” Integer NOT NULL,

“PROJECT_CODE” Integer NOT NULL

)

/

— Add keys for table EMPLOYEE_PROJECT

ALTER TABLE “EMPLOYEE_PROJECT” ADD CONSTRAINT “EMPLOYEE_PROJECT_FK” PRIMARY KEY (“PROJECT_CODE”,”EMPLOYEE_NO”)

/

— Create relationships section ————————————————-

ALTER TABLE “EMPLOYEE” ADD CONSTRAINT “EMPLOYEE_FK01” FOREIGN KEY (“DEPARTMENT_NO”) REFERENCES “DEPARTMENT” (“DEPARTMENT_NO”)

/

ALTER TABLE “EMPLOYEE” ADD CONSTRAINT “EMPLOYEE_FKO2” FOREIGN KEY (“TITLE_CODE”) REFERENCES “TITLE” (“TITLE_CODE”)

/

ALTER TABLE “EMPLOYEE_PROJECT” ADD CONSTRAINT “EMPLOYEE_PROJECT_FK02” FOREIGN KEY (“PROJECT_CODE”) REFERENCES “PROJECT” (“PROJECT_CODE”)

/

ALTER TABLE “EMPLOYEE_PROJECT” ADD CONSTRAINT “EMPLOYEE_PROJECT_FK01” FOREIGN KEY (“EMPLOYEE_NO”) REFERENCES “EMPLOYEE” (“EMPLOYEE_NO”)

/

ALTER TABLE “EMPLOYEE” ADD CONSTRAINT “EMPLOYEE_FK03” FOREIGN KEY (“MANAGER_NO”) REFERENCES “EMPLOYEE” (“EMPLOYEE_NO”)

/