create table product_categories (
	product_category_id	integer primary key,
	product_category_name	varchar(100) not null
);

create table manufacturers (
	manufacturer_id		integer primary key,
	manufacturer_name	varchar(100) not null
);

create table products (
	product_id		integer primary key,
	product_name		varchar(100) not null,
	product_category_id	references product_categories,
	manufacturer_id		references manufacturers
);

create table cities (
	city_id			integer primary key,
	city_name		varchar(100) not null,
	state			varchar(100) not null,
	population		integer not null
);

create table stores (
	store_id		integer primary key,
	city_id			references cities,
	store_location		varchar(200) not null,
	phone_number		varchar(20)	
);

create table sales (
	product_id	not null references products,
	store_id	not null references stores,
	quantity_sold	integer not null,
	-- the Oracle "date" type is precise to the second
	-- unlike the ANSI date datatype
	date_time_of_sale	date not null
);

-- put some data in 

insert into product_categories values (1, 'toothpaste');
insert into product_categories values (2, 'soda');

insert into manufacturers values (68, 'Colgate');
insert into manufacturers values (5, 'Coca Cola');

insert into products values (567, 'Colgate Gel Pump 6.4 oz.', 1, 68);
insert into products values (219, 'Diet Coke 12 oz. can', 2, 5);

insert into cities values (34, 'San Francisco', 'California', 700000);
insert into cities values (58, 'East Fishkill', 'New York', 30000);

insert into stores values (16, 34, '510 Main Street', '415-555-1212');
insert into stores values (17, 58, '13 Maple Avenue', '914-555-1212');

insert into sales values (567, 17, 1, to_date('1997-10-22 09:35:14', 'YYYY-MM-DD HH24:MI:SS'));
insert into sales values (219, 16, 4, to_date('1997-10-22 09:35:14', 'YYYY-MM-DD HH24:MI:SS'));
insert into sales values (219, 17, 1, to_date('1997-10-22 09:35:17', 'YYYY-MM-DD HH24:MI:SS'));

-- keep track of which dates are holidays
-- the presence of a date (all dates will be truncated to midnight)
-- in this table indicates that it is a holiday
create table holiday_map (
holiday_date		date primary key
);

-- where the prices are kept
create table product_prices (
product_id	not null references products,
from_date	date not null,
price		number not null
);

insert into product_prices values (567,'1997-01-01',2.75);
insert into product_prices values (219,'1997-01-01',0.40);

create table time_dimension (
	time_key		integer primary key,
	-- just to make it a little easier to work with; this is 
	-- midnight (TRUNC) of the date in question
	oracle_date		date not null,
	day_of_week		varchar(9) not null, -- 'Monday', 'Tuesday'...
	day_number_in_month	integer not null, -- 1 to 31
	day_number_overall	integer not null, -- days from the epoch (first day is 1)
	week_number_in_year	integer not null, -- 1 to 52
	week_number_overall	integer not null, -- weeks start on Sunday
	month			integer not null, -- 1 to 12
	month_number_overall	integer not null,
	quarter			integer not null, -- 1 to 4
	fiscal_period		varchar(10),
	holiday_flag		char(1) default 'f' check (holiday_flag in ('t', 'f')),
	weekday_flag		char(1) default 'f' check (weekday_flag in ('t', 'f')),
	season			varchar(50),
	event			varchar(50)
);

create table stores_dimension (
	stores_key		integer primary key,
	name			varchar(100),
	city			varchar(100),
	county			varchar(100),
	state			varchar(100),
	zip_code		varchar(100),
	date_opened		date,
	date_remodeled		date,
	-- 'small', 'medium', 'large', or 'super'
	store_size		varchar(100),
	...
);

create table sales_fact (
	time_key	integer not null references time_dimension,
	product_id	integer,
	store_id	integer,
	unit_sales	integer,
	dollar_sales	number
);