Database normalisation problem

[robertshippey]

Dear all,

I'm unsure about normalisation between UNF and 1NF. Below is a list of UNF fields in a database. The attributes with [] repeat for each employee.

Employee_No,
Employee_Name,
Employee_Address,
[Qualification_Type,
Qualification_Description,
Subject,
Grade,
School_University_Name,
School_University_Address,
Awarding_Body,
Date_Qualification_Obtained,
Past_Job_CompanyName,
Past_Job_CompanyAddress,
Past_Job_Position,
Past_Job_Start_Date,
Past_Job_End_Date,
Past_Job_Reason_for_Leaving],
Job_Description,
Status,
Start_Date,
Salary_Code,
Salary_Amount,
Department_No,
Department_Name,
Department_Location,
Dept_Manager_Employee_No,
Supervisor_Employee_No,
[Course_No,
Course_Name,
Course_Start_Date,
Course_End_Date,
CourseCoordinator_Staff_No,
Course_Employee_Mark,
Total_Units,
[Unit_No,
Unit_Name,
Unit_Cost,
Unit_Weighting,
Unit_Outcome_Code,
Unit_Outcome_Description,
Employee_Unit_Mark,
[Assessment_Type,
Assessment_Description,
Assessment_Weighting,
Employee_Assessment_Mark]],
Course_Employee_Outcome_Code,
Course_Outcome_Description,
Total_Course_Cost,
Third_Party_SupplierNo,
Third_Party_Name,
Third_Party_Tutor_Name,
Third_Party_Address]

Here are the primary keys(*) and other fields for what I think 1NF should look like. I have split it into six tables.

Employee table:
*Employee_No
Employee_Name
Employee_Address
Job_Description,
Status,
Start_Date,
Salary_Code,
Salary_Amount,
Department_No,
Department_Name,
Department_Location,
Dept_Manager_Employee_No,
Supervisor_Employee_No,


Qualification table:
*Qualification_Type
*Subject
*Employee_No
Qualification_Description,
Grade,
School_University_Name,
School_University_Address,
Awarding_Body,
Date_Qualification_Obtained

Past Job table:
*Past_Job_Employee_Name
*Employee_No
Past_Job_CompanyAddress,
Past_Job_Position,
Past_Job_Start_Date,
Past_Job_End_Date,
Past_Job_Reason_for_Leaving

Course Table:
*Course_No
*Employee_No
*Course_Employee_Outcome_Code
Course_Name,
Course_Start_Date,
Course_End_Date,
CourseCoordinator_Staff_No,
Course_Employee_Mark,
Total_Units
Course_Employee_Outcome_Code,
Course_Outcome_Description,
Total_Course_Cost,
Third_Party_SupplierNo,
Third_Party_Name,
Third_Party_Tutor_Name,
Third_Party_Address

Unit table:
*Unit_No
*Course_No
Unit_Name,
Unit_Cost,
Unit_Weighting,
Unit_Outcome_Code,
Unit_Outcome_Description,
Employee_Unit_Mark

Assessment table:
*Assesment_Type
*Unit_No
Assessment_Description,
Assessment_Weighting,
Employee_Assessment_Mark


Can anyone advise if I have correctly implimented 1NF? Would the database relate properly and will it be fine to later move to 2NF and 3NF?

Many thanks in advance,
Robert Shippey

 

[John Big Booty]

Welcome to the Forum.

Perhaps this tutorial covering normalisation will give you some pointers.

 

[robertshippey]

So in your Tutorial (thanks for that by the way) you don't split the table into multiple tables for 1NF?

So in my example, I would just make a concatenated key of the following for the one big table:
*Employee_No
*Qualification_Type
*Subject
*Past_Job_Company_Name
*Course_No
*Unit_No
*Assessment_Type

Then when I move to 2NF I'll split those keys into their own table?

 

[John Big Booty]

They are concatenated in name only, that is you would not physically concatenate them in a table, only in a query to allow you to identify specific records.