Question

You are a database consultant with Ace Software, Inc., and have been assigned to develop a database for the Johnson Video Store in town. The owners have been keeping their records of videos and DVDs purchased from distributors and rented to customers in stacks of invoices and piles of rental forms for years. They have finally decided to automate their record keeping with a relational database.

You sit down with the owners to discuss their business and watch their operation for about a week. You discover quickly that a video and a DVD are both copies of a movie kept in a separate plastic case that is rented out. They have several copies of each movie they rent; therefore, there are several videos and DVDs for each movie title. You learn that in their inventory they have several thousand videos and DVDs, which they get wholesale from about a half dozen distributors. The video and DVD prices for them are based on the quantity of their shipment and the past business they have done with each company.

The price of a DVD for a movie might be different from the price of a video for the same movie, even from the same distributor. Each distributor provides different types of movies (e.g., suspense, horror, mystery, comedy, etc.). A single distributor may provide several different types of movies in both video and DVD format. It is possible to obtain the same movie from multiple distributors and at different wholesale prices.

Each video and DVD has a unique identification number that The owners assign in their inventory, in addition to the distributor's serial number for the item. Each movie also has a unique identification number. The owners assign in addition to the title and any movie IDs the distributors use in their electronic catalogs. Distributors provide electronic catalogs to the owners, and the information from these catalogs must be included in the database.

The owners need to record when a video or DVD is rented, when a video or DVD is returned, and all customer charges such as late and damaged fees, failure to rewind fees, and taxes. They need a report of which videos are returned late because there are standard and late charges. On occasion, there are discount prices for specific movies or types of movies. Customers want to rent movies based on actors or actresses, running length, type of movie, rating, year released, the director, and the Academy Awards won (by the movie, the actors, the actresses and/or the directors). Customers also want to know how many videos they have rented in the last month, year, and so forth. The owners need to keep only basic information on customers in their database, such as name, address, telephone numbers, etc.

There must be no limit to the number of video and/or DVD copies of a movie that the owners can have in their inventory. Video/DVD ID numbers, movie ID numbers, and distributor ID numbers for videos, DVDs, and movies are all different. Also, each movie must be able to have an unlimited number of actors, actresses, directors, and Academy Awards (i.e., Oscars). Other types of awards (e.g., Golden Globe, People's Choice, etc.) are not of interest for this application. The rental of equipment, sale of videos, DVDs, popcorn, etc., is not to be kept in the database.

Question: Develop metadata from the ERD and document in an Excel spreadsheet

Answer 1

Entities of attributes : Entity Attribute Desceptin Academy-Aunod AA-20 categely This the entity used to stote awald details. Year Winney Nominee Academy awansd-mure AAM_20 Academy award -&D This entity is a intermediate beturen Quards and movies.enlity used to store the RD at auoids and movies. Movie - 2D Actors Act-2D The entity F- Name stones the actors :امامة Mr Name - Name MA-20 Movies - - Actors Ackoy - 20 This to entity actors is an intermediate and movies entity Movie - 20 changes to This entity charge - 80 ТЯPe charge steses the charges of charges of charge types. Rental charges 20 Rental-Ro This entity between acts as a charges and cheage -ED intermediate rentals. Customers C-20 E-name M- name L.. name This entity stie the intormation customers

Relationsships:

Category MOVIE_INVENTORY is a subclass of the UNION of the two distinct entity types DVD and VIDEO.

• Each MOVIE can be either a DVD or a MOVIE (also can have multiple copies of each one based on movie title). Each DVD is a MOVIE and each VIDEO is a MOVIE.

1)  DVD- A Super Class of Category MOVIE

2) VIDEO – A Super Class of the Category MOVIE

3) MOVIE_INVENTORY – DISTRIBUTORS

• Each movie is purchased from one or more distributors (of half a dozen). Once purchased the Distributors supplies the inventory for each movie.

4) CUSTOMER - VIDEO

• Each CUSTOMER is involved in one or more RENTALS.
• Each RENTAL includes a CUSTOMER.
• Each RENTAL includes a VIDEO.
• Each VIDEO may be involved in one or more RENTALS.

5) CUSTOMER - DVD

• Each CUSTOMER is involved in one or more RENTALS.
• Each RENTAL includes a CUSTOMER.
• Each RENTAL includes a DVD.
• Each DVD may be involved in one or more RENTALS.
• Each

6) WHOLESALE_PRICE

7) TRANSACTION_RECORDS - DVD

8) TRANSACTION_RECORDS - CUSTOMERS

• Each customer

9) Movie Inventory --> Video Games

• Video games can have one category. Categories can have multiple movies that are associated with it.

10) CreditCards --> Customers

• Customers can have one credit card. One credit card type can have many customers.

11) Customers --> FEES

• CUSTOMERS can have fees. Fees can applied to one customer at a time.

T Customers * Rentals Rental Charges T Charges_Info Movies - Distributors ml Academyaward Movie m Moviesof Directors mm Movies of Actors Academy Award O Directors Actors