CEMS Bus Timetable Application
Background
A number of operators provide services which call at the UWE Frenchay site. In all First, South Gloucestershire Bus Services and UWE itself provide over a dozen different services. Timetables for these services is available at a number of places:
- posted at the Frenchay bus stop
- leaflets on First services available from a number of outlets
- the web
In addition, a departures board at the Frenchay bus stop
shows timetabled departures for First and
This CEMS service consolidates information from all three service providers into a single database. This database is used to provide a number of dynamic information sources.
The impetus for this project came from the author’s investigation of the information display board on the Frenchay campus and its origins. This investigation is described here.
Architecture
The system is implemented as a 3-tier internet based application using PHP and MySQL.
Data Model
Data modelling was done using QSEE. The QSEE project is in this zip file.
The ER model is a conceptual model. Foreign keys are omitted from the model since these are the result of the accommodation of one-many relationships to an RDBMS. QSEE handles this automatically when SQL is generated.
The notation here is ‘crows-foot’; the crowsfoot indicates the many side of the relationship, the circle indicates optionality. The bar at the many end indicates a dependent entity.
Database Definition
The SQL to define this database was generated by the QSEE case tool. The foreign key prefix is null, so that they have the same name as the primary key to facilitate the use of the NATURAL JOIN (See below
It also generates SQL to drop all the tables
Database Description
Words in bold are attribute(field names)
Service
A service is a main route such as the 70 route, run by one
of the service providers.
serviceid is the key identifier for a service. It is private to the system i.e. is not used in any interface with users. In the current data, I have chosen to use names derived from the service id for ease of creation, but this is not necessary.
servicename is the name of the service, usually the name on the front of the bus. There can be more than one service per service name, for example the 518 passes UWE in two directions – to Shirehampton and to Longwell Green . In this case there will be two services with the same servicename (518). servicename appears in display boards
dpriority sets a prioriy – 1 highest for appearance in the service board
Timetable
A timetable is a set of departure times on a service with a single destination, running on certain days of the week (defined by rundays), and valid for one or more time periods (defined by runperiods).
versionid –unique within serviceid . Distinguishes between timetables for a service. So 99 is represented by two timetables – Mon-Fri and weekend. As with serviceid, versionid is not used in the user interface, although I have chosen to use a meaningful name.
rundays A timetable is specific to certain days of the week - this item codes the days on which this route, with ‘y’ for days of the week (starting with Sunday) when this timetable applies.
RunPeriod
A timetable is valid for one or more periods defined by a start date and an end date. So 70 has 4 timetables –Term-time 70 Mon-Fri and 70 Weekend and UWE holidays Mon-Fri and Weekends. The periods define the ranges of dates when the version is active. If the end is open, the end date is 2999-12-31. When the whole timetable for a service is revised, the new timetables can be put up as new versions, which will come into effect on the dates defined by the run periods. In this case, the end dates of the previous service will need to be amended too.
Departure
Because this system covers only departures from Frenchay, there is just a single departure time for each departure.
ddestination : A minority of departures go to a different destination than the destination defined in the timetable table. Destination in Departure is normally Null but if set will override that defined in the timetable.
Thus the last 99 buses have a Departure destination of ‘Blackboy Hill’ since they do not go all the way to the Centre.
Technical Notes
1. Foreign key fields in the database been given the same name as the primary key. In the scripts, Select statements must join several tables to ‘denormalize’ the data. It is simple to write the join conditions if the names are the same because we can use the NATURAL JOIN form, in which tables are implicitly joined on all fields which have the same name in each table.
e.g to denormalise the departure data so we can see all data about a departure time, we can write
select * from service natural join timetable natural
join runperiod natural join departure;
The result is of course a very large table, but we would normally be selecting only some rows, for example those applicable to the required service, time, date etc.
2. There is an SQL function to handle overriding, as in the two destinations- one at timetable level, the other at departure level: – COALESE
Select COALESE
(departure.destination, timetable.destination) as dest,….
If departure.destination is not null, it will be the value of dest; if it is null, the value will be the next expression, timetable.destination.
Actually, we don’t want to
call the columns the same name, because it would then be included in the
natural JOIN with very bad effects – nothing would be selected! Hence the name ddestination for the departure
destination.
Loading the database
A timetable is a complex structure – the single timetable record together with a set of departure times and run periods. These are treated as a single unit for the purposes of update. A file format has been defined for a timetable, to which all imetable files mus conform.
A PHP script loads files of this format into the
database. This can be run from anywhere
on the web, enabling a service provider to update their own services. A
Files
service.txt – SQL statements to load the service table
loadtimetable script
execute the loadtimetable script
example timetable file – 518 service to Shirehampton on weekdays
Displaying the Departure information
The main purpose of the system is to display the future departures in a manner similar to the display board by the bus stop. However a web-based display can be viewed from anywhere on campus, and even on the faculty monitors. Two PHP scripts have been written to do display the departures in different ways:
To display the next 20 departures: departureboard
To show the next bus on each service : serviceboard
These web displays auto-refresh every minute.
Files
departureboard script
serviceboard script
screen
monitor
Technical notes
1. These scripts are parameterised so that one script can run in slightly different ways
The style parameter defines the name of the
The refresh parameter defines the refresh rate (see below)
The maxrows parameter defines the number of rows to display
These are passed to the script attached to the URL: A typical URL to execute the script would look like :
http:
host/dir/dir/departureboard.php?maxrows=20&style=screen&refresh=60
2. The ability to
auto-refresh is provided by the tag:
<
This causes the browser to re-fetch the page every minute. For use on the Faculty monitor, refresh is not specified.
SMS service
Another purpose is to provide on-demand departure times in response to a text message sent from a mobile phone. This is one of a number of SMS services included in the CEMS faculty 2-way SMS service. An SMS server receives the incoming message and routes it to the appropriate script depending on the initial word in the message. Thus a new application such as the bus application need only provide a script which accepts some standard inputs and forms a standard message in reply. The reply to the originating form is handled by the SMS server.
This architecture allows the service to be tested independently o the SMS service. To get next two buses for a given service (e.g. 70), we can execute the following request:
smsrequest.php?code=BUS&text=70
See CEMS SMS Server for a full description of this framework.
Files
smsrequest script
WAP
A WAP interface is provided. The user is presented with a screen to select he required service and the system then responds with a WML file showing the next 2 departure times for that service.
Files
waprequest.wml Initial WML file ( shown as XML)
waptimes script to return the service times
test waptimes
Tutorial worksheets
A number of suggested exercises in implementation, design and analysis are described in these tutorial worksheets:
Basic understanding of application and simple exercises
Extended design exercises
Investigating the impact of design changes
Questions about the use and design of travel information services
Work required
All too easy to forget to update these timetables, or let the valid period expire. Database should be more proactive and email the webmaster when a service is in danger of expiring.
Timetables should have a priority. This would enable us to attach dates to the SatSun timetables which would override the lower priority term-time and this in turn would override the out-of term timetables. At present these are expected to be non-overlapping and Public holidays are not handled at all.
WAP is broken
Last updated 16 Feb 2006 – Chris Wallace