-- CREATE TABLES WITH PRIMARY KEYS AND IDENTITIES CREATE TABLE CH13_DEPARTMENT ( DEPT_ID NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY START WITH 10 INCREMENT BY 10, NAME VARCHAR2(30) NOT NULL, LOCATION VARCHAR2(30) NOT NULL, CONSTRAINT CH13_DEPARTMENT_PK PRIMARY KEY (DEPT_ID) ) / CREATE TABLE CH13_EMPLOYEE ( EMP_ID NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY START WITH 1 INCREMENT BY 1, FIRST_NAME VARCHAR2(40) NOT NULL, LAST_NAME VARCHAR2(40) NOT NULL, BIRTH_DATE DATE, EMAIL VARCHAR2(50) NOT NULL , MANAGER_ID NUMBER, DEPARTMENT_ID NUMBER NOT NULL, CONSTRAINT CH13_EMPLOYEE_PK PRIMARY KEY (EMP_ID) ) / CREATE TABLE CH13_NEWS ( NEWS_ID NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY START WITH 1 INCREMENT BY 1, TITLE VARCHAR2(40) NOT NULL, DESCRIPTION VARCHAR2(250) NOT NULL, TIMESTAMP DATE DEFAULT sysdate NOT NULL, VALID_TO DATE, EMPLOYEE_ID NUMBER NOT NULL, CONSTRAINT CH13_NEWS_PK PRIMARY KEY (NEWS_ID) ) / CREATE TABLE CH13_ATTACHMENT ( ATT_ID NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY, FILE_NAME VARCHAR2(100) NOT NULL, MIME_TYPE VARCHAR2(50) NOT NULL, DATE_CREATED DATE, CONTENT BLOB NOT NULL, NEWS_ID NUMBER NOT NULL, CONSTRAINT CH13_ATTACHMENT_PK PRIMARY KEY (ATT_ID) ) / CREATE TABLE CH13_ROLE ( ROLE_ID NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY START WITH 10 INCREMENT BY 10, NAME VARCHAR2(30) NOT NULL, DESCRIPTION VARCHAR2(150), CONSTRAINT CH13_ROLE_PK PRIMARY KEY (ROLE_ID) ) / CREATE TABLE CH13_EMPLOYEE_ROLE ( EMPLOYEE_ID NUMBER NOT NULL, ROLE_ID NUMBER NOT NULL, START_DATE DATE NOT NULL, END_DATE DATE, CONSTRAINT CH13_EMP_ROLE_PK PRIMARY KEY (EMPLOYEE_ID, ROLE_ID) ) / -- ADD CONSTRAINTS / FOREIGN KEYS TO TABLES ALTER TABLE CH13_ATTACHMENT ADD CONSTRAINT CH13_ATTACHMENT_NEWS_FK FOREIGN KEY (NEWS_ID) REFERENCES CH13_NEWS (NEWS_ID) / ALTER TABLE CH13_EMPLOYEE ADD CONSTRAINT CH13_EMPLOYEE_DEPARTMENT_FK FOREIGN KEY (DEPARTMENT_ID) REFERENCES CH13_DEPARTMENT (DEPT_ID) / ALTER TABLE CH13_EMPLOYEE ADD CONSTRAINT CH13_MANAGER_FK FOREIGN KEY (MANAGER_ID) REFERENCES CH13_EMPLOYEE (EMP_ID) / ALTER TABLE CH13_EMPLOYEE_ROLE ADD CONSTRAINT CH13_EMPLOYEE_ROLE_EMPLOYEE_FK FOREIGN KEY (EMPLOYEE_ID) REFERENCES CH13_EMPLOYEE (EMP_ID) / ALTER TABLE CH13_EMPLOYEE_ROLE ADD CONSTRAINT CH13_EMPLOYEE_ROLE_ROLE_FK FOREIGN KEY (ROLE_ID) REFERENCES CH13_ROLE (ROLE_ID) / ALTER TABLE CH13_NEWS ADD CONSTRAINT CH13_NEWS_EMPLOYEE_FK FOREIGN KEY (EMPLOYEE_ID) REFERENCES CH13_EMPLOYEE (EMP_ID) /