-- join example tables, left and right
CREATE TABLE on_left ( id INTEGER, description TEXT );
CREATE TABLE on_right ( id INTEGER, description TEXT );

INSERT INTO on_left VALUES ( 1, 'on_left 01' );
INSERT INTO on_left VALUES ( 2, 'on_left 02' );
INSERT INTO on_left VALUES ( 3, 'on_left 03' );
INSERT INTO on_left VALUES ( 4, 'on_left 04' );
INSERT INTO on_left VALUES ( 5, 'on_left 05' );
INSERT INTO on_left VALUES ( 6, 'on_left 06' );
INSERT INTO on_left VALUES ( 7, 'on_left 07' );
INSERT INTO on_left VALUES ( 8, 'on_left 08' );
INSERT INTO on_left VALUES ( 9, 'on_left 09' );

INSERT INTO on_right VALUES ( 6, 'on_right 06' );
INSERT INTO on_right VALUES ( 7, 'on_right 07' );
INSERT INTO on_right VALUES ( 8, 'on_right 08' );
INSERT INTO on_right VALUES ( 9, 'on_right 09' );
INSERT INTO on_right VALUES ( 10, 'on_right 10' );
INSERT INTO on_right VALUES ( 11, 'on_right 11' );
INSERT INTO on_right VALUES ( 11, 'on_right 12' );
INSERT INTO on_right VALUES ( 11, 'on_right 13' );
INSERT INTO on_right VALUES ( 11, 'on_right 14' );


CREATE TABLE IF NOT EXISTS `Customers` (
  `cust_id` char(10) NOT NULL,
  `cust_name` char(50) NOT NULL,
  `cust_address` char(50) DEFAULT NULL,
  `cust_city` char(50) DEFAULT NULL,
  `cust_state` char(5) DEFAULT NULL,
  `cust_zip` char(10) DEFAULT NULL,
  `cust_country` char(50) DEFAULT NULL,
  `cust_contact` char(50) DEFAULT NULL,
  `cust_email` char(255) DEFAULT NULL,
  PRIMARY KEY (`cust_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Dumping data for table `Customers`
--

INSERT INTO `Customers` (`cust_id`, `cust_name`, `cust_address`, `cust_city`, `cust_state`, `cust_zip`, `cust_country`, `cust_contact`, `cust_email`) VALUES
('1000000001', 'Village Toys', '200 Maple Lane', 'Detroit', 'MI', '44444', 'USA', 'John Smith', 'sales@villagetoys.com'),
('1000000002', 'Kids Place', '333 South Lake Drive', 'Columbus', 'OH', '43333', 'USA', 'Michelle Green', NULL),
('1000000003', 'Fun4All', '1 Sunny Place', 'Muncie', 'IN', '42222', 'USA', 'Jim Jones', 'jjones@fun4all.com'),
('1000000004', 'Fun4All', '829 Riverside Drive', 'Phoenix', 'AZ', '88888', 'USA', 'Denise L. Stephens', 'dstephens@fun4all.com'),
('1000000005', 'The Toy Store', '4545 53rd Street', 'Chicago', 'IL', '54545', 'USA', 'Kim Howard', NULL);

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

--
-- Table structure for table `OrderItems`
--

CREATE TABLE IF NOT EXISTS `OrderItems` (
  `order_num` int(11) NOT NULL,
  `order_item` int(11) NOT NULL,
  `prod_id` char(10) NOT NULL,
  `quantity` int(11) NOT NULL,
  `item_price` decimal(8,2) NOT NULL,
  PRIMARY KEY (`order_num`,`order_item`),
  KEY `FK_OrderItems_Products` (`prod_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Dumping data for table `OrderItems`
--

INSERT INTO `OrderItems` (`order_num`, `order_item`, `prod_id`, `quantity`, `item_price`) VALUES
(20005, 1, 'BR01', 100, '5.49'),
(20005, 2, 'BR03', 100, '10.99'),
(20006, 1, 'BR01', 20, '5.99'),
(20006, 2, 'BR02', 10, '8.99'),
(20006, 3, 'BR03', 10, '11.99'),
(20007, 1, 'BR03', 50, '11.49'),
(20007, 2, 'BNBG01', 100, '2.99'),
(20007, 3, 'BNBG02', 100, '2.99'),
(20007, 4, 'BNBG03', 100, '2.99'),
(20007, 5, 'RGAN01', 50, '4.49'),
(20008, 1, 'RGAN01', 5, '4.99'),
(20008, 2, 'BR03', 5, '11.99'),
(20008, 3, 'BNBG01', 10, '3.49'),
(20008, 4, 'BNBG02', 10, '3.49'),
(20008, 5, 'BNBG03', 10, '3.49'),
(20009, 1, 'BNBG01', 250, '2.49'),
(20009, 2, 'BNBG02', 250, '2.49'),
(20009, 3, 'BNBG03', 250, '2.49');

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

--
-- Table structure for table `Orders`
--

CREATE TABLE IF NOT EXISTS `Orders` (
  `order_num` int(11) NOT NULL,
  `order_date` datetime NOT NULL,
  `cust_id` char(10) NOT NULL,
  PRIMARY KEY (`order_num`),
  KEY `FK_Orders_Customers` (`cust_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Dumping data for table `Orders`
--

INSERT INTO `Orders` (`order_num`, `order_date`, `cust_id`) VALUES
(20005, '2012-05-01 00:00:00', '1000000001'),
(20006, '2012-01-12 00:00:00', '1000000003'),
(20007, '2012-01-30 00:00:00', '1000000004'),
(20008, '2012-02-03 00:00:00', '1000000005'),
(20009, '2012-02-08 00:00:00', '1000000001');

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

--
-- Table structure for table `Products`
--

CREATE TABLE IF NOT EXISTS `Products` (
  `prod_id` char(10) NOT NULL,
  `vend_id` char(10) NOT NULL,
  `prod_name` char(255) NOT NULL,
  `prod_price` decimal(8,2) NOT NULL,
  `prod_desc` text,
  PRIMARY KEY (`prod_id`),
  KEY `FK_Products_Vendors` (`vend_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Dumping data for table `Products`
--

INSERT INTO `Products` (`prod_id`, `vend_id`, `prod_name`, `prod_price`, `prod_desc`) VALUES
('BNBG01', 'DLL01', 'Fish bean bag toy', '3.49', 'Fish bean bag toy, complete with bean bag worms with which to feed it'),
('BNBG02', 'DLL01', 'Bird bean bag toy', '3.49', 'Bird bean bag toy, eggs are not included'),
('BNBG03', 'DLL01', 'Rabbit bean bag toy', '3.49', 'Rabbit bean bag toy, comes with bean bag carrots'),
('BR01', 'BRS01', '8 inch teddy bear', '5.99', '8 inch teddy bear, comes with cap and jacket'),
('BR02', 'BRS01', '12 inch teddy bear', '8.99', '12 inch teddy bear, comes with cap and jacket'),
('BR03', 'BRS01', '18 inch teddy bear', '11.99', '18 inch teddy bear, comes with cap and jacket'),
('RGAN01', 'DLL01', 'Raggedy Ann', '4.99', '18 inch Raggedy Ann doll'),
('RYL01', 'FNG01', 'King doll', '9.49', '12 inch king doll with royal garments and crown'),
('RYL02', 'FNG01', 'Queen doll', '9.49', '12 inch queen doll with royal garments and crown');

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

--
-- Table structure for table `Vendors`
--

CREATE TABLE IF NOT EXISTS `Vendors` (
  `vend_id` char(10) NOT NULL,
  `vend_name` char(50) NOT NULL,
  `vend_address` char(50) DEFAULT NULL,
  `vend_city` char(50) DEFAULT NULL,
  `vend_state` char(5) DEFAULT NULL,
  `vend_zip` char(10) DEFAULT NULL,
  `vend_country` char(50) DEFAULT NULL,
  PRIMARY KEY (`vend_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Dumping data for table `Vendors`
--

INSERT INTO `Vendors` (`vend_id`, `vend_name`, `vend_address`, `vend_city`, `vend_state`, `vend_zip`, `vend_country`) VALUES
('BRE02', 'Bear Emporium', '500 Park Street', 'Anytown', 'OH', '44333', 'USA'),
('BRS01', 'Bears R Us', '123 Main Street', 'Bear Town', 'MI', '44444', 'USA'),
('DLL01', 'Doll House Inc.', '555 High Street', 'Dollsville', 'CA', '99999', 'USA'),
('FNG01', 'Fun and Games', '42 Galaxy Road', 'London', NULL, 'N16 6PS', 'England'),
('FRB01', 'Furball Inc.', '1000 5th Avenue', 'New York', 'NY', '11111', 'USA'),
('JTS01', 'Jouets et ours', '1 Rue Amusement', 'Paris', NULL, '45678', 'France');

--
-- Constraints for dumped tables
--

--
-- Constraints for table `OrderItems`
--
ALTER TABLE `OrderItems`
  ADD CONSTRAINT `FK_OrderItems_Products` FOREIGN KEY (`prod_id`) REFERENCES `Products` (`prod_id`),
  ADD CONSTRAINT `FK_OrderItems_Orders` FOREIGN KEY (`order_num`) REFERENCES `Orders` (`order_num`);

--
-- Constraints for table `Orders`
--
ALTER TABLE `Orders`
  ADD CONSTRAINT `FK_Orders_Customers` FOREIGN KEY (`cust_id`) REFERENCES `Customers` (`cust_id`);

--
-- Constraints for table `Products`
--
ALTER TABLE `Products`
  ADD CONSTRAINT `FK_Products_Vendors` FOREIGN KEY (`vend_id`) REFERENCES `Vendors` (`vend_id`);