Step 1: Convert regular entities to tables
The first step is the simplest. Here's what you do:
For each non-weak entity in the ER model, write out a
TABLEstatement with the same name as the entity.
Include all attributes of the entity and assign appropriate types to the attributes.
KEYof the entity.
Add any modifiers to attributes and any additional keys as required.
To perform this step, you need to make decisions about attribute types in the SQL
TABLE statements. Attribute types are discussed in Chapter 3.
There are several non-weak entities in the model. Begin with the
region entity, which has the attributes
map. You might anticipate no more than 100 different regions, but being cautious is important if more than 1,000 regions need to be stored. Accordingly, a type of
int(4) allows up to 10,000 regions. Using a similar argument, define
region_name as a
varchar(100). Because descriptions may be long, let's define
description as a
map-which is an image-is defined as a
As decided earlier in the chapter, the unique key of the region table is an ID, which is now called
region_id. Accordingly, you define a
region_id. A requirement of all primary keys is that they are specified as
NULL, and this is added to the attribute. Now automate the creation of the values by adding the
auto_increment clause and a
'0'. (Recall from Chapter 3 that storing
0 in an
auto_increment attribute is a MySQL feature that automatically stores a unique ID larger than all other IDs for this table.)
The resulting definition for the
region table is then as follows:
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) );
Notice an additional
KEY on the
region. By adding this key, you anticipate that a common query is a search by
region_name. Also, a region must have a name, so a
NULL is added to the
TABLE statements for the other non-weak entities are listed in Example C-1. Remember, however, that this is only the first step: some of these
TABLE statements are altered by the processes in later steps.
Example C-1. CREATE TABLE commands for non-weak entities
CREATE TABLE wine ( wine_id int(5) DEFAULT '0' NOT NULL auto_increment, wine_name varchar(50) DEFAULT '' NOT NULL, type varchar(10) DEFAULT '' NOT NULL, year int(4) DEFAULT '0' NOT NULL, description blob, PRIMARY KEY (wine_id) ); CREATE TABLE winery ( winery_id int(4) DEFAULT '0' NOT NULL auto_increment, winery_name varchar(100) DEFAULT '' NOT NULL, description blob, phone varchar(15), fax varchar(15), PRIMARY KEY (winery_id) ); 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 grape_variety ( variety_id int(3), variety_name varchar(20) PRIMARY KEY (variety_id) );