Проверить BCNF в базе данных фильмов?

Модель Entity-Relationship — это скорее концептуальная модель, чем логическая модель. Он добавляет семантику, которая делает его более знакомым для людей, не обученных формальной логике, в то же время ограничивая их подмножеством реляционной модели и создавая разумно нормализованные отношения. Обычно мы не имеем дело с нормализацией на уровне ER — сущности и отношения разрабатываются только с одним первичным ключом, а все остальное является зависимостью от этих ключей, нет никаких обозначений для указания альтернативных ключей-кандидатов или частичных или транзитивных функциональных зависимостей. Кроме того, дополнительная семантика ER является препятствием при нормализации — нет никакой логической ценности в попытках сохранить разницу между наборами сущностей и наборами значений или между отношениями сущностей и отношениями отношений.

Нормализация лучше проверяется и применяется после преобразования модели ER в реляционную модель. Под этим я подразумеваю преобразование каждой сущности и каждого отношения в отдельное отношение и перечисление их функциональных зависимостей и ключей-кандидатов. В основном они могут быть получены непосредственно из модели ER, но следите за скрытыми естественными ключами (особенно при использовании суррогатного ключа), ищите зависимости среди неключевых атрибутов (особенно если вы использовали составные атрибуты EER) и следите за для нарушений 4NF из-за многозначных зависимостей, если вы использовали многозначные атрибуты EER.

Вы можете рисовать реляционные диаграммы, состоящие из прямоугольника для каждого столбца и стрелок между прямоугольниками для обозначения зависимостей. Я предпочитаю просто печатать это в тексте, например.

genre: genre_id -> genre_name
movies: movie_id -> title, release_date, rating
film_genre: movie_id, genre_id -> ()

Пустые скобки в последней строке означают, что в этом отношении нет неключевых атрибутов. Важно попытаться определить ВСЕ ключи-кандидаты и зависимости, которые имеют место для правильной нормализации, а не просто предполагать, что модель ER верна или завершена. После того, как у вас есть все ваши потенциальные ключи и функциональные зависимости, вы можете проверить их с помощью обычных форм.

Кстати, ваши отношения отношений неправильно реализованы в вашей физической модели/SQL. Они должны быть:

CREATE TABLE film_people ( 
    role_id INTEGER NOT NULL,
    person_id INTEGER NOT NULL,
    movie_id INTEGER NOT NULL,
    PRIMARY KEY (movie_id, person_id, role_id),
    FOREIGN KEY (movie_id) REFERENCES Movies (movie_id) ON DELETE CASCADE ON UPDATE CASCADE,
    FOREIGN KEY (person_id) REFERENCES People (person_id) ON DELETE CASCADE ON UPDATE CASCADE,
    FOREIGN KEY (role_id) REFERENCES Roles (role_id) ON DELETE CASCADE ON UPDATE CASCADE
);

CREATE TABLE film_genre ( 
    movie_id INTEGER NOT NULL,
    genre_id INTEGER NOT NULL,
    PRIMARY KEY (movie_id, genre_id),
    FOREIGN KEY (movie_id) REFERENCES Movies (movie_id) ON DELETE CASCADE ON UPDATE CASCADE,
    FOREIGN KEY (genre_id) REFERENCES genre (genre_id) ON DELETE CASCADE ON UPDATE CASCADE
);

Я удалил дублированные столбцы, например. movie_id и FK1_movie_id. Возможно, они были созданы благодаря включению ключевых атрибутов ваших отношений в вашу ER-диаграмму? Обычно мы понимаем, что ключ связи состоит из ключей сущностей, которые участвуют в связи, поэтому мы не указываем это на диаграмме.

Я также удалил уникальные ограничения для каждого из столбцов. Подумайте об этом - в каждом фильме есть только одна роль? Может ли каждый человек играть только одну роль в своей жизни? Каждую роль можно сыграть только один раз? Каждый фильм относится только к одному жанру? Каждый жанр содержит только один фильм? Эти ограничения не имели смысла.

Кроме того, на вашей диаграмме (0,N) индикаторы количества элементов не имеют смысла. 0 обычно указывает на необязательные компоненты отношения. Можно ли записать экземпляр film_genre без movie_id и/или genre_id? Нет, для каждого экземпляра отношения требуются обе сущности. Связи ER обычно считаются неограниченными, если не указано иное, поэтому единственная кардинальность, которую я когда-либо указывал, — это 1, когда сущность в отношении является зависимой, а не частью ключа. Для необязательных ассоциаций я использую пунктирные линии.

Возвращаясь к вашему вопросу, подумайте об отношении film_people, здесь может быть нарушение BCNF в зависимости от того, как вы интерпретируете ситуацию. Существуют ли скрытые перекрывающиеся ключи-кандидаты? Например, (movie_id, person_id) и (movie_id, role_id) уникальны? Другими словами, может ли человек играть только одну роль в фильме, и может ли только один человек играть каждую роль в фильме? Подумайте о (movie_id, role_id) и (role_id, person_id) таким же образом.

Наконец, также подумайте о своих пунктах ON DELETE CASCADE. Если вы удалите человека, он также удалит связанные роли из фильмов. Если вы удалите роль, она удалит связь человека с фильмом. Это правильно?