--------------------------------------------------------------------------------
Given the following three relations (EMPLOYEE, PROJECT, EMP_PROJ), sample of the relations is shown below. Based on these relations, answer the following questions.
PROJECT Table
pid
p_name
Manager
start0date
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
EMPLOYEE Table
id
first-name
last-name
role
1
John
Smith
VB Programmer
2
James
Gosling
Java Programmer
EMP_PROJ Table
id
pid
hours_per_week
1
p1
20
2
p2
20
2
p3
25
Familiar Problems Solving
Objectives: The aim of the questions in this part is to evaluate that the student has some basic knowledge of the key aspects of the lecture material and can attempt to solve familiar problems.
Question One (10 marks)
Note1: Please select one problem to code
Write an appropriate Oracle Object (Anonymous Block /VIEW) for the following problems:
A view to list employee first and last names, and the project names they been working own for those projects that have no manager. (5 marks)
A PL/SQL Anonymous Blockthat prints employees first and last names for those who work as 'Java Programmers' on project number 'p1'. (5 marks)
III. 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.
Note2: Select to solve only two problems in your answer sheet
Note3: Please select one problem to code
Write an appropriate Oracle named Object (Procedure/Function) for the following problems: (10 marks)
Input an employee first and last names and returns the list of project names in a record. (5 marks)
Input an employee number as and returns the number of projects he/s works in for the last three years (5 marks)
Input a project number and returns a record that contains the first name an last name for employees who worked on that project (5 marks)
ارجو المساعدة