Soft_uni

CREATE TABLE IF NOT EXISTS `towns`(
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `name` VARCHAR(50) NOT NULL
);

CREATE TABLE IF NOT EXISTS `addresses`(
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `address_text` TEXT NOT NULL,
    `town_id` INT
);

ALTER TABLE `addresses`
    ADD CONSTRAINT `FK_addresses_towns`
    FOREIGN KEY (`town_id`) REFERENCES `towns`(`id`);

CREATE TABLE IF NOT EXISTS `departments`(
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `name` VARCHAR(50) NOT NULL
);

CREATE TABLE IF NOT EXISTS `employees`(
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `first_name` VARCHAR(50) NOT NULL,
    `middle_name` VARCHAR(50),
    `last_name` VARCHAR(50) NOT NULL,
    `job_title` VARCHAR(50) NOT NULL,
    `department_id` INT,
    `hire_date` DATETIME,
    `salary` DECIMAL (6,2) NOT NULL,
    `address_id` INT
);

ALTER TABLE `employees`
    ADD CONSTRAINT `FK_employees_departments`
    FOREIGN KEY (`department_id`) REFERENCES `departments`(`id`),
    ADD CONSTRAINT `FK_employees_addresses`
    FOREIGN KEY (`address_id`) REFERENCES `addresses`(`id`);