(i) Transform the ERD in Figure Q3(a)(i) into a logical model. Figure Q3(a)(i) (ii) Refer to the description of the columns and relationships and then code the appropriate SQL statements to create the tables, derived from the ERD in Figure Q3(a)(i). You must use the same table names and the same column names as given in Figure Q3 (a) (i) or your statements will be marked as incorrect. List your SQL statements in the order that they should be executed. Description for columns: - studentId is a whole number starting from 1000000 with step increment 1 and is auto-generated. - program is at most 50 characters and is one of these values: Accountancy, Finance, Information and Communication Technology, Business Administration. - courseCode is exactly 5 characters starting with two letters (A-Z) followed by 3 digits (09). - courseName is at most 50 characters long. - assignmentNumber is a running number for each course and can be generated with a trigger. - weight is an integer that is greater than 0 , - dueDate is a date at least one week after today's date. - dateSubmitted is a date with default value today's date, - score is a decimal number between 0 and 100 . - All columns are required except for dateSubmitted and score Constraints Refer to Figure Q3(a) (i) for the minimum and maximum cardinalities. - enrols relationship - delete cascade and update disallowed for actions on Student and delete/update disallowed for actions on Course. - has relationship - delete and update cascade. - submits relationship - delete cascade and update disallowed. - isFor relationship - delete and update disallowed. (iii) Use the data in Figure Q3(a)(iii) to populate the tables created in Q3(a)(ii). Arrange your SQL statements in the order that they should be executed. Fioure Q3 (a)(iii) (b) Formulate SQL queries to display the given result sets: (i) Programs and the corresponding number of students, sorted by descending order of number of students. (ii) Students in Accounting, Business Administration or Finance who enrolled in BS100 but did not submit assignment number 3 of BS100. (iii) The most popular programs (iv) The score of students accumulated so far for courses enrolled, sorted by ascending course code, descending accumulated score and ascending student id. Show accumulated score with one digit after decimal point. The accumulated score a student has for a course is computed using the formula: i=1n(weighti)i=1n(weightiscorei) where weight ti is the weight of assignment i1, score i is the score earned by student for assignment i and n is the number of assignments the course has. (v) Students who were late more than once in submitting assignments, regardless of the course. (17 marks) (c) Write TWO (2) triggers to implement the following actions: - ONE (1) trigger to ensure that the sum of the weights of assignments for a course does not exceed 100 whenever a new assignment is added. Furthermore, assignments should be added without the need to specify the assignment number. The next running number for the course will be used as the assignment number. - When an assignment is successfully removed, the later assignment(s), if any, should have the assignment number updated to one less. For example, if assignment number 1 is removed successfully, then assignment 2 becomes assignment 1 , assignment 3 becomes assignment 2 , and so on, assuming there were 3 assignments before the removal. Show screenshots of your test queries and output. State any assumptions you made in implementing the trigger. (10 marks)
