Question

Consider the relation scheme ASSIGN (EMP ID, PROJECT, PROJECT BUDGET, TIME SPENT BY PERSON ON PROJECT) containing the following FDs:

EMP ID, PROJECT → TIME SPENT BY PERSON ON PROJECT
PROJECT → PROJECT BUDGET
(a) State which attributes of ASSIGN are prime and which are nonprime.
(b) Is ASSIGN in 3NF, if not then find a decomposition of ASSIGN into 3NF?
(c) What about BCNF, if not then find a decomposition of ASSIGN into BCNF?

Answer 1

Prime attributes of ASSIGN are EMP ID and PROJECT, and nonprime are PROJECT BUDGET and TIME SPENT BY PERSON ON PROJECT. ASSIGN is not in 3NF because the nonprime attribute PROJECT BUDGET depends on a part of the candidate key. To normalize ASSIGN into 3NF and BCNF, we decompose it into two relations: ASSIGN1 (EMP ID, PROJECT, TIME SPENT) and ASSIGN2 (PROJECT, PROJECT BUDGET).

Step-by-step explanation:

To address the student's question about the relation scheme ASSIGN (EMP ID, PROJECT, PROJECT BUDGET, TIME SPENT BY PERSON ON PROJECT) that contains specified functional dependencies (FDs), we need to determine the following: which attributes are prime and nonprime, whether ASSIGN is in 3NF, and whether it is in BCNF.

Prime and Nonprime Attributes

Prime attributes are those attributes that are part of a candidate key. Nonprime attributes are not part of any candidate key. In the given relation scheme ASSIGN, the candidate key would be the combination of EMP ID and PROJECT since they can uniquely identify each record. Therefore, EMP ID and PROJECT are prime attributes. PROJECT BUDGET and TIME SPENT BY PERSON ON PROJECT would be considered as nonprime attributes.

Normalization to 3NF and BCNF

For a relation to be in Third Normal Form (3NF), all of its attributes should either be functionally dependent on the primary key, only on a candidate key, or on nothing at all (trivial dependencies). The given FDs are EMP ID, PROJECT → TIME SPENT BY PERSON ON PROJECT and PROJECT → PROJECT BUDGET. The relation is not in 3NF because PROJECT BUDGET is a nonprime attribute that depends on a subset of the candidate key (PROJECT).

To decompose ASSIGN into 3NF, we can create two relations:

1. 
   
3. ASSIGN1 (EMP ID, PROJECT, TIME SPENT BY PERSON ON PROJECT) with EMP ID, PROJECT as the key.
4. 
   
6. ASSIGN2 (PROJECT, PROJECT BUDGET) with PROJECT as the key.

In Boyce-Codd Normal Form (BCNF), a stricter form of 3NF, every determinant must be a candidate key. The relation is not in BCNF because PROJECT is not a candidate key by itself but it determines PROJECT BUDGET. The decomposition into BCNF would be the same as for 3NF:

1. 
   
3. ASSIGN1 (EMP ID, PROJECT, TIME SPENT BY PERSON ON PROJECT)
4. 
   
6. ASSIGN2 (PROJECT, PROJECT BUDGET)