SHARE
TWEET

Untitled

a guest Nov 12th, 2019 82 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. CREATE TABLE STUDENT (ID numeric(12),ALBUM_NUMBER VARCHAR2(10) UNIQUE NOT NULL, FIRSTNAMES VARCHAR2(100) NOT NULL, LASTNAMES VARCHAR2(100) NOT NULL, STUDENT_PASSWORD VARCHAR2(50) NOT NULL, EMAIL VARCHAR2(50) NOT NULL, CONSTRAINT PK_STUDENT PRIMARY KEY (ID));
  2.  
  3. CREATE TABLE TERM (
  4.     ID INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  5.     START_DATE DATE NOT NULL,
  6.     END_DATE DATE NOT NULL,
  7.     ACTIVE TINYINT(1) DEFAULT 1
  8. );
  9.  
  10.  
  11. CREATE TABLE DEPARTMENT (
  12.     ID INT(12) UNSIGNED PRIMARY KEY,
  13.     DEPARTMENT_NAME VARCHAR(50) NOT NULL,
  14.     SHORTCUT VARCHAR(5)
  15. );
  16.  
  17. CREATE TABLE LECTURE (
  18.     ID INT(12) UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  19.     LECTURE_NAME VARCHAR(50) NOT NULL,
  20.     LECTURE_KEY VARCHAR(50) NOT NULL,
  21.     CATHEDR VARCHAR(50) NOT NULL,
  22.     SHORTCUT VARCHAR(5)
  23. );
  24.  
  25. CREATE TABLE LECTURER (
  26.     ID NUMERIC(12) UNSIGNED PRIMARY KEY,
  27.     LECTURER_NUMBER VARCHAR(10) UNIQUE NOT NULL,
  28.     FIRSTNAMES VARCHAR(100) NOT NULL,
  29.     LASTNAMES VARCHAR(100) NOT NULL,
  30.     LECTURER_PASSWORD VARCHAR(50) NOT NULL,
  31.     EMAIL VARCHAR(50) NOT NULL
  32. );
  33.  
  34. CREATE TABLE STUDENT_GROUP (
  35.     ID INT(12) UNSIGNED PRIMARY KEY,
  36.     LECTURE_TYPE VARCHAR(20),
  37.     ID_LECTURE INT(12) UNSIGNED,
  38.     ID_TERM INT(6) UNSIGNED,
  39.     ID_DEPARTMENT INT(12) UNSIGNED,
  40.     GROUP_IDENTIFIER VARCHAR(50),
  41.     CONSTRAINT FK_DEPARTMENT_STUDENT_GROUP FOREIGN KEY (ID_DEPARTMENT)
  42.         REFERENCES DEPARTMENT (ID),
  43.     CONSTRAINT FK_TERM_STUDENT_GROUP FOREIGN KEY (ID_TERM)
  44.         REFERENCES TERM (ID),
  45.     CONSTRAINT FK_LECTURE_LECTURER FOREIGN KEY (ID_LECTURE)
  46.         REFERENCES LECTURE (ID)
  47. );
  48.  
  49. ALTER TABLE STUDENT_GROUP DROP foreign key FK_DEPARTMENT_STUDENT_GROUP;
  50. ALTER TABLE STUDENT_GROUP ADD CONSTRAINT FK_DEPARTMENT_STUDENT_GROUP FOREIGN KEY (ID_DEPARTMENT)
  51.         REFERENCES DEPARTMENT (ID) ON DELETE CASCADE ;
  52.  
  53.  
  54. CREATE TABLE LECTURE_TERM_LINK (
  55.     ID_LECTURE INT(12) UNSIGNED,
  56.     ID_TERM INT(6) UNSIGNED,
  57.     PRIMARY KEY (ID_LECTURE , ID_TERM),
  58.     CONSTRAINT FK_LECTURE_TERM FOREIGN KEY (ID_LECTURE)
  59.         REFERENCES LECTURE (ID),
  60.     CONSTRAINT FK_TERM_LECTURE FOREIGN KEY (ID_TERM)
  61.         REFERENCES TERM (ID)
  62. );
  63.  
  64. ALTER TABLE LECTURE_TERM_LINK DROP foreign key FK_LECTURE_TERM;
  65. ALTER TABLE LECTURE_TERM_LINK ADD CONSTRAINT FK_LECTURE_TERM FOREIGN KEY (ID_LECTURE)
  66.         REFERENCES LECTURE (ID) ON DELETE CASCADE ;
  67.  
  68. ALTER TABLE LECTURE_TERM_LINK DROP foreign key FK_TERM_LECTURE;
  69. ALTER TABLE LECTURE_TERM_LINK ADD CONSTRAINT FK_TERM_LECTURE FOREIGN KEY (ID_TERM)
  70.         REFERENCES TERM (ID) ON DELETE CASCADE ;
  71.  
  72.  
  73. CREATE TABLE LECTURE_DEPARTMENT_LINK (
  74.     ID_DEPARTMENT INT(12) UNSIGNED,
  75.     ID_LECTURE INT(12) UNSIGNED,
  76.     PRIMARY KEY (ID_LECTURE , ID_DEPARTMENT),
  77.     CONSTRAINT FK_LECTURE_DEPARTMENT FOREIGN KEY (ID_LECTURE)
  78.         REFERENCES LECTURE (ID),
  79.     CONSTRAINT FK_DEPARTMENT_LECTURE FOREIGN KEY (ID_DEPARTMENT)
  80.         REFERENCES DEPARTMENT (ID)
  81. );
  82.  
  83. CREATE TABLE STUDENT_TERM_LINK (
  84.     ID_STUDENT INT(12) UNSIGNED,
  85.     ID_TERM INT(6) UNSIGNED,
  86.     TERM_NUMBER INT(1) UNSIGNED NOT NULL,
  87.     PRIMARY KEY (ID_STUDENT , ID_TERM),
  88.     CONSTRAINT FK_STUDENT_TERM FOREIGN KEY (ID_STUDENT)
  89.         REFERENCES STUDENT (ID),
  90.     CONSTRAINT FK_TERM_STUDENT FOREIGN KEY (ID_TERM)
  91.         REFERENCES TERM (ID)
  92. );
  93.  
  94. CREATE TABLE STUDENT_GROUP_LINK (
  95.     ID INT(12) UNSIGNED AUTO_INCREMENT,
  96.     ID_STUDENT INT(12) UNSIGNED,
  97.     ID_STUDENT_GROUP INT(12) UNSIGNED,
  98.     ANSWERED TINYINT(1) DEFAULT 0,
  99.     PRIMARY KEY (ID),
  100.     CONSTRAINT FK_STUDENT_GROUP_STUDENT FOREIGN KEY (ID_STUDENT)
  101.         REFERENCES STUDENT (ID),
  102.     CONSTRAINT FK_GROUP_STUDENT_LINK FOREIGN KEY (ID_STUDENT_GROUP)
  103.         REFERENCES STUDENT_GROUP (ID)
  104. );
  105.  
  106. CREATE TABLE STUDENT_DEPARTMENT_LINK (
  107.     ID_DEPARTMENT INT(12) UNSIGNED,
  108.     ID_STUDENT INT(12) UNSIGNED,
  109.     PRIMARY KEY (ID_STUDENT , ID_DEPARTMENT),
  110.     CONSTRAINT FK_STUDENT_DEPARTMENT FOREIGN KEY (ID_STUDENT)
  111.         REFERENCES STUDENT (ID),
  112.     CONSTRAINT FK_DEPARTMENT_STUDENT FOREIGN KEY (ID_DEPARTMENT)
  113.         REFERENCES DEPARTMENT (ID)
  114. );
  115.  
  116. CREATE TABLE DEPARTMENT_TERM_LINK (
  117.     ID_DEPARTMENT INT(12) UNSIGNED,
  118.     ID_TERM INT(6) UNSIGNED,
  119.     PRIMARY KEY (ID_TERM , ID_DEPARTMENT),
  120.     CONSTRAINT FK_TERM_DEPARTMENT FOREIGN KEY (ID_TERM)
  121.         REFERENCES TERM (ID),
  122.     CONSTRAINT FK_DEPARTMENT_TERM FOREIGN KEY (ID_DEPARTMENT)
  123.         REFERENCES DEPARTMENT (ID)
  124. );
  125.  
  126. INSERT INTO STUDENT(ID, ALBUM_NUMBER, FIRSTNAMES, LASTNAMES, STUDENT_PASSWORD, EMAIL) VALUES (1,'WI123456','NAME1','LASTNAME1', 'PASS1','student1@mail');
  127. INSERT INTO STUDENT(ID, ALBUM_NUMBER, FIRSTNAMES, LASTNAMES, STUDENT_PASSWORD, EMAIL) VALUES (2,'WI223456','NAME1','LASTNAME1', 'PASS1','student1@mail');
  128. INSERT INTO STUDENT(ID, ALBUM_NUMBER, FIRSTNAMES, LASTNAMES, STUDENT_PASSWORD, EMAIL) VALUES (3,'WI323456','NAME1','LASTNAME1', 'PASS1','student1@mail');
  129. INSERT INTO STUDENT(ID, ALBUM_NUMBER, FIRSTNAMES, LASTNAMES, STUDENT_PASSWORD, EMAIL) VALUES (4,'WI423456','NAME1','LASTNAME1', 'PASS1','student1@mail');
  130. INSERT INTO STUDENT(ID, ALBUM_NUMBER, FIRSTNAMES, LASTNAMES, STUDENT_PASSWORD, EMAIL) VALUES (5,'WI523456','NAME1','LASTNAME1', 'PASS1','student1@mail');
  131. INSERT INTO STUDENT(ID, ALBUM_NUMBER, FIRSTNAMES, LASTNAMES, STUDENT_PASSWORD, EMAIL) VALUES (6,'WI623456','NAME1','LASTNAME1', 'PASS1','student1@mail');
  132.  
  133. INSERT INTO TERM( START_DATE,END_DATE) VALUES ('2017-01-01','2017-06-01');
  134. INSERT INTO TERM(START_DATE,END_DATE) VALUES ('2018-01-01','2018-06-01');
  135. INSERT INTO TERM(START_DATE,END_DATE) VALUES ('2019-01-01','2019-06-01');
  136.  
  137. INSERT INTO LECTURER (ID, LECTURER_NUMBER, FIRSTNAMES, LASTNAMES, LECTURER_PASSWORD, EMAIL) VALUES (1,'WI123456','LECTURERNAME1','LECTURERLASTNAME1', 'PASS1','LECTURER1@mail');
  138. INSERT INTO LECTURER (ID, LECTURER_NUMBER, FIRSTNAMES, LASTNAMES, LECTURER_PASSWORD, EMAIL) VALUES (2,'WI223456','LECTURERNAME2','LECTURERLASTNAME2', 'PASS1','LECTURER2@mail');
  139. INSERT INTO LECTURER (ID, LECTURER_NUMBER, FIRSTNAMES, LASTNAMES, LECTURER_PASSWORD, EMAIL) VALUES (3,'WI323456','LECTURERNAME3','LECTURERLASTNAME3', 'PASS1','LECTURER3@mail');
  140. INSERT INTO LECTURER (ID, LECTURER_NUMBER, FIRSTNAMES, LASTNAMES, LECTURER_PASSWORD, EMAIL) VALUES (4,'WI423456','LECTURERNAME4','LECTURERLASTNAME4', 'PASS1','LECTURER4@mail');
  141.  
  142. INSERT INTO DEPARTMENT (ID, DEPARTMENT_NAME, SHORTCUT) VALUES (1,'Wydział informatyki','WI');
  143. INSERT INTO DEPARTMENT (ID, DEPARTMENT_NAME, SHORTCUT) VALUES (2,'Wydział budownictwa','WB');
  144. INSERT INTO DEPARTMENT (ID, DEPARTMENT_NAME, SHORTCUT) VALUES (3,'Wydział mechaniczny','WM');
  145.    
  146. INSERT INTO LECTURE (ID, LECTURE_NAME, LECTURE_KEY, CATHEDR, SHORTCUT) VALUES (1,'Programowanie Obiektowe','Z11','Programowania','PO');
  147. INSERT INTO LECTURE (ID, LECTURE_NAME, LECTURE_KEY, CATHEDR, SHORTCUT) VALUES (2,'Podstawy Programowania','Z12','Programowania','PP');
  148. INSERT INTO LECTURE (ID, LECTURE_NAME, LECTURE_KEY, CATHEDR, SHORTCUT) VALUES (3,'Zaawansowane Techniki Programowania','Z13','Programowania','ZTP');
  149. INSERT INTO LECTURE (ID, LECTURE_NAME, LECTURE_KEY, CATHEDR, SHORTCUT) VALUES (4,'Technika Cyfrowa','Z14','Programowania','TC');
  150.  
  151. INSERT INTO STUDENT_GROUP (ID,LECTURE_TYPE,ID_LECTURE,ID_TERM,ID_DEPARTMENT) VALUES (1,'WYKLAD',1,1,1);
  152. INSERT INTO STUDENT_GROUP (ID,LECTURE_TYPE,ID_LECTURE,ID_TERM,ID_DEPARTMENT) VALUES (2,'WYKLAD',1,1,1);
  153. INSERT INTO STUDENT_GROUP (ID,LECTURE_TYPE,ID_LECTURE,ID_TERM,ID_DEPARTMENT) VALUES (3,'WYKLAD',1,1,1);
  154. INSERT INTO STUDENT_GROUP (ID,LECTURE_TYPE,ID_LECTURE,ID_TERM,ID_DEPARTMENT) VALUES (4,'WYKLAD',2,1,1);
  155. INSERT INTO STUDENT_GROUP (ID,LECTURE_TYPE,ID_LECTURE,ID_TERM,ID_DEPARTMENT) VALUES (5,'WYKLAD',2,1,1);
  156.  
  157. INSERT INTO STUDENT_TERM_LINK(ID_STUDENT, ID_TERM, TERM_NUMBER) VALUES (1,1,1);
  158. INSERT INTO STUDENT_TERM_LINK(ID_STUDENT, ID_TERM, TERM_NUMBER) VALUES (2,1,1);
  159. INSERT INTO STUDENT_TERM_LINK(ID_STUDENT, ID_TERM, TERM_NUMBER) VALUES (3,1,1);
  160. INSERT INTO STUDENT_TERM_LINK(ID_STUDENT, ID_TERM, TERM_NUMBER) VALUES (4,2,3);
  161. INSERT INTO STUDENT_TERM_LINK(ID_STUDENT, ID_TERM, TERM_NUMBER) VALUES (5,2,3);
  162. INSERT INTO STUDENT_TERM_LINK(ID_STUDENT, ID_TERM, TERM_NUMBER) VALUES (6,2,3);
  163.  
  164. INSERT INTO STUDENT_GROUP_LINK(ID_STUDENT, ID_STUDENT_GROUP) VALUES (1,1);
  165. INSERT INTO STUDENT_GROUP_LINK(ID_STUDENT, ID_STUDENT_GROUP) VALUES (2,1);
  166. INSERT INTO STUDENT_GROUP_LINK(ID_STUDENT, ID_STUDENT_GROUP) VALUES (3,1);
  167. INSERT INTO STUDENT_GROUP_LINK(ID_STUDENT, ID_STUDENT_GROUP) VALUES (4,2);
  168. INSERT INTO STUDENT_GROUP_LINK(ID_STUDENT, ID_STUDENT_GROUP) VALUES (5,2);
  169. INSERT INTO STUDENT_GROUP_LINK(ID_STUDENT, ID_STUDENT_GROUP) VALUES (6,2);
  170.  
  171. INSERT INTO STUDENT_DEPARTMENT_LINK (ID_DEPARTMENT, ID_STUDENT) VALUES (1,1);
  172. INSERT INTO STUDENT_DEPARTMENT_LINK (ID_DEPARTMENT, ID_STUDENT) VALUES (1,2);
  173. INSERT INTO STUDENT_DEPARTMENT_LINK (ID_DEPARTMENT, ID_STUDENT) VALUES (1,3);
  174. INSERT INTO STUDENT_DEPARTMENT_LINK (ID_DEPARTMENT, ID_STUDENT) VALUES (1,4);
  175. INSERT INTO STUDENT_DEPARTMENT_LINK (ID_DEPARTMENT, ID_STUDENT) VALUES (1,5);
  176. INSERT INTO STUDENT_DEPARTMENT_LINK (ID_DEPARTMENT, ID_STUDENT) VALUES (1,6);
  177.  
  178. INSERT INTO LECTURER_STUDENT_GROUP_LINK(ID_STUDENT_GROUP, ID_LECTURER) VALUES (1,1);
  179. INSERT INTO LECTURER_STUDENT_GROUP_LINK(ID_STUDENT_GROUP, ID_LECTURER) VALUES (1,2);
  180. INSERT INTO LECTURER_STUDENT_GROUP_LINK(ID_STUDENT_GROUP, ID_LECTURER) VALUES (2,1);
  181. INSERT INTO LECTURER_STUDENT_GROUP_LINK(ID_STUDENT_GROUP, ID_LECTURER) VALUES (2,2);
  182. INSERT INTO LECTURER_STUDENT_GROUP_LINK(ID_STUDENT_GROUP, ID_LECTURER) VALUES (2,3);
  183.  
  184. INSERT INTO DEPARTMENT_TERM_LINK(ID_DEPARTMENT,ID_TERM) VALUES (1,1);
  185. INSERT INTO DEPARTMENT_TERM_LINK(ID_DEPARTMENT,ID_TERM) VALUES (2,1);
  186. INSERT INTO DEPARTMENT_TERM_LINK(ID_DEPARTMENT,ID_TERM) VALUES (3,1);
  187.  
  188. INSERT INTO DEPARTMENT_TERM_LINK(ID_DEPARTMENT,ID_TERM) VALUES (1,2);
  189. INSERT INTO DEPARTMENT_TERM_LINK(ID_DEPARTMENT,ID_TERM) VALUES (2,2);
  190. INSERT INTO DEPARTMENT_TERM_LINK(ID_DEPARTMENT,ID_TERM) VALUES (3,2);
  191.  
  192. INSERT INTO DEPARTMENT_TERM_LINK(ID_DEPARTMENT,ID_TERM) VALUES (1,3);
  193. INSERT INTO DEPARTMENT_TERM_LINK(ID_DEPARTMENT,ID_TERM) VALUES (2,3);
  194.  
  195.  
  196. INSERT INTO LECTURE_TERM_LINK(ID_LECTURE,ID_TERM) VALUES (1,1);
  197. INSERT INTO LECTURE_TERM_LINK(ID_LECTURE,ID_TERM) VALUES (2,1);
  198. INSERT INTO LECTURE_TERM_LINK(ID_LECTURE,ID_TERM) VALUES (3,1);
  199.  
  200. INSERT INTO LECTURE_TERM_LINK(ID_LECTURE,ID_TERM) VALUES (2,2);
  201. INSERT INTO LECTURE_TERM_LINK(ID_LECTURE,ID_TERM) VALUES (3,2);
  202.  
  203. INSERT INTO LECTURE_DEPARTMENT_LINK(ID_LECTURE,ID_DEPARTMENT) VALUES (1,1);
  204. INSERT INTO LECTURE_DEPARTMENT_LINK(ID_LECTURE,ID_DEPARTMENT) VALUES (2,1);
  205. INSERT INTO LECTURE_DEPARTMENT_LINK(ID_LECTURE,ID_DEPARTMENT) VALUES (3,1);
  206.  
  207. INSERT INTO LECTURE_DEPARTMENT_LINK(ID_LECTURE,ID_DEPARTMENT) VALUES (2,2);
  208. INSERT INTO LECTURE_DEPARTMENT_LINK(ID_LECTURE,ID_DEPARTMENT) VALUES (3,2);
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
 
Top