-
Notifications
You must be signed in to change notification settings - Fork 1
/
Create.sql
205 lines (134 loc) · 6.43 KB
/
Create.sql
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
CREATE TABLE brands (
id SERIAL NOT NULL,
name VARCHAR(64)
);
ALTER TABLE brands ADD CONSTRAINT PK_brands PRIMARY KEY (id);
CREATE TABLE genre (
id SERIAL NOT NULL,
genre VARCHAR(64) NOT NULL
);
ALTER TABLE genre ADD CONSTRAINT PK_genre PRIMARY KEY (id);
CREATE TABLE instruments (
id SERIAL NOT NULL,
name VARCHAR(64) NOT NULL
);
ALTER TABLE instruments ADD CONSTRAINT PK_instruments PRIMARY KEY (id);
CREATE TABLE person (
id SERIAL NOT NULL,
first_name VARCHAR(128) NOT NULL,
last_name VARCHAR(128) NOT NULL,
ssn VARCHAR(12) NOT NULL,
email VARCHAR(128) NOT NULL,
phone VARCHAR(32) NOT NULL,
street VARCHAR(64) NOT NULL,
zip VARCHAR(64) NOT NULL,
town VARCHAR(64) NOT NULL
);
ALTER TABLE person ADD CONSTRAINT PK_person PRIMARY KEY (id);
CREATE TABLE rentable_instruments (
id SERIAL NOT NULL,
instrument_id INT NOT NULL,
brand_id INT NOT NULL,
fee INT NOT NULL
);
ALTER TABLE rentable_instruments ADD CONSTRAINT PK_rentable_instruments PRIMARY KEY (id);
CREATE TABLE skill_level (
id SERIAL NOT NULL,
level VARCHAR(32) NOT NULL
);
ALTER TABLE skill_level ADD CONSTRAINT PK_skill_level PRIMARY KEY (id);
CREATE TABLE student (
id SERIAL NOT NULL,
enrolled BIT(1) NOT NULL,
parent_email VARCHAR(128) NOT NULL,
parent_phone VARCHAR(32) NOT NULL,
family_id INT NOT NULL,
sibling_discount INT NOT NULL,
person_id INT NOT NULL
);
ALTER TABLE student ADD CONSTRAINT PK_student PRIMARY KEY (id);
CREATE TABLE type_of_lesson (
id SERIAL NOT NULL,
type VARCHAR(64) NOT NULL
);
ALTER TABLE type_of_lesson ADD CONSTRAINT PK_type_of_lesson PRIMARY KEY (id);
CREATE TABLE fees_and_salary (
id SERIAL NOT NULL,
skill_id INT,
type_id INT NOT NULL,
fee INT NOT NULL,
salary INT NOT NULL
);
ALTER TABLE fees_and_salary ADD CONSTRAINT PK_fees_and_salary PRIMARY KEY (id);
CREATE TABLE instructor (
id SERIAL NOT NULL,
can_teach_ensemble BIT(1) NOT NULL,
person_id INT NOT NULL
);
ALTER TABLE instructor ADD CONSTRAINT PK_instructor PRIMARY KEY (id);
CREATE TABLE instrument_students (
student_id INT NOT NULL,
instrument_id INT NOT NULL,
skill_id INT NOT NULL
);
ALTER TABLE instrument_students ADD CONSTRAINT PK_instrument_students PRIMARY KEY (student_id,instrument_id,skill_id);
CREATE TABLE instrument_instructor (
instructor_id INT NOT NULL,
instrument_id INT NOT NULL
);
ALTER TABLE instrument_instructor ADD CONSTRAINT PK_instrument_instructor PRIMARY KEY (instructor_id,instrument_id);
CREATE TABLE rented_instrument (
id SERIAL NOT NULL,
rentable_instrument_id INT NOT NULL,
student_id INT NOT NULL,
lease_start DATE NOT NULL,
lease_end DATE NOT NULL
);
ALTER TABLE rented_instrument ADD CONSTRAINT PK_rented_instrument PRIMARY KEY (id);
CREATE TABLE schedule (
id SERIAL NOT NULL,
start_time TIME NOT NULL,
end_time TIME NOT NULL,
day DATE NOT NULL,
instructor_id INT NOT NULL,
fee_and_sal_id INT NOT NULL,
instrument_id INT
);
ALTER TABLE schedule ADD CONSTRAINT PK_schedule PRIMARY KEY (id);
CREATE TABLE schedule_student (
student_id INT NOT NULL,
lesson_id INT NOT NULL
);
ALTER TABLE schedule_student ADD CONSTRAINT PK_schedule_student PRIMARY KEY (student_id,lesson_id);
CREATE TABLE group_lesson (
lesson_id INT NOT NULL,
min_students INT NOT NULL,
max_students INT NOT NULL
);
ALTER TABLE group_lesson ADD CONSTRAINT PK_group_lesson PRIMARY KEY (lesson_id);
CREATE TABLE ensemble (
lesson_id INT NOT NULL,
genre_id INT NOT NULL
);
ALTER TABLE ensemble ADD CONSTRAINT PK_ensemble PRIMARY KEY (lesson_id,genre_id);
ALTER TABLE rentable_instruments ADD CONSTRAINT FK_rentable_instruments_0 FOREIGN KEY (instrument_id) REFERENCES instruments (id) ON DELETE CASCADE;
ALTER TABLE rentable_instruments ADD CONSTRAINT FK_rentable_instruments_1 FOREIGN KEY (brand_id) REFERENCES brands (id) ON DELETE CASCADE;
ALTER TABLE student ADD CONSTRAINT FK_student_0 FOREIGN KEY (person_id) REFERENCES person (id) ON DELETE CASCADE;
ALTER TABLE fees_and_salary ADD CONSTRAINT FK_fees_and_salary_0 FOREIGN KEY (skill_id) REFERENCES skill_level (id) ON DELETE CASCADE;
ALTER TABLE fees_and_salary ADD CONSTRAINT FK_fees_and_salary_1 FOREIGN KEY (type_id) REFERENCES type_of_lesson (id) ON DELETE CASCADE;
ALTER TABLE instructor ADD CONSTRAINT FK_instructor_0 FOREIGN KEY (person_id) REFERENCES person (id) ON DELETE CASCADE;
ALTER TABLE instrument_students ADD CONSTRAINT FK_instrument_students_0 FOREIGN KEY (student_id) REFERENCES student (id) ON DELETE CASCADE;
ALTER TABLE instrument_students ADD CONSTRAINT FK_instrument_students_1 FOREIGN KEY (instrument_id) REFERENCES instruments (id) ON DELETE CASCADE;
ALTER TABLE instrument_students ADD CONSTRAINT FK_instrument_students_2 FOREIGN KEY (skill_id) REFERENCES skill_level (id) ON DELETE CASCADE;
ALTER TABLE instrument_instructor ADD CONSTRAINT FK_instrument_instructor_0 FOREIGN KEY (instructor_id) REFERENCES instructor (id) ON DELETE CASCADE;
ALTER TABLE instrument_instructor ADD CONSTRAINT FK_instrument_instructor_1 FOREIGN KEY (instrument_id) REFERENCES instruments (id) ON DELETE CASCADE;
ALTER TABLE rented_instrument ADD CONSTRAINT FK_rented_instrument_0 FOREIGN KEY (rentable_instrument_id) REFERENCES rentable_instruments (id) ON DELETE CASCADE;
ALTER TABLE rented_instrument ADD CONSTRAINT FK_rented_instrument_1 FOREIGN KEY (student_id) REFERENCES student (id) ON DELETE CASCADE;
ALTER TABLE schedule ADD CONSTRAINT FK_schedule_0 FOREIGN KEY (instructor_id) REFERENCES instructor (id) ON DELETE CASCADE;
ALTER TABLE schedule ADD CONSTRAINT FK_schedule_1 FOREIGN KEY (fee_and_sal_id) REFERENCES fees_and_salary (id) ON DELETE CASCADE;
ALTER TABLE schedule ADD CONSTRAINT FK_schedule_2 FOREIGN KEY (instrument_id) REFERENCES instruments (id) ON DELETE CASCADE;
ALTER TABLE schedule_student ADD CONSTRAINT FK_schedule_student_0 FOREIGN KEY (student_id) REFERENCES student (id) ON DELETE CASCADE;
ALTER TABLE schedule_student ADD CONSTRAINT FK_schedule_student_1 FOREIGN KEY (lesson_id) REFERENCES schedule (id) ON DELETE CASCADE;
ALTER TABLE group_lesson ADD CONSTRAINT FK_group_lesson_0 FOREIGN KEY (lesson_id) REFERENCES schedule (id) ON DELETE CASCADE;
ALTER TABLE ensemble ADD CONSTRAINT FK_ensemble_0 FOREIGN KEY (lesson_id) REFERENCES group_lesson (lesson_id) ON DELETE CASCADE;
ALTER TABLE ensemble ADD CONSTRAINT FK_ensemble_1 FOREIGN KEY (genre_id) REFERENCES genre (id) ON DELETE CASCADE;