Parent Directory
| 
Revision Log
Revision 8 - (view) (download)
| 1 : | gage | 2 | ############################################################################## |
| 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 : | jj | 8 | DBsubject_id int(15) DEFAULT 0 NOT NULL auto_increment, |
| 13 : | gage | 2 | 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 : | jj | 8 | DBchapter_id int(15) DEFAULT 0 NOT NULL auto_increment, |
| 25 : | gage | 2 | name varchar(127) NOT NULL, |
| 26 : | jj | 8 | DBsubject_id int(15) DEFAULT 0 NOT NULL, |
| 27 : | gage | 2 | 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 : | jj | 8 | DBsection_id int(15) DEFAULT 0 NOT NULL auto_increment, |
| 39 : | gage | 2 | name varchar(255) NOT NULL, |
| 40 : | jj | 8 | DBchapter_id int(15) DEFAULT 0 NOT NULL, |
| 41 : | gage | 2 | 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 : | jj | 8 | institution_id int (15) DEFAULT 0 NOT NULL auto_increment, |
| 52 : | gage | 2 | 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 : | jj | 8 | author_id int (15) DEFAULT 0 NOT NULL auto_increment, |
| 69 : | gage | 2 | 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 : | jj | 8 | path_id int(15) DEFAULT 0 NOT NULL auto_increment, |
| 83 : | gage | 2 | 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 : | jj | 8 | pgfile_id int(15) DEFAULT 0 NOT NULL auto_increment, |
| 96 : | gage | 2 | 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 : | jj | 8 | keyword_id int(15) DEFAULT 0 NOT NULL auto_increment, |
| 111 : | gage | 2 | 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 : | jj | 8 | pgfile_id int(15) DEFAULT 0 NOT NULL, |
| 122 : | keyword_id int(15) DEFAULT 0 NOT NULL, | ||
| 123 : | gage | 2 | 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 : | jj | 8 | pgfile_id int(15) DEFAULT 0 NOT NULL, |
| 133 : | institution_id int(15) DEFAULT 0 NOT NULL, | ||
| 134 : | gage | 2 | 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 : | jj | 8 | textbook_id int (15) DEFAULT 0 NOT NULL auto_increment, |
| 143 : | gage | 2 | title varchar (255) NOT NULL, |
| 144 : | jj | 8 | edition int (3) DEFAULT 0 NOT NULL, |
| 145 : | gage | 2 | 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 : | jj | 8 | chapter_id int (15) DEFAULT 0 NOT NULL auto_increment, |
| 159 : | gage | 2 | 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 : | jj | 8 | section_id int(15) DEFAULT 0 NOT NULL auto_increment, |
| 174 : | gage | 2 | 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 : | jj | 8 | problem_id int(15) DEFAULT 0 NOT NULL auto_increment, |
| 188 : | gage | 2 | 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 : | jj | 8 | pgfile_id int(15) DEFAULT 0 NOT NULL, |
| 204 : | problem_id int(15) DEFAULT 0 NOT NULL, | ||
| 205 : | gage | 2 | PRIMARY KEY (pgfile_id, problem_id) |
| 206 : | ); | ||
| 207 : | |||
| 208 : | ############################################################################## | ||
| 209 : | # end of create_tables.sql | ||
| 210 : | ############################################################################## |
| aubreyja at gmail dot com | ViewVC Help |
| Powered by ViewVC 1.0.9 |