Creating the Database Schema

The advanced ACME employee system stores the employee data in the following database tables. To use this tutorial, create these tables in your database application. Table B–14 describes how each class relates to the database tables.

The column types listed here are generic; the actual column types depend on the database used.

Table B-6 EMPLOYEE Table

Column Name	Column Type	Details
EMP_ID	NUMERIC(15)	Primary key
F_NAME	VARCHAR(40)
L_NAME	VARCHAR(40)
ADDR_ID	NUMERIC(15)
GENDER	CHAR(1)
START_DATE	DATE
END_DATE	DATE
START_TIME	TIME
END_TIME	TIME
MANAGER_ID	NUMERIC(15)
VERSION	NUMERIC(15)

Table B-7 SALARY Table

Column Name	Column Type	Details
EMP_ID	NUMERIC(15)	Primary key
SALARY	NUMERIC(10)

Table B-8 ADDRESS Table

Column Name	Column Type	Details
ADDRESS_ID	NUMERIC(15)	Primary key
COUNTRY	VARCHAR(80)
STREET	VARCHAR(80)
CITY	VARCHAR(80)
PROVINCE	VARCHAR(80)
P_CODE	VARCHAR(20)

Table B-9 PHONE Table

Column Name	Column Type	Details
EMP_ID	NUMERIC(15)	Primary key
AREA_CODE	CHAR(3)
P_NUMBER	CHAR(7)
TYPE	VARCHAR(15)	Primary key

Table B-10 PROJECT Table

Column Name	Column Type	Details
PROJ_ID	NUMERIC(15)	Primary key
DESCRIP	VARCHAR(200)
PROJ_NAME	VARCHAR(30)
PROJ_TYPE	CHAR(1)
LEADER_ID	NUMERIC(15)
VERSION	NUMERIC(15)

Table B-11 LPROJECT Table

Column Name	Column Type	Details
PROJ_ID	NUMERIC(15)	Primary key
BUDGET	NUMERIC(10,2)
MILESTONE	TIMESTAMP

Table B-12 RESPONS Table

Column name	Column type	Details
EMP_ID	NUMERIC(15)	Primary key
DESCRIP	VARCHAR(200)

Table B-13 PROJ_EMP Table Between PROJECT and EMPLOYEE

Column Name	Column Type	Details
EMP_ID	NUMERIC(15)	Primary key
PROJ_ID	NUMERIC(15)	Primary key

Table B-14 Relationships Between Classes and Database Table

Column	Class Attribute	Database Type	Java Type
EMPLOYEE	`Employee`
EMP_ID	`id`	NUMERIC(15)	BigDecimal
F_NAME	`firstName`	VARCHAR(40)	String
L_NAME	`lastName`	VARCHAR(40)	String
ADDR_ID	`address`	NUMERIC(15)	Address
not applicable	`phoneNumbers`	not applicable	Vector
GENDER	`gender`	CHAR(1)	String
START_TIME	`normalHours[0]`	TIME	Time
END_TIME	`normalHours[1]`	TIME	Time
MANAGER_ID	`manager`	NUMERIC(15)	Employee
not applicable	`managedEmployees`	not applicable	Vector
not applicable	`projects`	not applicable	Vector
see Employment Period	`period`	not applicable	EmploymentPeriod
SALARY	`Employee`
EMP_ID	not applicable	NUMERIC(15)	not applicable
SALARY	`salary`	NUMERIC(10)	int
EMPLOYEE	`EmploymentPeriod`
START_DATE	`startDate`	DATE	Date
END_DATE	`endDate`	DATE	Date
RESPONS	`Employee`
EMP_ID	not applicable	NUMERIC(15)	not applicable
DESCRIP	`responsibilitiesList`	VARCHAR(200)	String
PROJECT	`LargeProject` and `SmallProject`
PROJ_ID	`id`	NUMERIC(15)	BigDecimal
DESCRIP	`description`	VARCHAR(200)	String
LEADER_ID	`teamLeader`	NUMERIC(15)	Employee
PROJ_NAME	`name`	VARCHAR(30)	String
PROJ_TYPE	not applicable	CHAR(1)	not applicable
VERSION	not applicable	NUMERIC(15)	not applicable
LPROJECT	`LargeProject`
PROJ_ID	not applicable	NUMERIC(15)	not applicable
BUDGET	`budget`	NUMERIC(10,2)	double
MILESTONE	`milestoneVersion`	TIMESTAMP	TimeStamp
ADDRESS	`Address`
ADDRESS_ID	`id`	NUMERIC(15)	BigDecimal
COUNTRY	`country`	VARCHAR(80)	String
STREET	`street`	VARCHAR(80)	String
CITY	`city`	VARCHAR(80)	String
PROVINCE	`province`	VARCHAR(80)	String
P_CODE	`postalCode`	VARCHAR(20)	String
PHONE	`PhoneNumber`
AREA_CODE	`areaCode`	CHAR(3)	String
P_NUMBER	`number`	CHAR(7)	String
EMP_ID	`owner`	NUMERIC(15)	Employee
TYPE	`type`	VARCHAR(15)	String
PROJ_EMP	Relation Table
PROJ_ID	not applicable	NUMERIC(15)	not applicable
EMP_ID	not applicable	NUMERIC(15)	not applicable