-- makeOrderTracking.sql -- Create heterogeneous database for storing data from
--			    a sample order tracking application. Also create
--			    tables for storing generic RDF statements.
-- $Id: makeOrderTracking.sql,v 1.8 2004/06/17 15:55:30 eric Exp $

-- These instructions assume you want a database called OrderTracking. If you
-- want a different database name, adjust accordingly.
-- In MySQL, you can create this with:
--   mysqladmin create OrderTracking

-- MySQL invocation:
--   mysql -u root OrderTracking < makeOrderTracking.sql
-- If you want a heterogeneous database, add this command:
--   mysql -u root rdf < makeGenericTripleStore.sql

-- You should see the output:
--   first	last	product	shipFirst	shipLast
--   Biff	Thompson	pool	NULL	NULL
--   Chip	Thompson	skateboard	NULL	NULL
--   Chip	Thompson	nose ring	NULL	NULL
--   Chip	Thompson	other ring	Eustis	Walker
-- when you run the last of these commands.

-- Create application-specific tables:

CREATE TABLE Products (id INT NOT NULL, 
		       partNo CHAR(80), 
		       description varchar(255), 
		       price float, 
		       PRIMARY KEY (id), 
		       UNIQUE KEY u_name(partNo));

CREATE TABLE Addresses (id INT NOT NULL, 
			apt CHAR(20), 
			street CHAR(80), 
			city CHAR(80), 
			state CHAR(2), 
			contact int, 
			PRIMARY KEY (id), 
			UNIQUE KEY u_apt_street_city_state(apt, street, city, state));

CREATE TABLE Customers (id INT NOT NULL, 
			givenName CHAR(80), 
			familyName CHAR(80), 
			billingAddress INT NOT NULL, 
			PRIMARY KEY (id), 
			UNIQUE KEY u_givenName_familyName_billingAddress(givenName, familyName, billingAddress));

CREATE TABLE Orders (id INT NOT NULL, 
		     customer INT NOT NULL, 
		     product INT NOT NULL, 
		     orderDate TIMESTAMP NOT NULL, 
		     shippingAddress INT, 
		     PRIMARY KEY (id), 
		     UNIQUE KEY u_customer_product_orderDate(customer, product, orderDate));


-- Insert some sample data:

INSERT INTO Products (id, partNo, description, price) VALUES (1001, "G1wh01", "white house", 800000.00);
INSERT INTO Products (id, partNo, description, price) VALUES (1002, "G1pf01", "picket fence", 100.00);
INSERT INTO Products (id, partNo, description, price) VALUES (1003, "G1SUV213", "sport utility vehicle", 46000.00);
INSERT INTO Products (id, partNo, description, price) VALUES (1004, "G1013", "pool", 10000.00);
INSERT INTO Products (id, partNo, description, price) VALUES (1005, "G1grl5", "grill", 39.95);

INSERT INTO Products (id, partNo, description, price) VALUES (2001, "G0max5", "skateboard", 46.00);
INSERT INTO Products (id, partNo, description, price) VALUES (2002, "G0rk5", "rebelious music", 14.99);
INSERT INTO Products (id, partNo, description, price) VALUES (2003, "G0jwl6", "earring", 19.99);
INSERT INTO Products (id, partNo, description, price) VALUES (2004, "G0jwl13", "nose ring", 39.99);
INSERT INTO Products (id, partNo, description, price) VALUES (2005, "G0jpl56", "other ring", 60.00);

INSERT INTO Addresses (id, apt, street, city, state, contact) VALUES (1, NULL, "123 Elm Street", "EdgeCity", "AV", 1);
INSERT INTO Addresses (id, apt, street, city, state, contact) VALUES (2, "1", "245 King Street", "EdgeCity", "AV", 3);
INSERT INTO Addresses (id, apt, street, city, state, contact) VALUES (3, "18b", "Ally 17", "BigCity", "AV", 3);

INSERT INTO Customers (id, givenName, familyName, billingAddress) VALUES (1, "Biff", "Thompson", 1);
INSERT INTO Customers (id, givenName, familyName, billingAddress) VALUES (2, "Chip", "Thompson", 1);
INSERT INTO Customers (id, givenName, familyName, billingAddress) VALUES (3, "Eustis", "Walker", 2);
INSERT INTO Customers (id, givenName, familyName, billingAddress) VALUES (4, "Elie", "Tweak", 3);

INSERT INTO Orders (id, customer, product, orderDate, shippingAddress) VALUES (2185, 1, 1004, "2002-09-07", NULL);
INSERT INTO Orders (id, customer, product, orderDate, shippingAddress) VALUES (2186, 2, 2001, "2002-09-08", NULL);
INSERT INTO Orders (id, customer, product, orderDate, shippingAddress) VALUES (2187, 2, 2004, "2002-09-07", NULL);
INSERT INTO Orders (id, customer, product, orderDate, shippingAddress) VALUES (3183, 2, 2005, "2002-09-08", 2);


-- Test inserted data

SELECT C1.givenName AS first, C1.familyName AS last, P1.description AS product, C2.givenName AS shipFirst, C2.familyName AS shipLast 
FROM Orders AS O1 
     INNER JOIN Products AS P1 ON O1.product = P1.id 
     INNER JOIN Customers AS C1 ON O1.customer = C1.id 
     LEFT OUTER JOIN Addresses AS A1 ON O1.shippingAddress = A1.id 
     LEFT OUTER JOIN Customers AS C2 ON A1.contact = C2.id;

-- Expected results from above query:
-- +-------+----------+------------+-----------+----------+
-- | first | last     | product    | shipFirst | shipLast |
-- +-------+----------+------------+-----------+----------+
-- | Biff  | Thompson | pool       | NULL      | NULL     |
-- | Chip  | Thompson | skateboard | NULL      | NULL     |
-- | Chip  | Thompson | nose ring  | NULL      | NULL     |
-- | Chip  | Thompson | other ring | Eustis    | Walker   |
-- +-------+----------+------------+-----------+----------+

