Celestial Bodies Database

Hello, trying to finish this project but got stuck with the last two steps. Here is what so far.
This also needs improvements so any suggestions to correct this if needed plus help for the last two steps. regards

--
-- PostgreSQL database dump
--

-- Dumped from database version 12.9 (Ubuntu 12.9-2.pgdg20.04+1)
-- Dumped by pg_dump version 12.9 (Ubuntu 12.9-2.pgdg20.04+1)

SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SELECT pg_catalog.set_config('search_path', '', false);
SET check_function_bodies = false;
SET xmloption = content;
SET client_min_messages = warning;
SET row_security = off;

DROP DATABASE universe;
--
-- Name: universe; Type: DATABASE; Schema: -; Owner: freecodecamp
--

CREATE DATABASE universe WITH TEMPLATE = template0 ENCODING = 'UTF8' LC_COLLATE = 'C.UTF-8' LC_CTYPE = 'C.UTF-8';


ALTER DATABASE universe OWNER TO freecodecamp;

\connect universe

SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SELECT pg_catalog.set_config('search_path', '', false);
SET check_function_bodies = false;
SET xmloption = content;
SET client_min_messages = warning;
SET row_security = off;

SET default_tablespace = '';

SET default_table_access_method = heap;

--
-- Name: galaxy; Type: TABLE; Schema: public; Owner: freecodecamp
--

CREATE TABLE public.galaxy (
    name character varying(30) NOT NULL,
    distance_km numeric,
    gal_id integer NOT NULL,
    hot boolean,
    life boolean,
    size_km integer,
    description text,
    age integer,
    num_sum integer
);


ALTER TABLE public.galaxy OWNER TO freecodecamp;

--
-- Name: moon; Type: TABLE; Schema: public; Owner: freecodecamp
--

CREATE TABLE public.moon (
    name character varying(30) NOT NULL,
    distance_km numeric,
    moo_id integer NOT NULL,
    hot boolean,
    life boolean,
    size_km integer,
    description text,
    num_sun integer,
    age integer
);


ALTER TABLE public.moon OWNER TO freecodecamp;

--
-- Name: planet; Type: TABLE; Schema: public; Owner: freecodecamp
--

CREATE TABLE public.planet (
    name character varying(30) NOT NULL,
    distance_km numeric,
    pla_id integer NOT NULL,
    hot boolean,
    life boolean,
    size_km integer,
    description text,
    age integer,
    num_sum integer
);


ALTER TABLE public.planet OWNER TO freecodecamp;

--
-- Name: star; Type: TABLE; Schema: public; Owner: freecodecamp
--

CREATE TABLE public.star (
    name character varying(30) NOT NULL,
    distance_km numeric,
    sta_id integer NOT NULL,
    hot boolean,
    life boolean,
    size_km integer,
    description text,
    num_sum integer,
    age integer
);


ALTER TABLE public.star OWNER TO freecodecamp;

--
-- Name: sun; Type: TABLE; Schema: public; Owner: freecodecamp
--

CREATE TABLE public.sun (
    name character varying(30) NOT NULL,
    distance_km numeric,
    sun_id integer NOT NULL,
    hot boolean,
    life boolean,
    size_km integer,
    description text,
    age integer,
    num_sun integer
);


ALTER TABLE public.sun OWNER TO freecodecamp;

--
-- Data for Name: galaxy; Type: TABLE DATA; Schema: public; Owner: freecodecamp
--

INSERT INTO public.galaxy VALUES ('gal1', 1000, 1, true, true, 1000, NULL, NULL, NULL);
INSERT INTO public.galaxy VALUES ('gal2', 1000, 2, false, true, 1000, NULL, NULL, NULL);
INSERT INTO public.galaxy VALUES ('gal3', 1000, 3, false, false, 1000, NULL, NULL, NULL);
INSERT INTO public.galaxy VALUES ('gal4', 1000, 4, true, false, 1000, NULL, NULL, NULL);
INSERT INTO public.galaxy VALUES ('gal5', 1000, 5, true, false, 1000, NULL, NULL, NULL);
INSERT INTO public.galaxy VALUES ('gal6', 1000, 6, true, true, 1000, NULL, NULL, NULL);


--
-- Data for Name: moon; Type: TABLE DATA; Schema: public; Owner: freecodecamp
--

INSERT INTO public.moon VALUES ('moo1', 1000, 1, true, true, 1000, NULL, NULL, NULL);
INSERT INTO public.moon VALUES ('moo2', 1000, 2, false, true, 1000, NULL, NULL, NULL);
INSERT INTO public.moon VALUES ('moo3', 1000, 3, false, false, 1000, NULL, NULL, NULL);
INSERT INTO public.moon VALUES ('moo4', 1000, 4, true, false, 1000, NULL, NULL, NULL);
INSERT INTO public.moon VALUES ('moo5', 1000, 5, true, true, 1000, NULL, NULL, NULL);
INSERT INTO public.moon VALUES ('moo6', 1000, 6, false, true, 1000, NULL, NULL, NULL);
INSERT INTO public.moon VALUES ('moo7', 1000, 7, false, false, 1000, NULL, NULL, NULL);
INSERT INTO public.moon VALUES ('moo8', 1000, 8, false, true, 1000, NULL, NULL, NULL);
INSERT INTO public.moon VALUES ('moo9', 1000, 9, true, true, 1000, NULL, NULL, NULL);
INSERT INTO public.moon VALUES ('moo10', 1000, 10, true, false, 1000, NULL, NULL, NULL);
INSERT INTO public.moon VALUES ('moo11', 1000, 11, false, false, 1000, NULL, NULL, NULL);
INSERT INTO public.moon VALUES ('moo12', 1000, 12, true, false, 1000, NULL, NULL, NULL);
INSERT INTO public.moon VALUES ('moo13', 1000, 13, false, true, 1000, NULL, NULL, NULL);
INSERT INTO public.moon VALUES ('moo14', 1000, 14, true, true, 1000, NULL, NULL, NULL);
INSERT INTO public.moon VALUES ('moo15', 1000, 15, false, true, 1000, NULL, NULL, NULL);
INSERT INTO public.moon VALUES ('moo16', 1000, 16, false, false, 1000, NULL, NULL, NULL);
INSERT INTO public.moon VALUES ('moo17', 1000, 17, false, true, 1000, NULL, NULL, NULL);
INSERT INTO public.moon VALUES ('moo18', 1000, 18, true, true, 1000, NULL, NULL, NULL);
INSERT INTO public.moon VALUES ('moo19', 1000, 19, true, false, 1000, NULL, NULL, NULL);
INSERT INTO public.moon VALUES ('moo20', 1000, 20, false, false, 1000, NULL, NULL, NULL);


--
-- Data for Name: planet; Type: TABLE DATA; Schema: public; Owner: freecodecamp
--

INSERT INTO public.planet VALUES ('pla1', 1000, 1, false, false, 1000, NULL, NULL, NULL);
INSERT INTO public.planet VALUES ('pla2', 1000, 2, true, false, 1000, NULL, NULL, NULL);
INSERT INTO public.planet VALUES ('pla3', 1000, 3, true, true, 1000, NULL, NULL, NULL);
INSERT INTO public.planet VALUES ('pla4', 1000, 4, false, true, 1000, NULL, NULL, NULL);
INSERT INTO public.planet VALUES ('pla5', 1000, 5, true, true, 1000, NULL, NULL, NULL);
INSERT INTO public.planet VALUES ('pla6', 1000, 6, true, false, 1000, NULL, NULL, NULL);
INSERT INTO public.planet VALUES ('pla7', 1000, 7, false, false, 1000, NULL, NULL, NULL);
INSERT INTO public.planet VALUES ('pla8', 1000, 8, false, true, 1000, NULL, NULL, NULL);
INSERT INTO public.planet VALUES ('pla9', 1000, 9, true, true, 1000, NULL, NULL, NULL);
INSERT INTO public.planet VALUES ('pla10', 1000, 10, false, false, 1000, NULL, NULL, NULL);
INSERT INTO public.planet VALUES ('pla11', 1000, 11, false, true, 1000, NULL, NULL, NULL);
INSERT INTO public.planet VALUES ('pla12', 1000, 12, true, true, 1000, NULL, NULL, NULL);


--
-- Data for Name: star; Type: TABLE DATA; Schema: public; Owner: freecodecamp
--

INSERT INTO public.star VALUES ('sta1', 1000, 1, true, true, 1000, NULL, NULL, NULL);
INSERT INTO public.star VALUES ('sta2', 1000, 2, true, false, 1000, NULL, NULL, NULL);
INSERT INTO public.star VALUES ('sta3', 1000, 3, false, false, 1000, NULL, NULL, NULL);
INSERT INTO public.star VALUES ('sta4', 1000, 4, true, false, 1000, NULL, NULL, NULL);
INSERT INTO public.star VALUES ('sta5', 1000, 5, true, true, 1000, NULL, NULL, NULL);
INSERT INTO public.star VALUES ('sta6', 1000, 6, false, false, 1000, NULL, NULL, NULL);


--
-- Data for Name: sun; Type: TABLE DATA; Schema: public; Owner: freecodecamp
--

INSERT INTO public.sun VALUES ('sun1', 1000, 1, false, true, 1000, NULL, NULL, NULL);
INSERT INTO public.sun VALUES ('sun2', 1000, 2, false, false, 1000, NULL, NULL, NULL);
INSERT INTO public.sun VALUES ('sun3', 1000, 3, false, false, 1000, NULL, NULL, NULL);


--
-- Name: galaxy galaxy_name_key; Type: CONSTRAINT; Schema: public; Owner: freecodecamp
--

ALTER TABLE ONLY public.galaxy
    ADD CONSTRAINT galaxy_name_key UNIQUE (name);


--
-- Name: galaxy galaxy_pkey; Type: CONSTRAINT; Schema: public; Owner: freecodecamp
--

ALTER TABLE ONLY public.galaxy
    ADD CONSTRAINT galaxy_pkey PRIMARY KEY (gal_id);


--
-- Name: moon moon_name_key; Type: CONSTRAINT; Schema: public; Owner: freecodecamp
--

ALTER TABLE ONLY public.moon
    ADD CONSTRAINT moon_name_key UNIQUE (name);


--
-- Name: moon moon_pkey; Type: CONSTRAINT; Schema: public; Owner: freecodecamp
--

ALTER TABLE ONLY public.moon
    ADD CONSTRAINT moon_pkey PRIMARY KEY (moo_id);


--
-- Name: planet planet_name_key; Type: CONSTRAINT; Schema: public; Owner: freecodecamp
--

ALTER TABLE ONLY public.planet
    ADD CONSTRAINT planet_name_key UNIQUE (name);


--
-- Name: planet planet_pkey; Type: CONSTRAINT; Schema: public; Owner: freecodecamp
--

ALTER TABLE ONLY public.planet
    ADD CONSTRAINT planet_pkey PRIMARY KEY (pla_id);


--
-- Name: star star_name_key; Type: CONSTRAINT; Schema: public; Owner: freecodecamp
--

ALTER TABLE ONLY public.star
    ADD CONSTRAINT star_name_key UNIQUE (name);


--
-- Name: star star_pkey; Type: CONSTRAINT; Schema: public; Owner: freecodecamp
--

ALTER TABLE ONLY public.star
    ADD CONSTRAINT star_pkey PRIMARY KEY (sta_id);


--
-- Name: sun sun_name_key; Type: CONSTRAINT; Schema: public; Owner: freecodecamp
--

ALTER TABLE ONLY public.sun
    ADD CONSTRAINT sun_name_key UNIQUE (name);


--
-- Name: sun sun_pkey; Type: CONSTRAINT; Schema: public; Owner: freecodecamp
--

ALTER TABLE ONLY public.sun
    ADD CONSTRAINT sun_pkey PRIMARY KEY (sun_id);


--
-- Name: galaxy galaxy_gal_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: freecodecamp
--

ALTER TABLE ONLY public.galaxy
    ADD CONSTRAINT galaxy_gal_id_fkey FOREIGN KEY (gal_id) REFERENCES public.galaxy(gal_id);


--
-- Name: moon moon_moo_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: freecodecamp
--

ALTER TABLE ONLY public.moon
    ADD CONSTRAINT moon_moo_id_fkey FOREIGN KEY (moo_id) REFERENCES public.moon(moo_id);


--
-- Name: planet planet_pla_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: freecodecamp
--

ALTER TABLE ONLY public.planet
    ADD CONSTRAINT planet_pla_id_fkey FOREIGN KEY (pla_id) REFERENCES public.planet(pla_id);


--
-- Name: star star_sta_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: freecodecamp
--

ALTER TABLE ONLY public.star
    ADD CONSTRAINT star_sta_id_fkey FOREIGN KEY (sta_id) REFERENCES public.galaxy(gal_id);


--
-- Name: star star_sta_id_fkey1; Type: FK CONSTRAINT; Schema: public; Owner: freecodecamp
--

ALTER TABLE ONLY public.star
    ADD CONSTRAINT star_sta_id_fkey1 FOREIGN KEY (sta_id) REFERENCES public.galaxy(gal_id);


--
-- Name: star star_sta_id_fkey2; Type: FK CONSTRAINT; Schema: public; Owner: freecodecamp
--

ALTER TABLE ONLY public.star
    ADD CONSTRAINT star_sta_id_fkey2 FOREIGN KEY (sta_id) REFERENCES public.star(sta_id);


--
-- Name: sun sun_sun_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: freecodecamp
--

ALTER TABLE ONLY public.sun
    ADD CONSTRAINT sun_sun_id_fkey FOREIGN KEY (sun_id) REFERENCES public.sun(sun_id);


--
-- PostgreSQL database dump complete
--

what are the last two steps please?

sorry forgot to mention. Here are the last two steps:

  • Each primary key column should follow the naming convention table_name_id. For example, the moon table should have a primary key column named moon_id

  • Each foreign key column should have the same name as the column it is referencing
    regards

Would you be able to list out your schema in the psql terminal instead? (using /d and /l for each table) and then provide the output?
I find it easier to read…

Dear hbar1st,
please see below:

universe=> \d moon
Table “public.moon”
±------------±----------------------±----------±---------±--------+
| Column | Type | Collation | Nullable | Default |
±------------±----------------------±----------±---------±--------+
| name | character varying(30) | | not null | |
| distance_km | numeric | | | |
| moo_id | integer | | not null | |
| hot | boolean | | | |
| life | boolean | | | |
| size_km | integer | | | |
| description | text | | | |
| num_sun | integer | | | |
| age | integer | | | |
±------------±----------------------±----------±---------±--------+
Indexes:
“moon_pkey” PRIMARY KEY, btree (moo_id)
“moon_name_key” UNIQUE CONSTRAINT, btree (name)
Foreign-key constraints:
“moon_moo_id_fkey” FOREIGN KEY (moo_id) REFERENCES moon(moo_id)
Referenced by:
TABLE “moon” CONSTRAINT “moon_moo_id_fkey” FOREIGN KEY (moo_id) REFERENCES moon(moo_id)

universe=> \d sun
Table “public.sun”
±------------±----------------------±----------±---------±--------+
| Column | Type | Collation | Nullable | Default |
±------------±----------------------±----------±---------±--------+
| name | character varying(30) | | not null | |
| distance_km | numeric | | | |
| sun_id | integer | | not null | |
| hot | boolean | | | |
| life | boolean | | | |
| size_km | integer | | | |
| description | text | | | |
| age | integer | | | |
| num_sun | integer | | | |
±------------±----------------------±----------±---------±--------+
Indexes:
“sun_pkey” PRIMARY KEY, btree (sun_id)
“sun_name_key” UNIQUE CONSTRAINT, btree (name)
Foreign-key constraints:
“sun_sun_id_fkey” FOREIGN KEY (sun_id) REFERENCES sun(sun_id)
Referenced by:
TABLE “sun” CONSTRAINT “sun_sun_id_fkey” FOREIGN KEY (sun_id) REFERENCES sun(sun_id)

universe=> \d galaxy;
Table “public.galaxy”
±------------±----------------------±----------±---------±--------+
| Column | Type | Collation | Nullable | Default |
±------------±----------------------±----------±---------±--------+
| name | character varying(30) | | not null | |
| distance_km | numeric | | | |
| gal_id | integer | | not null | |
| hot | boolean | | | |
| life | boolean | | | |
| size_km | integer | | | |
| description | text | | | |
| age | integer | | | |
| num_sum | integer | | | |
±------------±----------------------±----------±---------±--------+
Indexes:
“galaxy_pkey” PRIMARY KEY, btree (gal_id)
“galaxy_name_key” UNIQUE CONSTRAINT, btree (name)
Foreign-key constraints:
“galaxy_gal_id_fkey” FOREIGN KEY (gal_id) REFERENCES galaxy(gal_id)
Referenced by:
TABLE “galaxy” CONSTRAINT “galaxy_gal_id_fkey” FOREIGN KEY (gal_id) REFERENCES galaxy(gal_id)
TABLE “star” CONSTRAINT “star_sta_id_fkey” FOREIGN KEY (sta_id) REFERENCES galaxy(gal_id)
TABLE “star” CONSTRAINT “star_sta_id_fkey1” FOREIGN KEY (sta_id) REFERENCES galaxy(gal_id)

universe=> \d star;
Table “public.star”
±------------±----------------------±----------±---------±--------+
| Column | Type | Collation | Nullable | Default |
±------------±----------------------±----------±---------±--------+
| name | character varying(30) | | not null | |
| distance_km | numeric | | | |
| sta_id | integer | | not null | |
| hot | boolean | | | |
| life | boolean | | | |
| size_km | integer | | | |
| description | text | | | |
| num_sum | integer | | | |
| age | integer | | | |
±------------±----------------------±----------±---------±--------+
Indexes:
“star_pkey” PRIMARY KEY, btree (sta_id)
“star_name_key” UNIQUE CONSTRAINT, btree (name)
Foreign-key constraints:
“star_sta_id_fkey” FOREIGN KEY (sta_id) REFERENCES galaxy(gal_id)
“star_sta_id_fkey1” FOREIGN KEY (sta_id) REFERENCES galaxy(gal_id)
“star_sta_id_fkey2” FOREIGN KEY (sta_id) REFERENCES star(sta_id)
Referenced by:
TABLE “star” CONSTRAINT “star_sta_id_fkey2” FOREIGN KEY (sta_id) REFERENCES star(sta_id)

universe=> \d planet;
Table “public.planet”
±------------±----------------------±----------±---------±--------+
| Column | Type | Collation | Nullable | Default |
±------------±----------------------±----------±---------±--------+
| name | character varying(30) | | not null | |
| distance_km | numeric | | | |
| pla_id | integer | | not null | |
| hot | boolean | | | |
| life | boolean | | | |
| size_km | integer | | | |
| description | text | | | |
| age | integer | | | |
| num_sum | integer | | | |
±------------±----------------------±----------±---------±--------+
Indexes:
“planet_pkey” PRIMARY KEY, btree (pla_id)
“planet_name_key” UNIQUE CONSTRAINT, btree (name)
Foreign-key constraints:
“planet_pla_id_fkey” FOREIGN KEY (pla_id) REFERENCES planet(pla_id)
Referenced by:
TABLE “planet” CONSTRAINT “planet_pla_id_fkey” FOREIGN KEY (pla_id) REFERENCES planet(pla_id)

changed all the tables like below with respective names:

universe=> \d moon
Table “public.moon”
±------------±----------------------±----------±---------±--------+
| Column | Type | Collation | Nullable | Default |
±------------±----------------------±----------±---------±--------+
| name | character varying(30) | | not null | |
| distance_km | numeric | | | |
| moo_id | integer | | not null | |
| hot | boolean | | | |
| life | boolean | | | |
| size_km | integer | | | |
| description | text | | | |
| num_sun | integer | | | |
| age | integer | | | |
±------------±----------------------±----------±---------±--------+
Indexes:
“moon_pkey” PRIMARY KEY, btree (moo_id, name)
“moon_name_key” UNIQUE CONSTRAINT, btree (name)

It looks like you have not matched the requirements.

For eg.

When I check your moon table I see you do not have the required moon_id column (I think you have something called moo_id)

And it looks like the other tables are also having the same issue.

thanks a lot you were right, it helped. :+1:

great. Hopefully you are good to figure out the second issue?

i am trying but if there is a hint would be nice…

what this means is that anywhere you have created a foreign key, the name of the column should be identical in both tables.

so for eg. if the moon table has a foreign key that should match a column from the sun table, then it should be called by the same name.

(say sun table has sun_id and moon table wants to use that as a foreign key, then it too must have a column called sun_id)

1 Like

thanks :pray: a lot this one worked too…thanks for the explanation…was confusing a bit… but all tasks are cleared

1 Like

This topic was automatically closed 182 days after the last reply. New replies are no longer allowed.