Lab: Move Habitat JSON Into SQLite

Objective

Create a SQLite database from the command line and use it to store habitat simulation objects that previously lived in JSON.

Success Criteria

• Create a dedicated lab folder for this SQLite project.
• Use ChatGPT Training Mode as a step-by-step SQLite tutor.
• Create a local SQLite database file from the command line.
• Create tables for habitat objects from your previous JSON file.
• Represent at least one relationship between objects, such as an airlock connected to two doors.
• Insert sample rows into your tables.
• Verify your database with SQLite commands such as .tables, .schema, and SELECT.
• Explain how your SQLite database is similar to and different from your JSON file.
• Submit a public GitHub repository URL for your completed lab.

Instructions

1. Open Codex.

2. Create a new Codex project for this lab.

When Codex asks you to choose a folder, create or select this lab folder inside your course labs folder:

~/labs/habitat-sqlite

If your labs folder does not exist yet, create it when choosing the project folder. The goal is for this lab project to live at:

labs/habitat-sqlite

3. In Codex, click the terminal button in the upper right. The terminal should open in the lower window.

For the rest of this lab, run terminal commands from inside this habitat-sqlite project folder unless a step explicitly says otherwise.

4. Launch Zed from the Codex terminal while you are in this project folder:

zed .

This opens the same habitat-sqlite folder in Zed. Use Zed whenever this lab asks you to create, open, or edit a file. Save files in Zed before returning to the Codex terminal to run commands like sqlite3.

5. Add your previous habitat JSON file to this lab folder from the command line.

If your previous lab created a file such as habitat-data.json, use terminal commands to copy that file into this lab folder.

Do not use Finder or File Explorer for this step. You practiced moving around the filesystem, copying files, and checking folder contents in the terminal walkthrough.

After you copy the file, use the command line to confirm that your JSON file is in this lab folder. If you have forgotten which command to use, ask ChatGPT to remind you.

6. Check whether SQLite is available.

Run:

sqlite3 --version

If you see a version number, you are ready.

If the command is not found on WSL, run:

sudo apt update
sudo apt install sqlite3

Then run the version check again:

sqlite3 --version

If SQLite is missing on macOS, ask your instructor for help before continuing.

7. Start ChatGPT Training Mode and attach your JSON file.

Use this prompt:

I would like you to teach me SQLite. I have never used SQLite before, and this is my first introduction to databases.

In my previous lab, I used a JSON file to store objects for a habitat simulation. Now I want to convert a few of those objects from JSON into a SQLite database.

I will be using the command line, either Terminal on a Mac or WSL on a Windows PC. I am a high school student, so please teach this step by step.

Please do not just give me the full answer all at once. First, look at my attached JSON file and help me understand:

1. What objects are in the JSON
2. Which objects could become database tables
3. What columns each table might need
4. How relationships between objects could be represented

Then guide me through creating the database from the command line, creating the tables, adding a few rows, and checking that the data was saved correctly.

After each step, ask me to run the command myself and paste back what happened before moving on.

8. Save notes about your SQLite work.

After Training Mode has guided you through creating the database, creating tables, adding rows, and checking your data, create a notes file.

If you are still inside SQLite, exit SQLite:

.exit

Create a notes file from the terminal:

touch sqlite-notes.md

Open sqlite-notes.md in Zed. If the file is not visible yet, use Zed's file tree or file search to open it.

In sqlite-notes.md, write short answers to these questions:

1. What JSON objects did you turn into database tables?
2. What columns did you choose for each table?
3. What relationship did you represent in SQLite?
4. How did you verify that your data was saved?
5. What feels different about storing this data in SQLite instead of JSON?

9. Create a schema export file.

Run:

sqlite3 habitat.db ".schema" > schema.sql

Check that the file was created:

ls

You should see:

habitat.db
schema.sql
sqlite-notes.md

You should also still have your JSON file.

10. Create a query results file.

Use at least three useful SELECT statements from the work Training Mode helped you do. Save their output in query-results.txt.

If you are not sure how to save command output into a file, ask ChatGPT to remind you how to use terminal redirection.

Open query-results.txt in Zed and check that it contains data from your database.

11. Make sure your final lab folder includes the required files.

Your folder should include:

habitat-data.json
habitat.db
schema.sql
sqlite-notes.md
query-results.txt

Your JSON file may have a different name if you copied it from your previous lab.

Required Deliverables

Your GitHub repository must include:

• Your original JSON file from the previous lab.
• habitat.db, your SQLite database file.
• schema.sql, your exported database schema.
• query-results.txt, showing results from at least three SELECT queries.
• sqlite-notes.md, with answers to the reflection questions.
• A public GitHub repository URL submitted to the LMS.

Submit Your Lab With GitHub

Before you submit, make sure you are inside your lab directory. The path should end with:

labs/habitat-sqlite

Initialize Git, commit your work, create a public GitHub repository, and push it:

git init
git add .
git commit -m "Complete habitat SQLite lab"
gh repo create habitat-sqlite --public --source=. --remote=origin --push

Get the URL for your GitHub repository:

gh repo view --web

Copy the GitHub repository URL from your browser and submit that URL with your lab submission.