Celestial Bodies Database - Build a Celestial Bodies Database - Cannot Progress

Backend Development
1

Tell us what’s happening:
I have, as far as I can tell, entirely finished the Celestial Bodies Database project. However, few of the checkmarks will change to green, and those that do seem totally arbitrary. The first of them that won’t change is ‘All tables should have a primary key,’ which my code sample below shows is complete. Others that are easily verifiable – ’ You should use the TEXT data type at least once’, for instance – are also not changing. I’ve tried switching to a private window; switching browsers (typically use Firefox, tried Chrome); soft reset; pressing ‘Run’ and ‘Ctl+Enter’.

Any advice would be much appreciated.

Your code so far

postgres=> SELECT conrelid::regclass AS table_name,
postgres->        conname AS primary_key, 
postgres->        pg_get_constraintdef(oid) 
postgres-> FROM   pg_constraint 
postgres-> WHERE  contype = 'p' 
postgres-> AND    connamespace = 'public'::regnamespace   
postgres-> ORDER  BY conrelid::regclass::text, contype DESC; 
+------------+----------------+----------------------------+
| table_name |  primary_key   |    pg_get_constraintdef    |
+------------+----------------+----------------------------+
| galaxy     | galaxy_pkey    | PRIMARY KEY (galaxy_id)    |
| moon       | moon_pkey      | PRIMARY KEY (moon_id)      |
| planet     | planet_pkey    | PRIMARY KEY (planet_id)    |
| star       | star_pkey      | PRIMARY KEY (star_id)      |
| supernova  | supernova_pkey | PRIMARY KEY (supernova_id) |
+------------+----------------+----------------------------+
(5 rows)

postgres=>

Your browser information:

User Agent is: Mozilla/5.0 (X11; Linux x86_64; rv:107.0) Gecko/20100101 Firefox/107.0

Challenge: Celestial Bodies Database - Build a Celestial Bodies Database

Link to the challenge:

2

I’ve edited your code for readability. When you enter a code block into a forum post, please precede it with a separate line of three backticks and follow it with a separate line of three backticks to make it easier to read.

You can also use the “preformatted text” tool in the editor (</>) to add backticks around text.

Pre-formatted-text
Pre-formatted-text1356×380 401 KB

See this post to find the backtick on your keyboard.
Note: Backticks (`) are not single quotes (').

3

Do all your tables have the required rows?

4

Understood, thank you; I’ll remember that in the future. All the tables have the required rows – 6 for galaxy and star, 12 for planet, and 20 for moon, along with 4 for my extra table supernova (minimum 3).

5

Can you show a screenshot of the failing tests and also a complete schema of the tables? (Using /d for each table)

6

Sure thing.

CBDB_tests
CBDB_tests971×788 74.3 KB

Unfortunately I can’t get it all on my screen and can only upload one image at a time, per the forum’s restrictions. There are two above this that are also grey—‘Each table should have a primary key’ and ‘Each table should have a ‘name’ column.’ Then one below says ‘Each foreign key column should have the same name as the column it is referencing.’

postgres=> \d galaxy
                                       Table "public.galaxy"
+--------------+-----------------------+-----------+----------+------------------------------------+
|    Column    |         Type          | Collation | Nullable |              Default               |
+--------------+-----------------------+-----------+----------+------------------------------------+
| galaxy_id    | integer               |           | not null | nextval('galaxy_id_seq'::regclass) |
| galaxy_shape | character varying(20) |           |          |                                    |
| diameter_kpc | real                  |           |          |                                    |
| name         | character varying     |           | not null |                                    |
| redshift_z   | numeric               |           |          |                                    |
+--------------+-----------------------+-----------+----------+------------------------------------+
Indexes:
    "galaxy_pkey" PRIMARY KEY, btree (galaxy_id)
    "galaxy_name_key" UNIQUE CONSTRAINT, btree (name)
Referenced by:
    TABLE "star" CONSTRAINT "star_galaxy_id_fkey" FOREIGN KEY (galaxy_id) REFERENCES galaxy(galaxy_id)
    TABLE "supernova" CONSTRAINT "supernova_galaxy_id_fkey" FOREIGN KEY (galaxy_id) REFERENCES galaxy(galaxy_id)

postgres=> \d star
                                            Table "public.star"
+-------------------+-----------------------+-----------+----------+---------------------------------------+
|      Column       |         Type          | Collation | Nullable |                Default                |
+-------------------+-----------------------+-----------+----------+---------------------------------------+
| star_id           | integer               |           | not null | nextval('star_star_id_seq'::regclass) |
| spectral_type     | character(1)          |           |          |                                       |
| name              | character varying(30) |           | not null |                                       |
| mass_kg           | real                  |           |          |                                       |
| has_known_planets | boolean               |           |          |                                       |
| galaxy_id         | integer               |           |          |                                       |
+-------------------+-----------------------+-----------+----------+---------------------------------------+
Indexes:
    "star_pkey" PRIMARY KEY, btree (star_id)
    "star_name_key" UNIQUE CONSTRAINT, btree (name)
    "star_star_id_key" UNIQUE CONSTRAINT, btree (star_id)
Foreign-key constraints:
    "star_galaxy_id_fkey" FOREIGN KEY (galaxy_id) REFERENCES galaxy(galaxy_id)
Referenced by:
    TABLE "planet" CONSTRAINT "planet_star_id_fkey" FOREIGN KEY (star_id) REFERENCES star(star_id)

postgres=> \d planet
                                              Table "public.planet"
+---------------------+-----------------------+-----------+----------+-------------------------------------------+
|       Column        |         Type          | Collation | Nullable |                  Default                  |
+---------------------+-----------------------+-----------+----------+-------------------------------------------+
| planet_id           | integer               |           | not null | nextval('planet_planet_id_seq'::regclass) |
| name                | character varying(40) |           | not null |                                           |
| mass_kg             | real                  |           |          |                                           |
| orbital_period_days | numeric               |           |          |                                           |
| has_liquid_water    | boolean               |           |          |                                           |
| star_id             | integer               |           |          |                                           |
+---------------------+-----------------------+-----------+----------+-------------------------------------------+
Indexes:
    "planet_pkey" PRIMARY KEY, btree (planet_id)
    "planet_name_key" UNIQUE CONSTRAINT, btree (name)
    "planet_planet_id_key" UNIQUE CONSTRAINT, btree (planet_id)
Foreign-key constraints:
    "planet_star_id_fkey" FOREIGN KEY (star_id) REFERENCES star(star_id)
Referenced by:
    TABLE "moon" CONSTRAINT "moon_planet_id_fkey" FOREIGN KEY (planet_id) REFERENCES planet(planet_id)

postgres=> \d moon
                                             Table "public.moon"
+---------------------+-----------------------+-----------+----------+---------------------------------------+
|       Column        |         Type          | Collation | Nullable |                Default                |
+---------------------+-----------------------+-----------+----------+---------------------------------------+
| moon_id             | integer               |           | not null | nextval('moon_mood_id_seq'::regclass) |
| mass_in_kg          | numeric               |           |          |                                       |
| name                | character varying(30) |           | not null |                                       |
| surface_temp_k      | integer               |           |          |                                       |
| planet_id           | integer               |           |          |                                       |
| orbital_period_days | numeric               |           |          |                                       |
+---------------------+-----------------------+-----------+----------+---------------------------------------+
Indexes:
    "moon_pkey" PRIMARY KEY, btree (moon_id)
    "moon_mood_id_key" UNIQUE CONSTRAINT, btree (moon_id)
    "moon_name_key" UNIQUE CONSTRAINT, btree (name)
Foreign-key constraints:
    "moon_planet_id_fkey" FOREIGN KEY (planet_id) REFERENCES planet(planet_id)

postgres=> \d supernova
                                            Table "public.supernova"
+--------------+-----------------------+-----------+----------+-------------------------------------------------+
|    Column    |         Type          | Collation | Nullable |                     Default                     |
+--------------+-----------------------+-----------+----------+-------------------------------------------------+
| supernova_id | integer               |           | not null | nextval('supernova_supernova_id_seq'::regclass) |
| name         | character varying(50) |           | not null |                                                 |
| app_mag      | numeric               |           |          |                                                 |
| distance_ly  | integer               |           |          |                                                 |
| sn_type      | text                  |           |          |                                                 |
| galaxy_id    | integer               |           |          |                                                 |
+--------------+-----------------------+-----------+----------+-------------------------------------------------+
Indexes:
    "supernova_pkey" PRIMARY KEY, btree (supernova_id)
    "supernova_name_key" UNIQUE CONSTRAINT, btree (name)
    "supernova_supernova_id_key" UNIQUE CONSTRAINT, btree (supernova_id)
Foreign-key constraints:
    "supernova_galaxy_id_fkey" FOREIGN KEY (galaxy_id) REFERENCES galaxy(galaxy_id)

Happy to provide any other information, and thank you for taking the time to help me.

7

It does look good so far to me.

Can you dump out the sql file and anything else you created and perhaps it is time to think about deleting this entire container in codeally.io then restoring your database to a new container (essentially would mean that once the container for this course is deleted you would start a new one from the fCC website and then use your sql dump file to restore everything to see if that triggers the test to behave)

8

The other thing I would do is remove any duplicate or unasked for constraint in case the test is not very flexible. (Like this one)

9

Thank you for the suggestions. I went ahead and did so, and fool that I am failed to check if the dump file actually had the appropriate information, which it did not; in fact, none of the several dumps I made as backups were any different from the first one, so I suppose all the information was stored in the container somehow. Thankfully I saved all the data separately, so it’s just a matter of some tedious data entry now.

I’m not so familiar with how SQL dumps work, but I would guess that this issue is probably related to the fact that I wasn’t getting my checks marked. Maybe I’ll find out when I redo it.

I know this entire course is in beta, so if there’s any further information I can provide that might help with solutions, please let me know. Thanks again for taking the time to help me out, I really appreciate it.

10

Did you save the actual database setup ? (So just lost the data in the tables?)

I am curious if you have tried to restore the tables as of yet, and if you have noticed any changes to the tests yet?

11

I only saved the results of the dump using the provided command in the exercise, so now the outputs of \d [TABLE] are:

universe=> \d galaxy
                                          Table "public.galaxy"
+-------------------+-----------------------+-----------+----------+------------------------------------+
|      Column       |         Type          | Collation | Nullable |              Default               |
+-------------------+-----------------------+-----------+----------+------------------------------------+
| galaxy_id         | integer               |           | not null | nextval('galaxy_id_seq'::regclass) |
| galaxy_shape      | character varying(20) |           |          |                                    |
| galaxy_age_myears | real                  |           |          |                                    |
| name              | character varying     |           | not null |                                    |
| redshift_z        | numeric(3,1)          |           |          |                                    |
+-------------------+-----------------------+-----------+----------+------------------------------------+
Indexes:
    "galaxy_pkey" PRIMARY KEY, btree (galaxy_id)
    "galaxy_name_key" UNIQUE CONSTRAINT, btree (name)

universe=> \d star
                                      Table "public.star"
+---------------+--------------+-----------+----------+---------------------------------------+
|    Column     |     Type     | Collation | Nullable |                Default                |
+---------------+--------------+-----------+----------+---------------------------------------+
| star_id       | integer      |           | not null | nextval('star_star_id_seq'::regclass) |
| spectral_type | character(1) |           |          |                                       |
+---------------+--------------+-----------+----------+---------------------------------------+
Indexes:
    "star_star_id_key" UNIQUE CONSTRAINT, btree (star_id)

universe=> \d planet
                                  Table "public.planet"
+-----------+---------+-----------+----------+-------------------------------------------+
|  Column   |  Type   | Collation | Nullable |                  Default                  |
+-----------+---------+-----------+----------+-------------------------------------------+
| planet_id | integer |           | not null | nextval('planet_planet_id_seq'::regclass) |
+-----------+---------+-----------+----------+-------------------------------------------+
Indexes:
    "planet_planet_id_key" UNIQUE CONSTRAINT, btree (planet_id)

universe=> \d moon
                                              Table "public.moon"
+----------------------+-----------------------+-----------+----------+---------------------------------------+
|        Column        |         Type          | Collation | Nullable |                Default                |
+----------------------+-----------------------+-----------+----------+---------------------------------------+
| moon_id              | integer               |           | not null | nextval('moon_mood_id_seq'::regclass) |
| mass_in_kg           | numeric               |           |          |                                       |
| orbital_period_hours | numeric(3,1)          |           |          |                                       |
| name                 | character varying(30) |           |          |                                       |
+----------------------+-----------------------+-----------+----------+---------------------------------------+
Indexes:
    "moon_mood_id_key" UNIQUE CONSTRAINT, btree (moon_id)
    "moon_name_key" UNIQUE CONSTRAINT, btree (name)

universe=> \d supernova
                                     Table "public.supernova"
+--------------+---------+-----------+----------+-------------------------------------------------+
|    Column    |  Type   | Collation | Nullable |                     Default                     |
+--------------+---------+-----------+----------+-------------------------------------------------+
| supernova_id | integer |           | not null | nextval('supernova_supernova_id_seq'::regclass) |
+--------------+---------+-----------+----------+-------------------------------------------------+
Indexes:
    "supernova_supernova_id_key" UNIQUE CONSTRAINT, btree (supernova_id)

universe=>

and none of the check marks have changed, which looking over this output seems to make sense.

I also have a strong suspicion, based on the fact that the console in my previous post reads ‘postgres’ and in this one reads ‘universe,’ that this is actually entirely my fault for not connecting to my database after the first time I closed the VM, and I’ve actually been wasting your time here.

12

it would be interesting to understand if you have worked your way out of this one…(and how)

13

Sorry for the delay in responding; ultimately the way I worked my way out of it was ‘do it over’. I think my problem the entire time was that I wasn’t re-connecting to ‘universe’ using /c when logging back in, and ended up making all my tables directly in the ‘postgres’ database, which was a foolish mistake that I know now not to make in the future. Thanks again for trying to help me with this.

2 Likes