Multivalued Dependencies
- A multivalued dependency occurs when a teterminant is matched to a set of values.
- A salesperson has two SKU's
- A preson has multiple degrees, siblings, phone numbers ...
- EMPLOYEE_DEGREE
- EMPLOYEE_DEGREE(EmployeeName, EmployeeDegree)
-
| EmployeeName | EmployeeDegree |
|---|
| Chau | BS |
| Green | BS |
| Green | MS |
| Green | PhD |
| Jones | AA |
| Jones | BA |
- The author claims that this table is not subject to modification anomalies.
- But I think I would split it up anyway.
- EMPLOYEE_DEGREE_SIBLING
- EMPLOYEE_DEGREE_SIBLING(EmployeeName, EmployeeDegree,EmployeeSibling)
-
| EmployeeName | EmployeeDegree | EmployeeSibling |
|---|
| Chau | BS | Eileen |
| Chau | BS | Jonathan |
| Green | BS | Nikki |
| Green | MS | Nikki |
| Green | PhD | Nikki |
| Jones | AA | Frank |
| Jones | AA | Fred |
| Jones | AA | Sally |
| Jones | BA | Frank |
| Jones | BA | Fred |
| Jones | BA | Sally |
- Do we have problems with this table?
- Chau earns another degree
- Jones has another sibling
- EmployeeName → → EmployeeDegree
- EmployeeName → → EmployeeSibling
- This is even a problem with other fields
- PARTKIT_PART_PRICE
- PARTKIT_PART_PRICE(PartKitName, Part, PartKitPrice)
- PartKitName → → Part
- PartKitName → PartKitPrice
-
| PartKitName | Part | PartKitPrice |
|---|
| Bike Repair Kit | Tub Fix | 14.95 |
| Bike Repair Kit | Wrench | 14.95 |
| Bike Repair Kit | Screwdriver | 14.95 |
| First Aid | Asprin | 24.95 |
| First Aid | Bandaids | 24.95 |
| First Aid | Elastic Band | 24.95 |
| First Aid | Ibuprofin | 24.95 |
| Toolbox | Drill | 74.95 |
| Toolbox | Drill Bits | 74.95 |
| Toolbox | Hammer | 74.95 |
| Toolbox | Saw | 74.95 |
| Toolbox | Screwdriver | 74.95 |
- Notice that to add a part to a kit, the price needs to be repeated many times.
- The same technique as before applies, split into two tables.
- PARTKIT_PRICE(PartKitName,Price)
- PARTKIT_ITEMS(PartkitName,Part)
- A table is in fourth normal form (4NF) whe all multivalued dependencies have been isolated in their own tables.
- Unless you have a good reason, your tables should be both in BCNF and 4NF.
- Example: BUYER_DEGREE_SKU
- BUYER_DEGREE_SKU(BuyerName, Major, SKU)
- BuyerName → → SKU
- BuyerName → → Major
-
| BuyerName | SKU | Major |
|---|
| Cindy Lo | 201000 | History |
| Cindy Lo | 202000 | History |
| Jenny Martin | 301000 | Business Administration |
| Jenny Martin | 301000 | English Literature |
| Jenny Martin | 302000 | Business Administration |
| Jenny Martin | 302000 | English Literature |
| Nancy Meyers | 101100 | Art |
| Nancy Meyers | 101100 | Info Systems |
| Nancy Meyers | 101200 | Art |
| Nancy Meyers | 101200 | Info Systems |
| Pete Hansen | 100100 | Business Administration |
| Pete Hansen | 100200 | Business Administration |