Data Definition Language
set echo on;
drop table accept;
drop table rank;
drop table reject cascade constraints;
drop table ansedit;
drop table answer;
drop table defer cascade constraints;
drop table delinquency cascade constraints;
drop table pass cascade constraints;
drop table quesedit;
drop table quesword;
drop table question;
drop table status;
drop table directory;
drop table expword;
drop table experiment;
drop table glevel;
drop table linkword;
drop table link;
drop table keyword;
drop table interests;
drop table area;
drop table reply;
drop table message;
drop table moderator;
drop table hold;
drop table scientist;
drop table member;
drop table users;
drop table position;
drop table institute;
drop table zip;
drop table country;
/* ************************************************************************* */
CREATE TABLE COUNTRY (
COUNTRY_NO char(3) constraint country_pk primary key,
NAME varchar2(25) constraint country_name_null not null);
/* ************************************************************************* */
CREATE TABLE ZIP (
ZIP_NO char(8) constraint zip_pk primary key,
CITY varchar2(25) constraint zip_city_null not null,
STATE varchar2(25) constraint zip_state_null not null,
ZIP_CODE char(8) constraint zip_code_null not null,
COUNTRY_NO char(3) constraint zip_countryno_null not null
constraint zip_countryno_fk references country);
create index zip_country on zip(country_no);
/* ************************************************************************* */
CREATE TABLE INSTITUTE (
INSTITUTE_NO char(5) constraint institute_pk primary key,
NAME varchar2(40) constraint institute_name_null not null);
/* ************************************************************************* */
CREATE TABLE POSITION (
POSITION_NO char(2) constraint position_pk primary key,
TITLE varchar2(25) constraint position_title_null not null);
/* ************************************************************************* */
CREATE TABLE USERS (
USER_ID char(8) constraint user_pk primary key,
FIRST_NAME varchar2(15),
LAST_NAME varchar2(15) constraint user_lname_null not null,
EMAIL varchar2(30) constraint user_email_null not null,
STREET varchar2(35),
ZIP_NO char(8) constraint user_zipno_fk references zip
constraint user_zipno_null not null,
INSTITUTE_NO char(5) constraint user_institute_fk references institute,
POSITION_NO char(2) constraint user_position_fk references position);
create index users_lname on users(last_name);
create index users_zipno on users(zip_no);
create index users_ins on users(institute_no);
create index users_pos on users(position_no);
/* ************************************************************************* */
CREATE TABLE MEMBER (
MEMBER_ID char(4) constraint member_pk primary key,
FIRST_NAME varchar2(15),
LAST_NAME varchar2(15) constraint member_lname_null not null,
EMAIL varchar2(30) constraint member_email_null not null,
HOMEPAGE varchar2(35),
SELF_INTRO varchar2(100),
PASSWORD char(6) constraint member_password_null not null,
STREET varchar2(35),
ZIP_NO char(8) constraint member_zipno_fk references zip
constraint member_zipno_null not null,
INSTITUTE_NO char(5) constraint member_institute_null not null
constraint member_institute_fk references institute,
POSITION_NO char(2) constraint member_position_null not null
constraint member_position_fk references position);
create index member_lname on member(last_name);
create index member_zipno on member(zip_no);
create index member_ins on member(institute_no);
create index member_pos on member(position_no);
/* ************************************************************************* */
CREATE TABLE SCIENTIST (
MEMBER_ID char(4) constraint scientist_pk primary key
constraint scientist_memid_fk references member);
/* ************************************************************************* */
CREATE TABLE HOLD (
HOLD_NO char(6) constraint hold_pk primary key,
DATE_START date constraint hold_datestart_null not null,
DATE_END date constraint hold_dateend_null not null,
SCI_ID char(4) constraint hold_memid_null not null
constraint hold_memid_fk references scientist);
create index hold_sciid on hold(sci_id);
/* ************************************************************************* */
CREATE TABLE MODERATOR (
MEMBER_ID char(4) constraint moderator_pk primary key
constraint moderator_memid_fk references member);
/* ************************************************************************* */
CREATE TABLE MESSAGE (
MESSAGE_NO char(4) constraint message_pk primary key,
DATE_POST date constraint message_datepost_null not null,
SUBJECT varchar2(300) constraint message_subject_null not null,
CONTENT varchar2(1000) constraint message_content_null not null,
MOD_ID char(4) constraint message_memid_null not null
constraint message_memid_fk references moderator);
create index message_modid on message(mod_id);
/* ************************************************************************* */
CREATE TABLE REPLY (
REPLY_NO char(4) constraint reply_pk primary key,
DATE_POST date constraint reply_datepost_null not null,
CONTENT varchar2(1000) constraint reply_content_null not null,
MESSAGE_NO char(4) constraint reply_mesno_null not null
constraint reply_mesno_fk references message,
MOD_ID char(4) constraint reply_memid_null not null
constraint reply_memid_fk references moderator);
create index reply_mesno on reply(message_no);
create index reply_modid on reply(mod_id);
/* ************************************************************************* */
CREATE TABLE AREA (
AREA_NO char(2) constraint area_pk primary key,
NAME varchar2(40) constraint area_name_null not null,
DESCRIPTION varchar2(50) );
/* ************************************************************************* */
CREATE TABLE INTERESTS (
AREA_NO char(2) constraint interest_areano_fk references area,
MEMBER_ID char(4) constraint interest_memid_fk references member,
constraint interests_pk primary key(AREA_NO,MEMBER_ID));
/* ************************************************************************* */
CREATE TABLE KEYWORD (
KEYWORD_NO char(4) constraint keyword_pk primary key,
WORD varchar2(20) constraint keyword_word_null not null);
/* ************************************************************************* */
CREATE TABLE LINK (
LINK_NO char(3) constraint link_pk primary key,
URL varchar2(150) constraint link_url_null not null,
DESCRIPTION varchar2(300) constraint link_desc_null not null,
AREA_NO char(2) constraint link_areano_null not null
constraint link_areano_fk references area);
create index link_areano on link(area_no);
/* ************************************************************************* */
CREATE TABLE LINKWORD (
LINK_NO char(3) constraint linkword_linkno_fk references link,
KEYWORD_NO char(4) constraint linkword_keyno_fk references keyword,
constraint linkword_pk primary key(LINK_NO,KEYWORD_NO));
/* ************************************************************************* */
CREATE TABLE GLEVEL (
GLEVEL_NO char(2) constraint glevel_pk primary key,
NAME varchar2(20) constraint glevel_name_null not null);
/* ************************************************************************* */
CREATE TABLE EXPERIMENT (
EXP_NO char(4) constraint exp_pk primary key,
OVERVIEW varchar2(500) constraint exp_overview_null not null,
EQUIPMENT varchar2(500) constraint exp_equip_null not null,
PRECAUTION varchar2(500) constraint exp_precaution_null not null,
DESCRIPTION varchar2(1000) constraint exp_desc_null not null,
EDIBILITY char(1) constraint exp_edit_null not null,
ADULT_PRESENCE char(1) constraint exp_pre_null not null,
EXPLAINATION varchar2(500) constraint exp_exp_null not null,
REFERENCE varchar2(500),
COMMENTS varchar2(500) constraint exp_comments_null not null,
DATE_SUBMIT date constraint exp_datesubmit_null not null,
DATE_APPROVE date constraint exp_dateapprove_null not null,
USER_ID char(8) constraint exp_userid_null not null
constraint exp_userid_fk references users,
MOD_ID char(4) constraint exp_memid_null not null
constraint exp_memid_fk references moderator,
AREA_NO char(2) constraint exp_areano_null not null
constraint exp_areano_fk references area,
GLEVEL_NO char(2) constraint exp_levelno_null not null
constraint exp_levelno_fk references glevel);
create index expuser on experiment(user_id);
create index expmod on experiment(mod_id);
create index exparea on experiment(area_no);
create index expglevel on experiment(glevel_no);
/* ************************************************************************* */
CREATE TABLE EXPWORD (
EXP_NO char(4) constraint expword_expno_fk references experiment,
KEYWORD_NO char(4) constraint expword_keyno_fk references keyword,
constraint expword_pk primary key(EXP_NO,KEYWORD_NO));
/* ************************************************************************* */
CREATE TABLE DIRECTORY (
DIR_NO char(2) constraint directory_pk primary key,
DIR_NAME varchar2(20) constraint directory_dirname_null not null);
/* ************************************************************************* */
CREATE TABLE STATUS (
STATUS_NO char(2) constraint status_pk primary key,
DESCRIPTION varchar2(20) constraint status_desc_null not null);
/* ************************************************************************* */
CREATE TABLE QUESTION (
QUESTION_NO char(8) constraint question_pk primary key,
SUBJECT varchar2(150) constraint question_subject_null not null,
CONTENT varchar2(500) constraint question_content_null not null,
DATE_ASK date constraint question_dateask_null not null,
USER_ID char(8) constraint question_userid_null not null
constraint question_userid_fk references users,
GLEVEL_NO char(2) constraint question_levelno_null not null
constraint question_levelno_fk references glevel,
AREA_NO char(2) constraint question_areano_null not null
constraint question_areano_fk references area,
DIR_NO char(2) constraint question_dirno_null not null
constraint question_dirno_fk references directory,
STATUS_NO char(2) constraint question_statusno_null not null
constraint question_statusno_fk references status,
FINAL_ANSWER varchar2(3500));
create index quserid on question(user_id);
create index qglevel on question(glevel_no);
create index qareano on question(area_no);
create index qdirno on question(dir_no);
create index qstatusno on question(status_no);
/* ************************************************************************* */
CREATE TABLE QUESWORD (
QUESTION_NO char(8) constraint quesword_question_fk references question,
KEYWORD_NO char(4) constraint quesword_keyno_fk references keyword,
constraint quesword_pk primary key(QUESTION_NO,KEYWORD_NO));
/* ************************************************************************* */
CREATE TABLE QUESEDIT (
QUESTION_NO char(8) constraint quesedit_question_fk references question,
MEMBER_ID char(4) constraint quesedit_memid_fk references moderator,
DATE_EDIT date constraint quesedit_dateedit_null not null,
CHANGE varchar2(50) constraint quesedit_change_null not null,
constraint quesedit_pk primary key(QUESTION_NO,MEMBER_ID));
/* ************************************************************************* */
CREATE TABLE PASS (
PASS_NO char(5) constraint pass_pk primary key,
DATE_PASS date constraint pass_datepass_null not null,
QUESTION_NO char(8) constraint pass_questionno_null not null
constraint pass_questionno_fk references question,
MOD_ID char(4) constraint pass_modid_null not null
constraint pass_modid_fk references moderator,
SCI_ID char(4) constraint pass_sciid_null not null
constraint pass_sciid_fk references scientist);
create index passmodid on pass(mod_id);
create index passsciid on pass(sci_id);
/* ************************************************************************* */
CREATE TABLE DELINQUENCY (
PASS_NO char(5) constraint deliquency_pk primary key
constraint deliquency_passno_fk references pass,
DATE_DELIQ date constraint deliquency_datedeliq_null not null,
MOD_ID char(4) constraint deliquency_memid_null not null
constraint deliquency_memid_fk references moderator,
NEXT_PASS_NO char(5) constraint deliquency_passno_null1 not null
constraint deliquency_passno_fk1 references pass);
create index delmodid on delinquency(mod_id);
/* ************************************************************************* */
CREATE TABLE DEFER (
PASS_NO char(5) constraint defer_pk primary key
constraint defer_passno_fk references pass,
DATE_DEFER date constraint defer_datedef_null not null,
COMMENTS varchar2(200) constraint defer_comment_null not null,
NEXT_PASS_NO char(5) constraint defer_passno_null1 not null
constraint defer_passno_fk1 references pass);
/* ************************************************************************* */
CREATE TABLE ANSWER (
PASS_NO char(5) constraint answer_pk primary key
constraint answer_passno_fk references pass,
DATE_ANSWER date constraint answer_dateanswer_null not null,
CONTENT varchar2(3500) constraint answer_content_null not null);
/* ************************************************************************* */
CREATE TABLE ANSEDIT (
PASS_NO char(5) constraint ansedit_passno_fk references answer,
MOD_ID char(4) constraint ansedit_memid_fk references moderator,
DATE_EDIT date constraint ansedit_dateedit_null not null,
CHANGE varchar2(50) constraint ansedit_change_null not null,
constraint ansedit_pk primary key(PASS_NO,MOD_ID));
/* ************************************************************************* */
CREATE TABLE REJECT (
PASS_NO char(5) constraint reject_pk primary key
constraint reject_passno_fk references answer,
DATE_REJECT date constraint reject_datereject_null not null,
MOD_ID char(4) constraint reject_memid_null not null
constraint reject_memid_fk references moderator,
NEXT_PASS_NO char(5) constraint reject_passno_null1 not null
constraint reject_passno_fk1 references pass);
create index rejectmodid on reject(mod_id);
/* ************************************************************************* */
CREATE TABLE RANK (
RANK_NO char(1) constraint rank_pk primary key,
DESCRIPTION varchar2(30) constraint rank_desc_null not null);
/* ************************************************************************* */
CREATE TABLE ACCEPT (
PASS_NO char(5) constraint accept_pk primary key
constraint accept_passno_fk references answer,
DATE_ACCEPT date constraint accept_dateaccept_null not null,
MOD_ID char(4) constraint accept_memid_null not null
constraint accept_memid_fk references moderator,
RANK_NO char(1) constraint accept_rankno_fk references rank);
create index acceptmodid on accept(mod_id);
create index acceptrankno on accept(rank_no);
/* ************************************************************************* */