-
Notifications
You must be signed in to change notification settings - Fork 0
/
create_tables.sql
47 lines (42 loc) · 1.78 KB
/
create_tables.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
create table schools (
school_id tinyint not null,
school_name varchar(50) not null,
school_short_name varchar(3) not null
);
ALTER TABLE schools ADD PRIMARY KEY(school_id);
create table school_capacity (
school_id tinyint not null,
year smallint not null,
capacity tinyint not null
);
ALTER TABLE school_capacity ADD PRIMARY KEY(school_id, year);
ALTER TABLE school_capacity ADD FOREIGN KEY (school_id) references schools(school_id);
create table choices (
year smallint not null,
choice1 tinyint,
choice2 tinyint,
choice3 tinyint,
choice4 tinyint,
choice5 tinyint,
choice6 tinyint,
choice7 tinyint,
choice8 tinyint,
choice9 tinyint,
choice10 tinyint,
choice11 tinyint,
choice12 tinyint,
assigned_school tinyint
);
ALTER TABLE choices ADD FOREIGN KEY (choice1) references schools(school_id);
ALTER TABLE choices ADD FOREIGN KEY (choice2) references schools(school_id);
ALTER TABLE choices ADD FOREIGN KEY (choice3) references schools(school_id);
ALTER TABLE choices ADD FOREIGN KEY (choice4) references schools(school_id);
ALTER TABLE choices ADD FOREIGN KEY (choice5) references schools(school_id);
ALTER TABLE choices ADD FOREIGN KEY (choice6) references schools(school_id);
ALTER TABLE choices ADD FOREIGN KEY (choice7) references schools(school_id);
ALTER TABLE choices ADD FOREIGN KEY (choice8) references schools(school_id);
ALTER TABLE choices ADD FOREIGN KEY (choice9) references schools(school_id);
ALTER TABLE choices ADD FOREIGN KEY (choice10) references schools(school_id);
ALTER TABLE choices ADD FOREIGN KEY (choice11) references schools(school_id);
ALTER TABLE choices ADD FOREIGN KEY (choice12) references schools(school_id);
ALTER TABLE choices ADD FOREIGN KEY (assigned_school) references schools(school_id);