0)

 where to read about this stuff:

  a) from the books Jim'll leave in this room

  b) from http://sqlzoo.net/ , http://dbgrussell.org,
     or any similar "sql tutorial" or "relational database tutorial"
     google search

  c) work through the MySQL tutorial in the reference manual at
     http://cs.marlboro.edu/docs/MySQL/manual_Tutorial.html#Tutorial
 
     (The rest of that manual will come in handy, too.)

  --------------------------------------------------

 assignment: 

  1) Read a bunch of stuff.
  
  2) Draw an ERD for a smallish registrar database which contains 
     at least this info.  Feel free to add other details if you wish.

 student        course        faculty
 -------        ------        -------
 Joe Fish       calculus      Dr. Who
 Joe Fish       physics       A. Einstein
 Mary Green     calculus      Dr. Who
 Mary Green     chemistry     A. Noble
 Ellen White    radio         Dr. Who
 Francis Eld    radio         Dr. Who
 Frank Micklin  chemistry     A. Noble

  3) Write a file of MySQL commands that will
     i) create the tables for this database, and
    ii) populate the tables with this data

  4) Now generate some SQL queries that will find out
     i) which students are taking physics?  
    ii) which courses are Dr. Who teaching?

  5) Try some of the "Assessment" exercises on http://sqlzoo.net/


--------------------------------------

I) 

 everyone has a mysql account on cs.
  user = your username
  db   = user_db
  pass = user_pass with 55 rather than ss

 from the command line, type (your username instead of "user")

  $ mysql --user=*** --password  user_db

 which puts you into the system.  
 To read in a file of .sql commands type "source file.sql".

--------------------------------------

II)

 Here's some sample syntax, from the tutorial in the manual.
 Please note that this is a new language to learn, 
 with command to 
   * add stuff to the database
   * remove stuff from the database
   * retrieve stuff from the database
   * in many ways


CREATE TABLE person (
    id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
    name CHAR(60) NOT NULL,
    PRIMARY KEY (id)
);

CREATE TABLE shirt (
    id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
    style ENUM('t-shirt', 'polo', 'dress') NOT NULL,
    color ENUM('red', 'blue', 'orange', 'white', 'black') NOT NULL,
    owner SMALLINT UNSIGNED NOT NULL REFERENCES person(id),
    PRIMARY KEY (id)
);

INSERT INTO person VALUES (NULL, 'Antonio Paz');

INSERT INTO shirt VALUES
(NULL, 'polo', 'blue', LAST_INSERT_ID()),
(NULL, 'dress', 'white', LAST_INSERT_ID()),
(NULL, 't-shirt', 'blue', LAST_INSERT_ID());

INSERT INTO person VALUES (NULL, 'Lilliana Angelovska');

INSERT INTO shirt VALUES
(NULL, 'dress', 'orange', LAST_INSERT_ID()),
(NULL, 'polo', 'red', LAST_INSERT_ID()),
(NULL, 'dress', 'blue', LAST_INSERT_ID()),
(NULL, 't-shirt', 'white', LAST_INSERT_ID());

SELECT * FROM person;
+----+---------------------+
| id | name                |
+----+---------------------+
|  1 | Antonio Paz         |
|  2 | Lilliana Angelovska |
+----+---------------------+

SELECT * FROM shirt;
+----+---------+--------+-------+
| id | style   | color  | owner |
+----+---------+--------+-------+
|  1 | polo    | blue   |     1 |
|  2 | dress   | white  |     1 |
|  3 | t-shirt | blue   |     1 |
|  4 | dress   | orange |     2 |
|  5 | polo    | red    |     2 |
|  6 | dress   | blue   |     2 |
|  7 | t-shirt | white  |     2 |
+----+---------+--------+-------+

SELECT s.* FROM person p, shirt s
 WHERE p.name LIKE 'Lilliana%'
   AND s.owner = p.id
   AND s.color <> 'white';

+----+-------+--------+-------+
| id | style | color  | owner |
+----+-------+--------+-------+
|  4 | dress | orange |     2 |
|  5 | polo  | red    |     2 |
|  6 | dress | blue   |     2 |
+----+-------+--------+-------+

-------------------------------------------------

II)

OK, so uppose you wanna put this stuff in a database :

 name           phone      skills           department(s)        address
 ------------   -----      -------          ------------         -----------
 Nora Edwards   222-1234   perl,Java,sql    testing              building 21, room 301
 Ed Stigland    222-1227   perl,linux       testing              building 21, room 301
 Fred Jaffey    222-1271   Java,windows     development,docs     building 20, room 4
 Sally Ani      221-5566   windows,typing   docs                 building 20, room 3

How you gonna do it?

The rules :
 * each table should be a logically coherent "entity"
 * each row must have a unique primary key
 * each column must be a single thing: no lists allowed
 * only redundancy is in keys: primary in one table, foreign in others


 work through this in class...

--------------------------------------------------------

 coming: 

 Perl/DBI