CS 304: Databases with Web Interfaces

This course is about building database-backed web sites (web applications). That means websites like Facebook, Yelp, eBay, Stack Overflow and the like. What distinguishes web sites like those from websites like, say Wikipedia or Microsoft.com or the New York Times websites? The difference is user-contributed content. What does that mean? When you go to Yelp, for example, you can not only search for restaurants, you can also post reviews. Those reviews get stored in a database such that when other people read about restaurants, they can read your review. Similarly, Facebook is all about users posting content and reading one another's content. (They also connect with friends, and those connections are also stored in a database.)

Of course, the dividing line is not sharp between these categories. You're unlikely to be able to post information to the Microsoft website, but you can shop, and your shopping cart is stored in a database. You're not going to post articles to the NY Times website, but you can post a comment (and "like" other people's comments), and that information is stored in a database. Many commercial websites have some kind of database-backed user interaction.

Those database-backed websites that our focus, particularly ones with user-contributed content (like Yelp and Facebook) are often called Web 2.0. Static, read-only websites (like this one!) were then, retroactively, called Web 1.0.

Major Topics

In this course, you'll learn the fundamentals of how to create database-backed websites. Major topics include:

  1. Databases: how to create tables, insert and update data, and search for data to satisfy a query.
  2. The Flask web framework: how to connect web requests with Python code that responds to the request.
  3. Database APIs: how to interact with a database from Python code
  4. Security: how to ensure that your website is not vulnerable to common attacks
  5. Sessions: how to have continuous interactions with a user who is logged into your site
  6. File Upload: how to allow users to upload files, e.g. pictures, to your site
  7. Ajax: how to have seamless interactions, such as the Facebook "like" button

Three Tiers

Another way to break down the content of this course is the "three-tier hierarchy":

  1. relational databases and the SQL language, using MySQL, an open-source Relational Database Management System (RDBMS).
  2. middleware scripting, specifically Flask, a Python-based micro-framework.
  3. front-end web pages using HTML, CSS, JavaScript, jQuery and Ajax

The first two tiers run on servers and are collectively called the "back-end". The last tier runs in the web browser and is called the "front-end". Collectively, this is called the "full-stack".

Other Topics

We'll take some time to discuss important alternative back-end technologies, namely node.js and MongoDB.

Along the way, we discuss web application security, including SQL injection, XSS attacks, HTTPS and .htaccess, password management and other topics.

We will also learn about theoretical issues and practical issues, such as designing tables using entity-relationship diagrams, modeling tables using relational algebra, normalizing databases, and representing data using JSON.

We'll also discuss Ethics in the context of databases.

Rationale

I think this is important and exciting. Why? Using these skills, you can build web applications that can make the world a better place, whether it's social networking (e.g. Facebook or Mastodon), crowd-sourcing of information and reviews (e.g. Yelp and TripAdvisor), electronic commerce (eBay, Amazon, and Etsy) or community organizing (e.g. NextDoor). Finally, these skills are immensely practical, and you'll find you use them in internships, summer jobs, and in your career.