View of /trunk/NationalProblemLibrary/create_tables2.sql

    1 ##############################################################################
    2 # W.K. Ziemer, 2004 wziemer@csulb.edu
    3 ##############################################################################
    4 
    5 
    6 # DBsubject is the basic classification
    7 # for problems, the subject area
    8 #
    9 DROP TABLE IF EXISTS DBsubject;
   10 CREATE TABLE DBsubject
   11 (
   12   DBsubject_id int(15) NOT NULL auto_increment,
   13   name varchar(127) NOT NULL,
   14   KEY DBsubject (name),
   15   PRIMARY KEY (DBsubject_id)
   16 );
   17 
   18 # DBchapter is a more refined classification
   19 # for problem types
   20 #
   21 DROP TABLE IF EXISTS DBchapter;
   22 CREATE TABLE DBchapter
   23 (
   24   DBchapter_id int(15) NOT NULL auto_increment,
   25   name varchar(127) NOT NULL,
   26   DBsubject_id int(15) DEFAULT 0 NOT NULL,
   27   KEY DBchapter (name),
   28   KEY (DBsubject_id),
   29   PRIMARY KEY (DBchapter_id)
   30 );
   31 
   32 # DBsection is the finest classification
   33 # for problem types
   34 #
   35 DROP TABLE IF EXISTS DBsection;
   36 CREATE TABLE DBsection
   37 (
   38   DBsection_id int(15) NOT NULL auto_increment,
   39   name varchar(255) NOT NULL,
   40   DBchapter_id int(15) DEFAULT 0 NOT NULL,
   41   KEY DBsection (name),
   42   KEY (DBchapter_id),
   43   PRIMARY KEY (DBsection_id)
   44 );
   45 
   46 # institution table contains all places using webwork
   47 #
   48 DROP TABLE IF EXISTS institution;
   49 CREATE TABLE institution
   50 (
   51   institution_id int (15) NOT NULL auto_increment,
   52   name varchar (255) NOT NULL,
   53   department varchar (255),
   54   address varchar (255),
   55   city varchar (255),
   56   state char(2),
   57   zipcode char(10),
   58   website varchar (255),
   59   KEY institution (name),
   60   PRIMARY KEY (institution_id)
   61 );
   62 
   63 # author table contains all problem authors
   64 #
   65 DROP TABLE IF EXISTS author;
   66 CREATE TABLE author
   67 (
   68   author_id int (15) NOT NULL auto_increment,
   69   institution_id int (15) NOT NULL,
   70   lastname varchar (100) NOT NULL,
   71   firstname varchar (100) NOT NULL,
   72   email varchar (255),
   73   KEY author (lastname, firstname),
   74   PRIMARY KEY (author_id)
   75 );
   76 
   77 # path table contains relative path, machine, and user ownership
   78 #
   79 DROP TABLE IF EXISTS path;
   80 CREATE TABLE path
   81 (
   82   path_id int(15) NOT NULL auto_increment,
   83   path varchar(127) NOT NULL,
   84   machine varchar(127),
   85   user varchar(127),
   86   KEY (path),
   87   PRIMARY KEY (path_id)
   88 );
   89 
   90 # pgfile table contains classification, location, and revision history about the .pg file
   91 #
   92 DROP TABLE IF EXISTS pgfile;
   93 CREATE TABLE pgfile
   94 (
   95   pgfile_id int(15) NOT NULL auto_increment,
   96   DBsection_id int(15) NOT NULL,
   97   author_id int(15),
   98   institution_id int(15),
   99   path_id int(15) NOT NULL,
  100   filename varchar(255) NOT NULL,
  101   history blob,
  102   PRIMARY KEY (pgfile_id)
  103 );
  104 
  105 # keywords for problems
  106 #
  107 DROP TABLE IF EXISTS keyword;
  108 CREATE TABLE keyword
  109 (
  110   keyword_id int(15) NOT NULL auto_increment,
  111   keyword varchar(65) NOT NULL,
  112   KEY (keyword),
  113   PRIMARY KEY (keyword_id)
  114 );
  115 
  116 # pgfile_keyword associates prolems with keywords
  117 #
  118 DROP TABLE IF EXISTS pgfile_keyword;
  119 CREATE TABLE pgfile_keyword
  120 (
  121   pgfile_id int(15) DEFAULT 0 NOT NULL,
  122   keyword_id int(15) DEFAULT 0 NOT NULL,
  123   KEY pgfile_keyword (keyword_id, pgfile_id),
  124   KEY pgfile (pgfile_id)
  125 );
  126 
  127 # pgfile_institution associates problems with institutions
  128 #
  129 DROP TABLE IF EXISTS pgfile_institution;
  130 CREATE TABLE pgfile_institution
  131 (
  132   pgfile_id int(15) DEFAULT 0 NOT NULL,
  133   institution_id int(15) DEFAULT 0 NOT NULL,
  134   PRIMARY KEY (institution_id, pgfile_id)
  135 );
  136 
  137 # textbook table contains textbook info
  138 #
  139 DROP TABLE IF EXISTS textbook;
  140 CREATE TABLE textbook
  141 (
  142   textbook_id int (15) NOT NULL auto_increment,
  143   title varchar (255) NOT NULL,
  144   edition int (3) DEFAULT 0 NOT NULL,
  145   author varchar (63) NOT NULL,
  146   publisher varchar (127),
  147   isbn char (15),
  148   pubdate varchar (27),
  149   PRIMARY KEY (textbook_id)
  150 );
  151 
  152 # weak table chapter
  153 # chapters from a textbook
  154 #
  155 DROP TABLE IF EXISTS chapter;
  156 CREATE TABLE chapter
  157 (
  158   chapter_id int (15) NOT NULL auto_increment,
  159   textbook_id int (15),
  160   number int(3),
  161   name varchar(127) NOT NULL,
  162   page int(4),
  163   PRIMARY KEY (chapter_id)
  164 
  165 );
  166 
  167 # weak table section
  168 # sections from a textbook chapter
  169 #
  170 DROP TABLE IF EXISTS section;
  171 CREATE TABLE section
  172 (
  173   section_id int(15) NOT NULL auto_increment,
  174   chapter_id int (15),
  175   number int(3),
  176   name varchar(127) NOT NULL,
  177   page int(4),
  178   PRIMARY KEY section (section_id)
  179 );
  180 
  181 # problem
  182 # problems from a textbook
  183 #
  184 DROP TABLE IF EXISTS problem;
  185 CREATE TABLE problem
  186 (
  187   problem_id int(15) NOT NULL auto_increment,
  188   section_id int(15),
  189   number int(4) NOT NULL,
  190   page int(4),
  191   #KEY (page, number),
  192   KEY (section_id),
  193   PRIMARY KEY (problem_id)
  194 
  195 );
  196 
  197 # pgfile_problem table
  198 # associates pgfiles to problems from a textbook.
  199 #
  200 DROP TABLE IF EXISTS pgfile_problem;
  201 CREATE TABLE pgfile_problem
  202 (
  203   pgfile_id int(15) DEFAULT 0 NOT NULL,
  204   problem_id int(15) DEFAULT 0 NOT NULL,
  205   PRIMARY KEY (pgfile_id, problem_id)
  206 );
  207 
  208 ##############################################################################
  209 # end of create_tables.sql
  210 ##############################################################################
  211