show the process of moving the following excel data into 1st, 2nd and 3rd normal
ID: 3745454 • Letter: S
Question
show the process of moving the following excel data into 1st, 2nd and 3rd normal forms. There should be an answer as to what this data looks like in 1st normal form, what this data looks like in 2nd normal form, and what this data looks like in 3rd normal form. I have given you the first few user view problems in Excel because this is an excellent tool to use to follow the data through. You might find it easier if you cut and paste the data in Excel and you work through 1st, 2nd and 3rd NF.
I dont know what to put in as the values. Please explain
Explanation / Answer
1NF - this form shouldn't contain any multi valued parameter.
So given table becomes....
1NF - Employees(EmployeeNumber,ProjectNumber,ProjectName,RateCategory,HourlyRate,EmployeeFirstName,EmployeeLastName)
If you see above 1NF form. EmployeeName has been converted into EmployeeFirstName,EmployeeLastName to remove multi valued parameters. Now table is in 1NF.
2NF - Given table must be 1NF and no partial dependencies. Now we need to break Employees table to remove partial dependencies. If you see Employees table ,ProjectName,HourlyRate is partial dependencies of EmployeeNumber where EmployeeFirstName,EmployeeLastName are funcitfunc dependencies. Below is 2NF tables.
2NF- Employees(EmployeeNumber,EmployeeFirstName,EmployeeLastName,ProjectNumber,RateCategory)
Project(ProjectNumber,ProjectName)
Rate(RateCategory,HourlyRate)
EmployeeNumber,ProjectNumber,RateCategory are primary keys of respective tables. And Employees table doesnt have any partial dependencies. Hence above tables are 2NF.
3 -NF - Tables must be in 2NF and no transitive functional dependencies.
If you see employee table ProjectNumber,RateCategory are transitive functional dependencies. We need to remove those dependencies to meet 3NF. Below are final tables.
3NF
Employees(EmployeeNumber,EmployeefirstName,EmployeeLastName)
Project(ProjectNumber,ProjectName)
Rate(RateCategory,HourlyRate)
Employee_Project_Rate(EmployeeNumber,RateCategory,ProjectNumber)
Now no transitive dependencies in employee table.
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.