CREATE TABLE product (rfid VARCHAR(32) UNIQUE PRIMARY KEY, upc NUMERIC(12), brand_id INTEGER, name TEXT, price NUMERIC(5,2), price_unit_id INTEGER, price_quantity INTEGER, sale_type INTEGER, sale_quantity INTEGER, sale_price NUMERIC(5,2), quantity NUMERIC(5,2), quantity_unit_id INTEGER, location_id INTEGER, section_id INTEGER, clip TEXT);

CREATE TABLE location (id INTEGER UNIQUE PRIMARY KEY, name TEXT, aisle VARCHAR(4), clip TEXT);
CREATE TABLE section (id INTEGER UNIQUE PRIMARY KEY, name TEXT, location_id INTEGER, clip TEXT);
CREATE TABLE measure (id INTEGER UNIQUE PRIMARY KEY, name TEXT, clip TEXT);
CREATE TABLE alphanum (id INTEGER UNIQUE PRIMARY KEY, char VARCHAR(1), clip TEXT);
CREATE TABLE numeric (id INTEGER UNIQUE PRIMARY KEY, num VARCHAR(4), clip TEXT);


CREATE TABLE brand (id INTEGER UNIQUE PRIMARY KEY, name TEXT, clip TEXT);
CREATE TABLE sale ( id INTEGER UNIQUE PRIMARY KEY, name TEXT, clip TEXT);

INSERT INTO brand VALUES(0, 'Mercer Ranch', 'brand-00');
INSERT INTO brand VALUES(1, 'Western Express', 'brand-01');
INSERT INTO brand VALUES(2, 'Sun World', 'brand-02');
INSERT INTO brand VALUES(3, 'Pace', 'brand-03');
INSERT INTO brand VALUES(4, 'Kikkoman', 'brand-04');
INSERT INTO brand VALUES(5, 'Campbells', 'brand-05');
INSERT INTO brand VALUES(6, 'Pasta Roni', 'brand-06');
INSERT INTO brand VALUES(7, 'Wesson', 'brand-07');
INSERT INTO brand VALUES(8, 'Crisco', 'brand-08');
INSERT INTO brand VALUES(9, 'Nabisco', 'brand-09');
INSERT INTO brand VALUES(10, 'Ecco Domani', 'brand-10');
INSERT INTO brand VALUES(11, 'Huggies', 'brand-11');
INSERT INTO brand VALUES(12, 'Pantene', 'brand-12');
INSERT INTO brand VALUES(13, 'Tylenol', 'brand-13');
INSERT INTO brand VALUES(14, 'Lysol', 'brand-14');
INSERT INTO brand VALUES(15, 'Northern', 'brand-15');
INSERT INTO brand VALUES(16, 'Sara Lee', 'brand-16');
INSERT INTO brand VALUES(17, 'Sam Adams', 'brand-17');

INSERT INTO brand VALUES(18, 'Bisquick', 'brand-18');
INSERT INTO brand VALUES(19, 'Post', 'brand-19');
INSERT INTO brand VALUES(20, 'Dianes', 'brand-20'); 
INSERT INTO brand VALUES(21, 'Twizzlers', 'brand-21');

INSERT INTO sale VALUES(0, 'Two For One', 'sale-00');
INSERT INTO sale VALUES(1, 'Discounted Price', 'sale-01');
INSERT INTO sale VALUES(2, 'Buy One, Get One Free', 'sale-02');
INSERT INTO sale VALUES(3, 'Quantity Discount', 'sale-03');

INSERT INTO location VALUES(0, 'Garden Produce', NULL, 'loc-00');
INSERT INTO location VALUES(1, 'Fruits & Vegetables', NULL, 'loc-01');
INSERT INTO location VALUES(2, 'Meat & Poultry', NULL, 'loc-02');
INSERT INTO location VALUES(3, 'Seafood', NULL, 'loc-03');
INSERT INTO location VALUES(4, 'Lunch & Dinner', '1', 'loc-04');
INSERT INTO location VALUES(5, 'Breakfast', '2', 'loc-05');
INSERT INTO location VALUES(6, 'Bake Time', '3', 'loc-06');
INSERT INTO location VALUES(7, 'Snack Time', '3', 'loc-07');
INSERT INTO location VALUES(8, 'Wine', '4', 'loc-08');
INSERT INTO location VALUES(9, 'Snack Time', '4', 'loc-09');
INSERT INTO location VALUES(10, 'Breakfast', '5', 'loc-10');
INSERT INTO location VALUES(11, 'Baby Care', '5', 'loc-11');
INSERT INTO location VALUES(12, 'Personal Care', '6', 'loc-12');
INSERT INTO location VALUES(13, 'Pet Care', '7', 'loc-13');
INSERT INTO location VALUES(14, 'Dairy', NULL, 'loc-14');
INSERT INTO location VALUES(15, 'Party, Print & Paper', '7', 'loc-15');
INSERT INTO location VALUES(16, 'Party, Print & Paper', '8', 'loc-16');
INSERT INTO location VALUES(17, 'Home Care', '8', 'loc-17');
INSERT INTO location VALUES(18, 'Frozen Choices', '9', 'loc-18');
INSERT INTO location VALUES(19, 'Frozen Choices', '10', 'loc-19');
INSERT INTO location VALUES(20, 'Bakery', NULL, 'loc-20');
INSERT INTO location VALUES(21, 'Bread & Rolls', NULL, 'loc-21');
INSERT INTO location VALUES(22, 'Wine & Beer', '11', 'loc-22');
INSERT INTO location VALUES(23, 'Dairy Choices', '11', 'loc-23');
INSERT INTO location VALUES(24, 'Luncheon Meats', NULL, 'loc-24');
INSERT INTO location VALUES(25, 'Deli', NULL, 'loc-25');
INSERT INTO location VALUES(26, 'Customer Service', NULL, 'loc-26');
INSERT INTO location VALUES(27, 'Florist', NULL, 'loc-27');
INSERT INTO location VALUES(28, 'Pharmacy', NULL, 'loc-28');


INSERT INTO section VALUES(0, 'Mexican Foods', 4, 'sec-00');
INSERT INTO section VALUES(1, 'Asian Foods', 4, 'sec-01');
INSERT INTO section VALUES(2, 'Pasta', 4, 'sec-02');
INSERT INTO section VALUES(3, 'Soups', 4, 'sec-03');
INSERT INTO section VALUES(4, 'Juices', 5, 'sec-04');
INSERT INTO section VALUES(5, 'Canned Vegetables', 4, 'sec-05');
INSERT INTO section VALUES(6, 'Canned Fruit', 4, 'sec-06');
INSERT INTO section VALUES(7, 'Packaged Dinners', 4, 'sec-07');
INSERT INTO section VALUES(8, 'Salad Dressing', 4, 'sec-08');
INSERT INTO section VALUES(9, 'Stuffing & Gravy', 4, 'sec-09');
INSERT INTO section VALUES(10, 'Pickles & Olives', 4, 'sec-10');
INSERT INTO section VALUES(11, 'Cake Mixes', 6, 'sec-11');
INSERT INTO section VALUES(12, 'Dry & Canned Milk', 6, 'sec-12');
INSERT INTO section VALUES(13, 'Cooking Oil', 6, 'sec-13');
INSERT INTO section VALUES(14, 'Spices', 6, 'sec-14');
INSERT INTO section VALUES(15, 'Food Storage', 6, 'sec-15');
INSERT INTO section VALUES(16, 'Crackers', 7, 'sec-16');
INSERT INTO section VALUES(17, 'Rice Cakes', 7, 'sec-17');
INSERT INTO section VALUES(18, 'Cookies', 7, 'sec-18');
INSERT INTO section VALUES(19, 'Wine', 8, 'sec-19');
INSERT INTO section VALUES(20, 'Premium Wines', 8, 'sec-20');
INSERT INTO section VALUES(21, 'Champagne', 8, 'sec-21');
INSERT INTO section VALUES(22, 'Imported Wines', 8, 'sec-22');
INSERT INTO section VALUES(23, 'Soft Drinks', 9, 'sec-23');
INSERT INTO section VALUES(24, 'Flavored Beverages', 9, 'sec-24');
INSERT INTO section VALUES(25, 'Sport Drinks', 9, 'sec-25');
INSERT INTO section VALUES(26, 'Water', 9, 'sec-26');
INSERT INTO section VALUES(27, 'Popcorn & Nuts', 9, 'sec-27');
INSERT INTO section VALUES(28, 'Chips', 9, 'sec-28');
INSERT INTO section VALUES(29, 'Pancakes & Syrup', 10, 'sec-29');
INSERT INTO section VALUES(30, 'Cereal', 10, 'sec-30');
INSERT INTO section VALUES(31, 'Hot Cereal', 10, 'sec-31');
INSERT INTO section VALUES(32, 'Formula & Accessories', 11, 'sec-32');
INSERT INTO section VALUES(33, 'Baby Food', 11, 'sec-34');
INSERT INTO section VALUES(34, 'Diapers', 11, 'sec-34');
INSERT INTO section VALUES(35, 'Coffee', 10, 'sec-35');
INSERT INTO section VALUES(36, 'Tea', 10, 'sec-36');
INSERT INTO section VALUES(37, 'Powdered Drinks', 10, 'sec-37');
INSERT INTO section VALUES(38, 'Feminine Care & Incontinents', 12, 'sec-38');
INSERT INTO section VALUES(39, 'Hosiery & Cosmetics', 12, 'sec-39');
INSERT INTO section VALUES(40, 'Shampoo & Haircare', 12, 'sec-40');
INSERT INTO section VALUES(41, 'Digestive', 12, 'sec-41');
INSERT INTO section VALUES(42, 'Pain Relief', 12, 'sec-42');
INSERT INTO section VALUES(43, 'Cough & Cold', 12, 'sec-43');
INSERT INTO section VALUES(44, 'Dog Food', 13, 'sec-44');
INSERT INTO section VALUES(45, 'Cat Food', 13, 'sec-45');
INSERT INTO section VALUES(46, 'Pet Supplies', 13, 'sec-46');
INSERT INTO section VALUES(47, 'Automotive', 13, 'sec-47');
INSERT INTO section VALUES(48, 'Light Bulbs', 13, 'sec-48');
INSERT INTO section VALUES(49, 'Stationery', 13, 'sec-49');
INSERT INTO section VALUES(50, 'Greeting Cards', 15, 'sec-50');
INSERT INTO section VALUES(51, 'Gift Wrap', 15, 'sec-51');
INSERT INTO section VALUES(52, 'Party Supplies', 15, 'sec-52');
INSERT INTO section VALUES(53, 'Magazines & Books', 16, 'sec-53');
INSERT INTO section VALUES(54, 'Cleanin Supplies', 17, 'sec-54');
INSERT INTO section VALUES(55, 'Air Fresheners', 17, 'sec-55');
INSERT INTO section VALUES(56, 'Wax & Polish', 17, 'sec-56');
INSERT INTO section VALUES(57, 'Dish Soap', 17, 'sec-57');
INSERT INTO section VALUES(58, 'Laundry', 17, 'sec-58');
INSERT INTO section VALUES(59, 'Charcoal & Logs', 17, 'sec-59');
INSERT INTO section VALUES(60, 'Brooms & Mops', 17, 'sec-60');
INSERT INTO section VALUES(61, 'Bathroom Tisue', 17, 'sec-61');
INSERT INTO section VALUES(62, 'Paper Towels', 17, 'sec-62');
INSERT INTO section VALUES(63, 'Dinners', 18, 'sec-63');
INSERT INTO section VALUES(64, 'Pizza', 18, 'sec-64');
INSERT INTO section VALUES(65, 'Meat', 18, 'sec-65');
INSERT INTO section VALUES(66, 'Vegetables', 18, 'sec-66');
INSERT INTO section VALUES(67, 'Breakfast', 18, 'sec-67');
INSERT INTO section VALUES(68, 'Juices', 18, 'sec-68');
INSERT INTO section VALUES(69, 'Desserts', 19, 'sec-69');
INSERT INTO section VALUES(70, 'Ice Cream', 19, 'sec-70');
INSERT INTO section VALUES(71, 'Novelties', 19, 'sec-71');
INSERT INTO section VALUES(72, 'Eggs', 19, 'sec-72');
INSERT INTO section VALUES(73, 'Butter', 19, 'sec-73');
INSERT INTO section VALUES(74, 'Yogurt', 19, 'sec-74');
INSERT INTO section VALUES(75, 'MicroBrews & Beer', 22, 'sec-75');
INSERT INTO section VALUES(76, 'Cold Beverages', 23, 'sec-76');
INSERT INTO section VALUES(77, 'Cheese', 23, 'sec-77');


INSERT INTO measure VALUES (0, 'lb', 'meas-00-lb');
INSERT INTO measure VALUES (1, 'ct', 'meas-01-ct');
INSERT INTO measure VALUES (2, 'oz', 'meas-02-oz');
INSERT INTO measure VALUES (3, 'floz', 'meas-03-floz');
INSERT INTO measure VALUES (4, 'ea', 'meas-04-ea');
INSERT INTO measure VALUES (5, 'lt', 'meas-05-lt');
INSERT INTO measure VALUES (6, 'ml', 'meas-06-ml');
INSERT INTO measure VALUES (7, 'per', 'meas-07-per');
INSERT INTO measure VALUES (8, 'for', 'meas-08-for');
INSERT INTO measure VALUES (9, 'dollar', 'meas-09-dollar');

INSERT INTO numeric VALUES (0, '10', 'numeric-10');
INSERT INTO numeric VALUES (1, '11', 'numeric-11');
INSERT INTO numeric VALUES (2, '12', 'numeric-12');
INSERT INTO numeric VALUES (3, '13', 'numeric-13');
INSERT INTO numeric VALUES (4, '14', 'numeric-14');
INSERT INTO numeric VALUES (5, '15', 'numeric-15');
INSERT INTO numeric VALUES (6, '16', 'numeric-16');
INSERT INTO numeric VALUES (7, '17', 'numeric-17');
INSERT INTO numeric VALUES (8, '18', 'numeric-18');
INSERT INTO numeric VALUES (9, '19', 'numeric-19');
INSERT INTO numeric VALUES (10, '20', 'numeric-20');
INSERT INTO numeric VALUES (11, '30', 'numeric-30');
INSERT INTO numeric VALUES (13, '40', 'numeric-40');
INSERT INTO numeric VALUES (14, '50', 'numeric-50');
INSERT INTO numeric VALUES (15, '60', 'numeric-60');
INSERT INTO numeric VALUES (16, '70', 'numeric-70');
INSERT INTO numeric VALUES (17, '80', 'numeric-80');
INSERT INTO numeric VALUES (18, '90', 'numeric-90');
INSERT INTO numeric VALUES (19, '100', 'numeric-100');
INSERT INTO numeric VALUES (20, '1000', 'numeric-1000');


INSERT INTO alphanum VALUES (0, '0', 'alphanum-00');
INSERT INTO alphanum VALUES (1, '1', 'alphanum-01');
INSERT INTO alphanum VALUES (2, '2', 'alphanum-02');
INSERT INTO alphanum VALUES (3, '3', 'alphanum-03');
INSERT INTO alphanum VALUES (4, '4', 'alphanum-04');
INSERT INTO alphanum VALUES (5, '5', 'alphanum-05');
INSERT INTO alphanum VALUES (6, '6', 'alphanum-06');
INSERT INTO alphanum VALUES (7, '7', 'alphanum-07');
INSERT INTO alphanum VALUES (8, '8', 'alphanum-08');
INSERT INTO alphanum VALUES (9, '9', 'alphanum-09');
INSERT INTO alphanum VALUES (10, 'A', 'alphanum-10');
INSERT INTO alphanum VALUES (11, 'B', 'alphanum-11');
INSERT INTO alphanum VALUES (12, 'C', 'alphanum-12');
INSERT INTO alphanum VALUES (13, 'D', 'alphanum-13');
INSERT INTO alphanum VALUES (14, 'E', 'alphanum-14');
INSERT INTO alphanum VALUES (15, 'F', 'alphanum-15');
INSERT INTO alphanum VALUES (16, 'G', 'alphanum-16');
INSERT INTO alphanum VALUES (17, 'H', 'alphanum-17');
INSERT INTO alphanum VALUES (18, 'I', 'alphanum-18');
INSERT INTO alphanum VALUES (19, 'J', 'alphanum-19');
INSERT INTO alphanum VALUES (20, 'K', 'alphanum-20');
INSERT INTO alphanum VALUES (21, 'L', 'alphanum-21');
INSERT INTO alphanum VALUES (22, 'M', 'alphanum-22');
INSERT INTO alphanum VALUES (23, 'N', 'alphanum-23');
INSERT INTO alphanum VALUES (24, 'O', 'alphanum-24');
INSERT INTO alphanum VALUES (25, 'P', 'alphanum-25');
INSERT INTO alphanum VALUES (26, 'Q', 'alphanum-26');
INSERT INTO alphanum VALUES (27, 'R', 'alphanum-27');
INSERT INTO alphanum VALUES (28, 'S', 'alphanum-28');
INSERT INTO alphanum VALUES (29, 'T', 'alphanum-29');
INSERT INTO alphanum VALUES (30, 'U', 'alphanum-30');
INSERT INTO alphanum VALUES (31, 'V', 'alphanum-31');
INSERT INTO alphanum VALUES (32, 'W', 'alphanum-32');
INSERT INTO alphanum VALUES (33, 'X', 'alphanum-33');
INSERT INTO alphanum VALUES (34, 'Y', 'alphanum-34');
INSERT INTO alphanum VALUES (35, 'Z', 'alphanum-35');
INSERT INTO alphanum VALUES (36, '$', 'alphanum-36');
INSERT INTO alphanum VALUES (37, '&', 'alphanum-37');
INSERT INTO alphanum VALUES (38, '*', 'alphanum-38');
INSERT INTO alphanum VALUES (39, '.', 'alphanum-39');
INSERT INTO alphanum VALUES (40, '/', 'alphanum-40');


INSERT INTO product VALUES ('E007000017FAB6E7', '016000420809', 18, 'Baking Mix', '4.29', NULL, NULL, NULL, NULL, NULL, '60.0', 2, 6, 13, 'product-21');
INSERT INTO product VALUES ('E007000017FAB6D3', '0430001114605', 19, 'Raisin Bran Cereal', '3.69', NULL, NULL, NULL, NULL, NULL, '20.0', 2, 5, 30, 'product-22');
INSERT INTO product VALUES ('E007000017FAB6D2', '041565142163', 3, 'Chunky Salsa' , '2.49', NULL, NULL, NULL, NULL, NULL, '16.0', 2, 4, 0, 'product-23');
INSERT INTO product VALUES ('E00781BCC195072A', '070343004102', 20, 'Tortilla Rounds', '3.89', NULL, NULL, 1, NULL, '2.50', '32.0', 2, 9, 28, 'product-24');
INSERT INTO product VALUES ('E00781BCC1953E60', '034000563029', 21, 'Strawberry Twists', '1.69', NULL, NULL, 1, NULL, '1.50', '17.6', 2, 9, 18, 'product-25');
INSERT INTO product VALUES ('E007000017FAB6EA', '015300440490', 6, 'Angel Hair Pasta w/Parmesan Cheese', '5.00', 7, 4, 3, 5, '5.00', '5.1', 2, 4, 7, 'product-09');
INSERT INTO product VALUES ('E00781BCC1957A75', '04482008', 9, 'Oreo Cookies', '3.69', NULL, NULL, 2, NULL, NULL, '1.13', 0, 9, 18, 'product-11');
INSERT INTO product VALUES ('E007000017FAB6D4', '078000082463', NULL, 'Dr. Pepper Soda', '1.49', NULL, NULL, 1, NULL, '0.99', '2.0', 5, 9, 23, 'product-13');
INSERT INTO product VALUES ('E007000017FAB6E4', '087692100122', 17, 'Beer - Boston Lager - 6 Bottles', '7.19', NULL, NULL, 1, NULL, '6.39', '12.0', 3, 22, 75, 'product-20');


INSERT INTO product VALUES ('E00781BCC1950541', '033393650200', NULL, 'Head of Lettuce', '1.29', 4, NULL, NULL, NULL, NULL, NULL, NULL, 0, NULL, 'product-00');
INSERT INTO product VALUES ('E00781BCC1950A39', '033383664010', 0, 'Carrots', '1.39', 4, NULL, NULL, NULL, NULL, '2.0', 0, 1, NULL, 'product-01');
INSERT INTO product VALUES ('E007000017FAB6D7', '033383699990', 1, 'Cauliflower', '1.49', 0, NULL, NULL, NULL, NULL, '5.0', 0, 1, NULL, 'product-02');
INSERT INTO product VALUES ('E00781BCC195620D', '033383118560', 2, 'Grapefruit - 5lb Ruby', '3.79', 4, NULL, NULL, NULL, NULL, '5.0', 0, 1, NULL, 'product-03');
INSERT INTO product VALUES ('E00781BCC1957C75', '041565138340', 3, 'Enchilada Sauce - Mild', '2.99', NULL, NULL, NULL, NULL, NULL, '17.25', 2, 4, 0, 'product-04');
INSERT INTO product VALUES ('E00781BCC1956824', '041565000270', 3, 'Picante Sauce - Mild', '3.59', NULL, NULL, NULL, NULL, NULL, '24.0', 2, 4, 0, 'product-05');
INSERT INTO product VALUES ('E00781BCC195387C', '051000012510', 5, 'Chicken Noodle Soup', '0.87', NULL, NULL, NULL, NULL, NULL, '10.75', 2, 4, 3, 'product-08');
INSERT INTO product VALUES ('E007000017FAB6E9', '027000611760', 7, 'Vegetable Oil', '3.79', NULL, NULL, NULL, NULL, NULL, '48.0', 3, 6, 13, 'product-10');
INSERT INTO product VALUES ('E007000017FAB6E8', '027000253620', 8, 'Vegetable Oil', '4.15', NULL, NULL, NULL, NULL, NULL, '48.0', 3, 6, 13, 'product-10');


INSERT INTO product VALUES ('14', '032100047241', 16, 'Cherry Pie - Homestyle', '5.99', NULL, NULL, NULL, NULL, NULL, '37.0', 2, 19, 69, 'product-19'); 
INSERT INTO product VALUES ('15', '080878004508', 12, 'Pro-V Shampoo - Sheer Volume', '4.49', NULL, NULL, NULL, NULL, NULL, '13.5', 3, 12, 40, 'product-15');
INSERT INTO product VALUES ('16', '300450124104', 13, 'Tylenol Geltabs - extra Strength, 500mg', '8.99', NULL, NULL, NULL, NULL, NULL, '100.0', 1, 12, 42, 'product-16');
INSERT INTO product VALUES ('17', '019200008884', 14, 'Kitchen Cleaner Spray - Antibacterial', '3.19', NULL, NULL, NULL, NULL, NULL, '22.0', 3, 17, 54, 'product-17');
INSERT INTO product VALUES ('18', '042000164009', 15, 'Ultra Bath Tissue - Quilted Double Roll', '8.79', NULL, NULL, NULL, NULL, NULL, '12.0', 1, 17, 61, 'product-18');

