DATA NORMALIZATION METHOD - Database Design and Development: An Essential Guide for IT Professionals - page 309

EMPLOYEE-PROJECT

EmpId ProjNo ChrgCD Start End Hrs

PROJECT

100
140
160
160
190
190
100
110
110
120
180
180
200
200
200
100
130

23
14
14
36
45
48
25
31
25
11
31
25
11
12
13
31
23

D100 Apr-02 Jul-02 200

ProjNo ProjDesc

N140 Sep-02 Oct-02 120

S160 Sep-02 Nov-02 150

23
14
36
45
48
25
31
11
12
13

DB design
Network cabling
Network testing
Physical design
Space allocation
Reports
Forms
Order entry
Inventory Control

S160 Nov-02 Dec-02 100

D190 Aug-02 Nov-02 300

S190 Nov-02 Dec-02 80

C100 Oct-02 Nov-02 100

D110 Mar-02 May-02 120

D110 May-02 Jul-02 150

D120 Jul-02 Sep-02 300

C180 Sep-02 Nov-02 250

C180 Nov-02 Dec-02 200

D200 Sep-02 Oct-02 200

Invoicing P200 Nov-02 Dec-02 200

P200 Nov-02 Dec-02 100

D100 Aug-02 Oct-02 150

D130 Apr-02 Jun-02 200

EMPLOYEE

EmpId Name Salary Position Bonus DptNo DeptName Manager

100
140
160
190
110
120
180
200
130

Simpson 35000 Analyst 5000 3

2
4
1
5
5
6
6
3

Design Ross

Beeton 28000 Technician 3000 Operations Martin

Davis 30000 Technician 3000 Tech Suprt Lucero

Berger 45000 DBA 6000 DB Suprt Rawlins

Covino 34000 Analyst 5000 Analysis Williams

Brown 35000 Analyst 5000 Analysis Williams

Smith 30000 Programmer 4000 Programming Goldner

Rogers 32000 Programmer 4000 Programming Goldner

Clemens 38000 Analyst 5000 Design Ross

Figure 10-4 Data model in second normal form.

into the first normal form. Compare the relations shown in Figure 10-4 to the pre-
vious version in Figure 10-3. Apply the tests to the transformed version of the rela-
tions contained in Figure 10-4.

Update: Correction of name “Simpson” to “Samson”
The correction has to be made only in one row in EMPLOYEE rela-
tion. The update anomaly has disappeared.

Deletion: Deletion of data about Beeton
This deletion will unintentionally delete data about Department 2.
Deletion anomaly still persists.

Addition: Addition of data about new employee Potter
You can now add new employee Potter to the database in
EMPLOYEE relation. Addition anomaly has disappeared.

So you note that, although this step has resolved the problem of partial key
dependencies, still some data manipulation problems remain. Nevertheless, this
step has removed a major deficiency from the data model. We have to proceed to
the next steps and examine the effect of data manipulation operations.

Third Normal Form

After transformation to the second normal form, you note that a particular type of
functional dependencies is removed from the preliminary data model and that the
data model is closer to becoming a correct and true relational data model. In the