Question

Consider the following relation with structure EMPLOYEE_DATA (EmployeeID, EmployeeDegree, DepartmentID, DepartmentName)

EmployeeID	EmployeeDegree	DepartmentID	DepartmentName
001	Mathematics	1004	R&D
001	Mathematics	1004	R&D
002	Info. Systems	2003	Cybersecurity
003	Computer Sci	5816	Software

The functional dependencies are as follows: EmployeeID → EmployeeDegree, DepartmentID → DepartmentName, (EmployeeID,DepartmentID) → (EmployeeDegree, DepartmentName).

Put the above relation into BCNF. For your solution use the shorthand TABLENAME (primarykey1, primarykey2, attribute1, attribute2).

Answer 1

Hey! my answer is given below ..... to appreciate my work please give positive Rating.....

Determine BCNF:
For relation R to be in BCNF, all the functional dependencies (FDs) that hold in R need to satisfy property that the determinants X are all superkeys of R. i.e. if X->Y holds in R, then X must be a superkey of R to be in BCNF.

We have relation with structure EMPLOYEE_DATA (EmployeeID, EmployeeDegree, DepartmentID, DepartmentName).

The functional dependencies are as follows:

• EmployeeID → EmployeeDegree,
• DepartmentID → DepartmentName
• (EmployeeID,DepartmentID) → (EmployeeDegree, DepartmentName).

Let,

• R is EMPLOYEE_DATA relation.
• A is EmployeeID
• B is EmployeeDegree
• C is DepartmentID
• D is DepartmentName

so we have R (A, B, C, D) and functional dependencies are A -->  B and C --> D and AC --> BD

Decompose R into BCNF form:

• If R is not in BCNF, we decompose R into a set of relations S that are in BCNF.
• This can be accomplished with a very simple algorithm:

1. Initialize S = {R}
2. While S has a relation R' that is not in BCNF do:   

• Pick a FD: X->Y that holds in R' and violates BCNF
•   Add the relation XY to S
•   Update R' = R'-Y

3,) Return S

Now, Decompose R(A,B,C,D)

1.) S = {ABCD} // Intialization S = {R}
2.) S = {ACD, AB} // Pick FD: A->B which violates BCNF
3.) S = {AC, AB, CD} // Pick FD: C->D which violates BCNF

4.) S = {AC, AB, CD} // Pick FD: AC->BD now, Relation is R is already decomposed in 3 Relations.
// Return S as all relations are in BCNF

Now, new Decomposed Relations are:

1. R1 (AB)
2. R2 (CD)
3. R3 (AC)

Thus,Relation  EMPLOYEE_DATA (EmployeeID, EmployeeDegree, DepartmentID, DepartmentName). is decomposed into a set of relations:

• EMPLOYEE_DATA (EmployeeID, EmployeeDegree)
• DEPARTMENT_DATA (DepartmentID, DepartmentName)
• EMPLOYEE_DEPARTMENT_TABLE (EmployeeID, DepartmentID)

that satisfies BCNF.

1.)  EMPLOYEE_DATA Table

EmployeeID	EmployeeDegree
001	Mathematics
002	Info. Systems
003	Computer Sci

2.)  DEPARTMENT_DATA Table

DepartmentID	DepartmentName
1004	R&D
2003	Cybersecurity
5816	Software

3.) EMPLOYEE_DEPARTMENT_TABLE

EmployeeID	DepartmentID
001	1004
002	2003
003	5816

thanks.....................