A2.3 MEALS Database Normalization

The Seasoned Steak 'n Side is a restaurant started by a chef sommelier. Her minimalist menu began with a small selection of specially designed dinners, each of which included a specially seasoned main course, a specially seasoned side dish and just the right wine. The menu is a BCNF relation, with a composite primary key consisting of all three columns together. Notice that even though the table is in BCNF, there are still redundancies.

Both the side dish and the wine were dependent on the main dish. Because of special seasonings, the salmon could not be served with the baked potato and the beef steak could not be served with white rice. The chief chef sommelier was a real stickler on this.

Also, fish (e.g., Salmon) must be served with a white wine (e.g., Pinot Grigio, Chardonnay) and beef with a red (Cabernet Sauvignon, Pinot Noir). No exceptions.

Notice the relation contains multi-valued dependencies (MVDs). The main dish (salmon or beef) determines the possible side dishes and the wines, hence they are dependent.

However, a main dish doesn't just determine a single side dish, but only that it is one of a multi-valued set. Salmon steak will only be served with mashed potatoes or white rice, not with a baked potato. Beef steak will only be served with either a baked potato or mashed potatoes, but not white rice.

(If these were regular single valued dependencies, Salmon steak could determine either the side dish as either mashed potatoes or white rice, but it couldn't be more than one.) The relation is in BCNF but not 4NF when we remember that:

• R is in BCNF if and only if every Functional Dependency (FD) that holds in R is implied by the keys of R.
• R is in 4NF if and only if every Multi-Valued Dependency (MVD) that holds in R is implied by the keys of R.

Assignment:

1. Create an InnoDB database on your server named MEALS exactly.
2. Import the above table into MEALS.
   • Name the table "ORIGINAL"
   • Notice the field names `MAIN DISH` and `SIDE DISH` have spaces in them. Set the field types to varchar(50).
   • Be sure to specify the composite primary key correctly
3. Reduce the table into two two-attribute tables in 4NF where the MVDs of the two relations are implied by the keys. Name your tables MAIN_SIDE and MAIN_WINE.
   • Make sure you specify the primary keys correctly.
   • Make sure populate the tables from the original table.
4. Create a VIEW named VIEW_ORIGINAL with a NATURAL JOIN statement to produce the original relation back from your two 4NF relations.

When you are through, confirm you created your database correctly using the autograder.