File: createExportDatabase.sql

-- --------------------------------------------------------
CREATE DATABASE IF NOT EXISTS export_spreadsheets DEFAULT CHARACTER SET = utf8 DEFAULT COLLATE = utf8_bin;

USE export_spreadsheets;

GRANT USAGE ON * . * TO 'exportUser' IDENTIFIED BY 'exportPassword';

GRANT ALL PRIVILEGES ON `export_spreadsheets`. * TO 'exportUser';

-- --------------------------------------------------------
-- --------------------------------------------------------

-- 
-- Table structure for table `products`
-- 

DROP TABLE IF EXISTS `products`;
CREATE TABLE IF NOT EXISTS `products` (
  `id` int(12) NOT NULL auto_increment,
  `name` char(40) collate utf8_bin NOT NULL default '',
  `description` char(255) collate utf8_bin NOT NULL default '',
  `unitPrice` decimal(6,2) NOT NULL default '0.00',
  `unitMeasure` char(6) collate utf8_bin default NULL,
  `shippingWeight` int(11) NOT NULL default '0',
  `flavor` char(12) collate utf8_bin default NULL,
  `grind` char(12) collate utf8_bin default NULL,
  `color` char(12) collate utf8_bin default NULL,
  `size` char(12) collate utf8_bin default NULL,
  `photo` char(160) collate utf8_bin default NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

-- 
-- Dumping data for table `products`
-- 

INSERT INTO `products` (`id`, `name`, `description`, `unitPrice`, `unitMeasure`, `shippingWeight`, `flavor`, `grind`, `color`, `size`, `photo`) VALUES
(1, 'Holly Berry Creamer', 'A festive 6-ounce creamer for the holidays.', 23.90, 'EA', 2, NULL, NULL, 'white', '6 ounces', 'crm001.jpg'),
(2, 'Expresso Machine', 'A high quality expresso machine that will provide delicious refreshment year after year.', 149.95, 'EA', 12, NULL, NULL, 'Silver', NULL, 'exp001.jpg'),
(3, 'Wooden Coffee Grinder', 'Grind your morning coffee in this old-style grinder to obtain the freshest flavor possible.', 19.50, 'EA', 3, NULL, NULL, 'Natural Oak', NULL, 'grn001.jpg'),
(4, 'Travel Mug', 'This extra large travel mug will keep 16 ounces of your favorite morning beverage fresh and hot for up to 2 hours.', 17.95, 'EA', 3, NULL, NULL, 'Silver', '16 ounces', 'mug002.jpg'),
(5, 'Copper Coffee Carafe', 'This elegant carafe will keep 32 ounces of your favorite beverage at the correct serving temperature for up to 2 hours.', 24.95, 'EA', 5, NULL, NULL, 'Copper', '32 ounce', 'pot001.jpg');

-- --------------------------------------------------------

-- 
-- Table structure for table `customers`
-- 

DROP TABLE IF EXISTS `customers`;
CREATE TABLE IF NOT EXISTS `customers` (
  `id` int(8) NOT NULL auto_increment,
  `firstName` char(30) collate utf8_bin NOT NULL default '',
  `lastName` char(30) collate utf8_bin NOT NULL default '',
  `address1` char(40) collate utf8_bin NOT NULL default '',
  `address2` char(40) collate utf8_bin default NULL,
  `city` char(30) collate utf8_bin NOT NULL default '',
  `state` char(2) collate utf8_bin NOT NULL default '',
  `zip` char(11) collate utf8_bin NOT NULL default '',
  `email` char(160) collate utf8_bin NOT NULL default '',
  `phone` char(20) collate utf8_bin default NULL,
  `dob` datetime default NULL,
  `password` char(20) collate utf8_bin NOT NULL default '',
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

-- 
-- Dumping data for table `customers`
-- 

INSERT INTO `customers` (`id`, `firstName`, `lastName`, `address1`, `address2`, `city`, `state`, `zip`, `email`, `phone`, `dob`, `password`) VALUES
(1, 'James', 'Frederick', '123 Any Street', NULL, 'Anytown', 'PA', '12345-6789', 'jfred@myhouse.com', '(312) 967-1234', '1964-03-13 11:17:44', 'password'),
(2, 'Cheryl', 'Anderson', '600 West 300th Street', NULL, 'Farmtown', 'IN', '45678-9012', 'cheryl@myfarm.com', '(432) 547-1134', '1970-08-14 11:19:50', 'password'),
(3, 'Thomas', 'Edward', 'PO Box 9956', NULL, 'Smalltown', 'VA', '19022-2312', 'thomas@toyland.com', '(933) 339-3390', '1953-05-28 11:21:58', 'password'),
(4, 'Judy', 'Love', '111 Best Place', NULL, 'Best City', 'FL', '00123-5299', 'judy@coldwell.com', '(444) 213-0987', '1970-10-27 11:23:52', 'password');

-- --------------------------------------------------------

-- 
-- Table structure for table `orders`
-- 

DROP TABLE IF EXISTS `orders`;
CREATE TABLE IF NOT EXISTS `orders` (
  `id` int(12) NOT NULL auto_increment,
  `orderID` int(12) NOT NULL default '0',
  `orderDate` datetime default NULL,
  `customerID` int(12) NOT NULL default '0',
  `productID` int(12) NOT NULL default '0',
  `unitPrice` decimal(6,2) NOT NULL default '0.00',
  `orderQuantity` int(12) NOT NULL default '0',
  `totalPrice` decimal(6,2) NOT NULL default '0.00',
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

-- 
-- Dumping data for table `orders`
-- 

INSERT INTO `orders` (`id`, `orderID`, `orderDate`, `customerID`, `productID`, `unitPrice`, `orderQuantity`, `totalPrice`) VALUES
(1, 1, '2011-11-01 08:42:12', 1, 1, 23.90, 1, 23.90),
(2, 1, '2011-11-01 08:42:12', 1, 4, 17.90, 4, 71.60),
(3, 2, '2011-11-01 08:47:09', 4, 1, 23.90, 1, 23.90),
(4, 2, '2011-11-01 08:47:09', 4, 5, 24.90, 1, 24.90),
(5, 3, '2011-11-02 11:02:07', 3, 3, 19.95, 1, 19.95),
(6, 4, '2011-12-17 23:03:03', 3, 1, 23.95, 1, 23.95),
(7, 4, '2011-12-17 23:03:03', 3, 5, 24.95, 1, 24.95),
(8, 5, '2011-12-20 10:11:13', 2, 2, 149.95, 1, 149.95),
(9, 5, '2011-12-20 10:11:13', 2, 4, 17.95, 4, 71.80),
(10, 6, '2011-12-20 10:14:13', 4, 3, 19.95, 2, 39.90),
(11, 7, '2011-12-22 03:54:01', 1, 2, 149.95, 1, 149.95),
(12, 8, '2011-12-22 07:11:11', 3, 4, 17.95, 2, 35.90);

-- --------------------------------------------------------

-- 
-- Table structure for table `export_table`
-- 

DROP TABLE IF EXISTS `export_table`;
CREATE TABLE IF NOT EXISTS `export_table` (
  `id` int(4) NOT NULL auto_increment,
  `organizationName` varchar(255) collate utf8_bin NOT NULL default '',
  `boothNumber` varchar(4) collate utf8_bin NOT NULL default '',
  `tablesNeeded` int(4) NOT NULL default '0',
  `chairsNeeded` int(4) NOT NULL default '0',
  `lunch` set('Regular','Vegetarian') collate utf8_bin default NULL,
  `tshirtSize` set('S','M','L','XL','XXL') collate utf8_bin default NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

-- 
-- Dumping data for table `export_table`
-- 

INSERT INTO `export_table` (`id`, `organizationName`, `boothNumber`, `tablesNeeded`, `chairsNeeded`, `lunch`, `tshirtSize`) VALUES
(1, 'Best Volunteers', '1', 1, 2, 'Regular', 'XL'),
(2, 'Happy to Help Out', '2', 1, 2, 'Vegetarian', 'XL'),
(3, 'Frequent Flyers', '3', 1, 2, NULL, 'M'),
(4, 'Earth Tone Singers', '4', 1, 2, 'Regular', 'S'),
(5, 'Waterscapes', '5', 1, 2, 'Regular', 'L');

-- --------------------------------------------------------