Skip to content

Az-main/Alymni-Sync--DBMS-Project-

BranchesTags

Repository files navigation

🎓 Alumni Sync

A Production-Grade Alumni Management System
Full-stack web application built with Flask, MySQL, and Jinja2 — featuring role-based access, a normalized relational schema with triggers & views, real-time messaging, job board, event management, and more.

📌 Overview

Alumni Sync is a comprehensive alumni management platform designed for university ecosystems. It bridges the gap between current students and graduated alumni by providing networking tools, a job/internship board, event management, direct messaging, and profile directories — all powered by a normalized MySQL relational database with advanced features like triggers, views, stored procedures, and full-text search.

Built as a Database Management Systems (DBMS) Lab project at United International University (UIU).

✨ Key Features

Module Description
Authentication & RBAC Secure registration/login with Werkzeug bcrypt hashing, role-based access control (Admin, Alumni, Student)
Dual Profile System Separate profile schemas for Alumni (work history, skills, achievements) and Students (CGPA, semester, expected graduation)
Job Board Alumni post jobs with skills tagging, salary ranges, target audience filters; Students apply with CV upload
Event Management Create/register for events with visibility controls, attendee tracking, and speaker assignments
Private Messaging Real-time 1-to-1 conversation threads with read receipts and inbox management
Alumni Directory Searchable directory with skill-based and company-based filtering, profile view tracking
Student Directory Filterable student listing by degree program and search
Announcements Targeted announcements with priority levels, pinning, and audience segmentation
Profile Management Photo upload, skill tagging, bio editing, privacy settings, account deletion (soft-delete)
CMS (About Page) Admin-editable About page with sections, feature cards, quick links, and revision history

🏗️ System Architecture

┌────────────────────────────────────────────────────────┐
│                    CLIENT (Browser)                     │
│         HTML5 + CSS3 + Jinja2 Templates                │
└────────────────────┬───────────────────────────────────┘
                     │ HTTP Requests
┌────────────────────▼───────────────────────────────────┐
│              FLASK APPLICATION (app.py)                 │
│  ┌─────────────┐ ┌──────────────┐ ┌──────────────────┐│
│  │   Routes    │ │  Decorators  │ │  Jinja2 Filters  ││
│  │  (20+ API)  │ │ @login_req   │ │ format_time/date ││
│  │             │ │ @alumni_req  │ │                  ││
│  └──────┬──────┘ └──────────────┘ └──────────────────┘│
│         │                                              │
│  ┌──────▼──────────────────────────────────────────┐  │
│  │         db_connect.py (Database Layer)           │  │
│  │  get_db_connection() | execute_query(sql, params)│  │
│  │  Parameterized Queries — SQL Injection Safe      │  │
│  └──────┬──────────────────────────────────────────┘  │
└─────────┼──────────────────────────────────────────────┘
          │ mysql-connector-python
┌─────────▼──────────────────────────────────────────────┐
│                MySQL 8.0 (InnoDB)                      │
│  ┌───────────┐ ┌────────┐ ┌──────────┐ ┌────────────┐│
│  │ 30+ Tables│ │8 Views │ │7 Triggers│ │ FT Indexes ││
│  │   (3NF)   │ │        │ │          │ │            ││
│  └───────────┘ └────────┘ └──────────┘ └────────────┘│
└────────────────────────────────────────────────────────┘

🗄️ Database Design

Schema Statistics

  • 30+ Tables — Fully normalized to Third Normal Form (3NF)
  • 7 Triggers — Auto-update counters for likes, comments, applications, conversation timestamps
  • 8 Views — Pre-built analytical queries for dashboards and reports
  • FULLTEXT Indexes — On profiles, job postings, posts, and events for fast search
  • Foreign Keys — Referential integrity with CASCADE, SET NULL, RESTRICT policies
  • Soft-Delete Pattern — is_deleted + deleted_at across all major entities
  • Audit Trail — created_at, updated_at, created_by, updated_by on all tables

Entity Relationship Summary

roles ──< users ──< alumni_profiles ──< alumni_skills
                │                    ──< alumni_work_history
                │                    ──< alumni_achievements
                ├──< student_profiles
                ├──< job_postings ──< job_applications
                │                 ──< job_posting_skills
                ├──< events ──< event_registrations
                │           ──< event_speakers
                ├──< conversations ──< messages
                ├──< posts ──< post_likes
                │          ──< post_comments ──< comment_likes
                ├──< announcements
                ├──< notifications
                ├──< follows
                └──< feedback

Key Tables

Table Purpose Notable Columns
users Central auth table email, password_hash, role_id, status (ENUM), is_deleted
alumni_profiles Alumni info FULLTEXT(first_name, last_name, current_job_title, company, bio)
student_profiles Student info cgpa DECIMAL(3,2), expected_graduation_year, current_semester
job_postings Job board target_audience ENUM, salary_min/max DECIMAL(12,2), views_count
job_applications Applications cv_upload, status (8-stage workflow), applied_at GENERATED ALWAYS
conversations Messaging UNIQUE(user_one_id, user_two_id), last_message_at
events Events slug UNIQUE, max_attendees, is_online, registration_deadline

Triggers

Trigger Table Action
tr_post_like_insert/delete post_likes Auto-increment/decrement posts.likes_count
tr_post_comment_insert/delete post_comments Auto-increment/decrement posts.comments_count
tr_comment_like_insert/delete comment_likes Auto-increment/decrement post_comments.likes_count
tr_message_insert messages Update conversations.last_message_at
tr_job_application_insert/delete job_applications Auto-increment/decrement job_postings.applications_count

Views

View Purpose
vw_active_alumni Active alumni with degree, campus, and profile stats
vw_dashboard_stats Aggregated platform statistics for admin dashboard
vw_upcoming_events Published future events with registration counts
vw_open_jobs Open job postings with poster info and skill aggregation
vw_alumni_by_skill Alumni filterable by skill domain and proficiency
vw_pending_references Pending reference requests with requester and alumni details
vw_alumni_engagements Alumni engagement history (speaking, training, judging)
vw_alumni_careers Full work history timeline per alumni

🛠️ Technology Stack

Layer Technology Purpose
Runtime Python 3.8+ Server-side language
Framework Flask 2.x Web framework, routing, sessions
Database MySQL 8.0 (InnoDB) RDBMS with ACID transactions
ORM/Connector mysql-connector-python Direct SQL execution with parameterized queries
Templating Jinja2 Server-side HTML rendering
Auth Werkzeug Security generate_password_hash / check_password_hash (bcrypt)
Config python-dotenv Environment variable management via .env
Frontend HTML5, CSS3, Google Fonts (Inter) Responsive dark-themed UI
File Handling Werkzeug secure_filename Sanitized file uploads (CV, photos)

📁 Project Structure

Alumni Sync/
├── app.py                      # Main Flask application (1200+ LOC, 20+ routes)
├── db_connect.py               # Database connection layer & query executor
├── init_db.py                  # Schema loader (parses SQL with DELIMITER handling)
├── populate_data.py            # Seeds sample data (campus, degrees, users, content)
├── clean_db.py                 # Nuclear DB reset utility
├── check_db.py                 # Database diagnostic tool
├── alumni_sync_schema.sql      # Complete MySQL schema (1370+ lines, 30+ tables)
├── requirements.txt            # Python dependencies
├── setup_and_run.bat           # One-click Windows setup & launch script
├── .env                        # Environment config (DB credentials, secret key)
│
├── static/
│   ├── css/
│   │   └── style.css           # Complete stylesheet (2400+ lines)
│   └── uploads/
│       ├── cvs/                # Uploaded CV/resume files
│       └── photos/             # Profile photos
│
└── templates/                  # 25 Jinja2 templates
    ├── base.html               # Master layout (navbar, footer, flash messages)
    ├── home.html               # Homepage with stats & featured content
    ├── login.html              # Authentication
    ├── register.html           # Dual registration (alumni/student)
    ├── dashboard_alumni.html   # Alumni dashboard (my jobs, events, announcements)
    ├── dashboard_student.html  # Student dashboard (applications, registrations)
    ├── jobs.html               # Job board with search & filters
    ├── job_detail.html         # Job details + applicant list (for poster)
    ├── create_job.html         # Job posting form
    ├── apply_job.html          # Job application with CV upload
    ├── events.html             # Event listing
    ├── event_detail.html       # Event details + registrant list
    ├── create_event.html       # Event creation form
    ├── alumni_directory.html   # Searchable alumni directory
    ├── alumni_profile.html     # Alumni profile with skills & work history
    ├── student_directory.html  # Student directory with degree filter
    ├── student_profile.html    # Student profile view
    ├── edit_profile_alumni.html# Alumni profile editor
    ├── edit_profile_student.html# Student profile editor
    ├── announcements.html      # Targeted announcements feed
    ├── create_announcement.html# Announcement creation
    ├── messages_inbox.html     # Conversation list
    ├── compose_message.html    # Send new message
    ├── view_conversation.html  # Message thread with auto-read
    ├── about.html              # CMS-driven about page
    └── forgot_password.html    # Password recovery

🚀 Installation & Setup

Prerequisites

  • Python 3.8+
  • MySQL Server 8.0+
  • pip (Python package manager)

Quick Setup (Windows)

# One-click setup — creates venv, installs deps, initializes DB, runs app
setup_and_run.bat

Manual Setup

# 1. Clone the repository
git clone https://github.com/<your-username>/alumni-sync.git
cd alumni-sync

# 2. Create virtual environment
python -m venv .venv
.venv\Scripts\activate        # Windows
# source .venv/bin/activate   # Linux/Mac

# 3. Install dependencies
pip install -r requirements.txt

# 4. Configure environment variables
# Create .env file in project root:
DB_HOST=localhost
DB_USER=root
DB_PASSWORD=your_mysql_password
DB_NAME=alumni_sync
SECRET_KEY=your-secret-key-here
# 5. Initialize database (creates schema + seeds sample data)
python init_db.py

# 6. Run the application
python app.py

Access

Open http://127.0.0.1:5000 in your browser.

Demo Credentials

Role Email Password
Alumni alumni@example.com user123
Student student@example.com user123

🔗 API Route Map

Route Method Auth Role Description
/ GET — All Homepage with stats, upcoming events, recent jobs
/login GET/POST — All User authentication
/register GET/POST — All Dual registration (alumni/student)
/logout GET ✅ All Session termination
/dashboard GET ✅ All Role-adaptive dashboard
/jobs GET ✅ All Job board with search & type filter
/jobs/<id> GET ✅ All Job detail + applicant list (poster only)
/jobs/create GET/POST ✅ Alumni Create job posting with skills
/jobs/<id>/apply GET/POST ✅ All* Apply with CV upload (audience-restricted)
/jobs/<id>/delete POST ✅ Owner Soft-delete own job
/events GET ✅ All Upcoming events listing
/events/<id> GET ✅ All Event detail + registrant list
/events/<id>/register POST ✅ All* Register for event (audience-restricted)
/events/create GET/POST ✅ All Create new event
/events/<id>/delete POST ✅ Owner Soft-delete own event
/alumni GET ✅ All Alumni directory with skill/name search
/alumni/<id> GET ✅ All Alumni profile (skills, work history)
/students GET ✅ All Student directory with degree filter
/students/<id> GET ✅ All Student profile view
/profile/edit GET/POST ✅ All Edit own profile + photo upload
/delete_account POST ✅ All Soft-delete account
/announcements GET ✅ All Audience-filtered announcements
/announcements/create GET/POST ✅ All Create announcement
/messages GET ✅ All Conversation inbox
/messages/compose/<id> GET/POST ✅ All Send direct message
/messages/<id> GET ✅ All View thread + auto-mark read
/download/cv/<id> GET ✅ Poster Download applicant's CV
/about GET — All CMS-driven about page

🔒 Security Measures

  • Password Hashing — Werkzeug's generate_password_hash with bcrypt/scrypt
  • Parameterized Queries — All SQL uses %s placeholders (no string interpolation)
  • Session-based Auth — Flask sessions with configurable secret key
  • File Upload Validation — Extension whitelist (pdf, doc, docx, png, jpg, jpeg), 16MB limit, secure_filename()
  • Soft Delete — Data is never permanently removed; is_deleted=1 flags
  • Role-based Access — @login_required and @alumni_required decorators
  • Audience Restrictions — Jobs and events enforce target_audience visibility rules

🧪 DBMS Concepts Demonstrated

This project showcases the following relational database concepts:

  • ✅ Third Normal Form (3NF) — Separate tables for roles, degrees, campuses, skills
  • ✅ Foreign Key Constraints — ON DELETE CASCADE / SET NULL / RESTRICT
  • ✅ Triggers — 7 triggers for automatic counter maintenance
  • ✅ Views — 8 pre-built analytical views
  • ✅ FULLTEXT Indexes — Fast search across profiles, jobs, and posts
  • ✅ ENUM Types — Type-safe constrained fields
  • ✅ Generated Columns — applied_at GENERATED ALWAYS AS (application_date) STORED
  • ✅ JSON Columns — submitted_documents, revision_data
  • ✅ Composite Indexes — Multi-column indexes for query optimization
  • ✅ Unique Constraints — Prevent duplicate follows, applications, registrations
  • ✅ Audit Columns — created_at, updated_at, created_by, updated_by
  • ✅ Parameterized Queries — SQL injection prevention
  • ✅ Transaction Safety — InnoDB engine with ACID compliance

🖥️ Screenshots

Add screenshots of your running application here.

📄 License

This project is developed for academic purposes as part of the DBMS Lab coursework at United International University (UIU).

Built with ❤️ using Flask + MySQL

About

Full-stack Alumni Management System built with Flask & MySQL featuring 30+ normalized tables (3NF), 7 triggers, 8 views, FULLTEXT search, role-based access control, job board, event management, and real-time messaging.

Topics

css python flask sql web-application mysql-database html-css python-lambda dbms-project alumni-management

Resources

Readme
Activity

Stars

0 stars

Watchers

0 watching

Forks

0 forks
Report repository

Releases

No releases published

Packages

 
 
 

Contributors

Languages