Database System Final Project SQL Schema

database-project.sql

CREATE TABLE `movies` (
	`id` INT NOT NULL AUTO_INCREMENT,
	`title` VARCHAR(255) NOT NULL,
	`original_title` VARCHAR(255) NOT NULL,
	`rating` FLOAT NOT NULL,
	`introduction` VARCHAR(255) NOT NULL,
	`film_year` INT NOT NULL,
	`film_region_id` INT NOT NULL,
	`is_tv` BOOLEAN NOT NULL,
	`film_company_id` INT NOT NULL,
	PRIMARY KEY (`id`)
);

CREATE TABLE `screens` (
	`movie_id` INT NOT NULL,
	`screen_date` DATE NOT NULL,
	`duration` TIME NOT NULL,
	`region_id` INT NOT NULL,
	`release_date` DATE NOT NULL,
	`language` VARCHAR(255) NOT NULL,
	PRIMARY KEY (`movie_id`)
);

CREATE TABLE `tags` (
	`movie_id` INT NOT NULL,
	`name` VARCHAR(255) NOT NULL,
	PRIMARY KEY (`movie_id`,`name`)
);

CREATE TABLE `genres` (
	`name` VARCHAR(255) NOT NULL,
	`description` VARCHAR(255) NOT NULL,
	PRIMARY KEY (`name`)
);

CREATE TABLE `movie_genres` (
	`movie_id` INT NOT NULL,
	`genre_name` VARCHAR(255) NOT NULL,
	PRIMARY KEY (`movie_id`,`genre_name`)
);

CREATE TABLE `staffs` (
	`id` INT NOT NULL AUTO_INCREMENT,
	`name` VARCHAR(255) NOT NULL,
	`native_name` VARCHAR(255) NOT NULL,
	`abstract` VARCHAR(255) NOT NULL,
	`birthday` DATE NOT NULL,
	`region_id` DATE NOT NULL,
	PRIMARY KEY (`id`)
);

CREATE TABLE `participates` (
	`movie_id` INT NOT NULL,
	`staff_id` INT NOT NULL,
	`role` INT NOT NULL,
	PRIMARY KEY (`movie_id`,`staff_id`,`role`)
);

CREATE TABLE `regions` (
	`id` INT NOT NULL AUTO_INCREMENT,
	`name` VARCHAR(255) NOT NULL,
	`description` VARCHAR(255) NOT NULL,
	PRIMARY KEY (`id`,`name`)
);

CREATE TABLE `aliases` (
	`movie_id` INT NOT NULL,
	`name` VARCHAR(255) NOT NULL,
	PRIMARY KEY (`movie_id`,`name`)
);

CREATE TABLE `film_company` (
	`id` INT NOT NULL AUTO_INCREMENT,
	`name` VARCHAR(255) NOT NULL,
	`region_id` INT NOT NULL,
	`abstract` VARCHAR(255) NOT NULL,
	PRIMARY KEY (`id`)
);

ALTER TABLE `movies` ADD CONSTRAINT `movies_fk0` FOREIGN KEY (`film_region_id`) REFERENCES `regions`(`id`);

ALTER TABLE `movies` ADD CONSTRAINT `movies_fk1` FOREIGN KEY (`film_company_id`) REFERENCES `film_company`(`id`);

ALTER TABLE `screens` ADD CONSTRAINT `screens_fk0` FOREIGN KEY (`movie_id`) REFERENCES `movies`(`id`);

ALTER TABLE `screens` ADD CONSTRAINT `screens_fk1` FOREIGN KEY (`region_id`) REFERENCES `regions`(`id`);

ALTER TABLE `tags` ADD CONSTRAINT `tags_fk0` FOREIGN KEY (`movie_id`) REFERENCES `movies`(`id`);

ALTER TABLE `movie_genres` ADD CONSTRAINT `movie_genres_fk0` FOREIGN KEY (`movie_id`) REFERENCES `movies`(`id`);

ALTER TABLE `movie_genres` ADD CONSTRAINT `movie_genres_fk1` FOREIGN KEY (`genre_name`) REFERENCES `genres`(`name`);

ALTER TABLE `staffs` ADD CONSTRAINT `staffs_fk0` FOREIGN KEY (`region_id`) REFERENCES `regions`(`id`);

ALTER TABLE `participates` ADD CONSTRAINT `participates_fk0` FOREIGN KEY (`movie_id`) REFERENCES `movies`(`id`);

ALTER TABLE `participates` ADD CONSTRAINT `participates_fk1` FOREIGN KEY (`staff_id`) REFERENCES `staffs`(`id`);

ALTER TABLE `aliases` ADD CONSTRAINT `aliases_fk0` FOREIGN KEY (`movie_id`) REFERENCES `movies`(`id`);

ALTER TABLE `film_company` ADD CONSTRAINT `film_company_fk0` FOREIGN KEY (`region_id`) REFERENCES `regions`(`id`);