LMS Project

Tables (9)

Assignments

  • assign_id — INT
  • course_id — INT — Foreign key → Courses.course_id
  • title — VARCHAR(200) — NOT NULL
  • description — TEXT
  • due_date — DATETIME

CourseContents

  • content_id — INT
  • course_id — INT — Foreign key → Courses.course_id
  • title — VARCHAR(200) — NOT NULL
  • description — TEXT
  • file_path — VARCHAR(255)
  • content_type — ENUM('NOTICE', 'LECTURE_NOTE', 'REFERENCE')
  • created_at — DATETIME

Courses

  • course_id — INT
  • subject_code — VARCHAR(20) — Foreign key → Subjects.subject_code
  • prof_id — VARCHAR(20) — Foreign key → Users.user_id
  • year — YEAR — NOT NULL
  • semester — VARCHAR(20) — NOT NULL
  • section — VARCHAR(10)
  • classroom — VARCHAR(50)
  • schedule — VARCHAR(100)

Departments

  • dept_id — INT
  • dept_name — VARCHAR(100) — NOT NULL

Enrollments

  • enroll_id — INT
  • course_id — INT — Foreign key → Courses.course_id
  • student_id — VARCHAR(20) — Foreign key → Users.user_id
  • grade — VARCHAR(5)

Subjects

  • subject_code — VARCHAR(20)
  • subject_name — VARCHAR(100) — NOT NULL
  • credits — INT
  • category — VARCHAR(50)

Submissions

  • submit_id — INT
  • assign_id — INT — Foreign key → Assignments.assign_id
  • student_id — VARCHAR(20) — Foreign key → Users.user_id
  • content — TEXT
  • file_path — VARCHAR(255)
  • submitted_at — DATETIME
  • score — DECIMAL(5,2)
  • is_graded — BOOLEAN

Syllabuses

  • course_id — INT — Foreign key → Courses.course_id
  • overview — TEXT
  • learning_goal — TEXT
  • main_textbook — TEXT
  • grading_policy — TEXT

Users

  • user_id — VARCHAR(20)
  • name — VARCHAR(50) — NOT NULL
  • email — VARCHAR(100)
  • role — ENUM('STUDENT', 'PROFESSOR', 'ADMIN') — NOT NULL
  • dept_id — INT — Foreign key → Departments.dept_id