Andre Long
CISP 350 Oracle
Febuary 28, 2006
Chapter 2 PSC 

CASE#6

CREATE TABLE client
( client_id		number(6),
  client_name	varchar2(40),
  contact_last	varchar2(30),
  contact_first	varchar2(30),
  contact_phone	varchar2(10),
  CONSTRAINT	client_client_id_pk	PRIMARY KEY(client_id));
  
CREATE TABLE project
( p_id			number(6),
  project_name	varchar2(50),
  client_id		number(6),
  mgr_id		number(6),
  parent_p_id	number(6),
  CONSTRAINT	project_p_id_pk	PRIMARY KEY(p_id),
  CONSTRAINT	project_client_id_fk	FOREIGN KEY(client_id)	REFERENCES client(client_id),
  CONSTRAINT	project_parent_id_fk	FOREIGN KEY(parent_p_id)REFERENCES project(p_id));
  
CREATE TABLE project_skill
( p_id			number(6),
  skill_id		number(6),
  CONSTRAINT	project_skill_p_id_fk	FOREIGN KEY(p_id)REFERENCES project(p_id),
  CONSTRAINT	project_skill_skill_id_fk	FOREIGN KEY(skill_id)REFERENCES skill(skill_id));
  
CREATE TABLE project_consultant
( p_id			number(6),
  c_id			number(6),
  roll_on_date	date,
  roll_off_date	date,
  elapsed_time	interval year to month,
  CONSTRAINT	project_consultant_p_id_fk	FOREIGN KEY(p_id)REFERENCES project(p_id),
  CONSTRAINT	project_consultant_c_id_fk	FOREIGN KEY(c_id)REFERENCES consultant(c_id));
  
CREATE TABLE evaluation
( e_id			number(6),
  e_date		date,
  p_id			number(6),
  evaluator_id	number(6),
  evaluatee_id	number(6),
  score			number(6),
  comments		clob,
  CONSTRAINT	evaluation_e_id_pk	PRIMARY KEY(e_id),
  CONSTRAINT	evaluation_p_id_fk	FOREIGN KEY(p_id)	REFERENCES project(p_id),
  CONSTRAINT	evaluation_score_cc	CHECK ( (score>=0) AND (score<=100) ));