Database systems(help needed urgently) thanks.
Enrollment SIDSName SID CCode Midterm Final Total 100 Jones 100 BD445 70 77 150 Parks 150 BA200 65 89 200 Baker 93 Student Major Year Age History GR 21 Accounting | so 19 Math GR 50 History SR 50 Accounting SR 41 Math JR 20 Accounting GR 18 History FR 24 Course CCode CTime Room Norst Avg Total BA200 MWF9 SC110 3 BD445 MWF3 SC213 BF410 MWF SC213 1 CS150 MWF3 EA304 2 CS250 MWF12 EB210 2 250 Glass 71 93 88 98 300 Baker 99 100 78 350 Russell 400 Rye 450 Jones 200 BD445 200 CS250 300C5150 400 BA200 400 BD445 400 BF410 400 CS150 400 CS250 450 BA200 54 68 47 69 33 55 86 87 79 81 Notes: Course.Nbrst: number of students taking that course. Course.Avg Total: average of Totals of student-course enrollments from table Enrollment for a related course. Enrollment. Total: summation of 40% of Midterm and 60% of Final. 1) [60 pts] Implement a stored procedure sp_UpdateAvg Total Course.CCode) such that exec sp_UpdateAvg Total (Course.CCode) updates the field Avg Total of table Course. The field Avg Total is the average of Totals of student-course enrollments from table Enrollment for a related course. In order to calculate field Avg Total of table Course, field Total of table Enrollment is first to be computed by adding 30% of Midterm and 70% of Final. Create a table for logging errors before creating sp_Update Avg Total. Name the table as Log, table will have three fields: Logid, CCode, ErrCode. The primary key will be LogiD. The field Login will be an identity column, CCode will be corresponding Ccode that is to be entered, and ErrCode will be "r" for "calling invalid course" (i.e. not available in table Course), "NC" for "no enrolled course" (i.e. not available in table Enrollment). You have to enter appropriate records to the table Log. After these errors, the transaction will be rolled back. Data types for the fields are as follows: LogID int; CCode varchar(8); ErrCode varchar(2). 2) [40 pts) Write a trigger that implements the following: When a record of student (SID) and course (CCode) is inserted, deleted or updated from the table Enrollment, update the field Nbrst field of table Course. The field Nbrst is the number of students that is enrolled to the related course. Hint: you can assume there is only one insertion, deletion or update at a time. Enrollment SIDSName SID CCode Midterm Final Total 100 Jones 100 BD445 70 77 150 Parks 150 BA200 65 89 200 Baker 93 Student Major Year Age History GR 21 Accounting | so 19 Math GR 50 History SR 50 Accounting SR 41 Math JR 20 Accounting GR 18 History FR 24 Course CCode CTime Room Norst Avg Total BA200 MWF9 SC110 3 BD445 MWF3 SC213 BF410 MWF SC213 1 CS150 MWF3 EA304 2 CS250 MWF12 EB210 2 250 Glass 71 93 88 98 300 Baker 99 100 78 350 Russell 400 Rye 450 Jones 200 BD445 200 CS250 300C5150 400 BA200 400 BD445 400 BF410 400 CS150 400 CS250 450 BA200 54 68 47 69 33 55 86 87 79 81 Notes: Course.Nbrst: number of students taking that course. Course.Avg Total: average of Totals of student-course enrollments from table Enrollment for a related course. Enrollment. Total: summation of 40% of Midterm and 60% of Final. 1) [60 pts] Implement a stored procedure sp_UpdateAvg Total Course.CCode) such that exec sp_UpdateAvg Total (Course.CCode) updates the field Avg Total of table Course. The field Avg Total is the average of Totals of student-course enrollments from table Enrollment for a related course. In order to calculate field Avg Total of table Course, field Total of table Enrollment is first to be computed by adding 30% of Midterm and 70% of Final. Create a table for logging errors before creating sp_Update Avg Total. Name the table as Log, table will have three fields: Logid, CCode, ErrCode. The primary key will be LogiD. The field Login will be an identity column, CCode will be corresponding Ccode that is to be entered, and ErrCode will be "r" for "calling invalid course" (i.e. not available in table Course), "NC" for "no enrolled course" (i.e. not available in table Enrollment). You have to enter appropriate records to the table Log. After these errors, the transaction will be rolled back. Data types for the fields are as follows: LogID int; CCode varchar(8); ErrCode varchar(2). 2) [40 pts) Write a trigger that implements the following: When a record of student (SID) and course (CCode) is inserted, deleted or updated from the table Enrollment, update the field Nbrst field of table Course. The field Nbrst is the number of students that is enrolled to the related course. Hint: you can assume there is only one insertion, deletion or update at a time
