1 Scuola247 Read table & column comments as mouse-over tooltips. persons teachers_registry schools classrooms school_years classroom_registry metrics timetables cities Sistema Fk persons_addresses_fk_city persons_addresses ref cities ( city ) city Fk persons_addresses_fk_person persons_addresses ref persons ( person ) person Fk weekly_timetable_fk_classroom weekly_timetables ref classrooms ( classroom ) classroom Fk weekly_timetables_days_fk_teacher weekly_timetables_days ref persons ( teacher -> person ) teacher Fk weekly_timetables_days_fk_subject weekly_timetables_days ref subjects ( subject ) subject Fk weekly_timetables_days_fk_weekly_timetable weekly_timetables_days ref weekly_timetables ( weekly_timetable ) weekly_timetable Fk districts_fk_region districts ref regions ( region ) region Fk cities_fk_district cities ref districts ( district ) district Fk school_years_fk_school school_years ref schools ( school ) school Fk grading_meetings_fk_school_year grading_meetings ref school_years ( school_year ) school_year Fk grading_meetings_valutations_qua_fk_grade grading_meetings_valutations_qua ref grades ( grade ) grade Fk grading_meetings_valutations_qua_fk_grading_meeting_valutation grading_meetings_valutations_qua ref grading_meetings_valutations ( grading_meeting_valutation ) grading_meeting_valutation Fk grading_meetings_valutations_qua_fk_qualification grading_meetings_valutations_qua ref qualifications ( qualification ) qualification Fk grading_meetings_valutations_fk_classroom grading_meetings_valutations ref classrooms ( classroom ) classroom Fk grading_meetings_valutations_fk_grade grading_meetings_valutations ref grades ( grade ) grade Fk grading_meetings_valutations_fk_grading_meeting grading_meetings_valutations ref grading_meetings ( grading_meeting ) grading_meeting Fk grading_meetings_valutations_fk_student grading_meetings_valutations ref persons ( student -> person ) student Fk grading_meetings_valutations_fk_teacher grading_meetings_valutations ref persons ( teacher -> person ) teacher Fk grading_meetings_valutations_fk_subject grading_meetings_valutations ref subjects ( subject ) subject Fk metrics_fk_school metrics ref schools ( school ) school Fk grades_fk_metric grades ref metrics ( metric ) metric Fk grade_types_fk_subject grade_types ref subjects ( subject ) subject Fk communications_media_fk_communication_type communications_media ref communication_types ( communication_type ) communication_type Fk communications_media_fk_person communications_media ref persons ( person ) person Fk communication_types_fk_school communication_types ref schools ( school ) school Fk holidays_fk_school holidays ref schools ( school ) school Fk subjects_fk_school subjects ref schools ( school ) school Fk degrees_fk_school degrees ref schools ( school ) school Fk qualifications_fk_metric qualifications ref metrics ( metric ) metric Fk qualifications_fk_qualification_parent qualifications ref qualifications ( qualification_parent -> qualification ) qualification_parent Fk qualifications_fk_school qualifications ref schools ( school ) school Fk conversations_fk_classroom_student conversations ref classrooms_students ( classroom_student ) classroom_student Fk classrooms_students_fk_classroom classrooms_students ref classrooms ( classroom ) classroom Fk classrooms_students_fk_student classrooms_students ref persons ( student -> person ) student Fk classrooms_fk_degree classrooms ref degrees ( degree ) degree Fk classrooms_fk_school_year classrooms ref school_years ( school_year ) school_year Fk classrooms_fk_branch classrooms ref branches ( branch ) branch Fk usenames_schools_fk_school usenames_schools ref schools ( school ) school Fk conversations_invites_fk_conversation conversations_invites ref conversations ( conversation ) conversation Fk conversations_invites_fk_person conversations_invites ref persons ( invited -> person ) invited Fk persons_relations_fk_person persons_relations ref persons ( person ) person Fk persons_relations_fk_person_related_to persons_relations ref persons ( person_related_to -> person ) person_related_to Fk persons_roles_fk_person persons_roles ref persons ( person ) person Fk messages_read_fk_message messages_read ref messages ( message ) message Fk messages_read_fk_person messages_read ref persons ( person ) person Fk messages_fk_conversation messages ref conversations ( conversation ) conversation Fk messages_fk_person messages ref persons ( person ) person Fk persons_fk_city_of_birth persons ref cities ( city_of_birth -> city ) city_of_birth Fk persons_fk_country_of_birth persons ref countries ( country_of_birth -> country ) country_of_birth Fk persons_fk_school persons ref schools ( school ) school Fk persons_fk_usename persons ref usenames_ex ( usename ) usename Fk lessons_fk_classroom lessons ref classrooms ( classroom ) classroom Fk lessons_fk_teacher lessons ref persons ( teacher -> person ) teacher Fk lessons_fk_subject lessons ref subjects ( subject ) subject Fk leavings_fk_classroom_student leavings ref classrooms_students ( classroom_student ) classroom_student Fk leavings_fk_explanation leavings ref explanations ( explanation ) explanation Fk leavings_fk_teacher leavings ref persons ( teacher -> person ) teacher Fk out_of_classrooms_fk_classroom_student out_of_classrooms ref classrooms_students ( classroom_student ) classroom_student Fk out_of_classrooms_fk_school_operator out_of_classrooms ref persons ( school_operator -> person ) school_operator Fk delays_fk_classroom_student delays ref classrooms_students ( classroom_student ) classroom_student Fk delays_fk_explanation delays ref explanations ( explanation ) explanation Fk delays_fk_teacher delays ref persons ( teacher -> person ) teacher Fk teachears_notes_fk_classroom teachears_notes ref classrooms ( classroom ) classroom Fk teachears_notes_fk_student teachears_notes ref persons ( student -> person ) student Fk teachears_notes_fk_teacher teachears_notes ref persons ( teacher -> person ) teacher Fk valutations_fk_grade_type valutations ref grade_types ( grade_type ) grade_type Fk valutations_fk_grade valutations ref grades ( grade ) grade Fk valutations_fk_note valutations ref notes ( note ) note Fk valutations_fk_teacher valutations ref persons ( teacher -> person ) teacher Fk valutations_fk_topic valutations ref topics ( topic ) topic Fk valutations_fk_classroom_student valutations ref classrooms_students ( classroom_student ) classroom_student Fk valutations_qualificationtions_fk_grade valutations_qualifications ref grades ( grade ) grade Fk valutations_qualificationtions_fk_valutation valutations_qualifications ref valutations ( valutation ) valutation Fk valutations_qualificationtions_fk_qualification valutations_qualifications ref qualifications ( qualification ) qualification Fk topics_fk_degree topics ref degrees ( degree ) degree Fk topics_fk_subject topics ref subjects ( subject ) subject Fk faults_fk_lesson faults ref lessons ( lesson ) lesson Fk faults_fk_student faults ref persons ( student -> person ) student Fk branches_fk_schools branches ref schools ( school ) school Fk schools_fk_behavior schools ref subjects ( behavior -> subject ) behavior Fk explanations_fk_created_by explanations ref persons ( created_by -> person ) created_by Fk explanations_fk_registered_by explanations ref persons ( registered_by -> person ) registered_by Fk explanations_fk_student explanations ref persons ( student -> person ) student Fk notes_fk_classroom notes ref classrooms ( classroom ) classroom Fk notes_fk_classroom_student notes ref classrooms_students ( classroom, student ) classroom,student Fk notes_fk_teacher notes ref persons ( teacher -> person ) teacher Fk signatures_fk_teacher signatures ref persons ( teacher -> person ) teacher Fk absences_fk_classroom_student absences ref classrooms_students ( classroom_student ) classroom_student Fk absences_fk_explanation absences ref explanations ( explanation ) explanation Fk absences_fk_teacher absences ref persons ( teacher -> person ) teacher persons_addressesTable public.persons_addresses Contains the address for every person registered in a school Pk persons_addresses_pk ( person_address ) person_addressperson_address * bigserial Unique identification code for the row # Unq persons_addresses_uq_indirizzo ( person, street, zip_code, city ) persons_addresses_fx_person ( person ) personperson * bigint Person that have that address References persons ( person ) address_typeaddress_type * address_type The address type of the person t Unq persons_addresses_uq_indirizzo ( person, street, zip_code, city ) streetstreet * varchar(160) The street t Unq persons_addresses_uq_indirizzo ( person, street, zip_code, city ) zip_codezip_code * varchar(15) The zip code t Unq persons_addresses_uq_indirizzo ( person, street, zip_code, city ) persons_addresses_fx_city ( city ) citycity * char(4) City of the person References cities ( city ) weekly_timetablesTable public.weekly_timetables Contains the weekly timetables for every school Pk weekly_timetable_pk ( weekly_timetable ) weekly_timetableweekly_timetable * bigserial Unique identification code for the row Referred by weekly_timetables_days ( weekly_timetable ) Unq weekly_timetable_uq_description ( classroom, description ) weekly_timetable_fx_classroom ( classroom ) classroomclassroom * bigint The classroom with this weekly timetable References classrooms ( classroom ) Unq weekly_timetable_uq_description ( classroom, description ) descriptiondescription varchar(160) The description for the weekly timetable t weekly_timetables_daysTable public.weekly_timetables_days Pk weekly_timetables_days_pk ( weekly_timetable_day ) weekly_timetable_dayweekly_timetable_day * bigserial Unique identification code for the row # Unq weekly_timetables_days_uq_weekly_timetable ( weekly_timetable, weekday, teacher, subject, from_time ) weekly_timetables_days_fx_weekly_timetable ( weekly_timetable ) weekly_timetableweekly_timetable * bigint Unique identification code for the row References weekly_timetables ( weekly_timetable ) Unq weekly_timetables_days_uq_weekly_timetable ( weekly_timetable, weekday, teacher, subject, from_time ) weekdayweekday * "utility"."week_day" Weekday for the timetable Unq weekly_timetables_days_uq_weekly_timetable ( weekly_timetable, weekday, teacher, subject, from_time ) weekly_timetables_days_fx_teacher ( teacher ) teacherteacher bigint Teacher for the weekly timetable References persons ( teacher -> person ) Unq weekly_timetables_days_uq_weekly_timetable ( weekly_timetable, weekday, teacher, subject, from_time ) weekly_timetables_days_fx_subject ( subject ) subjectsubject bigint Subject in the weekly timetable References subjects ( subject ) team_teachingteam_teaching * smallint default 1 Indicates the amount of team_teaching (1 first teacher, 2 second teacher ecc.) if there's only a teacher you have to insert 1 # Unq weekly_timetables_days_uq_weekly_timetable ( weekly_timetable, weekday, teacher, subject, from_time ) from_timefrom_time * time When the weekly timetable starts d to_timeto_time * time Time when the timetable finish d regionsTable public.regions Contains all avaible regions Pk regions_pk ( region ) regionregion * smallint Unique identification code for the row Referred by districts ( region ) Unq regions_uq_description ( description ) descriptiondescription * varchar(160) The description for the region t geographical_areageographical_area geographical_area The geographical area of the regions t districtsTable public.districts Contains all avaible districts Pk districts_pk ( district ) districtdistrict * char(2) Unique identification code for the row Referred by cities ( district ) Unq districts_uq_description ( description ) descriptiondescription * varchar(160) Unique identification code for the row t regionregion smallint The region References regions ( region ) citiesTable public.cities Contains all avaible cities Pk cities_pk ( city ) citycity * char(4) Unique identification code for the row Referred by persons ( city_of_birth -> city ) Referred by persons_addresses ( city ) Unq cities_uq_description ( description, district ) descriptiondescription * varchar(160) The description for the city t Unq cities_uq_description ( description, district ) cities_fx_district ( district ) districtdistrict * char(2) The district References districts ( district ) usenames_exTable public.usenames_ex Add informations to usename's system table usefull only to scuola247 Pk usenames_ex_pk ( usename ) usenameusename * name The usename for the person Referred by persons ( usename ) tokentoken varchar(1024) Used for the password reset t languagelanguage "utility"."language" Preferred language for the user t school_yearsTable public.school_years Rapreseant the school year and it's separated by schools Pk school_years_pk ( school_year ) school_yearschool_year * bigserial Unique identification code for the row Referred by classrooms ( school_year ) Referred by grading_meetings ( school_year ) Unq school_years_uq_description ( school, description ) school_years_ex_duration ( school, duration ) school_years_fx_school ( school ) schoolschool * bigint School References schools ( school ) Unq school_years_uq_description ( school, description ) descriptiondescription * varchar(160) The description for the school year t school_years_ex_duration ( school, duration ) durationduration daterange The duration for the school year lessons_durationlessons_duration daterange The duration of the lessons grading_meetingsTable public.grading_meetings Pk grading_meetings_pk ( grading_meeting ) grading_meetinggrading_meeting * bigserial Unique identification code for the row Referred by grading_meetings_valutations ( grading_meeting ) Unq grading_meetings_uq_description ( school_year, description ) Unq grading_meetings_uq_on_date ( school_year, on_date ) grading_meetings_fx_school ( school_year ) school_yearschool_year bigint The school year for the grading meeting References school_years ( school_year ) Unq grading_meetings_uq_on_date ( school_year, on_date ) on_dateon_date date Date of the grading meeting d Unq grading_meetings_uq_description ( school_year, description ) descriptiondescription * varchar(60) The description for the grading meeting t closedclosed * bool default false Indicates if the grading meeting is closed and the changes are locked b grading_meetings_valutations_quaTable public.grading_meetings_valutations_qua Shows all grading meetings, close or still open Pk grading_meetings_valutations_qua_pk ( grading_meeting_valutation_qua ) grading_meeting_valutation_quagrading_meeting_valutation_qua * bigserial The valutation from the grading meeting # Unq grading_meetings_valutations_qua_uq_qualification ( grading_meeting_valutation, qualification ) grading_meetings_valutations_qua_fx_grading_meeting_valutation ( grading_meeting_valutation ) grading_meeting_valutationgrading_meeting_valutation * bigint The grading meeting valutation References grading_meetings_valutations ( grading_meeting_valutation ) Unq grading_meetings_valutations_qua_uq_qualification ( grading_meeting_valutation, qualification ) grading_meetings_valutations_qua_fx_qualification ( qualification ) qualificationqualification * bigint The qualification for the grading meeting References qualifications ( qualification ) grading_meetings_valutations_qua_fx_grade ( grade ) gradegrade * bigint The final grade from the grading meeting References grades ( grade ) notesnotes varchar(2048) The notes at the grading meetings t grading_meetings_valutationsTable public.grading_meetings_valutations Pk grading_meetings_valutations_pk ( grading_meeting_valutation ) grading_meeting_valutationgrading_meeting_valutation * bigserial The valutation from the grading meeting Referred by grading_meetings_valutations_qua ( grading_meeting_valutation ) Unq grading_meetings_valutations_uq_student ( grading_meeting, classroom, student, subject, teacher ) grading_meetings_valutations_fx_grading_meeting ( grading_meeting ) grading_meetinggrading_meeting * bigint Unique identification code for the row References grading_meetings ( grading_meeting ) Unq grading_meetings_valutations_uq_student ( grading_meeting, classroom, student, subject, teacher ) grading_meetings_valutations_fx_classroom ( classroom ) classroomclassroom * bigint The classroom of these valutations from grading meeting References classrooms ( classroom ) Unq grading_meetings_valutations_uq_student ( grading_meeting, classroom, student, subject, teacher ) grading_meetings_valutations_fx_student ( student ) studentstudent * bigint Indicates all the valutations of the grading meeting References persons ( student -> person ) Unq grading_meetings_valutations_uq_student ( grading_meeting, classroom, student, subject, teacher ) grading_meetings_valutations_fx_subject ( subject ) subjectsubject * bigint The subject of the grading meeting References subjects ( subject ) grading_meetings_valutations_fx_grade ( grade ) gradegrade * bigint If the teacher is ' this indicates the grade of the grading meeting otherwise the grade of the teacher References grades ( grade ) notesnotes varchar(2048) The notes in the valutations from the grading meeting t lack_of_traininglack_of_training * bool default false Indicates if the student shows formative lacks b council_votecouncil_vote bool default false Indicates if the grade was chose by the grading metting or it's from the teacher if showed(so the row in the db shows the proposal grade and it can't be ') b Unq grading_meetings_valutations_uq_student ( grading_meeting, classroom, student, subject, teacher ) idx_grading_meetings_valutations ( teacher ) teacherteacher bigint If ' indicates the grade of the grading meeting othwerwise it shows the grade of the teacher References persons ( teacher -> person ) metricsTable public.metrics Pk metrics_pk ( metric ) metricmetric * bigserial Unique identification code for the row Referred by grades ( metric ) Referred by qualifications ( metric ) Unq metrics_uq_description ( school, description ) metrics_fx_school ( school ) schoolschool * bigint School with these metrics References schools ( school ) Unq metrics_uq_description ( school, description ) descriptiondescription * varchar(160) The description for the metrics t sufficiencysufficiency * smallint default 600 Indicates the value to reach the sufficiency # gradesTable public.grades For each metric here we have the possible grades Pk grades_pk ( grade ) gradegrade * bigserial Unique identification code for the row Referred by grading_meetings_valutations ( grade ) Referred by grading_meetings_valutations_qua ( grade ) Referred by valutations ( grade ) Referred by valutations_qualifications ( grade ) Unq grades_uq_description ( metric, description ) Unq grades_uq_mnemonic ( metric, mnemonic ) grades_fx_metric ( metric ) metricmetric * bigint Metric for the grades References metrics ( metric ) Unq grades_uq_description ( metric, description ) descriptiondescription * varchar(160) The description for the grade t thousandthsthousandths * smallint Indicates the weight in thousandths for that grade. Its value allows to compare different metrics # Unq grades_uq_mnemonic ( metric, mnemonic ) mnemonicmnemonic * varchar(3) A mnemonic for the grade t grade_typesTable public.grade_types The marks has to been grouped by mark type. Example: `Oral` or `Written` Pk grade_types_pk ( grade_type ) grade_typegrade_type * bigserial Unique identification code for the row Referred by valutations ( grade_type ) Unq tipi_grades_uq_description ( subject, description ) descriptiondescription * varchar(60) The description t Unq grade_types_uq_mnemonic ( subject, mnemonic ) Unq tipi_grades_uq_description ( subject, description ) grade_types_fx_subject ( subject ) subjectsubject * bigint The subject with these grade types References subjects ( subject ) Unq grade_types_uq_mnemonic ( subject, mnemonic ) mnemonicmnemonic varchar(3) The mnemonic of the grade type t communications_mediaTable public.communications_media Pk communications_media_pk ( communication_media ) communication_mediacommunication_media * bigserial Unique identification code for the row # Unq communications_media_uq_description ( person, communication_type, description ) Unq communications_media_uq_uri ( person, communication_type, uri ) communications_media_ix_person ( person ) personperson * bigint Person for the communication media References persons ( person ) Unq communications_media_uq_description ( person, communication_type, description ) Unq communications_media_uq_uri ( person, communication_type, uri ) communications_media_ix_communication_type ( communication_type ) communication_typecommunication_type * bigint Communication type for the communications References communication_types ( communication_type ) Unq communications_media_uq_description ( person, communication_type, description ) descriptiondescription varchar(160) The description for the communication media t Unq communications_media_uq_uri ( person, communication_type, uri ) uriuri * varchar(255) The uri for the media t notificationnotification * bool default false It states whether to use the notifications, only if the communication allows it. b communication_typesTable public.communication_types It refers to the kind of communication handled from the school and any notifies management that will be distincted by school, because it could have addictive costs that not every school wants. Pk communication_types_pk ( communication_type ) communication_typecommunication_type * bigserial Unique identification code for the row Referred by communications_media ( communication_type ) Unq communication_types_uq_description ( school, description ) descriptiondescription * varchar(160) All types of communication avaible t Unq communication_types_uq_description ( school, description ) communication_types_fx_school ( school ) schoolschool bigint School with these communication type References schools ( school ) notification_managementnotification_management * bool default false It's indicates if this kind of communication manage the notifications b holidaysTable public.holidays Contains all holidays for every school Pk holidays_pk ( holiday ) holidayholiday * bigserial Unique identification code for the row # Unq holidays_uq_on_date ( school, on_date ) holidays_fx_school ( school ) schoolschool * bigint School with these holidays References schools ( school ) Unq holidays_uq_on_date ( school, on_date ) on_dateon_date * date Start for holidays d descriptiondescription varchar(160) Description for the holidays t subjectsTable public.subjects Contains subjects for every school Pk subjects_pk ( subject ) subjectsubject * bigserial Unique identification code for the row Referred by grade_types ( subject ) Referred by grading_meetings_valutations ( subject ) Referred by lessons ( subject ) Referred by qualifications_plan ( subject ) Referred by schools ( behavior -> subject ) Referred by topics ( subject ) Referred by weekly_timetables_days ( subject ) Unq subjects_uq_description ( school, description ) subjects_fx_school ( school ) schoolschool * bigint The school with these subjects References schools ( school ) Unq subjects_uq_description ( school, description ) descriptiondescription * varchar(160) The description of a subject t degreesTable public.degrees Pk degrees_pk ( degree ) degreedegree * bigserial Unique identification code for the row Referred by classrooms ( degree ) Referred by qualifications_plan ( degree ) Referred by topics ( degree ) Unq degrees_uq_description ( school, description ) degrees_fx_school ( school ) schoolschool * bigint The school with these degrees References schools ( school ) Unq degrees_uq_description ( school, description ) descriptiondescription * varchar(160) The description of the degree t course_yearscourse_years * course_year Years of the course, e.g 5 for the elementary schools, 3 for the middle schools, 5 for the high schools qualificationsTable public.qualifications Describes for single school the knowledges, competences and skills. Adding all in only a table it's duplicated the term qualification to be generic compared to declicountry that can have: Expertise, knowledge, skills Pk qualifications_pk ( qualification ) qualificationqualification * bigserial Unique identification code for the row Referred by grading_meetings_valutations_qua ( qualification ) Referred by qualifications ( qualification_parent -> qualification ) Referred by qualifications_plan ( qualification ) Referred by valutations_qualifications ( qualification ) Unq qualifications_uq_name ( school, name ) qualificationtions_fx_school ( school ) schoolschool * bigint The school for the qualifications References schools ( school ) Unq qualifications_uq_name ( school, name ) namename * varchar(160) The name of the qualification t descriptiondescription * varchar(4000) Description of the qualification t qualificationtions_fx_metric ( metric ) metricmetric * bigint The metric used for the qualifications References metrics ( metric ) typetype * qualificationtion_types The type for these qualifications t qualificationtions_fx_riferimento ( qualification_parent ) qualification_parentqualification_parent bigint It's needed for create the qualification tree, in this coloumn will be indicated the family dependency References qualifications ( qualification_parent -> qualification ) conversationsTable public.conversations Pk conversations_pk ( conversation ) conversationconversation * bigserial Unique identification code for the row Referred by conversations_invites ( conversation ) Referred by messages ( conversation ) conversations_fx_school_record ( classroom_student ) classroom_studentclassroom_student * bigint Reference to the classroom_students table. References classrooms_students ( classroom_student ) subjectsubject * varchar(160) subject for the conversation t confidentialconfidential * bool default false States that the conversation has to be viewed by the participants and from the school insiders. Furthermore it's not included in the personal school_record. b begin_onbegin_on timestamp default now() When the conversation start d end_onend_on timestamp When a conversation ends, it's not longer possible to add or edit messages. d classrooms_studentsTable public.classrooms_students Pk classrooms_students_pk ( classroom_student ) classroom_studentclassroom_student * bigserial Unique identification code for the row Referred by absences ( classroom_student ) Referred by conversations ( classroom_student ) Referred by delays ( classroom_student ) Referred by leavings ( classroom_student ) Referred by out_of_classrooms ( classroom_student ) Referred by valutations ( classroom_student ) Unq classrooms_students_uq_classroom_student ( classroom, student ) classrooms_students_fx_classroom ( classroom ) classroomclassroom * bigint Classroom for these students References classrooms ( classroom ) Referred by notes ( classroom, student ) Unq classrooms_students_uq_classroom_student ( classroom, student ) studentstudent * bigint Student of the classroom References persons ( student -> person ) Referred by notes ( classroom, student ) retreat_onretreat_on date The date when the student has retreated from the classroom, or when he changed to another classroom of the same school, or another one. d classroom_destinationclassroom_destination bigint It's kept the trace about classroom where the student was transfered. # classroomsTable public.classrooms Contains all classrooms for every school Pk classrooms_pk ( classroom ) classroomclassroom * bigserial Unique identification code for the row Referred by classrooms_students ( classroom ) Referred by grading_meetings_valutations ( classroom ) Referred by lessons ( classroom ) Referred by notes ( classroom ) Referred by signatures ( classroom ) Referred by teachears_notes ( classroom ) Referred by weekly_timetables ( classroom ) Unq classrooms_uq_classroom ( school_year, branch, degree, section, course_year ) Unq classrooms_uq_description ( school_year, description ) classrooms_fx_school_year ( school_year ) school_yearschool_year * bigint The school year for these classrooms References school_years ( school_year ) Unq classrooms_uq_classroom ( school_year, branch, degree, section, course_year ) classrooms_fx_degree ( degree ) degreedegree * bigint Indicates the degree of a classroom References degrees ( degree ) Unq classrooms_uq_classroom ( school_year, branch, degree, section, course_year ) sectionsection varchar(5) The section of the classroom t Unq classrooms_uq_classroom ( school_year, branch, degree, section, course_year ) course_yearcourse_year * course_year The course year of the classroom Unq classrooms_uq_description ( school_year, description ) descriptiondescription * varchar(160) Description for the classroom t Unq classrooms_uq_classroom ( school_year, branch, degree, section, course_year ) classrooms_fx_branch ( branch ) branchbranch * bigint The branch of the class References branches ( branch ) usenames_schoolsTable public.usenames_schools record schools accessible to the user Pk usenames_schools_pk ( usename_school ) usename_schoolusename_school * bigserial Unique identification code for the row # Unq usenames_schools_uq_usename_school ( usename, school ) usenameusename * name The session's usename t Unq usenames_schools_uq_usename_school ( usename, school ) usenames_schools_fx_school ( school ) schoolschool * bigint School enabled for the the usename References schools ( school ) conversations_invitesTable public.conversations_invites Defines the guests at the conversation, the persons able to view and participate to a certain conversation. Pk conversations_invites_pk ( conversation_invite ) conversation_inviteconversation_invite * bigserial Unique identification code for the row # Unq conversations_invites_uq_invited ( conversation, invited ) conversations_partecipanti_fx_conversation ( conversation ) conversationconversation * bigint The conversation that has these invites References conversations ( conversation ) Unq conversations_invites_uq_invited ( conversation, invited ) conversations_partecipanti_fx_partecipante ( invited ) invitedinvited * bigint Check if is invited References persons ( invited -> person ) persons_relationsTable public.persons_relations Indicates the relations abount persons: tipically student(coloumn person) will have for relationship 'Parent' the father (person_related_to) for indicates the mother will add a row with same values as said before but looking that this time to insert in coloumn person_related_to the code of person that identifies the mother Pk persons_relations_pk ( person_relation ) person_relationperson_relation * bigserial The relation of this person with the student # Unq persons_relations_uq_person ( person, relationship, person_related_to ) persons_relations_fx_person ( person ) personperson * bigint Person that has this relation References persons ( person ) Unq persons_relations_uq_person ( person, relationship, person_related_to ) persons_relations_fx_person_related_to ( person_related_to ) person_related_toperson_related_to * bigint Indicates the person related to the student References persons ( person_related_to -> person ) sign_requestsign_request * bool default true Indicates that, in case of classroom notes (example: museum visit) or disciplinary notes, the teacher has to check if the parent checked the note b Unq persons_relations_uq_person ( person, relationship, person_related_to ) relationshiprelationship * relationships The relationship about the parent and the student t can_do_explanationcan_do_explanation bool default false Indicates if can do explanations for the student b persons_rolesTable public.persons_roles Contains the list of person with their role Pk persons_roles_pk ( person_role ) person_roleperson_role * bigserial Unique identification code for the row # Unq persons_roles_uq_person ( person, role ) idx_persons_roles ( person ) personperson * bigint The person with these roles References persons ( person ) Unq persons_roles_uq_person ( person, role ) rolerole * role The role of a person t messages_readTable public.messages_read Pk messages_read_pk ( message_read ) message_readmessage_read * bigserial Unique identification code for the row # Unq messages_read_uq_read_on ( message, person ) libretti_messages_read_fx_school_record_mess ( message ) messagemessage * bigint Message to read References messages ( message ) Unq messages_read_uq_read_on ( message, person ) libretti_messages_read_fx_person ( person ) personperson * bigint The person that rode the message References persons ( person ) read_onread_on timestamp Date when the message has been red d messagesTable public.messages Pk messages_pk ( message ) messagemessage * bigserial Unique identification code for the row Referred by messages_read ( message ) Unq messages_uq_from_time ( conversation, person, written_on ) messages_fx_conversation ( conversation ) conversationconversation * bigint Conversation References conversations ( conversation ) Unq messages_uq_from_time ( conversation, person, written_on ) written_onwritten_on * timestamp default now() Date when the message was written d message_textmessage_text * varchar(2048) The text of the message t Unq messages_uq_from_time ( conversation, person, written_on ) messages_fx_from_time ( person ) personperson * bigint La person fisica che ha scritto il message References persons ( person ) countriesTable public.countries Contains all avaible countries Pk countries_pk ( country ) countrycountry * smallint Unique identification code for the row Referred by persons ( country_of_birth -> country ) Unq countries_uq_description ( description ) descriptiondescription * varchar(160) A description for the countries t existingexisting * bool default true Checked if the country exist b personsTable public.persons Pk persons_pk ( person ) personperson * bigserial Unique identification code for the row Referred by absences ( teacher -> person ) Referred by classrooms_students ( student -> person ) Referred by communications_media ( person ) Referred by conversations_invites ( invited -> person ) Referred by delays ( teacher -> person ) Referred by explanations ( created_by -> person ) Referred by explanations ( registered_by -> person ) Referred by explanations ( student -> person ) Referred by faults ( student -> person ) Referred by grading_meetings_valutations ( student -> person ) Referred by grading_meetings_valutations ( teacher -> person ) Referred by leavings ( teacher -> person ) Referred by lessons ( teacher -> person ) Referred by messages ( person ) Referred by messages_read ( person ) Referred by notes ( teacher -> person ) Referred by notes_signed ( person ) Referred by out_of_classrooms ( school_operator -> person ) Referred by parents_meetings ( person ) Referred by parents_meetings ( teacher -> person ) Referred by persons_addresses ( person ) Referred by persons_relations ( person ) Referred by persons_relations ( person_related_to -> person ) Referred by persons_roles ( person ) Referred by signatures ( teacher -> person ) Referred by teachears_notes ( student -> person ) Referred by teachears_notes ( teacher -> person ) Referred by valutations ( teacher -> person ) Referred by weekly_timetables_days ( teacher -> person ) Referred by wikimedia_files_persons ( person ) namename * varchar(60) Name of the person t surnamesurname * varchar(60) The surname of this person t bornborn date The date when person born d deceaseddeceased date Check if the person is deceased d persons_fx_country_of_birth ( country_of_birth ) country_of_birthcountry_of_birth smallint Country of birth for the person References countries ( country_of_birth -> country ) Unq persons_uq_school_tax_code ( school, tax_code ) tax_codetax_code char(16) The tax code for every person c sexsex * sex Sex of the person t photophoto image The photo for these persons Unq persons_uq_school_tax_code ( school, tax_code ) Unq persons_uq_usename ( school, usename ) persons_fx_school ( school ) persons_ix_usename_school ( usename, school ) schoolschool bigint The school for this person References schools ( school ) sidisidi bigint Sidi of the person # persons_fx_city_of_birth ( city_of_birth ) city_of_birthcity_of_birth char(4) The city of birth for the person References cities ( city_of_birth -> city ) thumbnailthumbnail image Thumbnail of the person notenote text contains addtional informatio about person t Unq persons_uq_usename ( school, usename ) persons_fx_usename ( usename ) persons_ix_usename_school ( usename, school ) usenameusename name The usename of these persons References usenames_ex ( usename ) lessonsTable public.lessons Pk lessons_pk ( lesson ) lessonlesson * bigserial Unique identification code for the row Referred by faults ( lesson ) lessons_fx_classroom ( classroom ) classroomclassroom * bigint Classroom that have these lessons References classrooms ( classroom ) on_dateon_date * date Date when the lessons start d lessons_fx_subject ( subject ) subjectsubject * bigint The subject at lesson References subjects ( subject ) lessons_fx_teacher ( teacher ) teacherteacher * bigint The teacher for these lessons References persons ( teacher -> person ) descriptiondescription * varchar(2048) The description for the lessons t substitutesubstitute * bool default false Indicates if the lesson is a taken by a sobstitute teacher not owning the teacher post b from_timefrom_time * time Time to begin the lessons d to_timeto_time * time Time to end the lessons d assignmentassignment varchar(2048) assignments gaven during the lesson t periodperiod tsrange Period for the lessons leavingsTable public.leavings Reveals leavings of a student Pk leavings_pk ( leaving ) leavingleaving * bigserial Unique identification code for the row # leavings_fx_teacher ( teacher ) teacherteacher * bigint The teacher that giustified the leaving References persons ( teacher -> person ) leavings_fx_explanation ( explanation ) explanationexplanation * bigint Explanation for the leaving References explanations ( explanation ) Unq leavings_uq_classroom_student ( classroom_student, on_date ) on_dateon_date * date When the leaving has been done d at_timeat_time * time When the student left from the classroom d Unq leavings_uq_classroom_student ( classroom_student, on_date ) leavings_fx_classroom_student ( classroom_student ) classroom_studentclassroom_student bigint Student for the leaving References classrooms_students ( classroom_student ) out_of_classroomsTable public.out_of_classrooms Indicates when a student isn't present in classroom but don't has to be considered absent, example for sports activity Pk out_of_classrooms_pk ( out_of_classroom ) out_of_classroomout_of_classroom * bigserial Unique identification code for the row # out_of_classrooms_fx_school_operator ( school_operator ) school_operatorschool_operator * bigint School operator with out of classrooms References persons ( school_operator -> person ) descriptiondescription * varchar(160) The description for whom out of classroom t Unq out_of_classrooms_uq_classroom_student_on_date ( classroom_student, on_date ) on_dateon_date * date The date when out of classroom d from_timefrom_time * time at_time of leaving d to_timeto_time * time at_time of return d Unq out_of_classrooms_uq_classroom_student_on_date ( classroom_student, on_date ) out_of_classrooms_fx_classroom_student ( classroom_student ) classroom_studentclassroom_student bigint The classroom of the student References classrooms_students ( classroom_student ) delaysTable public.delays Reveals delays of a student Pk delays_pk ( delay ) delaydelay * bigserial Unique identification code for the row # delays_fx_teacher ( teacher ) teacherteacher * bigint The teacher with these delays References persons ( teacher -> person ) delays_fx_explanation ( explanation ) explanationexplanation bigint explanation for the delay References explanations ( explanation ) Unq delays_uq_classroom_student_on_date ( classroom_student, on_date ) on_dateon_date * date The date for the delay d at_timeat_time * time Date of the delay d Unq delays_uq_classroom_student_on_date ( classroom_student, on_date ) delays_fx_classroom_student ( classroom_student ) classroom_studentclassroom_student bigint The student that did the delay References classrooms_students ( classroom_student ) teachears_notesTable public.teachears_notes It does the same fuunctions of notes table but for teacher register. The only difference is that isn't necessary to replicate the coloumn 'disciplinary' too because the disciplinary notes have to be done only on classroom register unless these notes are mainly for private use of the teacher. Pk teachears_notes_pk ( teacher_notes ) teacher_notesteacher_notes * bigserial The teacher notes # Unq teachears_notes_uq_on_date_at_time ( classroom, student, on_date, at_time ) teachears_notes_fx_student ( student ) studentstudent bigint The student with this note References persons ( student -> person ) descriptiondescription * varchar(2048) Description for the note t teachears_notes_fx_teacher ( teacher ) teacherteacher * bigint Teacher that insert the note References persons ( teacher -> person ) Unq teachears_notes_uq_on_date_at_time ( classroom, student, on_date, at_time ) on_dateon_date * date When the note has been added d Unq teachears_notes_uq_on_date_at_time ( classroom, student, on_date, at_time ) at_timeat_time time When the teacher insert the note d Unq teachears_notes_uq_on_date_at_time ( classroom, student, on_date, at_time ) teachears_notes_fx_classroom ( classroom ) classroomclassroom * bigint Classroom for the teacher notes References classrooms ( classroom ) valutationsTable public.valutations Contains valutations of all students did by all teachers Pk valutations_pk ( valutation ) valutationvalutation * bigserial Unique identification code for the row Referred by valutations_qualifications ( valutation ) subjectsubject * bigint The subject with these valutations # valutations_fx_grade_type ( grade_type ) grade_typegrade_type * bigint The type of grade References grade_types ( grade_type ) valutations_fx_topic ( topic ) topictopic bigint The topic for these valutations References topics ( topic ) valutations_fx_grade ( grade ) gradegrade * bigint The grade for the valutation References grades ( grade ) evaluationevaluation varchar(160) The evaluation t privateprivate * bool default false Indicates that the grade is visible for only the teacher that has insert it b valutations_fx_teacher ( teacher ) teacherteacher * bigint The teacher coloumn has been inserted because the techer during the school year could be changed, in this mode will be tracked who insert the value References persons ( teacher -> person ) on_dateon_date * date When the valutation was insert d valutations_fx_note ( note ) notenote bigint The note associated to the valutation References notes ( note ) valutations_fx_classroom_student ( classroom_student ) classroom_studentclassroom_student * bigint References classrooms_students ( classroom_student ) valutations_qualificationsTable public.valutations_qualifications For every valution inserted in valutations table is possible to connect valutation too of qualifications connected that are saved here Pk valutations_qualificationtions_pk ( valutation_qualificationtion ) valutation_qualificationtionvalutation_qualificationtion * bigserial Unique identification code for the row # Unq valutations_qualificationtions_uq_qualification ( valutation, qualification ) valutations_qualificationtions_fx_valutation ( valutation ) valutationvalutation * bigint The valutation of a qualification References valutations ( valutation ) Unq valutations_qualificationtions_uq_qualification ( valutation, qualification ) valutations_qualificationtions_fx_qualification ( qualification ) qualificationqualification * bigint Unique identification code for the row References qualifications ( qualification ) valutations_qualificationtions_fx_grade ( grade ) gradegrade * bigint Grade of the qualification References grades ( grade ) notenote varchar(2048) The note for the qualification t topicsTable public.topics Contains the object topics of a valutation Pk topics_pk ( topic ) topictopic * bigserial Unique identification code for the row Referred by valutations ( topic ) Unq topics_uq_description ( degree, course_year, subject, description ) topics_fx_subject ( subject ) subjectsubject * bigint Subject for the topic References subjects ( subject ) Unq topics_uq_description ( degree, course_year, subject, description ) descriptiondescription * varchar(160) The description of the topic t Unq topics_uq_description ( degree, course_year, subject, description ) course_yearcourse_year course_year The course year with these topics Unq topics_uq_description ( degree, course_year, subject, description ) topics_fx_degree ( degree ) degreedegree * bigint The degree for the topic References degrees ( degree ) faultsTable public.faults Reveals the faults of a student Pk faults_pk ( fault ) faultfault * bigserial Unique identification code for the row # faults_fx_student ( student ) studentstudent * bigint The student with these faults References persons ( student -> person ) descriptiondescription * varchar(2048) Description for the faults t faults_fx_lesson ( lesson ) lessonlesson * bigint The lesson with these faults References lessons ( lesson ) notenote bigint Note for the fault # branchesTable public.branches Contains branches for every school Pk branches_pk ( branch ) branchbranch * bigserial Unique identification code for the row Referred by classrooms ( branch ) Unq branches_uq_description ( school, description ) branches_fx_school ( school ) schoolschool * bigint School for branches References schools ( school ) Unq branches_uq_description ( school, description ) descriptiondescription * varchar(160) The description for the branch t schoolsTable public.schools An institution for the instruction of children or people under college age Pk schools_pk ( school ) schoolschool * bigserial Uniquely identifies the table row Referred by branches ( school ) Referred by communication_types ( school ) Referred by degrees ( school ) Referred by holidays ( school ) Referred by metrics ( school ) Referred by persons ( school ) Referred by qualifications ( school ) Referred by school_years ( school ) Referred by subjects ( school ) Referred by usenames_schools ( school ) Unq schools_uq_description ( description ) descriptiondescription * varchar(160) Description for the school t Unq schools_uq_processing_code ( processing_code, example ) processing_codeprocessing_code * varchar(160) A code that identify the school on the government information system t Unq schools_uq_mnemonic ( mnemonic ) mnemonicmnemonic * varchar(30) Short description to be use as code t Unq schools_uq_processing_code ( processing_code, example ) exampleexample * bool default false It indicates that the data have been inserted to be an example of the use of the data base b logo image Picture to be used as a logo schools_fk_behavior ( behavior ) behaviorbehavior bigint Indicates the subject used for the behavior References subjects ( behavior -> subject ) explanationsTable public.explanations Contains the explanations for absences, delay annd leavings. It can be done by a schoolteacher that will compile the description or by a parents Pk explanations_pk ( explanation ) explanationexplanation * bigserial Unique identification code for the row Referred by absences ( explanation ) Referred by delays ( explanation ) Referred by leavings ( explanation ) explanations_fx_student ( student ) studentstudent * bigint The student with this explanation References persons ( student -> person ) descriptiondescription * varchar(2048) Description for the explanation t created_oncreated_on * timestamp Date when the explanation has been created d explanations_fx_created_by ( created_by ) created_bycreated_by * bigint The person, school teacher, family or student also, if adult, that has insert the explanation References persons ( created_by -> person ) registered_onregistered_on timestamp Date for register when the explanation has been insert d explanations_fx_usata_from_time ( registered_by ) registered_byregistered_by bigint Whom have insert the explanation References persons ( registered_by -> person ) from_timefrom_time date The starting absence time of an explanation d to_timeto_time date The return at school time of an explanation d coming_atcoming_at time When the student came at school (delay) d leaving_atleaving_at time When the student had leave the school d typetype explanation_type The type for the explanation t notesTable public.notes Pk notes_pk ( note ) notenote * bigserial Unique identification code for the row Referred by notes_signed ( note ) Referred by valutations ( note ) Unq notes_uq_on_date_at_time ( classroom, student, on_date, at_time ) notes_fx_classroom_student ( classroom, student ) studentstudent bigint The student with these notes References classrooms_students ( classroom, student ) descriptiondescription * varchar(2048) The description of the note t notes_fx_teacher ( teacher ) teacherteacher * bigint The teacher that insert this note References persons ( teacher -> person ) disciplinarydisciplinary * bool Indicates that the annotation is of disciplinary type so it will be reported at school_record for the view signature of a parent. The annotation is for all the classroom unless it isn't indicated a single student. If it's needed to do a disciplinary note (also normal) to two or more students is necessary to insert the note for everyone. b Unq notes_uq_on_date_at_time ( classroom, student, on_date, at_time ) on_dateon_date * date Note insert on date d Unq notes_uq_on_date_at_time ( classroom, student, on_date, at_time ) at_timeat_time * time When the note was insert d to_approveto_approve * bool default false Indicates that is requested the check from the student (if adult) or from someone that have the paternity authorition and have requested to be warned. If it isn't specified the check has to be requested for all the class, but if the note is disciplinary and is missing the student, the check will be requested only for present students b Unq notes_uq_on_date_at_time ( classroom, student, on_date, at_time ) notes_fx_classroom ( classroom ) notes_fx_classroom_student ( classroom, student ) classroomclassroom * bigint Indicates if the note is for all the classroom References classrooms ( classroom ) References classrooms_students ( classroom, student ) signaturesTable public.signatures Contains info of the signature for every teacher Pk signatures_pk ( signature ) signaturesignature * bigserial Unique identification code for the row # Unq signatures_uq_classroom ( classroom, teacher, on_date, at_time ) signatures_fx_classroom ( classroom ) classroomclassroom * bigint The classroom of the student References classrooms ( classroom ) Unq signatures_uq_classroom ( classroom, teacher, on_date, at_time ) signatures_fx_teacher ( teacher ) teacherteacher * bigint The teacher that did that signature References persons ( teacher -> person ) Unq signatures_uq_classroom ( classroom, teacher, on_date, at_time ) on_dateon_date * date Date of the signature d Unq signatures_uq_classroom ( classroom, teacher, on_date, at_time ) at_timeat_time time When the signature was insert d absencesTable public.absences Detect the absences of a student. Pk absences_pk ( absence ) absenceabsence * bigserial Unique identification code for the row # Unq absences_uq_classroom_student_on_date ( classroom_student, on_date ) on_dateon_date * date The date when the absence has been done d absences_fx_teacher ( teacher ) teacherteacher * bigint The teacher that has validated the absence. References persons ( teacher -> person ) absences_fx_explanation ( explanation ) explanationexplanation bigint Explanation for the absences References explanations ( explanation ) Unq absences_uq_classroom_student_on_date ( classroom_student, on_date ) absences_fx_classroom_student ( classroom_student ) classroom_studentclassroom_student bigint Student of the Classroom with this absence References classrooms_students ( classroom_student )


Table absences

Detect the absences of a student.

IndexesField NameData TypeDescription
* absence bigserial Unique identification code for the row
* on_date date The date when the absence has been done
* teacher bigint The teacher that has validated the absence.
explanation bigint Explanation for the absences
classroom_student bigint Student of the Classroom with this absence
Indexes
absences_pk ON absence
absences_uq_classroom_student_on_date ON classroom_student, on_date
absences_fx_classroom_student ON classroom_student
absences_fx_explanation ON explanation
absences_fx_teacher ON teacher
Foreign Keys
absences_fk_classroom_student ( classroom_student ) ref classrooms_students (classroom_student)
absences_fk_explanation ( explanation ) ref explanations (explanation)
absences_fk_teacher ( teacher ) ref persons (person)
Triggers
  absences_iu
  absences_iu


Table branches

Contains branches for every school

IndexesField NameData TypeDescription
* branch bigserial Unique identification code for the row
* school bigint School for branches
* description varchar( 160 ) The description for the branch
Indexes
branches_pk ON branch
branches_uq_description ON school, description
branches_fx_school ON school
Foreign Keys
branches_fk_schools ( school ) ref schools (school)


Table cities

Contains all avaible cities

IndexesField NameData TypeDescription
* city char( 4 ) Unique identification code for the row
* description varchar( 160 ) The description for the city
* district char( 2 ) The district
Indexes
cities_pk ON city
cities_uq_description ON description, district
cities_fx_district ON district
Foreign Keys
cities_fk_district ( district ) ref districts (district)


Table classrooms

Contains all classrooms for every school

IndexesField NameData TypeDescription
* classroom bigserial Unique identification code for the row
* school_year bigint The school year for these classrooms
* degree bigint Indicates the degree of a classroom
section varchar( 5 ) The section of the classroom
* course_year course_year The course year of the classroom
* description varchar( 160 ) Description for the classroom
* branch bigint The branch of the class
Indexes
classrooms_pk ON classroom
classrooms_uq_classroom ON school_year, branch, degree, section, course_year
classrooms_uq_description ON school_year, description
classrooms_fx_branch ON branch
classrooms_fx_degree ON degree
classrooms_fx_school_year ON school_year
Foreign Keys
classrooms_fk_degree ( degree ) ref degrees (degree)
classrooms_fk_school_year ( school_year ) ref school_years (school_year)
classrooms_fk_branch ( branch ) ref branches (branch)
Triggers
  classrooms_iu
  classrooms_iu


Table classrooms_students

IndexesField NameData TypeDescription
* classroom_student bigserial Unique identification code for the row
* classroom bigint Classroom for these students
* student bigint Student of the classroom
  retreat_on date The date when the student has retreated from the classroom, or when he changed to another classroom of the same school, or another one.
  classroom_destination bigint It's kept the trace about classroom where the student was transfered.
Indexes
classrooms_students_pk ON classroom_student
classrooms_students_uq_classroom_student ON classroom, student
classrooms_students_fx_classroom ON classroom
Foreign Keys
classrooms_students_fk_classroom ( classroom ) ref classrooms (classroom)
classrooms_students_fk_student ( student ) ref persons (person)
Triggers
  classrooms_students_iu
  classrooms_students_iu


Table communication_types

It refers to the kind of communication handled from the school and any notifies management that will be distincted by school, because it could have addictive costs that not every school wants.

IndexesField NameData TypeDescription
* communication_type bigserial Unique identification code for the row
* description varchar( 160 ) All types of communication avaible
school bigint School with these communication type
* notification_management bool DEFAULT false It's indicates if this kind of communication manage the notifications
Indexes
communication_types_pk ON communication_type
communication_types_uq_description ON school, description
communication_types_fx_school ON school
Foreign Keys
communication_types_fk_school ( school ) ref schools (school)


Table communications_media

IndexesField NameData TypeDescription
* communication_media bigserial Unique identification code for the row
* person bigint Person for the communication media
* communication_type bigint Communication type for the communications
description varchar( 160 ) The description for the communication media
* uri varchar( 255 ) The uri for the media
* notification bool DEFAULT false It states whether to use the notifications, only if the communication allows it.
Indexes
communications_media_pk ON communication_media
communications_media_uq_description ON person, communication_type, description
communications_media_uq_uri ON person, communication_type, uri
communications_media_ix_communication_type ON communication_type
communications_media_ix_person ON person
Foreign Keys
communications_media_fk_communication_type ( communication_type ) ref communication_types (communication_type)
communications_media_fk_person ( person ) ref persons (person)
Triggers
  communications_media_iu
  communications_media_iu


Table conversations

IndexesField NameData TypeDescription
* conversation bigserial Unique identification code for the row
* classroom_student bigint Reference to the classroom_students table.
* subject varchar( 160 ) subject for the conversation
* confidential bool DEFAULT false States that the conversation has to be viewed by the participants and from the school insiders. Furthermore it's not included in the personal school_record.
  begin_on timestamp DEFAULT now() When the conversation start
  end_on timestamp When a conversation ends, it's not longer possible to add or edit messages.
Indexes
conversations_pk ON conversation
conversations_fx_school_record ON classroom_student
Foreign Keys
conversations_fk_classroom_student ( classroom_student ) ref classrooms_students (classroom_student)
Constraints
  conversations_ck_end_on end_on >= begin_on


Table conversations_invites

Defines the guests at the conversation, the persons able to view and participate to a certain conversation.

IndexesField NameData TypeDescription
* conversation_invite bigserial Unique identification code for the row
* conversation bigint The conversation that has these invites
* invited bigint Check if is invited
Indexes
conversations_invites_pk ON conversation_invite
conversations_invites_uq_invited ON conversation, invited
conversations_partecipanti_fx_conversation ON conversation
conversations_partecipanti_fx_partecipante ON invited
Foreign Keys
conversations_invites_fk_conversation ( conversation ) ref conversations (conversation)
conversations_invites_fk_person ( invited ) ref persons (person)
Triggers
  conversations_invites_iu
  conversations_invites_iu


Table countries

Contains all avaible countries

IndexesField NameData TypeDescription
* country smallint Unique identification code for the row
* description varchar( 160 ) A description for the countries
* existing bool DEFAULT true Checked if the country exist
Indexes
countries_pk ON country
countries_uq_description ON description


Table degrees

IndexesField NameData TypeDescription
* degree bigserial Unique identification code for the row
* school bigint The school with these degrees
* description varchar( 160 ) The description of the degree
* course_years course_year Years of the course, e.g 5 for the elementary schools, 3 for the middle schools, 5 for the high schools
Indexes
degrees_pk ON degree
degrees_uq_description ON school, description
degrees_fx_school ON school
Foreign Keys
degrees_fk_school ( school ) ref schools (school)


Table delays

Reveals delays of a student

IndexesField NameData TypeDescription
* delay bigserial Unique identification code for the row
* teacher bigint The teacher with these delays
explanation bigint explanation for the delay
* on_date date The date for the delay
* at_time time Date of the delay
classroom_student bigint The student that did the delay
Indexes
delays_pk ON delay
delays_uq_classroom_student_on_date ON classroom_student, on_date
delays_fx_classroom_student ON classroom_student
delays_fx_explanation ON explanation
delays_fx_teacher ON teacher
Foreign Keys
delays_fk_classroom_student ( classroom_student ) ref classrooms_students (classroom_student)
delays_fk_explanation ( explanation ) ref explanations (explanation)
delays_fk_teacher ( teacher ) ref persons (person)
Triggers
  delays_iu
  delays_iu


Table districts

Contains all avaible districts

IndexesField NameData TypeDescription
* district char( 2 ) Unique identification code for the row
* description varchar( 160 ) Unique identification code for the row
region smallint The region
Indexes
districts_pk ON district
districts_uq_description ON description
Foreign Keys
districts_fk_region ( region ) ref regions (region)


Table explanations

Contains the explanations for absences, delay annd leavings. It can be done by a schoolteacher that will compile the description or by a parents

IndexesField NameData TypeDescription
* explanation bigserial Unique identification code for the row
* student bigint The student with this explanation
* description varchar( 2048 ) Description for the explanation
* created_on timestamp Date when the explanation has been created
* created_by bigint The person, school teacher, family or student also, if adult, that has insert the explanation
  registered_on timestamp Date for register when the explanation has been insert
registered_by bigint Whom have insert the explanation
  from_time date The starting absence time of an explanation
  to_time date The return at school time of an explanation
  coming_at time When the student came at school (delay)
  leaving_at time When the student had leave the school
  type explanation_type The type for the explanation
Indexes
explanations_pk ON explanation
explanations_fx_created_by ON created_by
explanations_fx_student ON student
explanations_fx_usata_from_time ON registered_by
Foreign Keys
explanations_fk_created_by ( created_by ) ref persons (person)
explanations_fk_registered_by ( registered_by ) ref persons (person)
explanations_fk_student ( student ) ref persons (person)
Constraints
  explanations_ck_leaving_at leaving_at > coming_at
  explanations_ck_registered_on registered_on >= created_on
  explanations_ck_to_time to_time >= from_time
Triggers
  explanations_iu
  explanations_iu


Table faults

Reveals the faults of a student

IndexesField NameData TypeDescription
* fault bigserial Unique identification code for the row
* student bigint The student with these faults
* description varchar( 2048 ) Description for the faults
* lesson bigint The lesson with these faults
  note bigint Note for the fault
Indexes
faults_pk ON fault
faults_fx_lesson ON lesson
faults_fx_student ON student
Foreign Keys
faults_fk_lesson ( lesson ) ref lessons (lesson)
faults_fk_student ( student ) ref persons (person)
Triggers
  faults_iu
  faults_iu


Table grade_types

The marks has to been grouped by mark type. Example: `Oral` or `Written`

IndexesField NameData TypeDescription
* grade_type bigserial Unique identification code for the row
* description varchar( 60 ) The description
* subject bigint The subject with these grade types
mnemonic varchar( 3 ) The mnemonic of the grade type
Indexes
grade_types_pk ON grade_type
grade_types_uq_mnemonic ON subject, mnemonic
tipi_grades_uq_description ON subject, description
grade_types_fx_subject ON subject
Foreign Keys
grade_types_fk_subject ( subject ) ref subjects (subject)


Table grades

For each metric here we have the possible grades

IndexesField NameData TypeDescription
* grade bigserial Unique identification code for the row
* metric bigint Metric for the grades
* description varchar( 160 ) The description for the grade
* thousandths smallint Indicates the weight in thousandths for that grade. Its value allows to compare different metrics
* mnemonic varchar( 3 ) A mnemonic for the grade
Indexes
grades_pk ON grade
grades_uq_description ON metric, description
grades_uq_mnemonic ON metric, mnemonic
grades_fx_metric ON metric
Foreign Keys
grades_fk_metric ( metric ) ref metrics (metric)


Table grading_meetings

IndexesField NameData TypeDescription
* grading_meeting bigserial Unique identification code for the row
school_year bigint The school year for the grading meeting
on_date date Date of the grading meeting
* description varchar( 60 ) The description for the grading meeting
* closed bool DEFAULT false Indicates if the grading meeting is closed and the changes are locked
Indexes
grading_meetings_pk ON grading_meeting
grading_meetings_uq_description ON school_year, description
grading_meetings_uq_on_date ON school_year, on_date
grading_meetings_fx_school ON school_year
Foreign Keys
grading_meetings_fk_school_year ( school_year ) ref school_years (school_year)
Triggers
  grading_meetings_iu


Table grading_meetings_valutations

IndexesField NameData TypeDescription
* grading_meeting_valutation bigserial The valutation from the grading meeting
* grading_meeting bigint Unique identification code for the row
* classroom bigint The classroom of these valutations from grading meeting
* student bigint Indicates all the valutations of the grading meeting
* subject bigint The subject of the grading meeting
* grade bigint If the teacher is ' this indicates the grade of the grading meeting otherwise the grade of the teacher
  notes varchar( 2048 ) The notes in the valutations from the grading meeting
* lack_of_training bool DEFAULT false Indicates if the student shows formative lacks
  council_vote bool DEFAULT false Indicates if the grade was chose by the grading metting or it's from the teacher if showed(so the row in the db shows the proposal grade and it can't be ')
teacher bigint If ' indicates the grade of the grading meeting othwerwise it shows the grade of the teacher
Indexes
grading_meetings_valutations_pk ON grading_meeting_valutation
grading_meetings_valutations_uq_student ON grading_meeting, classroom, student, subject, teacher
grading_meetings_valutations_fx_classroom ON classroom
grading_meetings_valutations_fx_grade ON grade
grading_meetings_valutations_fx_grading_meeting ON grading_meeting
grading_meetings_valutations_fx_student ON student
grading_meetings_valutations_fx_subject ON subject
idx_grading_meetings_valutations ON teacher
Foreign Keys
grading_meetings_valutations_fk_classroom ( classroom ) ref classrooms (classroom)
grading_meetings_valutations_fk_grade ( grade ) ref grades (grade)
grading_meetings_valutations_fk_grading_meeting ( grading_meeting ) ref grading_meetings (grading_meeting)
grading_meetings_valutations_fk_student ( student ) ref persons (person)
grading_meetings_valutations_fk_teacher ( teacher ) ref persons (person)
grading_meetings_valutations_fk_subject ( subject ) ref subjects (subject)
Constraints
  grading_meetings_valutations_ck_grade_consiglio ((teacher IS NOT NULL) AND (council_vote IS NULL)) OR ((teacher IS NULL) AND (council_vote IS NOT NULL))
Triggers
  grading_meetings_valutations_d
  grading_meetings_valutations_iu
  grading_meetings_valutations_iu


Table grading_meetings_valutations_qua

Shows all grading meetings, close or still open

IndexesField NameData TypeDescription
* grading_meeting_valutation_qua bigserial The valutation from the grading meeting
* grading_meeting_valutation bigint The grading meeting valutation
* qualification bigint The qualification for the grading meeting
* grade bigint The final grade from the grading meeting
  notes varchar( 2048 ) The notes at the grading meetings
Indexes
grading_meetings_valutations_qua_pk ON grading_meeting_valutation_qua
grading_meetings_valutations_qua_uq_qualification ON grading_meeting_valutation, qualification
grading_meetings_valutations_qua_fx_grade ON grade
grading_meetings_valutations_qua_fx_grading_meeting_valutation ON grading_meeting_valutation
grading_meetings_valutations_qua_fx_qualification ON qualification
Foreign Keys
grading_meetings_valutations_qua_fk_grade ( grade ) ref grades (grade)
grading_meetings_valutations_qua_fk_grading_meeting_valutation ( grading_meeting_valutation ) ref grading_meetings_valutations (grading_meeting_valutation)
grading_meetings_valutations_qua_fk_qualification ( qualification ) ref qualifications (qualification)
Triggers
  grading_meetings_valutations_qua_d
  grading_meetings_valutations_qua_iu
  grading_meetings_valutations_qua_iu


Table holidays

Contains all holidays for every school

IndexesField NameData TypeDescription
* holiday bigserial Unique identification code for the row
* school bigint School with these holidays
* on_date date Start for holidays
  description varchar( 160 ) Description for the holidays
Indexes
holidays_pk ON holiday
holidays_uq_on_date ON school, on_date
holidays_fx_school ON school
Foreign Keys
holidays_fk_school ( school ) ref schools (school)


Table leavings

Reveals leavings of a student

IndexesField NameData TypeDescription
* leaving bigserial Unique identification code for the row
* teacher bigint The teacher that giustified the leaving
* explanation bigint Explanation for the leaving
* on_date date When the leaving has been done
* at_time time When the student left from the classroom
classroom_student bigint Student for the leaving
Indexes
leavings_pk ON leaving
leavings_uq_classroom_student ON classroom_student, on_date
leavings_fx_classroom_student ON classroom_student
leavings_fx_explanation ON explanation
leavings_fx_teacher ON teacher
Foreign Keys
leavings_fk_classroom_student ( classroom_student ) ref classrooms_students (classroom_student)
leavings_fk_explanation ( explanation ) ref explanations (explanation)
leavings_fk_teacher ( teacher ) ref persons (person)
Triggers
  leavings_iu
  leavings_iu


Table lessons

IndexesField NameData TypeDescription
* lesson bigserial Unique identification code for the row
* classroom bigint Classroom that have these lessons
* on_date date Date when the lessons start
* subject bigint The subject at lesson
* teacher bigint The teacher for these lessons
* description varchar( 2048 ) The description for the lessons
* substitute bool DEFAULT false Indicates if the lesson is a taken by a sobstitute teacher not owning the teacher post
* from_time time Time to begin the lessons
* to_time time Time to end the lessons
  assignment varchar( 2048 ) assignments gaven during the lesson
  period tsrange Period for the lessons
Indexes
lessons_pk ON lesson
lessons_fx_classroom ON classroom
lessons_fx_subject ON subject
lessons_fx_teacher ON teacher
Foreign Keys
lessons_fk_classroom ( classroom ) ref classrooms (classroom)
lessons_fk_teacher ( teacher ) ref persons (person)
lessons_fk_subject ( subject ) ref subjects (subject)
Constraints
  lessons_ck_to_time to_time > from_time
Triggers
  lessons_iu
  lessons_iu


Table messages

IndexesField NameData TypeDescription
* message bigserial Unique identification code for the row
* conversation bigint Conversation
* written_on timestamp DEFAULT now() Date when the message was written
* message_text varchar( 2048 ) The text of the message
* person bigint La person fisica che ha scritto il message
Indexes
messages_pk ON message
messages_uq_from_time ON conversation, person, written_on
messages_fx_conversation ON conversation
messages_fx_from_time ON person
Foreign Keys
messages_fk_conversation ( conversation ) ref conversations (conversation)
messages_fk_person ( person ) ref persons (person)
Triggers
  messages_iu
  messages_iu


Table messages_read

IndexesField NameData TypeDescription
* message_read bigserial Unique identification code for the row
* message bigint Message to read
* person bigint The person that rode the message
  read_on timestamp Date when the message has been red
Indexes
messages_read_pk ON message_read
messages_read_uq_read_on ON message, person
libretti_messages_read_fx_person ON person
libretti_messages_read_fx_school_record_mess ON message
Foreign Keys
messages_read_fk_message ( message ) ref messages (message)
messages_read_fk_person ( person ) ref persons (person)
Triggers
  messages_read_iu
  messages_read_iu


Table metrics

IndexesField NameData TypeDescription
* metric bigserial Unique identification code for the row
* school bigint School with these metrics
* description varchar( 160 ) The description for the metrics
* sufficiency smallint DEFAULT 600 Indicates the value to reach the sufficiency
Indexes
metrics_pk ON metric
metrics_uq_description ON school, description
metrics_fx_school ON school
Foreign Keys
metrics_fk_school ( school ) ref schools (school)


Table notes

IndexesField NameData TypeDescription
* note bigserial Unique identification code for the row
student bigint The student with these notes
* description varchar( 2048 ) The description of the note
* teacher bigint The teacher that insert this note
* disciplinary bool Indicates that the annotation is of disciplinary type so it will be reported at school_record for the view signature of a parent. The annotation is for all the classroom unless it isn't indicated a single student. If it's needed to do a disciplinary note (also normal) to two or more students is necessary to insert the note for everyone.
* on_date date Note insert on date
* at_time time When the note was insert
* to_approve bool DEFAULT false Indicates that is requested the check from the student (if adult) or from someone that have the paternity authorition and have requested to be warned. If it isn't specified the check has to be requested for all the class, but if the note is disciplinary and is missing the student, the check will be requested only for present students
* classroom bigint Indicates if the note is for all the classroom
Indexes
notes_pk ON note
notes_uq_on_date_at_time ON classroom, student, on_date, at_time
notes_fx_classroom ON classroom
notes_fx_classroom_student ON classroom, student
notes_fx_teacher ON teacher
Foreign Keys
notes_fk_classroom ( classroom ) ref classrooms (classroom)
notes_fk_classroom_student ( classroom, student ) ref classrooms_students (classroom, student)
notes_fk_teacher ( teacher ) ref persons (person)
Constraints
  notes_ck_to_approve ((disciplinary = false) AND (to_approve = false)) OR ((disciplinary = false) AND (to_approve = true)) OR ((disciplinary = true) AND (to_approve = true))
Triggers
  notes_iu
  notes_iu


Table out_of_classrooms

Indicates when a student isn't present in classroom but don't has to be considered absent, example for sports activity

IndexesField NameData TypeDescription
* out_of_classroom bigserial Unique identification code for the row
* school_operator bigint School operator with out of classrooms
* description varchar( 160 ) The description for whom out of classroom
* on_date date The date when out of classroom
* from_time time at_time of leaving
* to_time time at_time of return
classroom_student bigint The classroom of the student
Indexes
out_of_classrooms_pk ON out_of_classroom
out_of_classrooms_uq_classroom_student_on_date ON classroom_student, on_date
out_of_classrooms_fx_classroom_student ON classroom_student
out_of_classrooms_fx_school_operator ON school_operator
Foreign Keys
out_of_classrooms_fk_classroom_student ( classroom_student ) ref classrooms_students (classroom_student)
out_of_classrooms_fk_school_operator ( school_operator ) ref persons (person)
Constraints
  out_of_classrooms_ck_to_time to_time > from_time
Triggers
  out_of_classrooms_iu
  out_of_classrooms_iu


Table persons

IndexesField NameData TypeDescription
* person bigserial Unique identification code for the row
* name varchar( 60 ) Name of the person
* surname varchar( 60 ) The surname of this person
  born date The date when person born
  deceased date Check if the person is deceased
country_of_birth smallint Country of birth for the person
tax_code char( 16 ) The tax code for every person
* sex sex Sex of the person
  photo image The photo for these persons
school bigint The school for this person
  sidi bigint Sidi of the person
city_of_birth char( 4 ) The city of birth for the person
  thumbnail image Thumbnail of the person
  note text contains addtional informatio about person
usename name The usename of these persons
Indexes
persons_pk ON person
persons_uq_school_tax_code ON school, tax_code
persons_uq_usename ON school, usename
persons_fx_city_of_birth ON city_of_birth
persons_fx_country_of_birth ON country_of_birth
persons_fx_school ON school
persons_fx_usename ON usename
persons_ix_usename_school ON usename, school
Foreign Keys
persons_fk_city_of_birth ( city_of_birth ) ref cities (city)
persons_fk_country_of_birth ( country_of_birth ) ref countries (country)
persons_fk_school ( school ) ref schools (school)
persons_fk_usename ( usename ) ref usenames_ex (usename)


Table persons_addresses

Contains the address for every person registered in a school

IndexesField NameData TypeDescription
* person_address bigserial Unique identification code for the row
* person bigint Person that have that address
* address_type address_type The address type of the person
* street varchar( 160 ) The street
* zip_code varchar( 15 ) The zip code
* city char( 4 ) City of the person
Indexes
persons_addresses_pk ON person_address
persons_addresses_uq_indirizzo ON person, street, zip_code, city
persons_addresses_fx_city ON city
persons_addresses_fx_person ON person
Foreign Keys
persons_addresses_fk_city ( city ) ref cities (city)
persons_addresses_fk_person ( person ) ref persons (person)


Table persons_relations

Indicates the relations abount persons: tipically student(coloumn person) will have for relationship 'Parent' the father (person_related_to) for indicates the mother will add a row with same values as said before but looking that this time to insert in coloumn person_related_to the code of person that identifies the mother

IndexesField NameData TypeDescription
* person_relation bigserial The relation of this person with the student
* person bigint Person that has this relation
* person_related_to bigint Indicates the person related to the student
* sign_request bool DEFAULT true Indicates that, in case of classroom notes (example: museum visit) or disciplinary notes, the teacher has to check if the parent checked the note
* relationship relationships The relationship about the parent and the student
  can_do_explanation bool DEFAULT false Indicates if can do explanations for the student
Indexes
persons_relations_pk ON person_relation
persons_relations_uq_person ON person, relationship, person_related_to
persons_relations_fx_person ON person
persons_relations_fx_person_related_to ON person_related_to
Foreign Keys
persons_relations_fk_person ( person ) ref persons (person)
persons_relations_fk_person_related_to ( person_related_to ) ref persons (person)


Table persons_roles

Contains the list of person with their role

IndexesField NameData TypeDescription
* person_role bigserial Unique identification code for the row
* person bigint The person with these roles
* role role The role of a person
Indexes
persons_roles_pk ON person_role
persons_roles_uq_person ON person, role
idx_persons_roles ON person
Foreign Keys
persons_roles_fk_person ( person ) ref persons (person)


Table qualifications

Describes for single school the knowledges, competences and skills. Adding all in only a table it's duplicated the term qualification to be generic compared to declicountry that can have: Expertise, knowledge, skills

IndexesField NameData TypeDescription
* qualification bigserial Unique identification code for the row
* school bigint The school for the qualifications
* name varchar( 160 ) The name of the qualification
* description varchar( 4000 ) Description of the qualification
* metric bigint The metric used for the qualifications
* type qualificationtion_types The type for these qualifications
qualification_parent bigint It's needed for create the qualification tree, in this coloumn will be indicated the family dependency
Indexes
qualifications_pk ON qualification
qualifications_uq_name ON school, name
qualificationtions_fx_metric ON metric
qualificationtions_fx_riferimento ON qualification_parent
qualificationtions_fx_school ON school
Foreign Keys
qualifications_fk_metric ( metric ) ref metrics (metric)
qualifications_fk_qualification_parent ( qualification_parent ) ref qualifications (qualification)
qualifications_fk_school ( school ) ref schools (school)


Table regions

Contains all avaible regions

IndexesField NameData TypeDescription
* region smallint Unique identification code for the row
* description varchar( 160 ) The description for the region
  geographical_area geographical_area The geographical area of the regions
Indexes
regions_pk ON region
regions_uq_description ON description


Table school_years

Rapreseant the school year and it's separated by schools

IndexesField NameData TypeDescription
* school_year bigserial Unique identification code for the row
* school bigint School
* description varchar( 160 ) The description for the school year
duration daterange The duration for the school year
  lessons_duration daterange The duration of the lessons
Indexes
school_years_pk ON school_year
school_years_uq_description ON school, description
school_years_ex_duration ON school, duration
school_years_fx_school ON school
Foreign Keys
school_years_fk_school ( school ) ref schools (school)
Constraints
  school_years_ck_duration duration @> lessons_duration


Table schools

An institution for the instruction of children or people under college age

IndexesField NameData TypeDescription
* school bigserial Uniquely identifies the table row
* description varchar( 160 ) Description for the school
* processing_code varchar( 160 ) A code that identify the school on the government information system
* mnemonic varchar( 30 ) Short description to be use as code
* example bool DEFAULT false It indicates that the data have been inserted to be an example of the use of the data base
  logo image Picture to be used as a logo
behavior bigint Indicates the subject used for the behavior
Indexes
schools_pk ON school
schools_uq_description ON description
schools_uq_mnemonic ON mnemonic
schools_uq_processing_code ON processing_code, example
schools_fk_behavior ON behavior
Foreign Keys
schools_fk_behavior ( behavior ) ref subjects (subject)
Constraints
  schools_min_description length((description)::text) > 1
  schools_min_mnemonic length((mnemonic)::text) > 1
  schools_min_processing_code length((processing_code)::text) > 1
Triggers
  schools_iu
  schools_iu


Table signatures

Contains info of the signature for every teacher

IndexesField NameData TypeDescription
* signature bigserial Unique identification code for the row
* classroom bigint The classroom of the student
* teacher bigint The teacher that did that signature
* on_date date Date of the signature
at_time time When the signature was insert
Indexes
signatures_pk ON signature
signatures_uq_classroom ON classroom, teacher, on_date, at_time
signatures_fx_classroom ON classroom
signatures_fx_teacher ON teacher
Foreign Keys
signatures_fk_classroom ( classroom ) ref classrooms (classroom)
signatures_fk_teacher ( teacher ) ref persons (person)
Triggers
  signatures_iu
  signatures_iu


Table subjects

Contains subjects for every school

IndexesField NameData TypeDescription
* subject bigserial Unique identification code for the row
* school bigint The school with these subjects
* description varchar( 160 ) The description of a subject
Indexes
subjects_pk ON subject
subjects_uq_description ON school, description
subjects_fx_school ON school
Foreign Keys
subjects_fk_school ( school ) ref schools (school)


Table teachears_notes

It does the same fuunctions of notes table but for teacher register. The only difference is that isn't necessary to replicate the coloumn 'disciplinary' too because the disciplinary notes have to be done only on classroom register unless these notes are mainly for private use of the teacher.

IndexesField NameData TypeDescription
* teacher_notes bigserial The teacher notes
student bigint The student with this note
* description varchar( 2048 ) Description for the note
* teacher bigint Teacher that insert the note
* on_date date When the note has been added
at_time time When the teacher insert the note
* classroom bigint Classroom for the teacher notes
Indexes
teachears_notes_pk ON teacher_notes
teachears_notes_uq_on_date_at_time ON classroom, student, on_date, at_time
teachears_notes_fx_classroom ON classroom
teachears_notes_fx_student ON student
teachears_notes_fx_teacher ON teacher
Foreign Keys
teachears_notes_fk_classroom ( classroom ) ref classrooms (classroom)
teachears_notes_fk_student ( student ) ref persons (person)
teachears_notes_fk_teacher ( teacher ) ref persons (person)
Triggers
  teachears_notes_iu
  teachears_notes_iu


Table topics

Contains the object topics of a valutation

IndexesField NameData TypeDescription
* topic bigserial Unique identification code for the row
* subject bigint Subject for the topic
* description varchar( 160 ) The description of the topic
course_year course_year The course year with these topics
* degree bigint The degree for the topic
Indexes
topics_pk ON topic
topics_uq_description ON degree, course_year, subject, description
topics_fx_degree ON degree
topics_fx_subject ON subject
Foreign Keys
topics_fk_degree ( degree ) ref degrees (degree)
topics_fk_subject ( subject ) ref subjects (subject)
Triggers
  topics_iu
  topics_iu


Table usenames_ex

Add informations to usename's system table usefull only to scuola247

IndexesField NameData TypeDescription
* usename name The usename for the person
  token varchar( 1024 ) Used for the password reset
  language "utility"."language" Preferred language for the user
Indexes
usenames_ex_pk ON usename
Triggers
  usenames_ex_iu


Table usenames_schools

record schools accessible to the user

IndexesField NameData TypeDescription
* usename_school bigserial Unique identification code for the row
* usename name The session's usename
* school bigint School enabled for the the usename
Indexes
usenames_schools_pk ON usename_school
usenames_schools_uq_usename_school ON usename, school
usenames_schools_fx_school ON school
Foreign Keys
usenames_schools_fk_school ( school ) ref schools (school)
Triggers
  usenames_schools_iu


Table valutations

Contains valutations of all students did by all teachers

IndexesField NameData TypeDescription
* valutation bigserial Unique identification code for the row
* subject bigint The subject with these valutations
* grade_type bigint The type of grade
topic bigint The topic for these valutations
* grade bigint The grade for the valutation
  evaluation varchar( 160 ) The evaluation
* private bool DEFAULT false Indicates that the grade is visible for only the teacher that has insert it
* teacher bigint The teacher coloumn has been inserted because the techer during the school year could be changed, in this mode will be tracked who insert the value
* on_date date When the valutation was insert
note bigint The note associated to the valutation
* classroom_student bigint
Indexes
valutations_pk ON valutation
valutations_fx_grade ON grade
valutations_fx_grade_type ON grade_type
valutations_fx_note ON note
valutations_fx_teacher ON teacher
valutations_fx_topic ON topic
valutations_fx_classroom_student ON classroom_student
Foreign Keys
valutations_fk_grade_type ( grade_type ) ref grade_types (grade_type)
valutations_fk_grade ( grade ) ref grades (grade)
valutations_fk_note ( note ) ref notes (note)
valutations_fk_teacher ( teacher ) ref persons (person)
valutations_fk_topic ( topic ) ref topics (topic)
valutations_fk_classroom_student ( classroom_student ) ref classrooms_students (classroom_student)
Triggers
  valutations_iu
  valutations_iu


Table valutations_qualifications

For every valution inserted in valutations table is possible to connect valutation too of qualifications connected that are saved here

IndexesField NameData TypeDescription
* valutation_qualificationtion bigserial Unique identification code for the row
* valutation bigint The valutation of a qualification
* qualification bigint Unique identification code for the row
* grade bigint Grade of the qualification
  note varchar( 2048 ) The note for the qualification
Indexes
valutations_qualificationtions_pk ON valutation_qualificationtion
valutations_qualificationtions_uq_qualification ON valutation, qualification
valutations_qualificationtions_fx_grade ON grade
valutations_qualificationtions_fx_qualification ON qualification
valutations_qualificationtions_fx_valutation ON valutation
Foreign Keys
valutations_qualificationtions_fk_grade ( grade ) ref grades (grade)
valutations_qualificationtions_fk_valutation ( valutation ) ref valutations (valutation)
valutations_qualificationtions_fk_qualification ( qualification ) ref qualifications (qualification)
Triggers
  valutations_qualifications_iu


Table weekly_timetables

Contains the weekly timetables for every school

IndexesField NameData TypeDescription
* weekly_timetable bigserial Unique identification code for the row
* classroom bigint The classroom with this weekly timetable
description varchar( 160 ) The description for the weekly timetable
Indexes
weekly_timetable_pk ON weekly_timetable
weekly_timetable_uq_description ON classroom, description
weekly_timetable_fx_classroom ON classroom
Foreign Keys
weekly_timetable_fk_classroom ( classroom ) ref classrooms (classroom)


Table weekly_timetables_days

IndexesField NameData TypeDescription
* weekly_timetable_day bigserial Unique identification code for the row
* weekly_timetable bigint Unique identification code for the row
* weekday "utility"."week_day" Weekday for the timetable
teacher bigint Teacher for the weekly timetable
subject bigint Subject in the weekly timetable
* team_teaching smallint DEFAULT 1 Indicates the amount of team_teaching (1 first teacher, 2 second teacher ecc.) if there's only a teacher you have to insert 1
* from_time time When the weekly timetable starts
* to_time time Time when the timetable finish
Indexes
weekly_timetables_days_pk ON weekly_timetable_day
weekly_timetables_days_uq_weekly_timetable ON weekly_timetable, weekday, teacher, subject, from_time
weekly_timetables_days_fx_subject ON subject
weekly_timetables_days_fx_teacher ON teacher
weekly_timetables_days_fx_weekly_timetable ON weekly_timetable
Foreign Keys
weekly_timetables_days_fk_teacher ( teacher ) ref persons (person)
weekly_timetables_days_fk_subject ( subject ) ref subjects (subject)
weekly_timetables_days_fk_weekly_timetable ( weekly_timetable ) ref weekly_timetables (weekly_timetable)
Constraints
  weekly_timetables_days_ck_teacher_subject (teacher IS NOT NULL) OR (subject IS NOT NULL)

Powered by DbSchema