-
Notifications
You must be signed in to change notification settings - Fork 0
Expand file tree
/
Copy pathschool_data.sql
More file actions
245 lines (233 loc) · 10.9 KB
/
school_data.sql
File metadata and controls
245 lines (233 loc) · 10.9 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
/*
Hypothetical school data generated by ChatGPT to fit schema
Data Overview:
- School: 1 school
- Departments: 12 departments
- Staff: 20 staff members (including governors)
- Tutor Groups: 5 tutor groups
- Students: 30 students (5 per tutor group)
- Subjects: 12 subjects
- Student Subject Choices: Compulsory and optional subjects for each student
*/
INSERT INTO school (id, name, street_name, city, postcode, google_map_link, telephone, email, hours, rating)
VALUES
(1, 'Greenwood High School', 'Elm Street', 'Greenwood', 'GW12 4AB', 'https://maps.example.com/gw12', '01234 567890', 'info@school.ac.uk', '08:30 - 15:30', 'Outstanding');
INSERT INTO person (id, first_name, last_name, email)
VALUES
(1, 'James', 'Smith', 'j.smith@school.ac.uk'), -- Governor (Headteacher)
(2, 'Emily', 'Johnson', 'e.johnson@school.ac.uk'), -- Governor
(3, 'Robert', 'Brown', 'r.brown@school.ac.uk'), -- Governor
(4, 'Sarah', 'Williams', 's.williams@school.ac.uk'), -- Governor
(5, 'Michael', 'Jones', 'm.jones@school.ac.uk'), -- Governor
(6, 'Elizabeth', 'Taylor', 'e.taylor@school.ac.uk'), -- Deputy Headteacher
(7, 'David', 'Wilson', 'd.wilson@school.ac.uk'), -- Head of Science Department
(8, 'Mary', 'Moore', 'm.moore@school.ac.uk'), -- Science Teacher
(9, 'John', 'Anderson', 'j.anderson@school.ac.uk'), -- Science Teacher
(10, 'Jennifer', 'Thomas', 'j.thomas@school.ac.uk'), -- Head of Mathematics Department
(11, 'Charles', 'Jackson', 'c.jackson@school.ac.uk'), -- Maths Teacher
(12, 'Patricia', 'White', 'p.white@school.ac.uk'), -- Maths Teacher
(13, 'Christopher', 'Harris', 'c.harris@school.ac.uk'), -- Head of Humanities
(14, 'Linda', 'Martin', 'l.martin@school.ac.uk'), -- History Teacher
(15, 'Barbara', 'Thompson', 'b.thompson@school.ac.uk'), -- Geography Teacher
(16, 'Susan', 'Garcia', 's.garcia@school.ac.uk'), -- French Teacher
(17, 'Joseph', 'Martinez', 'j.martinez@school.ac.uk'), -- German Teacher
(18, 'William', 'Robinson', 'w.robinson@school.ac.uk'), -- Head of Languages
(19, 'Dorothy', 'Clark', 'd.clark@school.ac.uk'), -- Spanish Teacher
(20, 'Daniel', 'Rodriguez', 'd.rodriguez@school.ac.uk'), -- Art Teacher
(21, 'Alexander', 'Lewis', 'a.lewis@school.ac.uk'), -- Student 1
(22, 'Sophia', 'Walker', 's.walker@school.ac.uk'), -- Student 2
(23, 'Ethan', 'Young', 'e.young@school.ac.uk'), -- Student 3
(24, 'Isabella', 'Hall', 'i.hall@school.ac.uk'), -- Student 4
(25, 'Mason', 'Allen', 'm.allen@school.ac.uk'), -- Student 5
(26, 'Olivia', 'King', 'o.king@school.ac.uk'), -- Student 6
(27, 'Lucas', 'Wright', 'l.wright@school.ac.uk'), -- Student 7
(28, 'Ava', 'Scott', 'a.scott@school.ac.uk'), -- Student 8
(29, 'Henry', 'Green', 'h.green@school.ac.uk'), -- Student 9
(30, 'Mia', 'Adams', 'm.adams@school.ac.uk'), -- Student 10
(31, 'Thomas', 'Baker', 't.baker@school.ac.uk'), -- Student 11
(32, 'Ella', 'Gonzalez', 'e.gonzalez@school.ac.uk'), -- Student 12
(33, 'Benjamin', 'Nelson', 'b.nelson@school.ac.uk'), -- Student 13
(34, 'Emily', 'Carter', 'e.carter@school.ac.uk'), -- Student 14
(35, 'Samuel', 'Mitchell', 's.mitchell@school.ac.uk'), -- Student 15
(36, 'Chloe', 'Perez', 'c.perez@school.ac.uk'), -- Student 16
(37, 'Jacob', 'Roberts', 'j.roberts@school.ac.uk'), -- Student 17
(38, 'Sophie', 'Turner', 's.turner@school.ac.uk'), -- Student 18
(39, 'Michael', 'Phillips', 'm.phillips@school.ac.uk'), -- Student 19
(40, 'Grace', 'Campbell', 'g.campbell@school.ac.uk'), -- Student 20
(41, 'Matthew', 'Parker', 'm.parker@school.ac.uk'), -- Student 21
(42, 'Lily', 'Evans', 'l.evans@school.ac.uk'), -- Student 22
(43, 'James', 'Edwards', 'j.edwards@school.ac.uk'), -- Student 23
(44, 'Alyssa', 'Collins', 'a.collins@school.ac.uk'), -- Student 24
(45, 'Joshua', 'Stewart', 'j.stewart@school.ac.uk'), -- Student 25
(46, 'Madison', 'Sanchez', 'm.sanchez@school.ac.uk'), -- Student 26
(47, 'Logan', 'Morris', 'l.morris@school.ac.uk'), -- Student 27
(48, 'Hannah', 'Rogers', 'h.rogers@school.ac.uk'), -- Student 28
(49, 'Andrew', 'Reed', 'a.reed@school.ac.uk'), -- Student 29
(50, 'Amelia', 'Cook', 'a.cook@school.ac.uk'); -- Student 30
INSERT INTO department (id, name, school_id)
VALUES
(1, 'English', 1),
(2, 'Mathematics', 1),
(3, 'Science', 1),
(4, 'Humanities', 1),
(5, 'Languages', 1),
(6, 'Performing Arts', 1),
(7, 'Art & Design', 1),
(8, 'Business & Computing', 1),
(9, 'Admin Team', 1),
(10, 'Leadership Team', 1),
(11, 'Pastoral Team', 1),
(12, 'Facilities', 1);
INSERT INTO job (id, job_title, department_id)
VALUES
(1, 'Headteacher', 10),
(2, 'Deputy Headteacher', 10),
(3, 'Governor', 9),
(4, 'Head of Year', 11),
(5, 'English Teacher', 1),
(6, 'Maths Teacher', 2),
(7, 'Science Teacher', 3),
(8, 'History Teacher', 4),
(9, 'French Teacher', 5),
(10, 'Business Studies Teacher', 8),
(11, 'Caretaker', 12),
(12, 'Head of English', 1),
(13, 'Head of Mathematics', 2),
(14, 'Head of Science', 3),
(15, 'Head of Humanities', 4),
(16, 'Geography Teacher', 4),
(17, 'German Teacher', 5),
(18, 'Head of Languages', 5),
(19, 'Spanish Teacher', 5),
(20, 'Art Teacher', 7);
INSERT INTO staff (id, person_id, school_id)
VALUES
(1, 1, 1), -- James Smith (Headteacher, Governor)
(2, 2, 1), -- Emily Johnson (Governor)
(3, 3, 1), -- Robert Brown (Governor)
(4, 4, 1), -- Sarah Williams (Governor)
(5, 5, 1), -- Michael Jones (Governor)
(6, 6, 1), -- Elizabeth Taylor (Deputy Headteacher)
(7, 7, 1), -- David Wilson (Head of Science Department)
(8, 8, 1), -- Mary Moore (Science Teacher)
(9, 9, 1), -- John Anderson (Science Teacher)
(10, 10, 1), -- Jennifer Thomas (Head of Mathematics Department)
(11, 11, 1), -- Charles Jackson (Maths Teacher)
(12, 12, 1), -- Patricia White (Maths Teacher)
(13, 13, 1), -- Christopher Harris (Head of Humanities)
(14, 14, 1), -- Linda Martin (History Teacher)
(15, 15, 1), -- Barbara Thompson (Geography Teacher)
(16, 16, 1), -- Susan Garcia (French Teacher)
(17, 17, 1), -- Joseph Martinez (German Teacher)
(18, 18, 1), -- William Robinson (Head of Languages)
(19, 19, 1), -- Dorothy Clark (Spanish Teacher)
(20, 20, 1); -- Daniel Rodriguez (Art Teacher)
INSERT INTO staff_job (staff_id, job_id)
VALUES
(1, 1), -- James Smith (Headteacher)
(1, 3), -- James Smith (Governor)
(2, 3), -- Emily Johnson (Governor)
(3, 3), -- Robert Brown (Governor)
(4, 3), -- Sarah Williams (Governor)
(5, 3), -- Michael Jones (Governor)
(6, 2), -- Elizabeth Taylor (Deputy Headteacher)
(6, 20), -- Elizabeth Taylor (Art Teacher)
(7, 14), -- David Wilson (Head of Science)
(7, 7), -- David Wilson (Science Teacher)
(8, 7), -- Mary Moore (Science Teacher)
(9, 7), -- John Anderson (Science Teacher)
(10, 13), -- Jennifer Thomas (Head of Mathematics)
(10, 6), -- Jennifer Thomas (Maths Teacher)
(11, 6), -- Charles Jackson (Maths Teacher)
(12, 6), -- Patricia White (Maths Teacher)
(13, 15), -- Christopher Harris (Head of Humanities)
(13, 16), -- Christopher Harris (Geography Teacher)
(13, 8), -- Christopher Harris (History Teacher)
(14, 8), -- Linda Martin (History Teacher)
(15, 16), -- Barbara Thompson (Geography Teacher)
(16, 9), -- Susan Garcia (French Teacher)
(17, 17), -- Joseph Martinez (German Teacher)
(18, 18), -- William Robinson (Head of Languages)
(18, 9), -- William Robinson (French Teacher)
(19, 19), -- Dorothy Clark (Spanish Teacher)
(20, 20); -- Daniel Rodriguez (Art Teacher)
INSERT INTO governor (id, person_id, job_id, school_id, start_date, end_date)
VALUES
(1, 1, 3, 1, '2022-01-01', '2026-01-01'), -- James Smith (Headteacher, Governor)
(2, 2, 3, 1, '2022-01-01', '2025-01-01'), -- Emily Johnson (Governor)
(3, 3, 3, 1, '2022-01-01', '2024-01-01'), -- Robert Brown (Governor)
(4, 4, 3, 1, '2022-01-01', '2024-01-01'), -- Sarah Williams (Governor)
(5, 5, 3, 1, '2022-01-01', '2023-01-01'); -- Michael Jones (Governor)
INSERT INTO tutor_group (id, group_code)
VALUES
(1, '7A'),
(2, '8A'),
(3, '9A'),
(4, '10A'),
(5, '11A'),
(6, '11B');
INSERT INTO student (id, person_id, school_id, tutor_group_id, year_group)
VALUES
(1, 21, 1, 5, 11), -- Alexander Lewis
(2, 22, 1, 6, 11), -- Sophia Walker
(3, 23, 1, 5, 11), -- Ethan Young
(4, 24, 1, 5, 11), -- Isabella Hall
(5, 25, 1, 6, 11), -- Mason Allen
(6, 26, 1, 5, 11), -- Olivia King
(7, 27, 1, 5, 11), -- Lucas Wright
(8, 28, 1, 6, 11), -- Ava Scott
(9, 29, 1, 6, 11), -- Henry Green
(10, 30, 1, 6, 11), -- Mia Adams
(11, 31, 1, 4, 10), -- Thomas Baker
(12, 32, 1, 4, 10), -- Ella Gonzalez
(13, 33, 1, 4, 10), -- Benjamin Nelson
(14, 34, 1, 4, 10), -- Emily Carter
(15, 35, 1, 4, 10), -- Samuel Mitchell
(16, 36, 1, 3, 9), -- Chloe Perez
(17, 37, 1, 3, 9), -- Jacob Roberts
(18, 38, 1, 3, 9), -- Sophie Turner
(19, 39, 1, 3, 9), -- Michael Phillips
(20, 40, 1, 3, 9), -- Grace Campbell
(21, 41, 1, 2, 8), -- Matthew Parker
(22, 42, 1, 2, 8), -- Lily Evans
(23, 43, 1, 2, 8), -- James Edwards
(24, 44, 1, 2, 8), -- Alyssa Collins
(25, 45, 1, 2, 8), -- Joshua Stewart
(26, 46, 1, 1, 7), -- Madison Sanchez
(27, 47, 1, 1, 7), -- Logan Morris
(28, 48, 1, 1, 7), -- Hannah Rogers
(29, 49, 1, 1, 7), -- Andrew Reed
(30, 50, 1, 1, 7); -- Amelia Cook
INSERT INTO subject (id, subject)
VALUES
(1, 'English Literature'),
(2, 'English Language'),
(3, 'Mathematics'),
(4, 'Science (Double)'),
(5, 'Science (Triple)'),
(6, 'History'),
(7, 'Geography'),
(8, 'French'),
(9, 'German'),
(10, 'Spanish'),
(11, 'Business Studies'),
(12, 'Computing');
INSERT INTO student_subject_choice (student_id, subject_id)
VALUES
-- GCSE subjects, so years 10 and 11 only
-- All students to take English Language, English Literature, Mathematics and do either double or triple science
(1, 1), (1, 2), (1, 3), (1, 4), (1, 6), (1, 8), (1, 11), -- Alexander Lewis
(2, 1), (2, 2), (2, 3), (2, 4), (2, 6), (2, 9), (2, 11), -- Sophia Walker
(3, 1), (3, 2), (3, 3), (3, 4), (3, 6), (3, 10), (3, 12), -- Ethan Young
(4, 1), (4, 2), (4, 3), (4, 5), (4, 6), (4, 8), (4, 12), -- Isabella Hall
(5, 1), (5, 2), (5, 3), (5, 4), (5, 6), (5, 9), (5, 11), -- Mason Allen
(6, 1), (6, 2), (6, 3), (6, 5), (6, 7), (6, 8), (6, 11), -- Olivia King
(7, 1), (7, 2), (7, 3), (7, 5), (7, 7), (7, 8), (7, 11), -- Lucas Wright
(8, 1), (8, 2), (8, 3), (8, 4), (8, 6), (8, 9), (8, 11), -- Ava Scott
(9, 1), (9, 2), (9, 3), (9, 4), (9, 6), (9, 9), (9, 11), -- Henry Green
(10, 1), (10, 2), (10, 3), (10, 4), (10, 7), (10, 10), (10, 12), -- Mia Adams
(11, 1), (11, 2), (11, 3), (11, 4), (11, 7), (11, 10), (11, 12), -- Thomas Baker
(12, 1), (12, 2), (12, 3), (12, 5), (12, 6), (12, 8), (12, 11), -- Ella Gonzalez
(13, 1), (13, 2), (13, 3), (13, 5), (13, 6), (13, 8), (13, 11), -- Benjamin Nelson
(14, 1), (14, 2), (14, 3), (14, 4), (14, 6), (14, 8), (14, 11), -- Emily Carter
(15, 1), (15, 2), (15, 3), (15, 4), (15, 6), (15, 8), (15, 12); -- Samuel Mitchell