To save on screen space I'll rename attributes:
- Brand:
BRD
- Flavor:
FLA
- Animal:
ANM
- Breed size:
SIZ
- Age group:
AGP
- Ingredient:
ING
- Price:
PRI
Method 1
Normalization, as in textbooks.
Step 1.1
Due to repeating groups in ingredients, the value in the table is not a relation, hence the table does not represent a relational variable, hence it is not in 1NF. The solution is to make sure that the ingredient column (ING) has exactly one ingredient per row-- as you have done. However, no new attributes are added (no new ID). Now we have (in 1NF):
R {BRD, FLA, ANM, SIZ, AGP, ING, PRI}
The whole heading is the key.
Step 1.2
From FDs:
{BRD, FLA} -> {ANM}{BRD, FLA} -> {SIZ}{BRD, FLA} -> {AGP}{BRD, FLA} -> {PRI}
by applying union rule for FDs and Heath's theorem:
R1 {BRD, FLA, ANM, SIZ, AGP, PRI}
KEY {BRD, FLA}
R2 {BRD, FLA, ING}
KEY {BRD, FLA, ING}
Done. That's it, if I got the FDs correctly.
Both are in BCNF, with confidence I would say that R1 is in 5NF, R2 in 6NF.
Method 2
Database design is predicate design.
Not a formal normalization method as found in most textbooks, but a design method which results in tables being in high NF (5NF, 6NF).
In the first step, the problem (business domain) is verbalized using simple predicates and associated constraints. The problem can be then reasoned about in natural language using logic.
A simple predicate can not be decomposed without losing information, its matching relvar is in 6NF.
In the second step, these simple predicates (and matching relvars) can be combined, making sure not to introduce redundancy and possibility of logical errors, namely contradiction. Once done, relvars (tables) are expected to be in 5NF.
Step 2.1
Describing the problem using simple predicates and matching 6NF relvars. Not verbalizing constraints (it gets long-winded), just stating them.
-- Brand BRD exists.
--
brand {BRD}
PK {BRD}
-- Flavor FLA exists.
--
flavor {FLA}
PK {FLA}
-- Animal type ANM exists.
--
animal {ANM}
PK {ANM}
-- Breed size SIZ exists.
--
bsize {SIZ}
PK {SIZ}
-- Age group AGP exists.
--
age {AGP}
PK {AGP}
-- Ingredient ING exists.
--
ingredient {ING}
PK {ING}
-- Pet food with flavor FLA made by brand BRD
-- is for animal type ANM.
--
food {BRD, FLA, ANM}
PK {BRD, FLA}
FK1 {BRD} REFERENCES brand {BRD}
FK2 {FLA} REFERENCES flavor {FLA}
FK3 {ANM} REFERENCES animal {ANM}
-- Pet food with flavor FLA made by brand BRD
-- is recommended for breed size SIZ.
--
food_bsize {BRD, FLA, SIZ}
PK {BRD, FLA}
FK1 {BRD, FLA} REFERENCES
food {BRD, FLA}
FK2 {SIZ} REFERENCES bsize {SIZ}
-- Pet food with flavor FLA made by brand BRD
-- is recommended for breed age group AGP.
--
food_age {BRD, FLA, AGP}
PK {BRD, FLA}
FK1 {BRD, FLA} REFERENCES
food {BRD, FLA}
FK2 {AGP} REFERENCES age {AGP}
-- Pet food with flavor FLA made by brand BRD
-- is priced at PRI Euros per unit.
--
price {BRD, FLA, PRI}
PK {BRD, FLA}
FK {BRD, FLA} REFERENCES
food {BRD, FLA}
-- Pet food with flavor FLA made by brand BRD
-- contains ingredient ING.
--
recipe {BRD, FLA, ING}
PK {BRD, FLA, ING}
FK1 {BRD, FLA} REFERENCES
food {BRD, FLA}
FK2 {ING} REFERENCES ingredient {ING}
Step 2.2
Just by looking at the keys, we can see that food, food_bsize, food_age, and price tables can be combined.
-- Pet food with flavor FLA made by brand BRD
-- is for animal type ANM, recommended for
-- breed size SIZ, breed age group AGP; priced
-- at PRI Euros per unit.
--
food_ {BRD, FLA, ANM, SIZ, AGP, PRI}
PK {BRD, FLA}
FK1 {BRD} REFERENCES brand {BRD}
FK2 {FLA} REFERENCES flavor {FLA}
FK3 {ANM} REFERENCES animal {ANM}
FK4 {SIZ} REFERENCES bsize {SIZ}
FK5 {AGP} REFERENCES age {AGP}
If we decide not to keep the first six tables defining domains, then the final result is as in the first method:
food_ {BRD, FLA, ANM, SIZ, AGP, PRI}
PK {BRD, FLA}
recipe {BRD, FLA, ING}
PK {BRD, FLA, ING}
FK {BRD, FLA} REFERENCES food_
{BRD, FLA}
However, in a real-world project, you will likely need brand, flavor, animal, bsize, and age tables to constrain domains. There is no rule in normalization stating that you should have them.
Also, not all attributes may be known at the sime time, so it is not likely that you'll combine all of food, food_bsize, food_age, and price tables into food_. This will depend on the business process and optionality of attributes.
Adding IDs
Adding surrogate keys (IDs) has nothing to do with normalization. You may need to add them for other reasons, take a look at this example.
Note:
All attributes (columns) NOT NULL
KEY = PK or AK
PK = Primary Key
AK = Alternate Key (Unique)
SK = Proper Superkey (Unique)
FK = Foreign Key
I always use the second method for design. Have never seen the first one used outside of a formal DB course of some kind; and people asking for help on SO.
For some reason, beyond my understanding, method 1 is taught in DB courses under topic "DB design" before method 2. Most schools do not teach method 2 at all. Go figure.
