Write a function which will determine whether teacher exists

Assignment Help PL-SQL Programming

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

Reference no: EM132654306

Questions Cloud

Terrorism-related information at three levels of government : Do you feel that the creation of fusion centers has improved information sharing of terrorism-related information at all three levels of government?

Describe the ideological foundations of each of the groups : Describe the ideological foundations of each of the groups. Explain how the motivations and tactics differ between the domestic and the international terrorist.

How to make journal entry at the date of the bond purchase : Prepare the journal entries to record the interest revenue and recognition of fair value for 2020. Prepare the journal entry at the date of the bond purchase.

Indicate the effects of debit and credit on the accounts : For each of the following accounts indicate the effects of {a} a debit and {b} a credit on the accounts and {c} the normal balance of the account

Write a function which will determine whether teacher exists : Write a procedure that is passed a teacher's ID and returns the Name, Office Location and Specialisms. Also write an interactive anonymous block

What kinds of energy transformations are happening : What kinds of energy transformations are happening? What processes are involved to make these transformations possible?

Contrast the krebs and the calvin cycle : Compare and contrast the Krebs (citric acid cycle) and the Calvin cycle (light independent reactions) including (but not limited to): the purpose, the molecules

Develop a population of insects : Use the Gizmo to develop a population of insects that are well adapted to their environment. (Average fitness is above 90%.) Change the Mutation rate

Domestic terrorist attacks : Describe any known planning for the attack, such as gathering supplies, surveillance of the target, or any outside support / assistance.

User Account

All Pages