[مخالف - طلب حل : ]اوركل

Given three relations (EMPLOYEE45, PROJECT45, EMP_PROJ45), sample of the relations is shown below:

pid p_name Manager Start-date end-date

p1 Alpha 10 01/10/03 01/10/05

p2 Beta 10 01/05/04 01/03/05

p3 Gamma 01/01/05 01/05/06

EMPLOYEE45 Table PROJECT45 Table

id first-name last-name role

1 John Smith VB Programmer

2 James Gosling Java Programmer

EMP_PROJ45 Table

id pid hours_per_week

1 p1 20

2 p2 20

2 p3 25

Question (1) - Using DDL and DML, create the above tables in Oracle SQLPlus and insert the values shown in the tables.

(5 marks)

Question (2) - Using Oracle Developer, answer the following problems:

(25 marks)

1) Create two data blocks for Tables EMPLOYEE45 AND EMP_PROJ45. The data block for EMPLOYEE45 should be content, and that of EMP_PROJ45 is tabular (5 records displayed). Link between the data blocks. (5 marks)

2) Create two different control text items and call them NUMBER_YEARS, FIRST_LAST_NAMES, respectively inside EMPLOYEE45 data block.

(3 marks)

3) Write a trigger that displays a) The number of years in which the employee works on a certain project in the NUMBER_YEARS text item, and b) Both first and last names concatenated together in the FIRST_LAST_NAMES text item. (4 marks)

4) Create a list of value (LOV) on p_id text item within EMP_PROJ45 data block. Your LOV should display the project number and name. The LOV should be displayed automatically when you run your form. (2 marks)

5) Create the following buttons and text items inside a new control block displayed on a new vertical toolbar: (5 marks)

a. "Enter Query" to search for a specific data in any block

b. "Change Item Mode" that 1) make item "role" invisible, and item "manager" disabled.

c. "Exit" to exit from the whole form

d. A control text item to display the current date and time.

e. A control text to display the user name.

Hint, all of your buttons should be working when you run your form.

6) Create new data block for PROJECT45 table, and link it to EMP_PROJ45 data block. (2 marks)

7) When you launch your form, display inside the two text items created in Question (5) their values, i.e. current date and time as well as the user name. (4 marks)

Unfamiliar Problems Solving

Objective: The aim of the questions in this part is to evaluate that the student can solve familiar problems with ease and can make progress towards the solution of unfamiliar problems, and can set out reasoning and explanation in a clear and coherent manner.

Question Three (10 marks)

Based on the relational database described earlier, write an appropriate Oracle named Object (Anonymous Block/ Procedure/Function) for the following problems: (20 marks)

a. A PL/SQL Anonymous Block that prints employees first and last names for those who work as 'Java Programmers' on project 'Alpha'. (5 marks)

b. Input an employee first and last names and returns the list of project names in a record. (5 marks)

c. Input an employee number and returns the number of projects he/s works in the last year (5 marks)

d. Input a project number and returns the first name an last name for employees who worked on that project (5 marks)

ارجوكم مساعدة