Executive summary

The use of database is much popular due to the use of information systems. Today various business ventures are using the information systems to ease their day to day business functions. Information systems are giving the highest level of functionality to an organisation and also they have the capability of automating the business process. In the information system the database commonly known as the centralised database is playing a major role. The data base should be able to capture, store, keep, retrieve and manipulate the data according the demands of the system users.

During the report it would assess how the database design would apply to a real life business scenario to automate the business process. The given case study is about the ticket booking system which is used by a theatre. The theatre is playing various dramas and events with the expectations of improvement of public entertaining. The report would contain the design diagrams which are developed according to proposed database design solution. In the given case the database would capture the information of the consumer, information of the events and drama, and consumer reservations. The SQL commands for the database and table creation would also be expressed during the report as well.

1. Introduction

The report would discuss the database design solution which was given to Raleigh Theatre. The information system should be able to handle the functions related to the ticket bookings of the theatre. The consumer would book the tickets using four major contact mediums, online, telephone, in person and by post. The database should contain the information and data about the entertaining events of the theatre.

Also the consumers can become the member with the theatre. To become a member they have to pay an annual subscription fee and being a member they would get special discounts for the tickets. Not only that the consumer has the opportunity of giving the volunteer services to the theatre. Since Raleigh Theatre does not have high number of employees they always look for the volunteers. Being a volunteer the individual should assist the theatre by providing necessary services to the consumers by issuing tickets, ushering the consumer, etc. In return the volunteers get free ticket for any event of the theatre when they have volunteered for three events of the theatre. The theatre has seating based pricing method for the tickets. The prices for the tickets would be differ from event to event as well. Below it was stated the objectives of this report.

  • Analyse the scenario and identify the client’s requirements.
  • Design the solution for the identified requirements. The design solution would only be limited to the database, because the report is only focused on the database solution to the situation.
  • Identify the assumptions and the drawbacks of the proposed solution.
  • Identify the relationships of the database and propose generalisation options.
  • Implement the database solution to the system.

During the report it would be discussed various database modelling methodologies and logics to achieve the above mentioned objectives. Also in the implementation phase would discuss the CREATE TABLE functions for the proposed design solutions. The database creation would be done using the ORACLE database and the database creation methods would be discussed using SQL database language.

2. Class diagrams for the database

2.1 Class diagram for the consumers, members and volunteers

Above diagram depicts the class diagram which is relevant to the customer, member and volunteer. Mainly the customer comes to the theatre and purchase the tickets. Afterwards the customer would become a member or a volunteer to the organisation. So the first the organisation should collect the information of the consumer and then the organisation can ask whether they can become a member or a volunteer or both to the organisation. In the Customer table the Customer_id would play the role of primary key and this would become foreign keys to the Members and Volunteer tables. To the Customer and Volunteer tables, Membership_id would be a foreign key from the Members table.

2.2 Class diagram for the events

The event information would be stored in the events data table. For the each event, Event_id table would be created with the relevant Event_id. As an example if the assigned Event_id was Event_17001, the table would be created under the table name of Event_17001. That table would store the tickets which were issued to the different customers. The Customer_id would be a foreign key to this table. Since there are individual tables for each event it would be easier to calculate the number of tickets which were issued to the event under the categories of a, b, c, and d. The table would not hold a foreign key from the Events table, but the table can be found using the Event_id variable of the Events table.

2.3 Class diagram for the whole database

The above diagram depicts all the classes of the database. Each table of the database has the connection to at least one table by allowing the primary key to become the foreign key to the other connected table. Each described table has a unique id which can be referred to select a unique raw when retrieving the data. It was assumed that the id generation should be done through the system and the system developer can use combination of various characters or simple incrementing numbers for the unique ids.

3. The relational data model

To avoid the complexity of the diagram it was decided to avoid mentioning the attributes of the each entity. The attributes of the each entity has described in the class diagrams of the previous chapter. As it was discussed in the earlier chapter the starting point of the system would focus on the customer buying the ticket to an event. The one customer can participate to one event at a time but one event would have one or more customers. That one to one or more relationship was depicted in the above diagram. The Customer_id would be the primary key to Customer table and Ticket_id would be the primary key to Event_id table. Actually as it was discussed in the previous chapter the Event_id table would be created using the value of the Event_id mentioned in the Events table. In the Event_id table the Customer_id would be considered as the foreign key.

The customer would become the member or the volunteer to the organisation. Because of this the relationship among the each table to other one would be one to one relationship. Customer_id, Membership_id and Volunteer_id would be the primary keys for the each tables consecutively. Customer_id would be a foreign key to both Member and Volunteer tables. Also the Customer table and Volunteer table would have the Membership_id as a foreign key from the Member table. As it was discussed in the class diagrams of the previous chapter it was cleared that the customer, member and volunteer have some repetitive information such as name, telephone number, address and birthday. To generalise the tables and to avoid unnecessary data repetitive pressure to the database it was decided to add all those details into a one table. That table would be the Customer table and since there are referral foreign keys of Customer_id those details can be easily found through that.

Also the management has decided to give the discounted rates for the senior citizens. Because of that the birth day is required from the customers. The customer table is capturing the birthday it would be easier to develop the software logic for the application using the customer birthday to check the suitability of the senior citizen discount.

The Event_id table would have the one to one relationship from Events. Under the Events table there would be one Event_id table and because of that the relationship is one to one. One event would have several volunteers and also several volunteers would be liable to get free tickets from the events. Because of this, Events and Event_id would have one to many relationship with Event_volunteers. Both Event_volunteer and Events have the same primary key of Event_id. Using this value of Event_id the Event_id table would be figured out by the system as it was discussed in the earlier chapter. The Volunteer table has the many to many relationship with Event_volunteer. Because the same volunteer person would have the capability of serving several events and also the event has the option of getting services from many volunteers.

4. Implementation

During this chapter it would discuss the implementation part of the database table. Only the CREATE TABLE commands would be mentioned due to the academic requirements of the assignment.

Customer table

CREATE TABLE Customer

( Customer_id varchar(10) not null,

Customer_name varchar(50) not null,

Address varchar(75),

Telephone_number numeric(10) not null,

Birthday date(10) not null,

Membership_status char(1) default 0,

Membership_id char(10),

CONSTRAINT PRIMARY KEY (Customer_id)

);

Member table

CREATE TABLE Member

( Member_id varchar(10) not null,

NIC varchar(10) not null,

Customer_id varchar(10) not null,

CONSTRAINT

PRIMARY KEY (Member_id),

FOREIGN KEY (Customer_id) REFERENCES Customer(Customer_id)

);

Volunteer table

CREATE TABLE Volunteer

( Volunteer_id varchar(10) not null,

Membership_status char(1) default 0,

Membership_id char(10),

Customer_id varchar(10) not null,

Availability varchar(30) not null,

Tasks varchar(30) not null,

CONSTRAINT

PRIMARY KEY (Volunteer_id),

FOREIGN KEY (Customer_id) REFERENCES Customer(Customer_id)

);

Event_id table

CREATE TABLE Event_id

(Ticket_id varchar(10) not null,

Customer_id varchar(10) not null,

Ticket_nos_a numeric (3) default 0,

Ticket_nos_b numeric (3) default 0,

Ticket_nos_c numeric (3) default 0,

Ticket_nos_d numeric (3) default 0,

CONSTRAINT

PRIMARY KEY (Ticket_id),

FOREIGN KEY (Customer_id) REFERENCES Customer(Customer_id)

);

Event_volunteer table

CREATE TABLE Event_volunteer

(Event_id varchar(10) not null,

Volunteer_id varchar(10) not null,

Served_days numeric (3) default 0,

CONSTRAINT

PRIMARY KEY (Event_id),

FOREIGN KEY (Volunteer_id) REFERENCES Volunteer(Volunteer_id)

);

Events table

CREATE TABLE Events

(Event_id varchar(10) not null,

Event_name varchar(40) not null,

Event_type varchar(20) not null,

Event_date date(10) not null,

Event_time_morning time(10) not null,

Event_time_noon time(10) not null,

Event_time_evening time(10) not null,

Event_time_night time(10) not null,

Ticket_price_a numeric (3) default 0,

Ticket_price_b numeric (3) default 0,

Ticket_price_c numeric (3) default 0,

Ticket_price_d numeric (3) default 0,

CONSTRAINT

PRIMARY KEY (Event_id)

);

5. Conclusion

During the report the writer get the opportunity to apply the theoretical and practical knowledge of the database designing and the development. The information systems are playing vital role in both public and corporate sector and the main component of the information system is its database. Because of that when handling the information systems the database management capabilities are essential and also the good database designing skills would give much smother process capability to the system.

The given scenario required to develop a database solution to a theatre. During the designing it was needed to consider the various requirements of the given case. Each and every requirement should be addressed in the designing and some of the requirements should be addressed through the system coding functions. The system development and coding should be a collaborative function and there should be a strong relationship among those main activities when addressing the requirements and developing the information system.

During the report it was assess the classes of the case and also the relational database model was proposed with the relations to the stated class diagrams. The data repetition was avoided as much as possible in the relational modelling and also the CREATE TABLE functions were tested successfully in the implementation stage.