database/setup_db.py

import sqlite3
from faker import Faker

def setup_database():
    """
    Initializes and populates the SQLite database with synthetic data.
    """
    conn = sqlite3.connect('database/university.db')
    cursor = conn.cursor()

    # Create tables
    cursor.execute('''
    CREATE TABLE IF NOT EXISTS students (
        id INTEGER PRIMARY KEY,
        name TEXT NOT NULL,
        email TEXT NOT NULL UNIQUE
    )
    ''')

    cursor.execute('''
    CREATE TABLE IF NOT EXISTS faculty (
        id INTEGER PRIMARY KEY,
        name TEXT NOT NULL,
        email TEXT NOT NULL UNIQUE,
        department TEXT NOT NULL
    )
    ''')

    cursor.execute('''
    CREATE TABLE IF NOT EXISTS courses (
        id INTEGER PRIMARY KEY,
        name TEXT NOT NULL,
        faculty_id INTEGER,
        FOREIGN KEY (faculty_id) REFERENCES faculty (id)
    )
    ''')

    cursor.execute('''
    CREATE TABLE IF NOT EXISTS enrollments (
        student_id INTEGER,
        course_id INTEGER,
        PRIMARY KEY (student_id, course_id),
        FOREIGN KEY (student_id) REFERENCES students (id),
        FOREIGN KEY (course_id) REFERENCES courses (id)
    )
    ''')

    # Populate with synthetic data
    fake = Faker()
    
    # Add students
    for _ in range(500):
        try:
            cursor.execute("INSERT INTO students (name, email) VALUES (?, ?)", (fake.name(), fake.email()))
        except sqlite3.IntegrityError:
            pass

    # Add faculty
    for _ in range(100):
        try:
            cursor.execute("INSERT INTO faculty (name, email, department) VALUES (?, ?, ?)", 
                           (fake.name(), fake.email(), fake.job()))
        except sqlite3.IntegrityError:
            pass

    # Add courses
    faculty_ids = [row[0] for row in cursor.execute("SELECT id FROM faculty").fetchall()]
    for _ in range(200):
        cursor.execute("INSERT INTO courses (name, faculty_id) VALUES (?, ?)", 
                       (fake.bs(), fake.random_element(elements=faculty_ids)))

    # Add enrollments
    student_ids = [row[0] for row in cursor.execute("SELECT id FROM students").fetchall()]
    course_ids = [row[0] for row in cursor.execute("SELECT id FROM courses").fetchall()]
    for _ in range(1500):
        try:
            cursor.execute("INSERT INTO enrollments (student_id, course_id) VALUES (?, ?)",
                           (fake.random_element(elements=student_ids), fake.random_element(elements=course_ids)))
        except sqlite3.IntegrityError:
            pass

    conn.commit()
    conn.close()
    print("Database setup complete. 'university.db' created and populated.")

if __name__ == "__main__":
    setup_database()