Write a procedure that takes a date and outputs

Assignment Help Database Management System

Reference no: EM132688355

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 an onymous 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's 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: Free Null
Session 4: TeacherName RoomNo SubjectTitle Session 5: TeacherName RoomNo SubjectTitle Session 6: TeacherName RoomNo SubjectTitle
Write an interactive anonymous block with the procedure call. Test your program works correctly.

Instructions for Part B
• For question 1 - 5, Use Oracle or SAS Studio to run the SQL Queries
• You may put all the query commands for Part B into ONE notepad file. However you must place appropriate labels (comments) in this file e,g. Part B Q2

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:- Database Application.rar

Reference no: EM132688355

Questions Cloud

Which at the end of the first year the adjusting entry : $17 000 which is to be depreciated by 20% a year. At the end of the first year the adjusting entry to record depreciation is which of the following?

Which as a result of the error : Billabong Company neglected to adjust the Office Supplies (asset) account for the amount of supplies used up during the year. As a result of this error

Calculate the fund assessable income from investments : Calculate the fund's assessable income from investments for the 2017/18 tax year. Unfranked Dividends from listed companies$ 12,450

Health promotion program : Identify the health promotion programs that you would want to investigate and get them approved by your instructor.

Write a procedure that takes a date and outputs : 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

Which example of a question raised by an external decision : Which example of a question raised by an external decision maker is? What will be the effect of increasing the selling price? / Is business financially sounds?

Find the translational speed at the top : The translational speed of the ball is 5.40 m/s at the bottom of the rise. Find the translational speed at the top.

Should sue pay the invoice within the discount period : Should Sue pay the invoice within the discount period or should she keep the $7,200 in the savings account and pay at the end of the credit period?

Competition in health care : Describe the different forms of competition that take place among various types of health care organizations.

User Account

All Pages