Course Outline for SQLITE DATABASE MANAGEMENT

Course Duration: 16 weeks (4 months) 

Course Level: Beginner to Intermediate

Prerequisites: Basic understanding of programming concepts. Familiarity with any programming language is a plus

📌 Month 1: Foundations of SQLite & SQL Basics

      Week 1: Introduction & Setup

       ---  What is SQLite? Features, advantages & use cases

       ---  Embedded database vs. server-based databases

       ---  Installing SQLite on Windows/Linux/Mac

       ---  Understanding SQLite CLI & GUI tools (DB Browser, SQLiteStudio)

       ---  Creating and managing database files

      Week 2: SQL Basics & Data Types

       ---  SQL overview: DDL, DML, DCL

       ---  SQLite supported data types

       ---  Creating tables, inserting records

       ---  Updating and deleting records

       ---  Querying data using SELECT

      Week 3: Filtering, Sorting & Built-in Functions

       ---  WHERE clause filtering techniques

       ---  ORDER BY, LIMIT, DISTINCT

       ---  Aggregate functions: COUNT, SUM, AVG, MIN, MAX

       ---  String, Date, and Numeric functions

       ---  Practical exercises

      Week 4: Constraints & Data Integrity

       ---  Primary key, foreign key (SQLite foreign key rules)

       ---  UNIQUE, CHECK, NOT NULL

       ---  Default values

       ---  Understanding SQLite’s relaxed type system

       ---  Hands-on mini project: Student Record Manager

📌 Month 2: Intermediate SQLite – Joins, Views & Optimization

      Week 1: Multi-table Operations

       ---  Understanding relationships in SQLite

       ---  JOINS: INNER, LEFT, CROSS

       ---  Mapping foreign key relationships

       ---  Self-joins and derived tables

      Week 2: Advanced Querying

       ---  Subqueries (single & multi-row)

       ---  UNION and INTERSECT

       ---  Grouping and HAVING clause

       ---  CASE expressions

      Week 3: Views, Indexes & Virtual Tables

       ---  Creating and managing views

       ---  Understanding and creating indexes

       ---  Query performance improvements

       ---  Introduction to Virtual Tables

       ---  FTS5 Full-text Search (Trending Topic)

      Week 4: Transactions & Error Handling

       ---  COMMIT, ROLLBACK, SAVEPOINT

       ---  Atomicity in SQLite

       ---  Handling errors and constraints

       ---  Practical mini project: Inventory Management System

📌 Month 3: SQLite Integration with Applications

      Week 1: SQLite with Python

       ---  Using sqlite3 module

       ---  CRUD operations via Python

       ---  Parameterized queries

       ---  Building a console-based app

       ---  Project: Expense Tracker

      Week 2: SQLite with JavaScript & Node.js

       ---  Using better-sqlite3 / sqlite3 packages

       ---  Building a small Node.js REST API

       ---  Handling async queries

       ---  Data manipulation through APIs

      Week 3: SQLite in Mobile Development

       ---  Flutter – sqflite plugin

       ---  CRUD operations in Flutter

       ---  Local storage for mobile apps

       ---  React Native – react-native-sqlite-storage

       ---  Offline-first mobile apps (Trending Topic)

      Week 4: SQLite with Web & Desktop Applications

       ---  Using SQLite in Electron.js

       ---  Integrating SQLite in local desktop apps

       ---  Understanding WASM + SQLite (WebAssembly version – Trending Topic)

📌 Month 4: Advanced Topics, Security & Final Project

      Week 1: Database Design & Normalization

       ---  Normal forms (1NF, 2NF, 3NF, BCNF)

       ---  Creating efficient schemas

       ---  Denormalization strategies

       ---  Handling large datasets in SQLite

      Week 2: Backup, Security & Encryption

       ---  Backup strategies and VACUUM

       ---  Understanding SQLite WAL mode

       ---  Database encryption (SQLCipher)

       ---  Handling file-level permissions

       ---  Data corruption handling

      Week 3: Performance Optimization

       ---  Query profiling & EXPLAIN

       ---  Index tuning

       ---  Avoiding slow queries

       ---  SQLite memory & cache optimization

       ---  Using triggers for automation

      Week 4: Final Project Development

       ---  Students build any one complete application using SQLite, such as:

       ---  Mobile app with offline storage

       ---  Inventory or billing system

       ---  School/office record system

       ---  Personal finance or budget manager

       ---  Desktop app with local database

              Includes:

              ---- Complete database schema design

              ---- CRUD operations

              ---- Search, filters, and reporting

              ---- Data backup & export feature

              ---- Documentation & presentation

✅ Final Deliverables

       ---  Complete SQLite project

       ---  Database design documentation

       ---  SQL scripts

       ---  Source code (Python/Node.js/Flutter/React Native)

       ---  Certificate of Completion from Computer Park