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 `NPL-DBsubject`;
   10 CREATE TABLE `NPL-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 `NPL-DBchapter`;
   22 CREATE TABLE `NPL-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 `NPL-DBsection`;
   36 CREATE TABLE `NPL-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 `NPL-institution`;
   49 CREATE TABLE `NPL-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 `NPL-author`;
   66 CREATE TABLE `NPL-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 `NPL-path`;
   80 CREATE TABLE `NPL-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 `NPL-pgfile`;
   93 CREATE TABLE `NPL-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 `NPL-keyword`;
  108 CREATE TABLE `NPL-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 `NPL-pgfile-keyword`;
  119 CREATE TABLE `NPL-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 `NPL-pgfile-institution`;
  130 CREATE TABLE `NPL-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 `NPL-textbook`;
  140 CREATE TABLE `NPL-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 `NPL-chapter`;
  156 CREATE TABLE `NPL-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 `NPL-section`;
  171 CREATE TABLE `NPL-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 `NPL-problem`;
  185 CREATE TABLE `NPL-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 `NPL-pgfile-problem`;
  201 CREATE TABLE `NPL-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 ##############################################################################