-- dept_emp 테이블 생성
CREATE TABLE dept_emp
(
    emp_no          INT             NOT NULL,
    dept_no         CHAR(4)         NOT NULL,
    from_date       DATE            NOT NULL,
    to_date         DATE            NOT NULL,
    FOREIGN KEY (emp_no)  REFERENCES employees   (emp_no)  ON DELETE CASCADE,
    FOREIGN KEY (dept_no) REFERENCES departments (dept_no) ON DELETE CASCADE,
    PRIMARY KEY (emp_no,dept_no)
);

-- titles 테이블 생성
CREATE TABLE titles
(
    emp_no          INT             NOT NULL,
    title           VARCHAR(50)     NOT NULL,
    from_date       DATE            NOT NULL,
    to_date         DATE,
    FOREIGN KEY (emp_no) REFERENCES employees (emp_no) ON DELETE CASCADE,
    PRIMARY KEY (emp_no,title, from_date)
);

-- salaries 테이블 생성
CREATE TABLE salaries
(
    emp_no          INT             NOT NULL,
    salary          INT             NOT NULL,
    from_date       DATE            NOT NULL,
    to_date         DATE            NOT NULL,
    FOREIGN KEY (emp_no) REFERENCES employees (emp_no) ON DELETE CASCADE,
    PRIMARY KEY (emp_no, from_date)
);