Implementing the Database

As we mentioned earlier, we have made some minor modifications to the Book-O-Rama database presented in Part II.

The SQL to create the book_sc database is shown in Listing 25.1.

Listing 25.1 book_sc.sql—SQL to Create the book_sc Database

create database book_sc;

use book_sc;

create table customers
(
  customerid int unsigned not null auto_increment primary key,
  name char(40) not null,
  address char(40) not null,
  city char(20) not null,
  state char(20),
  zip char(10),
  country char(20) not null
);

create table orders
(
  orderid int unsigned not null auto_increment primary key,
  customerid int unsigned not null,
  amount float(6,2),
  date date not null,
  order_status char(10),
  ship_name char(40) not null,
  ship_address char(40) not null,
  ship_city char(20) not null,
  ship_state char(20),
  ship_zip char(10),
  ship_country char(20) not null
);

create table books
(
   isbn char(13) not null primary key,
   author char(30),
   title char(60),
   catid int unsigned,
   price float(4,2) not null,
   description varchar(255)
);

create table categories
(
  catid int unsigned not null auto_increment primary key,
  catname char(40) not null
);

create table order_items
(
  orderid int unsigned not null,
  isbn char(13) not null,
  item_price float(4,2) not null,
  quantity tinyint unsigned not null,
  primary key (orderid, isbn)
);

create table admin
(
  username char(16) not null primary key,
  password char(16) not null
);

grant select, insert, update, delete
on book_sc.*
to book_sc@localhost identified by 'password';

Although nothing was wrong with the original Book-O-Rama interface, we have a few other requirements now that we are going to make it available online.

The changes we have made to the original database are as follows:

• The addition of more address fields for customers—this is more important now that we are building a more realistic application.

• The addition of a shipping address to an order. A customer's contact address might not be the same as the shipping address, particularly if she is using the site to buy a gift.

• The addition of a categories table and a catid to books table. Sorting books into categories will make the site easier to browse.

• The addition of item_price to the order_items table to recognize the fact that an item's price might change. We want to know how much it cost when the customer ordered it.

• The addition of an admin table to store administrator login and password details.

• The removal of the reviews table—you could add reviews as an extension to this project. Instead, each book has a description field which will contain a brief blurb about the book.

To set this database up on your system, run the book_sc.sql script through MySQL as the root user, as follows:

					
mysql -u root -p < book_sc.sql

				

(You will need to supply your root password.)

Beforehand, you should change the password for the book_sc user to something better than 'password'.

We have also included a file of sample data. This is called populate.sql. You can put the sample data into the database by running it through MySQL in this same way.