CASE STUDY 2: EMPLOYEES (SOLUTION) [24]

I. REQUIREMENTS

A. Narrative

A company employee system has a set of departments (DEPT# unique), each with a budget (DBUDGET) and manager (MGR#).

Each department has a set of employees (EMP# unique), a set of projects (PROJ# unique), and a set of offices (OFF# unique).

Each employee has a PHONE# (unique but shared by other employees) and is assigned to a project.

Each employee has a job history, each with a DATE, JOBTITLE and SALARY.

Each project has a budget (PBUDGET).

Each office has a set of phones.

B. Inputs

The GUI collects DEPT#, DBUDGET, MGR#, EMP#, PROJ#, PBUDGET, OFF#, PHONE#, DATE, JOBTITLE, SALARY.

C. Outputs

Report: find the offices that have employees from department D01.

II. ANALYSIS [25]

A. Modeling

1. Entity/Relationship (E/R) Diagram

ALTERNATIVELY:

ALTERNATIVE E/R DIAGRAM [26]

DEPT

DEPT#

DBUDGET

MGR#

$\downarrow$ $\downarrow$

PROJ

PROJ#

DEPT#

PBUDGET

OFF

OFF#

DEPT#

AREA

$\downarrow$ $\downarrow$

PHONE

PHONE#

OFF#

$\downarrow$ $\downarrow$

EMP

EMP#

PROJ#

PHONE#

$\downarrow$

JOB

EMP#

DATE

JOBTITLE

SALARY

2. Functional Dependency (FD) List [27]

DEPT# $\rightarrow$ DBUDGET, MGR#

MGR# $\rightarrow$ DEPT#

EMP# $\rightarrow$ DEPT#, PROJ#, OFF#, PHONE#

PROJ# $\rightarrow$ DEPT#, PBUDGET

OFF# $\rightarrow$ DEPT#, AREA

EMP#, DATE $\rightarrow$ JOBTITLE, SALARY

PHONE# $\rightarrow$ OFF#

3. FD Diagram

4. OO Set Diagram: UML [28]

III. DESIGN [29]

A. Preliminary Design

1. Data Design

b. External: Relation Schema

Depts(DEPT#, DBUDGET, MGR#)

Emps(EMP#, PROJ#, PHONE#)

Projs(PROJ#, DEPT#, PBUDGET)

Offices(OFF#, DEPT#, AREA)

Jobs(EMP#, DATE, JOBTITLE, SALARY)

Phones(PHONE#, OFF#)

This is a 3NF and BCNF decomposition.

BUT HERE ARE THE STEPS TO GET TO THE ABOVE SCHEMA.

Make each FD a relation, establish keys.

Depts(DEPT#, DBUDGET, MGR#)

Emps(EMP#, DEPT#, PROJ#, OFF#, PHONE#)

Projs(PROJ#, DEPT#, PBUDGET)

Offices(OFF#, DEPT#, AREA)

Jobs(EMP#, DATE, JOBTITLE, SALARY )

Phones(PHONE#, OFF#)

This design is SO-SO and would be typical of first attempt.

Not in 3NF.

Problem: Projs will have the same PROJ#, DEPT# pairs as Emps.

Cause: FD's were not a MINIMAL COVER (redundant FD's):

EMP# $\rightarrow$ DEPT#, EMP# $\rightarrow$ PROJ#, PROJ# $\rightarrow$ DEPT#

Solution: remove DEPT# from Emps.

Emps(EMP#, PROJ#, OFF#, PHONE# )

Problem: if PROJ#=NULL (``missing field''), don't know DEPT#.

ALTERNATIVE SOLUTION [30]

Alternative: remove DEPT# from Projs:

Projs(PROJ#, PBUDGET)

Problems:

No employee's assigned to project (``missing record''), DEPT# ???

Not 3NF because of DEPT# in Emps.

Offices will have the same OFF#, DEPT# pairs as EMP.

Cause: FD's were not a MINIMAL COVER (redundant FD's):

EMP# $\rightarrow$ DEPT#, EMP# $\rightarrow$ OFF#, Off# $\rightarrow$ DEPT#

Therefore, remove DEPT# from Emps as before.

Emps(EMP#, PROJ#, OFF#, PHONE# )

Problem: If not assigned project or office yet, don't know DEPT#

Alternative: remove DEPT# from Offices

Offices(OFF#, AREA)

Problems:

No employee's in office, don't know DEPT#

Not 3NF.

Phones will have the same PHONE#, OFF# pairs as Emps.

Cause: FD's were not a MINIMAL COVER (redundant FD's):

EMP# $\rightarrow$ OFF#, EMP# $\rightarrow$ PHONE#, PHONE# $\rightarrow$ OFF#

Therefore, remove OFF# from Emps.

Emps(EMP#, PROJ#, PHONE#)

Problem: if phone not assigned yet, can't locate the employee.

Alternative: remove the Phones relation.

Problems: can't install phones unless assigned to employee.

Not a good idea.

OK decomposition but not 3NF (due to DEPT#):

Depts(DEPT#, DBUDGET, MGR#)

Emps(EMP#, DEPT#, PROJ#, PHONE# )

Projs(PROJ#, PBUDGET)

Offices(OFF#, AREA)

Jobs(EMP#, DATE, JOBTITLE, SALARY)

Phones(PHONE#, OFF#)

Employee's on the same project will duplicate DEPT#

Employee's that share a phone will duplicate DEPT#

B. Detail Design [31]

1. Detail Data Design

b. External: SQL Data Dictionary

create table Depts ( 
  DEPT_NO char(8) primary key,
  DBUDGET float(10,2),
  MGR_NO char(8)
)
create table Emps (
  EMP_NO char(8) primary key,
  PROJ_NO char(8),
  PHONE_NO char(8),
  foreign key(PROJ_NO) references Projs,
  foreign key(PHONE_NO) references Phones
)
create table Projs (
  PROJ_NO char(8) primary key,
  DEPT_NO char(8),
  PBUDGET float(10,2),
  foreign key(DEPT_NO) references Depts
)
create table Offices (
  OFF_NO char(8) primary key,
  DEPT_NO char(8),
  AREA float,
  foreign key(DEPT_NO) references Depts
)
create table Jobs (
  EMP_NO char(8),
  JDATE char(6),
  JOBTITLE char(10),
  SALARY float(10,2),
  primary key(EMP_NO, JDATE)
)
create table Phones (
  PHONE_NO char(8),
  OFF_NO char(8),
  primary key(PHONE_NO),
  foreign key(OFF_NO) references Offices
)

2. Procedural Design [32]

b. External: SQL Query

select distinct Phones.OFF_NO
from   Projs, Emps, Phones
where  Projs.DEPT_NO = 'D01'
and    Projs.PROJ_NO = Emps.PROJ_NO
and    Emps.PHONE_NO = Phones.PHONE_NO

V. TEST

A. Data Sets

2. External

a. SQL Inserts

insert into Depts values('D01',10000.00,'Bill')
insert into Emps values('E01','P01','555-5555')
insert into Projs values('P01','D01',1000.00)
insert into Offices values('O01','D01',500.00)
insert into Jobs values('E01','01/01/97','Programmer',5000.00)
insert into Phones values('555-5555','O01')

b. Query Results

OFF_NO
O01