CS 200
CS 200 Fall 2013
SQL
1
Lecture 08
Relational Databases – SQL
(Structured Query Language)
CS 200 Fall 2013
SQL
BEFORE lab, please read and highlight
•
Assignment 8
•
Database Design (on learn.uwaterloo.ca)
Adapted from Access Database Design & Programming
by Steven Roman
•
The SQL tutorial notes (on learn.uwaterloo.ca)
•
Slides for this lecture
START the lab this week by doing the SQL tutorial
Topics for today
•
relational database design
•
when you need another table, and why
•
SQL (Structured Query Language)
•
a model for relational databases
Please ask questions!
2
Administrivia
CS 200 Fall 2013
SQL
CS 100 or equivalent experience with a relational DBMS (DataBase Management System)
•
most usefully, with FileMaker
•
you’ve seen a two-table relational database
•
you’ve been exposed to
forms, reports, queries, sorting, & data validation
•
you’re familiar with the terms
database
table
record (aka row)
eld (aka column)
If this wasn’t true at the beginning of the term,
•
by now you’ve worked through the Filemaker Intro
3
Assumptions for Today’s Lecture
CS 200 Fall 2013
SQL
It’s an excellent MODEL
for how relational DBMS’s work
Modern “big” DBMS’s are SQL-based
Many PC databases are not
•
but can often be used as “front-ends” to mainframe SQL systems
•
though FileMaker Pro 7, 8, 9 and MS Access
are
based on SQL
OS X actually comes with two SQLs!
•
“SQL Lite,” which is designed to be embedded in programs (including the O/S)
•
“My SQL,” a (very) popular open source SQL server (used for the Math Faculty’s inventory database)
Often you can
import data from an SQL database
into software with a nice GUI (eg FileMaker)
by crafting an appropriate “SQL select statement”
4
Why SQL?
CS 200 Fall 2013
SQL
Why use a database at all?
Structuring data allows us to do things we can’t do e
ciently,
or can’t feasibly do, with unstructured data
•
The added power &
exibility aren’t free
it takes time and e
ort to create (and maintain) the structure
•
You have to decide if that e
ort is worthwhile
Obvious questions:
•What do I mean by “structure” in a database? •What’s the payo?
5
Databases – Structured Data
CS 200 Fall 2013
SQL
The visa worksheet in the Excel assignment
•
is an example of a 1–table database,
although we built it in Excel, not with a DBMS
•
each ROW
(“record”) holds data for a particular transaction
•
each COLUMN holds a particular piece of data about that transaction (a “
eld”)
•
we
could
have used FileMaker
though for what we wanted to do,
it wouldn’t have been worth the e
ort of learning FileMaker
•
indeed, we could have placed the data in a Word table
or even in a text processor (eg BBEdit)...
separate
elds by tabs, separate records by ¶
but working with the data would have been MUCH harder
— think about implementing the Actual Balance and Statement Balance columns!
We’ll look at databases in Excel later
6
“Structure”
CS 200 Fall 2013
SQL
7
The Excel Assignment — Keeping Track of
VISA Charges
CS 200 Fall 2013
SQL
Data entry
“validation”
Sophisticated searching (aka
“queries”
)
Sophisticated summarizing and reporting
Safe simultaneous updates by multiple users
