Javier Raut
Back to Projects
schoolwork

BEMMRA: An Application for Bus and Employee Management for Maintenance and Route Assignment

An offline, centralized database system designed to manage HR, maintenance, and route scheduling for a large-scale bus company. Built with role-based access control to eliminate data redundancy across multiple base departments.

Technologies

.NETC#PostgreSQL
Source

BEMMRA: Revamping Transit Operations Through Centralized Data

The Vision: A Database Fundamentals Project

As a collaborative project for our Fundamentals of Database Systems course, we were tasked with identifying a complex data problem and engineering a viable solution. We chose to tackle the logistical hurdles of a large-scale bus transport network.

Disclaimer: While our system architecture uses Rural Transit Mindanao Inc. (RTMI) as its conceptual subject, the specific organizational data, fleet numbers, and route details populated within our database are simulated for academic purposes and do not accurately reflect the company's real-world operations.

Our goal was to design a system that replaces a theoretical decentralized, fragmented setup—where HR, Maintenance, and Operations rely on isolated computers—with a single source of truth to eliminate redundancy and data inconsistency.

The Application: Designing for Offline Efficiency

We built BEMMRA (Bus and Employee Management for Maintenance and Route Assignment) as an offline, locally installed desktop client tailored for specific bases of operations.

  • The Tech Stack: The application interface was developed using C# and the .NET Framework, providing a responsive, user-friendly desktop experience tailored for administrative staff.
  • The Database Engine: We utilized PostgreSQL as our database solution. By carefully designing the Conceptual, Logical, and Physical schemas, we shifted the workflow from scattered records to a strictly relational, normalized data structure.
  • Departmental Integration: The system unifies data across three major domains: Human Resources (managing drivers and conductors), Operations (route planning and bus deployment), and Maintenance (tracking fleet health and schedules).

Security: Role-Based Access Control

Because this system houses critical operational and employee data, strict authorization rules were a non-negotiable priority. We implemented Role-Based Access Control (RBAC) tied directly to database privileges:

  • Targeted Authority: The application features distinct access levels for the Administrator, HR Manager, Operations Manager, and Maintenance Manager.
  • Data Protection: Users can only modify data relevant to their specific role. For instance, the HR Manager can update employee profiles but cannot alter bus maintenance schedules, while Operations can view fleet availability but cannot edit core bus metadata.

The Infrastructure: Maintenance and Reliability

To ensure the longevity and reliability of the database in a production-like environment, we engineered a comprehensive Database Maintenance and Recovery Policy.

  • Routine Maintenance: The system includes protocols for weekly database error checking and index rebuilding to maintain quick data retrieval speeds.
  • Disaster Recovery: We designed a backup policy that mandates weekly localized backups and yearly cloud/external storage archiving, ensuring the fictional base of operations could recover quickly from complete system failures or data corruption.

The Result

This project serves as a comprehensive "Proof of Concept" for our foundational understanding of database systems. It demonstrates our ability to not only architect a normalized relational database from scratch but also to connect it to a functional, role-restricted frontend application that solves a practical business workflow problem.