I was wondering if anyone could help me where to start on this?
Consider the following schema containing data for a particular air base:
Pilots
{PID, Name, HomeTown, HomeState, Rank}
AirCraftTypes {Model, Aname, MachSpeed}
Aircraft
{AID, Model, DateInService, HID}
FlightSkills {PID, Model, HoursLogged}
Data for this schema can be found on the department Database server in the Airbase
schema.
Required:
Implement the schema above as a series of user defined types and object tables. Make
sure to use references instead of foreign keys. Insert the same test data that is found in
the airbase1 schema.
Include the following member functions in the pilot type:
totalFlightHours()
- returns the total hours logged by that pilot in all aircraft.
totalFlightHours(model)
- returns the total hours logged by that pilot in a particular aircraft. The function
should return 0 if they have not flight hours in that model.
addFlightHours(model,hours)
- adds additional flight hours for a pilot for a particular model. If there is not
already a flightskills row for that pilot/model combination it should be added.
Answer the following queries. Use your member functions and referencing following to
avoid joins.
1) The name, F-15E flight hours logged, and total flight hours logged of all pilots.
2) The name and rank of all pilots who can fly the plane with AID=1173
3) The name, rank, and model for all pilots that can fly a supersonic jet. A
supersonic jet is one with a MachSpeed of 1 or greater.
here is what i have so far and not sure if it is right
CREATE or REPLACE Type PilotsADT as object
(PiloID Number(6,0),
AirID Number(6,0),
PName varchar2(20),
ModelName Varchar2(20),
Hours Number(8,0),
member function totalFlightHours Return Number,
member function totalFlightHours Return Number,
member function addFlightHours Return Number);
CREATE or REPLACE Type PilotsADT as
member function totalFlightHours Return Number IS
Begin
SELECT sum(HoursLogged)
INTO totalFlightHours
FROM FlightSkills
GROUP BY PID, Model;
End;