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

PROJECT-ASSIGNMENT
EmpId Name Salary Position Bonus DptNo DeptName Manager ProjNo ProjDesc ChrgCD Start End Hrs

100 Simpson 35000 Analyst 5000 3 Design Ross 23 DB design D100 Apr-02 Jul-02 200

140 Beeton 28000 Technician 3000 2 Operations Martin 14 Network cabling N140 Sep-02 Oct-02 120

160 Davis 30000 Technician 3000 4 Tech Suprt Lucero 14 Network cabling S160 Sep-02 Nov-02 150

36 Network testing S160 Nov-02 Dec-02 100

190 Berger 45000 DBA 6000 1 DB Suprt Rawlins 45 Physical design D190 Aug-02 Nov-02 300

48 Space allocation S190 Nov-02 Dec-02 80

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

110 Covino 34000 Analyst 5000 5 Analysis Williams 31 Forms D110 Mar-02 May-02 120

25 Reports D110 May-02 Jul-02 150

120 Brown 35000 Analyst 5000 5 Analysis Williams 11 Order entry D120 Jul-02 Sep-02 300

180 Smith 30000 Programmer 4000 6 Programming Goldner 31 Forms C180 Sep-02 Nov-02 250

25 Reports C180 Nov-02 Dec-02 200

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

12 Inventory Control P200 Oct-02 Dec-02 200

13 Invoicing P200 Nov-02 Dec-02 100

100 Simpson 35000 Analyst 5000 3 Design Ross 31 Forms

DB design

D100 Aug-02 Oct-02 150

130 Clemens 38000 Analyst 5000 3 Design Ross 23 D130 Apr-02 Jun-02 200

Figure 10-2 Table created from information requirements.

Before proceeding further, let us have a brief explanation of the column named
ChrgCD. When an employee is assigned to a project, a charge code is given for that
assignment. The charge code depends on the type of work done by the employee
in that assignment irrespective of his or her position or title. For example, when
Simpson, an analyst, does design work in a project, a charge code of D100 is given
for that assignment; when he does coding work in another project, a charge code of
C100 is given for this assignment. Charge codes indicate the type of work done by
an employee in the various projects.
Next, observe the projects for Davis, Berger, Covino, Smith, and Rogers. Each of
these employees has been assigned to multiple projects. The resulting relational
database must contain information about these multiple assignments. However,
looking at the rows for these employees, these rows contain multiple values for some
attributes. In other words, not all columns contain atomic or single-valued attribute
values. This is a violation of the attribute atomicity requirement in a relational data
model. Therefore, the random PROJECT-ASSIGNMENT table we created quickly
cannot be part of true relational data model.
Let us now examine the table further and see how it will hold up when we try to
manipulate the data contents. As indicated in Figure 10-1, a proper relational data
model must avoid data redundancies and also ensure that data manipulation will
not cause problems. When we attempt to use the data model for data manipulation,
you will find that we run into three types of problems or anomalies as noted below:

Update anomaly. Occurs while updating values of attributes in the database.
Deletion anomaly. Occurs while deleting rows from a relation.
Addition anomaly. Occurs while adding (inserting) new rows in a relation.

We will discuss these anomalies in the next subsections. Try to understand the
nature of these problems and how our PROJECT-ASSIGNMENT table has such
problems and therefore cannot be correct. Unless we remove these anomalies, our
table cannot be part of a true relational model.