A relational database management system (RDBMS) engineered to power TalentLMS, a comprehensive Learning Management System. The repository showcases advanced database lifecycle architecture spanning conceptual Entity-Relationship (ER) modeling, strict relational mapping schema design, data definition setups, and analytical data validation workflows.
- Normalized Layout Design: Structured modular configurations to mitigate anomalies and eliminate processing redundancy.
- Referential Data Integrity: Enforced operational consistency utilizing programmatic boundaries, unique indexing parameters, and cascading foreign key configurations.
- Transactional Testing: Populated relational schemas using dynamic multi-row dataset inputs and validated performance via multi-table analytical joins.
The database build is decoupled into a sequential script execution tree:
| Execution Order | Component Script | Technical Operational Scope |
|---|---|---|
| 1 | 01_Create_Database.sql | Initializes the core physical database container schema context. |
| 2 | 02_Create_Tables.sql | Generates all 21 highly normalized relational entity tables with strict indexing rules. |
| 3 | 03_Insert_Dummy_Data.sql | Seeds the relational schema with dense transactional mock datasets. |
| 4 | 04_Queries.sql | Contains administrative evaluation scripts, complex filters, and validation analytics. |
| 5 | ER Diagram (TalentLMS).pdf | Detailed enterprise blueprint illustrating full model bounds and structural cardinalities. |
The systemic schema decouples operational workloads into isolated domain blocks to enforce high transaction safety:
- Organization: Manages multi-tenant corporate identity nodes and domain registration metrics.
- Users: Central actor registry tracking roles (admin, instructor, learner) with validation constraints.
- Branch: Controls regional or departmental sub-organizational operational units.
- Category: Contextually groups learning topics to streamline system searches.
- Course: Primary inventory tracking curriculum bounds and instructional ownership.
- Enrollment: Many-to-Many resolution registry evaluating user course allocations.
- Module: Segregates courses into structural lessons and educational steps.
- Quiz, Question, and Answer: Formulates verification paths, question variations, and evaluation outputs.
- Assignment and AssignmentSubmission: Manages student task execution, file logs, and grades tracking.
- Progress and Certificate: Evaluates active completion parameters and records generated credentials.
- Discussion and Comment: Powers collaborative interaction channels across instructional tracks.
- Notification: Handles system notification parameters with read status validation flags.
- Survey and SurveyResponse: Collects student evaluation outputs to measure continuous teaching quality.
- Payment: Tracks platform income streams, handling transaction amounts and gateway logs.
- EventLog: Maintains a historical record of system operations for security audits.
The system relies on explicit Primary and Foreign Key pairings to keep structural ties consistent during state shifts.
[ Organization ] ---(1:N)---> [ Users (Instructors) ] ---(1:N)---> [ Courses ] │ └───(1:N)───┐ ▼ [ Users (Learners) ] ----------------(1:N)--------------------------> [ Enrollment ]
- Identity Mechanics: Enforced auto-increment keys across entity matrices utilizing MS SQL IDENTITY(1,1) settings.
- Domain Check Rules: Isolated user scope inputs using strict rule bounds.
- Default Baselines: Maintained log accuracy using automatic timestamps.
To deploy the entire TalentLMS pipeline architecture on your local Microsoft SQL Server environment via the SQLCMD tool or SQL Server Management Studio (SSMS), execute the scripts sequentially:
sqlcmd -S YOUR_SERVER -U sa -P YOUR_PASS -i 01_Create_Database.sql
sqlcmd -S YOUR_SERVER -U sa -P YOUR_PASS -i 02_Create_Tables.sql
sqlcmd -S YOUR_SERVER -U sa -P YOUR_PASS -i 03_Insert_Dummy_Data.sql
sqlcmd -S YOUR_SERVER -U sa -P YOUR_PASS -i 04_Queries.sql
(Note: Alternatively, open the files in order inside SSMS and press F5 to execute.)
All administrative transactional evaluations, structural setups, and execution records are validated and documented as verification images inside this repository.