[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 214 Revision 501
… …
4 4
5 5
6# DBsubject is the basic classification 6# DBsubject is the basic classification
7# for problems, the subject area 7# for problems, the subject area
8# 8#
9DROP TABLE IF EXISTS DBsubject; 9DROP TABLE IF EXISTS `NPL-DBsubject`;
10CREATE TABLE DBsubject 10CREATE TABLE `NPL-DBsubject`
11( 11(
12 DBsubject_id int(15) 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
18# DBchapter is a more refined classification 18# DBchapter is a more refined classification
19# for problem types 19# for problem types
20# 20#
21DROP TABLE IF EXISTS DBchapter; 21DROP TABLE IF EXISTS `NPL-DBchapter`;
22CREATE TABLE DBchapter 22CREATE TABLE `NPL-DBchapter`
23( 23(
24 DBchapter_id int(15) 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),
… …
30); 30);
31 31
32# DBsection is the finest classification 32# DBsection is the finest classification
33# for problem types 33# for problem types
34# 34#
35DROP TABLE IF EXISTS DBsection; 35DROP TABLE IF EXISTS `NPL-DBsection`;
36CREATE TABLE DBsection 36CREATE TABLE `NPL-DBsection`
37( 37(
38 DBsection_id int(15) 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),
… …
43 PRIMARY KEY (DBsection_id) 43 PRIMARY KEY (DBsection_id)
44); 44);
45 45
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 `NPL-institution`;
49CREATE TABLE institution 49CREATE TABLE `NPL-institution`
50( 50(
51 institution_id int (15) 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),
… …
60 PRIMARY KEY (institution_id) 60 PRIMARY KEY (institution_id)
61); 61);
62 62
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 `NPL-author`;
66CREATE TABLE author 66CREATE TABLE `NPL-author`
67( 67(
68 author_id int (15) 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,
… …
74 PRIMARY KEY (author_id) 74 PRIMARY KEY (author_id)
75); 75);
76 76
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 `NPL-path`;
80CREATE TABLE path 80CREATE TABLE `NPL-path`
81( 81(
82 path_id int(15) 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),
… …
87 PRIMARY KEY (path_id) 87 PRIMARY KEY (path_id)
88); 88);
89 89
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 `NPL-pgfile`;
93CREATE TABLE pgfile 93CREATE TABLE `NPL-pgfile`
94( 94(
95 pgfile_id int(15) 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),
… …
102 PRIMARY KEY (pgfile_id) 102 PRIMARY KEY (pgfile_id)
103); 103);
104 104
105# keywords for problems 105# keywords for problems
106# 106#
107DROP TABLE IF EXISTS keyword; 107DROP TABLE IF EXISTS `NPL-keyword`;
108CREATE TABLE keyword 108CREATE TABLE `NPL-keyword`
109( 109(
110 keyword_id int(15) 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
116# pgfile_keyword associates prolems with keywords 116# pgfile_keyword associates prolems with keywords
117# 117#
118DROP TABLE IF EXISTS pgfile_keyword; 118DROP TABLE IF EXISTS `NPL-pgfile-keyword`;
119CREATE TABLE pgfile_keyword 119CREATE TABLE `NPL-pgfile-keyword`
120( 120(
121 pgfile_id int(15) DEFAULT 0 NOT NULL, 121 pgfile_id int(15) DEFAULT 0 NOT NULL,
122 keyword_id int(15) DEFAULT 0 NOT NULL, 122 keyword_id int(15) DEFAULT 0 NOT NULL,
123 KEY pgfile_keyword (keyword_id, pgfile_id), 123 KEY pgfile_keyword (keyword_id, pgfile_id),
124 KEY pgfile (pgfile_id) 124 KEY pgfile (pgfile_id)
125); 125);
126 126
127# pgfile_institution associates problems with institutions 127# pgfile_institution associates problems with institutions
128# 128#
129DROP TABLE IF EXISTS pgfile_institution; 129DROP TABLE IF EXISTS `NPL-pgfile-institution`;
130CREATE TABLE pgfile_institution 130CREATE TABLE `NPL-pgfile-institution`
131( 131(
132 pgfile_id int(15) DEFAULT 0 NOT NULL, 132 pgfile_id int(15) DEFAULT 0 NOT NULL,
133 institution_id int(15) DEFAULT 0 NOT NULL, 133 institution_id int(15) DEFAULT 0 NOT NULL,
134 PRIMARY KEY (institution_id, pgfile_id) 134 PRIMARY KEY (institution_id, pgfile_id)
135); 135);
136 136
137# textbook table contains textbook info 137# textbook table contains textbook info
138# 138#
139DROP TABLE IF EXISTS textbook; 139DROP TABLE IF EXISTS `NPL-textbook`;
140CREATE TABLE textbook 140CREATE TABLE `NPL-textbook`
141( 141(
142 textbook_id int (15) 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,
… …
150); 150);
151 151
152# weak table chapter 152# weak table chapter
153# chapters from a textbook 153# chapters from a textbook
154# 154#
155DROP TABLE IF EXISTS chapter; 155DROP TABLE IF EXISTS `NPL-chapter`;
156CREATE TABLE chapter 156CREATE TABLE `NPL-chapter`
157( 157(
158 chapter_id int (15) 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,
… …
165); 165);
166 166
167# weak table section 167# weak table section
168# sections from a textbook chapter 168# sections from a textbook chapter
169# 169#
170DROP TABLE IF EXISTS section; 170DROP TABLE IF EXISTS `NPL-section`;
171CREATE TABLE section 171CREATE TABLE `NPL-section`
172( 172(
173 section_id int(15) 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,
… …
179); 179);
180 180
181# problem 181# problem
182# problems from a textbook 182# problems from a textbook
183# 183#
184DROP TABLE IF EXISTS problem; 184DROP TABLE IF EXISTS `NPL-problem`;
185CREATE TABLE problem 185CREATE TABLE `NPL-problem`
186( 186(
187 problem_id int(15) 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),
… …
195); 195);
196 196
197# pgfile_problem table 197# pgfile_problem table
198# associates pgfiles to problems from a textbook. 198# associates pgfiles to problems from a textbook.
199# 199#
200DROP TABLE IF EXISTS pgfile_problem; 200DROP TABLE IF EXISTS `NPL-pgfile-problem`;
201CREATE TABLE pgfile_problem 201CREATE TABLE `NPL-pgfile-problem`
202( 202(
203 pgfile_id int(15) DEFAULT 0 NOT NULL, 203 pgfile_id int(15) DEFAULT 0 NOT NULL,
204 problem_id int(15) DEFAULT 0 NOT NULL, 204 problem_id int(15) DEFAULT 0 NOT NULL,
205 PRIMARY KEY (pgfile_id, problem_id) 205 PRIMARY KEY (pgfile_id, problem_id)
206); 206);
Legend:
Removed from v.214  
changed lines
  Added in v.501
aubreyja at gmail dot com
 ViewVC Help
Powered by ViewVC 1.0.9