Perform conceptual design (ER Diagram) for the following case. You must turn in both an ER diagram and a specification that includes additional constraints not shown on the diagram. The specification should be written clearly. If you believe that you need to make additional assumptions, clearly state them in the specification. Please print out and turn in a hard copy of the ERD and additional comments.

XYZ Video Rentals is considering creating a database to keep track of various aspects of the store. Impressed by your MIS knowledge and skills, the management of XYZ Video Rentals has decided to hire you to design a conceptual schema first. XYZ Video Rentals has various genres (Classic, Horror, Action, Comedy, Foreign, Educational, etc.) of movies for rent. Each movie is uniquely identified by an ID. Each movie is also described by a title, a rating, and possibly one or more directors. For some special genres of movies, more information is recorded. For example, each foreign movie has a spoken language and a subtitle language. Each educational movie has a recommended age group, which can have values infant, child, teenage, adult and senior. Each Classic movie needs to know the name of the novel based on which the movie was produced.

To allow customers to search for movies based on their preferred performers, XYZ Video Rentals also records the information about performers, their name and stage name. Stage name is assumed to be unique among all the performers although names are not. A movie may have zero or more performers. Each performer performs a specific role (actor, supporting actor, etc) in a movie and may perform in many movies. It also happens that a performer may plan both actors and supporting actor in a movie.

XYZ Video Rentals carries zero or many copies of each movie. Each copy has a copy number that is unique among the copies of the same movie. But multiple copies of a different movie may have the same copy number. For example, Movie A has five copies which are numbered as 1,2,3,4,5. On the other hand, Movie B has 3 copies numbered as 1,2,3. Other information about a copy includes the current condition. The condition of a copy is between 1 (new) and 5 (broken).

Each copy belongs to one media format (Blu-Ray, VCD, or DVD). It often seems that Movie A is available in both Blu-Ray and DVD format. Each media format, rather than the movie, determine rental price and the number of nights allowed for each rent, which apply to all copies of the type. The number of nights that a customer can rent is between 1 and 10. XYZ Video Rentals allocates a unique customer number for each customer, also name (first, middle initial, and last), address (street, city, state, zip), phone number, birth date (to compute age), and the total rental amount in the life cycle of the customer.

Every time a customer wants to rent some copies of movies, he must sign a rental agreement, which is uniquely identified by a serial number. A rental agreement records the checkout date and total amount of rental. The total amount is calculated from the rental prices of the copies included in the rental agreement. It is the copy that customer checked out. A customer with an age under 18 cannot rent a movie rated 18 or above. A broken copy cannot be rented anymore.