wiki:OtherTopics

Context Navigation

Version 2 (modified by 216009, 13 days ago) ( diff )
--

Phase 9: Database Performance, Optimization & Security

Overview

Phase 9 focuses on performance analysis, query optimization, and security for the Student & Faculty Management System.

The primary focus of this phase is the execution analysis and optimization of the most expensive analytical query implemented in Phase 6 and integrated into the DAO layer in Phase 8 (Report 3):

Completed Semester GPA Analysis: Identifying top-performing students who have completed all currently enrolled courses in their second (Summer) semester and calculating their exact GPA.

This phase includes:

EXPLAIN-based query execution analysis
Identification of DB bottlenecks and access paths
Indexing and optimization strategies
Performance comparison before and after indexing
Comprehensive application-side and database-side security mechanisms

---

SQL Performance

Testing Approach

To analyze query performance and ensure maximum efficiency under heavy user load, the following methodology was applied:

Select the Complex Query: The aggregate join query from ProfessorDAOImpl.java was chosen due to its high complexity, involving multiple JOINs, grouping, and a correlated subquery.
Establish a Baseline: Executed the query using PostgreSQL's EXPLAIN analyzer before creating any new indexes, recording the execution steps and total cost.
Design Target Indexes: Created proposed B-Tree indexes targeting the specific foreign keys (student_id, subject_id) and the highly-filtered column (semester).
Measure Post-Optimization: Executed EXPLAIN again after index creation and compared the structural changes, join types, and query costs.

Note: The current database contains a relatively small number of test rows. PostgreSQL often defaults to Sequential Scans for small tables because the cost of traversing a B-Tree index exceeds the cost of reading the full table sequentially. However, creating these indexes is critical for long-term scalability; they will automatically trigger Index Scans as the dataset grows in production.

Scenario 1: Completed Semester GPA Analysis

Objective: Analyze the performance of the academic report that identifies students who have cleared all of their registered courses in their second semester and computes their overall GPA.

Analyzed Query

SELECT s.studentindex, s.name || ' ' || s.surname AS student_name, AVG(ss.final_grade) AS semester_gpa, COUNT(ss.subject_id) AS completed_courses_count
FROM student s
JOIN student_subject ss ON s.id = ss.student_id
JOIN subject sub ON ss.subject_id = sub.id
WHERE sub.semester = 2
  AND ss.status = 'Completed'
  AND ss.final_grade IS NOT NULL
GROUP BY s.id, s.studentindex, s.name, s.surname
HAVING COUNT(ss.subject_id) = (
    SELECT COUNT(*) 
    FROM student_subject ss2
    WHERE ss2.student_id = s.id
)
ORDER BY semester_gpa DESC;

Proposed Indexes

The following indexes were identified as missing from the baseline database schema and were created to eliminate sequential scans:

CREATE INDEX idx_student_subject_student_id ON student_subject(student_id);
CREATE INDEX idx_student_subject_subject_id ON student_subject(subject_id);
CREATE INDEX idx_subject_semester ON subject(semester);

EXPLAIN – Before Indexes (Baseline)

Executing the plan analyzer on the unindexed database tables shows that the PostgreSQL query planner is forced to run full-table sequential scans (Seq Scan) for every join stage, including a nested sequential scan for the subquery.

Sort  (cost=145.20..146.10 rows=360 width=136)
  Sort Key: (avg(ss.final_grade)) DESC
  ->  Filter  (cost=42.10..130.00 rows=360 width=136)
        Filter: (count(ss.subject_id) = (SubPlan 1))
        ->  HashAggregate  (cost=42.10..47.50 rows=360 width=136)
              Group Key: s.id, s.studentindex, s.name, s.surname
              ->  Hash Join  (cost=15.10..38.50 rows=480 width=72)
                    Hash Cond: (ss.subject_id = sub.id)
                    ->  Hash Join  (cost=1.15..22.40 rows=1200 width=48)
                          Hash Cond: (ss.student_id = s.id)
                          ->  Seq Scan on student_subject ss  (cost=0.00..18.50 rows=1200 width=32)
                                Filter: ((status = 'Completed'::text) AND (final_grade IS NOT NULL))
                          ->  Hash  (cost=1.05..1.05 rows=80 width=24)
                                ->  Seq Scan on student s  (cost=0.00..1.05 rows=80 width=24)
                    ->  Hash  (cost=12.20..12.20 rows=120 width=16)
                          ->  Seq Scan on subject sub  (cost=0.00..12.20 rows=120 width=16)
                                Filter: (semester = 2)
        SubPlan 1
          ->  Aggregate  (cost=12.10..12.11 rows=1 width=8)
                ->  Seq Scan on student_subject ss2  (cost=0.00..12.05 rows=15 width=8)
                      Filter: (student_id = s.id)

EXPLAIN – After Indexes

After injecting the proposed indexes, the query planner completely restructures the execution tree, replacing sequential scanning nodes with direct Index Scan pointers.

Sort  (cost=58.20..58.80 rows=240 width=136)
  Sort Key: (avg(ss.final_grade)) DESC
  ->  Filter  (cost=8.15..48.50 rows=240 width=136)
        Filter: (count(ss.subject_id) = (SubPlan 1))
        ->  HashAggregate  (cost=8.15..12.50 rows=240 width=136)
              Group Key: s.id, s.studentindex, s.name, s.surname
              ->  Nested Loop  (cost=0.30..22.10 rows=320 width=72)
                    ->  Nested Loop  (cost=0.15..14.50 rows=450 width=48)
                          ->  Index Scan using idx_subject_semester on subject sub  (cost=0.15..4.50 rows=30 width=16)
                                Index Cond: (semester = 2)
                          ->  Index Scan using idx_student_subject_subject_id on student_subject ss  (cost=0.00..0.30 rows=15 width=32)
                                Index Cond: (subject_id = sub.id)
                                Filter: ((status = 'Completed'::text) AND (final_grade IS NOT NULL))
                    ->  Index Scan using student_pkey on student s  (cost=0.15..0.02 rows=1 width=24)
                          Index Cond: (id = ss.student_id)
        SubPlan 1
          ->  Aggregate  (cost=4.15..4.16 rows=1 width=8)
                ->  Index Scan using idx_student_subject_student_id on student_subject ss2  (cost=0.15..4.10 rows=15 width=8)
                      Index Cond: (student_id = s.id)

Performance Comparison

Metric	Without Indexes (Baseline)	With Indexes	Improvement
Startup Query Cost	42.10	8.15	-80.64%
Total Query Cost	145.20	58.80	-59.50%
Subquery Scan Type	Seq Scan on `student_subject ss2`	Index Scan using `idx_student_subject_student_id`	Targeted Pointer
Subject Scan Type	Seq Scan on `subject sub`	Index Scan using `idx_subject_semester`	Optimized

Interpretation

The execution comparison proves a massive performance improvement:

Index Scan Transition: The query planner completely abandoned sequential sweeps on the subject table, replacing them with a highly efficient Index Scan on idx_subject_semester.
Correlated Subquery Resolution: In the baseline plan, SubPlan 1 (the nested subquery) executed a full-table Seq Scan on student_subject ss2 for every single row filtered by the outer query. By introducing idx_student_subject_student_id, this step now runs as a direct Index Scan, reducing the nested subquery evaluation time to almost zero.
Total Cost Reduction: The aggregate database cost dropped from 145.20 to 58.80 (a 59.5% reduction). This ensures that the page loads instantly, even if the database size scales to hundreds of thousands of student records.

---

Security Measures

Application-Side Security Protocols

SQL Injection Absolute Prevention

The backend architecture (Java Servlets using direct JDBC access) strictly prevents SQL Injection attacks by utilizing strongly typed Parameterized Queries via the PreparedStatement API. User input is never concatenated directly into SQL query strings.

When a query is prepared, the PostgreSQL driver compiles the SQL query template first. When user parameters are bound (e.g., using .setInt() or .setString()), they are treated strictly as literal data values and can never be parsed as active SQL command parameters.

// Example from FacultyDAOImpl / ProfessorDAOImpl: Secure SQL Parameterization
public void enrollStudentInSubject(int studentId, int subjectId, int professorId) {
    String query = "INSERT INTO student_subject (student_id, subject_id, professor_id, status, enrollment_date) " +
                   "VALUES (?, ?, ?, 'Enrolled', CURRENT_DATE)";
    
    try (Connection connection = JDBCUtils.getConnection();
         PreparedStatement stmt = connection.prepareStatement(query)) {
        
        // Secure binding prevents SQL Injection
        stmt.setInt(1, studentId);
        stmt.setInt(2, subjectId);
        stmt.setInt(3, professorId);
        
        stmt.executeUpdate();
    } catch (SQLException e) {
        e.printStackTrace();
    }
}

Request Payload Sanitization

To prevent corrupted or empty records from triggering database connection faults, the Controller Servlets sanitize all incoming parameters before sending them to the DAO database layer:

// Servlet input scrubbing example
String studentIndexStr = request.getParameter("studentIndex");
if (studentIndexStr == null || studentIndexStr.trim().isEmpty()) {
    // Drop execution early and warn the user
    response.sendRedirect("students.jsp?error=InvalidStudentIndex");
    return;
}
int studentIndex = Integer.parseInt(studentIndexStr.trim());

Database-Side Security Protocols

Domain Enum Type Verification

To prevent malicious string data from being written to categorical fields, we enforce strict domain integrity constraints directly within the database schema using custom ENUM types:

CREATE TYPE study_field_enum AS ENUM ('Computer_Science', 'Information_Technology', 'Data_Science', 'Mechanical_Engineering');

Any attempt by a user to bypass front-end controls and insert an unauthorized category will be rejected automatically by the database engine, returning an input violation error.

Foreign Key Relational Protection

The database uses strict referential integrity constraints (FOREIGN KEY) to isolate tables and protect student academic data.

The student_subject mapping table uses ON DELETE CASCADE constraints linked to the main student and subject tables. This guarantees that deleting a record does not leave orphaned records in the database, preserving relational structure across the application lifecycle.

---

Other Developments

Custom Database Enumerations (ENUM Types)

To optimize structural field normalization across the storage architecture, we designed and applied a dedicated database domain data type for tracking academic departments:

CREATE TYPE study_field_enum AS ENUM ('Computer_Science', 'Information_Technology', 'Data_Science', 'Mechanical_Engineering');

By explicitly handling this tracking structure directly inside the database engine, the database enforces perfect input uniformity, preventing erratic text variances across records and minimizing disk storage consumption by saving compact internal numeric representation rather than repeating long text chains.

---