"A charter is the founding document of any official organization." Charter is the platform that keeps everything after it running.
A fully database-driven Campus Club and Event Management Suite built as the Database Systems semester project. Charter centralizes every operation a university club touches — memberships, events, attendance, budgets, feedback, sponsorships, venues — into a single Oracle SQL backend and Oracle APEX frontend, with role-based access control separating what each user can see and do.
16 tables. 17 APEX pages. 8 analytical queries. 4 roles. One system.
University clubs operate across spreadsheets, WhatsApp groups, and verbal agreements. There is no single source of truth for who attended what, how much budget remains, which venues are available, or how engaged members actually are.
Charter fixes that — a centralized analytical platform where admins, presidents, faculty advisors, and members each interact with exactly the data their role requires.
🔐 Role Based Access Control
Four roles. Four different views of the same system. Custom authentication backed by the CHARTER_USERS table — not Oracle APEX default accounts. Login credentials verified against the database, roles assigned via session state after authentication.
| Role | Access Level |
|---|---|
| Admin | Full access — all pages, all CRUD |
| President | Events, Workshops, Competitions, Meetups, Attendance, Memberships — view Registrations, edit existing ones |
| Member | Registration and Feedback only - view Registrations & Feedback, create new ones |
| Advisor | Analytics and Budget — read only |
🏛️ Club and Member Management
Create and manage clubs with assigned faculty advisors. Track members by degree, semester, and status. Memberships are club-specific — a member's role (President, Secretary, etc.) is tied to a specific club, not their global account.
📅 Event Management — Workshop, Competition, Meetup
Events are modeled using Total Disjoint Specialization — every event must be exactly one of Workshop, Competition, or Meetup, each with its own subclass-specific fields. Like an abstract class in Java — you cannot instantiate a bare Event.
Subclass-filtered dropdowns ensure Workshop pages only show Workshop-type events. Same for Competitions and Meetups.
✅ Registration and Attendance Pipeline
Members register for events with status defaulting to Waitlisted. Presidents and Admins confirm or cancel registrations. Attendance is tracked per confirmed registration. Only members with Attended = YES are eligible to submit feedback — enforced at the database level, not just the UI. Button level authorization separates who can create vs edit at every stage of the pipeline.
⭐ Feedback System
Feedback is linked to Attendance, not Member. You cannot review an event you did not attend. Verified attendance is the gate — keeping the rating system honest.
💰 Budget Tracking
Per-club, per-semester budgets with a virtual column for remaining balance — auto-computed as TotalAmount - AmountSpent directly in the schema. No manual updates, no sync issues.
🏢 Venue Management
Venues tracked with availability control. Only available venues appear in the Event form dropdown — unavailable venues are filtered at the query level, not the application level.
📊 Analytics Dashboard
8 analytical charts and reports. 4 live stat cards with real-time data. Visible to Admin and Advisor roles.
- Context Aware Read Only Fields — record identity fields locked after creation, operational fields remain editable
- Transparent Subclass Relations — Event IDs displayed directly in subclass forms, filtered by EventType
| # | Table | Role |
|---|---|---|
| 1 | FacultyAdvisor | Club oversight |
| 2 | Club | Core club entity |
| 3 | Member | Registered students |
| 4 | Role | Club roles and permissions |
| 5 | Membership | Resolves Member ↔ Club M:N |
| 6 | Venue | Event locations with availability |
| 7 | Sponsor | Event sponsors |
| 8 | Event | Superclass — core of the system |
| 9 | Workshop | Subclass of Event |
| 10 | Competition | Subclass of Event |
| 11 | Meetup | Subclass of Event |
| 12 | Registration | Resolves Member ↔ Event M:N |
| 13 | Attendance | Tracks event attendance |
| 14 | Budget | Per club per semester |
| 15 | Feedback | Verified attendee reviews |
| 16 | Charter_Users | Application user accounts |
-- Total Disjoint Specialization on Event
-- Every event MUST be a Workshop, Competition, or Meetup
-- Cannot insert a bare Event row — like abstract class in Java
CONSTRAINT event_type CHECK (EventType IN ('Workshop','Competition','Meetup'))
-- Virtual column — Budget Remaining auto-computed, never stale
BudgetRemaining AS (TotalAmount - AmountSpent)
-- Feedback gate — only verified attendees can review
-- Feedback.AttendanceID → Attendance.AttendanceID
-- WHERE Attendance.Attended = 'YES'
-- Venue filter — only available venues in Event form dropdown
WHERE Venue.IsAvailable = 'YES'
-- Button level authorization — granular control per operation
-- Member can create registrations but not edit existing ones
-- President can edit registrations but not create new ones
-- Advisor can view budget but cannot create or modify records
-- Only Admin can delete any record system wide
-- Read Only fields on edit — context aware
-- Fields that define record identity are locked after creation
-- Member, Club locked in Membership — role change via update
-- EventType, Club locked in Event — venue and sponsor flexible
-- Registration reference locked in Attendance and Feedback
-- Club, Semester locked in Budget — amounts remain editable
-- Subclass transparency — numeric Event IDs in subclass forms
-- Makes PK-FK relationship explicit at UI level
-- Filtered by EventType — Workshop IDs only in Workshop form📐 Enhanced Entity Relationship Diagram
🗺️ Relational Model
| # | Query | What It Reveals |
|---|---|---|
| 1 | Most Attended Events | Events ranked by verified attendance count |
| 2 | Top Rated Events | Events ranked by average feedback score |
| 3 | Club Budget Utilization | Spending percentage per club per semester |
| 4 | Most Active Members | Members ranked by total registrations |
| 5 | Sponsor Contribution Ranking | Sponsors ranked by total amount contributed |
| 6 | Monthly Event Count | Event volume trend over time |
| 7 | Venue Utilization | Venues ranked by events hosted |
| 8 | Member Retention | Membership growth tracked over time |
charter/
│
├── docs/
│ ├── EERD.png ← entity relationship diagram
│ └── Relational_Model.png ← relational model
│
├── sql/
│ ├── 01_create_tables.sql ← full schema creation
│ ├── 02_insert_data.sql ← sample data
│ ├── 03_auth_setup.sql ← Charter_Users setup
│ └── analytical_queries/
│ ├── 01_most_attended_events.sql
│ ├── 02_top_rated_events.sql
│ ├── 03_club_budget_utilization.sql
│ ├── 04_most_active_members.sql
│ ├── 05_sponsor_contribution_ranking.sql
│ ├── 06_monthly_event_count.sql
│ ├── 07_venue_utilization.sql
│ └── 08_member_retention.sql
│
├── apex/
│ └── charter_app.sql ← full APEX application export
│
└── README.md
| Element | Value |
|---|---|
| Primary Color | #1B2A4A Deep Navy |
| Accent Color | #F0A500 Gold |
| Background | #F8F9FA Off White |
| Card Background | #FFFFFF Pure White |
| Text | #2D2D2D Dark Charcoal |
| Theme | Oracle APEX Universal Theme |
| Icons | Font Awesome |
| Chart Colors | Navy + Gold diagonal alternating |
Oracle Database · Oracle APEX Workspace
# Step 1 - Clone
git clone https://github.com/MuhammadAhmadHamim/charter.git-- Step 2 — Create the schema
-- SQL Workshop → SQL Scripts → Run
01_create_tables.sql
-- Step 3 — Load sample data
02_insert_data.sql
-- Step 4 — Configure authentication
03_auth_setup.sql-- Step 5 — Import the APEX application
App Builder → Import → charter_app.sql
┌──────────────┬───────────────┬───────────┐
│ Username │ Password │ Role │
├──────────────┼───────────────┼───────────┤
│ admin │ admin123 │ Admin │
│ president │ president123 │ President │
│ member │ member123 │ Member │
│ advisor │ advisor123 │ Advisor │
└──────────────┴───────────────┴───────────┘
- Course — Database Systems (DBS)
- Semester — 3
- Institution — COMSATS University Islamabad, Wah Cantt Campus
- Instructor — Amjad Usman
Every design decision in Charter has a reason. The Total Disjoint Specialization on Event is not an accident — it enforces at the schema level what would otherwise require application-level validation. The Feedback-to-Attendance link is not just good design — it makes gaming the review system structurally impossible. The virtual column on Budget is not a shortcut — it eliminates an entire category of sync bugs before they can exist.
Good database design is not about tables. It's about making wrong states unrepresentable.
Designed at the schema level. Enforced at every level below it.