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

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:

• Wikipedia's introduction to relational databases which provides a good overview of the relevance of Edwin Codd's relational database theory
• If interested, read a more in-depth investigation of relational database theory.

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.

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.

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.

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

Read:

• We'll deconstruct the Wikipedia page on data modeling.
• MySQL developers' take: Why you want to be good at data modeling
  
  
• Participate in the data modeling in-class exercise

Homework:

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

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

Read:

• We will continue a MySQL command language primer using data from a social media site.
• Our focus will be on data analysis using SQL commands from the Parts Database Primer

Homework:

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

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.