Database normalization lab / NORMALIZATION
RELATIONAL DESIGN / 01
One table. How many repeated facts?
Change one record and watch a repeated fact become inconsistent.
Enrollment records
| SStudent ID | NName | DDept. ID | MDept. name | CCourse ID | GGrade |
|---|---|---|---|---|---|
| S01 | Lin | D01 | Computing | C01 | 92 |
| S01 | Lin | D01 | Computing | C02 | 88 |
| S02 | Chen | D01 | Computing | C01 | 85 |
| S03 | Zhou | D02 | Mathematics | C03 | 90 |
Assumptions: S uniquely identifies a student; each student belongs to one department; each student has one grade per course. Retakes across terms are outside this model.
A student’s department and a student’s enrollment are different facts. That is where decomposition begins.
Whenever the values on the left agree, the values on the right must agree too.
The arrow has a direction. X → Y does not imply Y → X.
Given business rules · 1NF assumed · Analysis through BCNF
S+=Start with the selected attributes.
No nonprime attribute may depend on a proper subset of any candidate key. Check all candidate keys, not just the chosen primary key.
S→NDMA proper subset of candidate key SC
S⁺=SNDMSCSCNontrivial: at least one right-hand attribute is absent from the left.
All facts in one relation
Original enrollment
1NF| SStudent ID | NName | DDept. ID | MDept. name | CCourse ID | GGrade |
|---|---|---|---|---|---|
| S01 | Lin | D01 | Computing | C01 | 92 |
| S01 | Lin | D01 | Computing | C02 | 88 |
| S02 | Chen | D01 | Computing | C01 | 85 |
| S03 | Zhou | D02 | Mathematics | C03 | 90 |
SC is the candidate key. S alone determines student and department details: nonprime attributes depend on part of the key.
Lossless join
YesR₁ ∩ R₂ = S→SNDMThe shared attributes determine at least one complete relation.
Dependency preservation
YesThe union of projected dependencies implies every original dependency.
Inspect projected dependencies
Projection includes implied dependencies, not just the arrows originally entered.
S→NDMD→MSN→DMSD→NMND→MSM→NDSND→MSNM→DSDM→NSC→GThis checks two relations under the given functional dependencies. The two properties are computed independently.
R(A, B, C) · A → B, B → C