Database Dependacy Problem Solutions

Recap

The table is as follows:

ProjectID	EmployeeID	EmployeeName	ProjectName	DepartmentID	DepartmentName
P01	E01	Alice	Alpha	D01	HR
P01	E02	Bob	Alpha	D01	HR
P02	E03	Charlie	Beta	D02	Finance

Step 1: Analyze the Dependencies

1. Primary Key:
   In the given table, the composite primary key is (ProjectID, EmployeeID), as each row uniquely represents an employee assigned to a project.
2. Functional Dependencies:
   Let’s identify the functional dependencies in the table:
   • ProjectID → ProjectName: A project ID uniquely determines the project name.
   • EmployeeID → EmployeeName, DepartmentID: An employee ID uniquely determines the employee name and the department they belong to.
   • DepartmentID → DepartmentName: A department ID uniquely determines the department name.
3. Partial Dependencies:
   Partial dependencies exist when a non-prime attribute (not part of the primary key) depends on only part of the composite key:
   • ProjectName depends only on ProjectID (not the full composite key).
   • EmployeeName and DepartmentID depend only on EmployeeID (not the full composite key).
4. Transitive Dependencies:
   A transitive dependency exists when a non-prime attribute depends on another non-prime attribute:
   • DepartmentName depends on DepartmentID, which in turn depends on EmployeeID.

---

Step 2: Normalize the Table

First Normal Form (1NF)

The table is already in 1NF because it does not have multi-valued attributes or repeating groups.

---

Second Normal Form (2NF)

To achieve 2NF, we need to eliminate partial dependencies.

1. Split the table into two tables:
   • Projects Table: Remove attributes dependent only on ProjectID.
   • Employees Table: Remove attributes dependent only on EmployeeID.

Decomposed Tables:

Projects Table:

ProjectID	ProjectName
P01	Alpha
P02	Beta

Employees Table:

EmployeeID	EmployeeName	DepartmentID
E01	Alice	D01
E02	Bob	D01
E03	Charlie	D02

Assignment Table (Bridging Table):

ProjectID	EmployeeID
P01	E01
P01	E02
P02	E03

---

Third Normal Form (3NF)

To achieve 3NF, we need to eliminate transitive dependencies.

1. Split the Employees Table to remove the dependency between DepartmentID and DepartmentName:
   • Create a separate Departments Table for department details.

Decomposed Tables:

Employees Table:

EmployeeID	EmployeeName	DepartmentID
E01	Alice	D01
E02	Bob	D01
E03	Charlie	D02

Departments Table:

DepartmentID	DepartmentName
D01	HR
D02	Finance

---

Final Database Design

The fully normalized database consists of four tables:

Projects Table

ProjectID	ProjectName
P01	Alpha
P02	Beta

Employees Table

EmployeeID	EmployeeName	DepartmentID
E01	Alice	D01
E02	Bob	D01
E03	Charlie	D02

Departments Table

DepartmentID	DepartmentName
D01	HR
D02	Finance

Assignment Table

ProjectID	EmployeeID
P01	E01
P01	E02
P02	E03

---

Step 3: Benefits of Normalization

1. Reduces Redundancy:
   • DepartmentName is no longer repeated for every employee.
   • ProjectName is stored only once in the Projects Table.
2. Prevents Update Anomalies:
   • If the department name for D01 changes, it only needs to be updated in the Departments Table.
3. Prevents Deletion Anomalies:
   • Deleting the last employee from a department no longer causes the department’s information to be lost.
4. Improves Data Integrity:
   • Relationships between tables are explicitly defined using foreign keys.

---

Step 4: Problems with Leaving Partial and Transitive Dependencies

If we had left the original table unnormalized:

1. Redundancy:
   • DepartmentName would be repeated for every employee.
   • ProjectName would be repeated for every employee working on that project.
2. Update Anomalies:
   • Changing the department name would require updating multiple rows, increasing the risk of inconsistency.
3. Insertion Anomalies:
   • Adding a new department without employees would require creating a placeholder row with dummy data.
4. Deletion Anomalies:
   • Deleting all employees from a department would result in the department’s information being lost.

---

Conclusion

By breaking the table into smaller, focused tables (Projects, Employees, Departments, and Assignments), we eliminate redundancy, maintain consistency, and prevent anomalies. This exercise demonstrates the importance of functional dependencies and the role of normalization in designing efficient databases.

SQL to create and query these tables