Question 1:

The Cinema Guild Theatre Group wants the following collection of 21 data items to be implemented in a database to manage its member theatres in several towns. Provide the 3NF LDM that satisfies these requirements.

Your answer must be the minimum required for a valid Logical Data Model solution. No extraneous entities or relationships. Do not add *any* data items to the 21 items shown. You must label with a name each entity and each relationship (between entities). Check carefully that you have satisfied all constraints A through K.

Data items:

Actor, Address_of_Theatre, Adult_Count, Adult_Price, Child_Count, Child_Price, Date, Director, Movie_Name, Movie_Rating, Movie_Review, Number_of_Screens, Screen_Number, Senior_Count, Senior_Price, Student_Count, Student_Price, Theatre_Name, Theatre_Phone_Number, Time_of_Showing, Town

The following describe key business rules, definitions, requirements and relationships between data items:

A: Theatres can show a "double bill" (2 films for 1 admission price) or a "triple bill" (3 films) or a "festival" (a larger number of films for one price).

B: The film distributor requires each theatre to report the count of each type of ticket sold by showing. A showing is one start of a movie on a particular day at a specific time. Movies shown together (double bill, etc.) have one start time.

C: A given movie may be showing at more than one theatre at the same time.

D: The movie review is a short paragraph quoted from a national magazine. The movie's rating is also based on non-local information.

E: The database needs to track all principal actors in a movie, e.g. co-stars Bette Middler and Carrie Fisher.

F: Theatres sometimes run film festivals, where they show several films starring the same actor. (Don't handle the festival separately, just be sure that your design for individual movies handles it.)

G: Theatres have four classes of admission with separate prices: adults, students, children, and senior citizens.

H: All prices are determined by the time of day and the day of the week (i.e. the date). Therefore, weekend and holiday prices can be different than weekday prices. Prices can be different in each theatres showing the same film at the same time.

I: A theatre is in only one town, but a town can have more than one theatre.

J: Most of the theatres in the theatre group have become multi-screen centers, as specified by the "Number of Screens" data item for each theatre. For each showing, the screen number must be recorded to identify the actual room in which the screening will occur. Date and time alone do not uniquely identify a showing.

K: Assume that a movie has only one director, but that director film festivals can be held (e.g. showing five Alfred Hitchcock or Woody Allen movies).

Solution for the above question:

Verify for yourself that this solution handles all requirements A-K of the problem.

DIRECTOR ( Director(K) )

ACTOR ( Actor(K) )

FILM ( Movie-name(K), Review, Rating )

PRICE ( ( Date, Time, Theatre )(K), Adult-Price, Child-Price, Student-Price, Senior-Price )

SHOWING ( ( Date, Time, Theatre, Screen# )(K), Adult-Count, Child-Count, Student-Count, Senior-Count )

THEATRE ( Theatre(K), Number-of-Screens, Address, Phone )

TOWN ( Town(K) )

contains: TOWN 1:M THEATRE

directs: DIRECTOR 1:M FILM

stars-in: ACTOR M:N FILM

shown-at: FILM M:N SHOWING

price-for: PRICE 1:M SHOWING

shows: THEATRE 1:M SHOWING

charges: THEATRE 1:M PRICE

Question 2 : Using Question 1

Given the policy changes below (and only these changes), make the necessary revisions and show your new 3NF model. (Create one model covering all of the original requirements and both policy changes (a) and (b).)

The management of the Cinema Guild Theatre Group has just approved two major changes in policy: (a) To compete with cable TV, video rentals, and internet distribution, the theatre group has implemented a radical (courageous? daring? risky?) pricing policy. Under this new policy, the price paid in all theatres of group for each class of admission is fixed for the time of day. (For example, admission for adults is always $7.50 for showings before noon.) It does *not* change for weekends or holidays.

The table of prices is to be stored in the database. Since the prices are for a time range, not for a single time, management has decided to add two data items: "From-Time" and "To-Time".

The prices charged for a showing will be decided by which time range the start time of that showing falls within. So, a double-feature showing that starts at 11:30 AM will be charged the prices from the 8:00AM to 11:59AM range, even though its second film will be shown in a later time range (afternoon). (b) Under the new agreement with the projectionists' (people who run the film projectors) union, a report will have to be produced each month that shows which showings each projectionist was responsible for. It must also show the identity (title), approximate start time, duration, and order of each film shown in that showing. ("Order" means which film was shown first, which one second, etc.; the only film in a single-bill will have order as 1.) This will allow the union to verify work schedules and overtime payments.

To accomplish this, management has decided to add three data items: "Duration", "Projectionist", and "Order-in-Showing". The configuration of each theatre allows a projectionist to handle more than one screen room at the same time (i.e. more than one showing).

[Note that it is not necessary to store the start time of each separate film in a showing. The start time of the showing, plus the order and duration of each film, allows the approximate film start times to be calculated when the report is generated.]