Reference no: EM132654306
BIT358 Advance Database - Melbourne Polytechnic
Assessment - Database Application
Part A - PL/SQL
1. Write a procedure that is passed a teacher's ID and returns the Name, Office Location and Specialisms. Also write an interactive anonymous block with the procedure call. The anonymous block must print all the teacher's information. Test the program and print out your results to show that the program is working correctly.
2. Write a function which will determine whether a teacher exists. If the teacher table does not contain that teacher ID, return a FALSE value, otherwise return a TRUE value as Boolean. Print the appropriate message in the interactive calling program (anonymous block) based on the result. Test the program and print out your results through anonymous block.
3. Write a procedure that takes a teacher's ID and dates details and outputs the information given in the following report (in text format on command prompt). Write an interactive anonymous block with the procedure call. Test the program (show that the program is working correctly).
4. Create a database trigger entitled TIMETABLE_SECURITY_TIME_CHECK_1234 (where 1234 is your student id). This trigger should fire before any UPDATE or INSERT statement occurs on the TIMETABLED_SLOT table. This trigger will check the date and time attributes and if the day is a Saturday or a Sunday or if the time is outside the school business hours i.e. between 8am and 3pm a message "Out of school hours - this transaction has not completed by 1234" (where 1234 is your student id) should be displayed. Test this trigger.
Hint: Create a user defined exception to handle incorrect date/time entries.
5. (Self-Learning ) Alter table Teacher and add an additional attribute to the Teacher table to store details of the teacher's salary and run the following update commands.
Create a PL/SQL block to increase the salary of all teachers' following an annual pay rise. The increase is 15% for those teachers whose salary is less than $50,000 and 10% for those who earn $50,000 or more. You may use a Cursor FOR loop in this problem. Test and show evidence of all features of your answer.
Hints: Ensure your table contains sufficient and suitable data to demonstrate that this block works correctly. You will need to use the SELECT ... FOR UPDATE command . Also you will need to use the WHERE CURRENT OF clause
6. Write a procedure that takes a date and outputs details of the sessions running on that day.The details include the name of teacher teaching, Room no, Subject being taught and session number as shown below in the following report format:
Timetable for date: 04/04/2020
Session 1: TeacherName RoomNo SubjectTitle Session 2: TeacherName RoomNo SubjectTitle Session 3: TeacherName RoomNo SubjectTitle Session 4: Free
Session 5: Null
Session 6: Free
Write an interactive anonymous block with the procedure call. Test your program works correctly.
Part B SQL QUERIES
1. Using TOP-N list 2 teachers those teachers who are paid the most.
2. Display all teachers along with the sessions they are scheduled to teach for a particular day. Your query should also display those teachers who have NOT been allocated any sessions that day. You are to use one query only for this problem.
3. Which teachers has more specialisms than the teacher with name ‘Tom Wright'? List the teacher details and the total number of specialisms they have.
4. For each Session display the details of that session in date order with the date shown in the format
‘4 April 2020'.
5. There have been many data entry operator errors where data has been entered with Caps Lock on sometimes, other times with Caps Lock off and other times where the Caps Lock has been used intermittently. With this in mind display the details of all teachers who are able to teach ‘Maths'
Hint: You will need to use an SQL function for this.
Attachment:- Advance Database.rar