Created
June 17, 2021 23:32
-
-
Save colejhudson/88fb4559b3f968e2acc7eb4b5a5c043a to your computer and use it in GitHub Desktop.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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 |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
/* 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 |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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) | |
); |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment