ORACLE Data Mart For Ticket Sales For Midlands Theatre (MT) Company Essay.

Introduction and Overview

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, has decided that a computerised booking system for MT is needed. Although the ultimate aim is to enable the on-line booking of tickets for any MT productions at any MT theatres, Ms. Heritage would like a simple booking system to be created first. This first version booking system will automate 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 2018, is provided in Table 1.

Table 1.  Example of one week performance at the Cropston Theatre

Identification of dimension tables are based on the dimensions that are the companion of the facts for a warehouse. These attributes present in the dimension table is used later for making the queries and data retrieval. The dimension tables identified here are DimClient (stores client details), DimProduction (stores production details of the plays), DimTheatre (stores theatre details), DimCalendar (This dimension table has been created to store each day, week, month, date month name, etc. in a year that act as a whole calendar), DimShowtime (stores show times and names of the performance).

Identification of the fact tables has been done on the basis of the measurements, facts and amount details of the business. The table used for facts has been named FactPerformance here. It stores the foreign key of all the dimension tables along with the amounts, methods, and dates. It is used for analytical evaluation of the business.

Figure 1: Star Schema of Data Mart

Source: created by author

 

Figure 2: Logical relations between the entities in Data Mart

Source: created by author

DimTheatre

CREATE TABLE DimTheatre (

  TheatreID VARCHAR(10) NOT NULL,

  Name VARCHAR(45) NULL,

  Address VARCHAR(45) NULL,

  MainTel INT NULL,

  PRIMARY KEY (TheatreID));

DimProduction

CREATE TABLE DimProduction (

  Pid VARCHAR(10) NOT NULL,

  Title VARCHAR(45) NULL,

  PDirector VARCHAR(45) NULL,

  Playauthor VARCHAR(45) NULL,

  PRIMARY KEY (Pid));

DimShowtime

CREATE TABLE DimShowTime (

  Name VARCHAR(45) NULL,

  Hour number(2) NULL,

  Minute number(2) NULL,

  PRIMARY KEY (hour,minute));

DimClient

CREATE TABLE DimClient (

  ClientID VARCHAR(10) NOT NULL,

  Title VARCHAR(45) NULL,

  Name VARCHAR(45) NULL,

  street varchar(45) not null,

  town   varchar(45) not null,

  country varchar(45) not null,

  Telno INT NULL,

  email VARCHAR(45) NULL,

  PRIMARY KEY (ClientID));

DimCalendar

CREATE TABLE DimCalendar AS

WITH base_calendar AS

  (SELECT CurrDate          AS Day_ID,

    1                       AS Day_Time_Span,

    CurrDate                AS Day_End_Date,

    TO_CHAR(CurrDate,'Day') AS Week_Day_Full,

    TO_CHAR(CurrDate,'DY')  AS Week_Day_Short,

    TO_NUMBER(TRIM(leading '0'

  FROM TO_CHAR(CurrDate,'D'))) AS Day_Num_of_Week,

    TO_NUMBER(TRIM(leading '0'

  FROM TO_CHAR(CurrDate,'DD'))) AS Day_Num_of_Month,

    TO_NUMBER(TRIM(leading '0'

  FROM TO_CHAR(CurrDate,'DDD'))) AS Day_Num_of_Year,

    UPPER(TO_CHAR(CurrDate,'Mon')

    || '-'

    || TO_CHAR(CurrDate,'YYYY')) AS Month_ID,

    TO_CHAR(CurrDate,'Mon')

    || ' '

    || TO_CHAR(CurrDate,'YYYY') AS Month_Short_Desc,

    RTRIM(TO_CHAR(CurrDate,'Month'))

    || ' '

    || TO_CHAR(CurrDate,'YYYY') AS Month_Long_Desc,

    TO_CHAR(CurrDate,'Mon')     AS Month_Short,

    TO_CHAR(CurrDate,'Month')   AS Month_Long,

    TO_NUMBER(TRIM(leading '0'

  FROM TO_CHAR(CurrDate,'MM'))) AS Month_Num_of_Year,

    'Q'

    || UPPER(TO_CHAR(CurrDate,'Q')

    || '-'

    || TO_CHAR(CurrDate,'YYYY'))     AS Quarter_ID,

    TO_NUMBER(TO_CHAR(CurrDate,'Q')) AS Quarter_Num_of_Year,

    CASE

      WHEN TO_NUMBER(TO_CHAR(CurrDate,'Q')) <= 2

      THEN 1

      ELSE 2

    END AS half_num_of_year,

    CASE

      WHEN TO_NUMBER(TO_CHAR(CurrDate,'Q')) <= 2

      THEN 'H'

        || 1

        || '-'

        || TO_CHAR(CurrDate,'YYYY')

      ELSE 'H'

        || 2

        || '-'

        || TO_CHAR(CurrDate,'YYYY')

    END                      AS half_of_year_id,

    TO_CHAR(CurrDate,'YYYY') AS Year_ID

  FROM

    (SELECT level n,

      -- Calendar starts at the day after this date.

      TO_DATE('31/12/2017','DD/MM/YYYY') + NUMTODSINTERVAL(level,'DAY') CurrDate

    FROM dual

      -- Change for the number of days to be added to the table.

      CONNECT BY level <= 365

    )

  )

SELECT day_id,

  day_time_span,

  day_end_date,

  week_day_full,

  week_day_short,

  day_num_of_week,

  day_num_of_month,

  day_num_of_year,

  month_id,

  COUNT(*) OVER (PARTITION BY month_id)    AS Month_Time_Span,

  MAX(day_id) OVER (PARTITION BY month_id) AS Month_End_Date,

  month_short_desc,

  month_long_desc,

  month_short,

  month_long,

  month_num_of_year,

  quarter_id,

  COUNT(*) OVER (PARTITION BY quarter_id)    AS Quarter_Time_Span,

  MAX(day_id) OVER (PARTITION BY quarter_id) AS Quarter_End_Date,

  quarter_num_of_year,

  half_num_of_year,

  half_of_year_id,

  COUNT(*) OVER (PARTITION BY half_of_year_id)    AS Half_Year_Time_Span,

  MAX(day_id) OVER (PARTITION BY half_of_year_id) AS Half_Year_End_Date,

  year_id,

  COUNT(*) OVER (PARTITION BY year_id)    AS Year_Time_Span,

Identified Dimension Tables

  MAX(day_id) OVER (PARTITION BY year_id) AS Year_End_Date

FROM base_calendar

ORDER BY day_id;

alter table dimcalendar

add primary key (Day_id);

FactPerformance

CREATE TABLE FactPerformance (

  Ticktno VARCHAR(45) NOT NULL,

  TheatreID VARCHAR(10) NOT NULL,

  Pid VARCHAR(10) NOT NULL,

  showhour int NOT NULL,

showminute int not null,

  ClientID VARCHAR(10) NOT NULL,

  amount DECIMAL(10,2) NULL,

  Deliverymethod varchar(25) NOT NULL,

  PaymentMethod varchar(25) NOT NULL,

  PerformanceDate Date,

  PRIMARY KEY (Ticktno),

  CONSTRAINT fk_Performance_Theatre

    FOREIGN KEY (TheatreID)

    REFERENCES DimTheatre (TheatreID),

  CONSTRAINT fk_Performance_date

    FOREIGN KEY (performancedate)

    REFERENCES dimcalendar (day_ID),

  CONSTRAINT fk_Performance_Production1

    FOREIGN KEY (Pid)

    REFERENCES DimProduction (Pid),

  CONSTRAINT fk_Performance_Showtime1

    FOREIGN KEY (showhour,showminute)

    REFERENCES DimShowtime (hour,minute),

  CONSTRAINT fk_FactPerformance_DimClient1

    FOREIGN KEY (ClientID)

    REFERENCES DimClient (ClientID));

DimTheatre

Figure 3: Data sources mapped for Dimension table theatre

Source: created by author

DimClient

Figure 4: Data sources mapped for Dimension table client

Source: created by author

DimShowtime

Figure 5: Data sources mapped for Dimension table showtime

Source: created by author

DimProduction

 

Figure 6: Data sources mapped for Dimension table production

Source: created by author

FactPerformance

Figure 7: Data sources mapped for Fact table FactPerformance

Source: created by author

DimClient

insert into dimclient

select * from client;

Dimshowtime

insert into dimshowtime

select distinct  comments,phour, pminute from performance;

DimTheatre

insert into dimtheatre

select * from theatre;

DimProduction

insert into dimproduction

select * from production;

FactPerformance

insert into factperformance

select t.purchase, p.theatreid,p.p,

p.phour,p.pminute,t.clientid,t.totalamount,

t.deliverymethod,t.paymentmethod,p.pdate

from

performance p

inner join

ticketpurchase t

on t.per=p.per;

Query1

select sum(fp.amount) as "Total Sales",

extract(year from fp.performancedate) as "Year",

dt.name from factperformance fp

inner join dimtheatre dt on

dt.theatreid=fp.theatreid group by (dt.name,extract(year from fp.performancedate));

 

Figure 8: Output of Query 1

Source: created by author

Query2

select c.name from dimclient c,

factperformance f where f.clientid=c.clientid  group by c.name

having count(extract (month from f.performancedate)) >4;

 

Figure 9: Output of Query 1

Source: created by author

Query3

select  sum(fp.amount) as "Total sales",

p.title, p.pdirector,

p.playauthor from factperformance fp

inner join dimproduction p on

p.pid=fp.pid group by (p.title,p.pdirector,p.playauthor)

order by sum(fp.amount) desc;

 

Figure 10: Output of Query 3

Source: created by author

A Data Mart is basically represented as subset of the relational database as the data in the Data Mart is extracted from the relational databases. Although some of the data is generated from the other sources. For example, for better date reference a Calendar table is created and populated here with the help of procedures and formulas. The date key here is referenced to the date column in the fact table. Also, it does not need to populate dates while the system is in use as it will automatically can be referred to the calendar table as it holds all the dates and same dates are not needed to be input in the fact table more than once. Hence, the size of the Data Mart is comparatively less than the Relational databases. Apart from that, the transaction reports are clear as the each particular transaction is stored here as a separate row. Also the relational models requires a lot of joining tables for the query. Whereas the Data Mart has stored all the factual data in the one table. If dimension attributes are needed in the query then also it requires minimum joining conditions.

Anand, N. and Kumar, M., 2013, July. Modeling and optimization of extraction-transformation-loading (ETL) processes in data warehouse: An overview. In 2013 Fourth International Conference on Computing, Communications and Networking Technologies (ICCCNT) (pp. 1-5). IEEE.

Jukic, N., Vrbsky, S. and Nestorov, S., 2016. Database systems: Introduction to databases and data warehouses. Prospect Press.

Mansmann, S., Rehman, N.U., Weiler, A. and Scholl, M.H., 2014. Discovering OLAP dimensions in semi-structured data. Information Systems, 44, pp.120-133.

Sadoghi, M., Bhattacherjee, S., Bhattacharjee, B. and Canim, M., 2016. L-Store: A real-time OLTP and OLAP system. arXiv preprint arXiv:1601.04084.

Selene Xia, B. and Gong, P., 2014. Review of business intelligence through data analysis. Benchmarking: An International Journal, 21(2), pp.300-311.

Sharma, S. and Jain, R., 2014, February. Modeling ETL process for data warehouse: an exploratory study. In 2014 Fourth International Conference on Advanced Computing & Communication Technologies (pp. 271-276). IEEE.

Sidi, E., El Merouani, M., Amin, E. and Abdelouarit, A., 2016. Star Schema Advantages on Data Warehouse: Using Bitmap Index and Partitioned Fact Tables. International Journal of Computer Applications, 975, p.8887.

Sohail, A. and Dominic, P.D.D., 2015. From ER model to star model: a systematic transformation approach. International Journal of Business Information Systems, 18(3), pp.249-267.