Applying Enterprise JavaBeans 2nd

4.6 Database Schemas

The Benefits Enrollment application uses three databases. In order for you to understand the example application, this section describes the schemas for these databases.

4.6.1 The Employee Database

The Star Enterprise human resources department maintains information about employees, company departments, and department positions in EmployeeDatabase. The information is stored in three tables. The Employees table within the database holds employee identifying information. Code Example 4.21 shows the SQL CREATE statement defining this table:

Code Example 4.21 The Employees Table Schema

CREATE TABLE Employees (
   empl_id INT,
   empl_first_name VARCHAR(32),
   empl_last_name VARCHAR(32),
   empl_addr_street VARCHAR(32),
   empl_addr_city VARCHAR(32),
   empl_addr_zip VARCHAR(10),
   empl_addr_state VARCHAR(2),
   empl_dept_id VARCHAR(10),
   empl_start_date DATE,
   empl_position VARCHAR(5),
   empl_birth_date DATE,
   PRIMARY_KEY (empl_id)
)

The Employees table contains the following columns:

• empl_id? The employee identifier number, which uniquely identifies each employee and is the primary key for these records

• empl_first_name and empl_last_name? The employee's first and last names

• empl_addr_street, empl_addr_city, empl_addr_zip, and empl_addr_state? The employee's complete address

• empl_dept_id? The identifier梐 foreign key reference to a row in the Departments table梖or the department in which the employee works

• empl_start_date? The employee's start date with the company

• empl_position? The identifier梐 foreign key reference to a Positions table record梖or the employee's current job or position

• empl_birth_date? The employee's date of birth

EmployeeDatabase includes two other tables that pertain to the Benefits Enrollment application. The Positions table keeps, in the pos_desc column, a description of each job position within the company. The primary key of this table is the pos_id column, which contains the position identifier. Code Example 4.22 shows the schema for this table:

Code Example 4.22 The Positions Table Schema

create table Positions (
   pos_id VARCHAR(5),
   pos_desc VARCHAR(32),
   PRIMARY_KEY (pos_id)
)

The third relevant table in EmployeeDatabase is the Departments table, which keeps information about each department within the company. Code Example 4.23 shows the schema for this table:

Code Example 4.23 The Departments Table Schema

create table Departments (
   dept_id VARCHAR(10),
   dept_desc VARCHAR(32),
   dept_mgr INT,
   PRIMARY_KEY (dept_id)
)

The Departments table contains the following columns:

• dept_id? The unique department identifier, the primary key for the table

• dept_desc? A description of the department

• dept_mgr? The empl_id of the current manager of the department

4.6.2 The Benefits Database

The BenefitsDatabase schema defines one table, Selections, pertaining to the Benefits Enrollment application. Code Example 4.24 shows the schema defining the Selections table:

Code Example 4.24 The Selections Table Schema

create table Selections (
   sel_empl INT,
   sel_coverage INT,
   sel_medical_plan VARCHAR(10),
   sel_dental_plan VARCHAR(10),
   sel_smoker CHAR(1),
   PRIMARY_KEY (sel_empl)
)

The Selections table contains the following columns:

• sel_empl? The identifier number of the employee for whom the benefits selections pertain

• sel_coverage? The type of coverage selected by the employee

• sel_medical_plan? The plan identifier of the employee's selected medical plan

• sel_dental_plan? The plan identifier of the employee's selected dental plan

• sel_smoker? An indicator, Y or N, of whether the employee is a smoker

4.6.3 The Payroll Database

The PayrollDatabase schema defines one table relevant to the Benefits Enrollment application梩he Paychecks table. Code Example 4.25 shows its definition:

Code Example 4.25 The Paychecks Table Schema

create table Paychecks (
   pay_empl INT,
   pay_salary FLOAT,
   pay_ded_benefits FLOAT,
   PRIMARY_KEY (pay_empl)
)

This table maintains two columns: the employee's payroll amount, or salary (pay_salary), and the benefits deduction amount (pay_ded_benefits). The table's primary key is the employee identifier number, which is held in the pay_empl column.