To help you understand a bit of SQL, you must create a very simple database, composed of two tables:

• Instructor(id, name, email, birthdate)
• Lecture(id, description, type, price, id_instructor)

id is a numeric identifier, auto-incremented, and is the primary key of the tables.

name, email, description and type are strings (varchar).

price is a real number (float).

name will be indexed and is considered as unique.

We first create the database: create database test1;

Then we create the Instructor table:

CREATE TABLE instructor (

id SMALLINT not null AUTO_INCREMENT,

name VARCHAR (50) not null,

email VARCHAR (50) not null,

birthdate DATE not null,

PRIMARY KEY (id),

INDEX (name),

UNIQUE (name));

Then we create the Lecture table:

CREATE TABLE Lecture (id SMALLINT not null AUTO_INCREMENT,

description VARCHAR (50) not null,

type CHAR DEFAULT 'R' not null,

price FLOAT DEFAULT '0' not null ,

id_instructor SMALLINT,

PRIMARY KEY (id));

Then we record an instructor:

INSERT INTO instructor (id, name, email, birthdate) VALUES ('', 'David Billard', 'David.Billard@adm.unige.ch', NOW(''));

Then we record a lecture:

INSERT INTO Lecture (id, description, type, price, id_instructor) VALUES ('', 'Electronic Commerce', '', '10.5', '1');

Then we print at the screen the content of the tables (you already know how to do it).

Then we want to know the name and email of the instructor of the lecture on Electronic Commerce:

select * from Lecture, instructor where Lecture.id_instructor=instructor.id;

Oops, just the name and email please.

As you can see, it is very tiring to do a single request by hand.

Create a new database called shop.

Create the following set of tables and populate them at your whish:

• products(id, description, price)
• clients(id, name, email)
• orders(id, id_client, id_product, quantity, timestamp)

Create a new directory under /server/httpd/htdocs, let say ceenet2001.

Create the file lpro.php that lists the products of the database.

Create the file lcli.php that lists the clients, and the file lord.php that lists the orders.

In case of trouble creating the files, there is an example in the appendix.

Modify or create the necessary files so that the Internet user is able:

# phpMyAdmin MySQL-Dump
# http://phpwizard.net/phpMyAdmin/
#
# Host: linux-billard Database : shop

# --------------------------------------------------------
#
# Table structure for table 'clients'
#

CREATE TABLE clients (
   id int(11) NOT NULL auto_increment,
   name varchar(50),
   email varchar(50) NOT NULL,
   PRIMARY KEY (id)
);

#
# Dumping data for table 'clients'
#

INSERT INTO clients VALUES( '1', 'David Billard', 'David.Billard@adm.unige.ch');
INSERT INTO clients VALUES( '2', 'Miroslav Milinovic', 'miroslav.milinovic@srce.hr');
INSERT INTO clients VALUES( '3', 'Sebastien Blondeel', 'blondeel@clipper.ens.fr');

# --------------------------------------------------------
#
# Table structure for table 'orders'
#

CREATE TABLE orders (
   id int(11) NOT NULL auto_increment,
   id_product int(11) NOT NULL,
   id_client int(11) NOT NULL,
   quantity int(11) NOT NULL,
   timestamp timestamp(14),
   PRIMARY KEY (id),
   KEY id (id, id_product, id_client)
);

#
# Dumping data for table 'orders'
#

INSERT INTO orders VALUES( '1', '1', '1', '10', '20010823133237');
INSERT INTO orders VALUES( '2', '1', '1', '10', '20010823133327');

# --------------------------------------------------------
#
# Table structure for table 'products'
#

CREATE TABLE products (
   id int(11) NOT NULL auto_increment,
   description varchar(50) NOT NULL,
   price float DEFAULT '0           ' NOT NULL,
   PRIMARY KEY (id)
);

#
# Dumping data for table 'products'
#

INSERT INTO products VALUES( '1', 'lecture DB in the Internet', '10          ');
INSERT INTO products VALUES( '2', 'Lecture Electronic Commerce', '50          ');
INSERT INTO products VALUES( '3', 'Exercises in DB', '5           ');
INSERT INTO products VALUES( '4', 'Exercises in EC', '8           ');