The Department-Employee Master/Detail Application
This application uses two database tables joined on a specific column. This typical example of a department and its employees demonstrates how to process two tables as a master and a detail.
The program scans through all the departments in a database table, in order to reduce expenses. Department information is stored in program variables. Based on certain criteria, the program updates department and employee records. The conditions for updating the data are the following:
Departments:
• If a department has made less than $50,000 in sales, the department is dissolved.
Employees:
• If an employee was hired since the start of 1985, the employee is terminated.
• If the employee's yearly salary is more than the minimum company wage of $14,000 and the employee is not nearing retirement (over 58 years of age), the employee takes a 5% pay cut.
• If the employee's department is dissolved and the employee is not terminated, the employee is moved into a state of limbo (the "toberesolved" database table, described below) to be resolved by a supervisor.
This program uses two cursors in a master/detail fashion. The first cursor is for the Department table, and the second is for the Employee table. The create statements used to create the tables are shown below. The cursors retrieve all the information in their respective tables, some of which is updated. The cursor for the Employee table also retrieves an integer date interval whose value is positive if the employee was hired after January 1, 1985.
Each row that is scanned, both from the Department table and the Employee table, is recorded into the system output file. This file serves as a log of the session and as a simplified report of the updates that were made.
Each section of code is commented for the purpose of the application and also to clarify some of the uses of the EQUEL statements. The program illustrates table creation, multi-query transactions, all cursor statements and direct updates. For purposes of brevity, error handling on data manipulation statements is simply to close down the application.
The following two create statements describe the Employee and Department database tables:
## create dept
## (name = c12, { Department name }
## totsales = money, { Total sales }
## employees = i2) { Number of employees }
## create employee
## (name = c20, { Employee name }
## age = i1, { Employee age }
## idno = i4 { Unique employee id }
## hired = date, { Date of hire }
## dept = c10, { Employee department }
## salary = money) { Yearly salary }
## program Departments( input, output );
## type
## String12 = varying[12] of Char;
## String20 = varying[20] of Char;
## String25 = varying[25] of Char;
## String132 = varying[132] of Char;
## Short_Short_Integer = [Byte] -128 .. 127;
## Short_Integer = [Word] -32768 .. 32767;
## Long_Float = Double;
## label
## Exit_Program;
## DECLARE
{
| Function: Close_Down
| Purpose: If an error occurs during the execution of an
| EQUEL statement this error handler is called.
| Errors are printed and the current database session is
| terminated. Any open transactions are implicitly closed.
| Parameters:
| ingerr - Integer containing Ingres error number.
}
## [global] function Close_Down(ingerr: Integer): Integer;
## var
## err_text: varying [200] of char;
## begin {Close_Down}
## inquire_ingres (err_text = errortext)
## exit
Writeln('Closing down because of database error:');
Writeln(err_text);
Close_Down := ingerr;
goto Exit_Program;
## end; {Close_Down}
{
| Procedure: Process_Expenses -- MAIN
| Purpose: Main body of the application. Initialize
| the database,
| process each department, and terminate the session.
| Parameters:
| None
}
## procedure Process_Expenses;
{
| Function: Init_Db
| Purpose: Initialize the database. Connect to the
| database, and abort on error. Before
| processing departments and employees create
| the table for employees who lose their
| department, "toberesolved". Initiate the
| multi-statement transaction.
| Parameters:
| None
| Returns:
| FALSE - Failed to start application.
| TRUE - Succeeded in starting application.
}
## Function Init_Db : Boolean;
## var
## create_err: Integer;
## begin {Init_Db}
## Ingres personnel
{Create the table}
Writeln('Creating "To_Be_Resolved" table.');
## create toberesolved
## (name = c20,
## age = smallint,
## idno = integer,
## hired = date,
## dept = c10,
## salary = money)
## inquire_ingres (create_err = ERRORNO)
if (create_err > 0) then begin
Writeln('Fatal error creating application table.');
Init_Db := FALSE;
end else begin
{
| Inform Ingres runtime system about error handler
| All errors from here on close down
| the application.
}
IIseterr(%immed Close_Down);
## begin transaction
Init_Db := TRUE;
end; {If create error}
## end; {Init_Db}
{
| Procedure: End_Db
| Purpose: Commit the multi-statement transaction and
| end access to the database after successful
| completion of the application.
| Parameters:
| None
}
## Procedure End_Db;
## begin {End_Db}
## end transaction
## exit
## end; {End_Db}
{
| Procedure: Process_Employee
| Purpose: Scan through all the employees for a
| particular department. Based on given
| conditions the employee may be terminated,
| or take a salary reduction.
| 1. If an employee was hired since 1985 then
| the employee is terminated.
| 2. If the employees yearly salary is more
| than the minimum company wage of $14,000
| and the employee is not close
| to retirement
| (over 58 years of age), then the employee
| takes a 5% salary reduction .
| 3. If the employee's department is dissolved
| and the employee is not terminated, then
| the employee is moved into the
| "toberesolved" table.
| Parameters:
| dept_name - Name of current department.
| deleted_dept - Is current department being
| dissolved?
| emps_term - Set locally to record how many
| employees were terminated
| for the current department.
}
## procedure Process_Employees (dept_name: String12;
## deleted_dept: Boolean;
## var emps_term: Integer);
## const
## SALARY_REDUC = 0.95;
## MIN_EMP_SALARY = 14000.00;
## NEARLY_RETIRED = 58;
## type
{Emp_Rec corresponds to the "employee" table}
## Emp_Rec = record
## name: String20;
## age: Short_Short_Integer;
## hired: String25;
## idno: Integer;
## salary: Real;
## hired_since_85: Integer;
## end; {record}
## var
## emp: Emp_Rec;
title: String12; {Formatting values}
description: String25;
## no_rows: Integer;
## begin {Process_Employees}
{
| Note the use of the Ingres function to find out
| who was hired since 1985.
}
## range of e IS employee
## declare cursor empcsr FOR
## retrieve (e.name, e.age, e.idno, e.hired, e.salary,
## res = int4(interval('days',
## e.hired-date('01-jan-1985'))))
## where e.dept = dept_name
## for direct update of (name, salary)
no_rows := 0;
emps_term := 0; {Record how many}
## open cursor empcsr
while (no_rows = 0) do begin
## retrieve cursor empcsr (emp.name, emp.age, emp.idno,
## emp.hired, emp.salary,
## emp.hired_since_85)
## inquire_equel (no_rows = endquery)
if (no_rows = 0) then begin
{Terminate new employees}
if (emp.hired_since_85 > 0) then begin
## delete cursor empcsr
title := 'Terminated: ';
description := 'Reason: Hired since 1985.';
emps_term := emps_term + 1;
{Else reduce salary if large and not nearly retired}
end else if (emp.salary > MIN_EMP_SALARY) then begin
if (emp.age < nearly_retired) then begin
## replace cursor empcsr
## (salary = salary * salary_reduc)
title := 'Reduction: ';
description := 'Reason: Salary.';
end else begin
{Do not reduce salary - nearly retired}
title := 'No Changes: ';
description := 'Reason: Retiring.';
end; {If retiring}
{Else leave employee alone - low salary}
end else begin
title := 'No Changes: ';
description := 'Reason: Salary.';
end;
{Was employee's department dissolved?}
if (deleted_dept) then begin
## append to toberesolved (e.all)
## where e.idno = emp.idno
## delete cursor empcsr
end;
{Log the employee's information}
Write(' ', title, ' ', emp.idno:6, ', ');
Write(emp.name, ', ', emp.age:3, ', ');
Writeln(emp.salary:8:2, ' ; ', description);
end; {If a row was retrieved}
end; {Continue with cursor loop}
## close cursor empcsr
## end; {Process_Employees}
{
| Procedure: Process_Depts
| Purpose: Scan through all the departments, processing
| each one. If the department has made less
| than $50,000 in sales, then the department
| is dissolved.
| For each department process all the
| employees (they may even be moved to another
| database table).
| If an employee was terminated, then update
| the department's employee counter.
| Parameters:
| None
}
## Procedure Process_Depts;
## const
MIN_TOT_SALES = 50000.00;
## type
{Dept_Rec corresponds to the "dept" table}
## Dept_Rec = record
## name: String12;
## totsales: Long_Float;
## employees: Short_Integer;
## end;
## var
## no_rows: Integer;
## emps_term: Integer; {Employees terminated}
deleted_dept: Boolean; {Was the dept deleted?}
dept_format: String20; {Formatting value}
## dpt: Dept_Rec;
## begin {Process_Depts}
## range of d is dept
## declare cursor deptcsr for
## retrieve (d.name, d.totsales, d.employees)
## for direct update of (name, employees)
no_rows := 0;
emps_term := 0;
## open cursor deptcsr
while (no_rows = 0) do begin
## retrieve cursor deptcsr (dpt.name,
## dpt.totsales,
## dpt.employees)
## inquire_equel (no_rows = endquery)
if (no_rows = 0) then begin
{Did the department reach minimum sales?}
if (dpt.totsales < MIN_TOT_SALES) then begin
## delete cursor deptcsr
deleted_dept := TRUE;
dept_format := ' -- DISSOLVED --';
end else begin
deleted_dept := False;
dept_format := ' ';
end; {If reached minimum sales}
{Log what we have just done}
Write('Department: ', dpt.name);
Write(', Total Sales: ', dpt.totsales:12:3);
Writeln(dept_format);
{Now process each employee in the department}
Process_Employees(dpt.name, deleted_dept, emps_term);
{If employees were terminated, record it}
if ((emps_term > 0) and (not deleted_dept)) then
## replace cursor deptcsr
## (employees = employees - emps_term)
end; {If a row was retrieved}
end; {Continue with cursor loop}
## close cursor deptcsr
## end; {Process_Depts}
## begin {Process_Expenses}
Writeln('Entering application to process expenses.');
if (Init_Db) then begin
Process_Depts;
End_Db;
Writeln('Completion of application.');
end;
## end; {Process_Expenses}
## begin {main}
Process_Expenses;
Exit_Program:;
## end. {main}