rem
rem	Script:		c_prods.sql
rem	Author:		jpl
rem	Dated:		June 1999
rem	Purpose:	Create a product hierarchy table, then normalise it,
rem			for the demonstration of query rewrite.
rem
rem	Notes:
rem	There are currently 40 products (the product code was generated
rem	as mod(rownum,40).  So we will have 5 products to a group to 
rem	give 8 groups and 4 groups to a department to give 2 departments
rem

drop table product_hierarchy;

create table product_hierarchy (
	product		number(6),
	prod_name	varchar2(20),
	product_group	number(6),
	group_name	varchar2(20),
	product_dept	number(6),
	dept_name	varchar2(20)
);

insert into product_hierarchy
select
	rownum,
	'Product_' || rownum,
	mod(rownum,8),
	'Group_' || mod(rownum,8),
	mod(rownum,2),
	'Department_' || mod(rownum,2)
from
	all_objects
where
	rownum <= 40
;


drop table products;
drop table groups;
drop table departments;

create table departments as
	select distinct 
		product_dept, 
		dept_name
	from
		product_hierarchy;

create table groups as
	select distinct
		product_group,
		group_name,
		product_dept
	from
		product_hierarchy;

create table products as
	select distinct
		product,
		prod_name,
		product_group
	from
		product_hierarchy
	;

alter table departments
	add constraint dpt_pk 
	primary key (product_dept);

alter table groups
	add constraint grp_pk 
	primary key (product_group);

alter table groups
	add constraint grp_fk_dpr 
	foreign key (product_dept)
	references departments

alter table products
	add constraint prd_pk
	primary key (product);

alter table products
	add constraint prd_fk_grp
	foreign key (product_group)
	references groups;