The following attributes represent data about a movie copy at a video rental sto

Question

The following attributes represent data about a movie copy at a video rental store. 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). A video store has multiple copies of the same movie, and the store differentiates copies with a movie copy number, which is unique within a single movie but not unique between different movies. Each movie copy has a rental status and return date; in addition, each copy has a type (VHS, DVD, or Bluray). The rental price depends on the movie and the copy type, but the price is the same for all copies of the same type. The attributes are as follows:

Answer the following questions:

a. Draw a relational schema and diagram the functional dependencies in the relation.
b. In what normal form is this relation?
c. Decompose MOVIES into a set tables in BCNF.

Note: You will need to take into account the business rules given in the problem narrative in your text. The data don’t tell the whole story.

Explanation / 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}

Related Questions

Navigate

• Browse (All)
• Browse T
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.