[npl] / trunk / NationalProblemLibrary / create_tables2.sql Repository:
ViewVC logotype

Diff of /trunk/NationalProblemLibrary/create_tables2.sql

Parent Directory Parent Directory | Revision Log Revision Log | View Patch Patch

Revision 8 Revision 214
… …
7# for problems, the subject area 7# for problems, the subject area
8# 8#
9DROP TABLE IF EXISTS DBsubject; 9DROP TABLE IF EXISTS DBsubject;
10CREATE TABLE DBsubject 10CREATE TABLE DBsubject
11( 11(
12 DBsubject_id int(15) DEFAULT 0 NOT NULL auto_increment, 12 DBsubject_id int(15) NOT NULL auto_increment,
13 name varchar(127) NOT NULL, 13 name varchar(127) NOT NULL,
14 KEY DBsubject (name), 14 KEY DBsubject (name),
15 PRIMARY KEY (DBsubject_id) 15 PRIMARY KEY (DBsubject_id)
16); 16);
17 17
… …
19# for problem types 19# for problem types
20# 20#
21DROP TABLE IF EXISTS DBchapter; 21DROP TABLE IF EXISTS DBchapter;
22CREATE TABLE DBchapter 22CREATE TABLE DBchapter
23( 23(
24 DBchapter_id int(15) DEFAULT 0 NOT NULL auto_increment, 24 DBchapter_id int(15) NOT NULL auto_increment,
25 name varchar(127) NOT NULL, 25 name varchar(127) NOT NULL,
26 DBsubject_id int(15) DEFAULT 0 NOT NULL, 26 DBsubject_id int(15) DEFAULT 0 NOT NULL,
27 KEY DBchapter (name), 27 KEY DBchapter (name),
28 KEY (DBsubject_id), 28 KEY (DBsubject_id),
29 PRIMARY KEY (DBchapter_id) 29 PRIMARY KEY (DBchapter_id)
… …
33# for problem types 33# for problem types
34# 34#
35DROP TABLE IF EXISTS DBsection; 35DROP TABLE IF EXISTS DBsection;
36CREATE TABLE DBsection 36CREATE TABLE DBsection
37( 37(
38 DBsection_id int(15) DEFAULT 0 NOT NULL auto_increment, 38 DBsection_id int(15) NOT NULL auto_increment,
39 name varchar(255) NOT NULL, 39 name varchar(255) NOT NULL,
40 DBchapter_id int(15) DEFAULT 0 NOT NULL, 40 DBchapter_id int(15) DEFAULT 0 NOT NULL,
41 KEY DBsection (name), 41 KEY DBsection (name),
42 KEY (DBchapter_id), 42 KEY (DBchapter_id),
43 PRIMARY KEY (DBsection_id) 43 PRIMARY KEY (DBsection_id)
… …
46# institution table contains all places using webwork 46# institution table contains all places using webwork
47# 47#
48DROP TABLE IF EXISTS institution; 48DROP TABLE IF EXISTS institution;
49CREATE TABLE institution 49CREATE TABLE institution
50( 50(
51 institution_id int (15) DEFAULT 0 NOT NULL auto_increment, 51 institution_id int (15) NOT NULL auto_increment,
52 name varchar (255) NOT NULL, 52 name varchar (255) NOT NULL,
53 department varchar (255), 53 department varchar (255),
54 address varchar (255), 54 address varchar (255),
55 city varchar (255), 55 city varchar (255),
56 state char(2), 56 state char(2),
… …
63# author table contains all problem authors 63# author table contains all problem authors
64# 64#
65DROP TABLE IF EXISTS author; 65DROP TABLE IF EXISTS author;
66CREATE TABLE author 66CREATE TABLE author
67( 67(
68 author_id int (15) DEFAULT 0 NOT NULL auto_increment, 68 author_id int (15) NOT NULL auto_increment,
69 institution_id int (15) NOT NULL, 69 institution_id int (15) NOT NULL,
70 lastname varchar (100) NOT NULL, 70 lastname varchar (100) NOT NULL,
71 firstname varchar (100) NOT NULL, 71 firstname varchar (100) NOT NULL,
72 email varchar (255), 72 email varchar (255),
73 KEY author (lastname, firstname), 73 KEY author (lastname, firstname),
… …
77# path table contains relative path, machine, and user ownership 77# path table contains relative path, machine, and user ownership
78# 78#
79DROP TABLE IF EXISTS path; 79DROP TABLE IF EXISTS path;
80CREATE TABLE path 80CREATE TABLE path
81( 81(
82 path_id int(15) DEFAULT 0 NOT NULL auto_increment, 82 path_id int(15) NOT NULL auto_increment,
83 path varchar(127) NOT NULL, 83 path varchar(127) NOT NULL,
84 machine varchar(127), 84 machine varchar(127),
85 user varchar(127), 85 user varchar(127),
86 KEY (path), 86 KEY (path),
87 PRIMARY KEY (path_id) 87 PRIMARY KEY (path_id)
… …
90# pgfile table contains classification, location, and revision history about the .pg file 90# pgfile table contains classification, location, and revision history about the .pg file
91# 91#
92DROP TABLE IF EXISTS pgfile; 92DROP TABLE IF EXISTS pgfile;
93CREATE TABLE pgfile 93CREATE TABLE pgfile
94( 94(
95 pgfile_id int(15) DEFAULT 0 NOT NULL auto_increment, 95 pgfile_id int(15) NOT NULL auto_increment,
96 DBsection_id int(15) NOT NULL, 96 DBsection_id int(15) NOT NULL,
97 author_id int(15), 97 author_id int(15),
98 institution_id int(15), 98 institution_id int(15),
99 path_id int(15) NOT NULL, 99 path_id int(15) NOT NULL,
100 filename varchar(255) NOT NULL, 100 filename varchar(255) NOT NULL,
… …
105# keywords for problems 105# keywords for problems
106# 106#
107DROP TABLE IF EXISTS keyword; 107DROP TABLE IF EXISTS keyword;
108CREATE TABLE keyword 108CREATE TABLE keyword
109( 109(
110 keyword_id int(15) DEFAULT 0 NOT NULL auto_increment, 110 keyword_id int(15) NOT NULL auto_increment,
111 keyword varchar(65) NOT NULL, 111 keyword varchar(65) NOT NULL,
112 KEY (keyword), 112 KEY (keyword),
113 PRIMARY KEY (keyword_id) 113 PRIMARY KEY (keyword_id)
114); 114);
115 115
… …
137# textbook table contains textbook info 137# textbook table contains textbook info
138# 138#
139DROP TABLE IF EXISTS textbook; 139DROP TABLE IF EXISTS textbook;
140CREATE TABLE textbook 140CREATE TABLE textbook
141( 141(
142 textbook_id int (15) DEFAULT 0 NOT NULL auto_increment, 142 textbook_id int (15) NOT NULL auto_increment,
143 title varchar (255) NOT NULL, 143 title varchar (255) NOT NULL,
144 edition int (3) DEFAULT 0 NOT NULL, 144 edition int (3) DEFAULT 0 NOT NULL,
145 author varchar (63) NOT NULL, 145 author varchar (63) NOT NULL,
146 publisher varchar (127), 146 publisher varchar (127),
147 isbn char (15), 147 isbn char (15),
… …
153# chapters from a textbook 153# chapters from a textbook
154# 154#
155DROP TABLE IF EXISTS chapter; 155DROP TABLE IF EXISTS chapter;
156CREATE TABLE chapter 156CREATE TABLE chapter
157( 157(
158 chapter_id int (15) DEFAULT 0 NOT NULL auto_increment, 158 chapter_id int (15) NOT NULL auto_increment,
159 textbook_id int (15), 159 textbook_id int (15),
160 number int(3), 160 number int(3),
161 name varchar(127) NOT NULL, 161 name varchar(127) NOT NULL,
162 page int(4), 162 page int(4),
163 PRIMARY KEY (chapter_id) 163 PRIMARY KEY (chapter_id)
… …
168# sections from a textbook chapter 168# sections from a textbook chapter
169# 169#
170DROP TABLE IF EXISTS section; 170DROP TABLE IF EXISTS section;
171CREATE TABLE section 171CREATE TABLE section
172( 172(
173 section_id int(15) DEFAULT 0 NOT NULL auto_increment, 173 section_id int(15) NOT NULL auto_increment,
174 chapter_id int (15), 174 chapter_id int (15),
175 number int(3), 175 number int(3),
176 name varchar(127) NOT NULL, 176 name varchar(127) NOT NULL,
177 page int(4), 177 page int(4),
178 PRIMARY KEY section (section_id) 178 PRIMARY KEY section (section_id)
… …
182# problems from a textbook 182# problems from a textbook
183# 183#
184DROP TABLE IF EXISTS problem; 184DROP TABLE IF EXISTS problem;
185CREATE TABLE problem 185CREATE TABLE problem
186( 186(
187 problem_id int(15) DEFAULT 0 NOT NULL auto_increment, 187 problem_id int(15) NOT NULL auto_increment,
188 section_id int(15), 188 section_id int(15),
189 number int(4) NOT NULL, 189 number int(4) NOT NULL,
190 page int(4), 190 page int(4),
191 #KEY (page, number), 191 #KEY (page, number),
192 KEY (section_id), 192 KEY (section_id),
Legend:
Removed from v.8  
changed lines
  Added in v.214
aubreyja at gmail dot com
 ViewVC Help
Powered by ViewVC 1.0.9