-- create tables create table ch22_acad_cal_umb ( id number generated by default on null as identity constraint ch22_acad_cal_umb_id_pk primary key, acad_year number, acad_date date, acad_status varchar2(1 char) constraint ch22_acad_cal_u_acad_status_ck check (acad_status in ('Y','N')) ) ; create table ch22_department ( id number generated by default on null as identity constraint ch22_department_id_pk primary key, department_description varchar2(300 char) not null ) ; create table ch22_location ( id number generated by default on null as identity constraint ch22_location_id_pk primary key, location_description varchar2(512 char) not null, location_room varchar2(10 char) default 'R1' not null, location_url varchar2(512 char) default 'HTTPS://BEEAPEX.EU/BBB' not null, place varchar2(1 char) default 'I' constraint ch22_location_place_ck check (place in ('R','I','B')) ) ; create table ch22_teacher ( id number generated by default on null as identity constraint ch22_teacher_id_pk primary key, firstname varchar2(50 char) not null, lastname varchar2(50 char) not null, email varchar2(50 char) not null, pref_location_id number constraint ch22_teacher_pref_location_fk references ch22_location on delete cascade, pref1st_dh date ) ; -- table index create index ch22_teacher_i1 on ch22_teacher (pref_location_id); create table ch22_teacher_dept ( id number generated by default on null as identity constraint ch22_teacher_dept_id_pk primary key, teacher_id number constraint ch22_teacher_de_teacher_id_fk references ch22_teacher on delete cascade, dept_id number constraint ch22_teacher_dept_dept_id_fk references ch22_department on delete cascade, acad_year number ) ; -- table index create index ch22_teacher_dept_i1 on ch22_teacher_dept (dept_id); create index ch22_teacher_dept_i132 on ch22_teacher_dept (teacher_id); create table ch22_student ( id number generated by default on null as identity constraint ch22_student_id_pk primary key, firstname varchar2(50 char) not null, lastname varchar2(50 char) not null, email varchar2(255 char) not null, lingua varchar2(50 char) not null ) ; create table ch22_student_dept ( id number generated by default on null as identity constraint ch22_student_dept_id_pk primary key, student_id number constraint ch22_student_de_student_id_fk references ch22_student on delete cascade, dept_id number constraint ch22_student_dept_dept_id_fk references ch22_department on delete cascade, acad_year number ) ; -- table index create index ch22_student_dept_i1 on ch22_student_dept (dept_id); create index ch22_student_dept_i192 on ch22_student_dept (student_id); create table ch22_purpose ( id number generated by default on null as identity constraint ch22_purpose_id_pk primary key, purpose_description varchar2(512 char), duration_minutes number default '15' constraint ch22_purpose_duration_minut_ck check (duration_minutes in (10,15,20,25,30,45,60)) ) ; create table ch22_duty_hour ( id number generated by default on null as identity constraint ch22_duty_hour_id_pk primary key, teacher_id number constraint ch22_duty_hour_teacher_id_fk references ch22_teacher on delete cascade, location_id number constraint ch22_duty_hour_location_id_fk references ch22_location on delete cascade, start_date date not null, stop_date date not null, modif_date date, dh_status varchar2(50 char) ) ; -- table index create index ch22_duty_hour_i1 on ch22_duty_hour (location_id); create index ch22_duty_hour_i252 on ch22_duty_hour (teacher_id); create table ch22_dh_slot ( id number generated by default on null as identity constraint ch22_dh_slot_id_pk primary key, slot_id number constraint ch22_dh_slot_slot_id_fk references ch22_duty_hour on delete cascade, student_id number constraint ch22_dh_slot_student_id_fk references ch22_student on delete cascade, purpose_id number constraint ch22_dh_slot_purpose_id_fk references ch22_purpose on delete cascade, estim_start date, estim_stop date, modif_date date, dhslot_status varchar2(50 char) ) ; -- table index create index ch22_dh_slot_i1 on ch22_dh_slot (purpose_id); create index ch22_dh_slot_i312 on ch22_dh_slot (slot_id); create index ch22_dh_slot_i323 on ch22_dh_slot (student_id); -- load data -- Generated by Quick SQL Monday May 01, 2023 08:57:49 /* ch22_acad_cal_umb acad_year num acad_date date acad_status vc1 /check y n ch22_department department_description vc300 /nn ch22_location location_description vc512 /nn location_room vc10 /nn /default R1 location_url vc512 /nn /default https://beeapex.eu/bbb place vc1 /check R,I,B /default I ch22_teacher firstname vc50 /nn lastname vc50 /nn email vc50 /nn pref_location_id num /fk ch22_location pref1st_dh date ch22_teacher_dept teacher_id num /fk ch22_teacher dept_id num /fk ch22_department acad_year num ch22_student firstname vc50 /nn lastname vc50 /nn email vc255 /nn lingua vc50 /nn ch22_student_dept student_id num /fk ch22_student dept_id num /fk ch22_department acad_year num ch22_purpose purpose_description vc512 duration_minutes num /check 10 15 20 25 30 45 60 /default 15 ch22_duty_hour teacher_id /fk ch22_teacher location_id /fk ch22_location start_date date /nn stop_date date /nn modif_date date dh_status vc50 ch22_dh_slot slot_id /fk ch22_duty_hour student_id /fk ch22_student purpose_id /fk ch22_purpose estim_start date estim_stop date modif_date date dhslot_status vc50 # settings = { semantics: "CHAR", language: "EN", APEX: true } */