models.rb

require 'active_record'

ActiveRecord::Base.establish_connection(
	:adapter => "sqlite3",
	:database => "./tables.db"
)

class Term < ActiveRecord::Base
	has_many :courses
	has_many :sections
end

class Subject < ActiveRecord::Base
	has_many :courses
	has_many :sections
end

class Course < ActiveRecord::Base
  belongs_to :term
  belongs_to :subject
end

class Section < ActiveRecord::Base
  belongs_to :course
	has_many :instructors
	has_many :section_schedules
end

class SectionSchedule < ActiveRecord::Base
	belongs_to :section
end

class Instructor < ActiveRecord::Base
end

class SectionInstructorAssociation < ActiveRecord::Base
	belongs_to :section
	belongs_to :instructor
end

queries.sql

/* 1. List all classes in a given term. */
SELECT section_number
FROM classes
INNER JOIN (
	SELECT id, period
	FROM terms
	WHERE period = ?
) as ts 
ON ts.id == classes.term_id

/* 2. Which days is a given instructor teaching in a given term? */
/* Select only the days associated to the given classes. */
SELECT days_of_week
FROM classes
INNER JOIN (
	/* Select only the classes associated to the relevent 
		 instructor. */
	SELECT class_id
	FROM class_to_instructors c2i 
	INNER JOIN (
		/* Select only the instructors we care about. */
		SELECT id
		FROM instructors as i 
		WHERE i.first_name = ? AND i.last_name = ? 
	) as instructors 
	ON instructors.id = c2i.instructor_id
) AS matched_class_ids
ON matched_class_ids.id = classes.id

/* 3. How many hours a week does a class require? */
SELECT
	(monday + tuesday + wednesday + thursday + friday + saturday + sunday) as days,
	(end_time - start_time) as duration,
	days * duration as time
FROM class_schedules
INNER JOIN (
	SELECT id
	FROM classes
	WHERE classes.section_number = ?
) as matched_classes 
ON class_schedules.class_id = matched_classes.id 

tables.sql

CREATE TABLE IF NOT EXISTS terms(
	id INTEGER PRIMARY KEY,
	semester TEXT,
	year INTEGER
);

CREATE TABLE IF NOT EXISTS subjects(
	id INTEGER PRIMARY KEY,
	name TEXT,
	mnemonic TEXT
);

CREATE TABLE IF NOT EXISTS courses(
	id INTEGER PRIMARY KEY,
	name TEXT,
	code INTEGER,
	term_id INTEGER,
	subject_id INTEGER,
	FOREIGN KEY(term_id) REFERENCES terms(id),
	FOREIGN KEY(subject_id) REFERENCES subjects(id)
);

CREATE TABLE IF NOT EXISTS sections(
	id INTEGER PRIMARY KEY,
	section_number TEXT,
	room_number TEXT,
	units FLOAT,
	status TEXT,
	seat_capacity INTEGER,
	seats_available INTEGER,
	start_date DATETIME,
	end_date DATETIME,
	final_exam_date DATETIME,

	term_id INTEGER,
	subject_id INTEGER,
	course_id INTEGER,
	location_id INTEGER,
	FOREIGN KEY(term_id) REFERENCES terms(id),
	FOREIGN KEY(subject_id) REFERENCES subjects(id)
	FOREIGN KEY(course_id) REFERENCES courses(id)
	FOREIGN KEY(location_id) REFERENCES locations(id)
);

CREATE TABLE IF NOT EXISTS locations(
	id INTEGER PRIMARY KEY,
	name TEXT
);

CREATE TABLE IF NOT EXISTS section_schedules(
	id INTEGER PRIMARY KEY,

	start_time DATETIME,
	end_time DATETIME,

	monday BOOLEAN,
	tuesday BOOLEAN,
	wednesday BOOLEAN,
	thursday BOOLEAN,
	friday BOOLEAN,
	saturday BOOLEAN,
	sunday BOOLEAN,

	section_id INTEGER,
	FOREIGN KEY(section_id) REFERENCES sections(id)
);

CREATE TABLE IF NOT EXISTS instructors(
	id INTEGER PRIMARY KEY,
	first_name TEXT,
	last_name TEXT,
	email TEXT
);

CREATE TABLE IF NOT EXISTS section_instructor_associations(
	section_id,
	instructor_id,
	FOREIGN KEY(section_id) REFERENCES sections(id),
	FOREIGN KEY(instructor_id) REFERENCES instructors(id)
);