vwShQvbB

1CREATE DATABASE IF NOT EXISTS testdb DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_polish_ci;
2
3USE testdb;
4
5CREATE TABLE IF NOT EXISTS student (
6    student_id integer(10) NOT NULL AUTO_INCREMENT PRIMARY KEY,
7    first_name varchar(50) NOT NULL,
8    surname varchar(50) NOT NULL,
9    date_of_birth date NOT NULL,
10    class_id integer(10),
11    guardian_id integer(10)
12);
13
14CREATE TABLE IF NOT EXISTS guardian (
15    guardian_id integer(10) NOT NULL AUTO_INCREMENT PRIMARY KEY,
16    first_name varchar(50) NOT NULL,
17    surname varchar(50) NOT NULL,
18    address varchar(100) NOT NULL,
19    phone_number integer(10) NOT NULL,
20    kiddo_ids integer(10)
21);
22
23CREATE TABLE IF NOT EXISTS teacher (
24    teacher_id integer(10) NOT NULL AUTO_INCREMENT PRIMARY KEY,
25    first_name varchar(50) NOT NULL,
26    surname varchar(50) NOT NULL,
27    address varchar(100) NOT NULL,
28    phone_number integer(10) NOT NULL,
29    specialty_subject varchar(50)
30);
31
32CREATE TABLE IF NOT EXISTS class (
33    class_id integer(10) NOT NULL AUTO_INCREMENT PRIMARY KEY,
34    graduation date NOT NULL,
35    start date NOT NULL,
36    course_ids integer(10),
37    supervisor_id integer(10)
38);
39
40CREATE TABLE IF NOT EXISTS course (
41    course_id integer(10) NOT NULL AUTO_INCREMENT PRIMARY KEY,
42    course_name varchar(10) NOT NULL,
43    teacher_id integer(10)
44);
45
46CREATE TABLE IF NOT EXISTS mark (
47    mark_id integer(50) NOT NULL AUTO_INCREMENT PRIMARY KEY,
48    mark_value float NOT NULL,
49    course_id integer(10) NOT NULL,
50    student_id integer(10) NOT NULL,
51    teacher_id integer(10) NOT NULL
52);
53
54
55
56# ALTER TABLE student ADD CONSTRAINT fk_student_author FOREIGN KEY (guardian_id) REFERENCES guardian (guardian_id);
57# ALTER TABLE student ADD CONSTRAINT fk_student_class FOREIGN KEY (class_id) REFERENCES class (class_id);
58# ALTER TABLE guardian ADD CONSTRAINT fk_guardian_kiddo_ids FOREIGN KEY (kiddo_ids) REFERENCES  student (student_id);
59# ALTER TABLE course ADD CONSTRAINT fk_course_teacher_id FOREIGN KEY (teacher_id) REFERENCES teacher (teacher_id);
60# ALTER TABLE mark ADD CONSTRAINT fk_mark_course_id FOREIGN KEY (course_id) REFERENCES course (course_id);
61# ALTER TABLE mark ADD CONSTRAINT fk_mark_student_id FOREIGN KEY (student_id) REFERENCES student (student_id);
62# ALTER TABLE mark ADD CONSTRAINT fk_mark_teacher_id FOREIGN KEY (teacher_id) REFERENCES teacher (teacher_id);
63
64ALTER TABLE student ADD FOREIGN KEY (guardian_id) REFERENCES guardian (guardian_id);
65ALTER TABLE student ADD FOREIGN KEY (class_id) REFERENCES class (class_id);
66ALTER TABLE guardian ADD FOREIGN KEY (kiddo_ids) REFERENCES  student (student_id);
67ALTER TABLE course ADD FOREIGN KEY (teacher_id) REFERENCES teacher (teacher_id);
68ALTER TABLE mark ADD FOREIGN KEY (course_id) REFERENCES course (course_id);
69ALTER TABLE mark ADD FOREIGN KEY (student_id) REFERENCES student (student_id);
70ALTER TABLE mark ADD FOREIGN KEY (teacher_id) REFERENCES teacher (teacher_id);