The Wombat Film Company has cinemas in Wagga, Orange, Mildura, Bathurst, Albury and Temora. It requires a database to record its screenings and revenue.
For each cinema we need to store the owner name and phone.
Each cinema consists on several theatres, ranging from 6 in Bathurst and Orange, to 2 in Temora. We need to store seating capacity for each theatre. Each theatre has several screenings per day, where we record the theatre number, preview code and name (always one preview shown before the main film), the preview rating (G, PG, MA or R) and distributors name and phone, the film code and name, its rating, distributor's name and phone, film length, number of adult and concession tickets purchased and the start time and end time of the screening.
For all screenings with a start time before 6pm (Mon to Fri excluding public holidays) the cost is adult $6 and concession $4 (equal price code B). At other times the cost is adult $10 and concession $6 (equals price code A). However, on some special occasions, such as movie releases, all tickets are $12 (equals price code C.)
Some of the recorded attributes include town, theatre-no, film-name, preview-name and screening-no.
The design of your database should be able to handle queries which could work out (not that you are required to do so):
the total revenue for each film in each town;
the number of screenings of each film for each town;
the total number of adult and concession tickets sold each day for each town.