Question

Create a MySQL database according to design below: 10p salaries emp_no INT salary INT from_date DATE ◇ to-date DATE dept_emp ?emp_no INT ?dept_no CHAR(4) from-date DATE ◇ to-date DATE dept. no CHAR(4) dept name VARCHAR(40) emp_no INT birth-date DATE o first_name ARCHAR(14) ola丈-name VARCHAR( 16) | o gender ENUMM,F) o hire_date DATE ? dept no CHAR(4) emp_no INT ofrom_ date DATE to_date DATE titles emp_no INT tile VARCHAR(50) ?from_date DATE o to date DATE Add at least 5 r Explain all relations between tables (cardinalities, min, max) 10p s to each table 10O -

Answer 1

If you have any doubts, please give me comment...

CREATE TABLE departments(

dept_no CHAR(4) NOT NULL PRIMARY KEY,

dept_name VARCHAR(40)

);

CREATE TABLE employees(

emp_no INT NOT NULL PRIMARY KEY,

birth_date DATE,

first_name VARCHAR(14),

last_name VARCHAR(16),

gender ENUM('M', 'F'),

hire_date DATE

);

CREATE TABLE dept_emp(

emp_no INT,

dept_no CHAR(4),

from_date DATE,

to_date DATE,

PRIMARY KEY(emp_no, dept_no),

FOREIGN KEY(emp_no) REFERENCES employees(emp_no),

FOREIGN KEY(dept_no) REFERENCES departments(dept_no)

);

CREATE TABLE dept_manager(

dept_no CHAR(4),

emp_no INT,

from_date DATE,

to_date DATE,

PRIMARY KEY(emp_no, dept_no),

FOREIGN KEY(emp_no) REFERENCES employees(emp_no),

FOREIGN KEY(dept_no) REFERENCES departments(dept_no)

);

CREATE TABLE salaries(

emp_no INT,

salary INT,

from_date DATE,

to_date DATE,

PRIMARY KEY(emp_no, from_date),

FOREIGN KEY(emp_no) REFERENCES employees(emp_no)

);

CREATE TABLE titles(

emp_no INT,

title VARCHAR(50),

from_date DATE,

to_date DATE,

PRIMARY KEY(emp_no, title, from_date),

FOREIGN KEY(emp_no) REFERENCES employees(emp_no)

);