Normalisation Exercise 5

Normalisation Exercise 5

Staff Employment Agency

Scenario Author: Colin Ritchie

Job seekers are asked to complete an application form when they apply to the agency looking for employment– this data needs to be stored in a database. The data will include the details of the client (including name, telephone number, qualifications and previous employment history) as well as the agency consultant responsible for that individual.

Note: To simplify the number of fields I have used a single field for a name rather than first name and surname.

Note: It is assumed that a client will not have worked for a previous employer twice.

Note: I would personally use a key field for qual rather than text, same for qualification body.

UNF

Client (Cl_Reg, Cl_Name, Cl_Tel, Cons_ID, Cons_Name, Cons_Ex, Qual, Qual _Lev,Qual _Year, Prev_Emp_Name, Prev_Emp_Tel, Prev_Job_Title, Reason_FL, Final_Pay, Date_Start, Date_Left)

Sample data is shown below:

Client Reg / Client
Name / Client
Tel / Cons
ID / Cons
Name / Cons
Ext / Qual
/ Qual
Level / Qual
Year / Prev
Emp
Name / Prev
Emp
Tel / Prev
Job
Title / Reason
For
Leaving / Final
Pay / Date Start / Date
Left
B01 / Fred Bloggs / 01782 123456 / 1 / Jeremy
Smythe / 3443 / HNC / 4 / 1997 / Morrisons / 01782
111234 / Admin Assistant / Temporary / £6 / 1/1/1996 / 31/1/2011
A Level / 3 / 1995
F01 / Katie
French / 01902
182735 / 2 / Timothy
Holland / 1112 / MBA / 7 / 2008 / SOT
College / 01782
208208 / PA to HOF / Health / £10 / 5/9/2009 / 30/4/2011
BA / 6 / 2006 / Fradley
Builders / 01785
808121 / Secretary / New Position / £6 / 31/1/2006 / 1/9/2009
Cert / 2 / 2002 / Olde Toyshop / 01902
654322 / Sales / Temporary / £4 / 2/2/2001 / 5/1/2006

Solution

1NF

Qualification (awards) and previous employment data occurs several times for each client so we need to be put into separate groups (ClientQual and Employment) but bringing the key from the client into both new tables as a composite key in both cases.

Note: As a person can have two qualifications with the same award you would be better creating a new key field in ClientQual rather than using a composite key.

Note: If you wanted to factor in a client working for an employer more than once you could make a key of three fields and include the ‘Date Start’.

Client(Cl_Reg, Cl_Name, Cl_Tel, Cons_ID, Cons_Name, Cons_Ext)

ClientQual (ClientQualID, Cl_Reg, Qual, Qual _Lev, Qual _Year)

Employment (Cl_Reg, Prev_Emp_Name, Prev_Emp_Tel, Prev_Job_Title, Reason_FL, Final_Pay, Date_Start, Date_Left)

2NF

In the ClientQual table Qual_Lev relates solely to the Qual key (remember ClientQualID is in the ClientQual table because a client may have more than one qualification at the same level so this won’t be copied into a new Qual table).

In the Employment table previous employer telephone number (Prev_Emp_Tel) is only dependent upon the previous employer name.

Client(Cl_Reg, Cl_Name, Cl_Tel, Cons_ID, Cons_Name, Cons_Ext)

ClientQual (ClientQualID, Cl_Reg, Qual, Qual _Year)

Employment (Cl_Reg, Prev_Emp_Name, Prev_Job_Title, Reason_FL, Final_Pay, Date_Start, Date_Left)

Qual (Qual, Qual _Lev)

Prev_Employer (Prev_Emp_Name, Prev_Emp_Tel)

3NF

In the Client table Cons_Name and Cons_Ext are functionally dependent uponCons_ID so can be removed from Client and form a new Consultant table.

Client(Cl_Reg, Cl_Name, Cl_Tel, Cons_ID)

ClientQual (ClientQualID, Cl_Reg, Qual, Qual _Year)

Employment(Cl_Reg, Prev_Emp_Name, Prev_Job_Title, Reason_FL, Final_Pay, Date_Start, Date_Left)

Prev_Employer(Prev_Emp_Name, Prev_Emp_Tel)

Qual (Qual, Qual _Lev)

Consultant (Cons_ID, Cons_Name, Cons_Ext)