Project Description: Suppose we are to create hypothetical grades information for different schools in a university for analysis purposes. Each school has its own distribution of grades based on a probability set. Please write a function using procedural language PL pgSQL to randomly assign a grade to each record (for a student) in the table simulated_records" based on the grade probability distribution for each school. Your function will be based on the follow three tables. Please do the following steps to create those tables by using the queries provided in the Package.zip" file: 1. Create a table called "school_probs by executing query 1_create_school_probs.sql 2. Insert values into table "school_probs by executing query 2_insert_school_probs.sql 3. Create a table called grade_values by executing query 3_create_grade_values.sql 4. Insert values into table "grade_values" by executing query 4_insert_grade_values.sql 5. Create a table called simulated_records by executing query 5_create_simulated_records.sql 6. Insert values into table "simulated_records by executing query 6_insert_simulated_records.sql TABEL 3: simulated_records record_id school grade [PK] bigint text text 1 CL 2 CEC 3 CEC w N 4 CEC CAA 5 6 6 CAS 7 TABEL 1: school_probs school_code school probs [PK] bigint text numeric[] 1 CAA {0.05,0.08,0.18,0-3,0.11,0.28) 2 CAS {0.06,0.1,0.295,0.36,0.12,0.065) 3 CBA {0.05,0.11,0.35,0.32,0.12,0.05) 4 CE {0.04,0.05,0.08,0.3,0.42,0.11} 5 CEC {0.05,0.11,0.35,0.32,0.12,0.05) HC {0.12,0.1,0.23,0.4,0.06,0.09} 7 CL {0.07,0.09,0.24,0.4,0.12,0.08} CNHS {0.08,0.1,0.295,0.34,0.12,0.065) SJMC {0.09,0.11,0.31,0.32,0.12,0.05) TABEL 2: grade_values id score grade [PK] integer text text 1 95 - 100 2 90 - 94 A- 3 80 - 89 4 70 - 79 5 60 - 69 6 0-59 8 8 9 9 CAA CAA CL 10 11 12 CBA CAA HC A 13 14 CL CNHS B+ 15 16 17 CBA B CE D Some explanations 1 CAA {0.05.0.08.0.18.0.3.0.11.028} The first record in the school_probs table means the grade distribution for the students at the school CAA is as follows. Score Percentage Score Number of Students 95 - 100 5% 95 - 100 5 90 - 94 8% If the total number of students at CAA is 100 90 - 94 8 80 - 89 18% 80 - 89 18 70 - 79 30% 70 - 79 30 60 - 69 11% 60 - 69 11 0-59 28% 0 - 59 28 The idea is* not* *totally* randomly giving a grade to each student. For each school, the numbers of students getting each grade are fixed by the probability. The "randomly assign" means the grade assigned to which student is random as long as the total number in each grade matches the probability distribution (for example, 5 for scores 95 to 100). You can base on the idea of how to calculate the number of students for each grade in every school to find out how to randomly assign the grades to the students. Besides, we are expecting your function without any hard code values. For example, you should get each school's name from the table instead of define the name as a variable by yourself (e.g., school_name=CAA). If you failed to do that you may not be able to get full credits. Here is an example for one of the school You can use a query to find the total number of students at school "CAA" If it is 198, then the probability distribution will be You can use a query to find the total number of students at school "CAA" If it is 198, then the probability distribution will be (0.05*198.0.08*198.0.18*198.0.3*198.0.11*198.028*198) Then we will have 9.9 students to get A. 15.84 students to get A- 35.64 students to get B+, 59.4 students to get B. 21.78 students to get C. and 55.44 students to get D. As you know, the student number must be an integer, so you can have 10 students get A instead of 9.9. 16 students get A- instead of 15.84. But the total number of students will not change, which means if you have one additional student to get A then you should have one less student to get B or other grade. After you get the number of students for each grade, you can randomly choose which record you will assign the grade. Submission For all the students, you need to save your implemented function as a SQL file and upload it to Canvas. Project Description: Suppose we are to create hypothetical grades information for different schools in a university for analysis purposes. Each school has its own distribution of grades based on a probability set. Please write a function using procedural language PL pgSQL to randomly assign a grade to each record (for a student) in the table simulated_records" based on the grade probability distribution for each school. Your function will be based on the follow three tables. Please do the following steps to create those tables by using the queries provided in the Package.zip" file: 1. Create a table called "school_probs by executing query 1_create_school_probs.sql 2. Insert values into table "school_probs by executing query 2_insert_school_probs.sql 3. Create a table called grade_values by executing query 3_create_grade_values.sql 4. Insert values into table "grade_values" by executing query 4_insert_grade_values.sql 5. Create a table called simulated_records by executing query 5_create_simulated_records.sql 6. Insert values into table "simulated_records by executing query 6_insert_simulated_records.sql TABEL 3: simulated_records record_id school grade [PK] bigint text text 1 CL 2 CEC 3 CEC w N 4 CEC CAA 5 6 6 CAS 7 TABEL 1: school_probs school_code school probs [PK] bigint text numeric[] 1 CAA {0.05,0.08,0.18,0-3,0.11,0.28) 2 CAS {0.06,0.1,0.295,0.36,0.12,0.065) 3 CBA {0.05,0.11,0.35,0.32,0.12,0.05) 4 CE {0.04,0.05,0.08,0.3,0.42,0.11} 5 CEC {0.05,0.11,0.35,0.32,0.12,0.05) HC {0.12,0.1,0.23,0.4,0.06,0.09} 7 CL {0.07,0.09,0.24,0.4,0.12,0.08} CNHS {0.08,0.1,0.295,0.34,0.12,0.065) SJMC {0.09,0.11,0.31,0.32,0.12,0.05) TABEL 2: grade_values id score grade [PK] integer text text 1 95 - 100 2 90 - 94 A- 3 80 - 89 4 70 - 79 5 60 - 69 6 0-59 8 8 9 9 CAA CAA CL 10 11 12 CBA CAA HC A 13 14 CL CNHS B+ 15 16 17 CBA B CE D Some explanations 1 CAA {0.05.0.08.0.18.0.3.0.11.028} The first record in the school_probs table means the grade distribution for the students at the school CAA is as follows. Score Percentage Score Number of Students 95 - 100 5% 95 - 100 5 90 - 94 8% If the total number of students at CAA is 100 90 - 94 8 80 - 89 18% 80 - 89 18 70 - 79 30% 70 - 79 30 60 - 69 11% 60 - 69 11 0-59 28% 0 - 59 28 The idea is* not* *totally* randomly giving a grade to each student. For each school, the numbers of students getting each grade are fixed by the probability. The "randomly assign" means the grade assigned to which student is random as long as the total number in each grade matches the probability distribution (for example, 5 for scores 95 to 100). You can base on the idea of how to calculate the number of students for each grade in every school to find out how to randomly assign the grades to the students. Besides, we are expecting your function without any hard code values. For example, you should get each school's name from the table instead of define the name as a variable by yourself (e.g., school_name=CAA). If you failed to do that you may not be able to get full credits. Here is an example for one of the school You can use a query to find the total number of students at school "CAA" If it is 198, then the probability distribution will be You can use a query to find the total number of students at school "CAA" If it is 198, then the probability distribution will be (0.05*198.0.08*198.0.18*198.0.3*198.0.11*198.028*198) Then we will have 9.9 students to get A. 15.84 students to get A- 35.64 students to get B+, 59.4 students to get B. 21.78 students to get C. and 55.44 students to get D. As you know, the student number must be an integer, so you can have 10 students get A instead of 9.9. 16 students get A- instead of 15.84. But the total number of students will not change, which means if you have one additional student to get A then you should have one less student to get B or other grade. After you get the number of students for each grade, you can randomly choose which record you will assign the grade. Submission For all the students, you need to save your implemented function as a SQL file and upload it to Canvas