Tables

activity_log

CREATE TABLE IF NOT EXISTS `movie_production_companies`.`activity_log` (

  `id` INT NOT NULL AUTO_INCREMENT,

  `event_type` VARCHAR(45) NOT NULL,

  `table_name` VARCHAR(45) NOT NULL,

  `key_attribute` VARCHAR(95) NOT NULL,

  `event_description` VARCHAR(212) NOT NULL,

  `user` VARCHAR(45) NOT NULL,

  `event_timestamp` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,

  PRIMARY KEY (`id`))

ENGINE = InnoDB;

country

CREATE TABLE IF NOT EXISTS `movie_production_companies`.`country` (

  `code` CHAR(2) NOT NULL,

  `name` VARCHAR(200) NOT NULL,

  `isd_code` VARCHAR(9) NOT NULL,

  PRIMARY KEY (`code`))

ENGINE = InnoDB

DEFAULT CHARACTER SET = utf8mb3;

CREATE UNIQUE INDEX `name_UNIQUE` ON `movie_production_companies`.`country` (`name` ASC) VISIBLE;

shareholder

CREATE TABLE IF NOT EXISTS `movie_production_companies`.`shareholder` (

  `id` INT NOT NULL AUTO_INCREMENT,

  `first_name` VARCHAR(50) NOT NULL,

  `last_name` VARCHAR(50) NOT NULL,

  `country_code` CHAR(2) NOT NULL,

  `place_of_birth` VARCHAR(75) NOT NULL,

  `mother's_maiden_name` VARCHAR(45) NOT NULL,

  `father's_first_name` VARCHAR(45) NOT NULL,

  `personal_telephone` VARCHAR(25) NOT NULL,

  `national_insurance_number` VARCHAR(30) NOT NULL,

  `passport_number` VARCHAR(25) NOT NULL,

  PRIMARY KEY (`id`),

  CONSTRAINT `nation_fk_for_shareholders`

    FOREIGN KEY (`country_code`)

    REFERENCES `movie_production_companies`.`country` (`code`))

ENGINE = InnoDB

AUTO_INCREMENT = 17

DEFAULT CHARACTER SET = utf8mb3;

CREATE INDEX `nation_fk_idx` ON `movie_production_companies`.`shareholder` (`country_code` ASC) VISIBLE;

city

CREATE TABLE IF NOT EXISTS `movie_production_companies`.`city` (

  `id` INT NOT NULL,

  `name` VARCHAR(200) NOT NULL,

  `country_code` CHAR(2) NOT NULL,

  PRIMARY KEY (`id`),

  CONSTRAINT `country_code_fk`

    FOREIGN KEY (`country_code`)

    REFERENCES `movie_production_companies`.`country` (`code`))

ENGINE = InnoDB

DEFAULT CHARACTER SET = utf8mb3;

CREATE INDEX `city_country_idx` ON `movie_production_companies`.`city` (`country_code` ASC) VISIBLE;

kind_of_organization

CREATE TABLE IF NOT EXISTS `movie_production_companies`.`kind_of_organization` (

  `id` INT NOT NULL AUTO_INCREMENT,

  `name` VARCHAR(255) NOT NULL,

  PRIMARY KEY (`id`))

ENGINE = InnoDB

AUTO_INCREMENT = 6

DEFAULT CHARACTER SET = utf8mb3;

CREATE UNIQUE INDEX `name_UNIQUE` ON `movie_production_companies`.`kind_of_organization` (`name` ASC) VISIBLE;

registration_body

CREATE TABLE IF NOT EXISTS `movie_production_companies`.`registration_body` (

  `id` INT NOT NULL AUTO_INCREMENT,

  `name` VARCHAR(255) NOT NULL,

  `country_code` CHAR(2) NOT NULL,

  `price` INT NOT NULL,

  PRIMARY KEY (`id`),

  CONSTRAINT `reg_country_fk`

    FOREIGN KEY (`country_code`)

    REFERENCES `movie_production_companies`.`country` (`code`)

    ON DELETE NO ACTION

    ON UPDATE NO ACTION)

ENGINE = InnoDB

AUTO_INCREMENT = 12

DEFAULT CHARACTER SET = utf8mb3;

CREATE UNIQUE INDEX `name_UNIQUE` ON `movie_production_companies`.`registration_body` (`name` ASC) VISIBLE;

CREATE INDEX `reg_country_fk_idx` ON `movie_production_companies`.`registration_body` (`country_code` ASC) VISIBLE;

company

CREATE TABLE IF NOT EXISTS `movie_production_companies`.`company` (

  `id` VARCHAR(144) NOT NULL,

  `name` VARCHAR(144) NOT NULL,

  `address` VARCHAR(115) NOT NULL,

  `zip_code` INT NOT NULL,

  `city_id` INT NOT NULL,

  `country_code` CHAR(2) NOT NULL,

  `kind_of_organization_id` INT NOT NULL,

  `total_asset` DECIMAL(10,2) NOT NULL,

  `total_liability` DECIMAL(10,2) NOT NULL,

  `registration_body_id` INT NOT NULL,

  `registration_date` DATE NOT NULL,

  PRIMARY KEY (`id`),

  CONSTRAINT `city_id_fk`

    FOREIGN KEY (`city_id`)

    REFERENCES `movie_production_companies`.`city` (`id`)

    ON DELETE NO ACTION

    ON UPDATE NO ACTION,

  CONSTRAINT `company_country_code_fk`

    FOREIGN KEY (`country_code`)

    REFERENCES `movie_production_companies`.`country` (`code`)

    ON DELETE NO ACTION

    ON UPDATE NO ACTION,

  CONSTRAINT `kind_of_organization_fk`

    FOREIGN KEY (`kind_of_organization_id`)

    REFERENCES `movie_production_companies`.`kind_of_organization` (`id`)

    ON DELETE NO ACTION

    ON UPDATE NO ACTION,

  CONSTRAINT `registration_body_fk`

    FOREIGN KEY (`registration_body_id`)

    REFERENCES `movie_production_companies`.`registration_body` (`id`)

    ON DELETE NO ACTION

    ON UPDATE NO ACTION)

ENGINE = InnoDB

DEFAULT CHARACTER SET = utf8mb3;

CREATE UNIQUE INDEX `id_UNIQUE` ON `movie_production_companies`.`company` (`id` ASC) VISIBLE;

CREATE UNIQUE INDEX `company_name_UNIQUE` ON `movie_production_companies`.`company` (`name` ASC) VISIBLE;

CREATE INDEX `country_id_idx` ON `movie_production_companies`.`company` (`country_code` ASC) VISIBLE;

CREATE INDEX `kind_of_organization_fk_idx` ON `movie_production_companies`.`company` (`kind_of_organization_id` ASC) VISIBLE;

CREATE INDEX `regulatory_body_fk_idx` ON `movie_production_companies`.`company` (`registration_body_id` ASC) VISIBLE;

CREATE INDEX `company_fk_city_idx` ON `movie_production_companies`.`company` (`city_id` ASC) VISIBLE;

employee

CREATE TABLE IF NOT EXISTS `movie_production_companies`.`employee` (

  `id` VARCHAR(144) NOT NULL,

  `first_name` VARCHAR(45) NOT NULL,

  `middle_name` VARCHAR(45) NOT NULL,

  `last_name` VARCHAR(45) NOT NULL,

  `gender` ENUM('M', 'F') NOT NULL,

  `date_of_birth` DATE NOT NULL,

  `company_id` VARCHAR(144) NOT NULL,

  `employee_role` VARCHAR(75) NOT NULL,

  `date_started` DATE NOT NULL,

  `email_address` VARCHAR(115) NOT NULL,

  PRIMARY KEY (`id`),

  CONSTRAINT `employee_company_fk`

    FOREIGN KEY (`company_id`)

    REFERENCES `movie_production_companies`.`company` (`id`))

ENGINE = InnoDB

DEFAULT CHARACTER SET = utf8mb3;

CREATE UNIQUE INDEX `email_address_UNIQUE` ON `movie_production_companies`.`employee` (`email_address` ASC) VISIBLE;

CREATE INDEX `company_fk_idx` ON `movie_production_companies`.`employee` (`company_id` ASC) VISIBLE;

phone_number

CREATE TABLE IF NOT EXISTS `movie_production_companies`.`phone_number` (

  `id` INT NOT NULL AUTO_INCREMENT,

  `employee_id` VARCHAR(144) NOT NULL,

  `phone` VARCHAR(35) NOT NULL,

  `description` VARCHAR(45) NOT NULL DEFAULT 'Home',

  PRIMARY KEY (`id`),

  CONSTRAINT `employee_fk`

    FOREIGN KEY (`employee_id`)

    REFERENCES `movie_production_companies`.`employee` (`id`))

ENGINE = InnoDB

AUTO_INCREMENT = 40

DEFAULT CHARACTER SET = utf8mb3;

CREATE INDEX `employee_fk_idx` ON `movie_production_companies`.`phone_number` (`employee_id` ASC) VISIBLE;

role

CREATE TABLE IF NOT EXISTS `movie_production_companies`.`role` (

  `id` INT NOT NULL AUTO_INCREMENT,

  `name` VARCHAR(55) NOT NULL,

  PRIMARY KEY (`id`))

ENGINE = InnoDB

AUTO_INCREMENT = 4

DEFAULT CHARACTER SET = utf8mb3;

CREATE INDEX `role_name_index` ON `movie_production_companies`.`role` (`name` ASC, `id` ASC) VISIBLE;

CREATE UNIQUE INDEX `name_UNIQUE` ON `movie_production_companies`.`role` (`name` ASC) VISIBLE;

crew

CREATE TABLE IF NOT EXISTS `movie_production_companies`.`crew` (

  `sn` INT NOT NULL AUTO_INCREMENT,

  `crew_id` VARCHAR(144) NOT NULL,

  `role_id` INT NOT NULL,

  PRIMARY KEY (`sn`),

  CONSTRAINT `crew_id_fk`

    FOREIGN KEY (`crew_id`)

    REFERENCES `movie_production_companies`.`employee` (`id`)

    ON DELETE NO ACTION

    ON UPDATE NO ACTION,

  CONSTRAINT `crew_role_id_fk`

    FOREIGN KEY (`role_id`)

    REFERENCES `movie_production_companies`.`role` (`id`)

    ON DELETE NO ACTION

    ON UPDATE NO ACTION)

ENGINE = InnoDB

AUTO_INCREMENT = 18

DEFAULT CHARACTER SET = utf8mb3;

CREATE INDEX `employee_fk_idx` ON `movie_production_companies`.`crew` (`crew_id` ASC) VISIBLE;

CREATE INDEX `role_fk_idx` ON `movie_production_companies`.`crew` (`role_id` ASC) VISIBLE;

film

CREATE TABLE IF NOT EXISTS `movie_production_companies`.`film` (

  `movie_code` VARCHAR(115) NOT NULL,

  `title` VARCHAR(115) NOT NULL,

  `release_year` YEAR NOT NULL,

  `first_released` DATE NOT NULL,

  PRIMARY KEY (`movie_code`))

ENGINE = InnoDB

DEFAULT CHARACTER SET = utf8mb3;

CREATE UNIQUE INDEX `title_UNIQUE` ON `movie_production_companies`.`film` (`title` ASC) VISIBLE;

CREATE INDEX `movie_code_index` ON `movie_production_companies`.`film` (`movie_code` ASC) INVISIBLE;

crew_info

CREATE TABLE IF NOT EXISTS `movie_production_companies`.`crew_info` (

  `crew_id` VARCHAR(144) NOT NULL,

  `movie_code` VARCHAR(115) NOT NULL,

  `role_id` INT NOT NULL,

  `hourly_rate` DECIMAL(10,2) NULL DEFAULT NULL,

  `daily_bonus` DECIMAL(10,2) NULL DEFAULT NULL,

  `scene_bonus` DECIMAL(10,2) NULL DEFAULT NULL,

  `completion_bonus` DECIMAL(10,2) NULL DEFAULT NULL,

  `contractual_incentive` DECIMAL(10,2) NULL DEFAULT NULL,

  PRIMARY KEY (`crew_id`, `movie_code`, `role_id`),

  CONSTRAINT `crew_id_info_fk`

    FOREIGN KEY (`crew_id`)

    REFERENCES `movie_production_companies`.`crew` (`crew_id`)

    ON DELETE NO ACTION

    ON UPDATE NO ACTION,

  CONSTRAINT `movie_code_fk`

    FOREIGN KEY (`movie_code`)

    REFERENCES `movie_production_companies`.`film` (`movie_code`)

    ON DELETE NO ACTION

    ON UPDATE NO ACTION,

  CONSTRAINT `role_id_fk`

    FOREIGN KEY (`role_id`)

    REFERENCES `movie_production_companies`.`role` (`id`)

    ON DELETE NO ACTION

    ON UPDATE NO ACTION)

ENGINE = InnoDB

DEFAULT CHARACTER SET = utf8mb3;

CREATE INDEX `movie_code_fk_idx` ON `movie_production_companies`.`crew_info` (`movie_code` ASC) VISIBLE;

CREATE INDEX `role_fk_idx` ON `movie_production_companies`.`crew_info` (`role_id` ASC) VISIBLE;

department

CREATE TABLE IF NOT EXISTS `movie_production_companies`.`department` (

  `id` INT NOT NULL AUTO_INCREMENT,

  `name` VARCHAR(115) NOT NULL,

  PRIMARY KEY (`id`))

ENGINE = InnoDB

AUTO_INCREMENT = 11

DEFAULT CHARACTER SET = utf8mb3;

CREATE UNIQUE INDEX `name_UNIQUE` ON `movie_production_companies`.`department` (`name` ASC) VISIBLE;

department_address

CREATE TABLE IF NOT EXISTS `movie_production_companies`.`department_address` (

  `department_id` INT NOT NULL,

  `company_id` VARCHAR(144) NOT NULL,

  `building` VARCHAR(144) NOT NULL,

  `address` VARCHAR(144) NOT NULL,

  PRIMARY KEY (`department_id`, `company_id`),

  CONSTRAINT `fk_department_has_companies_companies1`

    FOREIGN KEY (`company_id`)

    REFERENCES `movie_production_companies`.`company` (`id`),

  CONSTRAINT `fk_department_has_companies_department1`

    FOREIGN KEY (`department_id`)

    REFERENCES `movie_production_companies`.`department` (`id`))

ENGINE = InnoDB

DEFAULT CHARACTER SET = utf8mb3;

CREATE INDEX `fk_department_has_companies_companies1_idx` ON `movie_production_companies`.`department_address` (`company_id` ASC) VISIBLE;

CREATE INDEX `fk_department_has_companies_department1_idx` ON `movie_production_companies`.`department_address` (`department_id` ASC) VISIBLE;

grant_request

CREATE TABLE IF NOT EXISTS `movie_production_companies`.`grant_request` (

  `id` VARCHAR(24) NOT NULL,

  `title` VARCHAR(199) NOT NULL,

  `funding_organization` VARCHAR(135) NOT NULL,

  `maximum_monetary_value` DECIMAL(10,2) NOT NULL,

  `desired_amount` DECIMAL(10,2) NOT NULL,

  `application_date` DATE NOT NULL,

  `deadline` DATE NOT NULL,

  `outcome` ENUM('Approved', 'Denied', 'Pending') NOT NULL,

  PRIMARY KEY (`id`))

ENGINE = InnoDB

DEFAULT CHARACTER SET = utf8mb3;

company_shareholder

CREATE TABLE IF NOT EXISTS `movie_production_companies`.`company_shareholder` (

  `company_id` VARCHAR(144) NOT NULL,

  `shareholder_id` INT NOT NULL,

  PRIMARY KEY (`company_id`, `shareholder_id`),

  CONSTRAINT `fk_company_has_shareholder_company1`

    FOREIGN KEY (`company_id`)

    REFERENCES `movie_production_companies`.`company` (`id`)

    ON DELETE NO ACTION

    ON UPDATE NO ACTION,

  CONSTRAINT `fk_company_has_shareholder_shareholder1`

    FOREIGN KEY (`shareholder_id`)

    REFERENCES `movie_production_companies`.`shareholder` (`id`)

    ON DELETE NO ACTION

    ON UPDATE NO ACTION)

ENGINE = InnoDB

DEFAULT CHARACTER SET = utf8mb3;

CREATE INDEX `fk_company_has_shareholder_shareholder1_idx` ON `movie_production_companies`.`company_shareholder` (`shareholder_id` ASC) VISIBLE;

CREATE INDEX `fk_company_has_shareholder_company1_idx` ON `movie_production_companies`.`company_shareholder` (`company_id` ASC) VISIBLE;

company_film

CREATE TABLE IF NOT EXISTS `movie_production_companies`.`company_film` (

  `company_id` VARCHAR(144) NOT NULL,

  `film_movie_code` VARCHAR(115) NOT NULL,

  PRIMARY KEY (`company_id`, `film_movie_code`),

  CONSTRAINT `fk_company_has_film_company1`

    FOREIGN KEY (`company_id`)

    REFERENCES `movie_production_companies`.`company` (`id`)

    ON DELETE NO ACTION

    ON UPDATE NO ACTION,

  CONSTRAINT `fk_company_has_film_film1`

    FOREIGN KEY (`film_movie_code`)

    REFERENCES `movie_production_companies`.`film` (`movie_code`)

    ON DELETE NO ACTION

    ON UPDATE NO ACTION)

ENGINE = InnoDB

DEFAULT CHARACTER SET = utf8mb3;

CREATE INDEX `fk_company_has_film_film1_idx` ON `movie_production_companies`.`company_film` (`film_movie_code` ASC) VISIBLE;

CREATE INDEX `fk_company_has_film_company1_idx` ON `movie_production_companies`.`company_film` (`company_id` ASC) VISIBLE;

staff

CREATE TABLE IF NOT EXISTS `movie_production_companies`.`staff` (

  `sn` INT NOT NULL AUTO_INCREMENT,

  `staff_id` VARCHAR(144) NOT NULL,

  `department_id` INT NOT NULL,

  PRIMARY KEY (`sn`),

  CONSTRAINT `staff_id_fk`

    FOREIGN KEY (`staff_id`)

    REFERENCES `movie_production_companies`.`employee` (`id`)

    ON DELETE NO ACTION

    ON UPDATE NO ACTION,

  CONSTRAINT `staff_department_id_fk`

    FOREIGN KEY (`department_id`)

    REFERENCES `movie_production_companies`.`department` (`id`)

    ON DELETE NO ACTION

    ON UPDATE NO ACTION)

ENGINE = InnoDB

AUTO_INCREMENT = 19

DEFAULT CHARACTER SET = utf8mb3;

CREATE INDEX `employee_fk_idx` ON `movie_production_companies`.`staff` (`staff_id` ASC) VISIBLE;

CREATE INDEX `department_fk_idx` ON `movie_production_companies`.`staff` (`department_id` ASC) VISIBLE;

staff_salary

CREATE TABLE IF NOT EXISTS `movie_production_companies`.`staff_salary` (

  `sn` INT NOT NULL AUTO_INCREMENT,

  `staff_id` VARCHAR(144) NOT NULL,

  `working_hours` ENUM('Full-time', 'Part-time') NOT NULL,

  `job_level` ENUM('Entry', 'Mid', 'Senior', 'Executive') NOT NULL,

  `salary` DECIMAL(10,2) NOT NULL,

  PRIMARY KEY (`sn`),

  CONSTRAINT `staff_salary_id_fk`

    FOREIGN KEY (`staff_id`)

    REFERENCES `movie_production_companies`.`staff` (`staff_id`)

    ON DELETE NO ACTION

    ON UPDATE NO ACTION)

ENGINE = InnoDB

DEFAULT CHARACTER SET = utf8mb3;

CREATE INDEX `staff_salary_fk_idx` ON `movie_production_companies`.`staff_salary` (`staff_id` ASC) VISIBLE;

CREATE UNIQUE INDEX `staff_id_UNIQUE` ON `movie_production_companies`.`staff_salary` (`staff_id` ASC) VISIBLE;

company_grant

CREATE TABLE IF NOT EXISTS `movie_production_companies`.`company_grant` (

  `company_id` VARCHAR(144) NOT NULL,

  `grant_request_id` VARCHAR(24) NOT NULL,

  PRIMARY KEY (`company_id`, `grant_request_id`),

  CONSTRAINT `fk_company_has_grant_request_company1`

    FOREIGN KEY (`company_id`)

    REFERENCES `movie_production_companies`.`company` (`id`)

    ON DELETE NO ACTION

    ON UPDATE NO ACTION,

  CONSTRAINT `fk_company_has_grant_request_grant_request1`

    FOREIGN KEY (`grant_request_id`)

    REFERENCES `movie_production_companies`.`grant_request` (`id`)

    ON DELETE NO ACTION

    ON UPDATE NO ACTION)

ENGINE = InnoDB

DEFAULT CHARACTER SET = utf8mb3;

CREATE INDEX `fk_company_has_grant_request_grant_request1_idx` ON `movie_production_companies`.`company_grant` (`grant_request_id` ASC) VISIBLE;

CREATE INDEX `fk_company_has_grant_request_company1_idx` ON `movie_production_companies`.`company_grant` (`company_id` ASC) VISIBLE;