Advance Database Project - CNSM Website

A collaborative MERN (MongoDB, Express.js, React, Node.js) web platform for the College of Natural Sciences and Mathematics designed to centralize departmental pages, announcements, and program information. Our database cohort co-built the project using Bootstrap for rapid UI assembly and GitHub for version control and issue tracking. Core functional areas included student attendance, profile management, intramurals scoring, and fines collection. The initiative progressed through requirements, schema design, and working prototypes, but was ultimately archived due to scope and time constraints within the semester.

Key processes across departments were fragmented—announcements posted in different channels, attendance tracked on paper or spreadsheets, and intramural results tallied per event with no single source of truth. Centralizing these workflows promised:

• One portal for announcements and program details.
• Consistent, queryable attendance logs per event/subject/organization.
• Transparent intramurals scoring with live, cumulative leaderboards.
• Streamlined fines assessment and payment tracking.

The project also served as a capstone-style exercise in collaborative software development using modern tooling and team workflows.

We scoped the initial datasets and collection methods:

• Directory Data: Departments, programs, courses, faculty/advisers (seeded via coordinator-provided spreadsheets and on-form submissions).
• Profiles: Student and staff records with role-based attributes (submitted via web forms; validated against registrar lists where available).
• Attendance Logs: Event/class sessions, participants, timestamps, and remarks (entered by coordinators; planned CSV import for bulk updates).
• Intramurals: Events, teams, scoring rules, and results by match/round.
• Fines: Infraction types, amounts, issuance date, status (unpaid/paid), and clearance history.

A lightweight data dictionary was drafted to standardize field names, types, and validation rules across forms and APIs.

We structured the backend around clear MongoDB collections with Mongoose schemas and REST endpoints on Express:

• User & Role: authentication credential, role (student, coordinator, admin), status.
• Profile: user reference, program/department, year level, contact info, and optional metadata for organizations.
• Department & Program: names, descriptions, coordinators, announcement relationships.
• Announcement: title, body, tags, publishedBy, publishedAt, visibility (dept-wide or college-wide).
• Attendance: session/event reference, participant userId, check-in/out timestamps, status, and remarks.
• IntramuralEvent & Score: divisions (e.g., Men/Women), teams, fixtures, scoring rules, and match results.
• Fine: profile reference, infraction type, amount, issuedBy, issuedAt, status (unpaid/paid), receipts.

API design followed resource-oriented routes (e.g., /api/announcements, /api/attendance) with role-based access checks at the controller layer and request validation via middleware.

While this was a web application (not an ML model), we planned and executed testing and evaluation in three layers:

• Unit & Integration Tests: Controller and service tests for CRUD and permission paths, focusing on attendance and fines logic.
• Manual QA & UAT: Coordinator walk-throughs of key flows (post announcement, record attendance, update scores, settle fines) against acceptance criteria.
• Performance Smoke Tests: Seeded sample data to validate list filtering, pagination, and scoreboard aggregation without timeouts.

Success metrics included low bug counts on critical paths, predictable API latency for list endpoints, and positive coordinator feedback during UAT.

The planned architecture separated concerns for flexibility and scale:

• Frontend: React + Bootstrap, built and served as static assets (e.g., Netlify/Vercel).
• API: Node/Express running on a managed host (e.g., Render/Heroku), connected to MongoDB Atlas.
• CI/CD: GitHub for collaboration with PR reviews, environment config via .env, and branch-based deploys.

Status: The team delivered working prototypes for the major modules, but the full deployment was deferred and the project archived due to timeline constraints and the breadth of features relative to semester deadlines. Next steps (post-archive) would be reducing scope to a minimal viable slice (Announcements + Attendance), then iterating.

The system handled sensitive student records, so we documented safeguards:

• Privacy & Consent: Limit personally identifiable information, obtain consent for attendance tracking where applicable, and provide opt-out/appeal paths for fines.
• Security: Role-based access control, hashed credentials, server-side validation, and least-privilege defaults for coordinators versus admins.
• Fairness & Transparency: Clear intramurals scoring rules, audit logs for fines issuance and payments, and traceable attendance edits with reason codes.
• Data Retention: Time-bounded retention for logs and the ability to export/delete a student's data upon request.