@startuml
!define Table(name,desc) class name as "desc" << (T,#FFAAAA) >>
!define primary_key(x) <b>x</b>
!define foreign_key(x) <color:purple>x</color>
!define unique(x) <color:green>x</color>
!define not_null(x) <u>x</u>
hide methods
hide stereotypes

' entities
Table(courses, "courses") {
  primary_key(id) BIGINT
  unique(course_number) VARCHAR[255]
  not_null(title) VARCHAR[255]
}
' add unique constaint on courses(course_number, title);

Table(terms, "terms") {
  primary_key(id) BIGINT
  not_null(start_at) DATETIME
  not_null(end_at) DATETIME
}

Table(scheduled_courses, "scheduled_courses") {
  primary_key(id) BIGINT
  not_null(capacity) INTEGER
  foreign_key(term_id) BIGINT
  foreign_key(instructor_id) BIGINT
  foreign_key(course_id) BIGINT
  conference_whiteboard_url VARCHAR[1024]
}

Table(course_prerequisites, "course_prerequisites") {
  foreign_key(course_id) BIGINT
  foreign_key(prerequisite_id) BIGINT
}

Table(course_assistants, "course_assistants") {
  foreign_key(scheduled_course_id) BIGINT
  foreign_key(student_id) BIGINT
  type INTEGER [0 teaching, 1 research]
}

Table(instructors, "instructors") {
  primary_key(id) BIGINT
  not_null(title) VARCHAR[255]
  foreign_key(profile_id) BIGINT
  fax_number VARCHAR[255]
}

Table(students, "students") {
  primary_key(id) BIGINT
  foreign_key(profile_id) BIGINT
  type INTEGER [0 undergraduate, 1 graduate]
  postal_code VARCHAR[255]
  gpa DECIMAL
  years_of_work_experience INTEGER
}

Table(profiles, "profiles") {
  primary_key(id) BIGINT
  first_name VARCHAR[255]
  last_name VARCHAR[255]
  email_address VARCHAR[255]
  phone_number VARCHAR[255]
}

Table(course_enrollments, "course_enrollments") {
  primary_key(id) BIGINT
  foreign_key(student_id) BIGINT
  foreign_key(scheduled_course_id) BIGINT
  foreign_key(authorized_by_instructor_id) BIGINT
  grade DECIMAL
}

Table(disciplines, "disciplines") {
  primary_key(id) BIGINT
  not_null(name) VARCHAR[255]
  foreign_key(disciplines_id) BIGINT [parent discipline]
}

Table(course_disciplines, "course_disciplines") {
  foreign_key(course_id) BIGINT
  foreign_key(discipline_id) BIGINT
}

Table(instructor_disciplines, "instructor_disciplines") {
  foreign_key(instructor_id) BIGINT
  foreign_key(discipline_id) BIGINT
}

' relationships
disciplines --> disciplines : "belongs to"
course_enrollments --> scheduled_courses : "belongs to"
course_enrollments --> students : "belongs to"
students --> course_enrollments : "has many"
course_assistants --> students : "belongs to"
course_assistants --> scheduled_courses : "belongs to"
scheduled_courses --> course_assistants : "has many"
courses --> course_prerequisites : "has many"
scheduled_courses --> courses : "belongs_to"
scheduled_courses --> terms : "belongs_to"
scheduled_courses --> instructors : "belongs to"
instructors --> scheduled_courses : "has many"
courses --> course_disciplines : "has many"
course_disciplines --> disciplines : "belongs to"
instructors --> instructor_disciplines : "has many"
instructor_disciplines --> disciplines : "belongs to"
students --> profiles : "belongs to"
instructors --> profiles : "belongs to"
@enduml