· 6 years ago · Jun 30, 2019, 07:48 PM
1USE [master]
2
3IF NOT EXISTS (SELECT name FROM sys.DATABASES WHERE name = N'Quiz')
4 CREATE DATABASE [Quiz]
5ELSE
6 DROP DATABASE [Quiz]
7 CREATE DATABASE [Quiz]
8GO
9USE [Quiz]
10
11CREATE TABLE Lecturer
12(
13lecturer_id_ INT IDENTITY(1,1) NOT NULL,
14lecturer_nickname_ VARCHAR(50) NOT NULL,
15lecturer_password_ VARCHAR(50) NOT NULL,
16lecturer_email_ VARCHAR(50) NOT NULL,
17PRIMARY KEY(lecturer_id_)
18)
19
20CREATE TABLE Student
21(
22student_id_ INT IDENTITY(1,1) NOT NULL,
23student_nickname_ VARCHAR(50) NOT NULL,
24student_password_ VARCHAR(50) NOT NULL,
25student_email_ VARCHAR(50) NOT NULL,
26PRIMARY KEY(student_id_)
27)
28
29
30CREATE TABLE Course(
31course_id INT IDENTITY(1,1) NOT NULL,
32course_description_ VARCHAR(50) NOT NULL,
33course_lecturer_id int NOT NULL,
34course_creation_time VARCHAR(50) NOT NULL,
35
36
37PRIMARY KEY(course_id)
38)
39
40ALTER TABLE Course
41WITH CHECK ADD CONSTRAINT FK_course_lecturer_id FOREIGN KEY(course_lecturer_id)
42REFERENCES Lecturer(lecturer_id_);
43
44
45create table CourseSubscriptions
46(
47 course_subscription_id INT IDENTITY(1,1) not null,
48 course_id int,
49 student_id int,
50 PRIMARY KEY(course_subscription_id)
51);
52
53ALTER TABLE CourseSubscriptions
54WITH CHECK ADD CONSTRAINT FK_coursesubs_id FOREIGN KEY( course_id)
55REFERENCES Course( course_id);
56
57ALTER TABLE CourseSubscriptions
58WITH CHECK ADD CONSTRAINT FK_studentss_id FOREIGN KEY(student_id)
59REFERENCES Student(student_id_ );
60
61
62Create Table CourseLesson(
63lesson_id INT IDENTITY(1,1) NOT NULL,
64lesson_description_ VARCHAR(50) NOT NULL,
65lesson_creator VARCHAR(50) NOT NULL,
66lesson_creation_time DATETIME NOT NULL,
67course_id int
68PRIMARY KEY(lesson_id)
69)
70
71ALTER TABLE CourseLesson
72WITH CHECK ADD CONSTRAINT FK_courselesson_id FOREIGN KEY(course_id)
73REFERENCES Course(course_id);
74
75Create table LessonFiles
76(
77lesson_file_id INT IDENTITY(1,1) NOT NULL,
78file_content Varbinary not null,
79lesson_id INT,
80file_creation_time DATETIME NOT NULL,
81PRIMARY KEY(lesson_file_id)
82)
83
84ALTER TABLE LessonFiles
85WITH CHECK ADD CONSTRAINT FK_lessonfile_id FOREIGN KEY(lesson_id)
86REFERENCES CourseLesson(lesson_id);
87
88Create table CourseChat
89(
90Chat_id INT IDENTITY(1,1) NOT NULL,
91course_id int,
92creation_time DATETIME NOT NULL,
93PRIMARY KEY(Chat_id)
94)
95
96ALTER TABLE CourseChat
97WITH CHECK ADD CONSTRAINT FK_coursechat_id FOREIGN KEY(course_id)
98REFERENCES Course(course_id);
99
100Create table ChatMessages
101(
102message_id INT IDENTITY(1,1) NOT NULL,
103message_text Varchar(100) Not null,
104belong_to_chat int,
105author_lecturer_id int ,
106author_student_id int,
107creation_time DATETIME NOT NULL,
108PRIMARY KEY(message_id)
109)
110
111ALTER TABLE ChatMessages
112WITH CHECK ADD CONSTRAINT FK_author_lecturer_id FOREIGN KEY(author_lecturer_id)
113REFERENCES Lecturer(lecturer_id);
114
115
116ALTER TABLE ChatMessages
117WITH CHECK ADD CONSTRAINT FK_author_student_id FOREIGN KEY(author_student_id)
118REFERENCES Student(student_id);
119
120Create table SearchTags
121(
122search_tag_id int IDENTITY(1,1) NOT NULL,
123search_tag_text Varchar(100) Not null,
124PRIMARY KEY(search_tag_id)
125)
126
127Create table CourseSearchTags
128(
129course_search_tag_id int IDENTITY(1,1) NOT NULL,
130course_id int,
131search_tag_id int,
132PRIMARY KEY(course_search_tag_id)
133)
134
135ALTER TABLE CourseSearchTags
136WITH CHECK ADD CONSTRAINT FK_coursesearch_id FOREIGN KEY(course_id)
137REFERENCES Course(course_id);
138
139ALTER TABLE CourseSearchTags
140WITH CHECK ADD CONSTRAINT FK_coursesearchtags_id FOREIGN KEY(search_tag_id)
141REFERENCES SearchTags(search_tag_id);
142
143Create table LessonTestQuiz(
144quiz_id int IDENTITY(1,1) NOT NULL,
145quiz_title Varchar(100) Not null,
146lesson_id int
147PRIMARY KEY(quiz_id)
148);
149
150ALTER TABLE TestQuiz
151WITH CHECK ADD CONSTRAINT FK_testlesson_id FOREIGN KEY(lesson_id)
152REFERENCES CourseLessons(lesson_id);
153
154Create table TestQuizQuestions(
155question_id int IDENTITY(1,1) NOT NULL,
156question_title Varchar(100) Not null,
157quiz_id int
158PRIMARY KEY(question_id)
159);
160
161ALTER TABLE TestQuizQuestions
162WITH CHECK ADD CONSTRAINT FK_TestQuizQuestions_id FOREIGN KEY(quiz_id )
163REFERENCES LessonTestQuiz(quiz_id);
164
165Create table QuestionsAnswerResult(
166answer_result_id int IDENTITY(1,1) NOT NULL,
167answer Varchar(100) Not null,
168result int Not null,
169question_id int
170PRIMARY KEY(answer_result_id)
171);
172
173ALTER TABLE QuestionsAnswerResult
174WITH CHECK ADD CONSTRAINT FK_QuestionsAnswerResult_id FOREIGN KEY(question_id)
175REFERENCES TestQuizQuestions(question_id );
176
177
178Create table TestQuizStudent(
179pair_id int IDENTITY(1,1) NOT NULL,
180quiz_id int,
181student_id int,
182
183PRIMARY KEY(pair_id)
184);
185
186ALTER TABLE TestQuizStudent
187WITH CHECK ADD CONSTRAINT FK_TestQuizStudent_id FOREIGN KEY(quiz_id)
188REFERENCES LessonTestQuiz(quiz_id);
189
190ALTER TABLE TestQuizStudent
191WITH CHECK ADD CONSTRAINT FK_TestQuizStudent555_id FOREIGN KEY(student_id)
192REFERENCES Student(student_id_);
193
194Create table TestQuizStudentAnswers
195(
196 id int IDENTITY(1,1) NOT NULL,
197 quiz_question Varchar(100) Not null,
198 student_answer Varchar(100) Not null,
199 TestQuizStudent_id int not null,
200 PRIMARY KEY(id)
201);
202
203ALTER TABLE TestQuizStudentAnswers
204WITH CHECK ADD CONSTRAINT FK_TestQuizStudentAnswers_id FOREIGN KEY(TestQuizStudent_id)
205REFERENCES TestQuizStudent(pair_id);