A role-based learning management web app built with Flask, SQLAlchemy, and Bootstrap. SLAMS was developed as part of the Database Systems course in my 3rd semester of BS Artificial Intelligence (DUET). It showcases database modeling, relational queries, role-based access, and data integrity in a full-stack context.
The project demonstrates core database systems concepts applied in a real application: relational schemas, joins, filters, aggregation, authorization, consistency, and CRUD flows. Features emphasize how backend models map to real-world academic entities (courses, enrollments, attendance, notes, ratings) with clear access controls for admins, teachers, and students.
- Authentication and Roles: Registration with role-based approval; login/logout via Flask-Login; access guards per role.
- Dashboards:
- Admin: totals for users/courses/notes/enrollments/announcements, pending approvals, global attendance, recent feedback.
- Teacher: active courses, total students, pending actions (notes + enrollments), per-course pending badges.
- Student: enrolled courses, counts, attendance percentage, recent announcements.
- Courses and Departments: CRUD with approval workflow; sorting/searching/filtering; instructor assignment.
- Enrollments: Student requests; teacher approve/reject/remove; attendance stats per enrollment; bulk-enroll eligible students.
- Attendance: Session marking (Present/Absent/Late) and per-student history/percentages.
- Notes and Ratings: File uploads with visibility (public/private), approval, downloads; ratings (1–5) with comments; admin ratings view.
- Announcements: Admin/teacher posting (teacher pending approval) with role-aware visibility; approvals and deletions.
- Feedback: User submissions with admin review.
- Admin SQL (read-only): Safe SELECT/PRAGMA runner with schema introspection for learning DB inspection.
- Theming: Light/dark-friendly
themed-tablestyling; clear search/input contrast for usability.
| Component | Purpose |
|---|---|
| Python / Flask | Web framework and routing |
| SQLAlchemy | ORM and relational modeling/queries |
| SQLite (configurable) | Persistent storage for course data |
| Flask-Login | Authentication/session management |
| Bootstrap 5, Icons, Font Awesome | UI components and icons |
Custom CSS (static/css/theme.css) |
Theming, contrast, and table styles |
SLAMS/
├── app.py # Flask app factory, routes, queries, role guards
├── models.py # SQLAlchemy models (Course, Enrollment, Attendance, Note, Rating, Announcement, etc.)
├── extensions.py # DB init
├── templates/ # Jinja templates (dashboards, courses, enrollments, notes, attendance)
├── static/css/theme.css# Light/dark theme and input/search styling
├── static/uploads/ # Uploaded note files
├── requirements.txt # Python dependencies
└── wsgi.py # WSGI entrypoint
- Create and activate a virtualenv (Python 3.10+ recommended).
- Install dependencies:
pip install -r requirements.txt- Initialize the database:
flask --app app.py init-db- Run the development server:
flask --app app.py run --debugEnvironment variables:
SLAMS_DATABASE— SQLite path (default/home/abdulhayykhan/slams/database/slams.db)SLAMS_SECRET_KEY— Flask secret key
Ensure static/uploads/ is writable for note files.
- Register or log in (teachers require approval; students auto-approve per logic).
- Admin: approve users, courses, and notes; monitor metrics and feedback.
- Teacher: create/manage courses; handle enrollment approvals or bulk-enroll; mark attendance; post announcements; review ratings.
- Student: request enrollments, view/download approved notes, check attendance history, read announcements.
- Admin SQL page: run safe SELECT/PRAGMA to inspect schema and data for learning purposes.
| Concept | How it appears in SLAMS |
|---|---|
| Relational Modeling | Users, Courses, Enrollments, Attendance, Notes, Ratings, Announcements linked by FKs |
| Joins and Aggregation | Dashboards and listings aggregate counts, averages, attendance percentages |
| Access Control | Role-based guards on routes and visibility rules for notes/announcements |
| Transactions | Enrollment approvals, attendance marking, and note approvals commit via SQLAlchemy session |
| Data Integrity | Approval flags, visibility constraints, and enrollment uniqueness checks |
| Query Inspection | Admin SQL tool allows read-only SELECT/PRAGMA for schema/data exploration |
- Enrollment uniqueness & approval flow:
- Prevents duplicate enrollments per student/course by checking existing rows before insert.
- Uses
is_approvedflag to stage pending rows, then updates in-place on approve; demonstrates state transitions without extra tables. - Bulk-enroll logic filters only non-enrolled approved students → shows set-difference and membership checks in SQL.
- Attendance aggregation:
- Per-course attendance maps use grouped aggregates:
COUNT(*), conditional sums viaCASEto compute present/total and percentages. - Student course detail derives present/late/absent, illustrating derived attributes from base facts.
- Per-course attendance maps use grouped aggregates:
- Ratings aggregation:
- Notes list joins ratings to compute
AVGandCOUNTwithGROUP BYNote ID; highlights handling of NULLs viaCOALESCE.
- Notes list joins ratings to compute
- Role-scoped visibility:
- Queries gate rows by role (student vs teacher vs admin) and visibility flags; illustrates predicate-based access control in SQL.
- Announcements & notes approval:
- Approval flags separate draft/pending vs published; demonstrates soft-state promotion without schema changes.
- Admin SQL console:
- Restricted to read-only
SELECT/PRAGMA, reinforcing least-privilege and safe inspection of schema/indexes.
- Restricted to read-only
- Schema introspection:
- Uses SQLAlchemy inspector to list tables/columns for teaching schema design and metadata understanding.
- Foreign keys & cascading logic (conceptual): Users ↔ Courses ↔ Enrollments/Attendance/Notes/Ratings maintain relational consistency (enforced logically in app/DB).
- Validation before write: Checks for required fields, allowed file types, valid course/user IDs before insert/update.
- Idempotent updates: Attendance marking updates existing rows if present; enrollment approval updates status instead of duplicating rows.
- Visibility & approval flags: Control read paths so only approved/published entities appear to restricted roles.
- Indexed-style filters: frequent filters on
course_id,student_id,is_approvedmirror common indexing strategies (on production DBs you’d index these columns). - Batching/aggregation in SQLAlchemy queries avoids N+1 on dashboards by joining and grouping in a single round-trip.
- Add proper DB indexes and measure query time on larger datasets (courses, enrollments, attendance).
- Introduce foreign-key constraints and test rejection of orphan inserts; compare with current logical enforcement.
- Extend approval states (draft → pending → approved) and observe query/predicate changes.
- Add pagination to notes/announcements and examine
LIMIT/OFFSETvs cursor-based approaches.
- Create admin/teacher/student accounts; verify approval flows.
- Enroll a student, approve as teacher, mark attendance, and confirm percentages on manage enrollments.
- Upload a note as teacher; approve as admin; rate as student; view ratings as admin.
- Post announcements as teacher/admin; verify visibility by role.
- Use the admin SQL tool to inspect tables and confirm schema relationships.
Abdul Hayy Khan
abdulhayykhan.1@gmail.com
This project is open-source and available for educational use under the MIT License.