66 lines
2.5 KiB
SQL
66 lines
2.5 KiB
SQL
-- 001_initial_schema.sql
|
|
|
|
-- Table for user roles
|
|
CREATE TABLE IF NOT EXISTS `roles` (
|
|
`role_id` INT AUTO_INCREMENT PRIMARY KEY,
|
|
`role_name` VARCHAR(50) NOT NULL UNIQUE
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
|
|
|
-- Insert default roles
|
|
INSERT IGNORE INTO `roles` (`role_name`) VALUES ('student'), ('instructor'), ('admin');
|
|
|
|
-- Table for users
|
|
CREATE TABLE IF NOT EXISTS `users` (
|
|
`user_id` INT AUTO_INCREMENT PRIMARY KEY,
|
|
`username` VARCHAR(255) NOT NULL UNIQUE,
|
|
`password_hash` VARCHAR(255) NOT NULL,
|
|
`email` VARCHAR(255) NOT NULL UNIQUE,
|
|
`role_id` INT,
|
|
`created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
FOREIGN KEY (`role_id`) REFERENCES `roles`(`role_id`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
|
|
|
-- Table for courses
|
|
CREATE TABLE IF NOT EXISTS `courses` (
|
|
`course_id` INT AUTO_INCREMENT PRIMARY KEY,
|
|
`title` VARCHAR(255) NOT NULL,
|
|
`description` TEXT,
|
|
`instructor_id` INT,
|
|
`created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
FOREIGN KEY (`instructor_id`) REFERENCES `users`(`user_id`) ON DELETE SET NULL
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
|
|
|
-- Table for enrollments
|
|
CREATE TABLE IF NOT EXISTS `enrollments` (
|
|
`enrollment_id` INT AUTO_INCREMENT PRIMARY KEY,
|
|
`student_id` INT,
|
|
`course_id` INT,
|
|
`enrollment_date` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
FOREIGN KEY (`student_id`) REFERENCES `users`(`user_id`) ON DELETE CASCADE,
|
|
FOREIGN KEY (`course_id`) REFERENCES `courses`(`course_id`) ON DELETE CASCADE,
|
|
UNIQUE KEY `student_course_unique` (`student_id`, `course_id`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
|
|
|
-- Table for discussion posts
|
|
CREATE TABLE IF NOT EXISTS `discussion_posts` (
|
|
`post_id` INT AUTO_INCREMENT PRIMARY KEY,
|
|
`course_id` INT NOT NULL,
|
|
`user_id` INT NOT NULL,
|
|
`parent_post_id` INT NULL,
|
|
`post_content` TEXT NOT NULL,
|
|
`created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
FOREIGN KEY (`course_id`) REFERENCES `courses`(`course_id`) ON DELETE CASCADE,
|
|
FOREIGN KEY (`user_id`) REFERENCES `users`(`user_id`) ON DELETE CASCADE,
|
|
FOREIGN KEY (`parent_post_id`) REFERENCES `discussion_posts`(`post_id`) ON DELETE CASCADE
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
|
|
|
-- Table for grades
|
|
CREATE TABLE IF NOT EXISTS `grades` (
|
|
`grade_id` INT AUTO_INCREMENT PRIMARY KEY,
|
|
`enrollment_id` INT NOT NULL,
|
|
`grade` DECIMAL(5, 2) NOT NULL,
|
|
`comments` TEXT,
|
|
`assessment_date` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
FOREIGN KEY (`enrollment_id`) REFERENCES `enrollments`(`enrollment_id`) ON DELETE CASCADE
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|