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

PROJECT-ASSIGNMENT
Name

EmpId Salary Position Bonus DptNo DeptName Manager ProjNo ProjDesc ChrgCD Start End Hrs

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

Simpson 35000 Analyst 5000 3

2
4
4
1
1
3
5
5
5
6
6
6
6
6
3
3

Design Ross 23

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

DB design D100

N140
S160
S160
D190
S190
C100
D110
D110
D120
C180
C180
D200
P200
P200
D100
D130

Apr-02 Jul-02 200

Beeton 28000 Technician 3000 Operations Martin Network cabling Sep-02 Oct-02 120

Davis 30000 Technician 3000 Tech Suprt Lucero Network cabling Sep-02 Nov-02 150

Davis 30000 Technician 3000 Tech Suprt Lucero Network testing Nov-02 Dec-02 100

Berger 45000 DBA 6000 DB Suprt Rawlins Physical design Aug-02 Nov-02 300

Berger 45000 DBA 6000 DB Suprt Rawlins Space allocation Nov-02 Dec-02 80

Simpson 35000 Analyst 5000 Design Ross Reports Oct-02 Nov-02 100

Covino 34000 Analyst 5000 Analysis Williams Forms Mar-02 May-02 120

Covino 34000 Analyst 5000 Analysis Williams Reports May-02 Jul-02 150

Brown 35000 Analyst 5000 Analysis Williams Order entry Jul-02 Sep-02 300

Smith 30000 Programmer 4000 Programming Goldner Forms Sep-02 Nov-02 250

Smith 30000 Programmer 4000 Programming Goldner Reports Nov-02 Dec-02 200

Rogers 32000 Programmer 4000 Programming Goldner Order entry Sep-02 Oct-02 200

Rogers 32000 Programmer 4000 Programming Goldner Inventory Control Oct-02 Dec-02 200

Rogers 32000 Programmer 4000 Programming Goldner Invoicing Nov-02 Dec-02 100

Simpson 35000 Analyst 5000 Design Ross Forms Aug-02 Oct-02 150

Clemens 38000 Analyst 5000 Design Ross DB design Apr-02 Jun-02 200

Figure 10-3 Data model in first normal form.

Let us examine whether the transformation step has rectified the other types of
update, deletion, and addition anomalies encountered before the model was trans-
formed into first normal form. Compare the PROJECT-ASSIGNMENT relation
shown in Figure 10-3 to the earlier version in Figure 10-2. Apply the tests to the
transformed version of the relation contained in Figure 10-3.

Update: Correction of name “Simpson” to “Samson”
The correction has to be made in multiple rows. Update anomaly still
persists.

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
Cannot add new employee Potter to the database until he is assigned
to a project. Addition anomaly still persists.

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

Second Normal Form

Recall the discussion on functional dependencies in Chapter 8 covering the prop-
erties and rules of the relational data model. If the value of one attribute deter-