PartII. Normalization and Schema Refinement (30 points) The following attributes
ID: 3754223 • Letter: P
Question
PartII. Normalization and Schema Refinement (30 points) The following attributes represent data about a movie copy at an online video rental service. Each movie is identified by a movie number and has a title and information about the director and the studio that produced the movie. Each movie has one or several characters, and there is exactly one actor playing the role of each of the characters (but one actor can play multiple roles in each of the movies). The video rental service has multiple licenses for the same movie, and the service differentiates the licenses with a movie copy number, which is unique within a single movie but not unique between different movies. Each movie license has a rental status and return date; in addition, each license has a type (Regular or HD). The rental price depends on the movie and the license type, but the price is the same for all licenses of the same type. The attributes are as follows: Movie Nbr, Title, Director ID, Director Name, Studio ID, Studio Name, Studio Location, Studio CEO, Character, Actor ID, Name, Movie License Nbr, Movie license Type, Movie Rental Price, License Rental Status, License Return Date A sample data set regarding a movie would be as follows (the data in the curly brackets are character/actor data, in this case for four different characters): 567, "It's a Wonderful Life", 25, "Frank Capra", 234, "Liberty Films", "Hollywood, CA", "Orson Wells", "George Bailey", 245, "James Stewart" "Mary Bailey", 236, "Donna Reed" | "Clarence Oddbody", 765, "Henry Travers" | "Henry F.Potter", 325, "lionel Barrymore", 5434, "HD", 3.95, "Rented", "12/15/2018 1. Identify the function dependencies between attributes. For the dependency diagram, use XY2 or X Y notation. 2. Among the functional dependencies, list partial dependencies if any 3. Among the functional dependencies, list transitive dependencies if any 4. Identify the reasons why this relation is not in 3NF 5. Present the attributes organized so that the resulting relations are in 3NF. Clearly specify the primary key (PK) of each relation, and foreign keys (e.g., FK, FK, K2) if available. Denote the reference table of a foreign key using arrovwExplanation / Answer
(a)
Movies(MovieNbr, Title, DirectorID, DirectorName, StudioID, StudioName, StudioLocation, StudioCEO, Character, ActorID, ActorName, MovieLicenseNum, MovieLicenseType, MovieRentalCost, LicenseRent, LicenseReturn)
Functional dependencies:
{MovieNbr} : {Title, DirectorID, DirectorName, StudioID, StudioName, StudioLocation, StudioCEO}
{MovieNbr, Character} : {ActorID, ActorName}
{MovieNbr, MovieLicenseNum} : {MovieLicenseType, MovieRentalCost, LicenseRent, LicenseReturn}
(b)
Since no cell has more than 1 element, the table is in 1st normal form.
The primary key is {MovieNbr, Character, MovieLicenseNum}
Since there is partial dependency on the primary key, it is not in 2nd normal form.
Since it is not in 2NF, it is also not in 3 NF form.
Since it is not in 3NF, it is also not in BCNF form.
(c)
Table 1:
{MovieNbr, Title, DirectorID, DirectorName, StudioID, StudioName, StudioLocation, StudioCEO}
Table 2:
{MovieNbr, ActorID, ActorName}
Table 3:
{MovieNbr, MovieLicenseNum, MovieLicenseType, MovieRentalCost, LicenseRent, LicenseReturn}