-- Recreate the database from lecture 2 with a few minor changes:
--  * Company is simplified.
--  * Company.name is now cname and Product.name is now pname so that there is
--    no ambiguity when joined.

CREATE TABLE Company(
   cid INT PRIMARY KEY,
   cname VARCHAR(20),
   city VARCHAR(20));

INSERT INTO Company VALUES (1, 'GizmoWorks', 'Seattle');
INSERT INTO Company VALUES (2, 'Canon',    'Tokyo');
INSERT INTO Company VALUES (3, 'Hitachi',  'Kyoto');
INSERT INTO Company VALUES (4, 'QFC',  'Seattle');

CREATE TABLE Product(
   pname VARCHAR(20) PRIMARY KEY,
   price float,
   cid INT references Company);

PRAGMA foreign_keys=ON;

INSERT INTO Product VALUES ('Gizmo',      19.99, 1);
INSERT INTO Product VALUES ('PowerGizmo', 29.99, 1);
INSERT INTO Product VALUES ('SingleTouch', 149.99, 2);
INSERT INTO Product VALUES ('MultiTouch', 199.99, 3);
INSERT INTO Product VALUES ('SuperGizmo', 49.99, 3);
