# Procedures {:toc} - Upload all .sql in the __/highschool/procedures/__ folder in your Git repository. - Presentation will be taken into account. Using <a href="/DAM-BD/UD1/exercicis/01_ddl_highschool.html" target="_blank">the highschool database</a> __Files:__ - <a href="/itb/DAM-BD/files/en/highschool/schema/create_schema_highschool.sql" download="create_schema_highschool.sql">create_schema_highschool.sql</a> - <a href="/itb/DAM-BD/files/en/highschool/schema/create_schema_highschool_material.sql" download="create_schema_highschool_material.sql">create_schema_highschool_material.sql</a> - <a href="/itb/DAM-BD/files/en/highschool/data/insert_data_highschool.sql" download="insert_data_highschool.sql">insert_data_highschool.sql</a> - <a href="/itb/DAM-BD/files/en/highschool/data/insert_data_highschool_material.sql" download="insert_data_highschool_material.sql">insert_data_highschool_material.sql</a> __Tests__: - <a href="/itb/DAM-BD/files/en/highschool/tests/tests_highschool_procedures.yml" download="tests_highschool_procedures.yml">tests_highschool_procedures.yml</a> <div class="mermaid"> erDiagram MATERIAL { int material_id PK varchar100 name int amount } MATERIAL_HISTORY }o--|| TEACHER : "" MATERIAL_HISTORY }o--|| MATERIAL : "" MATERIAL_HISTORY { int material_id FK varchar9 teacherDNI FK int amount timestamp timestamp } PERSON { char9 DNI PK varchar50 name varchar50 surname varchar30 phone date birth_date varchar100 personal_email varchar100 corporate_email } TEACHER |o--|| PERSON: "" TEACHER { char9 DNI FK } STUDENT |o--|| PERSON: "" STUDENT { char9 DNI FK char9 NIA int course_id FK char group_letter FK } GROUP |o--|| TEACHER: "is tutor of" GROUP ||--|{ STUDENT: "belogns" GROUP }|--|| COURSE: "" GROUP { int course_id FK char letter PK char9 tutorDNI FK } ENROLLED }|--|| STUDENT : "" ENROLLED }|--|| SUBJECT: "" ENROLLED }|--|| YEAR: "" ENROLLED { char9 studentDNI FK char4 subject_id FK int year FK decimal4_2 grade } YEAR { int start_year PK int end_year } TEACHES }|--|| TEACHER: "" TEACHES }|--|| SUBJECT: "" TEACHES }|--|| YEAR: "" TEACHES { char9 teacherDNI FK char4 subject_id FK int year FK } FAMILY { int family_id PK varchar100 name } COURSE }o--|| FAMILY : "" COURSE { int course_id PK int family_id FK varchar100 name varchar6 acronym enum level } SUBJECT }o--|| COURSE : "" SUBJECT { char4 subject_id PK int course_id FK varchar100 name varchar6 acronym int etcs int hours } </div> Create the following procedures: ## create_procedure_retrieve_material.sql Create a procedure named __retrieve_material(teacherDNI, material_id, amount)__ that makes a transaction so the specified teacher retrieves the specified amount of material. The procedure must: - Check if there is enough quantity of the material specified. If there isn't enough, the teacher will retrieve any material available. - Lock the selected material until the end of the procedure. - Insert a log into the __material_history__ table with the amount of material retrieved. The procedure won't insert a log if no material is retrieved. ## create_procedure_grade_student.sql Create a procedure named __grade_student(studentDNI, subject_id, grade, year)__ that sets the specified grade to the student in a specific subject and year. - If there is no record in the __enrolled__, the procedure must create it. - If there is a record in the __enrolled__, the procedure must update it with the specified grade. ## create_procedure_split_group.sql Create a procedure named __split_group(course_acronym, letter)__ that will split the selected group in two parts. - Students will be assigned to each group based on their DNI. - The fitst half will remain on the original group. - The second half will be moved to the new group. - If the number of students is odd, the new group will have one student more. - The letter of the new group will be the next one available. - The tutor of the new group will be the teacher that: - Is not tutor of any group. - Teaches the most hours in that course. - The total weekly hours of the teacher is lower than 18 hours. (There are 33 weeks in a academic year). - If there isn't any teacher with the previous constraints, the first teacher (ordered by DNI) that is not tutor of any group will be selected.