Цели лабораторной работы:
-
Получить физическую модель базы данных для выбранной предметной области.
-
Сформулировать возможные запросы к базе даных.
Для построения физической модели базы данных возьмем за основу уже построенную в Lab1 логическую модель базы данных.
Определимся с типами атрибутов для сущности произведение киноиндустрии(Title):
-
titleID - CHAR(9).
-
titleType – ENUM(movie, tvSeries, etc.)
-
primaryTitle – VARCHAR.
-
originalTitle - VARCHAR.
-
isAdult - BOOLEAN (BIT(1)).
-
startYear – YEAR.
-
runtimeMinutes – TINYINT UNSIGNED NOT NULL.
-
posterURL - VARCHAR NULLABLE.
-
plot - TEXT NULLABLE.
CREATE TABLE `imdb_db`.`title` (
`title_ID` CHAR(9) NOT NULL,
`title_type` TEXT NOT NULL,
`primary_title` TEXT NOT NULL,
`original_title` TEXT NOT NULL,
`isAdult` BIT(1) NOT NULL,
`startYear` YEAR NOT NULL,
`runtimeMinutes` SMALLINT UNSIGNED NOT NULL,
`posterURL` TEXT NULL,
`plot` TEXT NULL,
PRIMARY KEY (`title_ID`));Определимся с типами атрибутов для сущности рейтинг(Rating):
-
ratingID - PK(titleID, ratingType).
-
titleID - CHAR(9) NOT NULL.
-
averageRating - DOUBLE UNSIGNED NOT NULL.
-
numVotes - INT UNSIGNED NOT NULL.
-
ratingType - ENUM(IMDB, Metacritic, etc.) NOT NULL.
CREATE TABLE `imdb_db`.`rating` (
`titleID` CHAR(9) NOT NULL,
`numVotes` INT UNSIGNED NOT NULL DEFAULT 0,
`averageRating` DOUBLE UNSIGNED NOT NULL DEFAULT 0,
`ratingType` ENUM('IMDB', 'METACRITIC') NOT NULL,
PRIMARY KEY (`titleID`, `ratingType`),
CONSTRAINT `rating_titleID_FK`
FOREIGN KEY (`titleID`)
REFERENCES `imdb_db`.`title`(`titleID`)
ON DELETE CASCADE
ON UPDATE NO ACTION);Определимся с типами атрибутов для сущности жанр(GENRE):
-
genreID - TINYINT AUTOINCREMENT UNSIGNED.
-
genreType - ENUM(Drama, comedy, etc.).
CREATE TABLE `imdb_db`.`genre` (
`genreID` TINYINT UNSIGNED NOT NULL AUTO_INCREMENT,
`genre` ENUM('Action', 'Comedy', etc.) NOT NULL,
PRIMARY KEY (`genreID`),
UNIQUE INDEX `idgenre_UNIQUE` (`genreID` ASC));Определимся с типами атрибутов для сущности эпизод сериала(SeriesEpisode):
-
titleID - CHAR(9).
-
parentTitleID - CHAR(9)
-
seasonNumber - TINYINT UNSIGNED.
-
episodeNumber - TINYINT UNSIGNED.
CREATE TABLE `imdb_db`.`series_episode` (
`titleID` CHAR(9) NOT NULL,
`parentTitleID` CHAR(9) NOT NULL,
`seasonNumber` TINYINT UNSIGNED NOT NULL,
`episodeNumber` TINYINT UNSIGNED NOT NULL,
PRIMARY KEY (`titleID`),
CONSTRAINT `series_episode_titleID_FK`
FOREIGN KEY (`parentTitleID`)
REFERENCES `imdb_db`.`title`(`titleID`)
ON DELETE CASCADE
ON UPDATE NO ACTION);Определимся с типами атрибутов для сущности деятель киноиндустрии(Name):
-
nameID - VARCHAR(10).
-
primaryName - TEXT.
-
birthYear - DATE.
-
deathYear - DATE.
CREATE TABLE `imdb_db`.`name` (
`nameID` CHAR(10) NOT NULL,
`primaryName` TEXT NOT NULL,
`birthDate` DATE NOT NULL,
`deathDate` DATE NULL,
PRIMARY KEY (`nameID`));Определимся с типами атрибутов для сущности профессия(Profession):
-
professionID - TINYINT AUTOINCREMENT UNSIGNED.
-
jobType - ENUM(director, writer, etc.).
CREATE TABLE `imdb_db`.`profession` (
`professionID` TINYINT NOT NULL AUTO_INCREMENT,
`profession` ENUM('Director', 'Writer', etc.) NOT NULL,
PRIMARY KEY (`professionID`));Создадим промежуточную таблицу titleGenre для реализации связи многие ко многим между сущностями Title и Genre.
CREATE TABLE `imdb_db`.`title_genre` (
`titleID` CHAR(9) NOT NULL,
`genreID` TINYINT UNSIGNED NOT NULL,
PRIMARY KEY (`titleID`, `genreID`),
CONSTRAINT `title_genre_titleID_FK`
FOREIGN KEY (`titleID`)
REFERENCES `imdb_db`.`title`(`titleID`)
ON DELETE CASCADE
ON UPDATE NO ACTION,
CONSTRAINT `title_genre_genreID_FK`
FOREIGN KEY (`genreID`)
REFERENCES `imdb_db`.`genre`(`genreID`)
ON DELETE CASCADE
ON UPDATE NO ACTION);Создадим промежуточную таблицу nameKnownForTitle для реализации связи многие ко многим между сущностями Name и Title.
CREATE TABLE `imdb_db`.`name_known_for_title` (
`nameID` CHAR(10) NOT NULL,
`titleID` CHAR(9) NOT NULL,
PRIMARY KEY (`nameID`, `titleID`),
CONSTRAINT `name_known_for_title_nameID_FK`
FOREIGN KEY (`nameID`)
REFERENCES `imdb_db`.`name`(`nameID`)
ON DELETE CASCADE
ON UPDATE NO ACTION,
CONSTRAINT `name_known_for_title_titleID_FK`
FOREIGN KEY (`titleID`)
REFERENCES `imdb_db`.`title`(`titleID`)
ON DELETE CASCADE
ON UPDATE NO ACTION);Создадим промежуточную таблицу nameProfessions для реализации связи многие ко многим между сущностями Name и Profession.
CREATE TABLE `imdb_db`.`name_professions` (
`nameID` CHAR(10) NOT NULL,
`professionID` TINYINT NOT NULL,
PRIMARY KEY (`nameID`, `professionID`),
CONSTRAINT `name_professions_nameID_FK`
FOREIGN KEY (`nameID`)
REFERENCES `imdb_db`.`name`(`nameID`)
ON DELETE CASCADE
ON UPDATE NO ACTION,
CONSTRAINT `name_professions_professionID_FK`
FOREIGN KEY (`professionID`)
REFERENCES `imdb_db`.`profession`(`professionID`)
ON DELETE CASCADE
ON UPDATE NO ACTION);Создадим промежуточную таблицу principal для реализации связи многие ко многим между сущностями Name, Profession и Title.
CREATE TABLE `imdb_db`.`principal` (
`titleID` CHAR(9) NOT NULL,
`nameID` CHAR(10) NOT NULL,
`professionID` TINYINT NOT NULL,
PRIMARY KEY (`titleID`, `nameID`, `professionID`),
CONSTRAINT `principal_titleID_FK`
FOREIGN KEY (`titleID`)
REFERENCES `imdb_db`.`title`(`titleID`)
ON DELETE CASCADE
ON UPDATE NO ACTION,
CONSTRAINT `principal_nameID_FK`
FOREIGN KEY (`nameID`)
REFERENCES `imdb_db`.`name`(`nameID`)
ON DELETE CASCADE
ON UPDATE NO ACTION,
CONSTRAINT `principal_professionID_FK`
FOREIGN KEY (`professionID`)
REFERENCES `imdb_db`.`profession`(`professionID`)
ON DELETE CASCADE
ON UPDATE NO ACTION); 