SQLize | PHPize | SQLtest

-- Создание таблицы Regions CREATE TABLE Regions ( region_id SERIAL PRIMARY KEY, region_name VARCHAR(50) NOT NULL ); -- Создание таблицы Countries CREATE TABLE Countries ( country_id CHAR(2) PRIMARY KEY, country_name VARCHAR(40) NOT NULL, region_id INT NOT NULL, FOREIGN KEY (region_id) REFERENCES Regions(region_id) ); -- Создание таблицы Locations CREATE TABLE Locations ( location_id SERIAL PRIMARY KEY, street_address VARCHAR(50), postal_code VARCHAR(12), city VARCHAR(30) NOT NULL, state_province VARCHAR(25), country_id CHAR(2) NOT NULL, FOREIGN KEY (country_id) REFERENCES Countries(country_id) ); -- Создание таблицы Jobs CREATE TABLE Jobs ( job_id VARCHAR(10) PRIMARY KEY, job_title VARCHAR(50) NOT NULL, min_salary INT, max_salary INT ); -- Создание таблицы Employees CREATE TABLE Employees ( employee_id SERIAL PRIMARY KEY, first_name VARCHAR(20) NOT NULL, last_name VARCHAR(25) NOT NULL, email VARCHAR(25), phone_number VARCHAR(20), hire_date DATE NOT NULL, job_id VARCHAR(10) NOT NULL, salary DECIMAL(8, 2) NOT NULL, commission_pct DECIMAL(2, 2), manager_id INT, department_id INT, FOREIGN KEY (job_id) REFERENCES Jobs(job_id), FOREIGN KEY (manager_id) REFERENCES Employees(employee_id), FOREIGN KEY (department_id) REFERENCES Departments(department_id) ); -- Создание таблицы Departments CREATE TABLE Departments ( department_id SERIAL PRIMARY KEY, department_name VARCHAR(30) NOT NULL, manager_id INT, location_id INT NOT NULL, FOREIGN KEY (manager_id) REFERENCES Employees(employee_id), FOREIGN KEY (location_id) REFERENCES Locations(location_id) ); -- Создание таблицы Job_history CREATE TABLE Job_history ( employee_id INT NOT NULL, start_date DATE NOT NULL, end_date DATE NOT NULL, job_id VARCHAR(10) NOT NULL, department_id INT NOT NULL, PRIMARY KEY (employee_id, start_date), FOREIGN KEY (employee_id) REFERENCES Employees(employee_id), FOREIGN KEY (job_id) REFERENCES Jobs(job_id), FOREIGN KEY (department_id) REFERENCES Departments(department_id) );