V5WvGJS7

1DROP SCHEMA IF EXISTS enrollment;
2
3CREATE SCHEMA IF NOT EXISTS enrollment;
4
5USE enrollment;
6
7
8
9-- -----------------------------------------
10
11-- STUDENTS
12
13-- -----------------------------------------
14
15CREATE TABLE student (
16
17    id INTEGER NOT NULL AUTO_INCREMENT,
18
19    f_name VARCHAR(99) NOT NULL,
20
21    l_name VARCHAR(99) NOT NULL,
22
23    student_id INTEGER NOT NULL,
24
25    PRIMARY KEY (id)
26
27);
28
29
30
31-- -----------------------------------------
32
33-- CLASSES
34
35-- -----------------------------------------
36
37CREATE TABLE class (
38
39    id INTEGER NOT NULL AUTO_INCREMENT,
40
41    name VARCHAR(99) NOT NULL,
42
43    max_size INTEGER NOT NULL,
44
45    PRIMARY KEY (id)
46
47);
48
49-- -----------------------------------------
50
51-- WAITLISTS
52
53-- -----------------------------------------
54
55CREATE TABLE waitlist (
56
57    id INTEGER NOT NULL AUTO_INCREMENT,
58    
59    class_id INTEGER NOT NULL,
60
61    PRIMARY KEY (id),
62
63    FOREIGN KEY (class_id) REFERENCES class (id)
64
65);
66
67-- -----------------------------------------
68
69-- STUDENT IN CLASS
70
71-- -----------------------------------------
72
73CREATE TABLE studentInClass (
74
75    id INTEGER NOT NULL AUTO_INCREMENT,
76
77    class_id INTEGER NOT NULL,
78
79    student_id INTEGER NOT NULL,
80
81    PRIMARY KEY (id),
82
83    FOREIGN KEY (class_id) REFERENCES class (id),
84
85    FOREIGN KEY (student_id) REFERENCES student (id)
86
87);
88
89-- -----------------------------------------
90
91-- STUDENT IN WAITLIST
92
93-- -----------------------------------------
94
95CREATE TABLE studentInWaitlist (
96
97    id INTEGER NOT NULL AUTO_INCREMENT,
98
99    waitlist_id INTEGER NOT NULL,
100
101    student_id INTEGER NOT NULL,
102
103    PRIMARY KEY (id),
104
105    FOREIGN KEY (waitlist_id) REFERENCES waitlist (id),
106
107    FOREIGN KEY (student_id) REFERENCES student (id)
108
109);