HEX
Server: Apache/2.4.41 (Ubuntu)
System: Linux vmi1674223.contaboserver.net 5.4.0-182-generic #202-Ubuntu SMP Fri Apr 26 12:29:36 UTC 2024 x86_64
User: root (0)
PHP: 7.4.3-4ubuntu2.22
Disabled: pcntl_alarm,pcntl_fork,pcntl_waitpid,pcntl_wait,pcntl_wifexited,pcntl_wifstopped,pcntl_wifsignaled,pcntl_wifcontinued,pcntl_wexitstatus,pcntl_wtermsig,pcntl_wstopsig,pcntl_signal,pcntl_signal_get_handler,pcntl_signal_dispatch,pcntl_get_last_error,pcntl_strerror,pcntl_sigprocmask,pcntl_sigwaitinfo,pcntl_sigtimedwait,pcntl_exec,pcntl_getpriority,pcntl_setpriority,pcntl_async_signals,pcntl_unshare,
$ pwd: /home/evaluation-leave/Schema/
Upload Files
File: //home/evaluation-leave/Schema/DDL001.sql
-- CREATING TABLES FOR DATABASE EVALUATION

CREATE TABLE SESSIONS (
id bigint (20) NOT NULL auto_increment PRIMARY KEY,
name varchar (100),
createdAt timestamp default current_timestamp,
created_by bigint (20) default 1,
updatedAt timestamp null,
updated_by bigint(20) null
);
-- CREATE INDEX idx_session ON SESSIONS (id);

CREATE TABLE ROLES (
id bigint (20) not null auto_increment primary key,
name varchar (50) not null unique,
createdAt timestamp default current_timestamp,
created_by bigint (20) default 1,
updatedAt timestamp null,
updated_by bigint(20) null
);
-- CREATE INDEX idx_roles ON ROLES (id);

CREATE TABLE USERS (
id bigint (20) NOT NULL auto_increment PRIMARY KEY,
fname varchar (100) not null,
lname varchar (100) not null,
email varchar (100) not null,
password varchar(200) not null,
roleid bigint(20) not null,
active_yn varchar(1) default 0,
createdAt timestamp default current_timestamp,
created_by bigint (20) default 1,
updatedAt timestamp null,
updated_by bigint(20) null,
FOREIGN KEY (roleid) REFERENCES ROLES(id)
);
-- CREATE INDEX idx_Users ON USERS(id);
CREATE INDEX idx_Users_Role ON ROLES (id);

CREATE TABLE FACULTY (
id bigint(20) not null auto_increment primary key,
name varchar(100) not null unique,
createdAt timestamp default current_timestamp,
created_by bigint (20) default 1,
updatedAt timestamp null,
updated_by bigint(20) null
);
-- CREATE INDEX idx_Faculty ON FACULTY (id);

CREATE TABLE SCORE (
id bigint(20) not null auto_increment primary key,
name varchar(100) not null unique,
value int(10) default 0 not null,
createdAt timestamp default current_timestamp,
created_by bigint (20) default 1,
updatedAt timestamp null,
updated_by bigint(20) null
);
-- CREATE INDEX idx_Score ON SCORE (id);

CREATE TABLE EV_MATRIX (
id bigint(20) not null auto_increment primary key,
name varchar(100) not null unique,
description text,
createdAt timestamp default current_timestamp,
created_by bigint (20) default 1,
updatedAt timestamp null,
updated_by bigint(20) null
);


CREATE TABLE COURSE_EVALUATION (
id bigint(20) not null auto_increment primary key,
userId  bigint(20) not null,
matrixId bigint(20) not null,
sessionId bigint(20) not null,
course_class_ontime int(20) not null,
course_consultation_time int(20) not null,
course_hosted_guest int(20) not null,
course_instructional_tech int(20) not null,
course_appropriate_design int(20) not null,
course_multimedia_type int(20) not null,
course_quality_instructional int(20) not null,
course_updated_record int(20) not null,
course_draft_exam int(20) not null,
course_standard_exam int(20) not null,
course_administer_exams int(20) not null,
course_mark_script int(20) not null,
course_process_grades int(20) not null,
course_attend_school_meetings int(20) not null,
course_support_school_activity int(20) not null,
-- HOD
course_class_ontime_h int(20) not null,
course_consultation_time_h int(20) not null,
course_hosted_guest_h int(20) not null,
course_instructional_tech_h int(20) not null,
course_appropriate_design_h int(20) not null,
course_multimedia_type_h int(20) not null,
course_quality_instructional_h int(20) not null,
course_updated_record_h int(20) not null,
course_draft_exam_h int(20) not null,
course_standard_exam_h int(20) not null,
course_administer_exams_h int(20) not null,
course_mark_script_h int(20) not null,
course_process_grades_h int(20) not null,
course_attend_school_meetings_h int(20) not null,
course_support_school_activity_h int(20) not null,
createdAt timestamp default current_timestamp,
created_by bigint (20) default 1,
updatedAt timestamp null,
updated_by bigint(20) null,
FOREIGN KEY (userId) REFERENCES USERS(id),
FOREIGN KEY (matrixId) REFERENCES EV_MATRIX(id),
FOREIGN KEY (sessionId) REFERENCES SESSIONS(id),
FOREIGN KEY (created_by) REFERENCES USERS(id),
FOREIGN KEY (updated_by) REFERENCES USERS(id)
);
CREATE INDEX idx_Course_Evaluation_Users ON USERS (id);

alter table course_evaluation
modify column course_class_ontime_h int(20) null,
modify column course_consultation_time_h int(20) null,
modify column course_hosted_guest_h int(20) null,
modify column course_instructional_tech_h int(20) null,
modify column course_appropriate_design_h int(20)  null,
modify column course_multimedia_type_h int(20) null,
modify column course_quality_instructional_h int(20) null,
modify column course_updated_record_h int(20) null,
modify column course_draft_exam_h int(20) null,
modify column course_standard_exam_h int(20) null,
modify column course_administer_exams_h int(20) null,
modify column course_mark_script_h int(20) null,
modify column course_process_grades_h int(20) null,
modify column course_attend_school_meetings_h int(20) null,
modify column course_support_school_activity_h int(20) null;


CREATE TABLE SCHOLAR_EVALUATION (
id bigint(20) not null auto_increment primary key,
userId  bigint(20) not null,
matrixId bigint(20) not null,
sessionId bigint(20) not null,
schoolar_onepeer_review int(20) not null,
schoolar_decumented_research int(20) not null,
schoolar_attended_nonru int(20) not null,
schoolar_presented_paper int(20) not null,
schoolar_served_facilitator int(20) not null,
-- HOD
schoolar_onepeer_review_h int(20) not null,
schoolar_decumented_research_h int(20) not null,
schoolar_attended_nonru_h int(20) not null,
schoolar_presented_paper_h int(20) not null,
schoolar_served_facilitator_h int(20) not null, 
createdAt timestamp default current_timestamp,
created_by bigint (20) default 1,
updatedAt timestamp null,
updated_by bigint(20) null,
FOREIGN KEY (userId) REFERENCES USERS(id),
FOREIGN KEY (matrixId) REFERENCES EV_MATRIX(id),
FOREIGN KEY (sessionId) REFERENCES SESSIONS(id),
FOREIGN KEY (created_by) REFERENCES USERS(id),
FOREIGN KEY (updated_by) REFERENCES USERS(id)
);
CREATE INDEX idx_Scholar_Evaluation_Users ON USERS (id);

alter table scholar_evaluation
modify column schoolar_onepeer_review_h int(20)  null,
modify column schoolar_decumented_research_h int(20)  null,
modify column schoolar_attended_nonru_h int(20)  null,
modify column schoolar_presented_paper_h int(20) null,
modify column schoolar_served_facilitator_h int(20)  null;


CREATE TABLE GRANTS_EVALUATION (
id bigint(20) not null auto_increment primary key,
userId  bigint(20) not null,
matrixId bigint(20) not null,
sessionId bigint(20) not null,
grants_submited int(20) not null,
grants_earned int(20) not null,
grants_activity_participated int(20) not null,
grants_innovation int(20) not null,
-- HOD
createdAt timestamp default current_timestamp,
created_by bigint (20) default 1,
updatedAt timestamp null,
updated_by bigint(20) null,
FOREIGN KEY (userId) REFERENCES USERS(id),
FOREIGN KEY (matrixId) REFERENCES EV_MATRIX(id),
FOREIGN KEY (sessionId) REFERENCES SESSIONS(id),
FOREIGN KEY (created_by) REFERENCES USERS(id),
FOREIGN KEY (updated_by) REFERENCES USERS(id)
);

CREATE INDEX idx_Grant_Evaluation_Users ON USERS (id);

alter table grants_evaluation
modify column grants_submited_h int(20) null,
modify column grants_earned_h int(20) null,
modify column grants_activity_participated_h int(20)  null,
modify column grants_innovation_h int(20) null;


CREATE TABLE SERVICE_EVALUATION (
id bigint(20) not null auto_increment primary key,
userId  bigint(20) not null,
matrixId bigint(20) not null,
sessionId bigint(20) not null,
service_university_commitee int(20) not null,
service_student_extra_carricular int(20) not null,
service_personal_engagement int(20) not null,
created_by bigint (20) default 1,
updatedAt timestamp null,
updated_by bigint(20) null,
FOREIGN KEY (userId) REFERENCES USERS(id),
FOREIGN KEY (matrixId) REFERENCES EV_MATRIX(id),
FOREIGN KEY (sessionId) REFERENCES SESSIONS(id),
FOREIGN KEY (created_by) REFERENCES USERS(id),
FOREIGN KEY (updated_by) REFERENCES USERS(id)
);
CREATE INDEX idx_Service_Evaluation_Users ON USERS (id);

alter table service_evaluation
modify column service_university_commitee_h int(20) null,
modify column service_student_extra_carricular_h int(20) null,
modify column service_personal_engagement_h int(20) null;

CREATE TABLE PROFESSIONALISM_EVALUATION (
id bigint(20) not null auto_increment primary key,
userId  bigint(20) not null,
matrixId bigint(20) not null,
sessionId bigint(20) not null,
proffesionalism_memeber_board int(20) not null,
proffesionalism_exhibits_respect int(20) not null,
proffesionalism_student_respect int(20) not null,
proffesionalism_initiative int(20) not null,
proffesionalism_manage_emotions int(20) not null,
proffesionalism_continued_developement int(20) not null,
proffesionalism_complete_tasks int(20) not null,
--  HOD Section
created_by bigint (20) default 1,
updatedAt timestamp null,
updated_by bigint(20) null,
FOREIGN KEY (userId) REFERENCES USERS(id),
FOREIGN KEY (matrixId) REFERENCES EV_MATRIX(id),
FOREIGN KEY (sessionId) REFERENCES SESSIONS(id),
FOREIGN KEY (created_by) REFERENCES USERS(id),
FOREIGN KEY (updated_by) REFERENCES USERS(id)
);
CREATE INDEX idx_Proffesionalism_Evaluation_Users ON USERS (id);

alter table professionalism_evaluation 
modify column proffesionalism_memeber_board_h int(20) null,
modify column proffesionalism_exhibits_respect_h int(20) null,
modify column proffesionalism_student_respect_h int(20)  null,
modify column proffesionalism_initiative_h int(20)  null,
modify column proffesionalism_manage_emotions_h int(20) null,
modify column proffesionalism_continued_developement_h int(20) null,
modify column proffesionalism_complete_tasks_h int(20) null;

CREATE TABLE EVALUATION (
    id bigint(20) NOT NULL AUTO_INCREMENT PRIMARY KEY,
    user_id bigint(20) NOT NULL,
    hod_id bigint(20),
    matrix_id bigint(20) NOT NULL,
    session_id bigint(20) NOT NULL,
    course_id bigint(20) NOT NULL,
    scholar_id bigint(20) NOT NULL,
    grants_id bigint(20) NOT NULL,
    service_id bigint(20) NOT NULL,
    professionalism_id bigint(20) NOT NULL,
    self_rating int(20) NOT NULL,
    supervisor_rating int(20) NOT NULL,
    self_comment text,
    supervisor_comment text,
    supervisor_recommendation text,
    dvc_recommendation text,
    vc_recommendation text,
    created_at timestamp DEFAULT CURRENT_TIMESTAMP,
    created_by bigint(20) DEFAULT 1,
    updated_at timestamp NULL,
    updated_by bigint(20) NULL,
    FOREIGN KEY (user_id) REFERENCES USERS(id),
    -- FOREIGN KEY (hod_id) REFERENCES USERS(id),-- 
    FOREIGN KEY (course_id) REFERENCES COURSE_EVALUATION(id),
    FOREIGN KEY (scholar_id) REFERENCES SCHOLAR_EVALUATION(id),
    FOREIGN KEY (grants_id) REFERENCES GRANTS_EVALUATION(id),
    FOREIGN KEY (service_id) REFERENCES SERVICE_EVALUATION(id),
    FOREIGN KEY (professionalism_id) REFERENCES PROFESSIONALISM_EVALUATION(id),
    FOREIGN KEY (matrix_id) REFERENCES EV_MATRIX(id),
    FOREIGN KEY (session_id) REFERENCES SESSIONS(id),
    FOREIGN KEY (created_by) REFERENCES USERS(id),
    FOREIGN KEY (updated_by) REFERENCES USERS(id)
);

CREATE INDEX idxx_Evaluation_Users ON USERS (id);
CREATE INDEX idx_Evaluation_Sessions ON SESSIONS (id);
CREATE INDEX idx_Evaluation_Matrix ON EV_MATRIX (id);
@ Telegram