Database Dependencies – Managing Multiple Functional Dependencies

dependencies

I am looking for the 3NF form of the following relation:

1. emp_no -> phone_no, office_no, dep_no, proj_no
2. emp_no, date -> job, salary
3. phone_no -> dep_no, office_no, area
4. proj_no -> dep_no, p_buject
5. dep_no -> mgr_emp_no, d_budget
6. mgr_emp_no -> dep_no

I can only derive the first dependency to:

1. emp_no -> phone_no, office_no, proj_no

because proj_no and phone_no are depended by dep_no. Then the rest is not decomposable, therefore they are copied over on my sheet.

As you can see, 5 and 6 are dependent on each other. I looked through resources on the Internet, I have never seen such relationship before (being dependent and parent of each other). How can I make these functional dependencies to 3NF format?

Best Answer

To bring a schema in 3NF, first you have to find a canonical cover of the dependencies. Then, you can apply either the “analysis” algorithm to find the Boyce-Codd Normal Form (which is more strict that the 3NF) or you can apply the “synthesis” algorithm to find the 3NF. In this case, both the algorithms gives the same results.

Here is a canonical cover of the dependencies:

date, emp_no → job
date, emp_no → salary
dep_no → d_budget
dep_no → mgr_emp_no
emp_no → phone_no
emp_no → proj_no
mgr_emp_no → dep_no
phone_no → area
phone_no → dep_no
phone_no → office_no
proj_no → dep_no
proj_no → p_buject

Let's find the 3NF with the synthesis algorithm. The first step is to collect together all the dependencies of the canonical cover with the same left part, that is:

date, emp_no → job
date, emp_no → salary

dep_no → d_budget
dep_no → mgr_emp_no

emp_no → phone_no
emp_no → proj_no

mgr_emp_no → dep_no

phone_no → area
phone_no → dep_no
phone_no → office_no

proj_no → dep_no
proj_no → p_buject

The second step creates a relation for each group, with the key given by the common left part:

R1 (date, emp_no, job, salary)          key: (date, emp_no)
R2 (dep_no, d_budget, mgr_emp_no)       key: (dep_no)
R3 (emp_no, phone_no, proj_no)          key: (emp_no)
R4 (mgr_em_no, dep_no)                  key: (mgr_em_no)
R5 (phone_no, area, dep_no, office_no)  key: (phone_no)
R6 (proj_no, dep_no, p_buject)          key: (proj_no)

The third step remove relations that are contained in another: in this case the relation R4 is contained inside R2, so R4 is removed.

The last step checks if any relation contains one of the keys of the original relation, and if this is not true add a new relation with one of the keys: since the original relation has (only) the key (date, emp_no), and the relation R1 contains it, no new relation must be added.

So the 3NF is the following:

R1 (date, emp_no, job, salary)          key: (date, emp_no)
R2 (dep_no, d_budget, mgr_emp_no)       keys: (dep_no), (mgr_em_no)
R3 (emp_no, phone_no, proj_no)          key: (emp_no)
R5 (phone_no, area, dep_no, office_no)  key: (phone_no)
R6 (proj_no, dep_no, p_buject)          key: (proj_no)

and reasoning from these relations you can infer the entities represented by each of them and the foreign keys towards other relations:

R1 represents the history of jobs of the employee, with key: (date, emp_no),
    and emp_no as FK
R2 represents a department, which is identified by dep_no but also by the number 
    of the manager (which is a FK for employees)
R3 represents an employee, with key emp_no, with FKs phone_no and dep_no
R5 represents the “place” of an employee, with key phone_no and FK dep_no
R6 represents information about a project, with key proj_no and FK dep_no

Finally, note that the dependencies 4 and 5, that you have called “Multiple Functional Dependency to the same dependency”, are in effect particular, in the sense that they are transformed in a relation (department) with two primary keys, the number of the department and the number of the manager of the department. And this is managed by the algorithm in the third step by merging the two relations previously obtained (and for this reason the relation has two primary keys).

Related Question