University Database¶

import datajoint as dj
import numpy as np
from datetime import date

schema = dj.Schema('tutorial_university')

dj.Diagram(schema)

def generate_students(n=500):
    """Generate n student records."""
    fake_name = {'F': fake.name_female, 'M': fake.name_male}
    for student_id in range(1000, 1000 + n):
        sex = random.choice(['F', 'M'])
        name = fake_name[sex]().split()[:2]
        yield {
            'student_id': student_id,
            'first_name': name[0],
            'last_name': name[-1],
            'sex': sex,
            'date_of_birth': fake.date_between(
                start_date='-35y', end_date='-17y'),
            'home_city': fake.city(),
            'home_state': fake.state_abbr()
        }

Student.insert(generate_students(500))
print(f"Inserted {len(Student())} students")

# Departments
Department.insert([
    {'dept': 'CS', 'dept_name': 'Computer Science'},
    {'dept': 'BIOL', 'dept_name': 'Life Sciences'},
    {'dept': 'PHYS', 'dept_name': 'Physics'},
    {'dept': 'MATH', 'dept_name': 'Mathematics'},
])

# Assign majors to ~75% of students
students = Student.keys()
depts = Department.keys()
StudentMajor.insert(
    {
        **s, **random.choice(depts),
        'declare_date': fake.date_between(start_date='-4y')
    }
    for s in students if random.random() < 0.75
)
print(f"{len(StudentMajor())} students declared majors")

# Course catalog
Course.insert([
    ['BIOL', 1010, 'Biology in the 21st Century', 3],
    ['BIOL', 2020, 'Principles of Cell Biology', 3],
    ['BIOL', 2325, 'Human Anatomy', 4],
    ['BIOL', 2420, 'Human Physiology', 4],
    ['PHYS', 2210, 'Physics for Scientists I', 4],
    ['PHYS', 2220, 'Physics for Scientists II', 4],
    ['PHYS', 2060, 'Quantum Mechanics', 3],
    ['MATH', 1210, 'Calculus I', 4],
    ['MATH', 1220, 'Calculus II', 4],
    ['MATH', 2270, 'Linear Algebra', 4],
    ['MATH', 2280, 'Differential Equations', 4],
    ['CS', 1410, 'Intro to Object-Oriented Programming', 4],
    ['CS', 2420, 'Data Structures & Algorithms', 4],
    ['CS', 3500, 'Software Practice', 4],
    ['CS', 3810, 'Computer Organization', 4],
])
print(f"{len(Course())} courses in catalog")

# Academic terms 2020-2024
Term.insert(
    {'term_year': year, 'term': term}
    for year in range(2020, 2025)
    for term in ['Spring', 'Summer', 'Fall']
)

# Create sections for each course-term with 1-3 sections
for course in Course.keys():
    for term in Term.keys():
        for sec in 'abc'[:random.randint(1, 3)]:
            if random.random() < 0.7:  # Not every course every term
                Section.insert1({
                    **course, **term,
                    'section': sec,
                    'auditorium': f"{random.choice('ABCDEF')}"
                                  f"{random.randint(100, 400)}"
                }, skip_duplicates=True)

print(f"{len(Section())} sections created")

# Enroll students in courses
terms = Term.keys()
for student in Student.keys():
    # Each student enrolls over 2-6 random terms
    student_terms = random.sample(terms, k=random.randint(2, 6))
    for term in student_terms:
        # Take 2-4 courses per term
        available = (Section & term).keys()
        if available:
            n_courses = min(random.randint(2, 4), len(available))
            for section in random.sample(available, k=n_courses):
                Enroll.insert1(
                    {**student, **section}, skip_duplicates=True)

print(f"{len(Enroll())} enrollments")

# Assign grades to ~90% of enrollments (some incomplete)
grades = LetterGrade.to_arrays('grade')
# Weight toward B/C range
weights = [5, 8, 10, 15, 12, 10, 15, 10, 5, 5, 5]

for enroll in Enroll.keys():
    if random.random() < 0.9:
        Grade.insert1({**enroll, 'grade': random.choices(grades, weights=weights)[0]})

print(f"{len(Grade())} grades assigned")

# Students from California
Student & {'home_state': 'CA'}

# Female students NOT from California
(Student & {'sex': 'F'}) - {'home_state': 'CA'}

# SQL-style string conditions
Student & "home_state IN ('CA', 'TX', 'NY')"

# OR conditions using a list
Student & [{'home_state': 'CA'}, {'home_state': 'TX'}]

# Students majoring in Computer Science
Student & (StudentMajor & {'dept': 'CS'})

# Students who have NOT taken any Math courses
Student - (Enroll & {'dept': 'MATH'})

# Students with ungraded enrollments (enrolled but no grade yet)
Student & (Enroll - Grade)

# All-A students: have grades AND no non-A grades
all_a = (Student & Grade) - (Grade - {'grade': 'A'})
all_a

# Select specific attributes
Student.proj('first_name', 'last_name')

# Computed attribute: full name
Student.proj(full_name="CONCAT(first_name, ' ', last_name)")

# Calculate age in years
Student.proj('first_name', 'last_name', 
             age='TIMESTAMPDIFF(YEAR, date_of_birth, CURDATE())')

# Keep all attributes plus computed ones with ...
Student.proj(..., age='TIMESTAMPDIFF(YEAR, date_of_birth, CURDATE())')

# Exclude specific attributes with -
Student.proj(..., '-date_of_birth')

# Rename attribute
Student.proj('first_name', family_name='last_name')

# All unique first names
dj.U('first_name') & Student

# All unique home states of enrolled students
dj.U('home_state') & (Student & Enroll)

# Birth years of students in CS courses
dj.U('birth_year') & (
    Student.proj(birth_year='YEAR(date_of_birth)') & (Enroll & {'dept': 'CS'})
)

# Students with their declared majors
Student.proj('first_name', 'last_name') * StudentMajor

# Courses with department names
Course * Department.proj('dept_name')

# Left join: all students, including those without majors (NULL for unmatched)
Student.proj('first_name', 'last_name').join(StudentMajor, left=True)

# Multi-table join: grades with student names and course info
(Student.proj('first_name', 'last_name') 
 * Grade 
 * Course.proj('course_name', 'credits'))

# Number of students per department
Department.aggr(StudentMajor, n_students='COUNT(*)')

# Breakdown by sex per department
Department.aggr(
    StudentMajor * Student,
    n_female="SUM(sex='F')",
    n_male="SUM(sex='M')"
)

# Enrollment counts per course (with course name)
Course.aggr(Enroll, ..., n_enrolled='COUNT(*)')

# Average grade points per course
Course.aggr(
    Grade * LetterGrade,
    'course_name',
    avg_gpa='AVG(points)',
    n_grades='COUNT(*)'
)

# Student GPA: weighted average of grade points by credits
student_gpa = Student.aggr(
    Grade * LetterGrade * Course,
    'first_name', 'last_name',
    total_credits='SUM(credits)',
    gpa='SUM(points * credits) / SUM(credits)'
)
student_gpa

# Top 5 students by GPA (with at least 12 credits)
student_gpa & 'total_credits >= 12' & dj.Top(5, order_by='gpa DESC')

# Students who have taken courses in ALL departments
# (i.e., no department exists where they haven't enrolled)
all_depts = Student - (
    Student.proj() * Department - Enroll.proj('student_id', 'dept')
)
all_depts.proj('first_name', 'last_name')

# Most popular courses (by enrollment) per department
course_enrollment = Course.aggr(Enroll, ..., n='COUNT(*)')

# For each department, find the max enrollment
max_per_dept = Department.aggr(course_enrollment, max_n='MAX(n)')

# Join to find courses matching the max
course_enrollment * max_per_dept & 'n = max_n'

# Grade distribution: count of each grade across all courses
LetterGrade.aggr(Grade, ..., count='COUNT(*)') & 'count > 0'

# Fetch as numpy recarray
data = (Student & {'home_state': 'CA'}).to_arrays()
print(f"Type: {type(data).__name__}, shape: {data.shape}")
data[:3]

# Fetch as list of dicts
(Student & {'home_state': 'CA'}).to_dicts(limit=3)

# Fetch specific attributes as arrays
first_names, last_names = (Student & {'home_state': 'CA'}).to_arrays('first_name', 'last_name')
list(zip(first_names, last_names))[:5]

# Fetch single row with fetch1
student = (Student & {'student_id': 1000}).fetch1()
print(f"{student['first_name']} {student['last_name']} from {student['home_city']}, {student['home_state']}")

# Fetch as pandas DataFrame
(student_gpa & 'total_credits >= 12').to_pandas().sort_values('gpa', ascending=False).head(10)