The Department-Employee Master/Detail Application
This application using 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 */
/* Global variable set in error handler */
int is_error = 0;
/*
** Procedure: MAIN
** Purpose: Main body of the application. Initialize the
** database process each department, and terminate the session.
** Parameters:
** None
*/
main()
{
printf("Entering application to process expenses.\n");
Init_Db();
Process_Depts();
End_Db();
printf("Successful completion of application.\n");
}
/*
** Procedure: Init_Db
** Purpose: Initialize the database.
** Start up the database, and abort if an error.
** Before processing employees, create the table
** for employees who lose their department,
** "toberesolved". Initiate the multi-statement
** transaction.
** Parameters:
** None
*/
## Init_Db()
## {
## char err_text[257];
int Error_Proc();
## ingres personnel;
/* Inform Ingres runtime system about error handler */
IIseterr(Error_Proc);
printf ("Creating \"To_Be_Resolved\" table.\n");
## create toberesolved
## (name = c20,
## age = i1,
## idno = i4,
## hired = date,
## dept = c10,
## salary = money)
if (is_error)
{
## inquire_ingres (err_text = errortext)
printf("Fatal error on creation:\n%s", err_text);
## exit
exit(-1);
}
## begin transaction
## }
/*
** Procedure: End_Db
** Purpose: Close off the multi-statement transaction and
** access to the database after successful completion
** of the application.
** Parameters:
** None
*/
## End_Db()
## {
## end transaction
## exit
## }
/*
** 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 table). If an employee was terminated, then
** update the department's employee counter. No error
** checking is done for cursor updates.
** Parameters:
** None
*/
## Process_Depts()
## {
## struct dpt { /* Corresponds to the "dept" table */
## char name[13];
## double totsales;
## short employees;
## } dpt;
## int no_rows = 0; /* Cursor loop control */
## define min_dept_sales 50000.00 /* Min sales of department */
## short emps_term = 0; /* Employees terminated */
short deleted_dept; /* Was the dept deleted? */
char *dept_format; /* Formatting value */
is_error = 0; /* Initialize error flag */
## range of d IS dept
## declare cursor deptcsr for
## retrieve (d.name, d.totsales, d.employees)
## for direct update of (name, employees)
## open cursor deptcsr
if (is_error)
Close_Down();
while (!no_rows)
{
is_error = 0;
## retrieve cursor deptcsr
## (dpt.name, dpt.totsales, dpt.employees)
## inquire_equel (no_rows = endquery)
if (!no_rows)
{
/* Did the department reach minimum sales? */
if (dpt.totsales < min_dept_sales)
{
## delete cursor deptcsr
/* If error occurred in deleting row, close down */
if (is_error)
Close_Down();
deleted_dept = 1;
dept_format = " -- DISSOLVED --";
}
else
{
deleted_dept = 0;
dept_format = "";
}
/* Log what we have just done */
printf( "Department: %14s, Total Sales: %12.3f %s\n",
dpt.name, dpt.totsales, dept_format );
/* Now process each employee in the department */
Process_Employees( dpt.name, deleted_dept, &emps_term );
/* If some employees were terminated, record this fact */
if (emps_term > 0 && !deleted_dept)
## replace cursor deptcsr
## (employees = dpt.employees - emps_term)
/* If error occurred in update, close down application */
if (is_error)
Close_Down();
}
}
## close cursor deptcsr
## }
/*
** Procedure: Process_Employees
** Purpose: Scan through all the employees for a particular
** department. Based on given conditions the employee
** may be terminated, or given a salary reduction.
** 1. If an employee was hired since 1985 then the
** employee is terminated.
** 2. If the employee's 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.
*/
## Process_Employees( dept_name, deleted_dept, emps_term )
## char *dept_name;
short deleted_dept;
short *emps_term;
## {
## struct emp { /* Corresponds to "employee" table */
## char name[21];
## short age;
## int idno;
## char hired[26];
## float salary;
## int hired_since_85;
## } emp;
## intno_rows = 0; /* Cursor loop control */
## define min_emp_salary 14000.00 /* Minimum employee salary */
## define nearly_retired 58
## define salary_reduc 0.95
char *title; /* Formatting values */
char *description;
is_error = 0; /* Initialize error flag */
/*
** 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)
## open cursor empcsr
if (is_error)
Close_Down();
* emps_term = 0; /* Record how many */
while (!no_rows)
{
is_error = 0;
## 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)
{
if (emp.hired_since_85 > 0)
{
## delete cursor empcsr
if (is_error)
Close_Down();
title = "Terminated:";
description = "Reason: Hired since 1985.";
(*emps_term)++;
}
else
{
/* Reduce salary if not nearly retired */
if (emp.salary > MIN_EMP_SALARY)
{
if (emp.age < nearly_retired)
{
## replace cursor empcsr
## (salary = salary * salary_reduc)
if (is_error)
Close_Down();
title = "Reduction: ";
description = "Reason: Salary.";
}
else
{
/* Do not reduce salary */
title = "No Changes:";
description = "Reason: Retiring.";
}
}
else /* Leave employee alone */
{
title = "No Changes:";
description = "Reason: Salary.";
}
/* Was employee's department dissolved ? */
if (deleted_dept)
{
## append to toberesolved (e.all)
## where e.idno = emp.idno
if (is_error)
Close_Down();
## delete cursor empcsr
}
}
/* Log the employee's information */
printf(" %s %6d, %20s, %2d, %8.2f; %s\n",
title, emp.idno, emp.name, emp.age, emp.salary, description);
}
}
## close cursor empcsr
is_error = 0;
## }
/*
** Procedure: Close_Down
** Purpose: If an error occurs during the execution of an
** EQUEL statement, the error handler sets a flag which
** may cause this routine to be called. For simplicity,
** errors cause the current transaction to be aborted and
** the application to be closed down.
*/
## Close_Down()
## {
## char err_text[257];
## inquire_ingres (err_text = ERRORTEXT)
printf("Closing down because of database
error:\n%s", err_text);
## abort
## exit
exit(-1);
## }
/*
** Procedure: Error_Proc
** Purpose: Process Ingres errors
** Set global "is_error" flag, allowing appropriate action
** after individual database statements. Return 0 so that
** Ingres runtime system will suppress error messages.
** Parameters:
** ingerr - Pointer to integer containing
** ingres error number.
*/
int
Error_Proc(ingerr)
int *ingerr;
{
is_error = 1;
return 0;
}