-- Vladimir Vacic <vladimir@vacic.org>
-- Center for Information Science and Technology
-- Temple University, Philadelphia, PA
-- Feb-21-2004


--
-- This script creates the STUDENTS example database.
--
-- Make sure tables you are trying to create are not
-- already in the database. One way to accomplish this
-- is to run the students_drop script. Otherwise Oracle 
-- will report an error.
--

--
-- departments
--

CREATE TABLE departments
(
    code    VARCHAR2(8) PRIMARY KEY,
    name    VARCHAR2(40) NOT NULL,
    college VARCHAR2(8) NOT NULL
);


--
-- students
--

CREATE TABLE students
(
    ssn         CHAR(10)     PRIMARY KEY,
    first_name  VARCHAR2(16) NOT NULL,
    middle_name VARCHAR2(8),
    last_name   VARCHAR2(16) NOT NULL,
    major       VARCHAR2(8) REFERENCES departments    
);


--
-- professors
--

CREATE TABLE professors
(
    ssn         CHAR(10)     PRIMARY KEY,
    first_name  VARCHAR2(16) NOT NULL,
    middle_name VARCHAR2(8),
    last_name   VARCHAR2(16) NOT NULL,
    position    VARCHAR2(32),
    department  VARCHAR2(8)  REFERENCES departments
);


--
-- classes
--

CREATE TABLE classes
(
    class_code VARCHAR2(8)  PRIMARY KEY,
    name       VARCHAR2(40) NOT NULL,
    credits    NUMBER,
    department VARCHAR2(8)  REFERENCES departments
);


--
-- students_classes (i.e. 'takes' as in 'a student takes a class')
--

CREATE TABLE students_classes
(
    student  CHAR(10)     NOT NULL REFERENCES students,
    class    VARCHAR2(8) NOT NULL REFERENCES classes,
    semester VARCHAR2(10) NOT NULL, 
    grade    NUMBER,
    PRIMARY KEY (student, class, semester)
);


--
-- professors_classes (i.e. teaches as in 'a professor teaches a class')
--

CREATE TABLE professors_classes
(
    professor CHAR(10)     NOT NULL REFERENCES professors,
    class     VARCHAR2(8) NOT NULL REFERENCES classes,
    semester  VARCHAR2(10) NOT NULL,
    PRIMARY KEY (professor, class, semester)
);