Midlands Theatre (MT) Company - Data Mart for Ticket Sales

Company Overview

Midlands Theatre (MT) is a chain of small theatres that are found within the suburbs of several cities and towns within the Midlands, including Leicester and Birmingham. It specialises in high quality theatre productions that may be seen to be insufficiently “mainstream” for the general population. The company purchased its first theatre in January 1982, and since then has seen substantial increases in the number of clients that want to visit a local theatre to see a production that is more alternative to those typically found within the city centre theatres and/or large entertainment complexes.

In response to the growing client base, MT sometimes offers a matinee (i.e., afternoon) performance of a popular production in addition to the traditional evening performance time.

 Alongside the increase in number of theatre goers comes the issue of managing the additional bookings and ticketing requirements. The Managing Director of MT, Ms. Heritage, developed a computerised booking system for MT. Although the ultimate aim is to enable the on-line booking of tickets for any MT productions at any MT theatres, Ms. Heritage has created a simple booking system first. This first version booking system has automated the existing manual booking processes, which are detailed below.

 Information regarding current booking processes at Midlands Theatre (MT)

 Each of the theatres in MT’s portfolio has a schedule of productions for a given month in a particular calendar year. For instance, a schedule for a production entitled “Wind Blows”, which ran for one week at the Cropston Theatre during March 2019.

Only one production is shown at a theatre at a time, and some of the more popular productions run both afternoon (matinee) and evening performances.

Productions could be at a theatre for several weeks, although the majority of productions run only for a few days over one week. The run of the next production cannot start until the previous production has ended (in other words there is no interleaving of the performance runs of different productions).  It is common for the same production to have runs in all of MT’s theatres - one theatre after another - so that the widest number of potential clients for a production is reached.

When a potential theatre goer wants to purchase a ticket for a particular performance of a production at a MT theatre in advance, s/he contacts the central booking service via the website, telephone or an App. Whenever a ticket is purchased, a unique purchase number is allocated, the performance to be attended, the theatre, the client details (such as name, postcode and house number, and telephone), the total amount paid and the method of payment (cash, Paypal, debit card or credit card) and the method of ticket delivery (either by mail/email or by client pick up from the local theatre box office) are recorded. If several tickets are bought at the same time by the same person, each ticket is recorded as if it were bought separately.

Any remaining tickets for a performance are available to buy on the day from the local theatre box office. The same details are recorded, although clearly in this case the ticket will always be collected from the local theatre box office!

A relational database has been created according to the ERD.  The tables are as follows:

-Theatre(Theatre#, Name, Address, MainTel);

-Production(P#, Title, ProductionDirector, PlayAuthor);

-Performance(Per#, P#, Theatre#,pDate, pHour, pMinute, Comments);

-Client(Client#, title, name, address, telNo, e-mail);

-TicketPurchase(Purchase#, Client#, Per#,PaymentMethod, DeliveryMethod, TotalAmount).

Having established the online transaction processing database, Ms. Heritage wants more intelligence information from the available data and she is looking for a potential data warehouse for MT.  As the first step in this process, she expects to setup a data mart for ticket sales as the first step.  The data mart should satisfy the following analysis requirements:

-Yearly total sale for each theatre.

-All clients who visited MT theatres in at least 4 different months in a year.

List the titles, production directors and play authors of all products with the highest total sale.

Your Tasks

You are to develop a prototype of an ORACLE data mart for ticket sales as part of a potential data warehouse for MT.  For this assignment you are required to work individually.

You have been given the MT company scenario, together with an Entity Relationship (ER) Diagram and corresponding tables for the existing relational database.  

Task 1: Analyse the given database design and the requirement for a data mart, and identify the dimensions and fact for your data mart.  Here for this assignment, only the essential (minimum) dimensions for the required queries need to be included.  

Task 2: Design the star schema for the data mart, and identify the corresponding PKs and FKs.  

Task 3: According to given data and requirements, determine the relevant attributes and suitable granularity in your data mart.  

Task 4: Map your star schema to logical relations.