Search code examples
databasedatabase-normalization3nf

Is this normalized up to 3rd NF


Projects(Project_id,Name,description,due_date) Emp_projects(Project_ID,Emp_id) Tasks(task_id,name,description,status,due_date,completed_date,Emp_id,Project_id)

An employee will work on none or many projects An employee will be assigned to one or many tasks related to the project A task will have only one employee assigned to it


Solution

  • Assuming that there are no multi-valued columns and all the values in a column are in same domain, it is in 1-NF.

    There seems to be no partial dependency either. i.e. None of the non-prime key depends on only part of the composite prime-key. There isn't even a table here where it would be possible since only the Emp_projects seems to have a composite candidate key which also happens to be only keys in it. So, we can say it is in 2-NF.

    For Projects, I assume that None of the non-primary key are candidate keys. I Am assuming same for (name,description,status,due_date,completed_date) in Tasks table and (Emp_id,Project_id) can not be a candidate key in any combination for this table since it is given that: "An employee will work on none or many projects An employee will be assigned to one or many tasks related to the project A task will have only one employee assigned to it".

    And none of the non-prime key seems to be dependent on any other non-prime key. So there is no trasistive dependency either.

    So I would say it is in 3-NF.