SUMMER2013

CLASS SYLLABUS

DAY/TIME/PLACE

Instructor: Bruce Donald Campbell

Faculty, Continuing Education - RISD
Providence, RI

Director, Watersheds Project
Providence, RI

Email: bcampbel01@risd.edu

Prerequisites: None

DESCRIPTION

While many software application packages address front-end issues, if you want to deepen your experience in the back-end of web design, then MySQL is well worth investigating. With a reported incidence of ten million installations, it is simply the most popular open source database available because of its extreme adaptability to run dependably on more than twenty platforms. Additionally, it is a popular database management tool for such web applications as YouTube, Friendster and Wikipedia, not to mention that it has a particular affinity for PHP. Besides the primary objective of examining the system for its practical applications, MySQL is worth exploring, as it has many uses that are within the General Public License.

GRADING

Class participation - 20%
Written project - 60%
Homework assignment - 20%

RESOURCES

Books

There are no required books associated with this class — instead, we will use the wide resource library available from the Web.

The recommended book for this class is the third edition of the book PHP 6 and MySQL 5 for Dynamic Web Sites: Visual QuickPro Guide, by Larry Ullman (December 2007), but the book is most useful in conjunction with the Dynamic Web Design with PHP course provided within the Web Design and Development RISD certificate program.

Course Handouts and On-line Readings as identified below and in class.


LINKS

  • Sun's MySQL Reference Manual
  • JUN10 TH

    welcome to mysql

    Goal: To become familiar with the relational database model and an RDMS (Relational Database Management System) including the activities associated with three primary roles associated with providing value as part of a relational database stewardship team:
    • Database Modeler
    • Database Administrator
    • Data Analyst

    Read:

    Homework:
    • Pre-test: To focus your attention on your learning objectives, please write a 1000 word essay of your current understanding of relational databases and how databases enable the information age.
    • Read the class project instructions.

    JUN17 th

    using databases for analytical inquiry

    Goal: To begin to use MySQL databases to investigate large datasets using the Structured Query Language.

    Read:

    • Review the parts tutorial from the Postgresql community that we investigated in class (the SQL to generate the tables with records is here).
    • Learn file load methods using the Structured Query Language (SQL) with US Census data as an example.
    • Structured Query Language tutorial
    • Try out loading data from a file like our census example:
      LOAD DATA LOCAL INFILE '/users/my_user_name/Desktop/census.txt' INTO TABLE population_data FIELDS TERMINATED BY '|' LINES TERMINATED BY '\n' (id, state_num, sumlevel, state, `2012`, `2011`, `2010`, `2009`, `2008`, `2007`, `2006`, `2005`, `2004`, `2003`, `2002`, `2001`);
    Homework:
    • Begin using the MAMP or WAMP environments in a RISD computer lab or on your personal devices.

    JUN24 th

    database tables for data storage

    Goal: To understand how relational tables work in isolation and in conjunction with a large data model in order to properly hold and maintain a data repository with MySQL.

    Read:

    • MySQL Data Types Tutorial
    • MySQL data type storage requirements document
    • MySQL ALTER TABLE commands
    • To find the primary keys for a database within MySQL:
      SELECT `TABLE_NAME`, `COLUMN_NAME`
      FROM `information_schema`.`COLUMNS`
      WHERE (`TABLE_SCHEMA` = 'parts')
      AND (`COLUMN_KEY` = 'PRI');

      To add a foreign key referential integrity check via SQL:
      CREATE TABLE accounts(
      account_id INT NOT NULL AUTO_INCREMENT,
      customer_id INT( 4 ) NOT NULL,
      account_type ENUM( 'savings', 'credit' ) NOT NULL,
      balance FLOAT( 9 ) NOT NULL,
      PRIMARY KEY ( account_id ),
      FOREIGN KEY (customer_id) REFERENCES supplier(sno)
      ) ENGINE=INNODB;
    Homework:
    • Build the parts database tables with the appropriate primary and foreign keys included.

    JUL1 st

    data modeling

    Goal: To participate in in-class data modeling exercises in order to gain exposure to the data modeling process.

    Read:

    Homework:
    • Perform a data modeling session with your target project data domain and build your data tables from the result.

    JUL8 th

    advanced querying with sql

    Goal: To build a solid understanding of SQL in order to build advanced commands for data manipulation and presentation.

    Read:

    Homework:
    • Work on your class project by generating the SQL commands for representative queries of interest.

    JUL15 th

    integrating mysql to websites

    Goal: Gain exposure to how MySQL databases are used to provide dynamic data services for website presentations.

    Integrated SQL queries for inventory listings:

    • To get the inventory of those Sku's that already have sales:
      SELECT inventory.Sku, SUM(inventory.transaction_qt) - SUM(sales.qt_sold) FROM `inventory`, `sales` WHERE `inventory`.transaction_category = 'ADD' AND `inventory`.Sku = `sales`.Sku GROUP BY inventory.Sku

      To get the inventory of those Sku's that have not yet had a sale:
      SELECT Sku, SUM(transaction_qt)
      FROM inventory I
      WHERE NOT EXISTS
      (SELECT * FROM sales SE
      WHERE SE.Sku = I.Sku) GROUP BY Sku;
    Homework:
    • Work on your class project.

    Turn in your project by 11:59pm on July 21st.