Untitled

USE master;
ALTER DATABASE course_registration SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
DROP DATABASE course_registration;
GO

-- Tạo database và chuyển context
CREATE DATABASE course_registration;
GO

USE course_registration;
GO

/********** Table: faculties **********/
CREATE TABLE faculties (
    faculty_id VARCHAR(20) PRIMARY KEY,
    faculty_name NVARCHAR(100) NULL,
    phone_number VARCHAR(20) NULL,
    email VARCHAR(50) NULL,
    website VARCHAR(50) NULL,
    dean NVARCHAR(50) NULL,
    description NVARCHAR(MAX) NULL,
    created_at DATETIME2 NOT NULL DEFAULT (GETDATE()),
    updated_at DATETIME2 NOT NULL DEFAULT (GETDATE())
);
GO

/********** Table: majors **********/
CREATE TABLE majors (
    major_id VARCHAR(20) PRIMARY KEY,
    major_name NVARCHAR(40) NULL,
    faculty_id VARCHAR(20) NOT NULL,
    degree_level NVARCHAR(20) NULL,
    duration_years INT NULL,
    description NVARCHAR(MAX) NULL,
    created_at DATETIME2 NOT NULL DEFAULT (GETDATE()),
    updated_at DATETIME2 NOT NULL DEFAULT (GETDATE()),
    CONSTRAINT FK_majors_faculties FOREIGN KEY (faculty_id)
        REFERENCES faculties(faculty_id)
);
ALTER TABLE majors
ADD CONSTRAINT CHK_majors_degree_level CHECK (
    degree_level IN (N'Cử nhân', N'Kỹ sư')
);
GO

/********** Table: users **********/
CREATE TABLE users (
    user_id VARCHAR(15) PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    password VARCHAR(50) NOT NULL,
    full_name NVARCHAR(70) NOT NULL,
    email VARCHAR(100) NOT NULL UNIQUE,
    role VARCHAR(10) NOT NULL,
    created_at DATETIME2 NOT NULL DEFAULT (GETDATE()),
    updated_at DATETIME2 NOT NULL DEFAULT (GETDATE())
);
ALTER TABLE users
ADD CONSTRAINT CHK_users_role CHECK (role IN (N'STUDENT', N'TEACHER', N'ADMIN'));
GO

/********** Table: students **********/
CREATE TABLE students (
    student_id VARCHAR(15) PRIMARY KEY,
    class VARCHAR(20) NOT NULL,
    major_id VARCHAR(20) NOT NULL,
    status NVARCHAR(20) NOT NULL,
    created_at DATETIME2 NOT NULL DEFAULT (GETDATE()),
    updated_at DATETIME2 NOT NULL DEFAULT (GETDATE()),
    CONSTRAINT FK_students_users FOREIGN KEY (student_id)
        REFERENCES users(user_id)
        ON DELETE CASCADE,
    CONSTRAINT FK_students_majors FOREIGN KEY (major_id)
        REFERENCES majors(major_id)
        ON DELETE NO ACTION
);
ALTER TABLE students
ADD CONSTRAINT CHK_students_status CHECK (status IN (N'Đang học', N'Nghỉ học'));
GO

/********** Table: teachers **********/
CREATE TABLE teachers (
    teacher_id VARCHAR(15) PRIMARY KEY,
    faculty_id VARCHAR(20) NOT NULL,
	degree NVARCHAR(50),
	experience_years INTEGER,
    created_at DATETIME2 NOT NULL DEFAULT (GETDATE()),
    updated_at DATETIME2 NOT NULL DEFAULT (GETDATE()),
    CONSTRAINT FK_teachers_users FOREIGN KEY (teacher_id)
        REFERENCES users(user_id)
        ON DELETE CASCADE,
    CONSTRAINT FK_teachers_faculties FOREIGN KEY (faculty_id)
        REFERENCES faculties(faculty_id)
        ON DELETE NO ACTION
);
ALTER TABLE students
ADD CONSTRAINT CHK_teachers_status CHECK (status IN (N'Đang học', N'Nghỉ học'));
GO

/********** Table: courses **********/
CREATE TABLE courses (
    course_id VARCHAR(15) PRIMARY KEY,
    course_name NVARCHAR(70) NULL,
    credits INT NULL,
    faculty_id VARCHAR(20) NOT NULL,
    created_at DATETIME2 NOT NULL DEFAULT (GETDATE()),
    updated_at DATETIME2 NOT NULL DEFAULT (GETDATE()),
    CONSTRAINT FK_courses_faculties FOREIGN KEY (faculty_id)
        REFERENCES faculties(faculty_id)
);
GO

/********** Table: rooms **********/
CREATE TABLE rooms (
    room_id VARCHAR(5) PRIMARY KEY,
    capacity INT NULL,
    projector BIT NULL,        -- dùng BIT cho boolean
    airconditioner BIT NULL,   -- dùng BIT cho boolean
    micro_speaker BIT NULL,    -- dùng BIT cho boolean
    created_at DATETIME2 NOT NULL DEFAULT (GETDATE()),
    updated_at DATETIME2 NOT NULL DEFAULT (GETDATE())
);
GO

/********** Table: semesters **********/
CREATE TABLE semesters (
    semester_id VARCHAR(15) PRIMARY KEY,
    term NVARCHAR(50) NULL,
    academic_year VARCHAR(10) NULL,
    start_date DATE NULL,
    end_date DATE NULL,
    created_at DATETIME2 NOT NULL DEFAULT (GETDATE()),
    updated_at DATETIME2 NOT NULL DEFAULT (GETDATE())
);
GO

/********** Table: course_offerings **********/
CREATE TABLE course_offerings (
    course_offering_id VARCHAR(15) PRIMARY KEY,
    course_id VARCHAR(15) NOT NULL,
    major_id VARCHAR(20) NOT NULL,
    instructor NVARCHAR(50) NULL,
    room_id VARCHAR(5) NOT NULL,
    semester_id VARCHAR(15) NOT NULL,
    max_capacity INT NULL,
    current_capacity INT NULL,
    created_at DATETIME2 NOT NULL DEFAULT (GETDATE()),
    updated_at DATETIME2 NOT NULL DEFAULT (GETDATE()),
    CONSTRAINT FK_co_courses FOREIGN KEY (course_id)
        REFERENCES courses(course_id)
        ON DELETE CASCADE,
    CONSTRAINT FK_co_majors FOREIGN KEY (major_id)
        REFERENCES majors(major_id)
        ON DELETE NO ACTION,
    CONSTRAINT FK_co_rooms FOREIGN KEY (room_id)
        REFERENCES rooms(room_id)
        ON DELETE NO ACTION,
    CONSTRAINT FK_co_semesters FOREIGN KEY (semester_id)
        REFERENCES semesters(semester_id)
        ON DELETE NO ACTION,
);
GO

/********** Table: schedules **********/
CREATE TABLE schedules (
    schedule_id VARCHAR(15) PRIMARY KEY,
    day_of_week INT NOT NULL, -- 2: T2 ... 8: CN
    start_time TIME NULL,
    end_time TIME NULL,
    created_at DATETIME2 NOT NULL DEFAULT (GETDATE()),
    updated_at DATETIME2 NOT NULL DEFAULT (GETDATE())
);
GO

/********** Table: course_offerings_schedules **********/
CREATE TABLE course_offerings_schedules (
    id INT IDENTITY(1,1) PRIMARY KEY,
    course_offering_id VARCHAR(15) NOT NULL,
    schedule_id VARCHAR(15) NOT NULL,
    start_date DATE NULL,
    end_date DATE NULL,
    created_at DATETIME2 NOT NULL DEFAULT (GETDATE()),
    updated_at DATETIME2 NOT NULL DEFAULT (GETDATE()),
    CONSTRAINT FK_cos_course_offerings FOREIGN KEY (course_offering_id)
        REFERENCES course_offerings(course_offering_id)
        ON DELETE CASCADE,
    CONSTRAINT FK_cos_schedules FOREIGN KEY (schedule_id)
        REFERENCES schedules(schedule_id)
        ON DELETE CASCADE,
);
GO

/********** Table: registrations **********/
CREATE TABLE registrations (
    registration_id VARCHAR(20) PRIMARY KEY,
    student_id VARCHAR(15) NOT NULL,
    course_offering_id VARCHAR(15) NOT NULL,
    registered_at DATETIME2 NULL,
    status NVARCHAR(20) NULL,
    note NVARCHAR(MAX) NULL,
    created_at DATETIME2 NOT NULL DEFAULT (GETDATE()),
    updated_at DATETIME2 NOT NULL DEFAULT (GETDATE()),
    CONSTRAINT FK_registrations_course_offerings FOREIGN KEY (course_offering_id)
        REFERENCES course_offerings(course_offering_id)
        ON DELETE CASCADE,
    CONSTRAINT FK_registrations_students FOREIGN KEY (student_id)
        REFERENCES students(student_id)
        ON DELETE CASCADE,
);
ALTER TABLE registrations
ADD CONSTRAINT CHK_registrations_status CHECK (status IN (N'Thành công', N'Thất bại'));
GO