Advertisement
MarkUa

Untitled

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