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 a foreign key reference to a row in the Departments table for the department in which the employee works

• empl_start_date The employee's start date with the company

• empl_position The identifier a foreign key reference to a Positions table record for 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 the 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.