Understanding Functional Dependencies in Database Design

1. Introduction to Functional Dependencies

In relational database design, functional dependencies (FDs) describe relationships between attributes in a table. These relationships are critical to understanding how data is structured and normalized to avoid redundancy, inconsistency, and anomalies.

Definition of Functional Dependency

An attribute B is functionally dependent on attribute A if, for every value of A, there is exactly one corresponding value of B. This is denoted as:

A → B

• A is called the determinant.
• B is called the dependent.

For example:

• In a table of students: StudentID → StudentName
  • A StudentID uniquely determines a StudentName.

---

2. Types of Functional Dependencies

2.1 Full Functional Dependency

An attribute is fully functionally dependent on a set of attributes if it depends on all parts of the composite key (and not just a subset).

Example

Table: OrderDetails

OrderID	ProductID	Quantity
1	101	2
1	102	1
2	101	4

• Composite Key: (OrderID, ProductID)
• Quantity is fully dependent on (OrderID, ProductID).
• If you remove ProductID, Quantity cannot be uniquely determined.

---

2.2 Partial Dependency

A partial dependency occurs when a non-prime attribute is dependent on only a part of a composite primary key.

Example

Table: OrderDetails

OrderID	ProductID	OrderDate	Quantity
1	101	2023-01-01	2
1	102	2023-01-01	1
2	101	2023-01-02	4

• Composite Key: (OrderID, ProductID)
• OrderDate depends only on OrderID, not the full composite key.

---

2.3 Transitive Dependency

A transitive dependency occurs when a non-prime attribute depends on another non-prime attribute instead of a primary key.

Example

Table: Employee

EmployeeID	DepartmentID	DepartmentName
1	D01	HR
2	D02	Finance
3	D01	HR

• Key: EmployeeID
• DepartmentName depends on DepartmentID (a non-prime attribute), which itself depends on EmployeeID.

---

3. Identifying Dependencies: Practice Problem

Here’s a sample table. Identify the types of dependencies (Full, Partial, or Transitive):

InvoiceID	ProductID	CustomerID	CustomerName	Quantity	ProductName
1	101	C001	Alice	2	Phone
1	102	C001	Alice	1	Charger
2	101	C002	Bob	1	Phone

Questions:

1. Is CustomerName dependent on CustomerID or InvoiceID?
2. Is ProductName dependent on ProductID or both InvoiceID and ProductID?
3. What dependencies can you identify for Quantity?

---

4. Removing Partial and Transitive Dependencies

4.1 Resolving Partial Dependencies

To eliminate partial dependencies, split the table into multiple tables so that every non-prime attribute is fully dependent on the entire primary key.

Example: Fixing Partial Dependencies

Original Table: OrderDetails

OrderID	ProductID	OrderDate	Quantity
1	101	2023-01-01	2
1	102	2023-01-01	1
2	101	2023-01-02	4

Decomposition:

1. Orders Table:
   • Primary Key: OrderID
   • Attributes: OrderID, OrderDate
   OrderIDOrderDate12023-01-0122023-01-02
2. OrderDetails Table:
   • Primary Key: (OrderID, ProductID)
   • Attributes: OrderID, ProductID, Quantity
   OrderIDProductIDQuantity110121102121014

---

4.2 Resolving Transitive Dependencies

To eliminate transitive dependencies, split the table into multiple tables so that non-prime attributes depend only on the primary key.

Example: Fixing Transitive Dependencies

Original Table: Employee

EmployeeID	DepartmentID	DepartmentName
1	D01	HR
2	D02	Finance
3	D01	HR

Decomposition:

1. Employees Table:
   • Primary Key: EmployeeID
   • Attributes: EmployeeID, DepartmentID
   EmployeeIDDepartmentID1D012D023D01
2. Departments Table:
   • Primary Key: DepartmentID
   • Attributes: DepartmentID, DepartmentName
   DepartmentIDDepartmentNameD01HRD02Finance

---

5. Problems with Leaving Partial and Transitive Dependencies

If partial or transitive dependencies remain in the design:

1. Data Redundancy: Repeated data across rows leads to inefficient storage.
   • Example: DepartmentName repeated for every employee.
2. Update Anomalies: Changes in one instance of data require updating multiple rows.
   • Example: Updating a department name requires updating all employees in that department.
3. Insertion Anomalies: Adding new data can be problematic without unnecessary fields.
   • Example: Adding a department requires creating a placeholder employee.
4. Deletion Anomalies: Deleting one piece of data can inadvertently remove related data.
   • Example: Deleting the last employee of a department could lose the department’s name.

---

6. Conclusion

• Full Functional Dependency ensures attributes are fully dependent on the primary key.
• Partial Dependencies are resolved by splitting tables into smaller, focused tables.
• Transitive Dependencies are removed by ensuring all attributes depend only on the primary key.
• Normalization improves data integrity, consistency, and reduces redundancy.

---

Challenge: Dependency Identification

Use the table below to identify all dependencies (Full, Partial, or Transitive). Then, normalize the table to eliminate partial and transitive dependencies.

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

Tasks:

1. Identify all dependencies.
2. Normalize the table into smaller tables.
3. Discuss how normalization improves the design.

solution