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