The Department-Employee Master/Detail Application
This application that 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
10 !
! Program: Process_Expenses
! Purpose: Main entry of the application. Initialize the database,
! process each department, and terminate the session.
!
program Process_Expenses
external byte function Init_Db
print 'Entering application to process expenses.'
if (Init_db = 1) then
call Process_Depts
call End_Db
print 'Successful completion of application.'
end if
end program ! Process_Expenses
!
! Function: 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.
! Returns:
! 0 - Failed to start application.
! 1 - Succeeded in starting application.
!
20 ## function byte Init_Db
## declare integer ing_error
external integer Close_Down
## ingres personnel
print 'Creating "To_Be_Resolved" table.'
## create toberesolved
## (name = char(20),
## age = smallint,
## idno = integer,
## hired = date,
## dept = char(10),
## salary = money)
## inquire_ingres (ing_error = ERRORNO)
if (ing_error > 0) then
print 'Fatal error creating application table.'
Init_Db = 0 ! Failed
else
## begin transaction
!
! Inform Ingres runtime system about error handler
! All errors from here on close down the application.
!
call IIseterr(Close_Down)
Init_Db = 1 ! Ok
end if
## end function ! Init_Db
!
! Subroutine: End_Db
! Purpose: Close off the multi-statement transaction and access
! to the database after successful completion of the
! application.
!
30 ## sub End_Db
## end transaction
## exit
## end sub ! End_Db
!
! Subroutine: 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.
!
40 ## sub Process_Depts
! Dept_Rec corresponds to the "dept" table
## record Dept_Rec
## string dname = 12
## double totsales
## word employees
## end record
## declare Dept_Rec dpt
## declare integer no_rows ! Cursor loop control
## declare integer emps_term ! Employees terminated
declare byte deleted_dept ! Was the dept deleted?
declare string dept_format ! Formatting value
declare byte dept_err
! Minimum sales of department
## declare real constant MIN_DEPT_SALES = 50000.0
no_rows = 0
## 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
while (no_rows = 0)
## retrieve cursor deptcsr
## (dpt::dname, dpt::totsales, dpt::employees)
## inquire_equel (no_rows = ENDQUERY)
if (no_rows = 0) then
! Did department reach minimum sales ?
if (dpt::totsales < MIN_DEPT_SALES) then
## delete cursor deptcsr
deleted_dept = 1
dept_format = ' -- DISSOLVED --'
else
deleted_dept = 0
dept_format = ''
end if
! Log what we have just done
print 'Department: ' + trm$(dpt::dname) + &
', Total Sales: ';
print using '$$####.##', dpt::totsales;
print dept_format
! Now process each employee in the department
call Process_Employees
(dpt::dname, deleted_dept, emps_term)
! If some employees were terminated,
! record this fact
if (emps_term > 0 and deleted_dept = 0) then
## replace cursor deptcsr
## (employees = dpt::employees - emps_term)
end if
end if ! If a row was retrieved
next ! Continue with cursor loop
## close cursor deptcsr
## end sub ! Process_Depts
!
! Subroutine: 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 department dissolved?
! emps_term - Set locally to record how many
! employees were terminated
! for the current department.
50 ## sub Process_Employees(string dept_name,
## byte deleted_dept,
## integer emps_term)
! Emp_Rec corresponds to the "employee" table
## record Emp_Rec
## string ename = 20
## byte age
## integer idno
## string hired = 25
## real salary
## integer hired_since_85
## end record
## declare Emp_Rec erec
! Minimum employee salary
## declare real constant MIN_EMP_SALARY = 14000.00
## declare byte constant NEARLY_RETIRED = 58
## declare real constant SALARY_REDUC = 0.95
## declare byte no_rows ! For cursor loop control
declare string title ! Formatting values
declare string description
no_rows = 0
emps_term = 0 ! Initialize how many
!
!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
while (no_rows = 0)
## retrieve cursor empcsr
## (erec::ename, erec::age, erec::idno,
## erec::hired, erec::salary, erec::hired_since_85)
## inquire_equel (no_rows = ENDQUERY)
if (no_rows = 0) then
! Terminate if new employee
if (erec::hired_since_85 > 0) then
## delete cursor empcsr
title = 'Terminated: '
description = 'Reason: Since 85.'
emps_term = emps_term + 1
! Reduce salary if not nearly retired
else
if (erec::salary > MIN_EMP_SALARY) then
if (erec::age < NEARLY_RETIRED) then
## replace cursor empcsr
## (salary = salary * SALARY_REDUC)
title = 'Reduction: '
description = 'Reason: Salary.'
else
! Do not reduce salary
title = 'No Changes: '
description = 'Reason: Retiring.'
end if
! Else leave employee alone
else
title = 'No Changes: '
description = 'Reason: Salary.'
end if
end if
! Was employee's department dissolved ?
if (deleted_dept = 1) then
## append to toberesolved (e.all)
## where e.idno = erec::idno
## delete cursor empcsr
end if
! Log the employee's information
print ' ' + title;
print str$(erec::idno);
print ', ' + trm$(erec::ename) + ', ';
print str$(erec::age) + ', ';
print using '$$####.##', erec::salary;
print '; ' + description
end if ! If a row was retrieved
next ! Continue with cursor loop
## close cursor empcsr
## end sub ! Process_Employees
!
! 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
! losed.
! Parameters:
! ingerr - Integer containing Ingres error number.
!
60 ## function integer Close_Down (integer ingerr)
## declare string err_text
## inquire_ingres (err_text = ERRORTEXT)
## exit
print 'Closing down because of database error: '
print err_text
stop ! Exit BASIC
Close_Down = ingerr
## end function ! Close_Down