Hello, if you have any need, please feel free to consult us, this is my wechat: wx91due
COMP9311 Database Systems
Question 6 (4 marks)
Define a SQL view Q6(name) that gives the distinct names (people.name) of students who begin their first stream enrolments only in the second year of the program that the stream enrolments belong to, where the stream is offered by the College of fine arts (orgunits.longname = 'College of Fine Arts (COFA)').
● For simplicity, the second year of a program is defined as the earliest year of enrolment in that program + 1.
● If a student has enrolled in multiple programs, they should still be included in the result if at least one of those programs has their first stream enrolment occurring in the program's second year.
● If a student has enrolled in multiple streams in a program, only consider the one thatd enrolled earliest.
Question 7 (5 marks)
Define a SQL view Q7(name) that gives the name of students (people.name) who completed less than 18 UOC in a semester when enrolled in a program offered by 'School of the Arts and Media' (orgunits.longname= 'School of the Arts and Media'). Exclude semesters in which the student had fewer than 18 UOC remaining in their program.
For UOC calculation,
● a course counts toward a program only if the course enrolments and the program enrolmer occur in the same semester.
● For semester X, a student's finished UOC in a program is the total UOC completed for courses in earlier semesters (i.e., semesters that ended (semesters.ending) before semester X began (semesters.starting)).
● Ignore any program or course that does not provide UOC.
● For simplicity, grades or marks do not need to be considered when determining earned UOC
Question 8 (6 marks)
Define a SQL view Q8(ID) that gives the id of the courses where each lecture session's room has enough capacity to accommodate all enrolled students, but the combined capacity of all tutorial rooms is insufficient for all enrolled students. If a room does not provide a capacity, treat it as O. Exclude the courses that do not have tutorials. (Tutorial rooms are defined as rooms where class_types.description contains 'tutorial' regardless of case, lecture rooms are defined as rooms where class_types.description contains 'lecture' regardless of case)
Question 9 (6 marks)
Define a PL/pgSQL function Q9 (subject_code character(8)) that finds the semesters of courses with the given subject code which have the highest number of students achieving HD (mark >= 85) and the name of these students (people.name). The function should return a set of texts, where each line contains the semester name(semesters.longname) and the student's name(people.name) separated by a comma, for example:
Semester 1 2009,Emily Johnson
Semester 1 2009,Daniel Chen
Semester 2 2010,Sophia Martinez
...
Question 10 (7 marks)
Define a PL/pgSQL function Q10(student_id integer, start_date date, end_date date) that returns the students' wam during this period (rounded to 2 decimal places), weighting each course by its uoc. Include a course only if the input start_date is before the semester of that course and the input end_date is after that semester. Do not consider the course if its mark is not provided.
Hint: Use the ROUND() function to round the result to 2 decimal places.