Databases | SQL | Oracle | MS Access Answers

Write a pl /SQL to determine whether salaries for members of the staff need to be adjusted or not. Depending on the work load (number of subjects), lectures need to get an increase. Management has decided that whoever teaches 2 or more subjects should get 15% increase on the salary. If prompt for staff identification number and display this data accordingly.

Write a pl /SQL anonymous block that will prompt the user to enter a particular student number and the displays the students registration information

a.     Make provision to handle any kind of unknown ORACLE error using a ORACLE predefined error handler by displaying appropriate message”ORACLE/Unknown error! – call support stuff”

Question 6                                                                                                                                                         [15]

A.     Create a function called calc_tax that receives an employee’s salary as a parameter and then returns 15% of that amount as tax.                                                                       (5)                              

B.     Create a procedure called display_tax that receives a staff number as a parameter, computes the corresponding payable tax by making use of the function created in Question 7A, and then displays information as shown in the sample below. Execute the procedure using any staff number. Show how you would do this.                                                                                                                 (10)

READY works as a LIBRARIAN, earn          R24000 and pays          R3600 as

tax

PL/SQL procedure successfully completed.

Question 5                                                                                                                                                         [15]

Create a procedure named subj_info that receives subject code as a parameter and displays information as shown in the sample output below. The procedure makes use of a CURSOR to retrieve and display the required information. Execute the procedure using any subject code.

Show how you would do this.

When you invoke the procedure with SP1 as a subject code, this is the output:

SLEEPY,07-APR-72 registered for SYSTEMS PROGRAMMING 1 on 10-JAN-97

SMUTS,12-JAN-70 registered for SYSTEMS PROGRAMMING 1 on 10-JAN-97

Question 4                                                                                                                                                         [20]

a.      Write a PL/SQL block to retrieve registration information and status for a particular student. The student identification number is supplied at run time by the user. The program should display the relevant information, refer to the sample output given below.

Enter value for studentno: 94001111

Petoors WHY (DoB: 03-Dec-1975) a Male student enrol for INTERNAL AUDITING 1

approved by Morgan, I on date unknown

b.      Make provision to handle any kind of unknown ORACLE error using a ORACLE predefined error handler by displaying appropriate message”ORACLE/Unknown error! – call support stuff”

c.      Using a sample data, 99000278. Display information as in question 5a above. Otherwise, make provision for “invalid or data does not exist error” using ORACLE pre-defined error message “ student 99000278 May not be a registered student”

Question 2                                                                                                                                                         [15]

Write a PL/SQL block to determine whether salaries for members of the staff need to be adjusted or not. Depending on the work load (number of subjects), lectures need to get an increase. Management has decided that whoever teaches 2 or more subjects should get a 15% increase on their salary. Prompt for staff identification number and display this data accordingly. Refer to the sample output

Enter value for staff_number: 513500

Mr/Mrs OLIVIER teaches 2 subjects

ATTENTION: This lecturer is eligible for an increase

The current salary is:          R78000 - The new salary is:          R89700

Enter value for staff_number: 502501

Mr/Mrs MORGAN teaches 1 subjects

ATTENTION: This lecturer is not eligible for an increase

Question 1                                                                                                                                                         [20]

Write a PL/SQL anonymous block that will prompt the user to enter a particular student number and the displays the student’s registration information. Refer to the sample output below

Enter value for studentno: 97003455

Student: SMUTS, JH

DOB / age: 12-JAN-70(51)

No. of Subject enrolled: 6

Total Fees payable: 7900

Registration date: 10-JAN-97

Enter value for studentno: 94001111

Student: PETOORS, WHY

DOB / age: 03-DEC-75(45)

No. of Subject enrolled: 1

Total Fees payable: 500

Registration date: Date Unknown

#266615

Consider the following relation with set of functional dependencies R(ABCDEF) ABC->D, ABD->E, CD->F, CDF->B, BF->D a) Identify the candidate key(s) in the relation (with proper steps followed). b) Identify which normal form this relation is in and reason for the answer