jBZz3ZRD

1CREATE TABLE IF NOT EXISTS wydzial(
2    department_id int,
3    department_name TEXT,
4    dean_id int,
5    CONSTRAINT department PRIMARY KEY (department_id)
6    );
7    
8 CREATE TABLE IF NOT EXISTS pracownik(
9 	 employee_id int,
10     department_id int,
11     first_name TEXT,
12     last_name TEXT,
13     CONSTRAINT employee 
14     PRIMARY KEY (employee_id),
15     FOREIGN KEY (department_id) REFERENCES wydzial(department_id)
16     );
17     
18  CREATE TABLE IF NOT EXISTS grupa(
19      group_id int,
20      CONSTRAINT groupa
21      PRIMARY KEY (group_id)
22      );
23      
24   CREATE TABLE IF NOT EXISTS student(
25       student_id int,
26       first_name TEXT,
27       last_name TEXT,
28       CONSTRAINT student
29       PRIMARY KEY (student_id)
30      );
31CREATE TABLE IF NOT EXISTS student_grupa(
32    student_id int,
33    group_id int,
34    CONSTRAINT student_group
35    FOREIGN KEY (student_id) REFERENCES student(student_id),
36    FOREIGN KEY (group_id) REFERENCES grupa(group_id)
37    );
38    
39CREATE TABLE IF NOT EXISTS przedmiot(
40    subject_id int,
41    subject_title TEXT,
42    CONSTRAINT subject
43    PRIMARY KEY (subject_id)
44    );
45    
46CREATE TABLE IF NOT EXISTS zajecia(
47    classes_id int,
48    group_id int,
49    subject_id int,
50    employee_id int,
51    CONSTRAINT calsses
52    PRIMARY KEY (classes_id),
53    FOREIGN KEY (group_id) REFERENCES grupa(group_id),
54    FOREIGN KEY (subject_id) REFERENCES przedmiot(subject_id),
55    FOREIGN KEY (employee_id) REFERENCES pracownik(employee_id)
56    );
57    
58CREATE TABLE IF NOT EXISTS ocena(
59    grade_id int,
60    subject_id int,
61    student_id int,
62    grade_value int,
63    CONSTRAINT grade
64    PRIMARY KEY (grade_id),
65    FOREIGN KEY (subject_id) REFERENCES zajecia(subject_id),
66    FOREIGN KEY (student_id) REFERENCES student(student_id)
67    );