Intro to Databases

In the first lesson of this fun, interactive course, students learn the fundamentals of digital data storage. They are introduced to the most common units of information, bits and bytes, by creating a journal program that enforces a 1000-byte memory limit. They learn the difference between short and long-term data storage and the basics of interacting with the file system with Python. This lesson supports students’ understanding of application development by illustrating the hardware considerations that underlie many common scenarios in programming.

In this lesson, students learn the vital skill of integrating file data into an application. A program’s data is lost once the program exits, unless the data is saved into a file. Students add a high-scores database to a version of the arcade classic Space Invaders using a simple comma-separated values (CSV) file. In adding this feature, students practice opening, reading, and writing to files, along with processing strings in CSV format. 

In this foundational lesson, students are introduced to the Structured Query Language (SQL), a common programming language for organizing and managing data in a table-structured database. They use an RPG Creator app to create characters, spells, and enemies for an RPG game. By interacting with this app, they learn to create, modify, and delete tables, as well as how to ensure unique data using a special ID called a primary key. It’s a fun and practical way to understand databases! 

In this lesson, students track down a fugitive space pirate by combing through a database of spaceships and captains, using a set of clues. The project introduces the concept of retrieving stored data from an SQL database using the SELECT syntax. Students learn to write queries using special keywords that filter data and narrow down the suspects based on the given clues. 

In this lesson, students continue their journey into SQL databases by helping assemble the crew for an interstellar expedition. After creating the data tables themselves, they learn how to add data to them by using INSERT statements. Students also learn to delete and update records when a crew member becomes sick and has to be replaced. The basics of creating, deleting, and updating data round out their understanding of the fundamental SQL operations associated with databases in application development.

In this lesson, students fuse their new SQL skills with their Python programming to create their first app that connects to a database. They help repair a spaceship by building an app that tells the user the status of the ship systems and gives an option to activate the backup systems. The Python app communicates with a database that stores the info about the ship’s systems in a table. Students learn the essential skills of connecting Python apps to a database, executing queries, and processing retrieved data.

In this lesson, students learn the fundamental concept of SQL table relations. Table relations allow an SQL database to establish links between two tables to represent complex relationships between types of entities, such as a ‘books’ table and an ‘authors’ table. This relatively simple feature is what underlies the robust infrastructure of the massive databases in use across every industry. To learn this concept, students interact with tables of spaceships and parts to construct a new spaceship by linking the ID column from the engines table to a new column in the spaceships table. 

In this lesson, students began a two-part project of updating a text-based adventure game (which they built in the previous course) using their new database skills. In this first phase, students connect to the database, create a users table to store names and passwords, add new account signup, and verify credentials for logging in. These basic techniques mimic the process of user management and authorization in real-world games and apps.

In this lesson, students complete updating the WizardQuest game. In M14 Capstone, students built a text-based adventured game called WizardQuest using Python. In this second phase, students create a table to store the player’s game stats. As they play, their stats are saved back to the table. When the player logs back in their stats are reloaded so the game begins where they left off. Students gain a practical understanding of the uses that databases in solving real-world programming problems.

In the context of data science work, SQL is often used in tandem with traditional programming languages like Python. In this lesson, students learn the basics of data analysis with SQL by identifying asteroids that may pose a threat to Earth from a NASA database. Students learn syntax and keywords to write queries that sort, filter, group the data, count rows, and find minimum and maximum values.

In this lesson, students begin their final project. For part one, students use all their accumulated knowledge to design a database scheme that integrates with a simple Python internet messaging app. They are introduced to the Python requests module as a basic introduction to network programming. The mail app they create can be used to actually send short messages to each other over the internet. 

In this final lesson, students complete their Wizard Mail app by adding features to print the inbox, open messages, and delete messages. In doing so, they review the fundamental data operations of integrating SQL with a Python app: creating connections, retrieving and processing data, executing queries, and committing changes to the database. This project illustrates the conceptual pillars of the Intro to Databases course: how programming languages like Python work in tandem with SQL databases to create robust, structured, and scalable applications.