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

CASE#1

DROP TABLE location;
CREATE TABLE location
( loc_id    number(6),
  bldg_code varchar2(3),
  room      varchar2(3),
  capacity  number(6),
  CONSTRAINT location_loc_id_pk PRIMARY KEY(loc_id) );



DROP TABLE faculty;
CREATE TABLE faculty
( f_id      number(6),
  f_last    varchar2(10),
  f_first   varchar2(10),
  f_mi      varchar2(1),
  loc_id    number(6),
  f_phone   varchar2(10),
  f_rank    varchar2(4),
  f_pin     varchar2(4),
  f_image   blob,
  CONSTRAINT faculty_f_id_pk PRIMARY KEY (f_id),
  CONSTRAINT FACULTY_f_id_fk FOREIGN KEY (loc_id) REFERENCES location(loc_id) );


DROP TABLE students;
CREATE TABLE students
( s_id      number(6),
  s_last    varchar2(10),
  s_first   varchar2(10),
  s_mi      varchar2(1),
  s_address varchar2(30),
  s_city    varchar2(30),
  s_state   varchar2(2),
  s_zip     varchar2(5),
  s_phone   varchar2(10),
  s_class   varchar2(2),
  s_dob     date,
  s_pin     varchar2(4),
  f_id      number(6),
  time_enroll interval year(3) to month,
  CONSTRAINT students_s_id_pk PRIMARY KEY (s_id),
  CONSTRAINT students_s_class_cc CHECK ( (s_class='FR')OR(s_class='SO')OR(s_class='JR')OR(s_class='SR') ), 
  CONSTRAINT students_f_id_fk FOREIGN KEY (f_id) REFERENCES faculty(f_id) );