Sample Database - Appendices - Expert MySQL: Second Edition (2012)

Expert MySQL: Second Edition (2012)

Part IV. Appendices

Sample Database

The following sample database is used in the later chapters of this text. The following listing shows the SQL dump of the database.

Listing A-1. Sample Database Create Statements

-- MySQL dump 10.10
--
-- Host: localhost Database: expert_mysql
-- ------------------------------------------------------
-- Server version 5.1.9-beta-debug-DBXP 1.0

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0
*/;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

CREATE DATABASE IF NOT EXISTS expert_mysql;

--
-- Table structure for table 'expert_mysql'.'building'
--

DROP TABLE IF EXISTS 'expert_mysql'.'building';
CREATE TABLE 'expert_mysql'.'building' (
'dir_code' char(4) NOT NULL,
'building' char(6) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

--
-- Dumping data for table 'expert_mysql'.'building'
--


/*!40000 ALTER TABLE 'expert_mysql'.'building' DISABLE KEYS */;
LOCK TABLES 'expert_mysql'.'building' WRITE;
INSERT INTO 'expert_mysql'.'building' VALUES
('N41','1300'),
('N01','1453'),
('M00','1000'),
('N41','1301'),
('N41','1305');
UNLOCK TABLES;
/*!40000 ALTER TABLE 'expert_mysql'.'building' ENABLE KEYS */;

--
-- Table structure for table 'expert_mysql'.'directorate'
--

DROP TABLE IF EXISTS 'expert_mysql'.'directorate';
CREATE TABLE 'expert_mysql'.'directorate' (
'dir_code' char(4) NOT NULL,
'dir_name' char(30) DEFAULT NULL,
'dir_head_id' char(9) DEFAULT NULL,
PRIMARY KEY ('dir_code')
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

--
-- Dumping data for table 'expert_mysql'.'directorate'
--


/*!40000 ALTER TABLE 'expert_mysql'.'directorate' DISABLE KEYS */;
LOCK TABLES 'expert_mysql'.'directorate' WRITE;
INSERT INTO 'expert_mysql'.'directorate' VALUES
('N41','Development','333445555'),
('N01','Human Resources','123654321'),
('M00','Management','333444444');
UNLOCK TABLES;
/*!40000 ALTER TABLE 'directorate' ENABLE KEYS */;

--
-- Table structure for table 'expert_mysql'.'staff'
--

DROP TABLE IF EXISTS 'expert_mysql'.'staff';
CREATE TABLE 'expert_mysql'.'staff' (
'id' char(9) NOT NULL,
'first_name' char(20) DEFAULT NULL,
'mid_name' char(20) DEFAULT NULL,
'last_name' char(30) DEFAULT NULL,
'sex' char(1) DEFAULT NULL,
'salary' int(11) DEFAULT NULL,
'mgr_id' char(9) DEFAULT NULL,
PRIMARY KEY ('id')
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

--
-- Dumping data for table 'expert_mysql'.'staff'
--


/*!40000 ALTER TABLE 'expert_mysql'.'staff' DISABLE KEYS */;
LOCK TABLES 'expert_mysql'.'staff' WRITE;
INSERT INTO 'expert_mysql'.'staff' VALUES
('333445555','John','Q','Smith','M',30000,'333444444'),
('123763153','William','E','Walters','M',25000,'123654321'),
('333444444','Alicia','F','St.Cruz','F',25000,NULL),
('921312388','Goy','X','Hong','F',40000,'123654321'),
('800122337','Rajesh','G','Kardakarna','M',38000,'333445555'),
('820123637','Monty','C','Smythe','M',38000,'333445555'),
('830132335','Richard','E','Jones','M',38000,'333445555'),
('333445665','Edward','E','Engles','M',25000,'333445555'),
('123654321','Beware','D','Borg','F',55000,'333444444'),
('123456789','Wilma','N','Maxima','F',43000,'333445555');
UNLOCK TABLES;
/*!40000 ALTER TABLE 'expert_mysql'.'staff' ENABLE KEYS */;

--
-- Table structure for table 'tasking'
--

DROP TABLE IF EXISTS 'expert_mysql'.'tasking';
CREATE TABLE 'expert_mysql'.'tasking' (
'id' char(9) NOT NULL,
'project_number' char(9) NOT NULL,
'hours_worked' double DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

--
-- Dumping data for table 'tasking'
--


/*!40000 ALTER TABLE 'tasking' DISABLE KEYS */;
LOCK TABLES 'expert_mysql'.'tasking' WRITE;
INSERT INTO 'expert_mysql'.'tasking' VALUES
('333445555','405',23),
('123763153','405',33.5),
('921312388','601',44),
('800122337','300',13),
('820123637','300',9.5),
('830132335','401',8.5),
('333445555','300',11),
('921312388','500',13),
('800122337','300',44),
('820123637','401',500.5),
('830132335','400',12),
('333445665','600',300.25),
('123654321','607',444.75),
('123456789','300',1000);
UNLOCK TABLES;
/*!40000 ALTER TABLE 'expert_mysql'.'tasking' ENABLE KEYS */;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

# Source on localhost: ... connected.

# Exporting metadata from bvm

DROP DATABASE IF EXISTS bvm;

CREATE DATABASE bvm;

USE bvm;

# TABLE: bvm.books

CREATE TABLE 'books' (

'ISBN' varchar(15) DEFAULT NULL,

'Title' varchar(125) DEFAULT NULL,

'Authors' varchar(100) DEFAULT NULL,

'Quantity' int(11) DEFAULT NULL,

'Slot' int(11) DEFAULT NULL,

'Thumbnail' varchar(100) DEFAULT NULL,

'Description' text,

'Pages' int(11) DEFAULT NULL,

'Price' double DEFAULT NULL,

'PubDate' date DEFAULT NULL

) ENGINE=MyISAM DEFAULT CHARSET=latin1;

# TABLE: bvm.settings

CREATE TABLE 'settings' (

'FieldName' char(30) DEFAULT NULL,

'Value' char(250) DEFAULT NULL

) ENGINE=MyISAM DEFAULT CHARSET=latin1;

#...done.

USE bvm;

# Exporting data from bvm

# Data for table bvm.books:

INSERT INTO bvm.books VALUES (978–1590595053, 'Pro MySQL', 'Michael Kruckenberg, Jay Pipes and Brian
Aker', 5, 1, 'bcs01.gif', NULL, 798, 49.99, '2005-07-15');

INSERT INTO bvm.books VALUES (978–1590593325, 'Beginning MySQL Database Design and Optimization',
'Chad Russell and Jon Stephens', 6, 2, 'bcs02.gif', NULL, 520, 44.99, '2004-10-28');

INSERT INTO bvm.books VALUES (978–1893115514, 'PHP and MySQL 5', 'W. Jason Gilmore', 4, 3, 'bcs03.gif',
NULL, 800, 39.99, '2004-06-21');

INSERT INTO bvm.books VALUES (978–1590593929, 'Beginning PHP 5 and MySQL E-Commerce', 'Cristian
Darie and Mihai Bucica', 5, 4, 'bcs04.gif', NULL, 707, 46.99, '2008-02-21');

INSERT INTO bvm.books VALUES (978–1590595091, 'PHP 5 Recipes', 'Frank M. Kromann, Jon Stephens,
Nathan A. Good and Lee Babin', 8, 5, 'bcs05.gif', NULL, 672, 44.99, '2005-10-04');

INSERT INTO bvm.books VALUES (978–1430227939, 'Beginning Perl', 'James Lee', 3, 6, 'bcs06.gif',
NULL, 464, 39.99, '2010-04-14');

INSERT INTO bvm.books VALUES (978–1590595350, 'The Definitive Guide to MySQL 5', 'Michael Kofler',
2, 7, 'bcs07.gif', NULL, 784, 49.99, '2005-10-04');

INSERT INTO bvm.books VALUES (978–1590595626, 'Building Online Communities with Drupal, phpBB, and
WordPress', 'Robert T. Douglass, Mike Little and Jared W. Smith', 1, 8, 'bcs08.gif', NULL, 560, 49.99, '2005-12-16');

INSERT INTO bvm.books VALUES (978–1590595084, 'Pro PHP Security', 'Chris Snyder and Michael
Southwell', 7, 9, 'bcs09.gif', NULL, 528, 44.99, '2005-09-08');

INSERT INTO bvm.books VALUES (978–1590595312, 'Beginning Perl Web Development', 'Steve Suehring',
8, 10, 'bcs10.gif', NULL, 376, 39.99, '2005-11-07');

# Blob data for table books:

UPDATE bvm.books SET 'Description' = "Pro MySQL is the first book that exclusively covers
intermediate and advanced features of MySQL, the world's most popular open source database server.
Whether you are a seasoned MySQL user looking to take your skills to the next level, or youre a
database expert searching for a fast-paced introduction to MySQL's advanced features, this book is
for you." WHERE 'ISBN' = 978–1590595053;

UPDATE bvm.books SET 'Description' = "Beginning MySQL Database Design and Optimization shows you
how to identify, overcome, and avoid gross inefficiencies. It demonstrates how to maximize the many
data manipulation features that MySQL includes. This book explains how to include tests and branches
in your queries, how to normalize your database, and how to issue concurrent queries to boost
performance, among many other design and optimization topics. You'll also learn about some features
new to MySQL 4.1 and 5.0 like subqueries, stored procedures, and views, all of which will help you
build even more efficient applications." WHERE 'ISBN' = 978–1590593325;

UPDATE bvm.books SET 'Description' = "Beginning PHP 5 and MySQL: From Novice to Professional offers
a comprehensive introduction to two of the most popular open-source technologies on the planet: the
PHP scripting language and the MySQL database server. You are not only exposed to the core features
of both technologies, but will also gain valuable insight into how they are used in unison to create
dynamic data-driven web applications, not to mention learn about many of the undocumented features
of the most recent versions." WHERE 'ISBN' = 978–1893115514;

UPDATE bvm.books SET 'Description' = "Beginning PHP 5 E-Commerce: From Novice to Professional is
an ideal reference for intermediate PHP 5 and MySQL developers, and programmers familiar with web
development technologies. This book covers every step of the design and build process, and provides
rich examples that will enable you to build high-quality, extendable e-commerce websites." WHERE
'ISBN' = 978–1590593929;

UPDATE bvm.books SET 'Description' = "We are confident PHP 5 Recipes will be a useful and welcome
companion throughout your PHP journey, keeping you on the cutting edge of PHP development, ahead
of the competition, and giving you all the answers you need, when you need them." WHERE 'ISBN' =
978–1590595091;

UPDATE bvm.books SET 'Description' = "This is a book for those of us who believed that we didn't
need to learn Perl, and now we know it is more ubiquitous than ever. Perl is extremely flexible and
powerful, and it isn't afraid of Web 2.0 or the cloud. Originally touted as the duct tape of the
Internet, Perl has since evolved into a multipurpose, multiplatform language present absolutely
everywhere: heavy-duty web applications, the cloud, systems administration, natural language
processing, and financial engineering. Beginning Perl, Third Edition provides valuable insight into
Perl's role regarding all of these tasks and more." WHERE 'ISBN' = 978–1430227939;

UPDATE bvm.books SET 'Description' = "This is the first book to offer in-depth instruction about the
new features of the world's most popular open source database server. Updated to reflect changes
in MySQL version 5, this book will expose you to MySQL's impressive array of new features: views,
stored procedures, triggers, and spatial data types." WHERE 'ISBN' = 978–1590595350;

UPDATE bvm.books SET 'Description' = "Building Online Communities with Drupal, phpBB, and Wordpress
is authored by a team of experts. Robert T. Douglass created the Drupal-powered blog site NowPublic.com.
Mike Little is a founder and contributing developer of the WordPress project. And Jared W. Smith has
been a longtime support team member of phpBBHacks.com and has been building sites with phpBB since
the first beta releases." WHERE 'ISBN' = 978–1590595626;

UPDATE bvm.books SET 'Description' = "Pro PHP Security is one of the first books devoted solely
to PHP security. It will serve as your complete guide for taking defensive and proactive security
measures within your PHP applications. The methods discussed are compatible with PHP versions 3, 4,
and 5." WHERE 'ISBN' = 978–1590595084;

UPDATE bvm.books SET 'Description' = "Beginning Perl Web Development: From Novice to Professional
introduces you to the world of Perl Internet application development. This book tackles all areas
crucial to developing your first web applications and includes a powerful combination of real-world
examples coupled with advice. Topics range from serving and consuming RSS feeds, to monitoring
Internet servers, to interfacing with e-mail. You'll learn how to use Perl with ancillary packages
like Mason and Nagios." WHERE 'ISBN' = 978–1590595312;

# Data for table bvm.settings:

INSERT INTO bvm.settings VALUES ('ImagePath', 'c://mysql_embedded//images//');

#...done.