Quick Start Guide (Web Database Applications with PHP & MySQL)

3.2.1. Loading the Winestore Database

A local copy of the winestore database is required to test the examples in this and later chapters. MySQL must be installed and configured before the winestore database can be loaded. MySQL installation instructions can be found in Appendix A.

The steps to load the winestore database are as follows:

Download a copy of the winestore database from this book's web site; look for winestore.database.tar.gz.
Uncompress the winestore database package in any directory by running:
```
gzip -d winestore.database.tar.gz
```
Untar the tape archive file by running:
```
tar xvf winestore.database.tar
```
A list of files extracted is output.
Check that MySQL is running using:
```
/usr/local/bin/mysqladmin -uroot -ppassword version
```
where password is the root user password. If MySQL isn't running, log in as the Linux root user, and start the MySQL server using:
```
/usr/local/bin/safe_mysqld --user=mysql &
```
Run the MySQL command-line interpreter using the username and password created when MySQL was installed, and load the winestore data. The login name is username, and the password is password:
```
/usr/local/bin/mysql -uusername -ppassword < winestore.database
```

After the loading is complete—it may take a few seconds—the database can be tested by running a query. Type the following command on one line:

/usr/local/bin/mysql -uusername -ppassword -e 'USE winestore; SELECT * FROM region;'

This should produce the list of wine regions as output:

+-----------+---------------------+-------------+------+
| region_id | region_name         | description | map  |
+-----------+---------------------+-------------+------+
|         1 | Goulburn Valley     | NULL        | NULL |
|         2 | Rutherglen          | NULL        | NULL |
|         3 | Coonawarra          | NULL        | NULL |
|         4 | Upper Hunter Valley | NULL        | NULL |
|         5 | Lower Hunter Valley | NULL        | NULL |
|         6 | Barossa Valley      | NULL        | NULL |
|         7 | Riverland           | NULL        | NULL |
|         8 | Margaret River      | NULL        | NULL |
|         9 | Swan Valley         | NULL        | NULL |
+-----------+---------------------+-------------+------+

The winestore database has now been loaded and tested.

3.2.2. 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.

3.2.2.1. 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

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.

3.2.2.2. Creating the winestore with SQL

The CREATE TABLE SQL statements that build the winestore database are shown for reference in Example 3-1.

The statements in Example 3-1 are derived from the entity-relationship model in Figure 3-1, and the process of converting this model to CREATE TABLE statements is described in Appendix C. An electronic copy of the statements can be found in the winestore.database file used to create the winestore database earlier in this section.

Example 3-1. The complete winestore DDL statements

CREATE TABLE wine (
  wine_id int(5) DEFAULT '0' NOT NULL auto_increment,   
  wine_name varchar(50) DEFAULT '' NOT NULL,
  winery_id int(4),
  type varchar(10) DEFAULT '' NOT NULL,
  year int(4) DEFAULT '0' NOT NULL,
  description blob,
  PRIMARY KEY (wine_id),
  KEY name (wine_name)
  KEY winery (winery_id)
);

CREATE TABLE winery (
  winery_id int(4) DEFAULT '0' NOT NULL auto_increment,
  winery_name varchar(100) DEFAULT '' NOT NULL,
  region_id int(4),
  description blob,
  phone varchar(15),
  fax varchar(15),
  PRIMARY KEY (winery_id),
  KEY name (winery_name)
  KEY region (region_id)
);

CREATE TABLE region (
  region_id int(4) DEFAULT '0' NOT NULL auto_increment,
  region_name varchar(100) DEFAULT '' NOT NULL,
  description blob,  
  map mediumblob,
  PRIMARY KEY (region_id),
  KEY region (region_name)
);

CREATE TABLE customer (
  cust_id int(5) NOT NULL auto_increment,
  surname varchar(50) NOT NULL,
  firstname varchar(50) NOT NULL,
  initial char(1),
  title varchar(10),
  addressline1 varchar(50) NOT NULL,
  addressline2 varchar(50),
  addressline3 varchar(50),
  city varchar(20) NOT NULL,
  state varchar(20),
  zipcode varchar(5),
  country varchar(20),
  phone varchar(15),
  fax varchar(15),
  email varchar(30) NOT NULL,
  birth_date date( ),
  salary int(7),
  PRIMARY KEY (cust_id),
  KEY names (surname,firstname)
);

CREATE TABLE users (
  cust_id int(4) DEFAULT '0' NOT NULL,
  user_name varchar(50) DEFAULT '' NOT NULL,
  password varchar(15) DEFAULT '' NOT NULL,
  PRIMARY KEY (user_name),
  KEY password (password)
);

CREATE TABLE grape_variety (
  variety_id int(3),
  variety_name varchar(20),
  PRIMARY KEY (variety_id), 
  KEY var (variety)
);

CREATE TABLE inventory (
  wine_id int(5) DEFAULT '0' NOT NULL,
  inventory_id int(3) NOT NULL,
  on_hand int(5) NOT NULL,
  cost float(5,2) NOT NULL,
  case_cost float(5,2) NOT NULL,
  dateadded timestamp(12) DEFAULT NULL,
  PRIMARY KEY (wine_id,inventory_id)
);

CREATE TABLE orders (
  cust_id int(5) DEFAULT '0' NOT NULL,
  order_id int(5) DEFAULT '0' NOT NULL,
  date timestamp(12),
  discount float(3,1) DEFAULT '0.0',
  delivery float(4,2) DEFAULT '0.00',
  note varchar(120),
  PRIMARY KEY (cust_id,order_no)
);

CREATE TABLE items (
  cust_id int(5) DEFAULT '0' NOT NULL,
  order_id int(5) DEFAULT '0' NOT NULL,
  item_id int(3) DEFAULT '1' NOT NULL,
  wine_id int(4) DEFAULT '0' NOT NULL
  qty int(3),
  price float(5,2),
  date timestamp(12),
  PRIMARY KEY (cust_id,order_no,item_id)
);

CREATE TABLE wine_variety (
  wine_id int(5) DEFAULT '0' NOT NULL,
  variety_id int(3) DEFAULT '0' NOT NULL,
  id int(1) DEFAULT '0' NOT NULL,

  PRIMARY KEY (wine_id, variety_id)
);