PHP —

Converting an Entity-Relationship Model to SQL

There are five steps to convert an ER model to a set of SQL CREATE TABLE statements.

Step 1: Convert regular entities to tables

The first step is the simplest. Here's what you do:

  1. For each non-weak entity in the ER model, write out a CREATE TABLE statement with the same name as the entity.

  2. Include all attributes of the entity and assign appropriate types to the attributes.

  3. Include the PRIMARY KEY of the entity.

  4. 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 CREATE 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 region_id, region_name, description, and 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 blob. A map-which is an image-is defined as a mediumblob.

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 PRIMARY KEY of region_id. A requirement of all primary keys is that they are specified as NOT NULL, and this is added to the attribute. Now automate the creation of the values by adding the auto_increment clause and a DEFAULT '0'. (Recall from Chapter 3 that storing NULL or 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_name named 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 NOT NULL is added to the region_name attribute.

The CREATE 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 CREATE 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)
);