create_tables.sql

CREATE DATABASE UNI_DB;

USE UNI_DB;


-- DROP TABLE IF EXISTS STUDENTS;
CREATE TABLE IF NOT EXISTS STUDENTS
(
	ID VARCHAR(36) PRIMARY KEY,
	FIRST_NAME VARCHAR(200) NOT NULL,
	LAST_NAME VARCHAR(200) NOT NULL,
	EMAIL VARCHAR(200) NOT NULL,
	PHONE VARCHAR(20) NOT NULL,
	COURSE INT,
	EDUCATIONAL_DEGREE VARCHAR(20),
	SPECIALITY VARCHAR(20),
	ACTIVE BOOLEAN
);

ALTER TABLE STUDENTS
COMMENT = 'Table to store student information';

ALTER TABLE STUDENTS
MODIFY COLUMN ID VARCHAR(36) COMMENT 'Unique identifier for each student',
MODIFY COLUMN FIRST_NAME VARCHAR(200) NOT NULL COMMENT 'First name of the student',
MODIFY COLUMN LAST_NAME VARCHAR(200) NOT NULL COMMENT 'Last name of the student',
MODIFY COLUMN EMAIL VARCHAR(200) NOT NULL COMMENT 'Email address of the student',
MODIFY COLUMN PHONE VARCHAR(20) NOT NULL COMMENT 'Phone number of the student',
MODIFY COLUMN COURSE INT COMMENT 'Course number the student is enrolled in',
MODIFY COLUMN EDUCATIONAL_DEGREE VARCHAR(20) COMMENT 'Educational degree of the student',
MODIFY COLUMN SPECIALITY VARCHAR(20) COMMENT 'Speciality of the student',
MODIFY COLUMN ACTIVE BOOLEAN COMMENT 'Indicates whether the student is active or not';



-- DROP TABLE IF EXISTS ROOMS;
CREATE TABLE IF NOT EXISTS ROOMS
(
	ID VARCHAR(36) PRIMARY KEY,
	BUILDING VARCHAR(200),
	FLOOR INT,
	NUMBER INT,
	DISPLAY_NAME VARCHAR(200),
	SEATS_NUMBER INT
);

ALTER TABLE ROOMS
COMMENT = 'Table to store room information';

ALTER TABLE ROOMS
MODIFY COLUMN ID VARCHAR(36) COMMENT 'Unique identifier for each room',
MODIFY COLUMN BUILDING VARCHAR(200) COMMENT 'Building where the room is located',
MODIFY COLUMN FLOOR INT COMMENT 'Floor number where the room is located',
MODIFY COLUMN NUMBER INT COMMENT 'Room number',
MODIFY COLUMN DISPLAY_NAME VARCHAR(200) COMMENT 'Display name of the room',
MODIFY COLUMN SEATS_NUMBER INT COMMENT 'Number of seats available in the room';




-- DROP TABLE IF EXISTS COURSES;
CREATE TABLE COURSES
(
	ID VARCHAR(36) PRIMARY KEY,
	COURSE_DISPLAY_SHORT_NAME VARCHAR(36),
	COURSE_DISPLAY_FULL_NAME VARCHAR(200),
	COURSE_DESCRIPTION VARCHAR(500),
	LECTURES_NUM INT,
	PRACTICES_NUM INT
);

ALTER TABLE COURSES
COMMENT = 'Table to store course information';

ALTER TABLE COURSES
MODIFY COLUMN ID VARCHAR(36) COMMENT 'Unique identifier for each course',
MODIFY COLUMN COURSE_DISPLAY_SHORT_NAME VARCHAR(36) COMMENT 'Short name of the course',
MODIFY COLUMN COURSE_DISPLAY_FULL_NAME VARCHAR(200) COMMENT 'Full name of the course',
MODIFY COLUMN COURSE_DESCRIPTION VARCHAR(500) COMMENT 'Description of the course',
MODIFY COLUMN LECTURES_NUM INT COMMENT 'Number of lectures in the course',
MODIFY COLUMN PRACTICES_NUM INT COMMENT 'Number of practice sessions in the course';

-- DROP TABLE IF EXISTS INSTRUCTORS;
CREATE TABLE IF NOT EXISTS INSTRUCTORS
(
	ID VARCHAR(36) PRIMARY KEY,
	FIRST_NAME VARCHAR(200),
	LAST_NAME VARCHAR(200),
	EMAIL VARCHAR(200),
	PHONE VARCHAR(20),
	ACTIVE BOOL
);

ALTER TABLE INSTRUCTORS
COMMENT = 'Table to store instructor information';

ALTER TABLE INSTRUCTORS
MODIFY COLUMN ID VARCHAR(36) COMMENT 'Unique identifier for each instructor',
MODIFY COLUMN FIRST_NAME VARCHAR(200) COMMENT 'First name of the instructor',
MODIFY COLUMN LAST_NAME VARCHAR(200) COMMENT 'Last name of the instructor',
MODIFY COLUMN EMAIL VARCHAR(200) COMMENT 'Email address of the instructor',
MODIFY COLUMN PHONE VARCHAR(20) COMMENT 'Phone number of the instructor',
MODIFY COLUMN ACTIVE BOOL COMMENT 'Indicates whether the instructor is active or not';

-- DROP TABLE LESSONS_SCHEDULE;
CREATE TABLE IF NOT EXISTS LESSONS_SCHEDULE
(
	ID INT PRIMARY KEY,
	START_TIME TIME,
	END_TIME TIME
);

ALTER TABLE LESSONS_SCHEDULE
COMMENT = 'Table to store lessons schedule';

ALTER TABLE LESSONS_SCHEDULE
MODIFY COLUMN ID INT COMMENT 'Unique identifier for each lesson schedule',
MODIFY COLUMN START_TIME TIME COMMENT 'Start time of the lesson',
MODIFY COLUMN END_TIME TIME COMMENT 'End time of the lesson';

-- DROP TABLE INSTRUCTORS_COURSES;
CREATE TABLE IF NOT EXISTS INSTRUCTORS_COURSES(
	INSTRUCTOR_ID VARCHAR(36),
	COURSE_ID VARCHAR(36),
	FOREIGN KEY (INSTRUCTOR_ID) REFERENCES INSTRUCTORS(ID)
	ON DELETE CASCADE,
	FOREIGN KEY (COURSE_ID) REFERENCES COURSES(ID)
	ON DELETE CASCADE
);

ALTER TABLE INSTRUCTORS_COURSES
COMMENT = 'Table to store relationship between instructors and courses';

ALTER TABLE INSTRUCTORS_COURSES
MODIFY COLUMN INSTRUCTOR_ID VARCHAR(36) COMMENT 'Identifier for the instructor',
MODIFY COLUMN COURSE_ID VARCHAR(36) COMMENT 'Identifier for the course';

-- DROP TABLE IF EXISTS STUDENTS_COURSE_GROUPS;
CREATE TABLE IF NOT EXISTS STUDENTS_COURSE_GROUPS(
ID VARCHAR(36) PRIMARY KEY,
COURSE_ID VARCHAR(36)
);

ALTER TABLE STUDENTS_COURSE_GROUPS
COMMENT = 'Table to store student course groups';

ALTER TABLE STUDENTS_COURSE_GROUPS
MODIFY COLUMN ID VARCHAR(36) COMMENT 'Unique identifier for each student course group',
MODIFY COLUMN COURSE_ID VARCHAR(36) COMMENT 'Identifier for the course';

-- DROP TABLE STUDENTS_COURSE_GROUP_STUDENTS;
CREATE TABLE IF NOT EXISTS STUDENTS_COURSE_GROUP_STUDENTS(
	STUDENT_ID VARCHAR(36),
	GROUP_ID VARCHAR(36),
	FOREIGN KEY (STUDENT_ID) REFERENCES STUDENTS(ID)
	ON DELETE CASCADE,
	FOREIGN KEY (GROUP_ID) REFERENCES STUDENTS_COURSE_GROUPS(ID)
	ON DELETE CASCADE
);

ALTER TABLE STUDENTS_COURSE_GROUP_STUDENTS
COMMENT = 'Table to store relationship between students and course groups';

ALTER TABLE STUDENTS_COURSE_GROUP_STUDENTS
MODIFY COLUMN STUDENT_ID VARCHAR(36) COMMENT 'Identifier for the student',
MODIFY COLUMN GROUP_ID VARCHAR(36) COMMENT 'Identifier for the student course group';

-- DROP TABLE IF EXISTS SCHEDULE;
CREATE TABLE IF NOT EXISTS SCHEDULE
(
	ID INT PRIMARY KEY,
	COURSE_ID VARCHAR(36),
	INSTRUCTOR_ID VARCHAR(36),
	STUDENTS_COURSE_GROUP_ID VARCHAR(36),
	WEEK_DAY VARCHAR(20),
	LESSON_SCHEDULE_ID INT,
	ROOM_ID VARCHAR(36),

	FOREIGN KEY (COURSE_ID) REFERENCES COURSES(ID),
	FOREIGN KEY (INSTRUCTOR_ID) REFERENCES INSTRUCTORS(ID),
	FOREIGN KEY (STUDENTS_COURSE_GROUP_ID) REFERENCES STUDENTS_COURSE_GROUPS(ID),
	FOREIGN KEY (LESSON_SCHEDULE_ID) REFERENCES LESSONS_SCHEDULE(ID),
	FOREIGN KEY (ROOM_ID) REFERENCES ROOMS(ID),

	UNIQUE KEY SCHEDULE_UNIQUE_KEY (COURSE_ID, INSTRUCTOR_ID, STUDENTS_COURSE_GROUP_ID, ROOM_ID)

);


ALTER TABLE SCHEDULE
COMMENT = 'Table to store schedule information';

ALTER TABLE SCHEDULE
MODIFY COLUMN ID INT COMMENT 'Unique identifier for each schedule entry',
MODIFY COLUMN COURSE_ID VARCHAR(36) COMMENT 'Identifier for the course',
MODIFY COLUMN INSTRUCTOR_ID VARCHAR(36) COMMENT 'Identifier for the instructor',
MODIFY COLUMN STUDENTS_COURSE_GROUP_ID VARCHAR(36) COMMENT 'Identifier for the student course group',
MODIFY COLUMN WEEK_DAY VARCHAR(20) COMMENT 'Day of the week for the schedule',
MODIFY COLUMN LESSON_SCHEDULE_ID INT COMMENT 'Identifier for the lesson schedule',
MODIFY COLUMN ROOM_ID VARCHAR(36) COMMENT 'Identifier for the room';