CREATE DATABASE DbExamOnline;
USE DbExamOnline;

CREATE TABLE Role(
	_id VARCHAR(3) PRIMARY KEY,
    _name VARCHAR(30) NOT NULL,
    _status BIT NOT NULL DEFAULT 1
);

CREATE TABLE User(
	_id VARCHAR(7) PRIMARY KEY,
    _role_id VARCHAR(3) NOT NULL,
    FOREIGN KEY (_role_id) REFERENCES Role(_id),
	_name VARCHAR(30) NOT NULL,
	_user_type BIT,
	_email VARCHAR(100) UNIQUE,
	_password VARCHAR(100) NOT NULL,
	_status BIT NOT NULL DEFAULT 1
);

CREATE TABLE Class(
	_id VARCHAR(7) PRIMARY KEY,
	_description VARCHAR(100),
	_user_id VARCHAR(7) NOT NULL,
	FOREIGN KEY (_user_id) REFERENCES User(_id),
	_status BIT NOT NULL DEFAULT 1
);

CREATE TABLE Student(
	_id VARCHAR(7) PRIMARY KEY,
	_name VARCHAR(30) NOT NULL,
	_email VARCHAR(100) UNIQUE NOT NULL,
	_password VARCHAR(100) NOT NULL,
	_class_id VARCHAR(7) NOT NULL,
	FOREIGN KEY (_class_id) REFERENCES Class(_id),
	_status BIT NOT NULL DEFAULT 1
);

CREATE TABLE Course(
	_id VARCHAR(7) PRIMARY KEY,
	_name VARCHAR(100),
    _user_id VARCHAR(7),
    FOREIGN KEY (_user_id) REFERENCES User(_id),
	_description VARCHAR(100),
	_status BIT NOT NULL DEFAULT 1
);

CREATE TABLE StudentCourse(
	_student_id VARCHAR(7),
    FOREIGN KEY (_student_id) REFERENCES Student(_id),
    _course_id VARCHAR(7),
    FOREIGN KEY (_course_id) REFERENCES Course(_id),
    PRIMARY KEY (_student_id, _course_id)
);

CREATE TABLE QuestionType(
	_id VARCHAR(3) PRIMARY KEY,
	_title VARCHAR(20) NOT NULL,
	_status BIT NOT NULL DEFAULT 1
);

CREATE TABLE Question(
	_id VARCHAR(7) PRIMARY KEY,
	_question_type_id VARCHAR(3) NOT NULL,
	FOREIGN KEY (_question_type_id) REFERENCES QuestionType(_id),
	_content VARCHAR(200),
	_point INT NOT NULL,
	_course_id VARCHAR(7) NOT NULL,
	FOREIGN KEY (_course_id) REFERENCES Course(_id),
	_status BIT NOT NULL DEFAULT 1
);

CREATE TABLE Answer(
	_id VARCHAR(10) PRIMARY KEY,
	_content VARCHAR(100) NOT NULL,
    _question_id VARCHAR(7) NOT NULL,
    FOREIGN KEY (_question_id) REFERENCES Question(_id),
	_is_correct BIT NOT NULL DEFAULT 0
);

CREATE TABLE Exam(
	_id VARCHAR(7) PRIMARY KEY,
	_name VARCHAR(100),
    _user_id VARCHAR(7),
    FOREIGN KEY (_user_id) REFERENCES User(_id),
    _num_of_question INT NOT NULL,
	_course_id VARCHAR(7) NOT NULL,
	FOREIGN KEY (_course_id) REFERENCES Course(_id),
	_duration INT NOT NULL,
	_start_time DATETIME
);

CREATE TABLE ExamQuestion(
	_exam_id VARCHAR(7) NOT NULL,
	FOREIGN KEY (_exam_id) REFERENCES Exam(_id),
	_question_id VARCHAR(7) NOT NULL,
	FOREIGN KEY (_question_id) REFERENCES Question(_id),
	PRIMARY KEY (_exam_id, _question_id)
);

CREATE TABLE ExamStudent(
	_exam_id VARCHAR(7) NOT NULL,
	FOREIGN KEY (_exam_id) REFERENCES Exam(_id),
	_student_id VARCHAR(7) NOT NULL,
	FOREIGN KEY (_student_id) REFERENCES Student(_id),
	PRIMARY KEY (_exam_id, _student_id),
	_result INT,
    _start_time DATETIME,
    _end_time DATETIME
);

INSERT INTO QuestionType VALUES ('T01', 'True/False', 1), ('T02', 'Multiple Choice - Single Answer', 1), ('T03', 'Multiple Choice - Multiple Answer', 1);
INSERT INTO Course VALUES ('CSE0001', 'Computer Basic', null, 1)
						,('CSE0002', 'Elemetary Programming', null, 1)
                        ,('CSE0003', 'Database Management', null, 1 );
INSERT INTO Answer VALUES ('A000000001', 'True', 1)
						,('A000000002', 'False', 0);
INSERT INTO Role VALUES ('R01', 'ADMINISTRATOR', 1)
						, ('R02', 'INSTRUCTOR', 1)
                        , ('R03', 'STAFF', 1);
INSERT INTO User VALUES ('U000001', 'Pham Manh Hieu', 'hieupm2096@gmail.com', '123456', 1, 'R01')
						, ('U000002', 'Pham Huu Tri', 'triph@gmail.com', '123456', 1, 'R02')
                        , ('U000003', 'Nguyen Tien Bao', 'baont@gmail.com', '123456', 1, 'R03');
INSERT INTO Class VALUES ('C000001', null, 1, 'U000002');

INSERT INTO Student VALUES ('S000001', 'Pham Manh Hieu', 'hieupm2096@gmail.com', '123456', 'C000001', 1)
						, ('S000002', 'Nguyen Thi Ha', 'hant@gmail.com', '123456', 'C000001', 1);