Database System Design

The assignment content provides a relational schema design for an event ticketing system, including entities such as Event, Venue, Artist, and Customer, with attributes like event date, venue name, artist name, and ticket price. The ER-diagram shows primary keys, foreign keys, and relationships between the entities. Queries are provided to retrieve specific information, such as tickets for a particular event or venues that host concerts. Sample output is also shown. Improvements can be made by adding security features, storing detailed customer and event information, and using more secure databases.

Introduction to Systems Analysis and Information Systems
REPORT
AUGUST 15, 2016
STUDENT

Contents
INTRODUCTION.................................................................................................................................2
CONTEXT DIAGRAM..........................................................................................................................2
DFD LEVEL 0......................................................................................................................................2
ATTRIBUTES AND ENTITIES...............................................................................................................2
NORMALIZATION..............................................................................................................................2
ER DIAGRAM.....................................................................................................................................2
RELATIONAL SCHEMA.......................................................................................................................2
IMPLEMENTATION............................................................................................................................2
1. TABLE STRUCTURE IN DESIGN VIEW.....................................................................................2
2. RELATIONSHIPS.....................................................................................................................2
3. QUERY IN DESIGN VIEW........................................................................................................2
4. SAMPLE DATA IN FORMS AND REPORTS...............................................................................2
5. SAMPLES OF OUTPUT FROM THE SYSTEM (TICKETS, QUERIES AND REPORTS).....................2
IMPROVEMENTS...............................................................................................................................2
CONCLUSION....................................................................................................................................2
REFERENCES......................................................................................................................................2
1 | P a g e

INTRODUCTION
The report is about an event management system that will be designed to hold the information
of:
A. Venues
B. Artists
C. Ticket bookings
D. Events
As given in the requirements that the events or performances will include different types of
events and artist. Typical types of event are concerts, variety shows, comedy shows etc. For
a concert, the artist will be the band name; for a comedy show the comedian/comedienne;
for a variety show the leading name will appear. Thus after analysis of the given
requirements, there came up certain hidden requirements or we can say non-functional
requirements and functional as well. So apart from the stated requirements, the information
related to other aspects like address, date, who will perform where, who booked which ticket
and other general information will also be stored in the database. Thus the document below
contains visual designs and database references that have been designed to meet the user
requirements and build it into an actual product. Along with this some enhancements have
also been suggested for future expansion and improvements because with the time, the size
and complexity of the database will also increase. So to keep the system updated with the
technological changes and changing user requirements, the improvements should always
take place.
2 | P a g e

CONTEXT DIAGRAM
This is the context diagram of the system that has been designed to give an overview that how
the system will look and communicate.
DFD LEVEL 0
The diagram given below is an enhancement of the context diagram and is called DFD level 0
diagram. It contains a more detailed view of the system and shows or visualizes what and how
many data stores and other information will be seen in the actual system.
3 | P a g e

ATTRIBUTES AND ENTITIES
The attributes and entities that have been assumed on the basis of the requirements are given in
the table below.
Entities Attributes
Event Event_ID
Event_type
Event_date
Event_venue
Artist_name
Ticket_price
4 | P a g e

Customer Customer_name
Address
Phone
Booking_ID
Ticket_no
Venue Venue_name
V_address
V_phone
Event_name
NORMALIZATION
As we can see above that the attributes identified above have a lot of redundancy. Moreover
none of the records can be identified uniquely. Thus this will create redundancy in the database
and will make it erroneous and complex to use. Moreover there will be less:
a. Security
b. Reliability
c. Correctness
d. Readability
e. Data mining for future business aspects.
Thus we need to normalize the database and break it down into smaller parts there by joining the
tables by creating primary keys and foreign keys, so that the database becomes consistent and
non-redundant. So the tables or entities developed after 3N are as follows:
5 | P a g e

Entities Attributes
Event Event_id
Event_type_id
Event_date
Venue_id
Event_type Event_type_id
Type_name
Venue Venue_id
Venue_name
V_address
V_phone
Artist Artist_id
Artist_name
Address
Phone
email
Artist_event Artist_id
Event_id
Customer Ticket_id
Cust_name
Phone
Email
6 | P a g e

Event_ticket Ticket_id
Event_id
Ticket_price
Seat_no
ER DIAGRAM
The ER-Diagram for the above designed entities contains primary keys. Foreign keys and relations
and is as follows:
RELATIONAL SCHEMA
7 | P a g e

The relational schema for the above designed ER-diagram is as follows:
Entities Attributes Keys
Event Event_id PRIMARY KEY
Event_type_id FOREIGN KEY REFERENCES
EVENT_TYPE(EVENT_TYPE_ID)
Event_date
Venue_id FOREIGN KEY REFERENCES
VENUE(VENUE_ID)
Event_type Event_type_id PRIMARY KEY
Type_name
Venue Venue_id PRIMARY KEY
Venue_name
V_address
V_phone
Artist Artist_id PRIMARY KEY
Artist_name
Address
Phone
email
Artist_event Artist_id PRIMARY KEY , FOREIGN KEY
REFERENCES
8 | P a g e

ARTIST(ARTIST_ID)
Event_id PRIMARY KEY , FOREIGN KEY
REFERENCES
EVENT(EVENT_ID)
Customer Ticket_id PRIMARY KEY, FOREIGN KEY
REFERENCES
EVENT_TICKET(TICKET_ID)
Cust_name
Phone
Email
Event_ticket Ticket_id PRIMARY KEY
Event_id FOREIGN KEY REFERENCES
EVENT(EVENT_ID)
Ticket_price
Seat_no
9 | P a g e

IMPLEMENTATION
1. TABLE STRUCTURE IN DESIGN VIEW
10 | P a g e

11 | P a g e

12 | P a g e

2. RELATIONSHIPS
13 | P a g e

3. QUERY IN DESIGN VIEW
A. TICKET QUERY: SELECT Venue.VENUE_NAME AS ["VENUE"], Venue.V_ADDRESS AS
ADDRESS, Venue.V_PHONE AS CONTACT, Event_Type.TYPE_NAME AS ["EVENT"],
Artist.ARTIST_NAME AS ["PERFORMING ARTIST"], Event.EVENT_DATE AS ["DATE"],
Event_Ticket.TICKET_ID AS ["TICKET NUMBER"], Event_Ticket.SEAT_NO AS ["SEAT
NUMBER"], Event_Ticket.TICKET_PRICE AS ["COST"]
FROM Venue INNER JOIN (((Event_Type INNER JOIN Event ON Event_Type.Event_type_Id
= Event.Event_type_id) INNER JOIN (Artist INNER JOIN Artist_Event ON Artist.Artist_ID =
Artist_Event.Artist_id) ON Event.Event_id = Artist_Event.Event_id) INNER JOIN
Event_Ticket ON Event.Event_id = Event_Ticket.Event_Id) ON Venue.Venue_id =
Event.Venue_id
WHERE (((Event_Ticket.TICKET_ID)='T002') AND ((Event.EVENT_ID)='E2'));
B. QUERY FOR PARTICLAR EVENT TYPE VENUES
SELECT VENUE.*
FROM Venue INNER JOIN (Event_Type INNER JOIN Event ON Event_Type.Event_type_Id =
Event.Event_type_id) ON Venue.Venue_id = Event.Venue_id WHERE
Event_Type.TYPE_NAME = 'CONCERT';
C. REPORT THAT LISTS THE NUMBER OF BOOKINGS FOR EACH EVENT AT EACH VENUE
SELECT Venue.VENUE_NAME AS ["VENUE"], EVENT_TYPE.TYPE_NAME AS "EVENT" ,
COUNT(EVENT_TICKET.TICKET_ID) AS "NO OF BOOKINGS"
FROM Event_Type INNER JOIN (Venue INNER JOIN (Event INNER JOIN Event_Ticket ON
Event.Event_id = Event_Ticket.Event_Id) ON Venue.Venue_id = Event.Venue_id) ON
Event_Type.Event_type_Id = Event.Event_type_id GROUP BY EVENT_TYPE.TYPE_NAME,
Venue.VENUE_NAME ;
4. SAMPLE DATA IN FORMS AND REPORTS
14 | P a g e

15 | P a g e

16 | P a g e

5. SAMPLES OF OUTPUT FROM THE SYSTEM (TICKETS, QUERIES
AND REPORTS).
TICKET LAYOUT OUTPUT
CONCERT VENUE QUERY OUTPUT
17 | P a g e

REPORT OUTPUT THAT LISTS THE NUMBER OF BOOKINGS FOR EACH EVENT AT EACH VENUE
IMPROVEMENTS
The improvements that can be incorporated into the system are:
1. Adding security features like authorized read, write and edit.
2. Tables to store detailed information about regular customers
3. Tables to store past and upcoming events separately.
18 | P a g e