Need help with normalization

[theashman88]

I have an assignment where I'm supposed to normalize up to three normalizations for a table but the teacher didn't explain it well.

Empno Pro# Hours Pro_name Pro_loc Ename Job Job# Pay_rate
56789 1100 32.5 x Apex Smith, John Database Deisgner 100 90
45555 1100 52.5 x Apex May, Joyce SQL Programmer 200 45
56789 1100 20 x Apex Smith, John System Design 110 70
45555 2100 7.5 y Tokyo May, Joyce SQL Programmer 200 45
56789 2100 7.5 y Tokyo Smith, John Database Design 100 90
56789 2100 20 y Tokyo Smith, John System Design 110 70
56789 2100 10 y Tokyo Smith, John System Analysis 120 80
45555 2100 10 y Tokyo Smith, John Business Analysis 130 90
84444 3100 10 z Zary Jones, Jay Database Design 100 90
84444 3100 40 z Zary Jones, Jay DBA 140 80
53453 3100 40 z Zary Wong, Lei SQL Programmer 200 45
53453 3100 10 z Zary Wong, Lei PL/SQL Progammer 210 65

The steps go like this.
1. The table must be an un-normalizaed table
2. Highlight the repeating-group data columns (Which ones are the repeating, I thought it was pro-name, pro#, and pro_loc, but I think its the other ones.)
3. Fill in blank
4. Move the highlighted columns to a new table
5. Copy and paste columns from old table to new table (which column do I choose)
6. Eliminate the repeating or identical records
7. Set up primary key
8. Write down the table structure in english

The biggest thing I need help on is step 2 finding out which columns are the repeating ones and which column and in step 5 do I choose to add to the new table.

Any help would be appreciated. If I messed up on how I posted this please let me know.

Thanks

[pritaeas]

For step 2 an example of repeating data is "Tokyo" or "System Design". Highlight ALL you think apply.

[1stDAN]

First, your given table is not in first normal form for Ename is not atomic, for example "Smith, John". To get 1NF, first introduce two new columns firstName and lastName in place of Ename.

Furthermore, there are various repeating groups consisting of two or more columns:

(Empno, lastName, firstName): 56789, Smith, John or 53453, Wong, Lei
(Pro#, Pro_name, Pro_loc): 1100, x, Apex or 2100, y, Tokio            
(Job, Job#): Database Design, 100 or SQL Programmer, 200

which must also be eliminated to get 1NF.

Second, we create new tables for each repeating group and introduce/identify their primary keys:

Employee
(Empno, lastName, firstName)
56789,  Smith,    John
45555,  May,      Joyce
84444,  Jones,    Jay
53453,  Wong,     Lei
Primary key: Empno

Project
(Pro#, Pro_name, Pro_loc)
1100,  x,        Apex 
2100,  y,        Tokyo 
3100,  z,        Zary
primary key: Pro# 


Job
(Job,              Job#, Pay_rate)
Database Design,   100,  90
SQL Programmer,    200,  45
System Design,     110,  70
System Analysis,   120,  80
Business Analysis, 130,  90
DBA,               140,  80
PL/SQL Progammer,  210,  65
Primary key: Job#

After the primary keys Empno, Pro# and Job# have been introduced/ identfied all appertaining columns can now be removed from the original ProjectResource, except of a copy of the primary-key columns what is necessay for reference purposes. These remaining columns are foreign keys. The reduced table ProjectResource is:

ProjectResource
(Empno, Pro#, Hours, Job#)

56789   1100  32.5   100 
45555   1100  52.5   200
56789   1100  20     110
45555   2100  7.5    200
56789   2100  7.5    100
56789   2100  20     110
56789   2100  10     120
45555   2100  10     130
84444   3100  10     100
84444   3100  40     140
53453   3100  40     200
53453   3100  10     210

Table ProjectResource has three references (Empno, Pro#, Job#) to other tables. Obviously, the remaining instance of ProjectResource shows that (Empno, Pro#, Job#) also uniquely identifies each row (I hope I haven't overlooked duplicates). Therefore we determine that this triple is primary key of ProjectResource, additionally to serving as foreign keys. (Do not introduce a surrogate key here what would defeat all efforts.)

Finally, are you able to determine the current normal form of each of your four new tables?

Addition: The subset of a single column which has the same value in multiple rows is also a repeating group in principle. However there wouldn't be any benefit if such a single column had been removed from the original table because a reference (foreign key) must always be left which can only be the just removed column itself or a just-for-that-purpose introduced surrogate key.