CSE 134A Lecture Notes, October 1, 2001

CSE134A LECTURE NOTES

October 1, 2001

ANNOUNCEMENTS

Today I will give an introduction to relational databases and SQL. For the practical details on how to use MySQL in the first project, see these section notes on Using PHP with MySQL.

Today's handout is by Jakob Nielsen on the top ten web page usability mistakes, from 1996. Mistake zero, not mentioned by Nielsen but the most important and common in amateur pages: unreadable text, e.g. dark text on a dark background, or light on light.

THREE-TIER ARCHITECTURE

Most web sites have the same basic three-tier architecture:

The top tier is a thin client, i.e. a web browser.
The middle (second) tier is a web server.
The bottom (third) tier is a database (db) server.

In general, the db server and the web server are different computers. They communicate via a network, and specifically via Unix sockets.

The db client is a PHP script. The script sends commands written in the SQL language. The db server sends responses. A response is often a record containing data.

CONNECTING TO THE MYSQL SERVER

// MySQL database connection information
$username = "php";
$password = "pw";
$hostname = "ieng8.ucsd.edu";
$dbname = "mydb";

if (!($link=mysql_connect($hostname, $username, $password)))
printerror("cannot connect to $hostname by $username");
if (!mysql_select_db($dbname, $link))
printerror("cannot select $dbname database");

Do not use pconnect. It is supposed to be more efficient but it has some bugs that are only visible when many scripts are making thousands of connections to the database server.

TABLES AND RECORDS

Tables are divided into rows and columns. A row is also called a record or tuple. A column is also called a field or attribute. A table is also called a relation.

The db server takes care of maintaining the tables on disk, of allowing multiple db clients to read and write to them without data corruption, and of keeping copies cached in memory to increase read/write speed.

All database operations are specified using a language of commands called SQL. For example:

drop table if exists temp;
create table temp (
    k        integer unsigned not null,
    s        double not null,
    d        datetime not null,
    quantile double not null )

The syntax of the SQL language is inspired by Cobol, a 1960s business-oriented language. Upper/lower case is not significant.

FIELD TYPES

Unlike in PHP, in MySQL and in all other relational databases, columns have fixed, predeclared types. The most important types are:

INTEGER: signed 32 bits
DOUBLE
CHAR(n): a string of fixed length at most n characters, where n <= 255
VARCHAR(n): a string of variable length at most n characters, where n <= 255
TEXT, BLOB: a string of length at most 2¹⁶-1 bytes
MEDIUMTEXT, MEDIUMBLOB: a string of length at most 2²⁴-1 bytes
LONGTEXT, LONGBLOB: a string of length at most 2³²-1 bytes
DATE: format 'YYYY-MM-DD' with 1000 <= YYYY <= 9999
TIMESTAMP: format 'YYYYMMDDHHMMSS' range 1970 to 2038

Modern database systems can handle very large fields, e.g. photographs. BLOB stands for "binary large object." BLOB values are case-sensitive while TEXT values are not (a leftover from the days before eight-bit ascii character codes).

The strange range for TIMESTAMP comes from a Unix-specific representation.

In MySQL and in all other relational databases, unlike variables in PHP, columns have fixed, predeclared types. Unlike in most programming languages, the basic types also have modifiers available. These include:

UNSIGNED
NOT NULL
DEFAULT value

Note that some keywords contain spaces, e.g. NOT NULL.

QUERIES

SQL is an acronym for "structured query language." A query allows data to be retrieved based on its content, not its storage location. For example:

select author, date from postings where date > '2000-11-11'