Introduction

 KTK Consultants is pleased to present the new redesign of the Transport for Wales (TfW) ticketing system database that aligns with the Welsh Transport Strategy for 2022.

 In an ever-increasing digital age, where customers are used to convenience and quick turnarounds, TfW aims to improve how passengers purchase tickets across all their various transport offerings. TfW's primary goal is to provide a ticketing system that allows passengers to conveniently purchase tickets from retail outlets, TfW stations, and digital platforms. The system will be supported through a real-time geolocating system that tracks bus and train arrival times, busy routes, travel patterns and passenger habits (Finzgar & Trebar, 2011). The goal is to use this data to inform future transport planning and resource allocation across Wales.

 A robust dynamic database management system needs to be in place for this ticketing system to run effectively and efficiently. The database management system needs to consider user requirements, current security policies, rate of growth, and legal and compliance considerations related to data protection. During the system definition stage, in which the scope and boundary of the database system were defined, KTK consultants identified the significant users and their requirements that are to be supported within the system. With the use of published annual reports and interviews of the CEO and senior executives, it was established that key decision makers wanted to understand their passenger habits and travel patterns to inform on how to improve the transport service offerings continuously.

Summary Findings

 TThe key users of the ticketing system were identified as the transport planner responsible for logistics, route management and overall oversight of the company's transport organisation. The customer service advisor is responsible for assisting customers in purchasing tickets, ensuring the safe dispatch of trains, and advising passengers on delays. Finally, the passengers are key users who want to purchase tickets, plan their journeys, and check the availability of trains and buses in their area.

System Requirements

 Transport for Wales has 248 stations located nationally and a total of 481 staff members, according to their 2020/2021 annual report. Five million passenger journeys were completed during the 2020/2021 financial year (Transport for Wales, 2021); this has increased to 17,6 million journeys completed between 2021-2022, an approximate average of 48,000 journeys a day. Regarding ticket-purchasing facilities, TfW has 42 booking stations, eight rail agents, seven at stations and one at a tourist centre, 234 ticket vending machines, and a website and mobile application accessible to anyone with a smartphone (Transport for Wales, 2021).

Performance

 The ticketing system should be operational 24 hours a day, seven days a week. Upon purchasing a ticket, there should be at least a 1-second response at the ticket vending machines and, on the website, and mobile application. Real-time information should be provided on trains and buses' arrival and departure times. Real-time data on the capacity of trains and buses should be accessible to passengers and customer service advisors. The mobile application and website should have a 99.9% uptime and be accessible to multiple users at any time.

 Regarding security, the database should be password-protected with optional multi-factor authentication option through an opt-in, and access privileges should be appropriate to the user view (Connolly, 2014). The passenger should only be able to see information regarding tickets they wish to purchase and past information on tickets they have purchased.

 KTK proposed using data lakes to store real-time data in the initial database proposal. This is crucial to the ticketing system as real-time data informs on key performance indicators such as arrival times, departure times, and possible delays. Historical data stored in the data warehouse will be used to inform on trends which will be essential in future transport planning initiatives. To better track traveller journeys, and the trends of travellers, having relationships between the customer, ticket purchases, route, and mode of transport entities would derive this data. Therefore, this can be explained as follows, one customer purchases one ticket for a route on one mode of transport. Over time this data can build traveller trends and patterns of those using Transport for Wales.

 However, on closer inspection of the database design model presented in the initial design report, some areas were not fully presented. Following the findings, other entities would need to be included in the ticketing system to function in a manner that meets the organisational needs. A transaction details entity could be included that captures details of the transactions made. A timetable entity that is connected to the tickets purchased entity and route entity. As mentioned earlier, the emphasis on ensuring that customers are given optimum experience depends on their ability to plan their journeys, and being informed on travel times is crucial to the overall transport experience. Equally significant is establishing a client card entity which will be used to store the details of a passenger and track their usage trends and patterns. In this manner, TfW will be able to categorise passenger trends by demographics and geographical locations and use this information to develop tailor-made transport solutions and service offerings.

 Though, there is a ticket-purchased entity, there was also a need to include a ticket-type entity. TfW offers a variety of ticket types for every type of traveller. Therefore, with each transaction, the passenger purchases a ticket type, and this ticket type is linked to a route and timetable.

 Regarding implementing practices that improve special needs people's experiences, a Needs entity can be implemented that captures the mobility limitations of travellers. For those requiring assistance during travel, the information is captured when making a ticket purchase. This entity serves as an aid for people with mobility issues and allows TfW to plan accordingly to accommodate passengers who need assistance.

Entity Relationship Diagram

 Following the findings, the entity relationship diagram will incorporate the entities that were mentioned. Below is a redesigned entity-relationship diagram for the ticketing system.

Data Protection

 Information and database systems require the business and technical implementation of data protection principles. This applies directly to the TfW ticketing system due to the heavy presence of personal data, such as customers' biodata, financial (e.g. transactions) and geolocation data. The data protection requirements are outlined in the UK's Data Protection Act 2018, also known as UK GDPR, which transposes into national law the General Data Protection Regulation of the European Union. Considering that EU Regulations set out the framework and member states have the right to be more restrictive while transposing them into national law, UK GDPR is based on seven fundamental principles, in contrast to EU GDPR, which is based on only five.

 The principles of UK GDPR are "Lawfulness, fairness and transparency", "Purpose limitation", "Data minimisation", "Accuracy", "Storage limitation", "Integrity and confidentiality (security)", and "Accountability" (Information Commissioner's Office, n.d). In addition to that, additional provisions are added for governmental services. Even though the transport network is not entirely state-owned, the TfW, whose primary responsibility is to oversee the transportation network and contractors, is a governmental service owned by the Ministers of Wales. Therefore, such provisions directly apply (Transport for Wales, n.d.).

 As a general guideline, the GDPR implementation and all necessary compliance need to be overseen by an accredited Data Protection Officer (DPO). The role of a DPO is to ensure compliance with data protection rules and regulations (regardless of the type of data, staff data, customer data, supplier data, or else) (European Data Protection Supervisor, n.d).

 In addition, considering the general system design, the principles of "Lawfulness, fairness and transparency" (lawful, fair and transparent data processing), "Purpose limitation" (data processing for explicit and well-defined purposes), and "Data minimisation" (data collection and data processing only for the data absolutely necessary for the organisation’s functions) are ensured due to the limited data collected and processed, which are aligned with the company's mandate.

 Moreover, due to the front-end application, users also have complete control over their data (including their right to be forgotten), as they can update or delete unnecessary data. Account deletion takes place by removing all personal data and anonymising the remaining identification parameters (e.g., transactions) for auditing reasons. TfW should apply the GDPR principles while balancing the audit needs as a governmental body.

 The "Storage Limitation" refers to storing data no longer than absolutely necessary. For this reason, dormant accounts will be deleted and anonymised, ensuring maximum data protection for the data subjects, even if they do not have or do not want to have access to their account anymore.

 Finally, the security aspects are handled in the front end, by applying a strong password policy, utilising off the self well tested features (e.g., android biometrics) and highly suggesting the use of two-factor-authentication (one-time password). On the back-end, the use of MS SQL server transfers that security responsibility to Microsoft, due to expertise and specialised personnel of database security aspects.

Recommendations

 For transport for Wales to meet its strategic objectives as laid out in the Llwbr Newydd – the Wales Transport Strategy 2021 of an integrated transport system supported by a dynamic ticketing system, the following need to be considered. The ticketing system is available on multiple purchase channels, mainly mobile applications, websites, and ticketing vending machines located at TfW stations and selected retail outlets that sell tickets. All these ticketing purchasing channels need to operate smoothly to avoid lag and delays. The thousands of transactions that occur daily or hourly need to be quickly accessed and analysed and for this, a Data Lake is recommended. This will allow for a combination of structured and unstructured data to be accessed. However, a data warehouse is still recommended for the storage of processed data that will be used by users such as the transport planner for further analysis.

 Transport for Wales must remain cognisant of the GDPR directives and ensure that data protection rules are upheld. No unauthorised access to data is allowed and strong security policies are to be put in place to protect sensitive information from hackers and prying eyes.

 A strong data culture should be built within the TFW, this means that priority is given to data and analytics as the driving force for insights and innovation, which ultimately leads to better decision-making. In addition, data literacy needs to be built within the organisation. Those with access to data should be able to ask the right questions, validate assumptions and make decisions ethically and efficiently (Southekal, 2022).

 Lastly TFW should invest in quality data management software that will help them derive the insights that they need from the data.

Conclusion

 To sum up, KTK Consultants aim to develop a ticketing system for transport of Wales which will be customised to meet TfW's requirements, aiming to raise the quality of the traveller's experience. The contents of the summary have been developed after carefully considering the latest project management practices and utilising well-established technologies while considering data protection considerations based on the Data Protection Act pillars through legal, compliance (e.g., DPO) and technical (e.g., two-factor authentication) interventions. The use cases used have been customer-centric, aiming to provide a satisfying user experience (UX) while at the same time respecting the traveller's time through the provision of feedback for the latest travel conditions (delays, occupancy, timetable), fully respecting their privacy. The updated logical design addresses auditing concerns (e.g., transactions) and touches upon data storage, a subject that overlaps both with data protection and technical implementation, to provide valuable business insights. Overall, the system proposed by KTK consultants will bring a change of era on how the ticketing system of TfW was working so far, while providing a smooth transition, and provide the necessary data for data-driven decisions of the executives of TfW.