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

CASE#2

CREATE TABLE term
( term_id   number(6),
  term_desc varchar2(20),
  status    varchar2(6),
  CONSTRAINT term_term_id_pk PRIMARY KEY(term_id),
  CONSTRAINT term_status_cc CHECK ( (status='open') OR (status='close') ) );
  
CREATE TABLE course
( course_id   number(6),
  call_id     varchar2(6),
  course_name varchar2(20),
  credits     number(2),
  CONSTRAINT  course_course_id_pk PRIMARY KEY (course_id) );
  
CREATE TABLE course_section
( c_sec_id		number(6),
  course_id		number(6),
  term_id		number(6),
  sec_num		number(6),
  f_id			number(6),
  c_sec_day		varchar2(10),
  c_sec_time	date,
  c_sec_duration	interval day to second,
  loc_id		number(6),
  max_enrl		number(6),
  CONSTRAINT	course_section_c_sec_id_pk	PRIMARY KEY(c_sec_id),
  CONSTRAINT	course_section_c_sec_id_fk	FOREIGN KEY(course_id)	REFERENCES	course(course_id),
  CONSTRAINT	course_section_term_id_fk	FOREIGN KEY(term_id)	REFERENCES	term(term_id),
  CONSTRAINT	course_section_f_id_fk		FOREIGN KEY(f_id)		REFERENCES	faculty(f_id),
  CONSTRAINT	course_section_loc_id_fk	FOREIGN KEY(loc_id)		REFERENCES	location(loc_id));
  
CREATE TABLE enrollment
( s_id			number(6),
  c_sec_id		number(6),
  grade			varchar2(1),
  CONSTRAINT	enrollment_s_id_pk	PRIMARY KEY(s_id),
  CONSTRAINT	enrollment_s_id_fk	FOREIGN KEY(s_id)	REFERENCES	students(s_id),
  CONSTRAINT	enrollment_c_sec_id_fk	FOREIGN KEY(c_sec_id)	REFERENCES	course_section(c_sec_id),
  CONSTRAINT	enrollment_grade_cc	CHECK ( (grade='A')OR(grade='B')OR(grade='C')OR(grade='D')OR(grade='F')));