-
Notifications
You must be signed in to change notification settings - Fork 0
Expand file tree
/
Copy pathuniverse-creation.sql
More file actions
149 lines (138 loc) · 6.3 KB
/
universe-creation.sql
File metadata and controls
149 lines (138 loc) · 6.3 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
create database universe;
create table constellation
(
constellation_id serial primary key,
"name" varchar(100) not null,
number_of_stars int,
number_of_unformed_stars int,
average_distance_from_earth_in_light_years numeric,
greek_name text not null,
circumpolar boolean,
seasonal boolean
);
create table galaxy
(
galaxy_id serial primary key,
name varchar(100) not null,
age_in_millions_of_years int,
number_of_stars_in_millions int,
distance_from_earth_in_light_years numeric,
type text not null,
visible_to_naked_eye boolean,
visible_with_hobby_telescope boolean,
constellation varchar(20)
);
create table star
(
star_id serial primary key,
name varchar(100) not null,
age_in_millions_of_years int,
number_of_planets_orbiting int,
distance_from_earth_in_light_years numeric,
type text not null,
visible_to_naked_eye boolean,
visible_with_hobby_telescope boolean,
galaxy_id int,
constraint galaxy_id_fkey
foreign key(galaxy_id)
references galaxy(galaxy_id)
);
create table planet
(
planet_id serial primary key,
name varchar(100) not null,
age_in_millions_of_years int,
number_of_moons_orbiting int,
distance_from_earth_in_light_years numeric,
type text not null,
visible_to_naked_eye boolean,
visible_with_hobby_telescope boolean,
star_id int,
constraint star_id_fkey
foreign key(star_id)
references star(star_id)
);
create table moon
(
moon_id serial primary key,
name varchar(100) not null,
age_in_millions_of_years int,
number_of_days_to_orbit_planet int,
distance_from_earth_in_light_years numeric,
type text not null,
visible_to_naked_eye boolean,
visible_with_hobby_telescope boolean,
planet_id int,
constraint planet_id_fkey
foreign key(planet_id)
references planet(planet_id)
);
insert into constellation
(name, number_of_stars, number_of_unformed_stars, average_distance_from_earth_in_light_years, greek_name, circumpolar, seasonal)
values
('Aquarius', 42, 3, 500, 'Ὑδροχόος', false, true),
('Cancer', 9, 4, 236, 'Καρκίνος', false, true),
('Leo', 27, 8, 1535, 'Λέων', false, true),
('Libra', 8, 9, 1535, 'Χηλαί', false, true);
insert into galaxy
(name, age_in_millions_of_years, number_of_stars_in_millions, distance_from_earth_in_light_years, type, visible_to_naked_eye, visible_with_hobby_telescope, constellation)
values
('Aquarius Dwarf', 10000, null, 3200000, 'dwarf', true, true, '1'),
('Milky Way', 13600, 100000, 0, 'barred spiral', true, true, '2, 3, 4'),
('Andromeda', 7500, 1000000, 2500000, 'barred spiral', true, true, null),
('Triangulum', 1600, 40000, 2730000, 'spiral', true, true, null),
('Pisces Dwarf', 8000, 100, 18400000, 'irregular dwarf', true, true, null),
('Messier 81', 12000, 250000, 11800000, 'grand design spiral', false, true, null);
insert into star
(name, age_in_millions_of_years, number_of_planets_orbiting, distance_from_earth_in_light_years, type, visible_to_naked_eye, visible_with_hobby_telescope, galaxy_id)
values
('Alpha Aquarii', 53, null, 760, 'single yellow supergiant', true, true, 1),
('Acubens', 600, 0, 170, 'white a-type main-sequence dwarf', true, true, 2),
('Alpha Leonis', 1000, 0, 79, 'quadruple blue-white main-sequence', true, true, 2),
('Alpha Librae', 1100, 0, 76, 'double', true, true, 2),
('Beta Aquarii', 56, 0, 610, 'yellow supergiant', true, true, 2),
('The Sun', 4500, 13, 0.0000158, 'yellow dwarf', true, true, 2);
insert into planet
(name, age_in_millions_of_years, number_of_moons_orbiting, distance_from_earth_in_light_years, type, visible_to_naked_eye, visible_with_hobby_telescope, star_id)
values
('Mercury', 4500, 0, 0.0000060841129, 'terrestrial', true, true, 6),
('Venus', 4500, 0, 0.0000097, 'terrestrial', true, true, 6),
('Earth', 4540, 1, 0, 'terrestrial', null, null, 6),
('Mars', 4500, 2, 0.000042, 'terrestrial', true, true, 6),
('Jupiter', 4570, 95, 0.00008233217279125351, 'gas giant', true, true, 6),
('Saturn', 4540, 146, 0.0001505453985955772, 'gas giant', true, true, 6),
('Uranus', 4540, 27, 0.0003027918751413869, 'gas giant', false, true, 6),
('Neptune', 4540, 14, 0.00047460074811487044, 'gas giant', false, true, 6),
('Ceres', 4500, 0, 0.0000437, 'Dwarf', false, true, 6),
('Pluto', 4600, 5, 0.0000005, 'Dwarf', false, true, 6),
('Haumea', 4500, 2, 0.000771, 'Dwarf', false, true, 6),
('Makemake', 4500, 1, 0.000841, 'Dwarf', false, false, 6),
('Eris', 4500, 1, 0.00000153, 'Dwarf', false, false, 6);
insert into moon
(name, age_in_millions_of_years, number_of_days_to_orbit_planet, distance_from_earth_in_light_years, type, visible_to_naked_eye, visible_with_hobby_telescope, planet_id)
values
('Moon', 4460, 27, 0.00000004063, 'natural satellite', true, true, 3),
('Deimos', 4500, 1, 0.0000238, 'natural satellite', false, true, 4),
('Phobos', 4500, 0, 0.0000238, 'natural satellite', false, true, 4),
('Callisto', 4500, 17, 0.0000664, 'natural satellite', true, true, 5),
('Europa', 4500, 6, 0.0000824, 'natural satellite', false, true, 5),
('Ganymede', 4500, 7, 0.0000664, 'natural satellite', false, true, 5),
('Io', 4500, 2, 0.0000422, 'natural satellite', false, true, 5),
('Atlas', 4500, 1, 0.0001268, 'natural satellite', false, false, 6),
('Calypso', 4500, 2, 0.0001505, 'natural satellite', false, false, 6),
('Janus', 4500, 1, 0.000158, 'natural satellite', false, false, 6),
('Prometheus', 4500, 1, 0.0001505, 'natural satellite', false, false, 6),
('Ariel', 4500, 3, 0.000271, 'natural satellite', false, false, 7),
('Oberon', 4500, 14, 0.000303, 'natural satellite', false, true, 7),
('Galatea', 4500, 0, 0.000475, 'natural satellite', false, false, 8),
('Proteus', 4500, 1, 0.000454, 'natural satellite', false, false, 8),
('Hydra', 4500, 38, 0.00079, 'natural satellite', false, false, 10),
('Styx', 4500, 20, 0.0005008, 'natural satellite', false, false, 10),
('Dysnomia', 4500, 16, 0.001524, 'natural satellite', false, false, 13),
('Namaka', null, 18, 0.000682, 'natural satellite', false, false, 11),
('Hiiaka', null, 49, 0.000676, 'natural satellite', false, false, 11);
alter table constellation add constraint constellation_unique unique(name);
alter table galaxy add constraint galaxy_unique unique(name);
alter table star add constraint star_unique unique(name);
alter table planet add constraint planet_unique unique(name);
alter table moon add constraint moon_unique unique(name);