Consider the following database application that is used to assess physical aptitudes and characteristics of the candidates who apply to one or more academies within the US military service system. For a candidate to be acceptable to one particular military academy, he or she must pass a set of rules based on the candidate’s medical records. There are many rules that are used in the real application, but for this project we will consider only three such rules: age, pulse, and blood pressure.

Here are the concrete requirements for this project:

A candidate may apply to one or more academies. A separate application is required for each academy that the candidate applies to
Each academy has its own specific parameters that the rules use. Hence, a candidate may be declared not acceptable for some academies, but acceptable for others
A candidate evaluation consists of running all the rules for that candidate and a given academy. A separate evaluation is performed for each application. For each evaluation, a row is created in EVALUATIONS table, and three rows in the RESULTS table (one for each of the three rules that we are considering)
As mentioned above, the rule evaluation results are stored in RESULTS table. The result of a rule is stored as a number, as follows: 1 – Acceptable, 0 - Not Acceptable
In order for an applicant to be declared “Acceptable” for a given application, all the rules must return 1 (Acceptable). If at least one rule returns 0, the candidate is declared “Not Acceptable”
Each rule is implemented by a specific PL/SQL.
The three rules are defined below:

a. 16 <= age <=22 and (USAFA or USMA) – “Acceptable”
b. 16 <= age <= 40 and USUHS – “Acceptable”
c. Otherwise – “Not Acceptable”

a. Pulse < 45 – “Not Acceptable”
b. 45 <= Pulse <= 99 – “Acceptable”
c. Pulse > 99 - “Not acceptable”

Blood Pressure:
a. Systolic < 140 and Diastolic < 90 – “Acceptable”
b. Systolic >= 140 or Diastolic >= 90 – “Unacceptable”

The following tables are created for this application (for concrete details see the attached DDL script):

APPLICANTS (SSN, FirstName, LastName, DOB)
ACADEMIES (AcadID, AcadName)
MEDICAL_RECORDS (SSN, Pulse, Systolic, Diastolic, DateUpdated)
EVAL_RULES (RuleID, FunctionName)
RESULTS (EvalID, RuleID, Result)
Given the above application description and database schema, you are asked to do the followings:

Create a PL/SQL function for each of the three rules described above. The function accepts one parameter, the AppID, and returns 0 – Not Acceptable, or 1 - Acceptable
Define a sequence for the primary key of the EVALUATIONS table
Create a PL/SQL procedure that evaluates an application (by calling all three functions defined at #1) and populates the EVALUATIONS and RESULTS tables accordingly. This procedure accepts AppID as input parameter.
Create a PL/SQL function that returns the final result of the most recent evaluation for a given application. This function accepts AppID as input parameter and returns 0 or 1. It uses the EVALUATIONS and RESULTS tables
Perform evaluations for all the applications that exist in APPLICATIONS table by calling the procedure defined at #3, and display the results by calling the function defined at #4, repeatedly for each application, and display the results using DBMS_OUTPUT. The following columns will be displayed: Applicant name, Academy, Evaluation result (Acceptable, Not Acceptable)
Use PL/SQL exceptions when defining your PL/SQL blocks.

Solution PreviewSolution Preview

This material may consist of step-by-step explanations on how to solve a problem or examples of proper writing, including the use of citations, references, bibliographies, and formatting. This material is made available for the sole purpose of studying and learning - misuse is strictly forbidden.

reate or replace function ageTest
(AppIDs IN number)
return number
-- we will iterate over rows which are got from below query
cursor item_cur is
    select a.ACADID,
      EXTRACT(YEAR FROM Sysdate) - EXTRACT(YEAR FROM ap.DOB) as "AGE" from
      APPLICATIONS a inner join APPLICANTS ap
      on ap.SSN = a.SSN
      where a.APPID = AppIDs;
-- iterator   
item_l item_cur%ROWTYPE;   
-- returns 0 – Not Acceptable, or 1 - Acceptable
results number;
age number;
acadid varchar2(10);

open item_cur;
results := 0;
fetch item_cur into item_l;
age := item_l.AGE;
acadid := item_l.ACADID;   
close item_cur;
if ( age >= 16 and age <= 22
       and ( acadid = 'USAFA' or acadid = 'USMA' ) )
$75.00 for this solution

PayPal, G Pay, ApplePay, Amazon Pay, and all major credit cards accepted.

Find A Tutor

View available Database Development Tutors

Get College Homework Help.

Are you sure you don't want to upload any files?

Fast tutor response requires as much info as possible.

Upload a file
Continue without uploading

We couldn't find that subject.
Please select the best match from the list below.

We'll send you an email right away. If it's not in your inbox, check your spam folder.

  • 1
  • 2
  • 3
Live Chats