As promised , here's the entire script for creating tables: /* Script to drop and then create the tables for the waiver database */ SET ECHO ON; SPOOL C:\SPOOL_FILES\WAIVER.LST; /* Start with the TABLESPACE commands */ ALTER TABLESPACE TABLESPACE_FOR_WAIVERS OFFLINE NORMAL; DROP TABLESPACE TABLESPACE_FOR_WAIVERS INCLUDING CONTENTS; CREATE TABLESPACE TABLESPACE_FOR_WAIVERS DATAFILE 'D:\TABLESPACES\DATA\TABLESPACE_FOR_WAIVERS' SIZE 1000M ONLINE PERMANENT; /* Table creation steps */ /* */ DROP TABLE TEST_TYPE_DESC CASCADE CONSTRAINTS; CREATE TABLE TEST_TYPE_DESC ( TYPE_ID NUMBER(6) PRIMARY KEY, TYPE_DESC VARCHAR2(30) NOT NULL ) TABLESPACE TABLESPACE_FOR_WAIVERS ; /* */ DROP TABLE TEST_ID CASCADE CONSTRAINTS; CREATE TABLE TEST_ID ( TEST_ID NUMBER(6) PRIMARY KEY, TEST_NAME VARCHAR2(40) NOT NULL, DATE_CREATED DATE NOT NULL, AUTHOR VARCHAR2(30) DEFAULT 'WAIVER ADMINISTRATION', COMMENTS VARCHAR2(30), TYPE_ID NUMBER(6) REFERENCES TEST_TYPE_DESC(TYPE_ID), PASSING_GRADE NUMBER(2) NOT NULL, TIME_LIMT NUMBER(4,2) NOT NULL ) TABLESPACE TABLESPACE_FOR_WAIVERS ; /* */ DROP TABLE QUESTIONS_TYPE_DESC CASCADE CONSTRAINTS; CREATE TABLE QUESTIONS_TYPE_DESC ( QUESTION_TYPE NUMBER(6) PRIMARY KEY, QUESTIONS_TYPE_DESC VARCHAR2(50) NOT NULL ) TABLESPACE TABLESPACE_FOR_WAIVERS ; /* */ DROP TABLE QUESTIONS CASCADE CONSTRAINTS; CREATE TABLE QUESTIONS ( QUESTION_ID NUMBER(6) PRIMARY KEY, QUESTION VARCHAR2(500) NOT NULL, CORRECT_ANSWER VARCHAR2(2) NOT NULL, QUESTION_TYPE NUMBER(6) REFERENCES QUESTIONS_TYPE_DESC(QUESTION_TYPE), DATE_CREATED DATE, AUTHOR_ID NUMBER(9) REFERENCES AUTHORS(AUTHOR_ID) ) TABLESPACE TABLESPACE_FOR_WAIVERS ; /* */ DROP TABLE AUTHORS CASCADE CONSTRAINTS; CREATE TABLE AUTHORS ( AUTHOR_ID NUMBER(9) PRIMARY KEY, AUTHOR VARCHAR2(75) DEFAULT 'WAIVER ADMINISTRATION' ) TABLESPACE TABLESPACE_FOR_WAIVERS ; /* */ DROP TABLE QUESTIONS CASCADE CONSTRAINTS; CREATE TABLE QUESTIONS ( QUESTION_ID NUMBER(6) PRIMARY KEY, QUESTION VARCHAR2(500) NOT NULL, CORRECT_ANSWER VARCHAR2(2) NOT NULL, QUESTION_TYPE NUMBER(6) REFERENCES QUESTIONS_TYPE_DESC(QUESTION_TYPE), DATE_CREATED DATE, AUTHOR_ID NUMBER(9) REFERENCES AUTHORS(AUTHOR_ID) ) TABLESPACE TABLESPACE_FOR_WAIVERS ; /* */ DROP TABLE TEST_QUESTIONS_LINK CASCADE CONSTRAINTS; CREATE table TEST_QUESTIONS_LINK ( LINK_TEST_ID NUMBER(6) REFERENCES TEST_ID(TEST_ID), LINK_QUESTION_ID NUMBER(6) REFERENCES QUESTIONS(QUESTION_ID), PRIMARY KEY (LINK_TEST_ID, LINK_QUESTION_ID) ) TABLESPACE TABLESPACE_FOR_WAIVERS ; /* */ DROP TABLE ANSWERS CASCADE CONSTRAINTS; CREATE TABLE ANSWERS ( ANSWER_QUESTION_ID NUMBER(6) PRIMARY KEY REFERENCES QUESTIONS(QUESTION_ID), ANSWER_ID VARCHAR2(2) NOT NULL, ANSWER VARCHAR2(30) NOT NULL ) TABLESPACE TABLESPACE_FOR_WAIVERS ; /* */ DROP TABLE STUDENTS CASCADE CONSTRAINTS; CREATE TABLE STUDENTS ( STUDENT_ID NUMBER(9) PRIMARY KEY, F_NAME VARCHAR2(15) NOT NULL, M_I VARCHAR2(1), L_NAME VARCHAR2(20) NOT NULL, SSNUM NUMBER(9) NOT NULL UNIQUE, B_DATE DATE, SEX VARCHAR2(1) CHECK (SEX IN ('M', 'F', 'm', 'f')), STREET1 VARCHAR2(15), STREET2 VARCHAR2(15), TOWN VARCHAR2(20), STATE VARCHAR2(2), COUNTRY VARCHAR2(15) DEFAULT 'UNITED STATES', ZIP NUMBER(5) NOT NULL, STUDENT_LEVEL NUMBER(6) NOT NULL, DATE_CREATED DATE, CREATED_BY VARCHAR2(20), DEPARTMENT_MAJOR VARCHAR2(10) NOT NULL, EMAIL VARCHAR2(60) ) TABLESPACE TABLESPACE_FOR_WAIVERS ; /* */ DROP TABLE TEST_HISTORY CASCADE CONSTRAINTS; CREATE TABLE TEST_HISTORY ( TEST_ID NUMBER(6) REFERENCES TEST_ID(TEST_ID), STUDENT_ID NUMBER(9) REFERENCES STUDENTS(STUDENT_ID), SCORE NUMBER(3) NOT NULL, DATE_TAKEN DATE NOT NULL, START_TIME VARCHAR2(8) NOT NULL, END_TIME VARCHAR2(8) NOT NULL, LOCATION VARCHAR2(15) NOT NULL, PRIMARY KEY (TEST_ID, STUDENT_ID) ) TABLESPACE TABLESPACE_FOR_WAIVERS ; /* */ DROP TABLE STUDENT_ANSWER_HISTORY CASCADE CONSTRAINTS; CREATE TABLE STUDENT_ANSWER_HISTORY ( STUDENT_ID NUMBER(9) REFERENCES STUDENTS(STUDENT_ID), TEST_ID NUMBER(6) REFERENCES TEST_ID(TEST_ID), QUESTION_ID NUMBER(6) REFERENCES QUESTIONS(QUESTION_ID), STUDENT_ANSWER VARCHAR2(2) NOT NULL ) TABLESPACE TABLESPACE_FOR_WAIVERS ; SPOOL OFF; |