The Pierian Games are a four-yearly sports competition open to all the countries of the world.
The games comprise a number of events. The events are held at specific locations, dates and
times determined by the games organisers. Spectators are issued tickets to attend events.
A spectator can have only one ticket for any given event and they must have been issued a ticket
before travelling to the event. Occasionally, events are moved to a different location, date or
time, in which case the event is cancelled and all the tickets for the event are cancelled.
Spectators may then be issued new tickets for the re-arranged event but this is outside the
scope of this exercise. Details of events, spectators, valid tickets and cancelled tickets are
recorded in a database. The database is used to provide up-to-date information to spectators
and to the organisers of the games. This information is accessed over the Internet and by
using dedicated workstations.
Data to be held in the database and queries concerning tickets come from various workstations
or active web pages. The IT team for the Pierian Games refer to these various inserts, deletes
and queries as tasks. Their central database is held on a server computer that processes the
tasks. Tasks are held in an input queue and processed by an application program on the server.
The results of processing the tasks are placed in an output queue for despatch to workstations
or web pages.
For this coursework exercise you are required to set up a database for the Pierian Games, write
SQL statements for the tasks and develop a Python application program for running the tasks
through the Python psycopg2 interface. Naturally, this exercise is greatly simplified
compared to a real application. A detailed specification of the work to be undertaken and the
deliverables to be produced for assessment is given below.
You will set up your ‘Pierian Games database’ as a set of tables. You are required to write the
server application program and not the workstation software or the web pages. For this
exercise you will simulate the queue of input tasks by reading a stream of data from a text
file. Similarly, the results of the tasks will be sent to a text file that simulates the output queue.
Thus, no GUI user interface programming is required.
You may, of course, use your own facilities to develop your program but the final version
must use PostgreSQL
event (ecode, edesc, elocation, edate, etime, emax)
spectator (sno, sname, semail)
ticket (tno, ecode, sno)
cancel (tno, ecode, sno, cdate, cuser)
Notes:
The event table holds details of each event which is scheduled for the games.
The spectator table holds details of spectators who have been issued tickets.
The ticket table holds details of tickets issued to spectators for events.
The cancel table is used to record details of all tickets that have been cancelled.
ecode is a four-character code identifying an event, e.g. B100 for the athletics event run over
100 metres.
edesc is a description of the event.
elocation is the place where the event is held.
edate is the date on which the event is held. Each event takes place on a single day. All
events are scheduled for the month of December 2021.
etime is the start time of the event. No events start before 09:00 hours.
emax is the maximum number of tickets that can be sold for an event. Ranges from 1 to 1000.
sno is a numeric identifier given to a spectator.
sname is the name of a spectator.
semail is the email address of a spectator.
tno is a reference number for a ticket issued to a spectator for an event. The reference
numbers start at 1 and are incremented by 1 each time a new ticket is sold. You should
increment the tno manually rather than use an automatic number generator as that would make
testing more difficult.
cdate is a timestamp showing when the cancellation of a ticket takes place.
cuser is the user id of the person responsible for a cancellation of a ticket being recorded in
the cancel table.
A. Insert a new spectator.
B. Insert a new event.
C. Delete a spectator. To be deleted, the spectator must not have any valid (i.e. not
cancelled) tickets.
D. Delete an event. All the tickets for the event must be cancelled before an event
can be deleted.
E. Issue a ticket for an event. A spectator may have only one ticket for a given
event.
P. Produce a visual table showing the total number of spectators liable to travel to a
location1. The table should show the total number of spectators that could travel to a
location on each date an event is held at a location.
Q. Produce a visual table showing the total number of tickets issued for each event.
Present the data in order of event description.
R. As Q above but only for a given event which is specified by the event code.
S. Produce a table showing the itinerary for a given spectator. The spectator is
specified by their spectator number. The itinerary should contain the spectator’s
name and the date, location, time and event description of each event for which the
spectator has been issued a ticket.
T. Given a specific ticket reference number, display the name of the spectator and
the event code for the ticket and indicate if the ticket is valid or is cancelled.
V. View the details of all cancelled tickets for a specific event.
X. A task code sent to close down the server application program.
Z. A task code sent to the server application program to empty the database tables
prior to use with live data.
2.3 Note that, for each task, the program should send a reply to the output queue (a text file).
For example, tasks A to E, X and Z should, if successful, send a simple message showing
the task type (A, B, …) and confirming that it has been completed. For example:
‘X. Pierian Games application closing down’.
The output ‘tables’ from tasks P, Q, R S and T are sent to the output queue and comprise the
task type followed by the relevant data in a simple tabular format. You are not expected to
implement report headers, report footers, page breaks, control breaks, fancy fonts, colour,
logos and so on, as might be found in a ‘real’ output.
A task that contains errors or would cause errors should send its task type and a user-
friendly error message to the output queue.
Prepare and test an SQL script to create your copy of the database. This should take, as a
starting point, the table definitions given in Appendix A2. Prepare additional SQL clauses
and/or statements to complete the definition of the database by specifying primary keys,
domain constraints, entity and referential integrity constraints, etc. Note that you should
NOT modify the name and type of the attributes (i.e. the information you have been
given). Save all your Data Definition Language (DDL) statements in a text file.
Load a reasonable volume of test data (e.g. 5 – 10 rows) into the tables for use in your
testing. The test data should be sufficient to test all of the queries with their expected output
and should provide a suitable environment in which to test normal operation as well as
abnormal conditions.
• Document this stage with a copy of your complete DDL statements in SQL
(including any table definitions, views, triggers, comments, etc.)
• Also produce a copy of the test data you loaded for testing in a text file.
Prepare and test interactive SQL statements for the various types of tasks defined in section
2.2 above. Test these statements using the SQL editor in PostgreSQL.
The purpose is to test your SQL statements before using them in your Python program. You
may need more than one execution of some of the task types to demonstrate the correctness
of your work. Your .sql files need to be ready to be loaded during the demo so if you fail to
demonstrate your Python program working you can at least demonstrate your prototype
SQL statements through the interactive interface.
• Document this stage with a copy of the SQL statements. Your SQL statements need
to be accessible as text files
• Evidence of testing of each SQL statement (e.g. copy of the output from running the
query).
Use suitable versions of your SQL statements produced for section 3.2 within a Python
application program using the Python psycopg2 interface. The program should read a queue
of task details from a text file and send the results and/or error messages to the output queue
(another text file). The precise input format of tasks is described in appendix A1 below.
Your program does not need to check that the values of individual fields in tasks are of the
correct type and format. However, the program must make checks against the database to
ensure that task details are valid. For example, an event code field used in a task type E will
be a four-character alphanumeric string but there may be no such actual event stored in the
database. Where possible, the program should handle errors by reporting them and then
continue with the next task.
The deliverables for this part of the assignment are:
• A commented program source listing (your .py file/s) which demonstrate the use of
SQL in Python
• Copies of input and output files used to show that the program works as specified.
The objective is to demonstrate the good use of SQL and database features and not complex
Python coding. Source programs should be consistently laid out in a format which is easy
to read.