45. Graduation & Capstone -- Full SMS Application

Level: Final project

ℹ️ What You'll Learn

• Complete SMS database design
• All 12 tables with relationships
• Core workflows (enrollment, grading, fees, attendance)
• Report generation and dashboards
• API patterns and pagination
• Performance optimization
• Security and compliance
• Production deployment checklist
• Troubleshooting and monitoring

Capstone: Bring everything together. This article synthesizes all 44 articles into one complete, production-ready School Management System.

Complete Schema Setup

Full database creation:

-- Create database
CREATE DATABASE NexCodeSchool;
USE NexCodeSchool;

-- Table 1: School
CREATE TABLE School (
    Id INT PRIMARY KEY IDENTITY(1,1),
    PublicId UNIQUEIDENTIFIER DEFAULT NEWID(),
    Name NVARCHAR(200) NOT NULL,
    Code NVARCHAR(20) UNIQUE NOT NULL,
    Subdomain NVARCHAR(50),
    Board NVARCHAR(100),
    Address NVARCHAR(500),
    City NVARCHAR(100),
    State NVARCHAR(100),
    PinCode NVARCHAR(10),
    Phone NVARCHAR(20),
    Email NVARCHAR(100),
    PrincipalName NVARCHAR(100),
    LogoUrl NVARCHAR(500),
    SubscriptionPlan NVARCHAR(50),
    IsActive BIT DEFAULT 1,
    CreatedAt DATETIME DEFAULT GETDATE(),
    CONSTRAINT UQ_School_Code UNIQUE (Code)
);

-- Table 2: User
CREATE TABLE [User] (
    Id INT PRIMARY KEY IDENTITY(1,1),
    PublicId UNIQUEIDENTIFIER DEFAULT NEWID(),
    SchoolId INT NOT NULL,
    Name NVARCHAR(100) NOT NULL,
    Email NVARCHAR(100) NOT NULL,
    PasswordHash NVARCHAR(MAX),
    Role NVARCHAR(20),  -- SuperAdmin, Admin, Principal, Teacher, Staff, Student
    IsActive BIT DEFAULT 1,
    LastLoginAt DATETIME,
    CreatedAt DATETIME DEFAULT GETDATE(),
    FOREIGN KEY (SchoolId) REFERENCES School(Id),
    CONSTRAINT UQ_User_Email UNIQUE (Email)
);

-- Table 3: Student
CREATE TABLE Student (
    Id INT PRIMARY KEY IDENTITY(1,1),
    PublicId UNIQUEIDENTIFIER DEFAULT NEWID(),
    SchoolId INT NOT NULL,
    UserId INT,
    Name NVARCHAR(100) NOT NULL,
    RollNumber NVARCHAR(20) NOT NULL,
    ClassName NVARCHAR(10) NOT NULL,
    Section NVARCHAR(5),
    DateOfBirth DATE,
    ParentName NVARCHAR(100),
    ParentPhone NVARCHAR(20),
    Address NVARCHAR(500),
    Status NVARCHAR(20) DEFAULT 'Active',  -- Active, Inactive, Graduated, Transferred
    CreatedAt DATETIME DEFAULT GETDATE(),
    FOREIGN KEY (SchoolId) REFERENCES School(Id),
    FOREIGN KEY (UserId) REFERENCES [User](Id),
    CONSTRAINT UQ_Student_RollClass UNIQUE (SchoolId, RollNumber, ClassName)
);

-- Table 4: Teacher
CREATE TABLE Teacher (
    Id INT PRIMARY KEY IDENTITY(1,1),
    PublicId UNIQUEIDENTIFIER DEFAULT NEWID(),
    SchoolId INT NOT NULL,
    UserId INT,
    Name NVARCHAR(100) NOT NULL,
    EmployeeCode NVARCHAR(20) UNIQUE,
    Qualification NVARCHAR(200),
    ExperienceYears INT,
    Salary DECIMAL(10,2),
    JoiningDate DATE,
    IsActive BIT DEFAULT 1,
    CreatedAt DATETIME DEFAULT GETDATE(),
    FOREIGN KEY (SchoolId) REFERENCES School(Id),
    FOREIGN KEY (UserId) REFERENCES [User](Id)
);

-- Table 5: Staff
CREATE TABLE Staff (
    Id INT PRIMARY KEY IDENTITY(1,1),
    PublicId UNIQUEIDENTIFIER DEFAULT NEWID(),
    SchoolId INT NOT NULL,
    UserId INT,
    Name NVARCHAR(100) NOT NULL,
    EmployeeCode NVARCHAR(20) UNIQUE,
    Designation NVARCHAR(50),  -- Clerk, Librarian, LabAssistant, Accountant, Peon, Security
    Department NVARCHAR(50),
    Salary DECIMAL(10,2),
    JoiningDate DATE,
    IsActive BIT DEFAULT 1,
    CreatedAt DATETIME DEFAULT GETDATE(),
    FOREIGN KEY (SchoolId) REFERENCES School(Id),
    FOREIGN KEY (UserId) REFERENCES [User](Id)
);

-- Table 6: Subject
CREATE TABLE Subject (
    Id INT PRIMARY KEY IDENTITY(1,1),
    PublicId UNIQUEIDENTIFIER DEFAULT NEWID(),
    SchoolId INT NOT NULL,
    TeacherId INT,
    Name NVARCHAR(100) NOT NULL,
    Code NVARCHAR(20),
    ClassName NVARCHAR(10),
    MaxMarks INT,
    PassingMarks INT,
    IsPractical BIT DEFAULT 0,
    CreatedAt DATETIME DEFAULT GETDATE(),
    FOREIGN KEY (SchoolId) REFERENCES School(Id),
    FOREIGN KEY (TeacherId) REFERENCES Teacher(Id)
);

-- Table 7: Exam
CREATE TABLE Exam (
    Id INT PRIMARY KEY IDENTITY(1,1),
    PublicId UNIQUEIDENTIFIER DEFAULT NEWID(),
    SchoolId INT NOT NULL,
    SubjectId INT NOT NULL,
    ExamName NVARCHAR(100),
    ExamType NVARCHAR(20),  -- UnitTest, MidTerm, Final, Practical, Assignment
    ExamDate DATE,
    MaxMarks INT,
    Duration INT,  -- Minutes
    CreatedAt DATETIME DEFAULT GETDATE(),
    FOREIGN KEY (SchoolId) REFERENCES School(Id),
    FOREIGN KEY (SubjectId) REFERENCES Subject(Id)
);

-- Table 8: ExamResult
CREATE TABLE ExamResult (
    Id INT PRIMARY KEY IDENTITY(1,1),
    PublicId UNIQUEIDENTIFIER DEFAULT NEWID(),
    SchoolId INT NOT NULL,
    StudentId INT NOT NULL,
    ExamId INT NOT NULL,
    MarksObtained DECIMAL(5,2),
    IsAbsent BIT DEFAULT 0,
    Remarks NVARCHAR(500),
    CreatedAt DATETIME DEFAULT GETDATE(),
    FOREIGN KEY (SchoolId) REFERENCES School(Id),
    FOREIGN KEY (StudentId) REFERENCES Student(Id) ON DELETE CASCADE,
    FOREIGN KEY (ExamId) REFERENCES Exam(Id)
);

-- Table 9: FeeAccount
CREATE TABLE FeeAccount (
    Id INT PRIMARY KEY IDENTITY(1,1),
    PublicId UNIQUEIDENTIFIER DEFAULT NEWID(),
    SchoolId INT NOT NULL,
    StudentId INT NOT NULL,
    AcademicYear NVARCHAR(9),
    TotalFees DECIMAL(10,2),
    PaidAmount DECIMAL(10,2) DEFAULT 0,
    DiscountAmount DECIMAL(10,2) DEFAULT 0,
    DueDate DATE,
    Status NVARCHAR(20) DEFAULT 'Pending',  -- Pending, Partial, Paid, Overdue
    CreatedAt DATETIME DEFAULT GETDATE(),
    FOREIGN KEY (SchoolId) REFERENCES School(Id),
    FOREIGN KEY (StudentId) REFERENCES Student(Id) ON DELETE CASCADE
);

-- Table 10: FeePayment
CREATE TABLE FeePayment (
    Id INT PRIMARY KEY IDENTITY(1,1),
    PublicId UNIQUEIDENTIFIER DEFAULT NEWID(),
    SchoolId INT NOT NULL,
    FeeAccountId INT NOT NULL,
    Amount DECIMAL(10,2),
    PaidOn DATETIME DEFAULT GETDATE(),
    PaymentMode NVARCHAR(20),  -- Cash, Online, Cheque, DemandDraft
    ReceiptNumber NVARCHAR(50),
    TransactionId NVARCHAR(100),
    CollectedBy NVARCHAR(100),
    CreatedAt DATETIME DEFAULT GETDATE(),
    FOREIGN KEY (SchoolId) REFERENCES School(Id),
    FOREIGN KEY (FeeAccountId) REFERENCES FeeAccount(Id) ON DELETE CASCADE
);

-- Table 11: Attendance
CREATE TABLE Attendance (
    Id INT PRIMARY KEY IDENTITY(1,1),
    PublicId UNIQUEIDENTIFIER DEFAULT NEWID(),
    SchoolId INT NOT NULL,
    StudentId INT NOT NULL,
    SubjectId INT NOT NULL,
    [Date] DATE,
    IsPresent BIT DEFAULT 0,
    MarkedBy NVARCHAR(100),
    CreatedAt DATETIME DEFAULT GETDATE(),
    FOREIGN KEY (SchoolId) REFERENCES School(Id),
    FOREIGN KEY (StudentId) REFERENCES Student(Id) ON DELETE CASCADE,
    FOREIGN KEY (SubjectId) REFERENCES Subject(Id)
);

-- Table 12: AuditLog
CREATE TABLE AuditLog (
    AuditId INT PRIMARY KEY IDENTITY(1,1),
    SchoolId INT,
    Action NVARCHAR(50),
    EntityName NVARCHAR(100),
    EntityId INT,
    OldValues NVARCHAR(MAX),
    NewValues NVARCHAR(MAX),
    ChangedBy NVARCHAR(100),
    ChangedAt DATETIME DEFAULT GETDATE(),
    IPAddress NVARCHAR(20)
);

-- Create indexes for performance
CREATE INDEX IX_Student_SchoolClass ON Student(SchoolId, ClassName);
CREATE INDEX IX_ExamResult_Student ON ExamResult(StudentId, ExamId);
CREATE INDEX IX_FeeAccount_StudentYear ON FeeAccount(StudentId, AcademicYear);
CREATE INDEX IX_Attendance_StudentDate ON Attendance(StudentId, [Date]);
CREATE INDEX IX_AuditLog_Entity ON AuditLog(EntityName, EntityId, ChangedAt DESC);

12 tables: School, User, Student, Teacher, Staff, Subject, Exam, ExamResult, FeeAccount, FeePayment, Attendance, AuditLog.

Core Workflows

Complete enrollment-to-graduation flow:

-- Workflow 1: Semester Enrollment
CREATE PROCEDURE sp_EnrollStudentSemester
    @StudentId INT,
    @ClassName NVARCHAR(10),
    @AcademicYear NVARCHAR(9),
    @IsSuccess BIT OUTPUT
AS
BEGIN
    SET @IsSuccess = 0;
    
    BEGIN TRANSACTION;
    BEGIN TRY
        -- Validate
        IF NOT EXISTS (SELECT 1 FROM Student WHERE Id = @StudentId)
            THROW 50001, 'Student not found', 1;
        
        -- Update class
        UPDATE Student SET ClassName = @ClassName WHERE Id = @StudentId;
        
        -- Create fee account
        DECLARE @FeeAmount DECIMAL(10,2) = 50000;  -- Standard fees
        INSERT INTO FeeAccount (SchoolId, StudentId, AcademicYear, TotalFees, DueDate)
        SELECT SchoolId, @StudentId, @AcademicYear, @FeeAmount, DATEFROMPARTS(YEAR(GETDATE()), 6, 30)
        FROM Student WHERE Id = @StudentId;
        
        -- Generate attendance slots for all subjects
        INSERT INTO Attendance (SchoolId, StudentId, SubjectId, [Date], IsPresent)
        SELECT DISTINCT s.SchoolId, @StudentId, sub.Id, CAST(GETDATE() AS DATE), 0
        FROM Student s
        CROSS JOIN Subject sub
        WHERE s.Id = @StudentId
        AND sub.ClassName = @ClassName;
        
        -- Log
        INSERT INTO AuditLog (SchoolId, Action, EntityName, EntityId, ChangedBy, ChangedAt)
        SELECT SchoolId, 'EnrollSemester', 'Student', @StudentId, SYSTEM_USER, GETDATE()
        FROM Student WHERE Id = @StudentId;
        
        COMMIT TRANSACTION;
        SET @IsSuccess = 1;
    END TRY
    BEGIN CATCH
        ROLLBACK TRANSACTION;
        SET @IsSuccess = 0;
    END CATCH
END;

-- Workflow 2: Exam & Grading
CREATE PROCEDURE sp_RecordExamAndGrade
    @StudentId INT,
    @ExamId INT,
    @MarksObtained DECIMAL(5,2),
    @IsSuccess BIT OUTPUT
AS
BEGIN
    SET @IsSuccess = 0;
    
    BEGIN TRY
        DECLARE @MaxMarks INT, @PassMarks INT, @SchoolId INT;
        
        SELECT @MaxMarks = e.MaxMarks, @SchoolId = e.SchoolId
        FROM Exam e WHERE e.Id = @ExamId;
        
        IF @MarksObtained < 0 OR @MarksObtained > @MaxMarks
            THROW 50001, 'Marks out of range', 1;
        
        -- Record result
        INSERT INTO ExamResult (SchoolId, StudentId, ExamId, MarksObtained)
        VALUES (@SchoolId, @StudentId, @ExamId, @MarksObtained);
        
        -- Log
        INSERT INTO AuditLog (SchoolId, Action, EntityName, ChangedBy, ChangedAt)
        VALUES (@SchoolId, 'RecordExam', 'ExamResult', SYSTEM_USER, GETDATE());
        
        SET @IsSuccess = 1;
    END TRY
    BEGIN CATCH
        SET @IsSuccess = 0;
    END CATCH
END;

-- Workflow 3: Fee Payment
CREATE PROCEDURE sp_ProcessFeePayment
    @FeeAccountId INT,
    @Amount DECIMAL(10,2),
    @PaymentMode NVARCHAR(20),
    @IsSuccess BIT OUTPUT
AS
BEGIN
    SET @IsSuccess = 0;
    
    BEGIN TRY
        DECLARE @SchoolId INT, @StudentId INT, @CurrentPaid DECIMAL(10,2), @Total DECIMAL(10,2);
        
        SELECT @SchoolId = SchoolId, @StudentId = StudentId, @CurrentPaid = PaidAmount, @Total = TotalFees
        FROM FeeAccount WHERE Id = @FeeAccountId;
        
        -- Update fee account
        UPDATE FeeAccount
        SET PaidAmount = PaidAmount + @Amount,
            Status = CASE WHEN PaidAmount + @Amount >= TotalFees THEN 'Paid' ELSE 'Partial' END
        WHERE Id = @FeeAccountId;
        
        -- Record payment
        INSERT INTO FeePayment (SchoolId, FeeAccountId, Amount, PaymentMode, CollectedBy)
        VALUES (@SchoolId, @FeeAccountId, @Amount, @PaymentMode, SYSTEM_USER);
        
        -- Log
        INSERT INTO AuditLog (SchoolId, Action, EntityName, ChangedBy, ChangedAt)
        VALUES (@SchoolId, 'FeePayment', 'FeePayment', SYSTEM_USER, GETDATE());
        
        SET @IsSuccess = 1;
    END TRY
    BEGIN CATCH
        SET @IsSuccess = 0;
    END CATCH
END;

Workflows: Enroll -> Exam -> Grade -> Fee Payment.

Dashboard & Reports

Complete reporting views:

-- Dashboard 1: School Overview
CREATE VIEW vw_SchoolDashboard AS
SELECT 
    (SELECT COUNT(DISTINCT Id) FROM Student WHERE Status = 'Active') AS ActiveStudents,
    (SELECT COUNT(DISTINCT Id) FROM Teacher WHERE IsActive = 1) AS ActiveTeachers,
    (SELECT COUNT(DISTINCT Id) FROM Staff WHERE IsActive = 1) AS ActiveStaff,
    (SELECT SUM(TotalFees) FROM FeeAccount) AS TotalFees,
    (SELECT SUM(PaidAmount) FROM FeeAccount) AS CollectedFees,
    (SELECT SUM(TotalFees) - SUM(PaidAmount) FROM FeeAccount) AS DueFees,
    ROUND((SELECT SUM(PaidAmount) FROM FeeAccount) * 100.0 / 
          NULLIF((SELECT SUM(TotalFees) FROM FeeAccount), 0), 2) AS CollectionPercentage;

-- Dashboard 2: Student Progress
CREATE VIEW vw_StudentPerformance AS
SELECT 
    s.Id,
    s.Name,
    s.ClassName,
    ROUND(AVG(er.MarksObtained), 2) AS AvgMarks,
    COUNT(er.Id) AS ExamsTaken,
    CASE WHEN AVG(er.MarksObtained) >= 80 THEN 'A'
         WHEN AVG(er.MarksObtained) >= 70 THEN 'B'
         WHEN AVG(er.MarksObtained) >= 60 THEN 'C'
         ELSE 'F' END AS Grade,
    ROUND(
        (SUM(CASE WHEN a.IsPresent = 1 THEN 1 ELSE 0 END) * 100.0 /
         NULLIF(COUNT(a.Id), 0)), 2
    ) AS AttendancePercent
FROM Student s
LEFT JOIN ExamResult er ON s.Id = er.StudentId
LEFT JOIN Attendance a ON s.Id = a.StudentId
GROUP BY s.Id, s.Name, s.ClassName;

-- Dashboard 3: Fee Collection
CREATE VIEW vw_FeeStatus AS
SELECT 
    s.ClassName,
    COUNT(DISTINCT s.Id) AS TotalStudents,
    SUM(CASE WHEN fa.Status = 'Paid' THEN 1 ELSE 0 END) AS PaidStudents,
    SUM(CASE WHEN fa.Status IN ('Pending', 'Overdue') THEN 1 ELSE 0 END) AS PendingStudents,
    SUM(fa.TotalFees) AS TotalDues,
    SUM(fa.PaidAmount) AS Collected,
    SUM(fa.TotalFees) - SUM(fa.PaidAmount) AS Outstanding
FROM Student s
LEFT JOIN FeeAccount fa ON s.Id = fa.StudentId
GROUP BY s.ClassName;

-- Dashboard 4: Attendance Summary
CREATE VIEW vw_AttendanceSummary AS
SELECT 
    s.ClassName,
    COUNT(DISTINCT s.Id) AS TotalStudents,
    COUNT(DISTINCT CASE WHEN a.IsPresent = 1 THEN a.StudentId END) AS PresentToday,
    COUNT(DISTINCT CASE WHEN a.IsPresent = 0 THEN a.StudentId END) AS AbsentToday,
    ROUND(
        COUNT(DISTINCT CASE WHEN a.IsPresent = 1 THEN a.StudentId END) * 100.0 /
        NULLIF(COUNT(DISTINCT s.Id), 0), 2
    ) AS AttendancePercentage
FROM Student s
LEFT JOIN Attendance a ON s.Id = a.StudentId AND CAST(a.[Date] AS DATE) = CAST(GETDATE() AS DATE)
GROUP BY s.ClassName;

-- Query dashboards
SELECT * FROM vw_SchoolDashboard;
SELECT * FROM vw_StudentPerformance WHERE ClassName = '10-A' ORDER BY AvgMarks DESC;
SELECT * FROM vw_FeeStatus ORDER BY PendingStudents DESC;
SELECT * FROM vw_AttendanceSummary ORDER BY AttendancePercentage;

Dashboards: School overview, student performance, fees, attendance.

API Patterns

Pagination and filtering for API:

-- Paginated student list with filtering
CREATE PROCEDURE sp_GetStudentsList
    @SchoolId INT,
    @PageNumber INT = 1,
    @PageSize INT = 20,
    @ClassName NVARCHAR(10) = NULL,
    @SearchName NVARCHAR(100) = NULL
AS
BEGIN
    DECLARE @Skip INT = (@PageNumber - 1) * @PageSize;
    DECLARE @TotalCount INT;
    
    -- Get total count
    SELECT @TotalCount = COUNT(*)
    FROM Student
    WHERE SchoolId = @SchoolId
    AND (@ClassName IS NULL OR ClassName = @ClassName)
    AND (@SearchName IS NULL OR Name LIKE '%' + @SearchName + '%');
    
    -- Return paginated data
    SELECT 
        Id,
        PublicId,
        Name,
        RollNumber,
        ClassName,
        Status,
        @TotalCount AS TotalCount,
        @PageNumber AS CurrentPage,
        @PageSize AS PageSize,
        CEILING(CAST(@TotalCount AS FLOAT) / @PageSize) AS TotalPages
    FROM Student
    WHERE SchoolId = @SchoolId
    AND (@ClassName IS NULL OR ClassName = @ClassName)
    AND (@SearchName IS NULL OR Name LIKE '%' + @SearchName + '%')
    ORDER BY ClassName, RollNumber
    OFFSET @Skip ROWS FETCH NEXT @PageSize ROWS ONLY
    FOR JSON AUTO;
END;

-- Usage
EXEC sp_GetStudentsList @SchoolId = 1, @PageNumber = 1, @PageSize = 20, @ClassName = '10-A';

API: Pagination, filtering, JSON response.

Performance & Optimization Summary

Key optimizations:

-- Index strategy summary
-- 1. Foreign key columns: IX_Table_ForeignKey
CREATE INDEX IX_Student_SchoolId ON Student(SchoolId);
CREATE INDEX IX_ExamResult_StudentId ON ExamResult(StudentId);

-- 2. Frequently filtered columns: IX_Table_Column
CREATE INDEX IX_FeeAccount_Status ON FeeAccount(Status);
CREATE INDEX IX_Attendance_Date ON Attendance([Date]);

-- 3. Composite indexes for common queries
CREATE INDEX IX_Attendance_StudentDate ON Attendance(StudentId, [Date]);

-- 4. Include columns for covering queries
CREATE INDEX IX_ExamResult_StudentExam_Include ON ExamResult(StudentId, ExamId) INCLUDE (MarksObtained);

-- Materialized views for expensive aggregates
CREATE VIEW vw_ClassStats WITH SCHEMABINDING AS
SELECT 
    ClassName,
    COUNT(DISTINCT StudentId) AS StudentCount,
    ROUND(AVG(CAST((SELECT AVG(MarksObtained) FROM ExamResult WHERE StudentId = s.Id) AS DECIMAL(5,2))), 2) AS AvgMarks
FROM dbo.Student s
GROUP BY ClassName;

CREATE UNIQUE CLUSTERED INDEX IX_ClassStats ON vw_ClassStats(ClassName);

-- Query tuning: Use execution plan
SET STATISTICS IO ON;
SELECT * FROM Student WHERE ClassName = '10-A';
-- Check: Index Seek vs Table Scan
SET STATISTICS IO OFF;

Optimization: Indexes, composite indexes, covering indexes, materialized views.

Production Deployment Checklist

Ready for production:

-- Checklist items
DECLARE @ChecksPass NVARCHAR(MAX) = '';

-- 1. All tables have primary keys
SELECT @ChecksPass = CONCAT(@ChecksPass, '[OK] Primary keys OK; ');

-- 2. Foreign keys referential integrity
SELECT @ChecksPass = CONCAT(@ChecksPass, '[OK] Foreign keys OK; ');

-- 3. Critical indexes exist
SELECT @ChecksPass = CONCAT(@ChecksPass, '[OK] Indexes OK; ');

-- 4. Backup strategy in place
SELECT @ChecksPass = CONCAT(@ChecksPass, '[OK] Backup OK; ');

-- 5. Audit logging configured
SELECT @ChecksPass = CONCAT(@ChecksPass, '[OK] Audit OK; ');

-- 6. Security roles and permissions
SELECT @ChecksPass = CONCAT(@ChecksPass, '[OK] Security OK; ');

-- 7. Monitoring and alerting
SELECT @ChecksPass = CONCAT(@ChecksPass, '[OK] Monitoring OK; ');

-- 8. Disaster recovery plan
SELECT @ChecksPass = CONCAT(@ChecksPass, '[OK] Disaster recovery OK');

PRINT CONCAT('Production Ready: ', @ChecksPass);

Deployment: Checks -> Backup -> Security -> Monitoring -> Live.

---

🎯 Q1: What are the 12 SMS tables?

1. School (tenant, school info)
2. User (login, roles)
3. Student (enrollment, personal)
4. Teacher (staff, subjects)
5. Staff (non-teaching staff)
6. Subject (courses, marks)
7. Exam (tests, schedules)
8. ExamResult (marks, grades)
9. FeeAccount (billing, student)
10. FeePayment (collections)
11. Attendance (daily presence)
12. AuditLog (compliance, history)

🎯 Q2: What are the core workflows?

Enroll -> Record Exams -> Grade -> Track Fees -> Mark Attendance

• Reports: Progress card, fee status, attendance summary
• API: Pagination, filtering, JSON

🎯 Q3: How do I optimize SMS queries?

• Index FK columns
• Composite indexes (StudentId, Date)
• Covering indexes (include MarksObtained)
• Materialized views for aggregates
• Statistics: STATISTICS IO

Use execution plan to guide optimization.

🎯 Q4: What performance goals?

• Enroll student: <1s
• Record exam: <500ms
• Report generation: <5s
• API pagination: <200ms

Monitor: Slow query log, DMVs, index fragmentation.

🎯 Q5: How do I ensure security?

• Row-level security (school-scoped)
• Roles: SuperAdmin, Admin, Principal, Teacher, Staff, Student
• Encrypt passwords, sensitive data
• Audit all writes
• Minimal permissions per role

Verify: GRANT/REVOKE per role.

🎯 Q6: What's my deployment checklist?

1. Database schema (all 12 tables)
2. Relationships (FK constraints)
3. Indexes (performance)
4. Stored procedures (workflows)
5. Views (reports)
6. Backup strategy (FULL, DIFF, LOG)
7. Monitoring (DMVs, jobs)
8. Disaster recovery (tested)

Go live when all checked.

---

You've Completed All 45 Articles

Congratulations! You've mastered SQL Server backend development with complete, production-ready patterns for the School Management System.

What You Can Build Now

[OK] Complete CRUD applications
[OK] Complex multi-step workflows
[OK] Performance-optimized queries
[OK] Secure, audited systems
[OK] Scalable APIs with pagination
[OK] Real-time dashboards
[OK] Scheduled batch jobs
[OK] Disaster recovery plans

Final Practice Script

Copy this full script into SSMS and run it. It creates a separate practice database named NexCodingSqlPractice, inserts sample School Management System data, and gives you practice queries at the end.

Safe note: this script only resets tables inside NexCodingSqlPractice. Do not run practice scripts inside a real production database.

IF DB_ID('NexCodingSqlPractice') IS NULL
BEGIN
    CREATE DATABASE NexCodingSqlPractice;
END;
GO

USE NexCodingSqlPractice;
GO

DROP TABLE IF EXISTS Attendance;
DROP TABLE IF EXISTS FeePayment;
DROP TABLE IF EXISTS FeeAccount;
DROP TABLE IF EXISTS ExamResult;
DROP TABLE IF EXISTS Exam;
DROP TABLE IF EXISTS Subject;
DROP TABLE IF EXISTS Student;
DROP TABLE IF EXISTS Teacher;
DROP TABLE IF EXISTS School;
GO

CREATE TABLE School
(
    SchoolId INT PRIMARY KEY IDENTITY(1,1),
    Name NVARCHAR(150) NOT NULL,
    City NVARCHAR(80) NOT NULL
);

CREATE TABLE Teacher
(
    TeacherId INT PRIMARY KEY IDENTITY(1,1),
    SchoolId INT NOT NULL,
    Name NVARCHAR(100) NOT NULL,
    EmployeeCode VARCHAR(20) NOT NULL UNIQUE,
    Qualification VARCHAR(50),
    IsActive BIT DEFAULT 1,
    CONSTRAINT FK_Teacher_School FOREIGN KEY (SchoolId) REFERENCES School(SchoolId)
);

CREATE TABLE Student
(
    StudentId INT PRIMARY KEY IDENTITY(1,1),
    SchoolId INT NOT NULL,
    Name NVARCHAR(100) NOT NULL,
    RollNumber INT NOT NULL,
    ClassName VARCHAR(10) NOT NULL,
    Section VARCHAR(5) NOT NULL,
    Status VARCHAR(20) DEFAULT 'Active',
    CONSTRAINT FK_Student_School FOREIGN KEY (SchoolId) REFERENCES School(SchoolId)
);

CREATE TABLE Subject
(
    SubjectId INT PRIMARY KEY IDENTITY(1,1),
    SchoolId INT NOT NULL,
    TeacherId INT NOT NULL,
    Name NVARCHAR(100) NOT NULL,
    ClassName VARCHAR(10) NOT NULL,
    MaxMarks INT DEFAULT 100,
    CONSTRAINT FK_Subject_School FOREIGN KEY (SchoolId) REFERENCES School(SchoolId),
    CONSTRAINT FK_Subject_Teacher FOREIGN KEY (TeacherId) REFERENCES Teacher(TeacherId)
);

CREATE TABLE Exam
(
    ExamId INT PRIMARY KEY IDENTITY(1,1),
    SchoolId INT NOT NULL,
    SubjectId INT NOT NULL,
    ExamName VARCHAR(50) NOT NULL,
    ExamDate DATE NOT NULL,
    MaxMarks INT DEFAULT 100,
    CONSTRAINT FK_Exam_School FOREIGN KEY (SchoolId) REFERENCES School(SchoolId),
    CONSTRAINT FK_Exam_Subject FOREIGN KEY (SubjectId) REFERENCES Subject(SubjectId)
);

CREATE TABLE ExamResult
(
    ResultId INT PRIMARY KEY IDENTITY(1,1),
    SchoolId INT NOT NULL,
    StudentId INT NOT NULL,
    ExamId INT NOT NULL,
    MarksObtained DECIMAL(5,2) NOT NULL,
    CONSTRAINT FK_Result_School FOREIGN KEY (SchoolId) REFERENCES School(SchoolId),
    CONSTRAINT FK_Result_Student FOREIGN KEY (StudentId) REFERENCES Student(StudentId),
    CONSTRAINT FK_Result_Exam FOREIGN KEY (ExamId) REFERENCES Exam(ExamId)
);

CREATE TABLE FeeAccount
(
    FeeAccountId INT PRIMARY KEY IDENTITY(1,1),
    SchoolId INT NOT NULL,
    StudentId INT NOT NULL,
    AcademicYear VARCHAR(10) NOT NULL,
    TotalFees DECIMAL(10,2) NOT NULL,
    PaidAmount DECIMAL(10,2) DEFAULT 0,
    Status VARCHAR(20) DEFAULT 'Pending',
    CONSTRAINT FK_FeeAccount_School FOREIGN KEY (SchoolId) REFERENCES School(SchoolId),
    CONSTRAINT FK_FeeAccount_Student FOREIGN KEY (StudentId) REFERENCES Student(StudentId)
);

CREATE TABLE FeePayment
(
    FeePaymentId INT PRIMARY KEY IDENTITY(1,1),
    SchoolId INT NOT NULL,
    FeeAccountId INT NOT NULL,
    Amount DECIMAL(10,2) NOT NULL,
    PaymentDate DATE NOT NULL,
    PaymentMode VARCHAR(20) NOT NULL,
    CONSTRAINT FK_FeePayment_School FOREIGN KEY (SchoolId) REFERENCES School(SchoolId),
    CONSTRAINT FK_FeePayment_Account FOREIGN KEY (FeeAccountId) REFERENCES FeeAccount(FeeAccountId)
);

CREATE TABLE Attendance
(
    AttendanceId INT PRIMARY KEY IDENTITY(1,1),
    SchoolId INT NOT NULL,
    StudentId INT NOT NULL,
    SubjectId INT NOT NULL,
    AttendanceDate DATE NOT NULL,
    IsPresent BIT NOT NULL,
    CONSTRAINT FK_Attendance_School FOREIGN KEY (SchoolId) REFERENCES School(SchoolId),
    CONSTRAINT FK_Attendance_Student FOREIGN KEY (StudentId) REFERENCES Student(StudentId),
    CONSTRAINT FK_Attendance_Subject FOREIGN KEY (SubjectId) REFERENCES Subject(SubjectId)
);
GO

INSERT INTO School (Name, City)
VALUES ('NexCoding Public School', 'Bangalore');

INSERT INTO Teacher (SchoolId, Name, EmployeeCode, Qualification)
VALUES
    (1, 'Dr. Mehta', 'EMP001', 'M.Sc'),
    (1, 'Mrs. Rao', 'EMP002', 'B.Ed'),
    (1, 'Mr. Sharma', 'EMP003', 'M.Tech');

INSERT INTO Student (SchoolId, Name, RollNumber, ClassName, Section)
VALUES
    (1, 'Ravi Kumar', 101, '10-A', 'A'),
    (1, 'Priya Sharma', 102, '10-A', 'A'),
    (1, 'Arjun Reddy', 103, '10-B', 'B'),
    (1, 'Sneha Patel', 104, '10-B', 'B'),
    (1, 'Kiran Rao', 105, '10-C', 'C');

INSERT INTO Subject (SchoolId, TeacherId, Name, ClassName)
VALUES
    (1, 1, 'Mathematics', '10-A'),
    (1, 2, 'English', '10-A'),
    (1, 3, 'Science', '10-B'),
    (1, 1, 'Mathematics', '10-C');

INSERT INTO Exam (SchoolId, SubjectId, ExamName, ExamDate)
VALUES
    (1, 1, 'Unit Test 1', '2026-06-10'),
    (1, 2, 'Unit Test 1', '2026-06-12'),
    (1, 3, 'Unit Test 1', '2026-06-14'),
    (1, 4, 'Unit Test 1', '2026-06-16');

INSERT INTO ExamResult (SchoolId, StudentId, ExamId, MarksObtained)
VALUES
    (1, 1, 1, 92),
    (1, 2, 1, 86),
    (1, 1, 2, 78),
    (1, 2, 2, 90),
    (1, 3, 3, 81),
    (1, 4, 3, 67),
    (1, 5, 4, 88);

INSERT INTO FeeAccount (SchoolId, StudentId, AcademicYear, TotalFees, PaidAmount, Status)
VALUES
    (1, 1, '2026-2027', 50000, 50000, 'Paid'),
    (1, 2, '2026-2027', 50000, 30000, 'Partial'),
    (1, 3, '2026-2027', 45000, 0, 'Pending'),
    (1, 4, '2026-2027', 45000, 45000, 'Paid'),
    (1, 5, '2026-2027', 40000, 10000, 'Partial');

INSERT INTO FeePayment (SchoolId, FeeAccountId, Amount, PaymentDate, PaymentMode)
VALUES
    (1, 1, 50000, '2026-06-01', 'UPI'),
    (1, 2, 30000, '2026-06-02', 'Cash'),
    (1, 4, 45000, '2026-06-03', 'Card'),
    (1, 5, 10000, '2026-06-04', 'UPI');

INSERT INTO Attendance (SchoolId, StudentId, SubjectId, AttendanceDate, IsPresent)
VALUES
    (1, 1, 1, '2026-06-10', 1),
    (1, 2, 1, '2026-06-10', 1),
    (1, 3, 3, '2026-06-10', 0),
    (1, 4, 3, '2026-06-10', 1),
    (1, 5, 4, '2026-06-10', 1);
GO

-- Practice 1: View all students
SELECT StudentId, Name, RollNumber, ClassName, Section
FROM Student;

-- Practice 2: Students with pending or partial fees
SELECT s.Name, s.ClassName, f.TotalFees, f.PaidAmount, f.Status
FROM Student s
INNER JOIN FeeAccount f ON s.StudentId = f.StudentId
WHERE f.Status IN ('Pending', 'Partial');

-- Practice 3: Exam marks with subject name
SELECT s.Name AS Student, sub.Name AS Subject, e.ExamName, r.MarksObtained
FROM ExamResult r
INNER JOIN Student s ON r.StudentId = s.StudentId
INNER JOIN Exam e ON r.ExamId = e.ExamId
INNER JOIN Subject sub ON e.SubjectId = sub.SubjectId
ORDER BY s.Name, sub.Name;

-- Practice 4: Average marks by class
SELECT s.ClassName, AVG(r.MarksObtained) AS AverageMarks
FROM ExamResult r
INNER JOIN Student s ON r.StudentId = s.StudentId
GROUP BY s.ClassName
ORDER BY s.ClassName;

-- Practice 5: Attendance status
SELECT s.Name, sub.Name AS Subject, a.AttendanceDate,
       CASE WHEN a.IsPresent = 1 THEN 'Present' ELSE 'Absent' END AS AttendanceStatus
FROM Attendance a
INNER JOIN Student s ON a.StudentId = s.StudentId
INNER JOIN Subject sub ON a.SubjectId = sub.SubjectId;

Next Steps

1. Build a real project: Apply SMS patterns to your own backend
2. Study the source: Read stored procedures in production systems
3. Optimize continuously: Monitor, profile, improve
4. Contribute: Share patterns on GitHub, StackOverflow, your team

Resources

• SQL Server documentation: https://learn.microsoft.com/sql/
• Execution plans: Use SSMS query analyzer
• DMVs: sys.dm_exec_requests, sys.dm_exec_sql_text
• Forums: StackOverflow (tag: sql-server)

---

🤖Use AI to Learn Faster

Use ChatGPT, Claude, or Copilot to go deeper on Complete School Management System implementation. Try these prompts:

• "Walk through full enrollment workflow (student -> fee -> attendance)"
• "Design dashboard for principal (overview, fees, performance)"
• "Create API endpoint for student list (pagination, filtering)"
• "What indexes do I need for SMS?"
• "Design disaster recovery plan for SMS"
• "Quiz me: Final exam -- all 45 topics"

💡 Tip: After reading this article, paste your own code into AI and ask "What could go wrong here and why?" — fastest way to find edge cases and deepen understanding.

---

Thank you for completing NexCoding's SQL Server tutorial series.

Built by Sahasra Technologies
Email: info@nexcoding.in | WhatsApp: +91-9951510727
Website: nexcoding.in