Celestial Bodies Database - Build a Celestial Bodies Database

someone how to solve this




this is the code

CREATE DATABASE universe; \c universe

CREATE TABLE galaxy (
galaxy_id bigserial GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
name VARCHAR(50) UNIQUE,
planet_types INT,
age_in_millions_of_years bigserial,
galaxy_types INT,
has_life BOOLEAN
);

CREATE TABLE star (
star_id bigserial GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
name VARCHAR(50) UNIQUE,
galaxy_id bigserial,
small_kite INT,
has_life BOOLEAN,
galaxy_types INT,
FOREIGN KEY (galaxy_id) REFERENCES galaxy(galaxy_id)
);

CREATE TABLE planet (
planet_id SERIAL PRIMARY KEY,
name VARCHAR(50) UNIQUE,
star_id bigserial,
has_life BOOLEAN,
Earth text,
Mars text,
Venus text,
Mercury text,
Jupiter text,
Saturn text,
Uranus text,
Neptune text,
FOREIGN KEY (star_id) REFERENCES star(star_id)
);

CREATE TABLE moon (
moon_id SERIAL PRIMARY KEY,
name VARCHAR(50) UNIQUE,
planet_id bigserial,
Phobos text,
Deimos text,
Ganymede text,
Callisto text,
Io text,
Europa text,
Titan text,
Rhea text,
Iapetus text,
Dione text,
Tethys text,
Mimas text,
Enceladus text,
Miranda text,
Ariel text,
Umbriel text,
Titania text,
FOREIGN KEY (planet_id) REFERENCES planet(planet_id)
);

CREATE TABLE small_kite (
small_kite_id SERIAL PRIMARY KEY,
name VARCHAR(50) UNIQUE,
FOREIGN KEY (small_kite_id) REFERENCES small_kite(small_kite_id)
);

– Insertar datos de ejemplo

INSERT INTO galaxy (name, planet_types, age_in_millions_of_years, galaxy_types, has_life)
VALUES
(‘Via Láctea’, 13610),
(‘Andrómeda’, 10100),
(‘Gran Nube de Magallanes’, 14000),
(‘Pequeña Nube de Magallanes’, 7000),
(‘Triángulo’, 6000),
(‘Osa Mayor’, 13000);

INSERT INTO star (name, galaxy_id, has_life, galaxy_types)
VALUES
(‘Sun’, 1, 0),
(‘Proxima Centauri’, 1, 4.2 ),
(‘Alpha Centauri A’, 1, 4.37),
(‘Alpha Centauri B’, 1, 4.37),
(‘Barnard’s Star’, 1, 6),
(‘Wolf 359’, 1, 7.8);

INSERT INTO planet (name, star_id, has_life, Earth, Mars, Venus, Mercury, Jupiter, Saturn, Uranus, Neptune)
VALUES
(‘Earth’, 1, true),
(‘Mars’, 1, false),
(‘Venus’, 1, false),
(‘Mercury’, 1, false),
(‘Jupiter’, 1, false),
(‘Saturn’, 1, false),
(‘Uranus’, 1, false),
(‘Neptune’, 1, false),
(‘Proxima Centauri b’, 2, false),
(‘Proxima Centauri c’, 2, false),
(‘Gliese 163 c’, 3, false),
(‘KOI-3010.01’, 4, false);

INSERT INTO moon (name, planet_id, Phobos, Deimos, Ganymede, Callisto, Io, Europa, Titan, Rhea, Iapetus, Dione, Tethys, Mimas, Enceladus, Miranda, Ariel, Umbriel, Titania)
VALUES
(‘The Moon’, 1),
(‘Phobos’, 2),
(‘Deimos’, 2),
(‘Ganymede’, 5),
(‘Callisto’, 5),
(‘Io’, 5),
(‘Europa’, 5),
(‘Titan’, 6),
(‘Rhea’, 6),
(‘Iapetus’, 6),
(‘Dione’, 6),
(‘Tethys’, 6),
(‘Mimas’, 6),
(‘Enceladus’, 6),
(‘Miranda’, 7),
(‘Ariel’, 7),
(‘Umbriel’, 7),
(‘Titania’, 7),
(‘Oberon’, 7),
(‘Triton’, 8);

INSERT INTO small_kite (name)
VALUES
(‘Halley’),
(‘Hale-Bopp’),
(‘Hyakutake’);

We don’t provide solutions, but if you ask specific questions we might be able to help you figure it out.

as above me said, you should ask specific questions that people here might be able to guide you on thinking & finding the solutions, instead of telling you the solutions outright. Based on what you’ve done though, do work on the requirements, one at a time.