Day 1

CS 304: Databases with Web Interfaces

Introductions

• Who am I?
• Who are you? What do you want out of this course?
• How many know HTML/CSS? JavaScript? SQL? Python? Linux commands?
• PHP? Perl? Java? Bash?

What the course is about

Our goal is to learn just enough about enough things so that we can build complete web database applications.

The Three-Tier Architecture

The following is the classic picture, though I relied heavily on the figure in the Platypus book.

The red arrows are where we will concentrate most of our efforts. We will not primarily be involved with receiving HTTP requests or delivering the results (the blue arrows), but we will when we do work with Ajax.

Why a Three-Tier Architecture?

• May only need to develop middle tier (although applications like iTunes may be an exception)
• Client tier can be kept "thin."
• Business logic can be kept on server, not distributed
• Uses well-debugged interfaces (HTTP, SQL)

The Back End

In principle, any database management system (DBMS) can be used as the back end. We have chosen the MySQL software because:

• It's Open Source
• It's very popular
• It's free
• It's got good, online documentation
• It's easy to install on your own computer. (Implementations exist for Windows, OSX, Linux and FreeBSD. Check them out at MySQL.com.)

It has its own quirks and limitations, but we'll do our best.

Middleware Technologies

In past offerings of this course, I've preferred to teach several different middleware technologies. This semester, we're going all-in on Flask.

Other middleware:

• PHP. This is a dedicated tool for the particular job of web databases. Because it's focused, it's very powerful and this technology has captured a lot of web applications. Takis strongly recommends it.
• CGI/Python. The CGI API, particularly combined with the suexec feature of Apache 2.2, has some very different security implications compared to PHP.
• Node.js, which is server-side JavaScript. We won't do an assignment with node.js, but it has some very important features that are worth knowing about, including a event-loop based architecture.

If we have time, we may learn a little PHP/PDO, just because it's simple, easy and pretty good.

LAMP

Collectively, this suite (or "stack") of technologies goes by the acronym LAMP, for

• Linux
• Apache
• MySQL
• PHP/Perl/Python

(Interestingly, there is now competition from WAMP, where Windows replaces Linux. There's also MEAN, for MongoDB, Express.js, AngularJS and Node.js. We'll learn something about three of those later in the course.)

Three Tier in Action

Here are some slides showing the three tiers in action, as we'll use them in this course.

The calm before the storm...

user clicks on a button, submitting a form

Apache gets it, and hands it off to a PHP or CGI script.

The script sends SQL code to MySQL to get or modify data

The MySQL DBMS returns results to the script, often a table of data

The script can do as it pleases with the data, and give a response to Apache

Apache hands the response, which is HTML, CSS and JavaScript to the browser

And the round trip is complete.

In another situation, the browser might submit to a Flask server, which may be integrated with Apache.

Flask includes some code that you write to handle different routes (urls)

Your handler can connect to MySQL, sending it some SQL code

Again, the DBMS replies with a table of data

The flask application replies to the browser

Events Cal

Events Cal, built Grace Hu and Cece Tsui in Fall of 2016.

Syllabus Review

We'll take a look at the syllabus together, just to make sure we all understand the requirements of the course.

Then, we'll look at the schedule together. Highlights from the syllabus/schedule:

• Reach me by email
• Office hours, listed there
• I've chosen Wednesday at 11pm for assignments. Will that work?
• Lateness policy: lateness coupons
• Time and work: Short, Report, Stop, Get Help.
• tutors: Anah and Hala
• I will hold evening office hours on Wednesdays when an assignment is due. After 9pm.
• The schedule somewhat tentative so we may make some minor adjustments. There will be forewarning if anything substantial changes. What is most likely is that something will take a bit longer than I hoped/predicted and everything will be delayed a bit.
• I strongly urge Pair Programming in this class, but I will not require it. Students can learn a lot from talking things through with one another, and it builds camaraderie. Plus, it's more fun. I understand that student schedules sometimes make this difficult, but I encourage you to try. We will coordinate this via a Google Doc, posted before each assignment.
• No laptop policy: to foster camaraderie in class, I ask you not to bring your laptops. A shared keyboard and mouse will result in better conversation and learning.

Pedagogical Approach

Class sessions are going to be as hands on as possible. If we don't get through everything, I'd like you to try to continue on your own, and we'll see where people get by next time.

Classes will not focus on a detailed and complete exposition of SQL, Python, or whatever the topic of the day is. We'll focus on understanding just enough to get that day's example working. I will expect you to refer to reference material for the details and particulars of the languages we'll be using.

Help me build up our FAQ! Don't hesitate to post to the Google group! Recommend entries for the FAQ.

Your Questions and Concerns?

What are they?

Books and Resources

• Online resources: Google is your friend. You'll find that you rarely need a book. Be careful with Stack Overflow. There's bad code out there, too.
• Textbook: none

To Do

For next time, please do the following:

• Read the Unix reading
• Read about Coding with Visual Studio Code
• Read about Queries

Day 2

Day 3: MS Access

Day 4: DDL, Referential Integrity, Datatypes, Altering Tables

Day 5: ER Diagrams