PHP —

The Winestore Database

To complete the introduction to the winestore database, we include in this section a summary of the entity-relationship model of the winestore and the SQL statements that create the winestore using the MySQL DBMS. This section is included for easy reference.

The winestore entity-relationship model

Figure 3-3 shows the completed entity-relationship model for the online winestore derived from the system requirements listed in Chapter 1. Appendix C includes a description of the meaning of each shape and line type used in the figure.

Figure 3-3. The winestore ER model
figs/wda_0303.gif

The winestore model can be summarized as follows:

  • A customer at the online winestore purchases wines by placing one or more orders.

  • Each customer has exactly one set of user details.

  • Each order contains one or more items.

  • Each item is for a specific quantity of wine at a specific price.

  • A wine is of a type such as "Red," "White," or "Sparkling."

  • A wine has a vintage year; if the same wine has two or more vintages from different years, these are treated as two or more separate, distinct wines.

  • Each wine is made by one winery.

  • Each winery is located in one region.

  • Each wine has one or more grape_variety entries. For example, a wine of wine_name "Archibald" might be made of the grape_variety entries "Sauvignon" and "Cabernet." The order of the entries is important. For example, a "Cabernet Sauvignon" is different from a "Sauvignon Cabernet."

  • Each wine may have one or more inventories.

  • Each inventory for a wine represents the on-hand stock of a wine that is available at a particular cost or case_cost (a case is 12 bottles of wine). If a wine is available at two prices, there are two inventories.