DROP TABLE Sells; DROP TABLE Offers; DROP TABLE Store_location; DROP TABLE Alternate_name; DROP TABLE Product; DROP TABLE Currency; DROP TABLE Shipping_offering;

CREATE TABLE Currency ( currency_id DECIMAL(12) NOT NULL PRIMARY KEY, currency_name VARCHAR(255) NOT NULL, us_dollars_to_currency_ratio DECIMAL(12,2) NOT NULL);

CREATE TABLE Store_location ( store_location_id DECIMAL(12) NOT NULL PRIMARY KEY, store_name VARCHAR(255) NOT NULL, currency_accepted_id DECIMAL(12) NOT NULL);

CREATE TABLE Product ( product_id DECIMAL(12) NOT NULL PRIMARY KEY, product_name VARCHAR(255) NOT NULL, price_in_us_dollars DECIMAL(12,2) NOT NULL);

CREATE TABLE Sells ( sells_id DECIMAL(12) NOT NULL PRIMARY KEY, product_id DECIMAL(12) NOT NULL, store_location_id DECIMAL(12) NOT NULL);

CREATE TABLE Shipping_offering ( shipping_offering_id DECIMAL(12) NOT NULL PRIMARY KEY, offering VARCHAR(255) NOT NULL);

CREATE TABLE Offers ( offers_id DECIMAL(12) NOT NULL PRIMARY KEY, store_location_id DECIMAL(12) NOT NULL, shipping_offering_id DECIMAL(12) NOT NULL);

CREATE TABLE Alternate_name ( alternate_name_id DECIMAL(12) NOT NULL PRIMARY KEY, name VARCHAR(255) NOT NULL, product_id DECIMAL(12) NOT NULL);

ALTER TABLE Store_location ADD CONSTRAINT fk_location_to_currency FOREIGN KEY(currency_accepted_id) REFERENCES Currency(currency_id);

ALTER TABLE Sells ADD CONSTRAINT fk_sells_to_product FOREIGN KEY(product_id) REFERENCES Product(product_id);

ALTER TABLE Sells ADD CONSTRAINT fk_sells_to_location FOREIGN KEY(store_location_id) REFERENCES Store_location(store_location_id);

ALTER TABLE Offers ADD CONSTRAINT fk_offers_to_location FOREIGN KEY(store_location_id) REFERENCES Store_location(store_location_id);

ALTER TABLE Offers ADD CONSTRAINT fk_offers_to_offering FOREIGN KEY(shipping_offering_id) REFERENCES Shipping_offering(shipping_offering_id);

ALTER TABLE Alternate_name ADD CONSTRAINT fk_name_to_product FOREIGN KEY(product_id) REFERENCES Product(product_id);

INSERT INTO Currency(currency_id, currency_name, us_dollars_to_currency_ratio) VALUES(1, 'Britsh Pound', 0.67); INSERT INTO Currency(currency_id, currency_name, us_dollars_to_currency_ratio) VALUES(2, 'Canadian Dollar', 1.34); INSERT INTO Currency(currency_id, currency_name, us_dollars_to_currency_ratio) VALUES(3, 'US Dollar', 1.00); INSERT INTO Currency(currency_id, currency_name, us_dollars_to_currency_ratio) VALUES(4, 'Euro', 0.92); INSERT INTO Currency(currency_id, currency_name, us_dollars_to_currency_ratio) VALUES(5, 'Mexican Peso', 16.76);

INSERT INTO Shipping_offering(shipping_offering_id, offering) VALUES (50, 'Same Day'); INSERT INTO Shipping_offering(shipping_offering_id, offering) VALUES (51, 'Overnight'); INSERT INTO Shipping_offering(shipping_offering_id, offering) VALUES (52, 'Two Day');

--Glucometer INSERT INTO Product(product_id, product_name, price_in_us_dollars) VALUES(100, 'Glucometer', 50); INSERT INTO Alternate_name(alternate_name_id, name, product_id) VALUES(10000, 'Glucose Meter', 100); INSERT INTO Alternate_name(alternate_name_id, name, product_id) VALUES(10001, 'Blood Glucose Meter', 100); INSERT INTO Alternate_name(alternate_name_id, name, product_id) VALUES(10002, 'Glucose Monitoring System', 100);

--Bag Valve Mask INSERT INTO Product(product_id, product_name, price_in_us_dollars) VALUES(101, 'Bag Valve Mask', 25); INSERT INTO Alternate_name(alternate_name_id, name, product_id) VALUES(10003, 'Ambu Bag', 101); INSERT INTO Alternate_name(alternate_name_id, name, product_id) VALUES(10004, 'Oxygen Bag Valve Mask', 101);

--Digital Thermometer INSERT INTO Product(product_id, product_name, price_in_us_dollars) VALUES(102, 'Digital Thermometer', 250); INSERT INTO Alternate_name(alternate_name_id, name, product_id) VALUES(10005, 'Thermometer', 102);

--Electronic Stethoscope INSERT INTO Product(product_id, product_name, price_in_us_dollars) VALUES(103, 'Electronic Stethoscope', 350); INSERT INTO Alternate_name(alternate_name_id, name, product_id) VALUES(10006, 'Cardiology Stethoscope', 103);

--Handheld Pulse Oximeter INSERT INTO Product(product_id, product_name, price_in_us_dollars) VALUES(104, 'Handheld Pulse Oximeter', 450); INSERT INTO Alternate_name(alternate_name_id, name, product_id) VALUES(10007, 'Portable Pulse Oximeter', 104); INSERT INTO Alternate_name(alternate_name_id, name, product_id) VALUES(10008, 'Handheld Pulse Oximeter System', 104);

--Berlin Extension INSERT INTO Store_location(store_location_id, store_name, currency_accepted_id) VALUES(10, 'Berlin Extension', 4); INSERT INTO Sells(sells_id, store_location_id, product_id) VALUES(1000, 10, 100); INSERT INTO Sells(sells_id, store_location_id, product_id) VALUES(1001, 10, 101); INSERT INTO Sells(sells_id, store_location_id, product_id) VALUES(1002, 10, 102); INSERT INTO Sells(sells_id, store_location_id, product_id) VALUES(1003, 10, 104); INSERT INTO Offers(offers_id, store_location_id, shipping_offering_id) VALUES(150, 10, 52);

--Cancun Extension INSERT INTO Store_location(store_location_id, store_name, currency_accepted_id) VALUES(11, 'Cancun Extension', 5); INSERT INTO Sells(sells_id, store_location_id, product_id) VALUES(1004, 11, 101); INSERT INTO Sells(sells_id, store_location_id, product_id) VALUES(1005, 11, 102); INSERT INTO Sells(sells_id, store_location_id, product_id) VALUES(1006, 11, 104); INSERT INTO Offers(offers_id, store_location_id, shipping_offering_id) VALUES(151, 11, 52);

--London Extension INSERT INTO Store_location(store_location_id, store_name, currency_accepted_id) VALUES(12, 'London Extension', 1); INSERT INTO Sells(sells_id, store_location_id, product_id) VALUES(1007, 12, 100); INSERT INTO Sells(sells_id, store_location_id, product_id) VALUES(1008, 12, 101); INSERT INTO Sells(sells_id, store_location_id, product_id) VALUES(1009, 12, 102); INSERT INTO Sells(sells_id, store_location_id, product_id) VALUES(1010, 12, 103); INSERT INTO Sells(sells_id, store_location_id, product_id) VALUES(1011, 12, 104); INSERT INTO Offers(offers_id, store_location_id, shipping_offering_id) VALUES(152, 12, 50); INSERT INTO Offers(offers_id, store_location_id, shipping_offering_id) VALUES(153, 12, 51); INSERT INTO Offers(offers_id, store_location_id, shipping_offering_id) VALUES(154, 12, 52);

--New York Extension INSERT INTO Store_location(store_location_id, store_name, currency_accepted_id) VALUES(13, 'New York Extension', 3); INSERT INTO Sells(sells_id, store_location_id, product_id) VALUES(1012, 13, 100); INSERT INTO Sells(sells_id, store_location_id, product_id) VALUES(1013, 13, 101); INSERT INTO Sells(sells_id, store_location_id, product_id) VALUES(1014, 13, 102); INSERT INTO Sells(sells_id, store_location_id, product_id) VALUES(1015, 13, 103); INSERT INTO Sells(sells_id, store_location_id, product_id) VALUES(1016, 13, 104); INSERT INTO Offers(offers_id, store_location_id, shipping_offering_id) VALUES(155, 13, 51); INSERT INTO Offers(offers_id, store_location_id, shipping_offering_id) VALUES(156, 13, 52);

--Toronto Extension INSERT INTO Store_location(store_location_id, store_name, currency_accepted_id) VALUES(14, 'Toronto Extension', 2); INSERT INTO Sells(sells_id, store_location_id, product_id) VALUES(1017, 14, 100); INSERT INTO Sells(sells_id, store_location_id, product_id) VALUES(1018, 14, 101); INSERT INTO Sells(sells_id, store_location_id, product_id) VALUES(1019, 14, 102); INSERT INTO Sells(sells_id, store_location_id, product_id) VALUES(1020, 14, 103); INSERT INTO Sells(sells_id, store_location_id, product_id) VALUES(1021, 14, 104); INSERT INTO Offers(offers_id, store_location_id, shipping_offering_id) VALUES(157, 14, 52);

5. Imagine that Denisha is a traveling doctor who works for an agency that sends her to various locations throughout the world with very little notice. As a result, she needs to know about medical supplies that are available in all store locations (not just some locations). This way, regardless of where she is sent, she knows she can purchase those products. She is also interested in viewing the alternate names for these products, so she is absolutely certain what each product is.

Note: It is important to Denisha that she can purchase the product in any location; only products sold in all stores should be listed, that is, if a product is sold in some stores, but not all stores, it should not be listed.

a. Develop a single query to list out these results, making sure to use uncorrelated subqueries where needed (one subquery will be put into the WHERE clause of the outer query). b. Explain how what each subquery does, its role in the overall query, and how the subqueries were integrated to give the correct results.

In your thinking about how to address this use case, one item should be brought to your attention the phrase all store locations. By eyeballing the data, you can determine the number of locations and hardcode that number, which will satisfy Denishas request at this present time; however, as the number of locations change over time (with stores opening or closing), such hardcoding would fail. Its better to dynamically determine the total number of locations in the query itself so that the results are correct over time.

6. For this problem you will write a single query to address the same use case as in step 5, but change your query so that the main uncorrelated subquery is in the FROM clause rather than in the WHERE clause. The results should be the same as in step 5, except of course possibly row ordering which can vary. Explain how you integrated the subquery into the FROM clause to derive the same results as step 5.

7. For this problem you will write a single query to address the same use case as in step 5, but change your query to use a correlated query combined with an EXISTS clause. The results should be the same as in step 5, except of course possibly row ordering which can vary. Explain:

a. how your solution makes use of the correlated subquery and EXISTS clause to help retrieve the result b. how and when the correlated subquery is executed in the context of the outer query.

8. For this problem you will write a query to address the same use case as in step 6, except you will create and use a view in place of the subquery. The results should be the same as in step 6, except of course possibly row ordering which can vary. 9. Compare and contrast the construction of the four different queries you developed in steps 5-8, which all address the same use case. What advantages and disadvantages do each construction have over the others? Which do you prefer and why?