CREATE DATABASE TJEFINALV;
USE TJEFINALV;

CREATE TABLE regions (
	region_id INT AUTO_INCREMENT PRIMARY KEY,
	region_name VARCHAR (25) DEFAULT NULL
);

CREATE TABLE countries (
	country_id CHAR (2) PRIMARY KEY,
	country_name VARCHAR (40) DEFAULT NULL,
	region_id INT NOT NULL,
	FOREIGN KEY (region_id) REFERENCES regions (region_id) ON DELETE CASCADE ON UPDATE CASCADE
);

CREATE TABLE locations (
	street_address VARCHAR (40) DEFAULT NULL,
	postal_code VARCHAR (12) DEFAULT NULL,
	city VARCHAR (30) PRIMARY KEY,
	state_province VARCHAR (25) DEFAULT NULL,
	country_id CHAR (2) NOT NULL,
	FOREIGN KEY (country_id) REFERENCES countries (country_id) ON DELETE CASCADE ON UPDATE CASCADE
);

CREATE TABLE jobs (
	job_title VARCHAR (35) PRIMARY KEY,
	grade int NOT NULL
);

CREATE TABLE departments (
	department_name VARCHAR (30) PRIMARY KEY,
	city VARCHAR(30),
	FOREIGN KEY (city) REFERENCES locations (city) ON DELETE CASCADE ON UPDATE CASCADE
);

CREATE TABLE employees (
	employee_id INT  AUTO_INCREMENT PRIMARY KEY,
	first_name VARCHAR (20) DEFAULT NULL,
	last_name VARCHAR (25) NOT NULL,
	birthdate DATE NOT NULL,
	hire_date DATE NOT NULL,
	gender CHAR(1),
	job_title VARCHAR(35)  NOT NULL,
	department_name VARCHAR(30) NOT NULL,
	FOREIGN KEY (job_title) REFERENCES jobs (job_title) ON DELETE CASCADE ON UPDATE CASCADE,
	FOREIGN KEY (department_name) REFERENCES departments (department_name) ON DELETE CASCADE ON UPDATE CASCADE
);

CREATE TABLE salaries (
	grade INT,
	country_id CHAR (2),
	amount DECIMAL(10,2)
);

CREATE TABLE dependents (
	dependent_id INT AUTO_INCREMENT PRIMARY KEY,
	first_name VARCHAR (50) NOT NULL,
	last_name VARCHAR (50) NOT NULL,
	relationship VARCHAR (25) NOT NULL,
	employee_id INT NOT NULL,
	FOREIGN KEY (employee_id) REFERENCES employees (employee_id) ON DELETE CASCADE ON UPDATE CASCADE
);