Developing a Prototype of ORACLE Data Mart for Ticket Sales - Midlands Theatre Company

1. A comprehensive understanding of the concepts, purposes, architectures, evolution and benefits of DW.
2. A systematic knowledge of how to apply ERD and Star Schema to design databases, DW or data mart.
3. The ability to design appropriate data extraction, transformation and loading strategy and create reasonable queries.

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

You are to develop a prototype of ORACLE data mart for ticket sales as a 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 data mart, 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  

Task 5: Create the corresponding tables in Oracle using SQL

Task 6: Identify your source data from the OLTP database and design your data extraction rules.  You need to give a detailed mapping and transformation list from the source to the destination.

Task 7: Implement your data extraction, transformation and loading through Oracle SQL.  The number of rows extracted into each dimension or fact table in your data mart should be printed from Oracle query.

Task 8: Comment on how your data mart satisfies the requirements of MT.  Implement the required data analysis requirements for both data mart and the original OLTP database.  Compare your queries and comments on the advantages of data mart in analysis operations.  Test results from Oracle query should be included.

• Evidence of the successful execution of your queries is required.  For large outputs, you need to provide only the last page of outputs with the number of rows in the end.