-------------------------------------------------------------
-- Example SQLite commands for 45881/70455 Modern Data Management
-- Wolfgang Gatterbauer
-- v151102
-------------------------------------------------------------


-------------------------
-- The following command enables foreign key constraints in SQLite.
-- They are not activated by default.
-- More details: http://code.google.com/p/sqlite-manager/wiki/ForeignKeys
-------------------------

PRAGMA foreign_keys=ON; 


-------------------------
-- Drop tables if they already exist
-------------------------

drop table if exists Company;
drop table if exists Product;


-------------------------
-- Create the tables
-------------------------

create table Company (
	CName 	char(20) PRIMARY KEY, 
	StockPrice 	int, 
	Country	char(20)); 

create table Product (
	PName 	char(20), 
	Price 	decimal(9, 2), 
	Category 	char(20), 
	Manufacturer 	char(20),
PRIMARY KEY (PName),
FOREIGN KEY (Manufacturer) REFERENCES Company(CName) ); 


---------------------------
-- Populate the tables
---------------------------

insert into Company values ('GizmoWorks', 25, 'USA');
insert into Company values ('Canon', 65, 'Japan');
insert into Company values ('Hitachi', 15, 'Japan');

insert into Product values ('Gizmo', 19.99, 'Gadgets', 'GizmoWorks');
insert into Product values ('PowerGizmo', 29.99, 'Gadgets', 'GizmoWorks');
insert into Product values ('SingleTouch', 149.99, 'Photography', 'Canon');
insert into Product values ('MultiTouch', 203.99, 'Household', 'Hitachi');


-- drop table Company
-- drop table Company cascade constraints;