ITA 342
Introduction to SQL and Data Storage Technologies

Course Introduction

Congratulations on enrolling in Introduction to SQL and Data Storage Technologies. The topics you'll learn about in this course are fundamental to almost every application you'll ever use or write, regardless of the language you are programming in or the operating system you are using. Databases and the Structured Query Language (SQL) are core to an application's ability to quickly and reliably store and retrieve data. Databases are used to store information on products (retail stores), accounts (phone companies), transactions (banks), and many other items. In this course you will learn the skills necessary to design and create your own database and then access it online using a simple example of client software written in PHP.

I am writing this course after using databases and the techniques in this course extensively for over the last six years. I believe in the power of information and data. You've heard the phrase "Knowledge is power." That can be correct in a lot of circumstances, but knowledge is based on good data and information. Note that I said "good information." In today's world there's too much information, so the key is finding reliable, systematic, and organized ways to store information so we can search it, browse it, and use it to make knowledgeable decisions.

Databases are built with these features in mind and are optimized for searching and querying information. Essentially databases allow you to organize information and turn that information into good information you can use as knowledge. As a database designer I have therefore coined my own phrase that better sums up the truth in today's world, "Good information is power." If you don't believe me, ask yourself why your supermarket wants to track your purchases in their database with their loyalty card, or why your Web browsing habits are so sought after by marketers for their databases. The first step toward good information is a database and in this course we're going to learn about using databases to store and retrieve data.

I have a background in both computer engineering and technical communication and worked for the University of Washington for over seven years developing educational software. Our software systems rely on databases as the data storage technology. I'm excited to write this course that will focus on giving you the skills to turn information into power.

Course Objectives

At the end of this course, you will understand the fundamentals of database design and access in a client-server architecture. You will be able to design a normalized database system, implement that design in SQL, and use the database with another language such as PHP Hypertext Preprocessor (PHP) to query data and display it on a Web page.

Specifically, you will be able to:

• use the basic tools necessary to connect to and administer a database;
• conceptually model real-life data into a database design that properly reflects the data dependencies and relationships;
• convert a conceptual database model into a physical database design;
• design a normalized database with the proper indexing;
• use SQL to store and query data from a relational database;
• describe and implement the three-tier architecture of client-server database applications;
• use PHP scripting to query and store data via a Web interface; and
• write secure code and identify and fix security weaknesses in database applications.

Required Materials

Textbooks

I picked two books I think represent good resources and are relatively inexpensive. They're short and aren't filled with pages of examples you'll never use. The publisher, O'Reilly and Associates, has a history of publishing outstanding technical references and I hope you'll find these materials some of the best you've used in a course. The books are available from most local bookstores as well as online retailers.

Web Resources

The MySQL database is well-documented in reference form on the MySQL Web site. I prefer the Reese, Yarger, and King text for its supplementary material, overview, and summaries, but if you need a reference for something that's not covered in the book (and I do on occasion), it can be found at MySQL Developer Zone, MySQL Manual: MySQL Reference Manual: http://dev.mysql.com/doc/mysql/en/index.html

Rather than have you purchase a PHP reference also, materials not covered in the Williams and Lane text can be found on the PHP site: http://www.php.net

Technology Requirements and Skills

In addition to the technology requirements and skills noted in the Online Student Handbook, for this course we will be primarily working on a course Linux server set up with an installation of MySQL, a leading free database server. Since the majority of what you will be doing is done on the course server, the need for specific hardware or software on your machine is minimal. You can use any operating system of your choosing as long as there is a Secure Shell (SSH) terminal client for it. To date there is an SSH terminal client for all versions of Windows, Macintosh, and Linux systems that I know.

SSH terminal clients are available for download as part of the University of Washington Internet Connectivity Kit (UWICK). Please follow their instructions for downloading and installing the software. You will need your UW NetID in order to download the software. The instructions and software are located at University of Washington: Software Guide, UWICK CD: https://www.washington.edu/uware/uwick/contents.html

As a University of Washington student you can receive free technical support with the UW Internet Connectivity Kit by e-mailing: help@cac.washington.edu. Please only send e-mail to that address if you need assistance installing the SSH terminal client from UWICK. For other technical assistance, e-mail the online learning technical support staff at: DlTechSupp@extn.washington.edu.

Is This Course for You?

You should take this course if you are a professional in information technology, publishing, or service fields and need to store, organize, or search information. This course is designed for the non-programmer, so you do not need a background in computer science. Managers and administrators will also find this course valuable as they come to understand the architecture of database server systems and data storage.

Databases are language neutral in respect to the programming language an application developer can use. Therefore this course is applicable to anyone regardless of what language you know. or even if you don't know a programming language at all. You will learn the fundamentals of database design that will be applicable for the rest of your career in information technology.

This course is the second in the three-course certificate program in "Web Technology Solutions." It is offered as an independent start, three-month completion course for a total of three credits. Because of the importance of database systems in today's world, the course is also offered on a space-available basis for professionals not wishing to pursue the certificate.

To be successful, you should have taken the "Client-Side Scripting and Design" course (the first course in the certificate program), completed a similar course, or have an understanding of Web design and scripting languages.

It is not necessary to have a background in computer science for this course. However, we will be learning a programming language called PHP in order to make our databases available on a Web site. PHP is an easy-to-learn language that doesn't require any specialized development software—you can program PHP documents with a simple text editor like Vi, Pico, or Notepad. SQL is the language we'll learn to interact with our database. SQL is very "English-like" with a well-defined structure that should be relatively easy to learn.

Online Courses Are Different

Online courses can be difficult because of the lack of in-person communication. However, they also have some advantages—they allow you to work at your own pace, you get more individualized assistance from the instructor, and you can now use online technologies that work to create community. For additional resources that will help you in your first distance learning experience, please see the UW Online Learning, Online Student Handbook at:
http://www.edoutreach.washington.edu/ol/handbook/handbook.asp

While taking this course, you will primarily interact with the instructor via e-mail and possibly telephone. You can also interact with other students in the discussion forum to share ideas, hints, tips, and questions.

Course Organization

This course is offered as an independent start course. You may enroll any time during the course offering period. Once accepted into the course you have three months to finish. You can work at your own pace as long as you complete the course within the three-month completion requirement. Do not wait until the last few weeks to try and complete the course. Use the planning calendar on the syllabus to help you organize your time.

The course is organized to give you hands-on experience right from the start and includes ten lessons in which you apply your skills by following along on your own computer, ten assignments, and one project. The course also includes Key Terms in each lesson (many with definitions), a Course Discussion Forum, and Your Turn activities.

About the Lessons and Assignments

The lesson topics include the following:

Lesson One: Getting Connected to the Class Server

This lesson introduces you to the course server, gets you connected, and gives you some basic UNIX commands with which to work.

For Assignment One, you will create a small HTML file.

Lesson Two: Beginning Database Design

This lesson covers the conceptual design of databases. You learn about entities and attributes and begin to design ER diagrams.

For Assignment Two, you will create several ER diagrams.

Lesson Three: Database Design—Normalization

Lesson Three explains how to convert a conceptual database design into a physical design and how to normalize your design to remove redundancy.

For Assignment Three, You will create a First Normal Form (1NF) and a Second Normal Form (2NF) from previous data.

Lesson Four: Using SQL

This lesson covers SQL data types so you can begin creating tables and implementing your database design.

For Assignment Four, you will create and implement the tables in your database using SQL.

Lesson Five: Using Basic SQL Statements

Lesson Five introduces the basic SQL statements necessary to insert, update, select, and delete information in a database.

For Assignment Five, you will write several SELECT statements using different "Where" clauses and SQL statements using several different commands.

Lesson Six: More Complex SQL

This lesson covers SQL "joins" for defining relationships and additional topics such as table locking.

For Assignment Six, you will write one query that uses a function from several functional types, and you will write SQL statements that use either INNER or OUTER joins.

Lesson Seven: Introduction to PHP

Lesson Seven introduces PHP and outlines why it is useful. You'll also create your first PHP script.

For Assignment Seven, you will create your first functional PHP script.

Lesson Eight: Additional PHP

Lesson Eight continues with PHP, including data structures such as arrays and associative arrays, and shows you how to connect to your database using the PHP language.

For Assignment Eight, you will write a PHP script that prints out all the data in each of the tables.

Lesson Nine: Interactive PHP

This lesson covers the PHP code that displays information from your database on a Web page and accepts information from HTML forms and saves it to the database.

For Assignment Nine, you will write an HTML search page, a PHP script that processes the search term and creates and editable form, and a PHP script that processes edited values and saves the new values to the database record.

Lesson Ten: Security and Web Databases

This lesson is an overview of security issues in database applications and helps you identify and secure your applications.

For Assignment Ten, you will evaluate scripts for security.

About Key Terms

Throughout the lessons, key terms and abbreviations appear in sidebars, near their reference in the lesson; when used in the course commentary for the first time, they are highlighted in bold italics.

About the Course Discussion Forum

There is a course discussion forum created to facilitate sharing of tips, hints and questions. It is a general resource for this course. When we begin our normalization and database design, we'll also post our suggestions in the course forum and comment on each other's solutions. The course forum has an e-mail link so you can be notified when new messages are posted.

To start, I recommend logging into the forum and posting a message about yourself and why you are taking this course: You can reply to my first post if you wish. I will check the forum when there are any new messages and try to reply that same day. I would encourage you to ask your questions in the forum rather than in e-mail to me, as it provides an archived place for answers and that over time becomes a valuable resource. The course forum is accessed from your syllabus .

About "Your Turn"

Some lessons provide an opportunity for you to actively apply what you are learning either by following along on your computer and performing tasks, or by asking questions that require you to consider course material.

Assessment

Assignments

Assignments apply concepts and skills covered in each lesson to creation and manipulation of a database and a PHP Web application. The assignments increase in complexity as the lessons progress.

Weekly assignments are graded based on the following criteria:

1. Functionality —Does it work?
2. Design—Is it properly designed?
3. Correctness—Is it efficient, readable, extendable?

Assignments should be graded and returned within two days of my receiving your submission. I will provide written evaluation on your assignments by e-mail. I often copy and paste examples from your work for commenting, or I reference line numbers in your code. Please let me know if you need more or less feedback in order to assist you in learning the material.

If your work did not receive a C or better score, you can modify and re-submit your work for re-evaluation. (You may modify your work and re-submit at any time, but it is not necessary unless you receive a score lower than a C.)

Project

There is no mid-term or final exam, but a final project is required. The final project is worth about 35% of your grade. This project brings everything you learned during the course together into a single application. See the "Final Project" on your Course Syllabus for details.

Evaluation

Your grade is based on ten weekly assignments worth 20 points each and a final project worth 100 points as follows:

Assignments: 10 assignments	@ 20 points   = 200 points
Project: 1 project	@ 100 points = 100 points
	Total points    = 300 points

Grade	Percentage	Assignment Points (20)	Project Points (100)	Final Grade
A: 3.7–4.0	90–100%	18+ points	90 + points	270+ points
B: 3.0–3.6	80–89%	16–17 points	80–89 points	240–269 points
C: 2.0–2.9	70–79%	14–15 points	70–79 points	210–239 points
D: 1.0–1.9	60–69%	12–13 points	60–69 points	180–209 points
F: less than 1.0	less than 60%	less than 12 points	less than 60 points	less than 180

You must complete all assignments and the final project and have a C or better in order to pass the class. If you are struggling with the work, please ask for help sooner rather than later. I'm here to assist you, but you need to ask for assistance if you need it.

How I Will Respond

E-mail is the best way to reach me. Please use the contact information on the "About Your Instructor" page. In most cases, I will respond to your e-mail within 24 hours. During the months of July and August, I may be away for up to 10 days. I will notify all enrolled students of my pending absences in advance so you can arrange your schedule. In most cases, your e-mail will be answered within several hours.

Study Tips

The best way to succeed in this course is to begin early. Essentially you have one lesson per week, and then several weeks to complete the final project at the end of the course. Beginning early means reading the required material on the Monday or Tuesday of each week. Begin the assignment shortly thereafter and ask questions early. If you e-mail me during the week, I'll be much more available to answer questions. Waiting to do everything on a Sunday night won't be beneficial, and I most likely won't be able to answer your questions until the next day.

Shoot to complete one lesson per week in this course.

---

©2006, University of Washington. All rights reserved.
No part of this publication may be reproduced in any form or by any means without permission in writing from the publisher.