Question

Problem 1 : Movies ER Model

Consider a Database that keeps track of scenes filmed for different movies. A movie uses a screenplay (or story) which is broken down into scenes. The movie will also have the same scenes because it is a movie of that screenplay. Not all screenplays in the database become movies, but every movie is of a particular screenplay.
Also, a screenplay is used for only one movie. That is, there are not two different movies made of the same screenplay. Scenes have a story-location where the story takes place and a filming-location where the filming will actually be done. Each scene has some actors that appear in that scene. Actors have a name, phone number, address and agent that represents them. A scene can be filmed more than once (maybe the actor forgot their lines). Each filming of a scene is called a “Take”. The movie is typically created by using the best take of each scene and putting them together. Below is an E-R diagram that captures these requirements.

Using the proposed E-R diagram provided, answer the questions related to designing the tables. (Note: thick lines denote weakentities or mandatory participation in relationships.)

Also in your functional depdencies use TakeNo and SceneNo instead of Take# and Scene# (i.e. avoid the # character).

1SCREENPLAY MOVIE MovieTitle Produceir Director SPTitle Author Movie-of ACTOR ctorName Phone Address AgentName Scene-of SCENE Scene# StoryLocation FilmLocation Appears-In TAKE Take# Date 「ime Take-of

R1.1 [2 marks] Provide a set of Functional dependencies that completely captures all the features in the situation depicted by the ER diagram.

2 marks if the answer is correct and complete (e.g. functional dependencies completely capture what is represented in the ER model), 1 mark if it's incomplete, 0 marks if its wrong.

R1.2 [2 marks] Provide a minimal cover for the set of functional dependencies.

R1.3 [2 marks] Based on your minimal cover find a dependency preserving, 3rd normal form set of tables use for your database that captures all of the data intended by the E-R model. Show for each table in the decomposition, its key and the functional dependencies that apply to it (that is, map to it).

Answer 1

Functional Dependencies and Tables :

Movie Table : (Movie Title, Director)-> Producer, {SPTitle}

Screenplay Table : (SPTitle,Author)->{Scene No}

Scene Table : (Scene No)-> Story Location,Film Location, {Take No} ,{Actor Name}

Take Table : (Take No)->Date, Time

Actor Table : (Actor Name)-> Phone Address, Agenet Name

() = Primary Key

{} = Foreign Key