Question

decomposing a schema into bcnf

• let \\(r\\) be a schema \\(r\\) that is not in bcnf. let \\(\alpha \

ightarrow \beta\\) be the fd that causes a violation of bcnf.

• we decompose \\(r\\) into:
• \\((\alpha \cup \beta)\\)
• \\((r - (\beta - \alpha))\\)
• in our example of \\(in\\_dep\\),
• \\(\alpha = dept\\_name\\)
• \\(\beta = building, budget\\)

and \\(in\\_dep\\) is replaced by

• \\((\alpha \cup \beta) = ( dept\\_name, building, budget )\\)
• \\((r - (\beta - \alpha)) = ( id, name, dept\\_name, salary )\\)

Explanation:

Identify the original schema and violation

The slide describes decomposing a schema \(R\) into Boyce-Codd Normal Form (BCNF).
From the example, the original schema is \(in\_dep\).
The attributes of the decomposed schemas are:

• \(\alpha \cup \beta = (\text{dept\_name}, \text{building}, \text{budget})\)
• \((R - (\beta - \alpha)) = (\text{ID}, \text{name}, \text{dept\_name}, \text{salary})\)

Combining all unique attributes from these two decomposed schemas gives the original schema \(R\):

$$ R = \{\text{ID}, \text{name}, \text{dept\_name}, \text{salary}, \text{building}, \text{budget}\} $$

The functional dependency (FD) causing the BCNF violation is \(\alpha
ightarrow \beta\), where:

• \(\alpha = \{\text{dept\_name}\}\)
• \(\beta = \{\text{building}, \text{budget}\}\)

Thus, the violating FD is:

$$ \text{dept\_name} ightarrow \text{building}, \text{budget} $$

Apply the BCNF decomposition rules

The BCNF decomposition rule splits a violating schema \(R\) using a violating FD \(\alpha
ightarrow \beta\) into two schemas:

1. \(R_1 = \alpha \cup \beta\)
2. \(R_2 = R - (\beta - \alpha)\)

Let's calculate each component step-by-step:

• \(\beta - \alpha = \{\text{building}, \text{budget}\} - \{\text{dept\_name}\} = \{\text{building}, \text{budget}\}\)
• \(R_1 = \{\text{dept\_name}\} \cup \{\text{building}, \text{budget}\} = \{\text{dept\_name}, \text{building}, \text{budget}\}\)
• \(R_2 = \{\text{ID}, \text{name}, \text{dept\_name}, \text{salary}, \text{building}, \text{budget}\} - \{\text{building}, \text{budget}\} = \{\text{ID}, \text{name}, \text{dept\_name}, \text{salary}\}\)

Verify the handwritten correction

There is a handwritten note in the image pointing to the formula \((R - (\beta - \alpha))\).
The note points to \(\beta - \alpha\) and has a correction pointing to \(\beta\) and \(\alpha\), with the text "building, budget" written below.
This confirms that:

$$ \beta - \alpha = \{\text{building}, \text{budget}\} $$

And subtracting this set from \(R\) leaves:

$$ \{\text{ID}, \text{name}, \text{dept\_name}, \text{salary}\} $$

This matches the slide's output perfectly.

Answer:

Based on the provided slide, the BCNF decomposition of the schema \(R = \text{in\_dep}\) with the violating functional dependency \(\alpha
ightarrow \beta\) is formulated as follows:

• Original Schema \(R\):

$$ R = (\text{ID}, \text{name}, \text{dept\_name}, \text{salary}, \text{building}, \text{budget}) $$

• **Violating Functional Dependency (\(\alpha

ightarrow \beta\))**:

$$ \text{dept\_name} ightarrow \text{building}, \text{budget} $$

where \(\alpha = \{\text{dept\_name}\}\) and \(\beta = \{\text{building}, \text{budget}\}\).

• Decomposed Schemas:

1. \(R_1 = \alpha \cup \beta = (\text{dept\_name}, \text{building}, \text{budget})\)
2. \(R_2 = R - (\beta - \alpha) = (\text{ID}, \text{name}, \text{dept\_name}, \text{salary})\)