Project: Student Records Database

In this capstone project, we will apply the complete SQLite database lifecycle—Schema Definition, Parameterized Queries, Context Managers, Row Factories, and Full CRUD Operations—to engineer an enterprise-grade Command-Line Student Records Database System.

1. Project Specifications & Schema Design

Our application manages academic student records stored persistently in university.db.

Database Schema

Key Capabilities:

1. 1
   Create: Enroll new students with duplicate roll number checks and valid CGPA constraints (0.0 to 10.0).
2. 2
   Read: Display active students in clean tabular format using sqlite3.Row.
3. 3
   Search: Search records using wildcard LIKE operators safely.
4. 4
   Update: Modify a student's CGPA or department by their unique roll number.
5. 5
   Delete: Remove a student record with confirmation.
6. 6
   Analytics: Compute department averages, highest CGPA, and student counts.

2. Complete Project Implementation

3. Sample Execution Simulation

Multiple Choice Questions

1. In our SQLite student table schema, what does roll_no TEXT UNIQUE NOT NULL enforce?

A. Roll numbers are hashed with SHA-256 B. Every student must have a roll number, and no two students can share the same roll number C. Roll numbers can only contain integers D. Roll numbers are deleted after graduation Answer: B Explanation: UNIQUE NOT NULL guarantees that the column must contain a value and that each entry across the table is strictly unique.

2. What happens if a user tries to enroll a student with a CGPA of 12.5?

A. Python rounds the value down to 10.0 B. SQLite triggers a sqlite3.IntegrityError because the value violates the CHECK(cgpa >= 0.0 AND cgpa <= 10.0) constraint C. The record is inserted with NULL D. The database creates a backup file Answer: B Explanation: The SQL table defines a CHECK constraint; inserting an out-of-range value violates database integrity and raises IntegrityError.

3. How does the search_students() method prevent SQL Injection when querying with wildcards?

A. By replacing spaces with dashes B. By wrapping the query in wildcards (f"%{query}%") and passing it as a bound parameter ? C. By deleting quotation marks D. By calling eval() Answer: B Explanation: Parameterized placeholders ? treat user input strictly as literal values, even when containing wildcard % characters, preventing SQL injection.

4. Which SQL clause groups rows sharing common department values to calculate averages?

A. ORDER BY B. GROUP BY C. PARTITION BY D. SPLIT BY Answer: B Explanation: GROUP BY department aggregates rows by department, allowing aggregate functions (AVG(), COUNT(), MAX()) to compute per-group statistics.

5. Why does update_student() check cursor.rowcount > 0 after executing its SQL statement?

A. To verify whether any student record actually matched the given roll number and was updated B. To check if the hard drive has free space C. To count how many columns exist in the table D. To commit the transaction Answer: A Explanation: An UPDATE query on a non-existent roll number runs successfully with zero rows modified. Checking cursor.rowcount allows notifying the user if the record was not found.