ER-ReviewProblems
10. Block-Bluster, a local video renting shop, is considering creating a database to keep track of various aspects of the store. Impressed by your MIS knowledge and skills, the management has decided to hire you to design a conceptual schema first.
B.B. has various categories (Classic, Horror, Action, Comedy, Foreign, Educational, etc.) of Films for rent.
Each film is uniquely identified by a Film ID. Each film is also described by a title, a rate, its category, and possibly one or more directors. For some special kinds of films, more information is recorded. Each foreign film has a spoken language and a subtitle language. Each educational film has a recommended age group, which can have values "infant", "child", "teenager", "adult" and "senior".
To allow customers to search for films based on their preferred actors, B.B. also records the information about the actors' name and given name. Name is assumed to be unique among all the actors although given name is not. A film may have zero or more actors. Each actor performs a role in a film and may perform in many films.
B.B. carries zero or more copies of video tapes of each film. Each copy has a tape number that is unique among the copies of the same film. But multiple copies of different film may have the same copy number. Other information about a copy includes the current condition and number of checkouts. The condition of a copy is between 1 (new) and 5 (broken).
Each copy belongs to one copy type (new release, 5- day rental, or 2-day rental). Each copy type has a rental price and a number of nights allowed for each
rent, which apply to all copies of the type. The number of nights per rent can only be 1, 2, or 5.
CPV allocates a unique customer ID for each customer. Other information about a customer includes name (first name, middle initial, and last name), address (street, city, state, zip), phone number, birth year (to compute age), and total rental amount. Two specific types of customers are employees, who use their employeeID as unique identifiers, and Valued Customers, who will have discount when renting tapes. The discount level of Valued Customer (1 to 5) should be recorded.