rem 
rem $Header: mktables.sql,v 1.2 1994/04/22 05:42:22 cracicot Exp $ 
rem 
Rem  Copyright (c) 1993 by Oracle Corporation 
Rem    NAME
Rem      mktables.sql - <one-line expansion of the name>
Rem    DESCRIPTION
Rem      <short description of component this file declares/defines>
Rem    RETURNS
Rem 
Rem    NOTES
Rem      <other useful comments, qualifications, etc.>
Rem    MODIFIED   (MM/DD/YY)
Rem     tssmith    02/15/94 -  Fixing constraint bug in a table dec.
Rem     tssmith    08/11/93 -  Creation 
REM Create all tables for the sample college database application.

REM Drop existing tables
REM Remove REMs next 6 lines when running under SQL*Plus

REM CLEAR SCREEN
REM Prompt WARNING!! About to recreate the SQL*Module example tables.
REM Prompt All previously entered data will be lost.
REM Prompt If you really want to do this, type ENTER or Return.
REM Pause  Else, type your CANCEL (INTR) character to exit this script now.

REM Prompt Dropping tables...

DROP TABLE students     CASCADE CONSTRAINTS;
DROP TABLE instructors  CASCADE CONSTRAINTS;
DROP TABLE courses      CASCADE CONSTRAINTS;
DROP TABLE classes      CASCADE CONSTRAINTS;
DROP TABLE enrollment   CASCADE CONSTRAINTS;
DROP TABLE departments  CASCADE CONSTRAINTS;

DROP SEQUENCE student_id_seq;
DROP SEQUENCE instructor_id_seq;
DROP SEQUENCE class_number_seq;
DROP SEQUENCE enrollment_seq;

CREATE SEQUENCE student_id_seq          START WITH 1000;
CREATE SEQUENCE instructor_id_seq       START WITH 100000;
CREATE SEQUENCE class_number_seq        START WITH 100;
CREATE SEQUENCE enrollment_seq          START WITH 100;

REM Prompt Creating tables...

CREATE TABLE departments  (name            VARCHAR2(16) NOT NULL,
                           id              NUMBER(6) PRIMARY KEY,
                           location        NUMBER(4),
                           chairperson     NUMBER(6),
                           budget          NUMBER(9,2)
                           );

CREATE TABLE instructors  (last_name       VARCHAR2(15) NOT NULL,
                           first_name      VARCHAR2(15) NOT NULL,
                           mi              VARCHAR2(3),
                           id              NUMBER(6) PRIMARY KEY,
                           hire_date       DATE,
                           dept            NUMBER(6)
                                NOT NULL REFERENCES departments(id),
                           salary          NUMBER(9,2),
                           rank            VARCHAR2(20)
                           );

CREATE TABLE students     (last_name       VARCHAR2(15) NOT NULL,
                           first_name      VARCHAR2(15) NOT NULL,
                           mi              VARCHAR2(3),
	                   id              NUMBER(6) PRIMARY KEY,
	                   status          VARCHAR2(5) NOT NULL,
                           date_of_birth   DATE,
                           matric_date     DATE,
                           grad_date       DATE,
                           major           NUMBER(6)
                                REFERENCES departments(id),
                           advisor_id      NUMBER(6)
                                REFERENCES instructors(id)
                           );

CREATE TABLE courses      (dept            NUMBER(6)
                                NOT NULL REFERENCES departments(id),
                           id              NUMBER(6),
                           name            VARCHAR2(38) NOT NULL
                           );

CREATE TABLE classes      (class_number    NUMBER(6) PRIMARY KEY,
                           course_number   NUMBER(6) NOT NULL,
                           dept            NUMBER(6) NOT NULL,
                           max_enrollment  NUMBER(4) NOT NULL,
                           building_number NUMBER(4),
                           room_number     NUMBER(5),
                           instructor      NUMBER(6),
                           quarter         NUMBER(1),
                           year            NUMBER(4)
                           );

CREATE TABLE enrollment   (e_sn            NUMBER(6) PRIMARY KEY,
                           class_no        NUMBER(6) NOT NULL,
                           student_id      NUMBER(6) NOT NULL,
                           grade           NUMBER(3,2),
                           comments        VARCHAR2(255)
                           );


REM Prompt INSERTing sample data in tables...

@departmt.sql
@instrucs.sql
@students.sql
@courses.sql
@enrolmnt.sql


