dJPMtcbA

1DROP TABLE IF EXISTS person;
2CREATE TABLE person (
3ID INT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE,
4phone_number text NOT NULL,
5firstname text NOT NULL,
6lastname text NOT NULL,
7age INT NOT NULL,
8address text NOT NULL,
9date_of_birth DATE NOT NULL,
10email varchar NOT NULL,
11
12PRIMARY KEY(ID)
13);
14
15DROP TABLE IF EXISTS parent;
16CREATE TABLE parent (
17ID INT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE,
18relationship_to_student text NOT NULL,
19
20PRIMARY KEY(ID)
21);
22
23DROP TABLE IF EXISTS pupil;
24CREATE TABLE pupil (
25    ID INT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE,
26	academic_year float NOT NULL,
27	attendance float NOT NULL,
28    parent_ID INT UNSIGNED NOT NULL,
29
30    PRIMARY KEY(ID),
31    FOREIGN KEY(parent_ID) REFERENCES parent(ID)
32    );
33
34DROP TABLE IF EXISTS staff;
35CREATE TABLE staff (
36ID INT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE,
37
38PRIMARY KEY(ID)
39);
40
41DROP TABLE IF EXISTS subject;
42CREATE TABLE subject (
43ID INT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE,
44exam text NOT NULL,
45name text NOT NULL,
46required_textbooks text NOT NULL,
47required_equipment text NOT NULL,
48staff_ID INT UNSIGNED NOT NULL,
49
50PRIMARY KEY(ID),
51FOREIGN KEY(staff_ID) REFERENCES staff(ID)
52);
53
54DROP TABLE IF EXISTS senior_staff;
55CREATE TABLE senior_staff (
56ID INT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE,
57role text NOT NULL,
58staff_ID INT UNSIGNED NOT NULL,
59
60PRIMARY KEY(ID),
61FOREIGN KEY(staff_ID) REFERENCES staff(ID)
62);
63
64DROP TABLE IF EXISTS office;
65CREATE TABLE office (
66ID INT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE,
67office_hours float NOT NULL,
68staff_ID INT UNSIGNED NOT NULL,
69
70PRIMARY KEY(ID),
71FOREIGN KEY(staff_ID) REFERENCES staff(ID)
72);
73
74DROP TABLE IF EXISTS teaching_staff;
75CREATE TABLE teaching_staff (
76ID INT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE,
77staff_ID INT UNSIGNED NOT NULL,
78
79PRIMARY KEY(ID),
80FOREIGN KEY(staff_ID) REFERENCES staff(ID)
81);
82
83DROP TABLE IF EXISTS teaches;
84CREATE TABLE teaches (
85ID INT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE,
86teaching_hours float NOT NULL,
87
88PRIMARY KEY(ID)
89);
90
91DROP TABLE IF EXISTS room;
92CREATE TABLE room (
93ID INT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE,
94maximum_number_of_students INT NOT NULL,
95room_name text NOT NULL,
96
97PRIMARY KEY(ID)
98);
99
100DROP TABLE IF EXISTS room_type;
101CREATE TABLE room_type (
102ID INT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE,
103description text NOT NULL,
104room_ID INT UNSIGNED NOT NULL,
105
106PRIMARY KEY (ID),
107FOREIGN KEY(room_ID) REFERENCES room(ID)
108);