Task 3: Procedure, Functions Triggers and Active Rules in Oracle 50%

Recently you have been appointed as a Database Administrator (DBA) in a University. As part of your job, you have to manage, monitor and log the database activities in their Student Management Database. The University needs to record the details of the students, courses, enrolment, grades, staff and sessions (sections). You must restrict data updates to working hours to protect the data.

You may have to add more sample data to test the functionalities of your procedures and to produce meaningful test reports.

Task 2.a: Grade Report:

The University needs to produce different reports related to the course, students and their performance, results etc.

University has to produce the Final Mark sheet for students. This should display student details, description of the courses that a student has enrolled on, Final Mark they have received for that course. Final marks must be calculated considering different types of assessment (quiz, examination, mid-term etc.), the number of assessments of the given type (3 quizzes) and their percentages towards the final grade.

10%

The University likes to get statistical reports on courses which include the description of the course, sections start date, name of the instructor who manages the section, number of students enrolled for section, the highest mark, lowest mark, average mark, standard deviation, number of 1^st (Final mark: 80 -100), 2^nd (Final mark: 70 79), 3^rd (Final mark: 60 69) number of failures (Final mark: 1 -59) etc.

10%

University likes to perform location-based analysis on recruitment, student progress etc.

Using the data from relevant tables, produce a report listing the number of students enrolled on specific courses, by state, city and postcode. You should be able to list popular courses by your preferred location details (i.e., by State, City or PostCode)

Produce a report detailing the results that the students achieved, display the results by state, city and postcode, You should be able to provide the highest performing course by state, city and postcode area as requested by the University.

(a & b - 20%)

Create appropriate PL/SQL procedures to produce the above-listed reports. Please ensure that all necessary details are included and displayed in the report.

Task 2.b: Active Rules Using Triggers

The University student data centre that is responsible for entering student details and enrolment details works from Monday 9.00 am Friday 5.00 pm. The system must be protected (dont allow any changes on appropriate tables) and only allow minimum operations from Friday 5.00 pm to Monday 8:00 am. For example, students details or enrolment details are only added or amended during office hours. Database activities are monitored all the time, create an active rule to stop such activities and display appropriate messages.

5%

University can only accommodate a limited number of students for each section.

Create an active rule to display an appropriate message on the screen to warn the DBA while the enrolment is nearing its capacity and also when it has reached the capacity limit.

5%