The Wedding Gift Registry Database
Example 11-1
is a file that contains the SQL
statements (and the MySQL use command) to create
and populate the wedding database. The database contains only two
tables: presents, which stores data about gifts,
including a unique identifier, description, desired quantity, color,
place of purchase, price, and the user who reserved the gift, and
people, which stores a unique username and
password for each guest.
A
one-to-many relationship is
maintained between the two tables; each guest stored in the
people table can reserve zero or more gifts in the
presents table. When the gifts are initially
inserted in the wedding database using the
statements in Example 11-1, the
people_id in the presents table
is set to NULL so that all gifts are unreserved.
If a guest reserves a gift, the NULL value is
replaced with the guest’s
people_id. For example, if the guest
hugh reserves the gift with a
present_id of 2 (such as the
Richmond
Tigers autographed
print
(unframed)), the
people_id of that gift is set to
hugh.
create database wedding;
use wedding;
CREATE TABLE people (
people_id varchar(30) DEFAULT '' NOT NULL,
passwd varchar(30),
PRIMARY KEY (people_id)
);
INSERT INTO people VALUES ('hugh','huw8o3cEvVS8o'),
CREATE TABLE presents (
present_id int(11) DEFAULT '0' NOT NULL auto_increment,
present varchar(255),
shop varchar(100),
quantity varchar(30),
colour varchar(30),
price varchar(30),
people_id varchar(30),
PRIMARY KEY (present_id)
);
INSERT INTO presents VALUES (1,'Mikasa Studio Nova Tivoli White 20 Piece
Dinnerset','Myer','1','White','102.10',NULL);
INSERT INTO presents VALUES (2,'Richmond Tigers autographed print (unframed)',
'www.greatmoments.com.au','1','NA','375.00',NULL);
INSERT INTO presents VALUES (3,'Breville Rice Cooker','Myer','1',
'Silver','95.00','NULL'),
INSERT INTO presents VALUES (4,'Krups - Nespresso 986 coffee machine','Myer','1',
'Black','608.00',NULL);
INSERT INTO presents VALUES (5,'Click Clack Airtight Cannisters - Small Coffee Jar
0.6 Ltr','Myer','3','Clear with White Lid','4.67ea (14.01 total)',NULL);
INSERT INTO presents VALUES (6,'Avanti Twin Wall Mixing Bowls 2.8 Ltr','Myer','2',
'Silver','41.65ea (83.30 total)',NULL);
INSERT INTO presents VALUES (7,'Lithograph - David Boyd 'Sorting the Score',
approx 1" sq.','Port Jackson Press, 397 Brunswick St, Fitzroy','1',
'Blue on white','594.00',NULL);
INSERT INTO presents VALUES (8,'Le Creuset Wok','Myer','1','Blue','258.00',NULL);
INSERT INTO presents VALUES (9,'Willow 12 Tin Muffin Tray','Myer','1',
'Silver','9.07',NULL);
INSERT INTO presents VALUES (10,'Baileys Comet 6 Ladder','Bunnings','1',
'Silver','97.50',NULL);
INSERT INTO presents VALUES (11,'Makita Drill HP1500k','Bunnings','1',
'Black/Green','128.00',NULL);
INSERT INTO presents VALUES (12,'Makita B04553 Palm Sander','Bunnings','1',
'Black/Green','121.99',NULL);
INSERT INTO presents VALUES (13,'Stanley Shifting Spanner 6""','Bunnings','2',
'Silver','10.40ea',NULL);The MySQL DBMS that maintains the gift registry has a user
fred who has a password shhh.
This user is set up using the following SQL GRANT
statement:
GRANT SELECT, INSERT, DELETE, UPDATE ON wedding.* TO fred@localhost IDENTIFIED by 'shhh';
In our environment, the web server and the MySQL DBMS are running on
the same machine, so the user fred needs access
only from the local host. Having the DBMS and web server on the same
machine is a good decision for small- to medium-size web database
applications because there is no network communications overhead
between the DBMS and the web server. For high-traffic or complex web
database applications, it may be desirable to have dedicated hardware
for each application.