Mastering Databases

In this first lesson, students create a geography quiz that displays a random picture along with a set of multiple choice answers. The data for the quiz questions is fetched from an SQL database and presented to the user on an HTML page. Students review using database connections in a Python program, requesting data from an HTML frontend, and dynamically creating user interface elements using JavaScript and jQuery.

In this lesson, students create a searchable database of movies by using a public dataset of the 1000 most popular movies on IMDB. The raw data is stored in the commonly encountered comma-separated values (CSV) form. Students learned how to open and process CSV data into an SQL table using Python and how to broadly search a database using pattern matching. They also refresh their skills with SQL by connecting to the database and executing statements to create tables, insert new records, and make conditionally filtered selections of records.

In this lesson, students build an app that retrieves a random activity someone can do when they’re bored. The user can select from several categories of activity types. They build a server that sends the HTML file for the app and also has an API endpoint for accessing an SQL database to retrieve a random activity. This project gives students a practical experience of how data is retrieved from a server and sent to a user’s browser. The lesson reviews SQL fundamentals and introduces new concepts including the server-client model, basics of HTTP, URL routing, request arguments, and randomizing the order of database results.

In this lesson, students learn how to efficiently handle delivering large amounts of data to a user application by implementing pagination. They build an e-commerce inventory page for WizTech, an imaginary tech products web store. Pagination is the practice of sending a fixed number of items from a data source rather than sending the entire data set at once. The user can select how many items to display per page, and click a “next” button to load the next page of items. Students learned SQL syntax for pagination, JavaScript for…of loops, and rendering HTML elements from a list of data records.

In this lesson, students begin building their first CRUD app. CRUD is short for Create-Read-Update-Delete, the fundamental data operations required for modern apps. This simple model underlies all major apps such as YouTube, Instagram, and Twitter. Students start with the classic beginner CRUD app: a blog. In this iteration, they set up a server to manage the application’s backend logic by connecting it to the database where blog posts are stored. They also work on the front end to fetch the stored posts from the server and display them as web elements. This lesson shows students how HTML/CSS/JavaScript webpages integrate with a server and database to create a modern data-driven application.

In this lesson, students complete their midterm CRUD blog project by building an editor page that allows the user to create and edit posts. They learn about HTTP request methods, how to use HTML forms to send data to a server, and how to redirect users to a new page. Building a blog is considered a benchmark of fundamental development skills and completing this lesson is a milestone in the student’s programming career.

In this lesson, students learn the basic methods of securing sensitive data. They begin by making a backup copy of a table of usernames and passwords to work with. Then, they use an encryption method known as hashing, which generates a long unique code for an input, to encrypt all the passwords. When the user types, their input is hashed before being checked against the stored hash password. Then students “brute-force” a break in using a list of several thousand common passwords. The students finish by adding a “salt” to the passwords, random data that ensures every password is strong enough to resist a brute-force attack. This lesson focuses on security, hash functions, cyber attacks, and salting passwords.

In this lesson, students learn the advanced concept of database normalization, a process of organizing data to make the database more flexible and ensure consistency. Typically, normalization results in a table with many columns being broken into multiple tables with fewer columns. Students redesign a database of spaceships to eliminate redundant and duplicate values which reduces the storage size required for the table. This practice is standard in all software and is used by developers in every industry and prepares students to build more professional applications.

In this lesson students are introduced to data visualization, the practice of turning stored data into graphical representations. Students use the popular JavaScript library Chart.js to create a bar chart of the top 10 most popular video games by average players per year. They also learned more advanced querying, such as selecting rows with distinct values and grouping data.

In this lesson, students learn advanced querying by solving an SQL crime mystery. Given a few starting clues, they gathered information by querying a database with multiple tables of linked information in order to track down the suspects. In doing this, they practiced using table relations and learned ways of selecting complex data distributed across multiple tables. The concepts included filtering data, grouping data, and joining tables.

In this lesson, students begin their final project by adding user accounts, signup, and login authentication to a chat application. They practice sending data from an HTML frontend to the Python server backend, querying the database, and handling sensitive user data using industry-standard security practices. The concepts include hashing, Python SQL libraries, and user authentication.

In the final lesson of M33 Mastering Databases, students finish adding database features to the chat app they began in Lesson 11. They start by storing all the chat messages as records in the database, along with the user’s ID and the message timestamp. Then they add code to retrieve the messages in order of timestamp and display them in the chat interface upon loading the app. They finish by adding a profile editor which lets users change their username and text color. As a bonus, they add a feature for users to delete their accounts. This lesson’s concepts feature JOIN statements, POST requests, updating data, and deleting data.