Theatre Event DBMS Retrofit Analysis and Design – Oracle 12c

Posted on by Fangzhou

Click to read the full report: Fangzhou Cheng Database Final Project


Event Management Database Retrofit Analysis and Design

Database Design and Management Class Final Project
NYU MASY-GC-2500 Fall 2014
Instructor: Marc S. Paller
Submitted By: Fangzhou Cheng
Submitted On: October 20, 2014

Project source code: https://github.com/funjo/BPAC_database_retrofit

BPAC vertical logo outline black

I. Overview

A. Background information

Baruch Performing Arts Center (BPAC) is a part of the Baruch College community. It has four venues and an average of 600 shows in total each year. These venues are available for outside companies to rent, internal student related events, and co-productions. An event management database designed by the Baruch IT department is currently in use.

The database contains specific production information including show names, venues, time frames, production company information, contact information, etc. The BPAC staff uses the database for recording and retrieving contract information, preparing for technical support, making staffing plans, and preparing for the audience.

B. Scope

This project will analyze, design, and create a replacement relational event management database for BPAC. Scope of the project shall encompass the following:

  • Planning and Requirement Gathering
  • Conceptual Model (Enhanced E-R Diagram)
  • Logical Database Model
  • Normalization of Relations
  • Creating of Relational Database Tables
  • Sample Queries

C. Business Purpose

Due to insufficient design of the database, not enough information is included in the DBMS. The staff has to go to 3 other separate places (Local server, Google Drive, and paper file folder) to gather all information needed for a show. During the busiest time, the staff has to work extra hours coordinating and organizing (include scanning and printing) all the files in different sources. The work efficiency is sacrificed and the morale stays low. This project aims to design a replacement event management system for BPAC to include all the information.

II. Planning

A. Current State

Currently, the productions team of BPAC maintains 4 different methods to store files related to a show: database, folders on local server, Google Drive, and paper files. The database in use contains only the basic event information (e.g., show names, venues, time frames, production company information, contact information, etc.) from the initial contracts. The following graph shows the project management phases involved in the show productions, the main documents generated and their storage methods:

Untitled1

B. Proposal

A replacement event management database shall be built to hold all data necessary to generate the documents specified in the project management process above. Key reports are listed below:

  • Pricing Offer Overview
  • Technical/Front of House Staffing Schedule
  • Technical Support Details
  • External Pass Information (for Baruch Security Office)
  • Monthly Event Overview
  • External Client Information

III. Conceptual Model

A. Enterprise diagram

The figure below is a high-level enterprises diagram of the event management model to be used:

Untitled2

B. Enhanced E-R Diagram

The figure below shows all the entities and their relationships that are used in the database system:

Untitled3

C. Business Rules

  1. There are three types of clients: outside production company (charged), Baruch College (uncharged), and co-production (cooperation between BPAC and outside production company). They are not overlapped.
  2. One client can have many events.
  3. One theatre can only have one event going on during one time frame.
  4. One theatre can hold many events during one day at different time frames.
  5. One event can have many time frames (be produced at different times on different days).
  6. One event can only be produced in one theatre.
  7. Two types of staff are involved in the staffing schedules: Front of House (FOH) staff and productions staff. They are not overlapped.
  8. One event can have many technical requirements.
  9. Many productions/FOH staff can work together on one event.
  10. One staff only has one working time frame during one day (they only sign in and sign out once).

IV. Logical Database Model

A. Normalized Relations

Relations converted from the EER Diagram are already in 3NF as listed:

THEATRE (ID, Name, Location)
TIME_FRAME (Start_Time, End_Time, Theatre_ID, Event_ID)
EVENT (ID, Name, Theatre_ID, Tech_Note, FOH_Note, Box_Office_Note, Marketing_Note, Lobbies_Note, Client_ID, Production_Meeting, Tech_Checkin, House_Checkin, Final_Payment)
FOH_TASK (Staff_ID, Event_ID, Start_Time, End_Time)
STAFF (ID, Name, Type)
TECH_REQUIREMENT (Event_ID, Projector, Screen, Microphone_W, Microphone_Wless, Lighting, Communication, Note)
PRODUCTIONS_TASK (Staff_ID, Event_ID, Start_Time, End_Time)
CLIENT (ID, Type)
PRODUCTION_COMPANY (ID, Name, Contact)
SCHOOL (ID, Department, Contact)
CO-PRODUCTION (ID, Project_Manager_ID)

B. SQL Developer Relational Model

Untitled4

V. Physical Database Mode

A. The DDL and DML

Since Oracle is used for this database system, the DDL (Data Definition Language) is used to generate all the essential tables, and the DML (Data Manipulation Language) is used to populate some test data for testing purpose.

/* script name: CREATEBPAC.SQL */
/* purpose: Build Oracle tables for Baruch Performing Arts Center (BPAC) */
/* date: 20 Oct 2014 */
/* owner: Fangzhou Cheng */


/* Drop all tables before creating tables */

DROP TABLE FOH_TASK CASCADE CONSTRAINTS ;
DROP TABLE STAFF CASCADE CONSTRAINTS ;
DROP TABLE PRODUCTIONS_TASK CASCADE CONSTRAINTS ;
DROP TABLE EVENT CASCADE CONSTRAINTS ;
DROP TABLE TECH_REQUIREMENT CASCADE CONSTRAINTS ;
DROP TABLE THEATRE CASCADE CONSTRAINTS ;
DROP TABLE TIME_FRAME CASCADE CONSTRAINTS ;
DROP TABLE CLIENT CASCADE CONSTRAINTS ;
DROP TABLE PRODUCTION_COMPANY CASCADE CONSTRAINTS ;
DROP TABLE SCHOOL CASCADE CONSTRAINTS ;


/* Create all BPAC Database Tables (11) */

Create table THEATRE(
ID Number(4) Not Null,
Name Varchar2(25) Not Null,
Location Varchar2(100) Not Null,
Constraint theatre_PK PRIMARY KEY (ID)
);

Create table STAFF(
ID Number Not Null,
First_Name Varchar2(15) Not Null,
Last_Name Varchar2(15) Not Null,
Type Varchar2(20) ,
Constraint staff_pk PRIMARY KEY (ID)
);

Create table CLIENT(
ID Number(7) Not Null,
Type Varchar2(20) ,
Constraint client_pk PRIMARY KEY (ID)
);

Create table EVENT(
ID Number(10) Not Null,
Client_ID Number(7) Not Null,
Theatre_ID Number(4) Not Null,
Name Varchar2(100) Not Null,
Tech_Note Varchar2(300) ,
FOH_Note Varchar2(300) ,
Box_Office_Note Varchar2(300) ,
Marketing_Note Varchar2(300) ,
Lobbies_Note Varchar2(300) ,
Production_Meeting Date ,
Tech_Checkin Date ,
House_Checkin Date ,
Final_Payment Date ,
Constraint event_pk PRIMARY KEY (ID),
Constraint event_client_fk FOREIGN KEY (Client_ID)
 REFERENCES CLIENT(ID),
Constraint event_theatre_fk FOREIGN KEY (Theatre_ID)
 REFERENCES THEATRE(ID)
);

Create table TIME_FRAME(
Start_Time Timestamp Not Null,
End_Time Timestamp Not Null,
Theatre_ID Number(4) Not Null,
Event_ID Varchar2(10) Not Null,
Constraint time_frame PRIMARY KEY (Start_Time, End_Time, Theatre_ID),
Constraint time_frame_theatre_fk FOREIGN KEY (THEATRE_ID)
 REFERENCES THEATRE(ID)
);

Create table FOH_TASK(
Staff_ID Number(7) Not Null,
Event_ID Number(10) Not Null,
Start_Time Timestamp Not Null,
End_Time Timestamp Not Null,
Constraint foh_task_pk PRIMARY KEY (Start_Time, End_Time, Event_ID, Staff_ID),
Constraint foh_task_staff_fk FOREIGN KEY (Staff_ID)
 REFERENCES STAFF(ID),
Constraint foh_task_event_fk FOREIGN KEY (Event_ID)
 REFERENCES EVENT(ID)
);

Create table TECH_REQUIREMENT(
Event_ID Number(10) Not Null,
Projector Varchar2(100) ,
Screen Varchar2(100) ,
Microphone_W Varchar2(100) ,
Microphone_Wless Varchar2(100) ,
Lighting Varchar2(100) ,
Communication Varchar2(100) ,
Note Varchar2(300) ,
Constraint tech_pk PRIMARY KEY (EVENT_ID),
Constraint tech_Fk FOREIGN KEY (EVENT_ID)
 REFERENCES EVENT(ID)
);

Create table PRODUCTIONS_TASK(
Staff_ID Number(7) Not Null,
Event_ID Number(10) Not Null,
Start_Time Timestamp ,
End_Time Timestamp ,
Constraint productions_task_pk PRIMARY KEY (Start_Time, End_Time, Staff_ID, Event_ID),
Constraint productions_task_staff_fk FOREIGN KEY (Staff_ID)
 REFERENCES STAFF(ID),
Constraint productions_task_event_fk FOREIGN KEY (Event_ID)
 REFERENCES TECH_REQUIREMENT(Event_ID)
);

Create table PRODUCTION_COMPANY(
ID Number(7) Not Null,
Name Varchar2(50) Not Null,
Contact Varchar2(500) Not Null,
Constraint production_company_pk PRIMARY KEY (ID),
Constraint production_company_fk FOREIGN KEY (ID)
 REFERENCES CLIENT(ID)
);

Create table SCHOOL(
ID Number(7) Not Null,
Department Varchar2(100) Not Null,
Contact Varchar2(500) Not Null,
Constraint school_pk PRIMARY KEY (ID),
Constraint school_fk FOREIGN KEY (ID)
 REFERENCES CLIENT(ID)
);

Create table COPRODUCTION(
ID Number(7) Not Null,
Project_Manager_ID Number(7) Not Null,
Constraint coproduction_pk PRIMARY KEY (ID),
Constraint coproduction_fk FOREIGN KEY (ID)
 REFERENCES CLIENT(ID)
);


/* Run Oracle specific command to display table structure in DB */

describe FOH_TASK;
describe STAFF;
describe PRODUCTIONS_TASK;
describe EVENT;
describe TECH_REQUIREMENTS;
describe THEATRE;
describe TIME_FRAME;
describe CLIENT;
describe PRODUCTION_COMPANY;
describe SCHOOL;
describe COPRODUCTION;


/* show constraints for each table */

COMMIT;


/* script name: LOADBPAC.SQL */
/* purpose: Load Oracle tables for Baruch Performing 
 Arts Center (BPAC) productions team */
/* date: 20 Oct 2014 */
/* owner: Fangzhou Cheng */


/* make sure tables are empty before adding records */

delete from FOH_TASK;
delete from STAFF;
delete from PRODUCTIONS_TASK;
delete from EVENT;
delete from TECH_REQUIREMENT;
delete from THEATRE;
delete from TIME_FRAME;
delete from CLIENT;
delete from PRODUCTION_COMPANY;
delete from SCHOOL;
delete from COPRODUCTION;


/* load all tables for testing purpose */

insert into THEATRE values ( 1, 'Engelman Recital Hall','55 Lexington Ave., NYC');
insert into THEATRE values ( 2, 'Mason Hall','17 Lexington Ave. at 23rd St, NYC');
insert into THEATRE values ( 3, 'Nagelberg Theater','55 Lexington Ave, NYC');
insert into THEATRE values ( 4, 'Bernie West Theater','17 Lexington Ave @ 23rd St., NYC');

insert into STAFF values ( 1, 'John','Malatesta','Admin');
insert into STAFF values ( 2, 'Ariadne','Condos','Admin');
insert into STAFF values ( 3, 'Robert','McGinnis','Productions');
insert into STAFF values ( 4, 'Rachel','Gilmore','Productions');
insert into STAFF values ( 5, 'Geoffrey','Barnes','Productions');
insert into STAFF values ( 6, 'Tsubasa','Kamei','Productions');
insert into STAFF values ( 7, 'Paul','Riznyk','Productions');
insert into STAFF values ( 8, 'Brian','Sierra','Productions');
insert into STAFF values ( 9, 'Justin','Chick','Productions');
insert into STAFF values ( 10, 'Robert','Bronstein','FOH');
insert into STAFF values ( 11, 'Justin','Chick','FOH');
insert into STAFF values ( 12, 'Fama','Gueye','Admin');
insert into STAFF values ( 13, 'Mecca','Meyers','Admin');
insert into STAFF values ( 14, 'Brian','Sierra','Productions');
insert into STAFF values ( 15, 'Joan','Weinberger','Admin');
insert into STAFF values ( 16, 'Jeffrey','Wigton','Boxoffice');
insert into STAFF values ( 17, 'Sana','Shaheed','Boxoffice');
insert into STAFF values ( 18, 'Liz','Skollar','Boxoffice');

insert into CLIENT values ( 1, 'Coproduction');
insert into CLIENT values ( 2, 'Production Company');
insert into CLIENT values ( 3, 'Coproduction');
insert into CLIENT values ( 4, 'Production Company');
insert into CLIENT values ( 5, 'Production Company');
insert into CLIENT values ( 6, 'School');
insert into CLIENT values ( 7, 'Coproduction');
insert into CLIENT values ( 8, 'School');

insert into EVENT values ( 1, 1, 3, 'Play of the Western World', 'All recorded music; No Video Tape', 'General Seating; 1 House Manager plus 3 Ushars for all event with audience; 15 Minute Intermission', 'Ticket Sales through our box office online; Tickets Sales through our box office in personal during open hours; Phone Sales through Ovation Tix', 'Listing in our Weekly Eblast; BPAC flyering (We do not provide fliers)','Reception: TBD', to_timestamp('25-JUL-14','DD-MON-RR HH:MI AM'), to_timestamp('19-SEP-14','DD-MON-RR HH:MI AM'), to_timestamp('03-OCT-14','DD-MON-RR HH:MI AM'), to_timestamp('10-OCT-14','DD-MON-RR HH:MI AM'));
insert into EVENT values ( 2, 1, 3, 'Karen Finley: WRITTEN IN SAND', '', '', '', '','', to_timestamp('','DD-MON-RR HH:MI AM'), to_timestamp('','DD-MON-RR HH:MI AM'), to_timestamp('','DD-MON-RR HH:MI AM'), to_timestamp('','DD-MON-RR HH:MI AM'));
insert into EVENT values ( 3, 3, 4, 'All About Amy/ Barrio Boy/ Aban/ Khorshid', '', '', '', '','', to_timestamp('','DD-MON-RR HH:MI AM'), to_timestamp('','DD-MON-RR HH:MI AM'), to_timestamp('','DD-MON-RR HH:MI AM'), to_timestamp('','DD-MON-RR HH:MI AM'));
insert into EVENT values ( 4, 7, 3, 'Playboy of the Western World', '', '', '', '','', to_timestamp('','DD-MON-RR HH:MI AM'), to_timestamp('','DD-MON-RR HH:MI AM'), to_timestamp('','DD-MON-RR HH:MI AM'), to_timestamp('','DD-MON-RR HH:MI AM'));
insert into EVENT values ( 5, 6, 1, '6th Annual Baruch College Holiday Concert: “A Not So Silent Night”', '', '', '', '','', to_timestamp('','DD-MON-RR HH:MI AM'), to_timestamp('','DD-MON-RR HH:MI AM'), to_timestamp('','DD-MON-RR HH:MI AM'), to_timestamp('','DD-MON-RR HH:MI AM'));
insert into EVENT values ( 6, 1, 3, 'GRIND: THE MOVIE', '', '', '', '','', to_timestamp('','DD-MON-RR HH:MI AM'), to_timestamp('','DD-MON-RR HH:MI AM'), to_timestamp('','DD-MON-RR HH:MI AM'), to_timestamp('','DD-MON-RR HH:MI AM'));
insert into EVENT values ( 7, 2, 3, 'Murakami Music: Stories of Loss and Nostalgia', '', '', '', '','', to_timestamp('','DD-MON-RR HH:MI AM'), to_timestamp('','DD-MON-RR HH:MI AM'), to_timestamp('','DD-MON-RR HH:MI AM'), to_timestamp('','DD-MON-RR HH:MI AM'));
insert into EVENT values ( 8, 4, 3, 'A Wake or a Wedding', '', '', '', '','', to_timestamp('','DD-MON-RR HH:MI AM'), to_timestamp('','DD-MON-RR HH:MI AM'), to_timestamp('','DD-MON-RR HH:MI AM'), to_timestamp('','DD-MON-RR HH:MI AM'));
insert into EVENT values ( 9, 5, 1, 'MAC presents They Write The Songs', '', '', '', '','', to_timestamp('','DD-MON-RR HH:MI AM'), to_timestamp('','DD-MON-RR HH:MI AM'), to_timestamp('','DD-MON-RR HH:MI AM'), to_timestamp('','DD-MON-RR HH:MI AM'));
insert into EVENT values ( 10, 8, 1, 'Journey to Planet Earth: “Extreme Realities”', '', '', '', '','', to_timestamp('','DD-MON-RR HH:MI AM'), to_timestamp('','DD-MON-RR HH:MI AM'), to_timestamp('','DD-MON-RR HH:MI AM'), to_timestamp('','DD-MON-RR HH:MI AM'));

insert into TIME_FRAME values (to_timestamp('21-OCT-14 08.00 PM','DD-MON-RR HH:MI AM'), to_timestamp('21-OCT-14 09.30 PM','DD-MON-RR HH:MI AM'), 3, 2);
insert into TIME_FRAME values (to_timestamp('24-OCT-14 07.00 PM','DD-MON-RR HH.MI AM'), to_timestamp('21-OCT-14 09.00 PM','DD-MON-RR HH.MI AM'), 3, 1);
insert into TIME_FRAME values (to_timestamp('22-OCT-14 07.30 PM','DD-MON-RR HH.MI AM'), to_timestamp('22-OCT-14 09.30 PM','DD-MON-RR HH.MI AM'), 4, 3);
insert into TIME_FRAME values (to_timestamp('22-OCT-14 08.00 PM','DD-MON-RR HH.MI AM'), to_timestamp('22-OCT-14 10.00 PM','DD-MON-RR HH.MI AM'), 3, 4);
insert into TIME_FRAME values (to_timestamp('23-OCT-14 08.00 PM','DD-MON-RR HH.MI AM'), to_timestamp('23-OCT-14 10.00 PM','DD-MON-RR HH.MI AM'), 3, 4);
insert into TIME_FRAME values (to_timestamp('16-DEC-14 07.00 PM','DD-MON-RR HH.MI AM'), to_timestamp('16-DEC-14 08.00 PM','DD-MON-RR HH.MI AM'), 1, 5);
insert into TIME_FRAME values (to_timestamp('27-OCT-14 07.00 PM','DD-MON-RR HH.MI AM'), to_timestamp('23-OCT-14 9.00 PM','DD-MON-RR HH.MI AM'), 3, 6);
insert into TIME_FRAME values (to_timestamp('01-NOV-14 08.00 PM','DD-MON-RR HH.MI AM'), to_timestamp('01-NOV-14 10.00 PM','DD-MON-RR HH.MI AM'), 3, 7);
insert into TIME_FRAME values (to_timestamp('13-NOV-14 08.00 PM','DD-MON-RR HH.MI AM'), to_timestamp('13-NOV-14 10.00 PM','DD-MON-RR HH.MI AM'), 3, 8);
insert into TIME_FRAME values (to_timestamp('14-NOV-14 08.00 PM','DD-MON-RR HH.MI AM'), to_timestamp('14-NOV-14 10.00 PM','DD-MON-RR HH.MI AM'), 3, 8);
insert into TIME_FRAME values (to_timestamp('15-NOV-14 08.00 PM','DD-MON-RR HH.MI AM'), to_timestamp('15-NOV-14 10.00 PM','DD-MON-RR HH.MI AM'), 3, 8);
insert into TIME_FRAME values (to_timestamp('16-NOV-14 03.00 PM','DD-MON-RR HH.MI AM'), to_timestamp('16-NOV-14 3.00 PM','DD-MON-RR HH.MI AM'), 3, 8);
insert into TIME_FRAME values (to_timestamp('16-NOV-14 04.00 PM','DD-MON-RR HH.MI AM'), to_timestamp('16-NOV-14 6.00 PM','DD-MON-RR HH.MI AM'), 1, 9);
insert into TIME_FRAME values (to_timestamp('02-DEC-14 06.00 PM','DD-MON-RR HH.MI AM'), to_timestamp('02-DEC-14 9.00 PM','DD-MON-RR HH.MI AM'), 1, 10);

insert into FOH_TASK values ( 10, 1, to_timestamp('24-OCT-14 07.00 PM','DD-MON-RR HH.MI AM'), to_timestamp('21-OCT-14 09.00 PM','DD-MON-RR HH.MI AM'));
insert into FOH_TASK values ( 10, 2, to_timestamp('21-OCT-14 08.00 PM','DD-MON-RR HH.MI AM'), to_timestamp('21-OCT-14 09.30 PM','DD-MON-RR HH.MI AM'));
insert into FOH_TASK values ( 11, 3, to_timestamp('22-OCT-14 07.30 PM','DD-MON-RR HH.MI AM'), to_timestamp('22-OCT-14 09.30 PM','DD-MON-RR HH.MI AM'));
insert into FOH_TASK values ( 10, 4, to_timestamp('22-OCT-14 08.00 PM','DD-MON-RR HH.MI AM'), to_timestamp('22-OCT-14 10.00 PM','DD-MON-RR HH.MI AM'));
insert into FOH_TASK values ( 11, 4, to_timestamp('23-OCT-14 08.00 PM','DD-MON-RR HH.MI AM'), to_timestamp('23-OCT-14 10.00 PM','DD-MON-RR HH.MI AM'));
insert into FOH_TASK values ( 10, 5, to_timestamp('16-DEC-14 07.00 PM','DD-MON-RR HH.MI AM'), to_timestamp('16-DEC-14 08.00 PM','DD-MON-RR HH.MI AM'));
insert into FOH_TASK values ( 11, 6, to_timestamp('27-OCT-14 07.00 PM','DD-MON-RR HH.MI AM'), to_timestamp('23-OCT-14 9.00 PM','DD-MON-RR HH.MI AM'));
insert into FOH_TASK values ( 10, 7, to_timestamp('01-NOV-14 08.00 PM','DD-MON-RR HH.MI AM'), to_timestamp('01-NOV-14 10.00 PM','DD-MON-RR HH.MI AM'));
insert into FOH_TASK values ( 10, 8, to_timestamp('13-NOV-14 08.00 PM','DD-MON-RR HH.MI AM'), to_timestamp('13-NOV-14 10.00 PM','DD-MON-RR HH.MI AM'));
insert into FOH_TASK values ( 11, 8, to_timestamp('14-NOV-14 08.00 PM','DD-MON-RR HH.MI AM'), to_timestamp('14-NOV-14 10.00 PM','DD-MON-RR HH.MI AM'));
insert into FOH_TASK values ( 10, 8, to_timestamp('15-NOV-14 08.00 PM','DD-MON-RR HH.MI AM'), to_timestamp('15-NOV-14 10.00 PM','DD-MON-RR HH.MI AM'));
insert into FOH_TASK values ( 11, 8, to_timestamp('16-NOV-14 03.00 PM','DD-MON-RR HH.MI AM'), to_timestamp('16-NOV-16 3.00 PM','DD-MON-RR HH.MI AM'));
insert into FOH_TASK values ( 10, 9, to_timestamp('16-NOV-14 04.00 PM','DD-MON-RR HH.MI AM'), to_timestamp('16-NOV-14 6.00 PM','DD-MON-RR HH.MI AM'));
insert into FOH_TASK values ( 11, 10, to_timestamp('02-DEC-14 06.00 PM','DD-MON-RR HH.MI AM'), to_timestamp('02-DEC-14 9.00 PM','DD-MON-RR HH.MI AM'));

insert into TECH_REQUIREMENT values ( '1', '', '', '1', '1', '1', '1', '');
insert into TECH_REQUIREMENT values ( '2', '1', '1', '2', '', '1', '1', '');
insert into TECH_REQUIREMENT values ( '3', '1', '1', '1', '1', '', '', '');
insert into TECH_REQUIREMENT values ( '4', '1', '1', '', '5', '1', '', '');
insert into TECH_REQUIREMENT values ( '5', '1', '1', '2', '', '', '', '');
insert into TECH_REQUIREMENT values ( '6', '', '', '1', '2', '', '', '');
insert into TECH_REQUIREMENT values ( '7', '', '', '1', '2', '1', '1', '');
insert into TECH_REQUIREMENT values ( '8', '1', '1', '3', '1', '', '', '');
insert into TECH_REQUIREMENT values ( '9', '1', '1', '', '2', '', '', '');
insert into TECH_REQUIREMENT values ( '10', '', '', '2', '', '', '1', '');

insert into PRODUCTIONS_TASK values ( 9, 1, to_timestamp('24-OCT-14 07.00 PM','DD-MON-RR HH.MI AM'), to_timestamp('21-OCT-14 09.00 PM','DD-MON-RR HH.MI AM'));
insert into PRODUCTIONS_TASK values ( 5, 1, to_timestamp('24-OCT-14 07.00 PM','DD-MON-RR HH.MI AM'), to_timestamp('21-OCT-14 09.00 PM','DD-MON-RR HH.MI AM'));
insert into PRODUCTIONS_TASK values ( 4, 2, to_timestamp('21-OCT-14 08.00 PM','DD-MON-RR HH.MI AM'), to_timestamp('21-OCT-14 09.30 PM','DD-MON-RR HH.MI AM'));
insert into PRODUCTIONS_TASK values ( 7, 2, to_timestamp('21-OCT-14 08.00 PM','DD-MON-RR HH.MI AM'), to_timestamp('21-OCT-14 09.30 PM','DD-MON-RR HH.MI AM'));
insert into PRODUCTIONS_TASK values ( 7, 3, to_timestamp('22-OCT-14 07.30 PM','DD-MON-RR HH.MI AM'), to_timestamp('22-OCT-14 09.30 PM','DD-MON-RR HH.MI AM'));
insert into PRODUCTIONS_TASK values ( 8, 4, to_timestamp('22-OCT-14 08.00 PM','DD-MON-RR HH.MI AM'), to_timestamp('22-OCT-14 10.00 PM','DD-MON-RR HH.MI AM'));
insert into PRODUCTIONS_TASK values ( 6, 4, to_timestamp('22-OCT-14 08.00 PM','DD-MON-RR HH.MI AM'), to_timestamp('22-OCT-14 10.00 PM','DD-MON-RR HH.MI AM'));
insert into PRODUCTIONS_TASK values ( 6, 4, to_timestamp('23-OCT-14 08.00 PM','DD-MON-RR HH.MI AM'), to_timestamp('23-OCT-14 10.00 PM','DD-MON-RR HH.MI AM'));
insert into PRODUCTIONS_TASK values ( 10, 4, to_timestamp('23-OCT-14 08.00 PM','DD-MON-RR HH.MI AM'), to_timestamp('23-OCT-14 10.00 PM','DD-MON-RR HH.MI AM'));
insert into PRODUCTIONS_TASK values ( 14, 8, to_timestamp('13-NOV-14 08.00 PM','DD-MON-RR HH.MI AM'), to_timestamp('13-NOV-14 10.00 PM','DD-MON-RR HH.MI AM'));
insert into PRODUCTIONS_TASK values ( 11, 8, to_timestamp('14-NOV-14 08.00 PM','DD-MON-RR HH.MI AM'), to_timestamp('14-NOV-14 10.00 PM','DD-MON-RR HH.MI AM'));
insert into PRODUCTIONS_TASK values ( 11, 8, to_timestamp('15-NOV-14 08.00 PM','DD-MON-RR HH.MI AM'), to_timestamp('15-NOV-14 10.00 PM','DD-MON-RR HH.MI AM'));
insert into PRODUCTIONS_TASK values ( 8, 8, to_timestamp('16-NOV-14 03.00 PM','DD-MON-RR HH.MI AM'), to_timestamp('16-NOV-16 3.00 PM','DD-MON-RR HH.MI AM'));
insert into PRODUCTIONS_TASK values ( 10, 9, to_timestamp('16-NOV-14 04.00 PM','DD-MON-RR HH.MI AM'), to_timestamp('16-NOV-14 6.00 PM','DD-MON-RR HH.MI AM'));
insert into PRODUCTIONS_TASK values ( 9, 9, to_timestamp('16-NOV-14 04.00 PM','DD-MON-RR HH.MI AM'), to_timestamp('16-NOV-14 6.00 PM','DD-MON-RR HH.MI AM'));
insert into PRODUCTIONS_TASK values ( 5, 9, to_timestamp('16-NOV-14 04.00 PM','DD-MON-RR HH.MI AM'), to_timestamp('16-NOV-14 6.00 PM','DD-MON-RR HH.MI AM'));
insert into PRODUCTIONS_TASK values ( 13, 10, to_timestamp('02-DEC-14 06.00 PM','DD-MON-RR HH.MI AM'), to_timestamp('02-DEC-14 9.00 PM','DD-MON-RR HH.MI AM'));
insert into PRODUCTIONS_TASK values ( 4, 10, to_timestamp('02-DEC-14 06.00 PM','DD-MON-RR HH.MI AM'), to_timestamp('02-DEC-14 9.00 PM','DD-MON-RR HH.MI AM'));

insert into PRODUCTION_COMPANY values ( 2, 'Eunbikimmusic','http://www.eunbikimmusic.com/; the cell: 338W 23rd st New York, NY 10011; email: thecelltheatre@gmail.org; Tel / Fax : (646) 861-2253');
insert into PRODUCTION_COMPANY values ( 4, 'Encompass New Opera Theatre','Nancy Rhodes: Artistic Director; Tel: (718) 398-4675; Fax: (718) 398-4684; Email: encompassopera@yahoo.com');
insert into PRODUCTION_COMPANY values ( 5, 'MACnyc','www.MACnyc.com; 212-465-2662; info@macnyc.com; Manhattan Association of Cabarets and Clubs');

insert into SCHOOL values ( 6, 'BPAC','Robert Mcginnis; Tel: 646.312.4086; email: robert.mcginnis@baruch.cuny.edu');
insert into SCHOOL values ( 8, 'Baruch College Task Force on Sustainability','646-312-1000; Sustainability@baruch.cuny.edu; http://blogs.baruch.cuny.edu/sustainability/');

insert into COPRODUCTION values ( 1, 3);
insert into COPRODUCTION values ( 3, 4);
insert into COPRODUCTION values ( 7, 7);

/* save all changes to table rows */
COMMIT;


/* run basic queries over every table to verify data */

SELECT * FROM FOH_TASK;
SELECT * FROM STAFF;
SELECT * FROM PRODUCTIONS_TASK;
SELECT * FROM EVENT;
SELECT * FROM TECH_REQUIREMENT;
SELECT * FROM THEATRE;
SELECT * FROM TIME_FRAME;
SELECT * FROM CLIENT;
SELECT * FROM PRODUCTION_COMPANY;
SELECT * FROM SCHOOL;
SELECT * FROM COPRODUCTION;

B. Database Tables

There are 11 tables created in replacement BPAC DBMS:

Untitled5

Example: Metadata Information of table EVENT

Untitled6

Example: Constraints on table EVENT

Untitled7

Example: Index on table EVENT

Untitled8

C. Queries

Pricing Offer Overview (for one show)

SELECT E.NAME AS EVENT, T.NAME AS THEATRE, E.TECH_NOTE AS TECH, E.Final_Payment, TF.START_TIME, TF.END_TIME
FROM THEATRE T, EVENT E, TIME_FRAME TF
WHERE E.THEATRE_ID = T.ID
AND TF.THEATRE_ID = T.ID
AND TF.EVENT_ID = E.ID
AND E.ID = 1;

Untitled9

Technical Staffing Schedule (for one show)

SELECT E.NAME, T.PROJECTOR AS PROJECTOR_NO, T.SCREEN AS SCREEN_NO, T.MICROPHONE_W AS WIRE_MICROPHONE_NO,
T.MICROPHONE_WLESS AS WIRELESS_MICROPHONE_NO, T.LIGHTING AS LIGHTING_NO, T.COMMUNICATION AS COMMUNICATION_NO
FROM EVENT E, TECH_REQUIREMENT T
WHERE E.ID = T.EVENT_ID
AND E.ID = 2;

Untitled10

Technical Support Details (for one show)

SELECT E.NAME AS EVENT, S.FIRST_NAME, S.LAST_NAME, P.START_TIME, P.END_TIME
FROM EVENT E, STAFF S, PRODUCTIONS_TASK P
WHERE E.ID = P.EVENT_ID
AND S.ID = P.STAFF_ID
AND E.ID = 4
ORDER BY P.START_TIME;

Untitled11

External Pass Information (for Baruch Security Office)

SELECT P.NAME AS COMPANY, T.NAME AS THEATRE, TF.START_TIME, TF.END_TIME
FROM PRODUCTION_COMPANY P, TIME_FRAME TF, EVENT E, THEATRE T
WHERE P.ID = E.CLIENT_ID
AND T.ID = E.THEATRE_ID
AND TF.EVENT_ID = E.ID;

Untitled12

Event Overview (by month)

SELECT E.NAME AS EVENT, T.NAME AS THEATRE, TO_CHAR(TF.START_TIME,'MONTH') AS MONTH
FROM TIME_FRAME TF, EVENT E, THEATRE T
WHERE E.THEATRE_ID = T.ID
AND E.ID = TF.EVENT_ID
ORDER BY MONTH DESC, EVENT;

Untitled13

External Client Information

SELECT P.ID AS CLIENT_ID, P.NAME AS PRODUCTION_COMPANY, E.NAME AS EVENT_NAME, P.CONTACT
FROM PRODUCTION_COMPANY P, EVENT E
WHERE E.CLIENT_ID = P.ID;

Untitled14

Advertisement

Leave a Reply

Fill in your details below or click an icon to log in:

Gravatar
WordPress.com Logo

You are commenting using your WordPress.com account. ( Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. ( Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. ( Log Out /  Change )

Cancel

Connecting to %s