/lenasys/trunk

To get this branch, use:
bzr branch http://gegoxaren.bato24.eu/bzr/lenasys/trunk

« back to all changes in this revision

Viewing changes to databas.sql

  • Committer: Gustav Hatvigsson
  • Date: 2013-04-17 12:51:33 UTC
  • mfrom: (29.1.5 lenasys)
  • Revision ID: gustav.hartvigsson@gmail.com-20130417125133-1iablfgxlgnqovj4
Merginging.

Show diffs side-by-side

added added

removed removed

Lines of Context:
databas.sql
6
6
USE `lenasys` ;
7
7
 
8
8
-- -----------------------------------------------------
 
9
-- Table `lenasys`.`Users`
9
10
-- -----------------------------------------------------
10
 
CREATE  TABLE IF NOT EXISTS `lenasys`.`Files` (
11
 
  `fileName` VARCHAR(20) NOT NULL ,
12
 
  `codeLanguage` VARCHAR(10) NULL ,
13
 
  `fileType` VARCHAR(5) NULL ,
14
 
  `dataBlob` BLOB NULL ,
15
 
  PRIMARY KEY (`fileName`) ,
16
 
  UNIQUE INDEX `fileName_UNIQUE` (`fileName` ASC) )
 
11
CREATE  TABLE IF NOT EXISTS `lenasys`.`Users` (
 
12
  `userName` VARCHAR(20) NOT NULL ,
 
13
  `name` VARCHAR(45) NULL ,
 
14
  `passwd` VARCHAR(45) NULL ,
 
15
  `userType` INT NULL , -- teacher or student
 
16
  `ssn` CHAR(13) NULL COMMENT 'yyyymmdd-xxxx' ,
 
17
  PRIMARY KEY (`userName`) ,
 
18
  UNIQUE INDEX `userName_UNIQUE` (`userName` ASC) )
17
19
ENGINE = InnoDB;
18
20
 
19
 
 
20
21
-- -----------------------------------------------------
21
22
-- Table `lenasys`.`Courses`
22
23
-- -----------------------------------------------------
23
24
CREATE  TABLE IF NOT EXISTS `lenasys`.`Courses` (
24
25
  `courseID` VARCHAR(10) NOT NULL ,
25
 
  `courseData` VARCHAR(45) NULL ,
26
 
  `courseName` VARCHAR(45) NULL ,
 
26
  `name` VARCHAR(45) NULL ,
 
27
  `courseData` VARCHAR(10) NULL , -- Short explenation about course, example: G1N 7,5hp
27
28
  PRIMARY KEY (`courseID`) ,
28
29
  UNIQUE INDEX `courseID_UNIQUE` (`courseID` ASC) )
29
30
ENGINE = InnoDB;
30
31
 
 
32
-- -----------------------------------------------------
 
33
-- Table `lenasys`.`StudentCourseRegistrations`
 
34
-- -----------------------------------------------------
 
35
CREATE  TABLE IF NOT EXISTS `lenasys`.`StudentCourseRegistrations` (
 
36
  `courseOccasion` VARCHAR(16) NOT NULL , --Example HT2012 period 2
 
37
  `userName` VARCHAR(20) NOT NULL ,
 
38
  `courseID` VARCHAR(10) NOT NULL , -- Example G14234
 
39
  PRIMARY KEY (`courseOccasion`,`userName`, `courseID`) ,
 
40
  INDEX `fk_StudentCourseRegistrations_Courses1_idx` (`courseID` ASC) ,
 
41
  CONSTRAINT `fk_StudentCourseRegistrations_Users`
 
42
    FOREIGN KEY (`userName` )
 
43
    REFERENCES `lenasys`.`Users` (`userName` )
 
44
    ON DELETE NO ACTION
 
45
    ON UPDATE NO ACTION,
 
46
  CONSTRAINT `fk_StudentCourseRegistrations_Courses1`
 
47
    FOREIGN KEY (`courseID` )
 
48
    REFERENCES `lenasys`.`Courses` (`courseID` )
 
49
    ON DELETE NO ACTION
 
50
    ON UPDATE NO ACTION)
 
51
ENGINE = InnoDB;
 
52
 
 
53
-- -----------------------------------------------------
 
54
-- Table `lenasys`.`Quizzes`
 
55
-- -----------------------------------------------------
 
56
CREATE  TABLE IF NOT EXISTS `lenasys`.`Quizzes` (
 
57
  `quizNr` INT NOT NULL ,
 
58
  `courseID` VARCHAR(10) NOT NULL ,
 
59
  `quizData` VARCHAR(45) NULL ,
 
60
  `allowMultipleReplies` BOOLEAN NULL , 
 
61
  `autoCorrected` BOOLEAN NULL , -- if the quiz is corrected by auto or if the teacher needs to do it manually.
 
62
  `openingDate` DATETIME NULL ,  -- time for the student to do the quiz
 
63
  `closingDate` DATETIME NULL ,
 
64
  PRIMARY KEY (`quizNr`, `courseID`) ,
 
65
  INDEX `fk_Quizzes_Courses1_idx` (`courseID` ASC) ,
 
66
  UNIQUE INDEX `quizNr_UNIQUE` (`quizNr` ASC) ,
 
67
  CONSTRAINT `fk_Quizzes_Courses1`
 
68
    FOREIGN KEY (`courseID` )
 
69
    REFERENCES `lenasys`.`Courses` (`courseID` )
 
70
    ON DELETE NO ACTION
 
71
    ON UPDATE NO ACTION)
 
72
ENGINE = InnoDB;
 
73
 
 
74
-- -----------------------------------------------------
 
75
-- Table `lenasys`.`QuizQuestions`
 
76
-- -----------------------------------------------------
 
77
CREATE  TABLE IF NOT EXISTS `lenasys`.`QuizQuestions` (
 
78
  `questionID` INT NOT NULL , -- The id of the individual question in the quiz.
 
79
  `quizNr` INT NOT NULL , -- The quiz from wich the question is from
 
80
  `courseID` VARCHAR(10) NOT NULL ,
 
81
  `questionData` VARCHAR(45) NULL , -- the question
 
82
  `correctAnswer` VARCHAR(45) NULL , -- the answer
 
83
  PRIMARY KEY (`questionID`, `quizNr`, `courseID`) ,
 
84
  UNIQUE INDEX `questionID_UNIQUE` (`questionID` ASC) ,
 
85
  INDEX `fk_QuizQuestions_Quizzes1_idx` (`quizNr` ASC, `courseID` ASC) ,
 
86
  CONSTRAINT `fk_QuizQuestions_Quizzes1`
 
87
    FOREIGN KEY (`quizNr` , `courseID` )
 
88
    REFERENCES `lenasys`.`Quizzes` (`quizNr` , `courseID` )
 
89
    ON DELETE NO ACTION
 
90
    ON UPDATE NO ACTION)
 
91
ENGINE = InnoDB;
 
92
 
 
93
-- -----------------------------------------------------
 
94
-- Table `lenasys`.`AssignedQuizzes`
 
95
-- -----------------------------------------------------
 
96
CREATE  TABLE IF NOT EXISTS `lenasys`.`AssignedQuizzes` (
 
97
  `courseOccasion` VARCHAR(16) NOT NULL , --Example HT2012 period 2
 
98
  `userName` VARCHAR(20) NOT NULL , -- wich student took the quiz
 
99
  `courseID` VARCHAR(10) NOT NULL ,
 
100
  `quizNr` INT NOT NULL ,
 
101
  `quizCourseID` VARCHAR(10) NOT NULL ,
 
102
  `answers` VARCHAR(45) NULL , -- answers provided by the student
 
103
  `answerHash` VARCHAR(45) NULL , -- a security measure
 
104
  `answeredTimeStamp` DATETIME NULL , -- when the student submittet the quiz
 
105
  `grade` VARCHAR(8) NULL ,
 
106
  `gradeComment` VARCHAR(200) NULL ,
 
107
  PRIMARY KEY (`userName`, `courseID`, `nr`, `courseID`) ,
 
108
  INDEX `fk_AssignedQuizzes_Quizzes1_idx` (`nr` ASC, `courseID` ASC) ,
 
109
  CONSTRAINT `fk_AssignedQuizzes_StudentCourseRegistrations1`
 
110
    FOREIGN KEY (`courseOccasion`,`userName` , `courseID` )
 
111
    REFERENCES `lenasys`.`StudentCourseRegistrations` (`courseOccasion`,`userName` , `courseID` )
 
112
    ON DELETE NO ACTION
 
113
    ON UPDATE NO ACTION,
 
114
  CONSTRAINT `fk_AssignedQuizzes_Quizzes1`
 
115
    FOREIGN KEY (`quizNr` , `quizCourseID` )
 
116
    REFERENCES `lenasys`.`Quizzes` (`quizNr` , `quizCourseID` )
 
117
    ON DELETE NO ACTION
 
118
    ON UPDATE NO ACTION)
 
119
ENGINE = InnoDB;
31
120
 
32
121
-- -----------------------------------------------------
33
122
-- Table `lenasys`.`Examples`
34
123
-- -----------------------------------------------------
35
124
CREATE  TABLE IF NOT EXISTS `lenasys`.`Examples` (
36
 
  `name` VARCHAR(20) NOT NULL ,
 
125
  `exampleName` VARCHAR(20) NOT NULL ,
37
126
  `courseID` VARCHAR(10) NOT NULL ,
38
 
  `orderNumber` INT NULL ,
 
127
  `orderNumber` INT NULL , -- the order of the examples in the same category 
39
128
  `description` VARCHAR(200) NULL ,
40
 
  PRIMARY KEY (`name`, `courseID`) ,
 
129
  PRIMARY KEY (`exampleName`, `courseID`) ,
41
130
  INDEX `fk_Examples_Courses1_idx` (`courseID` ASC) ,
42
 
  UNIQUE INDEX `name_UNIQUE` (`name` ASC) ,
 
131
  UNIQUE INDEX `exampleName_UNIQUE` (`exampleName` ASC) ,
43
132
  CONSTRAINT `fk_Examples_Courses1`
44
133
    FOREIGN KEY (`courseID` )
45
134
    REFERENCES `lenasys`.`Courses` (`courseID` )
48
136
    ON UPDATE NO ACTION)
49
137
ENGINE = InnoDB;
50
138
 
51
 
 
52
139
-- -----------------------------------------------------
53
140
-- Table `lenasys`.`Pages`
54
141
-- -----------------------------------------------------
55
142
CREATE  TABLE IF NOT EXISTS `lenasys`.`Pages` (
56
 
  `name` VARCHAR(45) NOT NULL ,
 
143
  `pageName` VARCHAR(45) NOT NULL ,
57
144
  `exampleName` VARCHAR(20) NOT NULL ,
58
145
  `courseID` VARCHAR(10) NOT NULL ,
59
 
  `pageColumn` INT NOT NULL ,
60
 
  `orderNumber` INT NULL ,
61
 
  PRIMARY KEY (`name`, `exampleName`, `courseID`) ,
 
146
  `orderNumber` INT NULL , 
 
147
  `pageColumn` INT NOT NULL , -- which column in the view the file is presented
 
148
  PRIMARY KEY (`pageName`, `exampleName`, `courseID`) ,
62
149
  INDEX `fk_Pages_Examples1_idx` (`exampleName` ASC, `courseID` ASC) ,
63
150
  CONSTRAINT `fk_Pages_Examples1`
64
151
    FOREIGN KEY (`exampleName` , `courseID` )
65
 
    REFERENCES `lenasys`.`Examples` (`name` , `courseID` )
 
152
    REFERENCES `lenasys`.`Examples` (`exampleName` , `courseID` )
66
153
    ON DELETE NO ACTION
67
154
    ON UPDATE NO ACTION)
68
155
ENGINE = InnoDB;
69
156
 
70
 
 
71
 
CREATE  TABLE IF NOT EXISTS `lenasys`.`PagesFiles` (
72
 
  `fileName` VARCHAR(20) NOT NULL ,
73
 
  `name` VARCHAR(45) NOT NULL ,
 
157
-- -----------------------------------------------------
 
158
-- Table `lenasys`.`Files`
 
159
-- -----------------------------------------------------
 
160
CREATE  TABLE IF NOT EXISTS `lenasys`.`Files` (
 
161
  `fileName` VARCHAR(20) NOT NULL ,
 
162
  `fileType` VARCHAR(5) NULL , -- can be for example: text, kod, video or bild
 
163
  `codeLanguage` VARCHAR(10) NULL , -- example: javascript, html, m.m.
 
164
  `dataBlob` BLOB NULL , -- This is where the binary file is stored
 
165
  PRIMARY KEY (`fileName`) ,
 
166
  UNIQUE INDEX `fileName_UNIQUE` (`fileName` ASC) )
 
167
ENGINE = InnoDB;
 
168
 
 
169
 
 
170
 
 
171
-- -----------------------------------------------------
 
172
-- Table `lenasys`.`PageFiles`
 
173
-- -----------------------------------------------------
 
174
CREATE  TABLE IF NOT EXISTS `lenasys`.`PageFiles` (
 
175
  `fileName` VARCHAR(20) NOT NULL ,
 
176
  `pageName` VARCHAR(45) NOT NULL , -- 
74
177
  `exampleName` VARCHAR(20) NOT NULL ,
75
178
  `courseID` VARCHAR(10) NOT NULL ,
76
 
  PRIMARY KEY (`fileName`, `name`, `exampleName`, `courseID`) ,
 
179
  PRIMARY KEY (`fileName`, `pageName`, `exampleName`, `courseID`) ,
77
180
  INDEX `fk_PagesFiles_Files1_idx` (`fileName` ASC) ,
78
 
  INDEX `fk_PagesFiles_Pages1_idx` (`name` ASC, `exampleName` ASC, `courseID` ASC) ,
 
181
  INDEX `fk_PagesFiles_Pages1_idx` (`pageName` ASC, `exampleName` ASC, `courseID` ASC) ,
79
182
  CONSTRAINT `fk_PagesFiles_Files1`
80
183
    FOREIGN KEY (`fileName` )
81
184
    REFERENCES `lenasys`.`Files` (`fileName` )
82
185
    ON DELETE NO ACTION
83
186
    ON UPDATE NO ACTION,
84
187
  CONSTRAINT `fk_PagesFiles_Pages1`
85
 
    FOREIGN KEY (`name` , `exampleName` , `courseID` )
86
 
    REFERENCES `lenasys`.`Pages` (`name` , `exampleName` , `courseID` )
 
188
    FOREIGN KEY (`pageName` , `exampleName` , `courseID` )
 
189
    REFERENCES `lenasys`.`Pages` (`pageName` , `exampleName` , `courseID` )
87
190
    ON DELETE NO ACTION
88
191
    ON UPDATE NO ACTION)
89
192
ENGINE = InnoDB;
98
198
CREATE  TABLE IF NOT EXISTS `lenasys`.`FileKeywords` (
99
199
  `id` INT NOT NULL AUTO_INCREMENT ,
100
200
  `fileName` VARCHAR(20) NOT NULL ,
 
201
  `keyword` VARCHAR(20) NOT NULL ,
101
202
  PRIMARY KEY (`id`, `fileName`) ,
102
203
  INDEX `fk_FileKeywords_Files1_idx` (`fileName` ASC) ,
103
204
  UNIQUE INDEX `id_UNIQUE` (`id` ASC) ,
115
216
CREATE  TABLE IF NOT EXISTS `lenasys`.`FileInterestingLines` (
116
217
  `id` INT NOT NULL AUTO_INCREMENT ,
117
218
  `fileName` VARCHAR(20) NOT NULL ,
118
 
  `beginningAt` INT NULL ,
119
 
  `endingAt` INT NULL ,
 
219
  `beginAt` INT NULL ,
 
220
  `endAt` INT NULL ,
120
221
  PRIMARY KEY (`id`, `fileName`) ,
121
222
  INDEX `fk_FileInterestingLines_Files1_idx` (`fileName` ASC) ,
122
223
  UNIQUE INDEX `id_UNIQUE` (`id` ASC) ,
127
228
    ON UPDATE NO ACTION)
128
229
ENGINE = InnoDB;
129
230
 
130
 
 
131
 
CREATE  TABLE IF NOT EXISTS `lenasys`.`Users` (
132
 
  `userName` VARCHAR(20) NOT NULL ,
133
 
  `name` VARCHAR(45) NULL ,
134
 
  `password` VARCHAR(45) NULL ,
135
 
  `userType` INT NULL ,
136
 
  `personalNumber` CHAR(13) NULL COMMENT 'yyyymmdd-xxxx' ,
137
 
  PRIMARY KEY (`userName`) ,
138
 
  UNIQUE INDEX `userName_UNIQUE` (`userName` ASC) )
139
 
ENGINE = InnoDB;
140
 
 
141
 
 
142
 
CREATE  TABLE IF NOT EXISTS `lenasys`.`StudentCourseRegistrations` (
143
 
  `userName` VARCHAR(20) NOT NULL ,
144
 
  `courseID` VARCHAR(10) NOT NULL ,
145
 
  `courseOccation` VARCHAR(45) NOT NULL ,
146
 
  PRIMARY KEY (`userName`, `courseID`) ,
147
 
  INDEX `fk_StudentCourseRegistrations_Courses1_idx` (`courseID` ASC) ,
148
 
  CONSTRAINT `fk_StudentCourseRegistrations_Users`
149
 
    FOREIGN KEY (`userName` )
150
 
    REFERENCES `lenasys`.`Users` (`userName` )
151
 
    ON DELETE NO ACTION
152
 
    ON UPDATE NO ACTION,
153
 
  CONSTRAINT `fk_StudentCourseRegistrations_Courses1`
154
 
    FOREIGN KEY (`courseID` )
155
 
    REFERENCES `lenasys`.`Courses` (`courseID` )
156
 
    ON DELETE NO ACTION
157
 
    ON UPDATE NO ACTION)
158
 
ENGINE = InnoDB;
159
 
 
160
 
 
161
 
CREATE  TABLE IF NOT EXISTS `lenasys`.`Quizzes` (
162
 
  `nr` INT NOT NULL ,
163
 
  `courseID` VARCHAR(10) NOT NULL ,
164
 
  `autoCorrected` TINYINT(1) NULL ,
165
 
  `allowMultipleReplies` TINYINT(1) NULL ,
166
 
  `quizData` VARCHAR(45) NULL ,
167
 
  `openingDate` DATETIME NULL ,
168
 
  `closingDate` DATETIME NULL ,
169
 
  PRIMARY KEY (`nr`, `courseID`) ,
170
 
  INDEX `fk_Quizzes_Courses1_idx` (`courseID` ASC) ,
171
 
  UNIQUE INDEX `Nr_UNIQUE` (`Nr` ASC) ,
172
 
  CONSTRAINT `fk_Quizzes_Courses1`
173
 
    FOREIGN KEY (`courseID` )
174
 
    REFERENCES `lenasys`.`Courses` (`courseID` )
175
 
    ON DELETE NO ACTION
176
 
    ON UPDATE NO ACTION)
177
 
ENGINE = InnoDB;
178
 
 
179
 
 
180
 
CREATE  TABLE IF NOT EXISTS `lenasys`.`AssignedQuizzes` (
181
 
  `userName` VARCHAR(20) NOT NULL ,
182
 
  `courseID` VARCHAR(10) NOT NULL ,
183
 
  `nr` INT NOT NULL ,
184
 
  `courseID` VARCHAR(10) NOT NULL ,
185
 
  `answerHash` VARCHAR(45) NULL ,
186
 
  `answer` VARCHAR(45) NULL ,
187
 
  `grade` VARCHAR(45) NULL ,
188
 
  `gradeComment` VARCHAR(200) NULL ,
189
 
  `answeredTimeStamp` DATETIME NULL ,
190
 
  PRIMARY KEY (`userName`, `courseID`, `nr`, `courseID`) ,
191
 
  INDEX `fk_AssignedQuizzes_Quizzes1_idx` (`nr` ASC, `courseID` ASC) ,
192
 
  CONSTRAINT `fk_AssignedQuizzes_StudentCourseRegistrations1`
193
 
    FOREIGN KEY (`userName` , `courseID` )
194
 
    REFERENCES `lenasys`.`StudentCourseRegistrations` (`userName` , `courseID` )
195
 
    ON DELETE NO ACTION
196
 
    ON UPDATE NO ACTION,
197
 
  CONSTRAINT `fk_AssignedQuizzes_Quizzes1`
198
 
    FOREIGN KEY (`nr` , `courseID` )
199
 
    REFERENCES `lenasys`.`Quizzes` (`nr` , `courseID` )
200
 
    ON DELETE NO ACTION
201
 
    ON UPDATE NO ACTION)
202
 
ENGINE = InnoDB;
203
 
 
204
 
 
205
 
CREATE  TABLE IF NOT EXISTS `lenasys`.`QuizQuestions` (
206
 
  `id` INT NOT NULL ,
207
 
  `nr` INT NOT NULL ,
208
 
  `courseID` VARCHAR(10) NOT NULL ,
209
 
  `questionData` VARCHAR(45) NULL ,
210
 
  `correctAnswer` VARCHAR(45) NULL ,
211
 
  PRIMARY KEY (`id`, `nr`, `courseID`) ,
212
 
  UNIQUE INDEX `id_UNIQUE` (`id` ASC) ,
213
 
  INDEX `fk_QuizQuestions_Quizzes1_idx` (`nr` ASC, `courseID` ASC) ,
214
 
  CONSTRAINT `fk_QuizQuestions_Quizzes1`
215
 
    FOREIGN KEY (`nr` , `courseID` )
216
 
    REFERENCES `lenasys`.`Quizzes` (`nr` , `courseID` )
217
 
    ON DELETE NO ACTION
218
 
    ON UPDATE NO ACTION)
219
 
ENGINE = InnoDB;
220
 
 
221
231
USE `lenasys` ;
222
232
 
223
 
 
 
233
-- allow maximum of 5 succesfull quiz-attemps is a good begining for implementation of the loggingtables to add restriction. 
224
234
SET SQL_MODE=@OLD_SQL_MODE;
225
235
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
226
236
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;