SQL project 2

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:

1. A candidate may apply to one or more academies. A separate application is required for each academy that the candidate applies to
2. 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
3. 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)
4. 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
5. 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” 
6. Each rule is implemented by a specific PL/SQL.

The three rules are defined below:

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

Pulse:
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):

1. APPLICANTS (SSN, FirstName, LastName, DOB)
2. ACADEMIES (AcadID, AcadName)
3. APPLICATIONS (AppID, SSN, AcadID, Year)
4. MEDICAL_RECORDS (SSN, Pulse, Systolic, Diastolic, DateUpdated)
5. EVAL_RULES (RuleID, FunctionName)
6. EVALUATIONS (EvalID, EvalDate, AppID)
7. RESULTS (EvalID, RuleID, Result) 

Given the above application description and database schema, you are asked to do the followings:

1. 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
2. Define a sequence for the primary key of the EVALUATIONS table
3. 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.
4. 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
5. 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)
6. Use PL/SQL exceptions when defining your PL/SQL blocks.

Submit the PL/SQL statements as a text file following the document naming convention FirstLastP2.txt. 

Grading: this project is awarded 100 points

10 points – PL/SQL Function for Rule 1

10 points – PL/SQL Function for Rule 2

10 points – PL/SQL Function for Rule 3

5 points – Sequence for EVALUATIONS table

20 points – PL/SQL procedure that evaluates an application 

20 points – PL/SQL function that returns the final result of an application evaluation

20 points – PL/SQL block that evaluates all applications in APPLICATIONS table

5 points – Proper use of EXCEPTION section of PL/SQL blocks

project 2 DDL for reference