Database Management System (DBMS) of online ticket sale for the stage play “Kusa-Paba” at the University of Calgary Theater.
Abstract and Project Description
The University of Calgary Theater has 500 available seats. This particular DBMS is built to allow users to order and purchase tickets online. Tickets sold by agents can also be entered to the system using the agents’ interface. Only a designated set of seats are available for sale. Some are as for reserved guests and others are designated as House Seats hence they are not available for sale. The designated seats for sale are categorized for different price levels; $50.00, $30.00 and $20.00 (Figure 1). The price levels are further broken down for adults and children.
This project has following features:
- Children – Age 3 and under will be free of charge and no seats will be assigned.
- Children – Age 4 to 12 (inclusive) can purchase tickets for half of the applicable ticket price.
Payments should be made to the ticket agents who will confirm the seats and issue requested tickets. There is no online credit card or money transfer system implemented in this particular system. However, the system can be modified to include third party payment processing companies such as PayPal. It is also possible to add direct payment system with site based SSL certificates, but this will require much greater degree of modification to the current DBMS.
There are two (2) main databases
1. Tickets administration, agent details and login details
2. Seat details, seat price and ticket ordering details
There are two (2) types of reports
a. Reports for users
b. Reports for ticket masters and ticketing agents
Introduction
The DBMS structure includes five (5) software components for;
1. Creating and changing the logical structure of a database
2. Querying and making changes to the information
3. Menus, data entry screens, reports and application software
4. Permission on who can use and see what information; backup and recovery
5. Queries to see the effect of structural changes
DBMS engine has data definition subsystem, data manipulation subsystem, data administration subsystem, and application generation subsystem.
Procedure
Data structure includes the following;
Database name: Ticket_order
Database type: flat
Database Tables:
Tickets
User_description
Accounting
For this particular report we will only discuss the Ticket_order: Tickets parameter.
Table name: Ticket_order=> Tickets
Description of the DBMS
Data definitions are preconfigured and parameters are assigned though the server SQL configuration. In this particular project, other variables and other options should be assigned before the ticket sales page is open. Once the ticket sales page is opened, the DBMS will carry 500 data records with each record relevant to each seat.
DBMS engine itself generates confirmation numbers respect to each ordered ticket for each user. DBMS generates another separate record to the ticket agents and to the ticket masters.
Table name: Ticket_order=>Tickets
When a ticket is reserved, the following recoding process is carried out using the database structure shown below. There are twenty (20) fields in the data table; Ticket_order=>Tickets. When the ticket is being generated, it will update only seven (7) fields.
rec_nmb_D
| 1 | A | A | 1 | 50 | 25 | 1 |
The record number is between 1 and 500 with each seat is assigned their own unique number.
TICKET_SECTION
| 1 | A | A | 1 | 50 | 25 | 1 |
The main section of the theater between A to F (Figure 1).
TICKET_ROW
| 1 | A | A | 1 | 50 | 25 | 1 |
The row of the relevant seat between A to L based on the theater seating plan (Figure 1).
TICKET_SEAT
| 1 | A | A | 1 | 50 | 25 | 1 |
The sea number based on the theater seating plan (Figure 1).
TICKET_PRICE
| 1 | A | A | 1 | 50 | 25 | 1 |
The price of the ticket for an adult.
TICKET_CHILD_PRICE
| 1 | A | A | 1 | 50 | 25 | 1 |
The price of the ticket for a child.
TICKET_CANORDER
| 1 | A | A | 1 | 50 | 25 | 1 |
Order restriction; value of 1 denotes a lock on the seat. This restriction allows guest/special guest seats to be reserved. In this particular project, Section B and E seats 1 to 5 are reserved.
Data Manipulation
To make modifications and deletion of data, there are three (3) types of data manipulation avenues.
a. By user
b. By ticket agent
c. By ticket master
By user
User can order tickets (seats) online though the web interfaces. The application software finds the record relevant to the selected seats and updates the database. To order tickets;
1. User should check the seating arrangement diagram (Figure 1)
2. Select the preferred section (A, B, C, D, E or F)
3. Check the available seats and ticket types (Figure bellow)
4. Input personal information such as Email, City, etc. and select payment option.
After a ticket (seat) has been ordered, the Ticket_order=>Tickets table will be updated as;
13 AC1 20 10 11 Sanuja Senanayake (491) 111-1111 Calgary 0007 Sam 0703041220pm Sena | 7 | 8 | 9 | 10 | 11 | 13 | 14 | 15 | 16 |17 | 19 | 20 | 21 | 22 | 23
Where, the | 7 | 8 | 9 | 10 | 11 | 13 | 14 | 15 | 16 |17 | 19 | 20 | 21 | 22 | 23 indicates one of the tickets of a 15 ticket set, which the user has ordered, and displays all associated ticket record numbers. This information is displayed under ORDER_COMMENTS
By agent
An agent updates the database records when a ticket has been sold and payment is made. If there are any issues, the agent can update the relevant ticket by adding comments.
TO BE UPDATED…EXAMPLE ORDER PROCESS IMAGE
Ordering Tickets Example
The following user (person) has ordered three (3) tickets as a set. Once the order is placed, the database will be updated as follows. If an agent sold the ordered ticket, then the TICKET_ISORDERED value will be set to 2.
Application Generation Subsystem
The application generation subsystem consists of menu, data entry screen, reports and the application software.
The application software has the following characteristics;
– Programing language: TCP/IP, html, php (ver 5.1)
– Database: MySQL and flat (txt)
– OS: Linux Enterprise
Flow Charts of the DBMS
TO BE UPDATED…. ADD THREE FLOW CHARTS
Menus
a. Agents main menu
b. Agents ticket confirm
Data Administration
Backup and disaster recovery (DRS) has two (2) main types.
1. System Backup
The main system of the server includes OS and application scripts. Backup will be done only once since they are static. Backup will be taken immediately after an OS or script update.
2. Dynamic Data Backup
Dynamic data such as database and frequently modified data will be backed up under 3 categories.
a. Online: if a ticket has been ordered, then a backup copy (encrypt version) is sent to the ticket master with all user details including encrypted credit card information (if site based online payment is implemented). In an event of a database corruption or non-recoverable failure, ticket master can retrieve the data using this email.
b. Daily: server administrator with an admin account will manually backup the database.
c. Remote backup weekly: weekly backup can be setup as an automated online based backup through a script or by manually. The information will be kept at a remote location and this backup will be used to restore database in an event of physical damage to the server such as fire, flood, etc.
Conclusion
This DBMS is not capable of recording cancelation of tickets individually by a user. The agent or the ticket master can put a note to allow other to resell the ticket. Cancelled tickets can be sold personally via other methods of communications such as by email or by phone between interested parties.
This is for a non-profit organization and therefore no SSL “high secure” system for online transaction payment is implemented. Cash or checks were manually collected by agents in advance or at the gate and cross referenced to the DBMS confirmation number.
These drawbacks can be overcome either by using third party support such as PayPal or by improving the current DBMS itself.
Acknowledgement
The primary server developer involved in this project would not like to have his name published. The project was conducted between March and April of 2007 at the request of the client, the Sri Lankan Buddhist Society, Calgary (NGO).